MySQL--联合索引应用细节应用规范
目录
一、索引覆盖
1.完全覆盖
2.部分覆盖
3.不覆盖索引-where条件不包含联合索引的最左则不覆盖
二、MySQL8.0在索引中的新特性
1.不可见索引
2.倒序索引
三、索引自优化--索引的索引
四、Change Buffer
五、优化器算法
1.查询优化器算法
2.设置算法
3.索引下推 ICP
4.MRR:Multi Range Read
六、索引应用规范
1.建立索引的原则
2.不走索引的情况
一、索引覆盖
以idx（a,b,c）为例
1.完全覆盖
where a= and b= and c=
where b= and c= and a=
where a= and b in() and c=
where a= and b= order by c=
where a= and b= and c>/<
2.部分覆盖
where a= and b>/< and c=
where a= and b=
where a=
where a= and c=
3.不覆盖索引-where条件不包含联合索引的最左则不覆盖
where b= and c=
where b=
where c=
二、MySQL8.0在索引中的新特性
1.不可见索引
建索引难，删索引易，为了避免轻易删掉索引，可以设为不可见
alter table city alter index idx_nn invisible;
2.倒序索引
where a order by b，c desc-->b为正排，c为倒排
这种情况在建立索引时可以---->idx(a,b,c desc)
三、索引自优化--索引的索引
AHI：自适应的HASH索引，根据缓冲区中索引页的热度，自动生成HASH索引表，快速锁定热点索引页在内存中的位置
四、Change Buffer
存储辅助索引的变更
将来需要用到的时候，自动在内存中进行merge合并，得到id值是目的
以前叫insert buffer，因为只对insert有效，现在对insert update delete都有效
五、优化器算法
1.查询优化器算法
select @@optimizer_switch;
2.设置算法
set global optimizer_switch='on/off';
3.索引下推 ICP
例如：where a= and b> and c=，正常情况下这种联合索引被b拦截后不会走到c的索引，但开启索引下推后可以对c进行索引，这是由于：
在mysql中，索引下推允许在存储引擎层过滤索引中的记录，而不是在server层进行，如果查询包含多个条件并且索引不是很精确时，索引下推可以在存储引擎层进行更多的过滤工作，从而减少不必要的IO.
如何开启：set global optimizer_swith='index_condition_pushdown=on';
4.MRR:Multi Range Read
辅助索引条件查询时，先扫描辅助索引，获得ID值，放在read_rnd_buffer中，由MRR进行排序后，回表查询
六、索引应用规范
1.建立索引的原则
1）必须有主键，主键选择业务无关的列
2）经常作为where条件列的要做索引，以及order by，group by，join on，distinct
3）最好使用唯一值多的列作为索引列，如果索引列重复值较多，可以考虑使用联合索引
4）列值长度较长的索引列，建议使用前缀索引 left(name,19) -->取name前19位
5）降低索引条目，不创建没有用的索引，不常使用的索引要清理
6）维护索引要避开业务繁忙期，用pt-osc
7）使用联合索引最左原则
2.不走索引的情况
1）没有查询条件，或者查询条件没建索引
select * from t1；
select * from t1 where 1=1；
2）查询结果集是原表中大部分数据，15%-25%以上，查询的结果集如果超过了总行数25%，优化器就觉得没必要走索引了
3）索引本身失效，统计信息不真实（过旧），对于表内容变化比较频繁的情况下，有可能会出现索引失效，一般是删除重建，如果有一条select语句平时查询很快，突然有一天很慢，应该是索引失效，统计信息不真实、
4）查询条件使用函数在索引列上，或者对索引列进行运算，+ - * / !。算数运算、函数运算、子查询，都不走索引
5）隐式转换导致索引失效，应引起重视，也是开发中常会犯的错误
select * from t1 where num='110' ——>走索引
select * from t1 where num=110 ——>不走索引
6）like “%_”：百分号在前面不走索引