MySQL中慢SQL的查询及原因分析
2020-12-15 本文已影响0人
nitricoxide
准备数据
查看系统变量
SHOW VARIABLES LIKE 'slow_query%';
- slow_query_log：慢sql日志开启状态
- slow_query_log_file：慢sql日志存放位置
SHOW VARIABLES LIKE 'long_query_time';
- long_query_time：执行超过多少秒才记录日志
修改系统变量
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time=0.001;
执行修改语句后如果再次查询没有生效，重新打开个窗口再次查询就可以了。
测试结果
执行查询语句
select * from student;
slow.log中的记录
select * from student;
# Time: 2020-08-04T06:09:07.716447Z
# User@Host: root[root] @ localhost [::1] Id: 13
# Query_time: 0.001787 Lock_time: 0.000417 Rows_sent: 2 Rows_examined: 1024
SET timestamp=1596521347;
再次测试更新语句
UPDATE student SET `name` = '李四' WHERE id = 999;
> Affected rows: 1
> 时间: 0.004s
发现也被记录了
UPDATE student SET `name` = '李四' WHERE id = 999;
# Time: 2020-08-04T06:25:11.808408Z
# User@Host: root[root] @ localhost [::1] Id: 13
# Query_time: 0.001236 Lock_time: 0.000092 Rows_sent: 356 Rows_examined: 712
SET timestamp=1596522311;
- Time：慢sql发生时间
- User@Host：客户端用户和IP
- Query_time：查询时间
- Lock_time：等待表锁的时间
- Rows_sent：语句返回的行数
- Rows_examined：语句执行期间从存储引擎扫描的行数
sql执行分析
EXPLAIN
Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。
使用方法：在查询语句前面加上 explain 运行就可以了。
EXPLAIN UPDATE student SET `name` = '李' WHERE id = 999;
EXPLAIN字段详解
| 列名 | 描述 |
|---|---|
| id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
| select_type | SELECT关键字对应的哪个查询的类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际使用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外的信息 |
select_type详解
| select_type的值 | 解释 |
|---|---|
| SIMPLE | 简单查询(不使用关联查询或子查询) |
| PRIMARY | 如果包含关联查询或者子查询 |
| UNION | 联合查询中第二个及后面的查询 |
| DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
| UNION RESULT | 联合查询的结果 |
| SUBQUERY | 子查询中的第一个查询 |
| DEPENDENT SUBQUERY | 子查询中的第一个查询，并且依赖外部查询 |
| DERIVED | 用到派生表的查询 |
| MATERIALIZED | 被物化的子查询 |
| UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存，必须重新评估外层查询的每一行 |
type详解
查询性能从上到下依次是最好到最差
| type的值 | 解释 |
|---|---|
| system | 查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况 |
| const | 基于主键或唯一索引查询，最多返回一条结果 |
| eq_ref | 表连接事基于主键或非NULL的唯一索引完成扫描 |
| ref | 基于普通索引的等值查询，或者表间等值连接 |
| fulltext | 全文检索 |
| ref_or_null | 表连接类型是ref，但进行扫描的索引列中可能包含NULL值 |
| index_merge | 利用多个索引 |
| unique_subquery | 子查询中使用唯一索引 |
| index_subquery | 子查询中使用普通索引 |
| range | 利用索引进行范围查询 |
| index | 全索引扫描 |
extra详解
| Extra常见的值 | 解释 |
|---|---|
| Using filesort | 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序 |
| Using temporary | 需要创建一个临时表来存储结构，通常发生对没有索引的列进行GROUP BY时 |
| Using index | 使用覆盖索引 |
| Using where | 使用where语句来处理结果 |
| Impossible WHERE | 对where子句判断的结果总是false而不能选择任何数据 |
| Using join buffer | 改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 |
| Using index condition | 先条件过滤索引，再查数据 |
| Select tables optimized away | 使用某些聚合函数(比如max、min)来访问存在索引的某个字段 |
