MySQL索引知识结构

前言

学习MySQL的知识，学习好索引是非常重要的，索引分类、索引如何正确添加、索引失效的场景、底层数据结构等问题是面试中必问的，就这些内容我们一起学习巩固下。

索引是什么

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容，是存储引擎用于快速找到记录的一种数据结构，索引和数据是位于存储引擎中的，比如InnoDB。

索引分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。 按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。 按字段特性分类可分为：主键索引、普通索引、前缀索引。 按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

我们来看看各类索引的特点和区别

数据结构分类

按数据结构分类有 B+tree索引、Hash索引、Full-text索引，而不同的存储引擎支持不同的索引类型，我们拿InnoDB和MyISAM来看看。

我们要知道的是InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型，后面基本都是基于InnoDB引擎和B+tree去讲。

为什么说看B+树更适合做索引，它有以下这些特性：

1. 叶子节点才存放数据，非叶子节点只存放索引
2. 每个节点里的数据是按主键顺序存放
3. 每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表

B+Tree 如图所示：

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，为什么？

我们从之前的分享中知道（Innodb行记录文章链接）InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1000个键值，注意指的的非叶子节点。

我们再看叶子节点，这里假设一行记录的数据大小为 1KB（页大小是16KB），那么高度为3的B+Tree可以存放 1000 * 1000 * 16 = 16000000条记录。

可以看出一个高度为3的B+tree 就能存千万条记录， 所以B+Tree最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

物理存储分类

MySQL索引按叶子节点存储（物理存储）的是否为完整表数据分为：聚簇索引、二级索引（辅助索引）

• 聚簇索引：叶子节点存放的是一行完整的用户记录，叶子节点间按主键id递增
• 二级索引：子节点存放的是主键值，而不是实际数据

其实上图中画的B+树就是聚簇索引的B+树，这里我们再看二级索引的B+树，如下图：

通过和上图聚簇索引的B+树对比，我们可以清楚的看到，聚簇索引的叶子节点存放的是数据data，而二级索引叶子节点存放的是主键ID值。

什么是回表、覆盖索引？

回表：如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，再通过检索主键索引查询到数据的过程。

覆盖索引：在查询时使用了二级索引，如果查询的列在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引

字段特性分类

MySQL索引按字段特性分类可分为：主键索引、普通索引、前缀索引。

主键索引：建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

唯一索引：建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

普通索引：建立在普通字段上的索引被称为普通索引。

前缀索引：引前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

字段个数分类

MySQL索引按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引），从字面上就知道单列索引是单独一个列上建立的索引，而联合索引是建立在多列上的索引。

如何更好创建和使用索引

我们知道索引在时间和空间上都是有代价的，所以了解如何更好的创建和使用索引是我们使用好索引的前提，一般会考虑以下因素：

1. 只为用于搜索、排序、分组的列创建索引
2. 索引列中不重复值得个数在总记录条数中的占比很大时，才为列建立索引
3. 索引列的类型尽量小
4. 前缀索引、覆盖索引
5. 主键索引最好是自增的
6. 索引最好设置为not null
7. 冗余和重复索引

1：只为用于搜索、排序、分组的列创建索引

我们只为出现在where子句中的列、order by或group by子句中的列、连接子句中的连接列创建索引，仅出现在查询列表中的列就没有必要建立索引。

2：索引列中不重复值得个数

某个列创建索引时，如果该列中不重复值的个数比例越低，则说明该列包含过多重复值，那么会进行过多的回表操作。

3：索引列的类型尽量小

数据类型越小，索引占用的存储空间就越少，在一个数据页就能存放更多的记录，磁盘IO的性能损耗也就越小。

4：前缀索引、覆盖索引

前缀索引说白了就是对文本的前几个字符建立索引，这样建立起来的索引更小，所以查询更快，占用的空间也更小，这里要注意两点：

• order by 就无法使用前缀索引；
• 无法把前缀索引用作覆盖索引；

一个索引包含所有需要查询的字段的值，就称为覆盖索引，这样能直接从二级索引上查到记录，而不需要再通过聚簇去查，避免了回表带来的性能损耗。

5：主键索引最好是自增的

InnoDB 创建主键索引默认为聚簇索引，数据存储在叶子节点上，叶子节点中的记录都是按照主键值从小到大的顺序排序的，如果主键值是随机的，插入新数据时可能插入到某个数据页中间位置，导致数据移动造成页分裂问题，从而影响查询效率，具体我们在后面索引优化的时候举例。

