# Day07 EXPLAIN 执行计划
# 本节关键词
explain
# 命令EXPLAIN介绍
语句的执行计划 针对索引应用和优化器算法应用部分信息
- SQL层估算值，没有实际执行
# 或者 desc
explain format=json select * from test.t100w where k1 = 'zz';
explain format=tree select * from test.t100w where k1 = 'zz';
mysql> explain select * from test.t100w where k1 = 'zz';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.46 sec)
- id 从上之下，从大到小，越大优先级越高
- type 应用索引的类型
ALL : 全表扫描，没走索引树
index : 扫描全部索引树，如果 后面的key是PRI(聚簇索引) 效率就是全表扫描，效率低
range : 范围扫描索引树，> < >= <= in or，可
ref : 辅助索引 等值查询
eq_ref : a join b on a.id=b.id ==> 非驱动表的关联列式，主键或唯一键
const(system) : 主键等值查询
NULL : id=1000000，统计信息直接判断
- possible_keys : 可能有多个
- key 实际所用索引
- key_len 评估联合索引 idx(a, b, c) 所用的长度，可结合 format=json中的 used_key_parts
长度？
参考字符集类型 utf8 utf8mb4
- rows 预估扫描行
- filtered 索引层过滤的百分比，如果不是100%的话，说明需要在engine层过滤，也就是需要加载到内存中过滤
- extra 重点关注 排序时是否额外开销，或者是否有临时表，或者优化器算法
1. Using filesort 大的排序会导致CPU过高
explain select * from world.city where countrycode='CHN' order by population;
单列不起作用，建联合索引，去掉 Using filesort 选项
alter table world.city add index idx_countrycode_population(countrycode, population);
2. Using tmp join或子查询会导致这种情况 -> 子查询转换为join
3. 优化器算法选择
# 是否走索引示例
- 走全表扫描情况，应避免，需优化
-- 新增索引
alter table world.city add index idx_name(name);
# 没走索引的情况
-- name 未创建索引
desc select * from world.city where name = 'peking';
-- % 开头
desc select * from world.city where name like '%pek%';
-- 否定
desc select * from world.city where name != 'peking';
-- not in
desc select * from world.city where name not in('peking');
-- 统计信息失效、过旧
-- 查询结果集 25% 以上，优化器有可能走全表扫描
-- 隐式转换 库表中 num char(3)
where num = '10' --> ref
where num = 10 --> index 扫描全部索引树
- 联合索引规范
alter table t1 add index idx_a_b_c(a, b, c);
-- 走索引情况
select * from t1 where a=1 and b=1 and c='xxx'
select * from t1 where c='xxx' and b=1 and a=1 -- 新版本也可以，顺序无所谓
-- b数值，!= 转换为 range 查询
select * from t1 where a=1 and b!=1 and c='xxx'
select * from t1 where a=1 and b>1 and c='xxx'
-- 缺少 a 不走索引
select * from t1 where b=1 and c='xxx'
联合索引最左原则
1. 建索引时，重复值少的放到最左列
2. 查询条件中必须包含最左列条件
# 索引应用场景-数据库慢
- 数据库慢
1. 应急性的慢
show full processlist; --> 对外连接 --> 慢SQL --> explain SQL --> 优化索引、改写语句
2. 间隙性的慢
slowlog --> 慢SQL --> explain SQL --> 优化索引、改写语句
# 索引应用规范-运维开发规范
- 建立索引的原则（DBA运维规范）
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引
- 必须要有主键，最好数字自增列
- 经常做为where条件列、order by、group by、join on、distinct的条件(业务:产品功能+用户行为)
- 联合索引最左原则
- 列值长度较长的索引列，我们建议使用前缀索引
idx(a,b,c) a ab abc
- 降低索引条目，一方面不要创建没用索引,不常使用的索引清理
* sys.schema_unused_indexes
* sys.schema_redundant_indexes
- 索引维护要避开业务繁忙期，建议用pt-osc\gh-ost
- 大量数据导入的时候，先把索引禁用
- 不走索引的情况（开发规范）
- 没有查询条件，或者查询条件没有建立索引
- 查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了
- 索引本身失效，统计信息不真实（过旧） analyze table t1;
- 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
where id - 1 = 2;
- 隐式转换导致索引失效，这一点应当引起重视，也是开发中经常会犯的错误
telnum char(11)
select * from t1 where telnum = '110'; √
select * from t1 where telnum = 110; X
- <>，not in 不走索引（辅助索引）
- 单独的>、<、in 有可能走，也有可能不走，和结果集大小有关，尽量结合业务添加limit
- like "%_" 百分号在最前面不走
# MySQL 自优化能力
- AHI 自适应HASH索引
自动评估"热"的内存索引page，生成HASH索引表
帮助InnoDB快速读取索引页，加快索引读取的效果
相当与索引的索引
- Change Buffer
Change Buffer功能是临时缓冲辅助索引需要的数据更新，当我们需要查询新insert的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的
比如insert、update、delete数据
1. 对于聚簇索引会立即更新，新增时只是追加数据，索引树影响不大
2. 对于辅助索引，不是实时更新的
根据读写比例，一般2/8、3/7，如果写比列特别小，可以适当关闭Change Buffer功能
# 优化器算法
- index hits
select * from t1 use index(col1_index, col2_index)
where col1=1 and col2=2 and col3=3;
- index_condition_pushdown (ICP)