explain分析SQL执行计划

最新推荐文章于 2022-05-08 18:18:41 发布

jialun0116

最新推荐文章于 2022-05-08 18:18:41 发布

阅读量293

点赞数

分类专栏：面试 mysql 面经文章标签：数据库索引 mysql 调优 explain

本文链接：https://blog.csdn.net/qq_30031221/article/details/117232505

版权

mysql 同时被 3 个专栏收录

44 篇文章 3 订阅

订阅专栏

面试

34 篇文章 5 订阅

订阅专栏

面经

21 篇文章 3 订阅

订阅专栏

explain分析SQL执行计划

慢查询配置

-- 查看是否开启慢查询
show variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | ON                                  |
| slow_query_log_file | /usr/local/var/mysql/admin-slow.log | 文件存放位置
+---------------------+-------------------------------------+

-- 开启慢查询和慢查询的时间 单次有用
set global slow_query_log='ON'; 
set global long_query_time=1;  --指定记录慢查询日志SQL执行时间得阈值
-- 写入配置文件
[mysqld]
long_query_time=1
slow_query_log=ON

配置了慢查询后，它会记录以下符合条件的SQL：

查询语句
数据修改语句
已经回滚的SQL

慢查询日志

在这里插入图片描述

第一行：记录时间
第二行：用户名、用户的IP信息、线程ID号
第三行：执行花费的时间【单位：秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数
第四行：这SQL执行的时间戳
第五行：具体的SQL语句

具体参数解析

在这里插入图片描述

id：
- 每个执行计划都有一个 id，并且id的顺序是按 select 出现的顺序增长的。
- SELECT的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
select_type：表示 SELECT 查询类型，常见的有
- SIMPLE（普通查询，即没有联合查询、子查询）
- PRIMARY（主查询）最外层的select
- UNION（UNION 中后面的查询）
- SUBQUERY (子查询) ，包含在 select 中的子查询(不在 from 子句中)
- DERIVED ，包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)
table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
partitions：访问的分区表信息。
type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
- 一般来说，得保证查询达到range级别，最好达到ref
- system/const：用于 primary key 或 unique key 的所有列与常数比较时。system是const的特例，表里只有一条元组匹配时为system
- eq_ref：使用唯一索引扫描**，primary key 或 unique key 索引的所有部分被连接使用** 常见于多表连接中使用主键和唯一索引作为关联条件。
- ref：非唯一索引扫描，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
- range：索引范围扫描，比如，<，>，between 等操作。
- index：索引全表扫描，此时遍历整个索引树。
- ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：可能使用到的索引。
key：实际使用到的索引。强制使用索引FORCE INDEX (index_name)、USE INDEX (index_name)
key_len：ysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列,
- 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节 char(n):
- varchar(n):如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
- 数值类型 tinyint:1字节、smallint:2字节、 int:4字节 bigint:8字节
- 时间类型 date:3字节、timestamp:4字节、datetime:8字节
- 如果字段允许为 NULL，需要1字节记录是否为 NULL
ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)，字段名(例: film.id)
rows：mysql估计要读取并检测的行数，注意这个不是结果集里的行数
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息。
- 前导列：联合索引中的第一列或“前几列”，以及单列索引的情况
- Using index。查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index。意味着通过索引查找就能直接找到符合条件的数据，无须回表。
- Using where。使用 where 语句来处理结果，并且查询的列未被索引覆盖。说明MySQL服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查询。可能的原因：1. 查询的列未被索引覆盖；2. where筛选条件非索引的前导列或无法正确使用到索引；
- Using index condition，查询的列不全在索引中，where条件中是一个前导列的范围
- Using temporary，这意味着MySQL在对查询结果排序时会使用一个临时表，可以添加索引来优化。
- Using filesort，将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
- Using where;Using index。查询的列被索引覆盖，并且where筛选条件是索引列之一，但不是索引的前导列或出现了其他影响直接使用索引的情况（如存在范围筛选条件等），Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据，影响并不大。

-- 例子
SELECT * from vio_basic_domain_info where app_name like '%陈%' ;
-- EXPLAIN 分析 1.185s
mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%陈%' ;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |    11.11 | Using where |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

