MySQL查询截取分析

一、查询优化

1、小表驱动大表

2、order by排序优化

尽量使用index方式排序，避免使用filesort方式排序

使用index排序的情况如下

使用filesort方式排序的情况如下


MySQL支持两种方式排序，filesort和index。index效率更高，它是MySQL扫描索引本身完成的排序。

order by满足两种情况会使用index排序：

• order by语句使用索引满足最佳左前缀原则
• where条件字段和ORDER BY子句组合起来，满足最佳左前缀法则

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀顺序，如果不再索引列上，filesort会使用双路排序或者单路排序算法完成排序。

双路排序

• 双路排序是指两次扫描磁盘，最终得到数据。
• 首先读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据并输出。
• 双路排序从磁盘读取排序字段，并在buffer中进行排序，再从磁盘读取其它字段。取一批数据，要对磁盘进行了两次扫描，IO很耗时。

单路排序

• 从磁盘读取查询所需要的所有列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。
• 单路排序效率更高，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中。
• 单路排序存在问题。在sort_buffer中，单路排序比双路排序要占用更多的空间，因为单路排序是把所有的字段全部取出来，所以有可能取出的数据总大小超过了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据来进行排序(创建tmp，多路合并)，每次排序完后再取sort_buffer容量大小的数据，再进行排序，只能所有数据排序完成，这样就会导致多次IO操作。

优化策略

排序总结

• MySQL有两种排序方式，文件排序和扫描有序索引排序。
• MySQL可以使用索引来进行查询或者排序。
• order by如果要使用索引排序，需满足最佳左前缀原则。
• 如果where使用索引的最左前缀定义为常量，则order by能使用索引。
  

3、group by优化

• group by实质是先排序后分组，遵照索引建的最佳左前缀原则
• 当无法使用索引列，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置
• where高于having，尽量在where中限定条件

二、慢查询日志

MySQL慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指的是运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，long_query_time默认值是10。提供慢查询日志再结合explain可以进行全面的分析。

日志分析工具mysqldumpslow

//--获取返回集最多的10条SQL
mysqldumpslow -s r -t 10 G:\Programs\mysql-8.0.18\data\layman-slow.log
//--获取访问次数最多的10条SQL
mysqldumpslow -s c -t 10 G:\Programs\mysql-8.0.18\data\layman-slow.log
//--获取按时间排序的前10条含有LEFT JOIN的SQL语句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" G:\Programs\mysql-8.0.18\data\layman-slow.log
//--结合|more使用，否则有可能会爆屏
mysqldumpslow -s r -t 10 G:\Programs\mysql-8.0.18\data\layman-slow.log |more

三、批量数据脚本

创建dept和emp两张表

创建函数，保证每条数据不同

随机产生字符串

随机产生部门编号

创建往emp表中插入数据的存储过程

创建往dept表中插入数据的存储过程

调用存储过程向dept表中插入数据

调用存储过程向emp表中插入50w条数据

四、使用show profile进行SQL分析

show profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。

开启Show Profile功能，默认是关闭的，使用前需要开启。

执行SQL语句

SELECT * FROM `emp` where `id`%20 = 0 ORDER BY 5;
SELECT * FROM `emp` where `id`%10 =0 LIMIT 150000;

查看结果，执行SHOW PROFILES;

诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID; 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。

Show Profile查询列表，日常开发需要注意的结论：

• converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
• Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
• Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
• locked：死锁。