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 “%_”：百分号在前面不走索引