6：索引最好设置为not null

索引列设置为not null主要有两个原因：

1. NULL 值是一个没意义的值，会占用物理空间，如果表中存在允许为null，则至少占用1字节空间
2. 索引列存在 NULL时会使索引、索引统计和值比较都更复杂

7：冗余和重复索引

我们经常能在看到一张数据表中，同样的字段又是联合索引，又是二级索引， 比如 col1、col2列是一个联合索引， 就没必要再对col1列再建一个普通索引，除了增加维护成本，对查询没有好处，这种重复索引应该避免。

索引优化的方法

通过上面关于如何创建和使用索引的内容中，我们也能总结出就如何进行索引优化提供思路，很多使用的方式其实就是索引优化的手段，主要有以下手段：

• 前缀索引优化
• 覆盖索引优化
• 主键索引最好是自增的
• 索引最好设置为NOT NULL
• 防止索引失效

因为我们上面【如何更好创建和使用索引】一节中也对优化手段的描述的比较清晰，这里再对【主键索引最好是自增的】和【防止索引失效】进行细讲。

主键索引最好是自增的

一般情况下，MySQL推荐使用自增ID，那么是什么原因呢？

在MySQL的 InnoDB存储引擎中，主键索引是聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，因为每次插入一条新记录，都是追加操作，不需要重新移动数据。

如果使用非自增主键，也就是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降，查询效率也会影像。

我看举个栗子，看看什么是页分裂 (盗个网图)

如果是非自增主键，那么可能出现如下情况，数据页中的记录主键id是随机的，如下图：

随着继续写入数据，写入数据的id并不一定比前一个数据页中记录的id大，此时就会触发页分裂。页分裂的目的就是保证：后一个数据页中的所有行主键值比前一个数据页中主键值大，这个时候数据页如下图：

UUID和自增ID怎么选择

主键一般是用自增ID还是UUID呢，怎么选择？

UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的

优点：

1. 唯一标识，不用考虑重复问题，在数据拆分、合并时也能达到全局的唯一性
2. 以在应用层生成，提高数据库的吞吐能力

缺点：

UUID的缺点就是使用主键自增id优点的反面，UUID占用空间较大，建立的索引越多，造成的影响越大，会发生随机IO，影响插入速度，并且会造成硬盘的使用率较低

那如何解决呢？

解决办法：1：可以把UUID作为逻辑主键，物理主键依然使用自增ID。 2：改为有序的数字主键生成策略就可以，如美团Leaf/推特的分布式自增ID算法SnowFlake （ ☝ᖗ乛◡乛ᖘ☝ ）

防止索引失效

创建了索引我们就一定能用上吗？答案是否定的，我们在查询的时候应该要知道哪些情况会让索引失效，这样写出来的SQL才能更好的使用上创建的索引，常见索引失效有这些情况：

• 以%开头的Like模糊查询
• 对索引列做了函数，表达式计算，类型转换操作
• 联合索引非最左匹配原则
• 索引字段是字符串，但查询时不加单引号
• 尽量使用覆盖索引，避免select *
• WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效

IS NULL ， IS NOT NULL 一定会索引失效吗？

这里我们也提了个问题，先给个答案就是，不一定，要看情况，后续具体分析我们后面跟失效场景的举例一起来解析下！

Explain 查看执行计划

上面都是讲失效场景，但是分析具体一条查询语句是否真实失效，还需要借助Explain查看执行计划，它可以模拟优化器执行SQL查询语句，让你知道你的SQL语句是怎么处理的。

我们在查询SQL前加explain，就可以看到如下执行计划分析：

explain 执行计划字段意思

type列就表明了这个访问方法是何种类型，是较为重要的一个指标，常见扫描类型的执行效率从低到高的顺序和出现的场景：

• all（全表扫描）
• index（全索引扫描）
• range（索引范围扫描）在 where 子句中使用 < 、>、in、between 等，指定了范围
• ref（非唯一索引扫描） 普通的二级索引列与常量进行等值匹配时
• eq_ref（唯一索引扫描） 使用了主键或唯一索引
• const（结果只有一条的主键或唯一索引扫描） 使用了主键或者唯一索引与常量值进行比较

因此应该避免全表扫描和全索引扫描

关于MySQL索引的基础知识总结就先到这里，后面将会继续分享一些MySQL的其他知识点，并且在某些内容上深入下去！