1、索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构，其设计思想是以空间换时间。

2、索引的分类

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

3、索引数据结构

B-Tree结构

• 叶节点具有相同的深度，叶节点的指针为空

• 所有索引元素不重复

• 节点中的数据索引从左到右递增排列

B+Tree结构（(B-Tree变种)）

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

• 叶子节点包含所有索引字段

• 叶子节点用指针连接，提高区间访问的性能

hash结构

• 对索引的key进行一次hash计算就可以定位出数据存储的位置

• 很多时候Hash索引要比B+ 树索引更高效

• 仅能满足 “=”，“IN”，不支持范围查询

• hash冲突问题

4、存储引擎

MyISAM存储引擎索引实现

MyISAM索引文件和数据文件是分离的(非聚集)

InnoDB存储引擎索引实现

• 表数据文件本身就是按B+Tree组织的一个索引结构文件

• 聚集索引-叶节点包含了完整的数据记录

InnoDB的索引和MyISAM的索引有什么区别？

首先InnoDB和MyISAM都是使用的B+树实现的，但是InnoDB使用的是聚簇索引而MyISAM使用的是非聚簇索引。

聚簇索引：

根据主键创建一颗B+树，叶子节点则存放的是数据行记录，也可以把叶子结点称为数据页。通俗点来说就是把数据和索引存在同一个块，找到了索引也就找到了数据。

• 因为叶子结点将索引和数据放在一起，就决定了聚簇索引的唯一性，一张表里面只能有一个聚簇索引。

• InnoDB引擎默认将主键设置为聚簇索引，但如果没有设置主键，那么InnoDB将会选择非空的唯一索引作为代替，如果没有这样的索引，InnoDB将会定一个隐式主键作为聚簇索引。

• 因为聚簇索引特殊的物理结构所决定，叶子结点将索引和数据存放在一起，在获取数据的速度上是比非聚簇索引快的。

• 聚簇索引数据的存储是有序的，在进行排序查找和范围查找的速度也是非常快的。

• 也正因为有序性，在数据插入时按照主键的顺序插入是最快的，否则就会出现页分裂等问题，严重影响性能。对于InnoDB我们一般采用自增作为主键ID。

• 第二个问题主键最好不要进行更新，修改主键的代价非常大，为了保持有序性会导致更新的行移动，一般来说我们通常设置为主键不可更新。

非聚簇索引：

将索引和数据分开存储，那么在访问数据的时候就需要2次查找。

但是和InnoDB的非聚簇部分还是有所区别。InnoDB是需要查找2次树，先查找辅助索引树，再查找聚簇索引树（这个过程也叫回表）。而MyISAM的主键索引叶子结点的存储的部分还是有所区别。InnoDB中存储的是索引和聚簇索引ID，但是MyISAM中存储的是索引和数据行的地址，只要定位就可以获取到。

5、索引的优缺点

数据是存储在磁盘上的，操作系统读取磁盘的最小单位是块，如果没有索引，会加载所有的数据到内存，依次进行检索，加载的总数据会很多，磁盘IO多。

优点

• 索引能够提高数据检索的效率，降低数据库的IO成本。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，创建唯一索引

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

• 加速两个表之间的连接，一般是在外键上创建索引

缺点

• 需要占用物理空间,建立的索引越多需要的空间越大

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

• 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

6、索引失效

如果表中有字段为NULL 索引是否会失效？

首先讲答案不一定。即使我们使用is null 或者is not null 它其实都是会走索引的。那为什么会有这样的言论呢？这里首先就得来讲讲NULL值是怎么在记录中存储的，又是怎么在B+树中存储的呢。

那么在InnoDB中分为聚簇索引和非聚簇索引两种，聚簇索引本身是不允许记录为空的，所以可以不不用考虑，那么就剩下非聚簇索引也就是我们的辅助索引。

那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引，那到底什么时候索引，什么时候采用全表扫描呢？

首先我们得知道两个东西，第一个在InnoDB引擎是如何存储NULL值的，第二个问题是索引是如何存储NULL值的，这样我们才能从根上理解NULL在什么场景走索引，在什么场景不走索引。

1、在InnoDB引擎是如何存储NULL值的？

InnoDB引擎通过使用一个特殊的值来表示null，这个值通常被称为"null bitmap"。null bitmap是一个二进制位序列，用来标记表中每一个列是否为null。当null bitmap中对应的位为1时，表示对应的列为null；当null bitmap中对应的位为0时，表示对应的列不为null。在实际存储时，InnoDB引擎会将null bitmap作为行记录的一部分，存储在行记录的开头，这样可以在读取行记录时快速判断每个列是否为null。

从头开始说理解起来会比较容易，理解了独占表空间文件就更容易理解行格式了，接着往下看：

当我们创建表的时候默认会创建一个*.idb 文件，这个文件又称为独占表空间文件，它是由段、区、页、行组成。InnoDB存储引擎独占表空间大致如下图；

Segment(表空间) 是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

