说下MySQL的索引

一、索引的分类

可以按照四个角度来分类索引

1. 按数据结构：B+tree索引、Hash索引、Full-text索引（全文索引）、空间数据索引

2. 为什么要使用B+树作为索引

• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，相比存储即存索引又存记录的 B 树，B+树一个数据页中可以存放更多的索引，因此 B+ 树高度更低，查询底层节点的磁盘 I/O次数会更少。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除时不容易像B树那样频繁的需要调整树的结构，效率更高。
  B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

3. 按物理存储：聚簇索引（主键索引）、辅助索引（二级索引）

聚簇索引（主键索引）：根据数据的主键创建的索引，B+Tree非叶节点中存储主键作为索引，在叶节点中按主键顺序存储数据，并且叶节点间通过链表连接。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为聚簇索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值且值唯一的列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

辅助索引（二级索引）：辅助索引通常是对主键之外的字段建立的索引，其叶节点并不存储完整数据，而是存储主键值，通过辅助索引查到主键值后，再通过主键值到聚簇索引中查找对应的数据，这种需要二次查表的过程也称为回表。如果只需要叶子节点数据则不需要回表 直接返回，这称为索引覆盖。

4. 按字段特性：主键索引、唯一索引、普通索引、前缀索引

主键索引：主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值且值必须唯一。

唯一索引：索引列的值必须唯一，但是允许有空值。一张表可以有多个唯一索引

普通索引：普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引

5. 按字段个数：单列索引、联合索引

单列索引：建立在单列上的索引称为单列索引，比如主键索引

联合索引：通过将多个字段组合成一个索引，该索引就被称为联合索引。联合索引是按字段的顺序优先级进行构建和排序，也就是说先按第一个字段进行排序，第一个字段相同时才按第二个字段排序。

由于联合索引中每个字段的顺序都是建立在前一字段的顺序之上，也就是只有前一个字段值相同的情况下，后一字段的值在索引表中才存在顺序性。因此只有查询条件中为联合索引中左边的部分在查询时才有可能利用到联合索引，例如联合索引（a,b,c），查询条件中只有（a）、（a,b）或（a,b,c）的部分索引才有效，这称为最左匹配原则。

并且根据最左匹配原则向右匹配的过程中遇到范围查询（>、<、between、like）后就会停止，因为显然一个字段一定范围内的数据其下一个字段并不是有序的，无法使用索引，比如查询条件a=3 and b=4 and c>5 and d=6如果建立（a，b，c，d）顺序的索引，d是用不到索引的。对于范围查询，只有等于范围边界的部分，后续字段才能用到联合索引。

二、优化索引的方法

1. 前缀索引

在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小，增加一个索引页中存储的索引值。

2. 覆盖索引

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

3. 主键索引最好是自增的

这样插入数据时直接在末尾追加，避免分裂页移动数据的消耗。

4. 索引列最好设置为 NOT NULL

第一：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化。

第二：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表。

三、索引失效的六种情况

1. 左/左右模糊匹配

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

2. 使用函数

当我们在查询条件中对索引列使用函数，就会导致索引失效。

3. 使用表达式

当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。

4. 隐式转换

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

5. 联合索引非最左匹配

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

综上所示，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，前面我也用了四个例子说明了。

6. where or子句

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

四、什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

五、什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。