-- 该SQL用到了idx_app_name索引，查询类型是索引范围查询，扫描行数141行  
SELECT * from vio_basic_domain_info where app_name like '陈%' ;
-- EXPLAIN 分析 0.156s 查找的列不完全被索引覆盖
EXPLAIN SELECT * from vio_basic_domain_info where app_name like '陈%' ;
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using index condition |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

-- 这条SQL使用到覆盖索引时，SQL如下：查询耗时：0.091s，查到141条数据
 EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '陈%' ;
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using where; Using index |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

explain extended:会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。
explain partitions:相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

优化案例 LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。
一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 1000000,10这样的查询，这是mysql需要查询1000000条然后只返回最后10条，前面的1000000条记录都将被舍弃，这样的代价很高，会造成慢查询。

mysql> EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |   100.00 | NULL  |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

该语句存在的最大问题在于limit M,N中偏移量M太大，导致每次查询都要先从整个表中找到满足条件 的前M条记录，
之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。
如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。

解决方法一：构造覆盖索引

通过修改SQL，使用上覆盖索引，比如我需要只查询表中的app_name、createTime等少量字段，那么我在app_name、createTime字段设置联合索引，即可实现覆盖索引，无需全表扫描。适用于查询列较少的场景，查询列数过多的不推荐。

mysql> EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10;
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | index | NULL          | idx_app_name | 515     | NULL | 1377809 |   100.00 | Using index |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

解决方法二：优化offset

重点是想办法快速过滤掉前100w条数据。我们可以利用自增主键有序的条件，先查询出第1000001条数据的id值，再往后查10行；适用于主键id自增的场景。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | vio_basic_domain_info | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |      10 |   100.00 | Using where |
|  2 | SUBQUERY    | vio_basic_domain_info | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | Using index |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set, 1 warning (0.40 sec)

解决方法三：延迟关联

这里我们利用到了覆盖索引+延迟关联查询，相当于先只查询id列，利用覆盖索引快速查到该页的10条数据id，然后再把返回的10条id拿到表中通过主键索引二次查询。（表数据增速快的情况对该方法影响较小。）

mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+
| id | select_type | table                 | partitions | type   | possible_keys | key     | key_len | ref      | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2>            | NULL       | ALL    | NULL          | NULL    | NULL    | NULL     | 1000010 |   100.00 | NULL        |
|  1 | PRIMARY     | vio_basic_domain_info | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myNew.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | vio_basic_domain_info | NULL       | index  | NULL          | PRIMARY | 8       | NULL     | 1000010 |   100.00 | Using index |
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

优化建议

like 语句的索引失效问题，尽量在字段后面使用模糊查询。如 SELECT * FROM t WHERE username LIKE ‘陈%’
尽量避免使用 not in，会导致引擎走全表扫描。建议用 not exists 代替
- 不走索引 select * from t where name not in (‘提莫’,‘队长’);
- 走索引 select * from t as t1 where not exists (select * from t as t2 where name in (‘提莫’,‘队长’) and t1.id = t2.id);
不要在等号的左侧进行表达式、函数操作使用函数和计算，这将导致索引失效而进行全表扫描。
尽量避免在 where 子句中使用 or 来连接条件，用union代替
- SELECT * FROM t WHERE id = 1 OR id = 3
- SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
复合索引的最左前缀原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用
尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描，优化方式：可以给字段添加默认值0，对0值进行判断。如下：
索引不会包含有NULL值的列 ，只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的
隐式转换的影响, date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换导致索引失效而进行全表扫描
多表关联查询时，小表在前，大表在后。
使用FullText全文索引，用match against 检索
数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级

jialun0116

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
explain分析SQL执行计划

explain分析SQL执行计划慢查询配置慢查询日志具体参数解析优化案例 LIMIT分页解决方法一：构造覆盖索引解决方法二：优化offset解决方法三：延迟关联优化建议慢查询配置-- 查看是否开启慢查询show variables like '%slow_query_log%';+---------------------+-------------------------------------+| Variable_name | Value
复制链接

扫一扫