慢sql优化（一）

慢sql优化

慢SQL优化思路。

慢查询日志记录慢SQL

explain分析SQL的执行计划

profile 分析执行耗时

Optimizer Trace分析详情

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

慢查询日志记录慢SQL

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件和数据库表。

slow_query_log表示慢查询开启的状态

slow_query_log_file表示慢查询日志存放的位置

explain查看分析SQL的执行计划

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

select_type

SIMPLE:简单SELECT（不适用UNION或子查询）
PRIMARY:最外面的select
UNION：UNION中的第二个或者后面的select语句
DEPENDENT UNION:UNION的第二个或者后面的select语句，取决于外面的查询
UNION RESULT：UNION的结果
SUBQUERY：子查询中第一个select
DEPENDENT SUBQUERY:子查询中第一个select，取决于外面的查询
DERIVED：导出表的select（FROM子句的子查询）

table

表

type

possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在
possible_keys中的某些键实际上不能按生成的表次序使用。

key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在
查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示mysql决定使用的键长度，如果键是null，则长度为null

ref

ref列显示使用哪个列或常数与key一起从表中选择行

rows

rows列显示mysql认为它执行查询时必须检查的行数

Extra

profile 分析执行耗时

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

set profiling=ON开启
show profiles查看
show profile for query id （其中id就是show profiles中的QUERY_ID）查看具体一条的SQL语句分析
show profile cpu,block io for query id

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的方式，或者数仓。

如果单表数据量过大导致慢查询，则可以考虑分库分表

如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟DBA讨论优化方案

如果存量数据量太大，考虑是否可以让部分数据归档

如何优化group by
group by 后面的字段加索引

order by null 不用排序

尽量只使用内存临时表

使用SQL_BIG_RESULT

参考博文：
SQL太慢如何进行优化：https://blog.csdn.net/qq877192055/article/details/131321092
MySQL数据库基础知识3，mysql索引详解，上篇：https://blog.csdn.net/guorui_java/article/details/127189522