• 数据段 存放 B + 树的叶子节点的区的集合

• 索引段 存放 B + 树的非叶子节点的区的集合

• 回滚段 存放的是回滚数据的区的集合， MVCC就是利用了回滚段实现了多版本查询数据

Extent(区) 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了 。

（我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。）

Page(页) 记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的，数据页的结构这里我就不讲细说了，总之知道表中的记录存储在「数据页」里面就行。

Row(行) 数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

重点来了！！！

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

• Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了，那就不展开详讲了。

• MySQL 5.0 之后引入了 Compact 行记录存储方式，由于 Redundant 不是一种紧凑的行格式，而采用更为紧凑的Compact ，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。

• Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

那么我们来看看Compact里面长什么样，先混个脸熟。

这里简单介绍一下，Compact行格式其他内容后面单独出一个章节介绍。

• NULL值列表（本问题介绍重点）

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

当然NULL 值列表也不是必须的。当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以节省 1 字节的空间（NULL 值列表占用 1 字节空间）。

「NULL 值列表」的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

2、索引是如何存储NULL值的？

我们知道InnoDB引擎中按照物理存储的不同分为聚簇索引和非聚簇索引，聚簇索引也就是主键索引，那么是不允许为空的。那就不再我们本问题的讨论范围，我们重点来看看非聚簇索引，非聚簇索引是允许值为空的。

在InnoDB中非聚簇索引是通过B+树的方式进行存储的。

从图中可以看出，对于s1表的二级索引idx_key1来说，值为NULL的二级索引记录都被放在了B+树的最左边，这是因为设计InnoDB的大叔有这样的规定：

We define the SQL null to be the smallest possible value of a field.

也就是说他们把SQL中的NULL值认为是列中最小的值。在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，也就是本例中id值为521的那条记录之后，就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为NULL。

3、我们了解了上面的两个问题之后，我们就可以来看看，使不使用索引的依据是什么了

实际上来说我们用is null is not null ≠ 这些条件都是能走索引的，那什么时候走索引什么时候走全表扫描呢？

总结起来就是两个字：成本！！！

如何去度量成本计算使用某个索引执行查询的成本就非常复杂了，展开讲这个话题就停不下来了，后面考虑单独列一个篇幅去讲。

这里总结性讲讲：第一个，读取二级索引记录的成本，第二，将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录操作所付出的成本。

要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦，我们就不展开说了），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1，而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。

反正不论采用index dive还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。

理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。

大家可以看到，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。大家以后也多多辟谣吧，没那么复杂，只是一个成本而已。

为什么LIKE以%开头索引会失效？

首先看看B+树是如何查找数据的：

查找数据时，MySQL会从根节点开始，按照从左到右的顺序比较查询条件和节点中的键值。如果查询条件小于节点中的键值，则跳到该节点的左子节点继续查找；如果查询条件大于节点中的键值，则跳到该节点的右子节点继续查找；如果查询条件等于节点中的键值，则继续查找该节点的下一个节点。

比如说我有下面这条SQL：

select * from `user` where nickname like '%冥';

如果数据库中存在南冥 北冥 西冥 东冥 ，那么在B+树中搜索的效率和全表扫描还有什么区别呢？

我走聚簇索引全表扫描还不用回表。

最后在扩展讲一个点，其实不一定会导致索引失效。举个例子：

create table `user`(
  id int primary key auto_increment,
  name varchar(20),
  index idx_name(name),
);

// 那么这种情况是会走索引的。
select id,name from `user` where name like '%冥';

为什么说上面的例子会走索引呢？

首先我们需要查询的id name 这两个字段是不是都在我们的辅助索引中，叶子节点是不是存的索引值和主键值，所以我们只要查辅助索引就可以直接拿到我们的需要的结果了，那么这个叫做索引覆盖。我们观察执行计划会发现它的查询级别是index ，其实也是全表遍历了辅助索引。

第二个问题来了，那为什么就要走辅助索引而不是走全表扫描呢？

因为辅助索引中记录的东西比主键索引少了很多，只有索引值和主键值，但是主键索引中就包含了，其他值、事物ID、MVCC的回流指针等等。再加上索引覆盖不用回表，优化器就认为直接遍历辅助索引的效率高于主键索引。

7、索引下推

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。

在讲这个技术之前你得对mysql架构有一个简单的认识，见下图

• MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。

• MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。

• MySQL 索引扫描：根据指定索引过滤条件，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。

• MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

• 使用索引下推实现

索引下推的使用条件

• ICP目标是减少全行记录读取，从而减少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

• where 条件中是用 and 而非 or 的时候。

• ICP适用于分区表。

• ICP不支持基于虚拟列上建立的索引，比如说函数索引

• ICP不支持引用子查询作为条件。

• ICP不支持存储函数作为条件，因为存储引擎无法调用存储函数。

索引下推相关语句

# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

8、唯一索引

和 "普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。 可以是单列唯一索引，也可以是联合唯一索引。

• 最大的所用就是确保写入数据库的数据是唯一值。

