大厂面试题：聊聊索引设计的原则以及索引失效场景有哪些

在学习设计索引的原则之前需要了解到 Mysql针对主键索引会维护一个B+树，也称之为聚簇索引。针对非主键（一般都是建立的联合索引）会对索引字段依次排序，然后从第一个字段值开始比较，如果相同就针对下一个字段值进行比较，依次类推。
另外一点就是聚簇索引的B+树保存的是一行记录的所有信息，而非聚簇索引仅仅只会保存索引字段值和主键字段值。

接着我们可以回归正题首先看第一个问题

一、设计索引的原则是什么？

1.主键索引

首先索引中最常见的是主键索引，对于主键索引需要注意的就是设计主键索引时一定要是自增的，不建议使用UUID作为主键。
那为什么要用自增主键而不是UUID这种的来做主键呢？
这是因为UUID是无序的，mysql在维护聚簇索引的时候都是按照主键的顺序排序的，简单的说其实就是每个数据页的数据一定是按照主键由小到大排序的，而且数据与数据之间通过单向链表连接的，数据页之间通过双向链表连接，所以前一个数据页的最大的主键值一定是小于后一个数据页的最小的主键值的。
话不多说 看图

所以总结来看 如果主键是自增的，mysql只需要根据主键的目录其实就能很快地定位出数据应该插入到哪里，如果不是自增的那么就需要从头开始比较，然后找到合适的位置，然后将数据插进去，这样特别的影响效率，所以主键一定要设计成自增的。
另外一点就是唯一索引和主键索引比较类似，但是唯一索引不一定是自增的，索引维护索引的成本肯定是大于主键索引的，但是唯一索引的值是唯一的（可以有一个值为null ），因此可以通过唯一索引来确定一条记录，但是可能需要进行回表查询。

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

我们在建立索引的时候，要为那些经常作为查询条件的字段建立索引，这样能够提高整个表的查询速度。但是一般查询时不止一个条件，所以需要建立联合索引。
另外查询条件一般有like这种的模糊查询，如果有模糊查询最好遵守 最左前缀匹配原则。
最左前缀匹配原则：说白了就是你的sql一定要有字段跟这个索引从最左边开始匹配它的一个或者几个字段。举个栗子 比如建立一个索引（a,b,c）那么其实是相当于等价建立了a，ab，abc三个索引，但其实还是只建立这一个索引，只不过效果等价于多个索引。

3.尽量使用数据量小的字段建立索引。

比如说现在有两个字段，一个varchar(5), 一个varchar(100),那么这种情况下优先为varchar(5)建立索引，那为啥子呢？ —这是因为MYSQL在维护索引的时候其实会将字段值一起维护，那这样必然会导致索引会占用更多的空间。另外排序的时候也需要花费更多的时间去对比。
如果我就想用varchar xxxx(100)这个字段来建立索引，那怎么办？
好办，通过取部分数据

CREATE INDEX  tbl_address ON dual(xxx(20));

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

比如说我现在有个字段，里面就两选项 0否 1是，那这种就不太适合做索引，这样的字段主要的特点就是区分度不高，值出现的机率几乎相同，这样的话无论搜索多少次都可能得到一半数据。
因此对于MYSQL来说，它有个查询优化器，如果查询优化器发现某个字段在表中出现的频率很高，它一般会忽略索引，进行全表扫描。

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

尽量为ORDER BY 和 GROUP BY 后面的字段建立索引，因为建立索引的时候这些记录在B+树中都是排好序的，这样在查询的时候就不用再做一次排序了。
如果 GROUP BY 后面的字段没有排序，那么这个时候MySQL是需要先进行排序的，这样就会产生临时表，一个排好序的临时表，然后再在临时表中执行聚合操作，这样子当然效率很低了，如果 GROUP BY 后面的字段已经建立了索引，那么MySQL 就不需要再去排序，也就不会产生临时表

然而比较坑的是，如果 GROUP BY的列和 ORDER BY的列不一样，即使都有索引也会产生临时表

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

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

如果非要对函数字段加索引，那么在建立索引的时候可以连着函数一起创建
假设有一个字段叫age，并为其创建了索引，但是使用的时候是这样子的

SELECT * FROM student WHERE round(age) = 2;

7.不要建立太多的索引

因为MySQL维护索引是需要空间和耗费性能的，MySQL会为每个索引字段维护一颗B+树。所以索引过多，会增加mysql的负担。

二、索引失效的常见场景

1.使用 OR 关键字会导致索引失效

不过如果要想使用OR 又不想让索引失效，那就得需要为or条件中的每个列都建立索引

2.联合索引如果不遵循最左前缀原则，那么索引也将失效

3.使用模糊查询的时候以%开头也会导致索引失效

4.索引列如果使用了隐式转换也会导致索引失效

比如说一个varchar类型的字段 aaa
我们一般查询如下

SELECT * FROM student WHERE aaa='15'

但是如果你写成

SELECT * FROM student WHERE aaa=15

那这种情况是使用不到索引的，也就是aaa列情的索引是失效的。

如果字段基数小也可能会导致索引失效，具体在本文的上面部分已经详细解释了，也就是MySQL 查询优化器导致的