MySql优化- 执行计划解读与优化(二)
待阅
https://mp.weixin.qq.com/s/IN2mzyOXdVWE0NQJr1egcA
说明
解读执行计划l对于我们日常工作中慢sql的分析和调优有很大帮助,同时在解读的过程中也能知道如何规避慢sql
建议需要了解join匹配原理的知识:https://www.cnblogs.com/LQBlog/p/10711743.html
查看sql优化器优化后的sql
EXPLAIN EXTENDED select * from( select * from( select cn.`id` from `cpn_coupon` cn where cn.`create_timestamp` >='2019-12-27'))tab2 SHOW WARNINGS
mysql执行计划表结构
各个字段详解
测试表结构说明
sl_sales_bill_copy1 订单抬头表
sl_sales_bill_copy1订单行项目表
order_status 订单状态变动表
id
执行顺序 值越大的优先执行 如果相同则根据从上到下的顺序来确定 如果为null则最后执行
#查出订单状态存在1010的订单的所有行项目信息
EXPLAIN select * from sl_sales_bill_copy1 lb join sl_sales_bill_head_copy1 lh on lh.SALES_BILL_NO = lb.SALES_BILL_NO where lb.SALES_BILL_NO in(select ls.SALES_BILL_NO from order_status ls where ls.status_code=1010)
先执行id为2查询 将查询结果保存为临时表,再执行id为1的 从上到下,先将lb为驱动表关联查询lh得出结果 subquery2临时表 然后作为临时表去非驱动表ls查询数据
select_type
用途:查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
1、SIMPLE：简单的select查询，查询中不包含子查询或者union 2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary 3、SUBQUERY：被驱动的SELECT子查询（子查询位于FROM子句 where）
4、DEPENDENT SUBQUERY 子查询依赖外部查询,慎用 5、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里
6、MATERIALIZED 物化子查询,子查询来自视图 7、UNION：在UNION查询语句中的第二个和紧随其后的SELECT。 8、UNION RESULT：组中union结果的临时表 9、DEPENDENT SUBQUERY 子查询依赖外部查询(慎用)
10、DEPENDENT UNION 子查询中包含union
SIMPLE
explain select * from cpn_coupon where id=1
SUBQUERY
先通过子查询得出id 再根据id值去查询
EXPLAIN select * from cpn_coupon c where c.id =(select max(cn.coupon_id) from `cpn_coupon_code` cn where cn.`create_timestamp` >='2019-12-27')
DEPENDENT SUBQUERY
慎用子查询依赖外部查询,会先根据外部查询得出一个临时表 再根据临时表 去触发子查询 因为p表没有查询条件 每条数据都会触发一次o表查询 现在数据 566条 相当于触发了566 t2 select 数据量大就更夸张(就算t2走了索引页会导致性能问题) 建议改成select join group by 可以理解为p表查询结果为临时表。在for循环遍历每一条数据 查询o表
可以理解为
var items=select * from prm_page_promotion for(var item in items){ select * from prm_page_promotion o where o.parent_id=item.getId(); }
explain select * from prm_page_promotion p where exists(select p.id from prm_page_promotion o where p.id=o.parent_id)
DERIVED
子查询位于form处 先通过create_timestamp查询结果到临时表 再根据临时表id查询
EXPLAIN select * from( select cn.`id` from `cpn_coupon` cn where cn.`create_timestamp` >='2019-12-27')tab2 where tab2.id=1
MATERIALIZED
--创建视图 create view v1 as select cn.coupon_id from `cpn_coupon_code` cn where cn.`create_timestamp` >='2019-12-27' --使用视图为子查询 EXPLAIN select * from cpn_coupon c where c.id in(select v1.coupon_id from v1)
UNION/UNOIN_RESULT
cp2为union表 可以看出是先执行cpu2再执行cp2 primary为最外层,然后汇聚成UNION RESULT 结果
explain select * from cpn_coupon where id=578032073359495168 union all select * from cpn_coupon where id=580952931153494016
DEPENDENT UNION
explain select * from cpn_coupon cp where cp.id in(select cp1.id from cpn_coupon cp1 where cp1.id=578032073359495168 union all select cp2.id from cpn_coupon cp2 where cp2.id=580952931153494016)
cp2 cp1 查询结果为unioReuslt再通过cp表子查询union结果集
table
table 列表示 EXPLAIN 的单独行的唯一标识符。这个值可能是表名、表的别名或者一个未查询产生临时表的标识符，如派生表、子查询或集合。
当 FROM 子句中有子查询时，如果优化器采用的物化方式，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当使用 UNION 查询时，UNION RESULT 的 table 列的值为 <UNION1,2>，1和2表示参与 UNION 的 SELECT 的行 id。
具体可参考 上面:select_type unino和 DERIVED的执行计划
type
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref
system
const的特例平时无法重现 可以忽略
const
表示通过扫描索引 扫描一行就找到了数据 const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
eq_ref
表连接时 被驱动表关联字段查询为主键或者唯一索引查找时
lb.id为主键索引
如果where条件中查询非驱动表为非唯一索引或者主键索引则会降为ref
ref
被驱动表关联字段查询为非唯一索索引和主键索引的普通索引时
lh.SALES_BILL_NO为普通索引
range
表示范围查询 常见于between 和>, >=,<, <= 前提是字段有建立btree索引
count未建立索引执行计划
count建立索引后
index
与ALL类似 只是index全表扫描扫描的是索引页而不是数据行
因为我们id做了索引 所以只需要去索引页里面取出所有id数据就好了
ALL
全表扫描
index_merge
index_merge其实就是分别通过对两个独立的index进行过滤之后，将过滤之后的结果聚合在一起，然后在返回结果集
1.为什么会有index merge
我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。
MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。
2.对 index merge 的进一步优化
index merge使得我们可以使用到多个索引同时进行扫描，然后将结果进行合并。听起来好像是很好的功能，但是如果出现了 index intersect merge，那么一般同时也意味着我们的索引建立得不太合理，因为 index intersect merge 是可以通过建立 复合索引进行更一步优化的。
————————————————
版权声明：本文为CSDN博主「lazzZa」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_33095135/article/details/122188017
possible_keys
指出MySQL可能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key
实际使用的索引，如果为NULL，则没有使用索引,如果出现possible_keys有值但是 key为null 可能是在数据少量情况下 mysql优化器认为全表扫描比走索引快,所以放弃使用索引
如果想强制 MySQL使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。
key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的
1.字符串类型key_length计算规则
各个字符集长度 utf8mb4=4,utf8=3,gbk=2,latin1=1
key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列)
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
注意：该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
比如：varchar(50)，字符集为utf8 则实际占用的key_len长度是 3 * 50 + 2 = 152，如果该列允许存储NULL，则key_len长度是153。
2.数值类型key_length计算规则
tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节
3.时间类型
date：3字节 timestamp：4字节 datetime：8字节
索引最大长度是768字节，当字符串过长时，MySQL 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
举例1：
id为主键索引 为bigint 索引key_length=8
explain select cp1.id from cpn_coupon cp1 where cp1.id=578032073359495168
举例2:
coupon_name为varchar(64) 按照计算公式key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列) 4*64+1+2=259
explain select cp1.id from cpn_coupon cp1 where cp1.coupon_name='a'
ref
ref 列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：user.id）
rows
列是查询优化器估计要读取并检测的行数，注意这个不是结果集里的行数。
如果查询优化器使用全表扫描查询，rows 列代表预计的需要扫码的行数；如果查询优化器使用索引执行查询，rows 列代表预计扫描的索引记录行数。
Extra
Extra 列提供了一些额外信息。这一列在 MySQL中提供的信息有几十个，这里仅列举一些常见的重要值如下：
Using index
表示使用了覆盖索引,覆盖索引:表示索引包含了返回所有列 而不回表
Using where Using index
查询的列被索引覆盖，并且 WHERE 筛选条件是索引列之一 id为主键 name为普通索引
explain select cp1.id from cpn_coupon cp1 where cp1.coupon_name='a'
NULL
查询的列未被索引覆盖，并且 WHERE 筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过 回表 来查询，不是纯粹地用到了索引，也不是完全没用到索引。
id主键 introduction没有索引，需要根据回表获取到对应数据
explain select cp1.id,cp1.introduction from cpn_coupon cp1
Using where
Using where的作用只是提醒我们MySQL将用where子句来过滤结果集
Using index condition
查询的列不完全被索引覆盖 where条件为二级索引 需要根据二级索引存储的聚簇索引id 获得数据才能拿到introduction(回表)
id为主键 introduction没有索引 coupon_name有索引
explain select cp1.id,cp1.introduction from cpn_coupon cp1 where cp1.coupon_name='a'
Using temporary
表示mysql需要临时表转存数据 常见于 group by、DISTINCT、ORDER BY使用非索引字段 一般出现这种情况就需要考虑进行优化了，首先是想到用索引来优化。
表示使用了非索引字段排序
Using filesort
如果一个排序操作不能通过索引来完成，那这次排序操作就叫做filesort，这跟file没有任何关系。filesort应该叫做sort，而它的实现，就是大家熟悉的快排(一般由于 排序列没有建索引导致)
有时候存在Using filesort，也未必是什么大不了的：如
select * from t_talbe order by id；只是告诉你它使用了“all rows”。
什么是回表查询如何避免回表
首先看聚簇索引和非聚簇索引构成点击跳转
例子1:
id为聚簇索引 name为非聚簇索引 通过聚簇索引name查找可以找到id 无须回表查询效率高
select id,name from user where name='shenjian';
Extra：Using index。
例子2
id为聚簇索引 name为非聚簇索引,sex为非索引，sex需要根据聚簇索引获取到对应的数据行(回表操作) 如果将name和sex升级为联合索引则无须回表
select id,name,sex from user where name='shenjian';
Extra：Using index condition。
不要使用cont(*) 避免回表 使用 coun(索引列)
如何查找mysql中的慢sql
1.查看mysql是否开启mansql记录日志
show variables like 'slow_query_log';
2.慢sql记录时间
show variables like 'long_query_time';
3.设置记录mysql为打开状态
set global slow_query_log='ON';OFF为关闭
设置超过一秒的sql都将记录
set global long_query_time=1
设置记录文件
set global slow_query_log_file='/var/lib/mysql/test_1116.log';
查看记录文件
show variables like 'slow_query_log_file';
优化实践
数据表
# 重建 `staff` 表 DROP TABLE `staff`; CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `s_name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '花名', `s_no` INT(4) NOT NULL DEFAULT 0 COMMENT '工号', `work_age` int(11) NOT NULL DEFAULT '0' COMMENT '工龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `arrival_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', `remark` VARCHAR(500) DEFAULT NULL COMMENT '备注', # 允许 NULL PRIMARY KEY (`id`), # 主键 UNIQUE KEY idx_s_name (s_name), # 唯一索引 KEY idx_s_no (s_no), # 普通索引 KEY `idx_name_age_position` (`name`,`work_age`,`position`) USING BTREE # 联合索引 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; # 初始化 `staff` 表数据 INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('zhangsan','zs',10,2,'manager',NOW()); INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('lisi','ls',11,3,'dev',NOW()); INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('wangwu','ww',12,8,'dev',NOW()); INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('zhangliu','zl',110,5,'dev',NOW()); INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('xiaosun','xs',111,5,'dev',NOW()); INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('donggua','dg',200,3,'dev',NOW());
全值匹配
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan';
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 2;
EXPLAIN SELECT * FROM staff where name = 'zhangsan' AND work_age = 2 AND position = 'dev';
EXPLAIN SELECT * FROM staff where position = 'dev' AND name = 'zhangsan' AND work_age = 2;
这里我们将联合索引最左name排在后面 也能正常使用索引时因为mysql优化后会帮我们排在前面,在写的过程中 还是要根据联合索引顺序编写，避免二次优化
最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
跳过了name(全表扫描)
EXPLAIN SELECT * FROM staff WHERE work_age = 2 AND position ='dev';
跳过了name和work_age(全表扫描)
EXPLAIN SELECT * FROM staff WHERE position = 'dev';
索引列上避免做计算操作
EXPLAIN SELECT * FROM staff WHERE LEFT(name, 5) = 'zhang';
EXPLAIN SELECT * FROM staff WHERE LOWER(name) = 'zhangsan';
查询值上函数运算是没问题的
EXPLAIN SELECT * FROM staff WHERE name = LEFT('zhang',5);
其实很好理解比如你通过hashMap存储根据name快速找到对应对象,如果你要根据key做处理匹配 就只能遍历keys 做完处理再比较
但是如果你正式查询条件使用函数，你只是在get之前 通过函数转了一下查询值 不会影响索引
范围条件右边的列无法使用索引
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age > 2 AND position ='dev';
根据上面key_length计算规则 idx_name+age的索引长度 正好为78 索引position没有用到索引
只是从 name = 'zhangsan' AND work_age > 2 条件返回的结果集中，再过滤符合 position 字段条件的数据。
尽量使用覆盖索引
1覆盖索引：简单理解，只访问建了索引的列。减少使用 SELECT * 语句查询列。 避免回表
未回表查询
EXPLAIN SELECT name,work_age FROM staff WHERE name= 'zhangsan' AND work_age = 3;
回表查询
因为查询 返回了非索引字段 所以需要根据聚簇索引找到对应的数据行
EXPLAIN SELECT name,work_age,s_no FROM staff WHERE name= 'zhangsan' AND work_age = 3; EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 3;
范围条件查找能够命中索引
例子1：
若条件中范围列有普通索引和主键索引同时存在， 优先使用主键索引:
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.id > 2;
例子2：
EXPLAIN SELECT * FROM staff WHERE staff.name != 'zl' AND staff.s_no > 1 ;
可以看到s_no没有走 索引 因为数据量比较小,sql优化引擎认为全表扫描比索引快,因为会回表 需要查询2次
我们可以改为force index强制走索引
EXPLAIN SELECT * FROM staff force index(idx_s_no) WHERE staff.name != 'zl' AND staff.s_no > 1 ;
IS NOT NULL 无法使用索引
EXPLAIN select * from cpn_coupon c where c.coupon_name is null
EXPLAIN select * from cpn_coupon c where c.coupon_name is not null
模糊条件查询以通配符开头索引失效
EXPLAIN SELECT * from staff where name like '%zhang%';
EXPLAIN SELECT * from staff where name like '%zhang';
EXPLAIN SELECT * from staff where name like 'zhang%';
改为覆盖索引将走索引
EXPLAIN SELECT name,work_age FROM staff WHERE name LIKE '%zhang%';
避免隐式转换
未加单引号
EXPLAIN SELECT * FROM staff WHERE name = 1;
可以理解为
EXPLAIN SELECT * FROM staff WHERE CONVERT(name,int) = 1;
OR多数情况会失效
因为是or查询,所以格努work_age找索引 没有满足最左前缀匹配将全表扫描
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR work_age = 2;
优化为将走索引
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR (name='zhangsan' and work_age = 2);
使用覆盖查询将走索引
explain SELECT name,work_age FROM staff WHERE name='zhangsan' OR work_age = 2;
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR s_name='wangwu';
使用union改进
EXPLAIN SELECT * FROM staff WHERE name='zhangsan' union all SELECT * FROM staff WHERE s_name='wangwu';
id2 后面extra是因为 唯一索引如果查询不存在的值将不会走索引 参考:https://www.cnblogs.com/huahua035/p/10573930.html
注意所以再日常使用中不要使用 先查询判断是否存在 再插入 直接插入 让mysql抛出异常 并捕获异常比如用户注册,但是建立了唯一索引查询的时候就要特别注意 查询的地方
负向查询条件不能使用索引
负向查询条件包括：!=、<>、NOT IN、NOT EXISTS、NOT LIKE 等。
EXPLAIN SELECT * FROM staff WHERE id !=1 AND id != 2; EXPLAIN SELECT * FROM staff WHERE id NOT IN (1,2);
in则可以命中
EXPLAIN SELECT * FROM staff WHERE id IN (11,12);
排序对索引的影响
ORDER BY是经常用的语句，排序也遵循最左前缀列的原则。
EXPLAIN SELECT * FROM staff ORDER BY name,work_age;
EXPLAIN SELECT name,work_age FROM staff ORDER BY name,work_age;
覆盖索引可以命中索引
索引优化总结
1.更新非常频繁字段不宜建索引
因为字段更新台频繁，会导致B+树的频繁的变更，重建索引。所以这个过程是十分消耗数据库性能的。
2.区分度不大的字段不宜建索引
比如类似性别这类的字段，区分度不大，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外注意一点，返回数据的比例在 30% 之外的，优化器不会选择使用索引。
3.业务中有唯一特性的字段，建议建成唯一索引
业务中如果有唯一特性的字段，即使是多个字段的组合，也尽量都建成唯一索引。尽管唯一索引会影响插入效率，但是对于查询的速度提升是非常明显的。此外，还能够提供校验机制，如果没有唯一索引，高并发场景下，可能还会产生脏数据。
但是要小心 查询不存在的数据不走索引
4.多表关联时，要确保关联字段上非驱动表必须有索引
5.避免字符集不一致导致的索引无效参考：https://www.cnblogs.com/LQBlog/p/16807067.html#autoid-0-0-0
