第四章 索引及执行计划（二）

1、上节回顾
1.1 information_schema.tables
INNODB_LOCKS 锁
INNODB_TRX 事务
INNODB_LOCK_WAITS 锁等待
INNODB_BUFFER_POOL_STATS 内存

1.2 B树查找算法

1.3 B树功能分类
聚集索引
辅助索引

1.4 辅助索引分类
单列
联合
唯一
前缀

2、执行计划分析
2.1 table

2.2 type: ALL index range ref eq_ref const NULL
ALL:
select * from t1;
select * from t1 where xxx where 条件无索引
select * from t1 where != not in like '%xaa%' 非主键
index:
select id from city;
range: > < >= <= like , in or
ref:
select * from city where countrycode='CHN';
eq_ref : 多表连接
const : 主键和唯一键的等值

2.3 possible_key: 可能会用到的索引

2.4 key: 真正选择了哪个索引

2.5 key_len 索引覆盖长度：数据类型的最大预留长度 *****（难点）
int最长保留长度就是4个字节
varchar(20) utf8mb4 最大预留长度为83， 其中1标识是否非空NOT NULL，还有2个表示开始和结束，char类型没有
1）能存20个任意字符
2）不管存储的是字符,数字,中文,1个字符最大预留长度是4个字节
3）对于中文,1个占4个字节
4）对于数字和字母,1个实际占用大小是1个字节
select length() from test;

Extra: Using filesort
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
order by
group by
distinct
union

3、联合索引应用细节 *****（难点）
在有联合索引执行计划查看的时候，一定是key_len覆盖长度越长越好
3.1 只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序
唯一值多的列放在最左侧，优化查询
abcd
acbd
adbc
acbd
等等
mysql> desc select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
mysql> desc select * from test where k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa';
原因: 优化器会自动做查询条件的排列

3.2 不连续部分条件，修改索引，因为索引会中断
cda ----> acd ---> a -----> idx(c,d,a)
dba ----> abd ---> ab ----> idx(d,b,a)

3.3 在where查询中如果出现> < >= <= like，联合索引会卡在不等值的查询条件
(1) mysql> desc select * from test where k1='aa' and k3='aaaa' and k4='中国你好' and k2>'中国';
(2) mysql> alter table test add index idx1(k1,k3,k4,k2);

3.4 多子句查询,应用联合索引
单列索引只能应用一个，按照子句的执行顺序建立联合索引
mysql> desc select * from test where k1='aa' order by k2;
mysql> alter table test add index idx3(k1,k2);
注意：索引的key_lens长度为k1的最大覆盖长度，而k2的覆盖长度则不包含在内，因为不需要再进行排序操作

4、explain(desc)使用场景（面试题）
你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
mysql出现性能问题,我总结有两种情况:
应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
（1）show processlist; 获取到导致数据库hang的语句
（2）explain 分析SQL的执行计划,有没有走索引,索引的类型情况
（3）建索引,改语句
一段时间慢(持续性的):
（1）记录慢日志slowlog,分析slowlog
（2）explain 分析SQL的执行计划,有没有走索引,索引的类型情况
（3）建索引,改语句

5、 索引应用规范
5.1 建立索引的原则（DBA运维规范）
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引

5.2 不走索引的情况（开发规范）
(1) 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2)对于辅助索引（大表，小表演示不出来） 查询结果集是原表中的大部分数据，应该是25％以上。
(3) 索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换（数字转字符串）导致索引失效.
(6) <> ，not in 不走索引（辅助索引）
(7) like "%aa" 百分号在最前面不走
(8) 联合索引