慢SQL排查定位

慢查询日志记录慢SQL

• 定位慢SQL可以通过慢查询日志来查看慢SQL，默认的情况下，MySQL数据库不开启慢查询日志(slow query log)，需要手动把它打开：SET GLOBAL slow_query_log = 'ON';
• 查看下慢查询日志配置，使用 show variables like 'slow_query_log%' 命令，如下：
  
• slow query log 表示慢查询开启的状态
• slow_query_log_file 表示慢查询日志存放的位置
• 使用 show variables like 'long_query_time' 命令，查看超过多少时间，才记录到慢查询日志，如下：
  

explain查看分析SQL的执行计划

• 当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划
• 当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息，即MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表等信息
• 一条简单SQL，使用了explain的效果如下：
  
• 需要重点关注type、rows、filtered、extra、key

  • type
    表示连接类型，查看索引执行情况的一个重要指标，以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    • system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的
    • const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，速度非常快
    • eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
    • ref : 常用于非主键和唯一索引扫描
    • ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
    • index_merge：使用了索引合并优化方法，查询使用了两个以上的索引
    • unique_subquery：类似于eq_ref，条件用了in子查询
    • index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值
    • range：常用于范围查询，比如：between … and 或 In 等操作
    • index：全索引扫描
    • ALL：全表扫描

  • rows
    该列表示MySQL估算要找到我们所需的记录，需要读取的行数，对于InnoDB表，此数字是估计值，并非一定是个准确值

  • filtered
    该列是一个百分比的值，表里符合条件的记录数的百分比，简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

  • extra
    该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

    • Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现，一般见于order by语句
    • Using index ：表示是否用了覆盖索引
    • Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化，一般多见于group by语句，或者union语句
    • Using where : 表示使用了where条件过滤
    • Using index condition：MySQL5.6之后新增的索引下推，在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据

  • key
    该列表示实际用到的索引，一般配合possible_keys列一起看

profile 分析执行耗时

• explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling，开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化
• profiling默认是关闭，使用 show variables like '%profil%' 查看是否开启，如下：
  
• 使用 set profiling=ON 开启，开启后，可以运行几条SQL，然后使用show profiles查看一下
  
• show profiles会显示最近发给服务器的多条语句，条数由变量profiling_history_size定义，默认是15；如果需要看单独某条SQL的分析，可以show profile查看最近一条SQL的分析，也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）查看具体一条的SQL语句分析
• profile 查看CPU、IO：https://blog.csdn.net/weixin_35049095/article/details/113452138

Optimizer Trace分析详情

• profile只能查看到SQL的执行耗时，但无法看到SQL真正执行的过程信息，不知道MySQL优化器是如何选择执行计划，这时候，可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程
• 使用 set optimizer_trace="enabled=on" 打开开关，接着执行要跟踪的SQL，最后执行 select * from information_schema.optimizer_trace 跟踪
  
• 分析其执行树，会包括三个阶段：
  • join_preparation：准备阶段
  • join_optimization：分析阶段
  • join_execution：执行阶段
    

确定问题并采用相应的措施

• 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，可以优化索引
• 还可以优化SQL语句，比如一些in元素过多问题(分批)，深分页问题(基于上一次数据过滤等)，进行时间分段查询
• SQL没办法很好优化，可以改用ES的方式，或者数仓
• 如果单表数据量过大导致慢查询，可以考虑分库分表
• 如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数
• 如果存量数据量太大，考虑是否可以让部分数据归档