唯一索引是否会影响性能呢？

我们通过和普通索引来做一个对比，有查询和插入两个场景。

首先第一个数据查询，一般情况下来说索引是通过B+树从根节点开始层序遍历到叶子结点，数据页内部通过二分搜索。

• 普通索引 查到满足条件的第一条记录，继续查找下一条记录，直到找到不满足条件的记录

• 唯一索引 查到第一个满足条件的记录，就停止搜索。

InnoDB 它是以数据页为单位进行读写的，我们读一条记录，并不是从磁盘加载一条记录，而是以页为单位整体读到内存里面来的。

普通索引比唯一索引就多了一次查找和判断下一条记录的操作，也就是一次指针寻找数据和一次计算。当然还有一种特殊情况，读取到的这条数据正好是数据页的最后一条，但是这种概率也是非常低，几乎可以忽略不计。

整体看下来看上去性能差距并不大对吧。

来看第二个更新的性能，我们按照上面图上的例子在2和6之间插入一个3。

在内存中

• 普通索引 找到2和6之间的位置 →插入值→ 结束

• 唯一索引 找到2和6之间的位置 →当判断有没有冲突→ 插入值→ 结束

不在内存中

• 普通索引 将更新记录在change buffer → 结束

• 唯一索引 将数据页读入内存→当判断到没有冲突→插入值→结束

数据读取到内存涉及了随机IO访问，这是在数据库里面成本最高的操作之一，而change buffer 就可以减少这种随机磁盘访问，所以性能提示比较明显。所以在这一块来说，如果两者在业务场景下都能满足时可以优先考虑使用普通索引。

9、组合/联合/复合 索引

按照字段个数来分，分为单列索引和组合索引

• 单列索引 一个索引只包含了一个列，一个表里面可以有多个单列索引，但是这不叫组合索引。

• 组合索引（联合索引 & 复合索引）一个索引包含多个列。

看上去感觉这组合索引并没有太大作用是吧，我一个列已经有一个索引了，我还要这组合索引干嘛？

• 高效率 如果说只有单列索引，那就会涉及多次二级索引树查找，再加上回表，性能相对于联合索引来说是比较低的。

• 减少开销 我们要记得创建索引是存在空间开销的，对于大数据量的表，使用联合索引会降低空间开销。

• 索引覆盖 如果组合索引索引值已经满足了我们的查询条件，那么就不会进行回表，直接返回。

最左前缀

简单理解就是只会从最左边开始组合，组合索引的第一个字段必须出现在查询组句中，还不能跳跃，只有这样才能让索引生效，比如说我查询条件里面有组合索引里面的第二个字段，那么也是不会走组合索引的。举个例子

// 假设给username，age创建了组合索引

// 这两种情况是会走索引的
select username,age from user where username = '张三' and age = 18;
select * from user where username = '张三';

// 这种是不会走索引的
select * from user where age = 18;
select * from user where city = '北京' and age = 18;

组合索引创建时字段顺序不一样使用效果一样吗？

// 特殊情况，这种也是会走索引的，虽然我的age在前面，username在后面。
// 刚刚不是手最左前缀匹配吗，为什么放到第二位也可以呢？
// 虽说顺序不一致，但是在SQL执行过程中，根据查询条件命中索引，
// 无论我username在不在前面，都会按照username去进行索引查找。
select * from user where age = 18 and username = '张三';

关于最左前缀的补充

MySQL一定是遵循最左前缀匹配的，这句话在mysql8以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。

索引跳跃扫描（Index Skip Scan）

参考：https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan

官网示例

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

虽然我们的SQL中，没有遵循最左前缀原则，只使用了f2作为查询条件，但是经过MySQL 8.0的优化以后，还是通过索引跳跃扫描的方式用到了索引了。

索引跳跃扫描优化原理

mysql8.013后通过优化器帮我们加了联合索引，SQL执行过程如下：

1. 获取 f1 字段第一个唯一值，也就是 f1 = 1

2. 构造 f1 = 1 and f2 > 40，进行范围查询

3. 获取 f1 字段第二个唯一值，也就是 f1 = 2

4. 构造 f1 = 2 and f2 > 40，进行范围查询

SELECT f1, f2 FROM t1 WHERE f2 > 40;

执行的最终SQL:
SELECT f1, f2 FROM t1 WHERE f1 =1 and f2 > 40
UNION
SELECT f1, f2 FROM t1 WHERE f1 =2 and f2 > 40;

所以对于对于f1值很少，区分度不高的情况索引跳跃扫描会快一些；反之查询效率慢些。

我们不能依赖这个优化，建立索引的时候，还是优先把区分度高的，查询频繁的字段放到联合索引的左边。

限制条件

• 查询必须只能依赖一张表，不能多表JOIN。

• 查询中不能使用 GROUP BY 或 DISTINCT 语句。

• 查询的字段必须是索引中的列。

• 组合索引形式：([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])，A，D 可以为空，但是B ,C 不能为空。