本节主要介绍了MySQL的查询截取分析，针对执行查询过程中出现的问题进行分析，包括查询优化，慢查询日志，批量数据脚本，Show Profile，全局查询日志等等，进一步提高SQL执行的效率。

查询截取分析总体步骤

  1. 慢查询开启并捕获
  2. explain+慢SQL分析
  3. show profile查询SQL在MySQL服务器中的执行细节和生命周期情况
  4. SQL数据库服务器参数调优

MySQL查询优化

小表驱动大表

  • 也即小的数据集驱动大的数据集

    MySQL查询截取分析 - 图1

    对于EXISTS的理解：

    SELECT xxx from table WHERE EXISTS(子查询);

    将外层主查询的结果，放到子查询中做条件验证，根据验证结果（TRUE或者FALSE）来决定主查询的结果是否保留 但实际的使用情况还需要根据实际进行调整，EXISTS其实也就是IN的一种替代

ORDER BY关键字优化

  1. Order by子句尽量使用Using INDEX排序，最好不要出现Using filesort文件排序。

    • 建立表以及索引

      MySQL查询截取分析 - 图2

    • 分析各个查询中的排序是否会出现Using filesort

      复合索引(age,birthday)

      索引排序Using INDEX

      MySQL查询截取分析 - 图3

      MySQL查询截取分析 - 图4

      当复合索引的最佳左前缀为常量的时候 后面的字段用于排序也是可以的

      MySQL查询截取分析 - 图5 出现 Using filesort

      MySQL查询截取分析 - 图6

索引：排好序的快速查找数据结构。这里的排好序，默认是升序（ASC）

MySQL查询截取分析 - 图7

但是，如果对索引列同为升序或者同为降序，那么索引还是有作用的

MySQL查询截取分析 - 图8 MySQL查询截取分析 - 图9

  1.   - MySQL支持两种排序，一种是使用INDEX进行排序，另一种就是filesort文件排序。当然，使用INDEX索引进行排序效率高于filesort文件排序。
  1. 尽可能在索引列上完成排序，遵照索引建的最佳左前缀原则
  2. 如果不在索引列上完成排序，filesort有两种算法
    • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
    • 双路排序：先根据排序的条件去表中取出排序字段以及每一条数据对应的行id，然后再在sort buffer中对排序字段进行排序，此时生成的就是按照排序字段排好序，并且带有未排序之前的表中行的id，然后再次根据这个id去表中取出其他字段
    • 至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序（因此建议查询什么字段就取什么字段，而不是select *），反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。
  3. 使用索引进行排序的情况

    MySQL查询截取分析 - 图10

GROUP BY关键字优化

  1. Group by 实质是先排序后分组，遵循索引的最佳左前缀原则
  2. 无法使用索引列的时候，同样的和order by增加配置文件中参数max_length_for_sort_data和sort_buffer_size的大小可以提高效率
  3. where高于having，能在where中进行限定的就不要再having中进行限定了

慢查询日志

是什么

  • MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过给定值的语句，具体是指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
  • long_query_time默认值是10，意思是运行时间10秒
  • 通过慢查询日志我们可以查看执行时间超过限制的SQL语句。

如何使用

  1. 默认情况下，MySQL是没有开启慢查询日志的。需要我们手动来设置这个参数
  2. 当然，如果不是调优需要的话，一般不建议开启这个参数。因为开启慢查询多少会影响性能，慢查询日志支持将日志记录写入到文件中

  3. 查看是否已经开启

    show variables like '%slow_query_log%';

    MySQL查询截取分析 - 图11

  4. 开启慢查询日志

    set global slow_query_log = 1;

    这个开启只对当前数据库成立，当数据库重启之后就会失效。

    MySQL查询截取分析 - 图12

  5. 修改默认的时长

    查看当前时长

    MySQL查询截取分析 - 图13

    修改当前时长为3s

    set global long_query_time = 3;

    MySQL查询截取分析 - 图14

    运行时间大于设定的时长才会被记录，等于的不会记录

    tips：修改之后需要重新开启一个sql窗口才能看到修改之后的变化

  6. 查看慢查询日志的内容

    测试语句

    select sleep(4);

    慢查询日志内容

    MySQL查询截取分析 - 图15

  7. 查看当前慢查询日志中记录的总条数

    show global status like '%slow_queries%';

    MySQL查询截取分析 - 图16

日志分析工具mysqldumpslow

  1. 此工具可以对慢查询日志进行统计，比如，执行最频繁的SQL语句，执行最耗时的SQL语句等等，避免我们进行人共统计
  2. 详细使用方法可以参考参考

批量数据脚本

插入大批量的数据不要使用insert，因为insert事务默认提交。可以批量插入数据，设置为手动提交事务，数据插入完成之后再commit。

Show Profile

用于查看SQL语句执行的过程

查看并开启功能

MySQL查询截取分析 - 图17 运行SQL，并查看运行记录

MySQL查询截取分析 - 图18 可能会遇到的问题

Converting HEAP to MyISAM 查询结果太大，内存不够，将数据搬到磁盘

Creating tmp table 创建零时表

Copying to tmp table on disk 把内存中的临时表复制到磁盘（危险！！）

全局查询日志

  • 记录MySQL执行的所有的SQL历史
  • 测试环境可以使用，但也是用的时候开启。
  • Show Profile功能更加强大

    MySQL查询截取分析 - 图19