索引设计原则及索引失效的场景

索引设计原则

索引的设计要根据 WHERE 条件和 ORDER BY 还有 GROUP BY 后面的字段进行设计。

1. 主键索引

   • 主键一定要设置为自增的，不要用UUID作为主键；

   • MySQL在维护主键（聚簇）索引的时候都是按照主键的顺序排序的；

   • 每个数据页中的数据一定是按照主键从小到大排序的，而且，数据与数据之间是通过单向链表连接的，上一个数据页中的最大的主键的值一定是小于下一个数据页中的最小的主键的值，数据页和数据页之间是通过双向链表来维护的

   • 唯一索引和主键索引类似，但是唯一索引不一定是自增的，所以维护唯一索引的成本肯定是大于主键索引的

   • 唯一索引的值是唯一的（唯一索引可以有一个值为 NULL），可以更快的通过索引字段来确定一条记录，但是可能需要进行回表查询

2. 频繁查询的字段建立索引

   因为查询条件一般比较多，所以一般需要建立联合索引

   查询条件中一般会有like这样的模糊查询，最好遵守最左前缀查询原则。

3. 避免为大字段建立索引

   会占用太多存储空间，影响性能，int(4字节),bigint(8字节)

4. 选择区分度大的列作为索引

   • 区分度不高的字段不适合做索引，比如性别，无论搜索哪个值都有可能得到一半的数据
   • MySQL查询优化器：发现某个值出现在表的数据行中的百分比很高的时候，一般会忽略索引，进行全表扫描
   • 惯用的百分比界线是”30%”。匹配的数据量超过一定限制的时候查询器会放弃使用索引,导致索引失效

5. 为ORDER BY 和 GROUP BY 后面的字段建立索引

   • 将 Order By后面的字段建立索引，这样在查询的时候就不需要再去做一次排序了，因为建立索引之后在B+树中的记录都是排序好的

   • GROUP BY 和 ORDER BY 类似,因为在GROUP BY 的时候也要先根据 GROUP BY 后面的字段排序，然后在执行聚合操作

   • 如果 GROUP BY 后面的字段没有排序，那么这个时候MySQL是需要先进行排序的，这样就会产生临时表，一个排好序的临时表，然后再在临时表中执行聚合操作，这样效率会很低，如果 GROUP BY 后面的字段已经建立了索引，那么MySQL 就不需要再去排序，也就不会产生临时表

   • 如果 GROUP BY的列和 ORDER BY的列不一样，即使都有索引也会产生临时表

     1. 如果GROUP BY 的列没有索引,产生临时表.
     2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表. 
     3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表. 
     4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表. 
     5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表. 
     6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
     7. GROUP BY 和 ORDER BY 的列一样且是主键，但SELECT 列含有除GROUP BY列之外的列，也会产生临时表 
     

6. 不要在条件中使用函数

   建立索引的字段使用函数，会使索引失效，MySQL为索引维护的B+树就是基于该字段原始数据的，如果正在使用过程中加了函数，MySQL就不会认为这个是原来的字段，那肯定不会走索引了

7. 不要建立太多索引

   MySQL维护索引是需要空间和耗费性能的，MySQL会为每个索引字段维护一颗B+树。

   所以如果索引过多，这无疑是增加了MySQL的负担。

8. 频繁增删改的字段不要建立索引

   字段的变化MySQL是需要重新维护索引的

   如果字段频繁修改，就会频繁重建索引，必然影响MySQL性能

   索引失效的场景

   1. 使用 OR 关键字会导致索引失效，不过如果要想使用OR 又不想让索引失效，那就得需要为or条件中的每个列都建立索引。这很显然是和上面的不要建立太多的索引相违背。
   2. 联合索引如果不遵循最左前缀原则，那么索引也将失效
   3. 使用模糊查询的时候以%开头也会导致索引失效
   4. 索引列如果使用了隐式转换也会导致索引失效
   5. 如果字段基数小也可能会导致索引失效，也就是MySQL 查询优化器导致的