admin 管理员组文章数量: 1087652
慢查询优化
1、定位慢SQL
实际工作中，如果碰到某个功能或者某个接口需要很长时间才能返回结果，那我们就需要去确定是否由慢查询引起的；
定位慢查询有以下两种解决方案：
- 对已执行完成慢查询，通过查看慢查询日志确定。
- 对正在执行的慢查询使用show processlist查看。
1.1 通过慢查询日志
MySQL的慢查询慢查询日志用来记录在MySQL中响应时间超过参数long_query_time（单位：秒，默认10）设置的值并且扫描记录数不小于min_examined_row_limit（默认值 0）的语句，能够帮我们找到执行完的慢查询，方便我们对这些 SQL 进行优化。
知识扩展：
默认情况下，慢查询日志中不会记录管理语句，可通过设置 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。
默认情况下，也不会记录查询时间不超过 long_query_time 但是不使用索引的语句，可通过配置 log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中（即使查询时间没超过 long_query_time 配置的值）。
如果需要使用慢查询日志，一般分为四步：
-
开启慢查询日志
慢查询日志，默认环境下，慢查询日志是关闭的，由参数 slow_query_log 决定是否开启，在 MySQL 命令行下输入下面的命令：
set global slow_query_log = on -
设置慢查询阀值
在 MySQL 命令行下输入下面的命令：
set global long_query_time = 1知识扩展：
MySQL 中 long_query_time 的值如何确定呢？
线上业务一般建议把 long_query_time 设置为 1 秒，如果某个业务的 MySQL 要求比较高的 QPS，可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。
测试环境一般建议 long_query_time 设置的阀值比生产环境的小，比如生产环境是 1 秒，则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。
甚至某些重要业务测试环境 long_query_time 可以设置为 0，以便记录所有语句。并留意慢查询日志的输出，上线前的功能测试完成后，分析慢查询日志每类语句的输出，重点关注 Rows_examined（语句执行期间从存储引擎读取的行数），提前优化。 -
确定慢查询日志路径
慢查询日志的路径默认是 MySQL 的数据目录
show global variables like "datadir" -
确定慢查询日志的文件名
show global variables like "slow_query_log_file"根据上面四步的查询结果，可以直接查看/var/lib/mysql/6017bde482bf-slow.log 文件获取已经执行完的慢查询
[root@mysqltest ~]# tail -n5 /var/lib/mysql/6017bde482bf-slow.logTime: 2019-05-21T09:15:06.255554+08:00User@Host: root[root] @ localhost [] Id: 8591152Query_time: 10.000260 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1558401306; select sleep(10);这里对上方的执行结果详细描述一下：
- tail -n5：只查看慢查询文件的最后 5 行
- Time：慢查询发生的时间
- User@Host：客户端用户和 IP
- Query_time：查询时间
- Lock_time：等待表锁的时间
- Rows_sent：语句返回的行数
- Rows_examined：语句执行期间从存储引擎读取的行数
1.2 通过 show processlist
有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限，则可以看到所有线程。否则，只能看到当前会话的线程。
知识扩展：如果不使用 FULL 关键字，在 info 字段中只显示每个语句的前 100 个字符，如果想看语句的全部内容可以使用 full 修饰（show full processlist）。
执行结果如下：
mysql> show processlist
这里对上面结果解释一下：
- Time：表示执行时间
- Info：表示 SQL 语句
我们这里可以通过它的执行时间（Time）来判断是否是慢 SQL。
2、使用explain分析慢SQL
分析 SQL 执行效率是优化 SQL 的重要手段，通过上面讲的两种方法，定位到慢查询语句后，我们就要开始分析 SQL 执行效率了，子曾经曰过：“工欲善其事，必先利其器”，我们可以通过 explain诊断工具来分析慢查询。
Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用方法：在查询语句前面加上 explain 运行就可以了。
如：
mysql> explain select * from t1 where b=100;
Explain 的结果各字段解释如下，加粗的列为需要重点关注的项。
| 列名 | 解释 |
|---|---|
| id | 查询编号 |
| select_type | 查询类型，显示该行是简单查询还是复杂查询 |
| table | 涉及到的表 |
| partitions | 匹配的区分：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。 |
| type | 本次查询的表连接类型 |
| possible_keys | 可能选择的索引 |
| key | 实际选择的索引 |
| ken_len | 被选择的索引长度，一般用于判断联合索引有多少列被选择了 |
| ref | 与索引比较的列 |
| rows | 预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确 |
| filtered | 按条件筛选的行的百分比 |
| extra | 附加信息 |
2.1 select_type
| select_type 的值 | 解释 |
|---|---|
| SIMPLE | 简单查询 (不使用关联查询或子查询) |
| PRIMARY | 如果包含关联查询或者子查询，则最外层的查询部分标记为 primary |
| UNION | 联合查询中第二个及后面的查询 |
| DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
| UNION RESULT | 联合查询的结果 |
| SUBQUERY | 子查询中的第一个查询 |
| DEPENDENT SUBQUERY | 子查询中的第一个查询，并且依赖外部查询 |
| DERIVED | 用到派生表的查询 |
| MATERIALIZED | 被物化的子查询 |
| UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存，必须重新评估外层查询的每一行 |
| UNCACHEABLE UNION | 关联查询第二个或后面的语句属于不可缓存的子查询 |
2.2 type
| type 的值 | 解释 |
|---|---|
| system | 查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况 |
| const | 基于主键或唯一索引查询，最多返回一条结果 |
| eq_ref | 表连接时基于主键或非 NULL 的唯一索引完成扫描 |
| ref | 基于普通索引的等值查询，或者表间等值连接 |
| fulltext | 全文检索 |
| ref_or_null | 表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值 |
| index_merge | 利用多个索引 |
| unique_subquery | 子查询中使用唯一索引 |
| index_subquery | 子查询中使用普通索引 |
| range | 利用索引进行范围查询 |
| index | 全索引扫描 |
| ALL | 全表扫描 |
上表的这些情况，查询性能从上到下依次是最好到最差。
2.3 Extra
| Extra 常见的值 | 解释 | 例子 |
|---|---|---|
| Using filesort | 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序 | explain select * from t1 order by create_time; |
| Using temporary | 需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时 | explain select * from t1 group by create_time; |
| Using index | 使用覆盖索引 | explain select a from t1 where a=111; |
| Using where | 使用 where 语句来处理结果 | explain select * from t1 where create_time=‘2019-06-18 14:38:24’; |
| Impossible WHERE | 对 where 子句判断的结果总是 false 而不能选择任何数据 | explain select * from t1 where 1<0; |
| Using join buffer (Block Nested Loop) | 关联查询中，被驱动表的关联字段没索引 | explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time); |
| Using index condition | 先条件过滤索引，再查数据 | explain select * from t1 where a >900 and a like “%9”; |
| Select tables optimized away | 使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是 | explain select max(a) from t1; |
3.show profile 分析慢查询
大致步骤：
- 确定这个 MySQL 版本是否支持 profile；
- 确定 profile 是否关闭；
- 如果关闭开启 profile；
- 在服务器端发送要执行的 SQL；
- 查看执行完 SQL 的 query id；
- 通过 query id 查看 SQL 的每个状态及耗时时间；
- 停止profile；
3.1 确定是否支持 profile
select @@have_profiling
3.2 查看 profiling 状态
select @@profiling;
3.3 执行要分析的sql语句
select @@profiling;
set profiling=1 ;
select * from t1 where b=100;
show profiles ;### 执行完成SQL后再执行：show profiles；得到profile id。
show profile for query 1 ; ## 根据profile id查询指定SQL执行详情
3.4 查看sql的开销
查看指定SQL的CPU开销：show profile cpu for query 1;
查询指定SQL的内存开销： show profile memory for query 1;
3.5 关闭profile
mysql> set profiling=off;
本文标签： 慢查询优化
版权声明：本文标题：慢查询优化 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1686562735a10673.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论