3.1 查询优化

clip_image155.jpg

3.1.1 永远小表驱动大表 类似嵌套循环Nested Loop

Case

clip_image157.jpg

clip_image159.jpg

clip_image161.jpg

3.1.2 order by关键字优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

建表SQL

Case

1

clip_image163.jpg

2

clip_image165.jpg

MySQL支持二种方式的排序，FileSort和Index,Index效率高。 它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序

ORDER BY语句使用索引最左前列

使用where子句与OrderBy子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序

双路排序

MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。 读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题

参阅: 优化策略

由于单路是后出来的，总体而言好过双路

但是用单路有问题

clip_image167.jpg

优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

why

clip_image169.jpg

小总结

clip_image171.jpg

3.1.3 GROUP BY关键字优化

groupby实质是先排序后进行分组，遵照索引建的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having,能写在where限定的条件就不要去having限定了。

3.2 慢查询日志

3.2.1 是什么

clip_image173.jpg

3.2.2 怎么玩

说明

clip_image175.jpg

查看是否开启及如何开启

默认

SHOW VARIABLES LIKE '%slow_query_log%'

clip_image177.jpg

开始

set global slow_query_log = 1

clip_image179.jpg

clip_image181.jpg

那么开启慢查询日志后，什么样的SQL参会记录到慢查询里面？

clip_image183.jpg

Case

查看当前多少秒算慢

SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阙值时间

clip_image185.jpg

set global long_query_time=3;

为什么设置后看不出变化？

需要重新连接或者新开一个回话才能看到修改值。 SHOW VARIABLES LIKE 'long_query_time%';

show global variables like 'long_query_time';

记录慢SQL并后续分析

clip_image187.jpg

查询当前系统中有多少条慢查询记录

clip_image189.jpg

配置版

clip_image191.jpg

3.2.3 日志分析工具mysqldumpshow

clip_image193.jpg

查看mysqldumpshow的帮助信息

clip_image195.jpg

s:是表示按何种方式排序

c:访问次数

l:锁定时间

r:返回记录

t:查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

t:即为返回前面多少条的数据

g:后边搭配一个正则匹配模式，大小写不敏感的

工作常用参考

clip_image197.jpg

3.3 批量数据脚本

作者：简简算算
链接：https://www.jianshu.com/p/154325bff7ad