mysql学习-9.如何正确的使用索引

1.索引的规则

1.1.等值匹配

假设索引(A) select * from table where A=xxx就是走索引的

1.2.最左侧列匹配

假设索引(A,B) select * from table where A=xxx and B=xxx就是走索引的

而缺少A select * from table where B=xxx就是不走索引的,因为在B+树中是从A字段最先判断的

如果想要走索引那么可以 select * from table where A in (穷举A的值) and B=xxx

1.3.最左前缀匹配原则

假设索引(A,B) select * from table where A like 'x%',这种前缀模糊匹配也是可以走索引的

如果是like '%x' 是没法走索引的

1.4.范围查找规则

假设索引(A,B) select * from table where A>'1' and A < '1000'

索引页是排序好的双向链表所以可以通过索引页找到范围内的数据页,然后返回

1.5.等值匹配+范围匹配的规则

这个规则是等值匹配和范围匹配的组合使用

select * from table where A='1' and B>'1' and B<'1000'

但是范围后面后面的查询条件是不能走索引了的

2.order by和group by语句使用索引

假设索引(A,B,C)

在order by的时候按照顺序来就行 order by A,B,C

group by也是一样

要求顺序不能打乱,order by的时候所有字段都是降序或者升序排序才行,不能某些字段是升序,某些字段是降序

3.覆盖索引查询

假设索引(A,B,C) ,查询的参数只有A,B,C三个字段,那么就是覆盖索引查询了

select A,B,C table where A=xxx and B=xxx and C=xxx .直接在索引页中查询就可以了,不用回表去到聚簇索引以及去查数据页了,这种就是覆盖索引查询的,性能比较快

4.怎样设计索引

4.1.在where条件后面 在order by后面 在group by后面 尽量满足最左匹配的原则

4.2.尽量设置的字段值比较多种可能的,才会比较好发挥二分法的好处,对于特别长值的字段,可以采取截取字段的前一部分来做为索引,也叫做前缀索引

4.3.在使用索引时,尽量不让字段有函数或者计算,另外主键一定是自增的,有序的.这样子在插入的时候,不会引起频繁的页分裂

4.4.建联合索引的时候,尽量让做范围查询的字段放在索引的最后一位,因为范围查询后面的不会走索引了

4.5.遇到一些不可避免的范围查询,可以转换为字段 例如 查询7天内的数据 可以转换为 is_in_7_day这种字段,然后值为0和1分别表示是否在7天内,然后加到索引中