慢查询

慢查询指的是查询速度慢的查询语句，MySQL提供了慢查询日志功能，可以记录查询速度慢的查询语句，以便后续优化。
使用下列命令查看慢查询日志是否开启

show variables like "slow_query_log";

slow query log表示慢查询开启的状态
slow_query_log_file表示慢查询日志存放的位置

如果没有开启慢查询日志，可以使用下列命令开启

set global slow_query_log = ON;

使用show variables like 'long_query_time'命令，查看超过多少时间，才记录慢查询日志
可以使用set global long_query_time = 1;设置超过1秒的查询记录慢查询日志，单位是秒，可以自己设置，0表示所有查询都记录慢查询日志。
可以通过上面两个命令结合，查看慢查询SQL的位置，以及慢查询日志记录的时间。

当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划。

• 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：找到我们所需的记录，需要读取的行数，对于InnoDB表，这个值是一个估计值
• filtered：百分比，表里符合条件的记录树的百分比。表示存储引擎返回的数据在经过过滤后，剩下的满足条件的记录数量的比例
• extra：包含MySQL解决查询的详细信息，常见的值有：
  • Using filesort：MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，这个过程称为“文件排序”
  • Using temporary：MySQL需要创建一个临时表来存储结果集，常见于排序和分组查询
  • Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率较高
  • Using where：表示MySQL服务器从存储引擎中获取记录后再进行过滤，这是一个非常好的标志，表示MySQL服务器在存储引擎检索行后，再进行条件过滤，而不是取出所有数据后在服务器层过滤
  • Using join buffer：表示使用了连接缓存
  • Using index condition：表示使用了索引过滤数据
• key：显示MySQL实际使用的索引，如果为null，则没有使用索引
• key_len：表示对应的执行计划在执行时，使用到的索引字段长度，一般情况下都为索引字段的长度

看执行计划的话，先看select_type、type，其次看rows，再看key、key_len，然后看ref，最后看Extra、filtered，然后结合各项指标，就能推断出谁快谁慢。

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态以及消耗的时间，需要使用profiling，profiling: 分析执行耗时，默认是关闭，可以使用show variables like '%profil%'查看是否开启。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。

set profiling = 1; -- 开启profiling
show profiles; -- 查看所有的SQL分析
show profile for query 1; -- 查看id为1的SQL的分析
show profile cpu, block io for query 1; -- 查看id为1的SQL的CPU和IO分析

profile只能查看SQL的执行耗时，无法查看SQL真正执行的过程信息，我们可以使用Optimizer Trace，跟踪执行语句的解析优化执行的全过程。可以使用set optimizer_trace = "enabled=on"开启，输入一条SQL语句，然后使用select * from information_schema.OPTIMIZER_TRACE查看那条SQL的执行过程。
可以看到三个阶段，join_preparation表示准备阶段，join_optimization表示分析阶段，join_execution表示执行阶段。

最后确认问题，采取措施

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