mysql 索引是怎么实现的？

对于MYSQL的INNODB存储引擎的索引，大家是不陌生的，都能想到是 B+树结构，可以加速SQL查询。但对于B+树索引，它到底“长”得什么样子，它具体如何由一个个字节构成的，这些的基础知识鲜有人深究。本篇文章从MYSQL行记录开始说起，层层递进，包括数据页，B+树聚簇索引，B+树二级索引，最后在文章末尾给出MYSQL索引的建议。文章涉及较多基础知识，内容较为枯燥，因此采用较多的图片补充说明，希望能对读者有帮助。

A. 一条记录存储格式：COMPACT行记录结构

mysql是关系型数据库，每一行记录都是表结构定义的关系的 显示表达。在脑中很直观地想到，记录存储时也可能按行存储。

的确，mysql是这么存储一条行记录的。但会添加一些额外信息，来补充行记录信息。

有一个概念可能大家不熟悉，是【变长字段】。mysql数据库类型中的 VARCHAR(M), VARBINARY(M), 各种TEXT，BLOB类型，这些类型的数据长度是可变的，称 数据类型为可变长类型的列 为 变长字段。

另外，mysql会默认为行记录添加一些列（隐藏列）。上图补充这些隐藏列之后，完整行记录的结构如：

DB_ROW_ID: 唯一标识一条记录，在表中未设置主键 或 未有不允许为NULL的UNIQUE键时，则MYSQL新增该隐藏列作为主键。 DB_TRX_ID: 事务ID。 DB_ROLL_PTR: 回滚指针。

下面再详细的铺开 ，关于记录的额外信息 的具体内容。

通过真实的数据库表的行数据，来强化下上面的概念。 首先新增一个表，并在表中insert两条记录。

create table record_format_demo(
c1 varchar(10),
c2 varchar(10) not null,
c3 char(10),
c4 varchar(10)
) charset=ascii row_format=compact

insert into record_format_demo(c1, c2, c3, c4) values
("aaaa", "bbb", "cc", "d"),
("eeee", "fff", NULL, NULL);

做一个简单的查询，验证数据正常写入。

分析这两行数据的存储记录。

第一行记录：

第二行记录：

应该会注意到，变长字段长度列表 与 NULL值列表都是逆序存放的。 原因是：这样可以使得记录中位置靠前的字段 和 它们对应的字段长度信息在内存中的距离更近，可能会提高 高速缓存的命中率。

B. 盛放记录的盒子：数据页

为了更清楚的理解 数据页结构 以及 下文中的索引，更换一个带主键的表。

CREATE TABLE page_demo(
c1 INT,
c2 INT,
c3 VARCHAR(10000),
PRIMARY KEY (c1)
) CHARSET=ascii ROW_FORMAT=Compact;

INSERT INTO page_demo VALUES
(1, 100, 'aaaa'),
(2, 200, 'bbbb'),
(3, 300, 'cccc'),
(4, 400, 'dddd');

做一个简单的查询，验证数据正常写入。

根据行记录结构中的next_recrod属性的含义，多条行记录是以单向链表的形式存储。mysql为了后续更好的查询，单向链表上的记录是按照主键顺序排列的。 上述这四条记录，可以显示的画成：

假如删除其中c1=2这条数据，则单向链表变更如下。 其中变化点为 c1=2 这条数据中，deleted_flag变更成0， next_record=0，但并没有从磁盘上直接清除掉，head_no也未清除。第一条记录的next_record 指向了第三条记录。

当我们查询数据时，如果数据以行记录的形式一条一条从磁盘加载到内存中，那么因为IO过多的原因，整体性能肯定较为低效。 因此mysql规定，磁盘与内存交换数据的基本单位是一个页大小。这个页大小 默认是16K。 根据页中存储的数据类型不同，页也分成许多类型。对于存储行记录的页，称为索引页（Index Page），也称为数据页。

那么接下来我们看看，数据页的结构如何，一条条行记录如何存放在数据页中。先上个图。

从上图中，我们可以猜到，数据页总共分成7个模块，目前咱们只关心 User Records 部分，它存储的就是用户真实的行记录。 但是一个数据页的空间总共是16K，不会自动增大空间，随着User Records 模块中的行记录越来越多，那么肯定有其他模块的空间就越来越小。 这个模块是 Free Space，是页中尚未使用的空间。更新一下上面这个图，补充 Free Space的内容。随着User Records中行记录的增加，Free Space空间则越来越小。

在一个数据页中，除了真实的行记录之外，还有两条固定的伪记录。 其中一条记录称为 infimum 【[ɪnˈfaɪməm] ，下确界】行记录，规定是数据页中记录的最小值。 infimum记录特别简单，仅包含了 记录头信息(5字节) + 真实记录数据(8字节)，其中【69 6E 66 69 6D 75 6D 00】16进制转换成对应的单词，就是【infimum】。

另外一条记录称为 supremum【[sə'priməm]，上确界】行记录，规定是数据页中记录的最大值。 supremum记录同样特别简单，仅包含了 记录头信息(5字节) + 真实记录数据(8字节)，其中【73 75 70 72 65 6D 75 6D】16进制转换成对应的单词，就是【supremum】。

再更新一版数据库页结构， 补充上infimum 与 supremum。

既然规定了页面中的最小记录 与 最大记录，理所应当，上文中串联各个行记录的单向链表，则应该有一个固定的头部 与 固定的尾部。 更新一下单链表的图。注意下infimum 与 supremum中的几个关键值。

infimum: n_owned=1，表示是某一个分组的最后一条记录，当前分组共1条记录；record_type=2； next_record=第一条真实行记录的真实值的相对位置。 supremum: n_owned=5，表示是某个分组的最后一条记录，当前分组共5条记录；record_type=3； next_record=0，表示记录是本数据页中最后一条记录。

OK，到现在数据页中完整的单链表已经形成了。 思考一个问题，如何根据主键值，在数据页中的单链表如何查找到相应的数据。 最直接的做法是：从 infimum记录开始，沿着单链表的顺序不断的向后查找，直到supremum结束。在这期间，找到满足条件的记录就返回，找不到则不返回。

一个数据页默认大小是16K，用于存储真实行记录的空间超过 98%以上。也就是说，一个数据页中在行记录填充满的情况下，行记录的数量是较多的（当然与行记录大小有关）。 如果每次查询都从单链表的infimum记录 一直到 supremum记录，肯定是无法接受的，很有必要对此现状进行优化。 由此，引出了数据页中的另一个模块，Page Directory，页目录。

首先返回看下上面完整单链表中，infimum记录 与 supremum记录中的两个值，n_owned。这个值分别为 n_owned=1 与 n_owned=5。

参考下n_owned的定义，它是：【页面分组之后，每个组最后一条行记录中该值等于本组内的记录数量。本组内其他记录该值都等于0。】 对于上面的单链表，其它行记录的owned值 都为零。也就是说，infimum单条记录作为一个组，剩余的四条行记录+supremum记录作为一个组。 mysql规定：

•对于infimum记录所在的分组只能有1条记录，也就是它本身。

•对于supremum记录所在的分组的记录数在1~8条之间。

•其它的分组记录的条数范围，在4~8条之间。

将每个组中 最后一条记录在页面中的地址偏移量（该记录的真实数据与数据页中第0个字节之间的距离）单独提取出来，以倒序存储到数据页中的固定模块中。 这个模块，就称为：Page Directory。Page Directory中存储的地址偏移量，也称为Slot 【[slɒt]， 槽】，每个Slot两字节。【可以想想为啥是两字节？】

再次更新下数据页结构图。

目前的只有四条记录，两个分组，数量太少了。我们继续往表中继续增加一些记录。

INSERT INTO page_demo VALUES
(5, 500, 'eeee'),
(6, 600, 'ffff'),
(7, 700, 'gggg'),
(8, 800, 'hhhh'),
(9, 900, 'iiii'),
(10, 1000, 'jjjj'),
(11, 1100, 'kkkk'),
(12, 1200, 'llll'),
(13, 1300, 'mmmm'),
(14, 1400, 'nnnn'),
(15, 1500, 'oooo'),
(16, 1600, 'pppp');
INSERT INTO page_demo VALUES
(5, 500, 'eeee'),
(6, 600, 'ffff'),
(7, 700, 'gggg'),
(8, 800, 'hhhh'),
(9, 900, 'iiii'),
(10, 1000, 'jjjj'),
(11, 1100, 'kkkk'),
(12, 1200, 'llll'),
(13, 1300, 'mmmm'),
(14, 1400, 'nnnn'),
(15, 1500, 'oooo'),
(16, 1600, 'pppp');

在不断的插入新行记录时，因此不同类型分组数量的约束，所以分组也会增加。这个过程如下：

•在初始情况下，一个数据页中只会有 infimum 与 supremum 这两条记录，它们分别属于两个组。此时Page Directory 也只会有两个slot，分别代表infimum的地址偏移量 与 supremum的地址偏移量。

•之后每新增一条行记录，都会从Page Directory中找到对应的记录的主键值 比 待新增记录的主键值大 并且差值最小的slot【slot是一个组内最大的那条记录在页面中的地址偏移量，通过slot可以快速找到对应记录的主键值】， 然后把该slot对应的记录的 n_owned值加1，表示本组内新增了一条记录，直到该组中的记录数等于8个。

•当一个组中的记录数等于8后，再插入一条记录，会将组中的记录拆分成两个组，其中一个组中是4条记录，另外一个组中是5条记录。拆分过程中，会新增一个slot，记录这个新增分组中最大的那条记录的地址偏移量。

现在来看看下，目前该数据页中的行记录的分组情况。

来演绎一个根据主键查询行记录的例子。假如想查询主键值 ，也就是C1=6的行记录。通过二分法查找，过程如下：

1.设置low=0，high=4。计算中间slot的位置，(0 + 4) / 2 = 2, 通过slot 2 查询到对应的主键值等于8。因为8 > 6， 所以设置high = 2， low = 0不变。

2.重新计算中间slot的位置，（0 + 2）/ 2 = 1, 查看 slot 1对应记录的主键值为4。又因为 4 < 6, 所以设置low = 1，high 不变。

3.此时low = 1， high = 2， 所以确定主键值为6的行记录在 slot2 对应的组中。此时找到slot 2的最小一条记录【通过slot 1 的next_record找到slot 2的最小记录】，遍历slot 2中的所有记录即可。

截止目前，数据页模块中，还要三个模块是未知的。回想一下，对于一条行记录，它有 记录头信息 来描述这条行记录的相关信息，那么对于一个数据页，它有对应的头部来记录数据页的相关信息吗？

有的，自然是有的，而且还不少。这个模块就称为 Page Header。

Page Header的结构如下：

主要作用是标识 数据页中记录了多少条记录，Free Space在页面中的地址偏移量，页目录中包含多少slot等等。

额外说下page_direction 与 page_n_direction的含义。

page_direction: 假如新插入的一条记录的主键值比上一条记录的主键值比上一条记录大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是page_direction。

page_n_direction: 假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。 当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

到此为止，仅剩下两个模块了，加油啊。

上文中的Page Header 是专门针对数据页记录的各种状态信息。但数据页 仅仅是多种页类型中的一种，其它的还有例如undo日志页，溢出页，存储段信息页，存储区信息页等。 因此mysql 使用了File Header 来描述各种页的通用信息。

从fil_page_prev 与 fil_page_next 两个属性可以联想到，不同页之间是有关联的，而且是以双向链表的形式。

最后一个模块，File Trailer 【 [ˈtreɪlə(r)]，挂车】。

InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。

但是在同步了一半的时候中断电了怎么处理呢？此时就需要靠 File Trailer 模块中数据起作用。

展示下完整的数据页结构。

盗用一下网上的一个很好的数据页图。

C. 快速查询的秘籍：B+树索引

上文在介绍File Header时，我们特别说明了里面的两个数据：FIL_PAGE_PREV，指向前一个页号。FIL_PAGE_NEXT, 指向后一个页号。由此可以得出，多个数据页之间的数据结构是双链表。

上文使用的数据共有16条，为了演示这个双链表的效果，现在假设【仅仅是假设】每个页中存放不超过4条行记录。则上文的16条记录，形成的数据页双链表结构如下图【此处省略了许多非必要展示的字段】。

从上面这个链表，可以得到以下结论：

1.双向链表的页号并不保证是连续的。

2.下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。【在依次写入主键数据不连续的行记录时，会发生页中数据的迁移。】

从目前的双向链表结构中，想要根据主键值查找记录，也只能是从第一页开始，一页一页的依次查找。虽然在一个数据页中，可以根据 Page Directory进行快速的二分查找，但总体效率肯定不尽人意。得优化。

我们注意到，【下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值】。因此，首先进行第一步改进。 维护一个目录项，目录项中记录某个页中主键的最小值以及页号，各个目录项再以单向链表的形式链接起来。这样就可以根据主键查询目录项，得到满足的条件页，再进入相应的页中查询行记录即可。

到现在看看，目录项是不是也很像行记录，只是它的列值是主键与页号。那把这些目录项维护成在一个页中看看效果。毫无违和感，浑然天成。现在看到的，就是主键索引的雏形了。

目前数据有些少了，继续补充一些数据，再画个图看看。

INSERT INTO page_demo VALUES
(17, 1700, 'qqqq'),
(18, 1800, 'rrrr'),
(19, 1900, 'sss'),
(20, 2000, 'tttt');

现在看到的，就是一个典型的INNODB的主键索引了。它包含以下特点：

1.整个主键索引的数据结构是一棵树，具体是以B+树实现。

2.叶子节点与非叶子节点中的行记录按照主键大小顺序排成一个单向链表，页内的记录被划分成若干组。可以利用Page Directory进行二分法快速查找到行记录。

3.存放用户记录的数据页，根据页中用户记录的主键大小顺序排成一个双向链表。所有用户记录都存放在叶子节点上。

4.存放目录项记录的数据页都是非叶子节点，分层不同的层级。同一层级中的页也是根据页中目录项记录的主键大小顺序，排成一个双向链表。

通常也把INNODB的B+树索引称为 聚簇索引（clustered/ˈklʌstəd / index），所有的真实数据都存储在聚簇索引中。【索引即数据，数据即索引】。

通常除了主键索引之外，肯定还会建一些普通索引，称为二级索引，或者辅助索引。上面的数据，我们以上文中的数据 C2列建立一个二级索引看看。

现在来看看下，INNODB中 二级索引的特点。

1.二级索引的数据结构 与 聚簇索引一样，是B+树结构。

2.二级索引的所有目录项页存储行记录的真实数据是 索引列+页号。

3.二级索引的非叶子节点存储行记录的真实数据是 索引列+主键值。

因为在二级索引中查询到的是主键值，如果想要得到完整的行记录，则需要拿着主键值再到聚簇索引中再进行一次查询。这个过程称为【回表】。 【回表的过程，特别要强调下每次对于二级索引来说，每次查询到一条满足二级索引字段条件的记录时，都进行一次 回表 判断是否满足其他条件，然后将满足所有条件的一条查询结果返回给客户端。】

再讲讲联合索引。现在以C2 与 C3两列作为联合索引，为了更好的展示联合索引的效果，先修改几条行记录。

update page_demo set c3 = 'dddd' where c2 = 100;
update page_demo set c3 = 'cccc' where c2 = 200;
update page_demo set c3 = 'bbbb' where c2 = 300;
update page_demo set c3 = 'aaaa' where c2 = 400;
update page_demo set c2 = 300 where c1 = 4;

给联合索引画个图。

总结下联合索引的特点：

联合索引的数据页中记录的排序，默认是按照定义联合索引的第一列排序的，在第一列值相等的情况下，再按照第二列排序。其它的方面均与单列的二级索引无差别。

联合索引还有一个比较特殊的使用场景：最左前缀匹配。 例如有联合索引的列包含：C1，C2，C3 三列，而且顺序也是 C1，C2，C3。 则查询语句：select * from page_demo where c1 = x and c2 = y and c3= z, 使用到了C1，C2，C3 列排序的结果。 select * from page_demo where c1 = x and c2 = y, 使用到了C1，C2 列排序的结果。 select * from page_demo where c1 = x and c3 = z, 仅使用到了C1 列排序的结果。

D. 索引的优缺点及建议

优点：

1.对于等值查询，可快速定位到对于的行记录。

2.对于范围查询，可辅助缩小扫描区间。

3.当ORDER BY的列名 与 索引的列名完全一致时，可加快排序的顺序。

4.当GROUP BY的列名 与 索引的列名完全一致时，可加快分组。

5.当二级索引列中 包含了 SELECT 关键字后面写明的所有列，则在查询完成二级索引之后无需进行回表操作，直接返回即可。这种情况，称为【覆盖索引】。

缺点：

1.建立索引占用磁盘空间。

2.对表中的数据进行 增加，删除，修改 操作时，都需要修改各个索引树，特别是如果新增的行记录的主键顺序不是递增的，就会产生页分裂，页回收等操作，有较大的时间成本。

3.当二级索引列的值 的 不重复值的个数较少时，通过二级索引查询找到的数据量就会比较多，相应的就会产生过多的回表操作。

4.在执行查询语句的时候，首先要生成一个执行计划。通常情况下，一个SQL在执行过程中最多使用一个二级索引，在生成执行计划时需要计算使用不同索引执行查询时所需的成本，最后选择成本最低的那个索引执行查询。 因此，如果建立太多的索引，就会导致成本分析过程耗时太多，从而影响查询语句的性能。

建议：

1.只为用于搜索，排序，分组的列创建索引。

2.索引的列需要有辨识性，尽可能地区分出不同的记录。

3.索引列的类型尽量小。因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘I/O带来的性能损耗也就越小。

4.如果需要对很长的字段进行快速查询，可考虑为列前缀建立索引。【alter table table_M add index idx_key1(column_n(10)) --> 将table_M表的 idx_key1列的前10个字符创建索引】

5.覆盖索引，当二级索引列中 包含了 SELECT 关键字后面写明的所有列，则在查询完成二级索引之后无需进行回表操作，直接返回即可。因此，编写【select *】的时候，要想想是否必要。

6.在查询语句中，索引列不要参与 条件值计算，也是把条件值计算完成之后，再和索引列对比。【否则MYSQL会认为 搜索条件不能形成 合适的扫描区间来减少扫描的记录数量】

欢迎来京东云开发者社区交流技术，原文作者：郑啟龙

希望你能静下心来看完我的这个回答，看完之后我保证你能彻底搞懂MySQL的索引，如果不懂，留言骂我好了。

1. 准备工作

为了更好地解释索引，我们先建个表。

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我创建了一个存储引擎为InnoDB的表user_innodb，其中包含主键id、姓名字段（name）、性别字段（gender，用0，1表示不同性别）、手机号字段（phone），并批量初始化了500W+条数据。

注：数据全部是模拟产生的，性别不做严格区分；手机号如有雷同，纯属巧合

mysql> SELECT COUNT(*) FROM user_innodb;
+----------+
| COUNT(*) |
+----------+
|  5283424 |
+----------+
1 row in set (0.31 sec)

例1：为name创建索引之前

mysql> SELECT * FROM user_innodb WHERE name = "蝉沐风";
+---------+-----------+--------+-------------+
| id      | name      | gender | phone       |
+---------+-----------+--------+-------------+
| 1099999 | 蝉沐风    |      0 | 13203398311 |
+---------+-----------+--------+-------------+
1 row in set (0.96 sec)

例2：为name创建索引之后

mysql> SELECT * FROM user_innodb WHERE name = "蝉沐风";
+---------+-----------+--------+-------------+
| id      | name      | gender | phone       |
+---------+-----------+--------+-------------+
| 1099999 | 蝉沐风    |      0 | 13203398311 |
+---------+-----------+--------+-------------+
1 row in set (0.03 sec)

例3：根据主键id进行查询

mysql> select * from user_innodb where id = 1099999;
+---------+-----------+--------+-------------+
| id      | name      | gender | phone       |
+---------+-----------+--------+-------------+
| 1099999 | 蝉沐风    |      0 | 13203398311 |
+---------+-----------+--------+-------------+
1 row in set (0.00 sec)

可以看到，创建索引之前搜索name为蝉沐风的记录花费时间为0.96秒，为name字段创建索引后，搜索时间仅为0.03秒，可见索引的作用之大。

但是我们没有显式为主键创建索引，为什么主键查询也这么快？我在上一篇文章中解释了主键查询快的原因，但是只解释了一半，现在我来解释另一半。

虽然我希望每一篇文章都讲述一个独立的知识点，但是对于MySQL这种复杂的软件，各种细节之间盘根错节，想深入理解一个知识点很多时候需要其他知识点的加持，在继续阅读之前，强烈推荐你花10分钟先读一下这篇文章。
如果你实在不想看，我会简单总结一下之前讲的内容。

强烈推荐阅读： 图解｜12张图解释MySQL主键查询为什么这么快

2. 前置知识

现在我们已经知道了，InnoDB存储引擎为我们提供了4种不同的行格式来保存我们向MySQL中插入的数据，在这里我们统一称之为记录。

记录是保存在InnoDB页中的，InnoDB存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的最小单位。InnoDB中页的大小默认为16KB。也就是默认情况下，一次最少从磁盘中读取16KB的数据到内存中，一次最少把内存中16KB的内容刷新到磁盘上。存储用户记录的页我们统一叫做数据页，它只是众多类型的InnoDB页中的一种而已，其他类型的页我们无需关注。

非常非常重要的一点是，在一个数据页中，用户记录是按照主键由小到大的顺序串联而成的单向链表。

但是一个数据页中的记录可能非常多，为了逃避低效的遍历，InnoDB引擎的设计者想出了一种绝妙的搜索方法，把数据页中的所有记录（包括伪记录）分成若干个小组（并对每个小组内的组员数量做了规定），每个小组选出组内最大的一条记录作为“小组长”，接着把所有小组长的地址拿出来，编成目录。

举个例子，下面的图片展示了一个数据页中的所有记录被分组的情况：

上图中的所有记录（包括伪记录）分成了4个小组，每个小组的“组长”被单独提拔，单独编制成“目录”，InnoDB官方称之为「槽」。槽在物理空间中是连续的，意味着通过一个槽可以很轻松地找到它的上一个和下一个，这一点非常重要。

槽的编号从0开始，我们查找数据的时候先找到对应的槽，然后再到小组中进行遍历即可，因为一个小组内的记录数量并不多，遍历的性能损耗可以忽略。而且每个槽代表的“组长”的主键值也是从小到大进行排列的，所以我们可以用二分法进行槽的快速查找。

图中包含4个槽，分别是0、1、2、3，二分法查找之前，最低的槽low=0，最高的槽high=3。现在我们再来看看在这个数据页中，我们查询id为7的记录，过程是怎样的。

1. 使用二分法，计算中间槽的位置，(0+3)/2=1，查看槽1对应的“组长”的主键值为4，因为4<7，所以设置low=1，high保持不变；
2. 再次使用二分法，计算中间槽的位置，(1+3)/2=2，查看槽2对应的“组长”的主键值为8，因为8>7，所以设置high=2，low保持不变；
3. 现在high=2，low=1，两者相差1，已经没有必要继续进行二分了，可以确定我们的记录就在槽2中，并且我们也能知道槽2对应的“组长”的主键是8，但是记录之间是单向链表，我们无法向前遍历。上文提到过，我们可以通过槽2找到槽1，进而找到它的“组长”，然后沿着“组长”向下遍历直到找到主键为7的记录就可以了。

当用户记录多到一个数据页装不下的时候，就再申请一个数据页，各个数据页在逻辑上使用双向链表进行连接，因此新分配的数据页编号就没必要非得按照从小到大的顺序进行排列了，如下图所示：

因此，虽然在一个数据页内能够做到主键的快速查询，但是InnoDB存储引擎不知道你要查找的记录所在的页号，那也只能从第一页开始沿着双向链表一直进行查找，遍历每一页，至于在每一个数据页中是怎么查找的，你已经很清楚了。

很显然，InnoDB引擎有办法能够快速定位到你要的主键数据所在的数据页，而不是从第一页开始遍历，否则不可能有例3那样的查询速度。

那么，InnoDB是怎么做到的呢？

3. InnoDB索引

3.1 主键索引登场

为了方便描述，我们假设一个数据页最多只能放3条用户记录，那么user_innodb表的前12条数据的保存形式如下图：

大家看这些连接起来的数据页像不像组成一本书的每一章？自然，数据页中的每一条记录就是章中的每一个小节了。

那么为了加快检索，我们可以模拟书籍章节目录，给数据页添加一个目录。

如上图，我们为4个数据页创建了一个目录，每个数据页对应了一条记录，为了区别于用户记录，我们称之为目录项记录，目录项记录同样是按照主键从小到大的顺序进行单向链接的。

不同于用户记录中包含了完整的数据，目录项记录只包含了数据页的最小主键值和对应的数据页号。既然都是记录，InnoDB的设计者直接用数据页来存储目录项记录了，所以上图中页32的页面结构和其他数据页是完全一样的。

接下来我们看看加了个目录是如何提高我们的查询效率的，以查询主键id为8的记录为例，步骤大致如下：

1. 先找到存储目录项的数据页32，通过二分法快速定位到对应的目录项记录，因为7<8<10，所以定位到对应的记录所在的页应该是页14；
2. 然后在页14中进行查找就可以了，查找的方法我们之前介绍过了。

目前的页面并不多，所以对查询效率的提升并不十分明显，但是一旦数据页的数量飞速增长，这种通过添加目录的方式带来的查询优势会被无限放大！但是同时有个问题，数据页多了，目录项记录在一个数据页中不够用了怎么办？

再加一个数据页。我们再添加2条用户记录，看一下添加之后的样子：

注：实际上一个页面中能够存放的记录（用户记录/目录项记录）数目是非常多的，为了方便画图，我只是假设了数据页最多存放3条用户记录，最多存放4条目录项记录

现在假设要查找主键ID为14的记录，我们还是先得找到存储目录项的数据页，可是现在有2个这种数据页，分别是页32、页124，我怎么知道要定位到哪一个目录项数据页呢？从页32开始遍历吗？别开玩笑了，我们做这么多就是为了不想遍历。这样吧，我们为存储目录项的数据页再生成一个目录。我们来捋一捋关系。

前面举过例子，存储用户记录的数据页相当于章，用户记录相当于小节，为章节生成目录就得到了存储目录项记录的数据页（页32和页124），相当于是一本书，然后再为书编一个目录，就相当于是个书架。

对应到存储结构上那就是下图：

按照上图，我们又添加了一个数据页99，用来保存页32和页124对应的2条目录，现在要查找主键ID为14的记录，需要经历这几个步骤：

1. 就从页99中，快速检索到对应的目录项数据页124；
2. 在页124中，快速检索到对应的数据页27；
3. 在页27中，快速检索到主键为14的记录。

到这里为止，你已经悄悄地掌握了B+树了。没错，上面我们一步步推导出来的搜索结构就是大名鼎鼎的B+树，而MySQL给它起了一个更响亮的名字——索引。

B+树最底层的节点（对应图中存储用户记录的数据页）被称为叶子节点，其他的节点自然叫做非叶子节点了，更特殊地，B+树最顶部的节点叫做根节点。

有一个值得我们关注的细节，这棵B+树的叶子节点存储了我们完整的用户记录（就是我们插入表的所有数据），而且，这是用户记录在InnoDB引擎中的唯一存储方式。也就是所谓的“索引即数据，数据即索引”。

更方便地一点是，这个关于主键的索引完全是由InnoDB存储引擎自动生成的，不需要我们显式地书写创建索引的语句。这个索引叫做主键索引，又叫做聚簇索引。

主键索引有两个特点：

1. 按照主键的大小对用户记录和数据页进行排序，记录用单向链表连接，数据页使用双向链表连接；
2. B+树的叶子节点保存了用户的完整记录。

现在终于解释完为什么主键查询这么快了，搞明白主键索引之后，普通索引和联合索引就太简单了！

3.2 普通索引

主键索引是在搜索条件为主键的时候才会发挥作用，但是我要以name='蝉沐风'为搜索条件怎么办？通过主键索引的讲解，我们首先会想到这么一个方案：再创建一个B+树（我们称为name索引），其中用户记录和数据页按照name字段进行排序，B+树的叶子节点保留完整的用户数据，这样就可以实现对name列的快速搜索了。

但是如此一来，表中数据就被完整记录了2次（主键索引的叶子节点和name索引的叶子节点），要是我们为其他字段再建立索引，磁盘空间可想而知。因此，我们得想个其他的办法。

我们已经知道根据主键查询用户记录是非常快的了，那我们可以想个办法根据name字段来迅速找到主键，然后再根据主键查找用户记录啊。这个办法同样离不开B+树。

这棵B+树和聚簇索引的B+树有点区别：

1. 叶子节点存放的不再是完整的用户记录，而是只记录name列和主键值；
2. 数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照name列排序；
3. 目录项记录除了存储索引列（name）和页号之外，同时还存储了主键值；（大家可以想一想，为什么要存储主键值）

有了这棵B+树，你就可以通过name列快速找到主键值了，查找的方式和根据主键值查找用户记录的方式完全一样，只不过前者查到的是主键值，后者查找到的是一条完整的用户记录罢了。

你可能对字符串进行二分法感到有点奇怪，甚至没有接触过的相关知识的读者连对字符串进行排序都会觉得很诧异。其实在创建表的时候我们可以对字符串字段指定字符集和比较规则，如果你不指定，MySQL会默认给你设置，总之，MySQL总会找到一个方式对字符串进行排序。

现在得到主键的id了，然后根据主键id到主键索引中查找到完整的用户记录，这个过程叫做回表。如果没有为name列设置唯一性约束，那就可能找到多个符合条件的主键id，多回几次表就可以了。

对name这种单个列添加的索引叫做普通索引，也叫二级索引。

如果同时对多个列建立索引，那B+树的存储又会是什么样子呢？这就是联合索引了，理解了上面的内容，再理解联合索引只是水到渠成的事罢了。

3.3 联合索引

假设我们为name列和phone列建立联合索引（注意我描述的顺序），自然也是创建一棵B+树，这棵B+树和之前又稍微有点不同：

1. 叶子节点存放的是name列、phone列和主键值；
2. 目录项记录除了存储索引列（name、phone）和页号之外，同时还存储了主键值；（大家可以想一想，为什么要存储主键值）
3. 数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照name列排序，如果name列相同，那就按照phone列排序；（如果phone列再一样呢？你现在明白为什么要存储主键值了吗？）

再画个图吧（有点偷懒了哈，数据页号没换）：

还是和二级索引一样，利用B+树快速定位到数据页，然后页内快速定位到记录，找到记录中的主键id，再回表，如果找到多条符合条件的记录，就多回几次表。

4. InnoDB其他的索引方式

以上介绍的是B+树索引，它其实是InnoDB存储引擎提供的众多索引中的一种而已，但却是使用最多、面试中最常被问到的一种索引。除此之外，还提供了其他的索引方式，例如我的TablePlus工具（Mac上的MySQL连接工具）提供了4种。

4.1 HASH

如果你用过Java的HashMap或者Python的字典，你对这个概念就应该很清楚了。

哈希表是一种采用键值对（Key-Value）存储数据的结构，它会根据索引字段生成哈希码和指针，指针指向表中的数据。不可避免地，多个索引字段值经过哈希函数的换算，会出现同一个值的情况，处理这种情况的一种方法就是创建一个单向链表。如下图所示，我们为name字段创建HASH索引：

哈希索引有3个重要特点：

1. 查询速度非常非常快，时间复杂度是O(1)，因为哈希索引中的数据不是按照顺序存储的，所以不能用于排序；
2. 查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询（=、IN），不支持范围查询（>、<、>=、<=、BETWEEN、AND）；
3. 如果哈希冲突，就得采用添加单向链表的方法解决，会造成效率下降。

另外，虽然提供了HASH的索引方法，但是在InnoDB中无法显式创建一个HASH索引，所谓地支持哈希索引其实指的是自适应哈希索引（AHI），是InnoDB自动为BufferPool中的热点页创建的索引。虽然TablePlus在创建索引的时候能够选择HASH，但是实际创建完之后显示类型仍然是BTREE。

4.2 FULLTEXT

如果你的数据表有一个大文本字段，你想查询这个字段中包含「蝉沐风」的所有记录，你可能会采用LIKE '%蝉沐风%'的方式进行查询，但是索引的最左匹配原则告诉你这样的查询效率太低了，这时候全文索引就出现了。

为了说明问题，我们假设一个文本字段存储了这样一段文字：

我叫蝉沐风，欢迎大家关注我的微信公众号

想要快速根据某个词进行查询，首先要对这段文本进行分词，得到下列分词结果：

我/叫/蝉/沐/风/，/欢迎/大家/关注/我/的/微信/公众号

然后建立每个分词和用户记录（在搜索领域中的专业术语叫做文档）的对应关系，生成一个单词文档矩阵

然后就可以根据某个单词进行查询了，这也是现代搜索引擎的基本原理，感兴趣的话可以搜索一下倒排索引，再感兴趣可以了解一下Elastic Search。

4.3 SPATIAL

是对空间数据的索引，我没使用过，就暂时解释这么多了。

5. MyISAM的索引方案

不同的存储引擎存放数据的方式不一样，产生的文件数量和格式也不一样，InnoDB文件包含2个，MEMORY文件包含1个，MyISAM文件包含3个。我们接下来关注的就是MyISAM中的文件。

• .MYD文件，D代表Data，是MyISAM的数据文件，存放用户记录，也就是我们插入的表数据；
• .MYI文件，I代表Index，是MyISAM的索引文件。一个索引就会有一棵B+树，所有的B+树都保存在这个文件当中。

也就是说，不同于InnoDB的“索引即数据”的思想，MyISAM存储引擎中的索引和数据是分开存储的。

MyISAM中的B+树长啥样子呢？其实样子和InnoDB差不多，区别就是MyISAM的B+树的叶子节点存储的是用户记录对应的磁盘地址，所以从索引文件.MYI中找到对应的索引键（建立索引的列的值）后，会到.MYD中找到对应的用户记录。以主键为例我再再再画个图：

如果你还没看懂，或许你该看一看我在回答中推荐你看的那一篇文章，看完之后再看一遍这个回答。如果还没懂，那可能真的是我写得不好，留言，我教你。

如果你觉得我写得还不错，欢迎关注我的公众号「蝉沐风」，邂逅每一篇用心书写的技术文章。

看了很多关于索引的博客，讲的大同小异。但是始终没有让我明白关于索引的一些概念，如B-Tree索引，Hash索引，唯一索引....或许有很多人和我一样，没搞清楚概念就开始研究B-Tree，B+Tree等结构，导致在面试的时候答非所问！本文中有关存储引擎请查看 MySQL存储引擎-InnoDB和MyISAM

索引是什么?

索引是帮助MySQL高效获取数据的数据结构。

索引能干什么?

提高数据查询的效率。

索引：排好序的快速查找数据结构！索引会影响where后面的查找，和order by 后面的排序。

一、索引的分类 #

1️⃣从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

2️⃣从应用层次来分：普通索引，唯一索引，复合索引

3️⃣根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

​ 1️⃣中所描述的是索引存储时保存的形式，2️⃣是索引使用过程中进行的分类，两者是不同层次上的划分。不过平时讲的索引类型一般是指在应用层次的划分。

就像手机分类：安卓手机，IOS手机 与 华为手机，苹果手机，OPPO手机一样。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

非聚簇索引：不是聚簇索引，就是非聚簇索引（认真脸）。

二、索引的底层实现 #

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb会透明建立自适应hash索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

不谈存储引擎，只讨论实现(抽象)

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

B-Tree索引（MySQL使用B+Tree）

​ B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

​ 是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

结合存储引擎来讨论（一般默认使用B+Tree）

案例：假设有一张学生表，id为主键

在MyISAM引擎中的实现（二级索引也是这样实现的）

在InnoDB中的实现

三、问题 #

问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

问：为什么官方建议使用自增长主键作为索引。

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

插入非连续的数据

原文地址： 李强的个人博客(基于SSM，Nginx+Redis的后台架构)

前言

上一篇介绍了 MySQL 的逻辑架构和执行过程，这一篇将介绍索引相关的内容。

索引是用额外的数据结构，来实现快速检索目标数据的。就像字典当中的目录一样，用额外的空间来存储部分内容，从而加快检索速度。

MySQL 的逻辑架构分为 Server 层和存储引擎层，其中索引和数据就位于存储引擎中，而不同的存储引擎可能有不同的实现索引的方式，比如常见的 InnoDB 和 MyISAM 使用的都是 B+Tree，但是实现方式不同。

按照不同的分类方式，索引可以分为以下几类：

• 按「数据结构」分类：B+ 树索引、B 树索引、Hash 索引等。
• 按「物理存储」分类：聚簇索引（主键索引）、非聚簇索引（二级索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引、全文索引、空间索引等。
• 按「字段个数」分类：单列索引、联合索引。

B+树索引

B+ 树是一种多叉平衡搜索树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表。所有节点按照索引键大小排序，构成一个双向链表，便于范围查询。

虽然，InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同：

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身（数据页）；
• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

由于物理空间地址是混乱无序的，MyISAM 只能先取数据，再排序；而 InnoDB 的物理存放顺序和索引顺序一致。

后续如果没有额外说明，则默认说的都是 InnoDB 中的 B+ 树索引的实现。

InnoDB 里的 B+ 树中的每个节点都是一个数据页。

B树和B+树

B 树又名平衡多路查找树，B 树中所有结点的孩子个数的最大值称为 B 树的阶，通常用 m 表示。

B 树和 B+ 树的区别在于，B 树的每个节点都存储了 key 和 data，而 B+ 树的 data 只存储在叶子节点上，这样单个节点可以存更多的索引键，树的高低就越小，查询时磁盘 IO 的次数就越小。且 B 树没有冗余节点，而 B+ 树有冗余节点。

B+ 树的冗余节点：不仅在叶节点中保存了所有的键，且部分键在非叶节点中也存在。

具体区别：

1、单点查询

B 树的查询效率可能更高，但波动较大；B+ 树相对更矮胖，查询底层节点的 I/O 次数更少。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引值，因此数据量相同的情况下，B+ 树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O 次数会更少。

2、范围查询

B 树的范围查询相当于树的遍历，效率很低；B+ 树的叶子节点间还有双链表连接，利于范围查询。

3、插入删除效率

B 树没有冗余节点，插入和删除都需要涉及树的变形；B+ 树存在冗余节点，插入和删除时不需要涉及树的变形，效率更高。

为什么选择 B+ 树而不是红黑树（或其他平衡二叉搜索树）？
红黑树（或其他平衡二叉搜索树）每个节点只能存储一个数据，所以在数据量大的情况下，树高很大，会导致多次磁盘 IO。

按物理存储分类

聚簇索引

Clustered Index，又称主键索引，叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+ 树的叶子节点里。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

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

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

当执行一条查询语句，比如使用主键索引查询 id 号为 5 的商品时，B+ 树会自顶向下逐层进行查找，查询过程是这样的：

1. 将 5 与根节点的索引数据（1，10，20）比较，因为 5 在 1 和 10 之间，根据搜索逻辑，会找到第二层的索引数据（1，4，7）；
2. 在第二层的索引数据（1，4，7）中进行查找，因为 5 在 4 和 7 之间，所以找到第三层的索引数据（4，5，6）；
3. 在叶子节点的索引数据（4，5，6）中进行查找，然后找到了索引值为 5 的行数据，完成查找。

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。

以一个整数字段索引为例，每个节点大约能存储 1200 条索引数据。当这棵树高是 4 的时候，就可以存 1200^3≈17 亿条记录。所以在一个 10 亿行的表上根据一个整数字段的索引查找一个值最多只需要访问 3 次磁盘。

通常 B+ 树存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 2-3 次磁盘 I/O。

非聚簇索引

Secondary Index，又称二级索引、辅助索引，主键索引的 B+ 树和二级索引的 B+ 树区别如下：

• 主键索引的 B+ 树的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+ 树的叶子节点里；
• 二级索引的 B+ 树的叶子节点存放的是主键值，而不是实际数据。

通过二级索引查询数据的过程：

这里将商品编号字段设置为二级索引，那么二级索引的 B+ 树如下图，其中非叶子的 key 值是 product_no（橙色部分），叶子节点存储的数据是主键值（绿色部分）。

如果用二级索引查询商品，会先检二级索引中的 B+ 树的索引值，检索过程与上文介绍的一致，找到对应的叶子节点获取主键值，然后再通过主键索引中的 B+ 树查询到对应的叶子节点，获取整行数据。这个过程叫「回表」，也就是说要查两个 B+ 树才能查到数据。如下图：

在索引树上，每次只能根据一个主键 id 查到一行数据，所以回表是一行行搜索主键索引的。

按字段特性分类

主键索引

建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引的值唯一且不能为空值。

唯一索引

建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，允许存在多个空值。

由于索引的唯一性，查询性能是最好的。但是在更新数据时，需要额外的查询来保证索引的唯一性，所以写性能会偏低。

由于每次更新前都需要通过查询来判断唯一性，所以唯一索引没有使用 change buffer，所以写性能会低很多。

普通索引

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

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。但是由于前缀索引没有存储一个字段的完整值，所以无法完成排序分组等工作。

全文索引

MySQL5.6 之前，只有 MyISAM 支持，MySQL 5.6 及以后的版本，InnoDB 也支持全文索引。只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

全文索引在做模糊查询的时候性能很高，但是由于会对字段做分词处理，分词结果也会存储在全文索引中，所以会占用很大的空间。

修改字段值后，需要时间分词，所以不会立刻更新全文索引，如果对实时性要求高，则需要手动更新。

一般可以使用 ElasticSearch、Solr、MeiliSearch 等搜索引擎来代替全文索引。

联合索引

联合索引的非叶子节点会按顺序用联合索引的字段作为键值，使用联合索引时，会按照最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

联合索引范围查询

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

select * from t_table where a > 1 and b = 2 在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的，所以联合索引的 b 字段并没有使用到索引。

select * from t_table where a >= 1 and b = 2 和 SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2 都有使用到联合索引。（在边界值时）

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

索引下推

ICP，Index Condition Pushdown。

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引。

• 在 MySQL5.6 之前，只能从一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
• 而 MySQL5.6 引入索引下推优化后，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

覆盖索引

Covering Index，在使用二级索引字段作为条件查询的时候，如果从二级索引中就能查找到需要的数据，而不必再次回表查询，这样就叫做覆盖索引。

示例：

ALTER TABLE `t_user` ADD INDEX `idx1`(`name`,`id`) USING BTREE;
// name 字段为二级索引
select id from t_user where name="木木";

MRR

Multi-Range Read Optimization，是在 MySQL5.6 中引入的性能优化措施，默认开启。通过把「随机磁盘读」转化为「顺序磁盘读」，从而提高了索引查询的性能。

随机磁盘读：在磁盘上随机读取数据，磁盘读取头需要不断移动到不同位置，导致产生大量的磁盘寻道时间，从而降低读取性能。

顺序磁盘读：按照顺序读取数据，磁盘读取头在读取完一个数据块后直接移动到下一个数据块进行读取，读取性能高。

示例：

select * from user where no > 10

如果随着二级索引 no 的值递增顺序查询的话，id 的值就变成随机的，那么回表时就会出现随机磁盘访问，性能相对较差。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

所以就有了 MRR 优化：

1. 根据二级索引 no，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中；
2. 将 read_rnd_buffer 中的 id 进行递增排序；
3. 排序后的 id 数组，依次到主键索引中查记录，并作为结果返回。

索引跳跃式扫描

Index Skip Scan，是 MySQL8.0 引入的优化机制。当没有使用到联合索引的第一个字段且后续索引字段都使用到时，可能会触发索引跳跃式扫描，会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查询。

// 有联合索引(A,B,C)，执行如下SQL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;
// 原本不符合最左前缀原则，不能使用联合索引，但是如果使用了索引跳跃式扫描，则原本的SQL语句会被重构为以下形式：
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "aaa"
......
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、分组操作时无法触发、使用了 DISTINCT 去重无法触发等。

且只有在唯一性较差的情况下，才能发挥出不错的效果；否则相当于走一次全表扫描。不过最后还是由优化器决定是否使用该策略。

执行计划

使用 Explain 工具可以模拟优化器执行 SQL 查询语句，经常用于分析查询语句是否正确使用索引以及排查性能瓶颈。Explain 只能解释 select 语句。

示例：

EXPLAIN SELECT * FROM `zz_users`;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | zz_users | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+

参数

• id：执行的顺序；
• select_type：查询类型；
• partitions：分区信息，非分区表为 null；
• type：数据扫描类型；
• possible_keys：可能用到的索引；
• key：实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len：索引的长度；
• ref：连接查询的连接条件；
• rows：扫描的数据行数；
• extra：其他信息，有几十种不同的值；

select_type

• simple：表示不需要 union 操作或者不包含子查询的简单查询。
• primary：表示最外层查询。
• subquery：子查询中的第一个查询。
• derived：派生表查询，既 from 字句中的子查询。
• union：union 操作中第二个及之后的查询。
• dependent union：union 操作中第二个及之后的查询，并且该查询依赖于外部查询。
• dependent subquery：子查询中的第一个查询，并且该查询依赖于外部查询。
• materialized：物化查询。
• uncacheable subquery：无法被缓存的子查询，对外部查询的每一行都需要重新进行查询。
• uncacheable union：union 操作中第二个及之后的查询，并且该查询属于 uncacheable subquery。

type

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描）；
• index（全索引扫描）：对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大；
• range（索引范围扫描）：一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找；
• ref（非唯一索引扫描）：多表查询时，根据非唯一非空索引进行查询的情况；
• eq_ref（唯一索引扫描）：多表关联查询时，根据唯一非空索引进行查询的情况；
• const（结果只有一条的主键或唯一索引扫描）。
• NULL：无需访问表或者索引，比如获取一个索引列的最大最小值；

全表扫描和全索引扫描的效率很低，要尽量避免。

const 类型和 eq_ref 都使用了主键或唯一索引。const 是与常量进行比较，查询效率会更快；而 eq_ref 通常用于多表联查中。

extra

• Using filesort ：当查询语句中包含 group/order by 操作，且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
• Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，即使用了覆盖索引，避免了回表操作。

索引失效

主要有两种情况会导致索引失效：

1. 优化器选择不使用索引。比如当索引扫描的行数超过表行数的 30% 时，就可能会选择放弃索引查询，转而使用全表扫描的方式，因为这样能够使用磁盘顺序 IO，查询效率更高。
2. 索引的结构满足不了 SQL 语句。比如联合索引没有遵循最左匹配原则。这种情况是我们可以通过调整索引结构和改写 SQL 语句可以避免的，也是我们在进行索引优化时需要考虑的。

以下几种为常见的索引失效的场景：

• 使用左模糊匹配时，即 like %xx 会造成索引失效；
• 在查询条件中对索引列使用计算和函数操作；
• 类型转换操作：比如对字符串字段使用整型作为查询参数（会发生隐式类型转换）；
• 联合索引没有遵循最左匹配原则（包括 group/order by）;
• order by 时，同时使用升序和降序；
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

索引优化

1. 主键索引自增：插入记录时，都是追加数据，可以避免移动数据。也能够避免页分裂的情况发生。
2. 覆盖索引优化：将所有需要查询的字段都建立联合索引，从而避免回表操作。
3. 前缀索引优化：只使用某个字段中字符串的前几个字符建立索引，减小索引字段大小。
   局限性：
   
1. order by 无法使用前缀索引
2. 无法把前缀索引用作覆盖索引

4. 索引设置非空：

1. 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
2. NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用。因为 InnoDB 默认行存储格式 COMPACT，会用 1 字节空间存储 NULL 值列表。

最后

本文介绍了 MySQL 索引相关的内容，最重要的是 InnoDB 是如何通过 B+ 树实现索引的，以及聚簇索引和非聚簇索引的在实现上的区别。在此基础上，就知道了索引是如何工作的，以及如何进行正确使用和优化，避免索引失效情况的发生。

下一节将介绍 MySQL 的事务。

刚设计完表，我咋知道未来会怎么查询表？表设计完后，先不急设计索引，确实你根本还不知道要怎么查表。

该正式业务开发了，根据需求Java业务代码写好，用MyBatis生成各种DAO和Mapper及SQL，开发阶段最后，功能都跑通了，就能开始考虑如何建立索引，因为现在你完全知道对每张表会怎么查询了。针对你的SQL语句里的where条件、order by条件以及group by条件去设计索引，即你的where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by要根据哪些字段来分组聚合？就能设计一或两三个联合索引，每一个联合索引都尽量去包含上你的where、order by、group by里的字段。

接着你就要审查是否每个where、order by、group by后面跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段？

比如你有个联合索引：INDEX(a,b,c)。发现有三个SQL，包含where a=? and b=?，order by a,b，group by a这些部分，此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段，说明你的每个SQL语句都会用上索引。

综上，设计的索引最好让你的各where、order by和group by后面跟的字段都是联合索引的最左侧开始的部分字段，这样他们都能用上索引。

但还得考虑其它问题：

字段基数

有个字段，在10万行数据里有10万个值。结果这10万个值，要么是0，要么是1，则其基数就是2，因为字段值就两个选择，0和1。

对这种字段建立索引，还不如全表扫描，因为索引树仅含0、1，无法进行二分查找，建立索引没有意义。

所以建立索引，尽量使用那些基数较大的字段，即值较多，才能发挥出B+树快速二分查找的优势。

对那些字段类型较小的列来设计索引，比如tinyint之类的，字段类型较小，说明字段本身值占用磁盘空间小，在搜索时性能也会好点。所谓的字段类型小一点的列，也并非绝对，有时你就要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间，那你也得去设计索引，关键尽量别把基数太低的字段包含在索引里。

前缀索引

若真的有那种varchar(255)字段，可能里面的值太大，你觉得都放索引树里太占据磁盘空间了，其实可以换策略，即仅针对这个varchar(255)字段的前20个字符建立索引：对这个字段里的每个值的前20个字符放在索引树里而已。此时建立出来的索引其实类似于KEY my_index(name(20),age,course)，假设name是varchar(255)类型，但是在索引树里你对name的值仅仅提取前20个字符。

此时在where里搜索时，若根据name搜索，就会先到索引树根据name字段的前20个字符搜索，定位到前20个字符的前缀匹配的部分数据后，再回到聚簇索引读完整的name字段值进行比对即可。

但若order by name，则此时你的name因为在索引树里仅包含前20个字符，所以这个排序没法用上索引，group by同理！

函数

where function(a) = xx

给你的索引里的字段a套个函数，就用不上索引了。所以尽量不要让你的查询语句里的字段搞什么函数或计算。

索引设计好后，系统跑起来，有数据插入也有查询，查询基本都能走索引一般问题不大，但插入肯定会更新索引树。插入数据肯定有主键吧，那有主键就得更新聚簇索引树，你插入一条数据肯定会包含索引里各字段的值吧，那你的联合索引的B+树是不是也要更新？随着你不停增删改，就会不停更新索引树。

所以因为你插入的数据值可能根本不按序，很可能导致索引树里的某个页就会自动分裂，页分裂过程很耗费时间，因此设计索引别太多，建议两三个联合索引就应该覆盖掉你这个表的全部查询了，否则索引太多必然导致增删改时性能很差，因为要更新多个索引树。

主键自增

别用UUID等，因为主键自增，至少你的聚簇索引不会频繁分裂， 主键值都有序，就会自然的新增一个页而已，但若UUID，会导致聚簇索引频繁页分裂。

一、介绍

1.什么是索引？

　　一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

2.为什么要有索引呢？

　　　　引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
　　　　非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
　　　　索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
　　　　索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

3.主要优点：

索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

　　　　可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

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

4.缺点：

　　　　创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
　　　　索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间。
　　　　表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
　　　　对于非常小的表，大部分情况下简单的全表扫描更高效

二、索引的本质

　　 索引原理：索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

索引本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

三、MySQL的索引分类

1.普通索引 index :加速查找


2.唯一索引　　与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引（组合索引）　　平时用的SQL查询 语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引 fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引 spatial :了解就好，几乎不用

　案例：

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
# 创建表
CREATE TABLE IF NOT EXISTS emp(emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
salary INT,
dept_id INT,
manager_id INT
);

#　插入数据
INSERT INTO emp (emp_name,salary,dept_id,manager_id)VALUES
("张三",1000,1,1),
("李四",2000,1,1),
("王五",5000,2,3),
("林青霞",8000,3,6),
("风清扬",6000,5,4),
("花无缺",10000,5,5),
("景天",14000,8,7),
("赵敏",17000,8,7);
# 给一个列的每个值 创建单独标记 利用索引创建的这些标记可以进行一个排序，更快查询 
SELECT * FROM emp WHERE emp_name = "李四";

# 1.1 创建索引:单独创建索引
CREATE INDEX index_name ON emp(emp_name);
SELECT * FROM emp WHERE emp_name = "李四";

# 1.2 创建索引：修改表结构创建索引
ALTER TABLE emp ADD INDEX index_salary(salary);

# 1.3 创建索引：创建表时，创建索引
CREATE TABLE IF NOT  EXISTS article(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30) NOT NULL,
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title(title)
);

# 2 删除索引
DROP INDEX index_name ON emp;

# 3.1 唯一索引：索引标记的列的值必须唯一
CREATE UNIQUE INDEX index_name ON emp(emp_name);

# 3.2 修改表时创建唯一索引
ALTER TABLE article ADD UNIQUE INDEX index_content(content);

# 3.3 创建表时创建唯一索引
CREATE TABLE IF NOT EXISTS test (id INT NOT NULL AUTO_INCREMENT ,
tname VARCHAR(20) NOT NULL,
age INT NOT NULL,
s_id INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX index_id(s_id)
) 
 
# 4.1 单独创建组合索引
CREATE INDEX index_ct ON article(content,dt);
 SELECT * FROM article WHERE content='123' AND dt = "2020-10-10"

# 4.2 修改表结构时创建组合结构
ALTER TABLE test  ADD INDEX index_to(tname,age);

# 4.3 创建表时创建组合索引
CREATE TABLE IF NOT  EXISTS test02(id INT NOT NULL AUTO_INCREMENT,
tname VARCHAR(20) NOT NULL,
age INT NOT NULL,
s_id INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX index_to(s_id,id)
)

四、复合索引

一列的索引称为单列索引，多列的称为复合索引，因为BTREE索引是顺序排列的，所以比较适合范围查询，但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

# 建表create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
  ) charset utf8 comment '员工记录表';# 添加三列的复合索引alter table staffs add index idx_nap(name, age, pos);#1. 全值匹配select * from staffs where name = 'July' and age = '23' and pos = 'dev' ;#2. 匹配最左列    对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列。select * from staffs where name = 'July' and age = '23';   # key字段显示用到了索引，注意，key_len字段（表示本次语句使用的索引长度）数值比上一条小了，意思是它并未使用全部索引列，事实上只用到了name和age列#3. 匹配列前缀select * fromstaffs where name like 'J%'；# 即一个索引中列的前一部分，主要用在模糊匹配#4. 匹配范围select * from staffs where name > 'Mary'；#5. 精确匹配一列并范围匹配右侧相邻列select * from staffs where name = 'July' and age > 25；# 即前一列是固定值，后一列是范围值，它用了name与age两个列的索引（key_len推测）#6. 只访问索引的查询
　select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';
　select name,age from staffs where name = July and age > 25;
　# 比如staffs表的情况，索引建立在(name,age,pos)上面，前面一直是读取的全部列，如果我们用到了哪些列的索引，查询时也只查这些列的数据，就是只访问索引的查询

如果感觉小编写得不错，请素质三连：点赞+转发+关注。我会努力写出更好的作品分享给大家。更多JAVA进阶学习资料小编已打包好，可以关注私信找我领取哦！

索引

定义

索引是对数据库表中一列或者多列的值进行排序的结构。

目的

数据库索引好比一本书的目录，提高查询效率。但是为表设置索引要付出相应的代价：

• 增加了数据库的存储空间
• 在插入和修改时需花费更多的时间（因为索引也要随之变动）

分类

1. 聚集索引

索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储其真实的数据行，不再有另外单独的数据页。

2. 非聚集索引

表数据存储顺序与索引顺序无关。对于非聚集索引，叶子结点包含索引字段值和数据页数据行的地址，其行数量与数据表中行数量一致。

注意：一个表中只有一个聚集索引，但是可以有多个非聚集索引。

3. 唯一索引

不允许具有索引值相同的行，但是可以为 NULL，不能有多个 NULL。

4. 主键索引

是唯一索引的特殊类型。数据库表中经常有一列或多列组合，其值唯一标识表中的每一行，该列称为表的主键。

在数据库中为表定义主键将自动创建主键索引。

索引存储结构

B 树

对于 m 阶 B 树，有如下性质：

• 根节点至少有 2 个孩子节点
  
• 树中每个节点最多含有 m 个孩子（m >= 2）
  
• 除根节点、叶子节点外其他节点至少有 ceil(m/2) 个孩子
  
• 所有叶子节点都在同一层
  
• 假设每个非终端节点中包含 n 个关键字信息，其中
  a）Ki(i=1..n)为关键字，being且找顺序升序排序 K(i-1) < Ki
  b）关键字的个数 n 必须满足：ceil(m/2)-1 <= n <= (m-1)
  c）非叶子节点的指针：P[1]，P[2]，... ，P[M]；其中 P[1] 指向关键字小于 K[1] 的子树，P[M] 指向关键字大于 K[M-1] 的子树，其他 P[i] 关键字属于(K[i-1],K[i]) 的子树
  

B+ 树

B+ 树是 B 树的变体，其定义基本与 B 树相同，除了：

• 非叶子节点的子树指针和关键字个数相同
  
• 非叶子节点的子树指针 P[i]，指向关键字值 [K[i],K[i+1]) 的子树
  
• 非叶子节点仅用来索引，数据都保存在叶子节点
  
• 所有叶子节点均有一个链指针指向下一个叶子节点
  

数据库系统普遍采用 B+ 树作为索引结构，主要有以下原因：

• B+ 树的磁盘读写代价更低
  因为非叶子结点只存储索引信息，其内部节点相同 B 树更小，如果把 key 存入同一盘块中，盘块所能包含的 key 越多，一次性读入内存中需要查找的 key 就越多，相对来说磁盘的 I/O次数就减少了。
  举个例子：假设磁盘中的一个盘块容纳 16 字节，而一个 key 占 2 字节，一个 key 具体信息指针占 2 字节。一棵 9 阶 B 树(一个结点最多 8 个关键字)的内部结点需要 2 ( (8*(2+2) / 16 = 2)个盘块。B+ 树内部结点只需要 1 （8 * 2 / 16 = 1）个盘块。当需要把内部结点读入内存中的时候，B 树就比 B+ 树多 1 次盘块查找时间。
  
• B+ 树的查询效率更加稳定
  由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
  
• B+ 树更有利于对数据库的扫描
  B+ 树只要遍历叶子结点就可以遍历到所有数据。
  

HASH

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应位置，速度非常快。

哈希索引底层的数据结构是哈希表，能以 O(1) 时间进行查找，但是失去了有序性；因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。

哈希索引的不足：

• 无法用于排序与分组
• 只支持精确查找，无法用于部分查找和范围查找
• 不能避免全表扫描
• 遇到大量 Hash 冲突的情况效率会大大降低

索引的物理存储

MySQL 索引使用的是 B 树中的 B+ 树，但索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

MyISAM 索引存储机制

MyISAM 引擎使用 B+ 树作索引结构，叶子节点的 data 域存放的是数据记录的地址，所有索引均是非聚集索引。

上图是一个 MyISAM 表的主索引（Primary key）示意图。

假设该表一共有三列，以 Col1 为主键。MyISAM 的索引文件仅仅保存数据记录的地址。

在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果在 Col2 上建立一个辅助索引，则该辅助索引的结构如下：

同样也是一棵 B+ 树，data 域保存数据记录的地址。

MyISAM 中首先按照 B+ 树搜索算法搜索索引，如果指定的 key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。 MyISAM 的索引方式也叫做非聚集索引（稀疏索引）（索引和数据是分开存储的）。

InnoDB 索引存储机制

InnoDB 也使用 B+ 树作为索引结构。有且仅有一个聚集索引，和多个非聚集索引。

InnoDB 的数据文件本身就是索引文件。MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+ 树组织的一个索引结构，这棵树的叶子节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

上图是 InnoDB 主索引（同时也是数据文件）的示意图。可以看到叶子节点包含了完整的数据记录。

这种索引叫做聚集索引（密集索引）（索引和数据保存在同一文件中）：

• 若一个主键被定义，该主键作为聚集索引；
• 若没有主键定义，该表的第一个唯一非空索引作为聚集索引；
• 若均不满足，则会生成一个隐藏的主键（ MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段是递增的，长度为 6 个字节）。

与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。例如，定义在 Col3 上的一个辅助索引：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索 2 遍索引：

首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

注意 InnoDB 索引机制中：

• 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
  
• 不建议用非单调的字段作为主键，因为 InnoDB 数据文件本身是一棵 B+ 树，非单调的主键会造成在插入新记录时数据文件为了维持 B+ 树的特性而频繁的分裂调整，十分低效。
  使用自增字段作为主键则是一个很好的选择。
  

索引的优点

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

建索引的原则

• 最左前缀匹配原则
  MySQL 会一直向右匹配知道遇到范围查询（>、<、between、like）就停止匹配。比如 a=3 and b=4 and c>5 and d=6，如果建立 (a,b,c,d) 顺序的索引，d 就是用不到索引的，如果建立(a,b,d,c) 的索引则都可以用到，并且 a,b,d 的顺序可以任意调整。
  
• = 和 in 可以乱序
  比如 a = 1 and b = 2 and c = 3建立 (a,b,c) 索引可以任意顺序，MySQL 的查询优惠器可进行优化。
  
• 尽量选择选择度高的列建索引
  # 选择度计算
  SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
  COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
  COUNT(*)
  FROM payment;
  
  
• 使用 like 进行模糊查询时，如果已经建立索引，第一个位置不要使用 '%'，否则索引会失效。
  
• 当检索性能远远大于检索性能时，不应该建立索引。
  

索引失效

• 最左前缀匹配原则，遇到范围查询
  
• like 模糊查询，第一个位置使用 '%'
  
• 没有查询条件
  
• 表比较小时，全表扫描速度比索引速度快时，索引失效
  （由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。）
  

承接上文 Mysql Server原理简介

聚簇索引、二级索引、联合索引分别具备什么样的特点？

聚簇索引

数据跟索引放在一起的叫聚簇索引；

数据和索引分开存储的叫非聚簇索引；

innodb存储引擎，数据和文件都放在ibd文件中，实际的数据是跟索引绑定在一起的，如果表中有主键，那么跟主键绑定，如果没有主键那么跟唯一键绑定，如果没有唯一键，和6个字节的rowid（6字节的随机字符串）进行绑定。

6字节的rowid是隐藏的，看不到的，实际的mysql数据行中包含了非常多的隐藏字段。

MyISAM是非聚簇索引，因为数据文件和索引文件是分开存放的。

innodb中有聚簇索引也有非聚簇索引。

一个表可以有N个索引，每一个索引都是一颗B+树，每个B+树都是独立的，一个表中会存在多颗B+tree。

表中的数据存储几份?

叶子节点存储的数据行存了一份。

如果存一份的话，就会存在一个问题，因为一个表里面会包含N多个B+ tree：

数据只存储一份，其他的非聚簇索引的叶子节点中存储的是聚簇索引的key值，

所以innobdb中也包含了非聚簇索引。

这是一个表数据，id是主键索引，name是普通索引，

id主键索引，叶子节点存储的是数据，所以id是聚簇索引；

name是普通索引，叶子节点存储的是id值，所以name B+ tree是一个非聚簇索引、二级索引或叫辅助索引。

索引是一个具体的物理结构；

如果表中唯一键有多个，没有主键，那么此时聚簇索引按照唯一键的前后顺序创建。

多个列建的索引叫联合索引或复合索引

一般情况下，设置索引列的时候，只会选择一个列作为索引字段，但是在某些特殊情况下，需要将多个列共同组成一个索引字段，称之为联合索引。

MyISAM索引都是辅助索引，没有聚簇索引，都是用来辅助查询的。

在索引优化的时候需要注意什么问题?

• 索引字段要尽可能小的占用存储空间
• 在满足业务系统的需求内尽可能自增
• 索引字段尽可能不要为null，因为很多情况下null并不等于空
• 选择索引的时候，索引的基数尽可能大，到底给哪些列建立索引，DV/count >= 80%适合创建索引，distinct value唯一值除以count，不重复的值要尽可能多
• 不要给所有字段添加索引，并不是索引越多越好

什么情况下会导致索引失效？

• 索引字段尽量不要频繁修改
• like查询的时候左边不要加%
• 索引字段不要添加任何表达式操作
• 索引字段在使用的时候不要出现类型的隐式转换

• 索引上不要出现函数计算
• 组合索引在进行使用的时候要遵循最左匹配原则
• in或or在很多情况下会导致索引失效，但是要根据实际的情况来进行判断
• 在使用索引的时候，如果中间的某个索引列使用了范围查询，会导致后续的索引失效

回表

select * from table where name='zhangsan'，

id是主键，name是普通索引， 先根据name值去name B+树找到对应的叶子节点，取出id值，再根据id值去id B+树中查找全部的结果，这个过程称为回表，回表的效率比较低，尽可能不要使用，避免回表的产生，因为需要回到原来的表里查询对应的数据记录。

索引覆盖

先根据name值去name B+树查找结果，能够直接获得id和name，不需要去id的B+树查找数据了，这个过程叫索引覆盖即索引的叶子节点中包含了要查询的全部数据，推荐使用索引覆盖。

最左匹配原则

id是主键，name、age是组合索引，在查找的时候必须从左往右匹配。第一和第三个sql符合该原则；

如果把age和name的顺序换下，不会影响最终的查询结果，因为这时候优化器会优化，调整对应的顺序，所以也会匹配该原则；在比较数据的时候，先比较第一个，再比较第二个，因为只有第一个相同了，才有比较第二个的可能。

索引下推

select * from table where name=? and age=?，没有索引下推前，先根据name的值从存储引擎中拿到符合条件的数据，然后在server中对age进行数据过滤，有了索引下推之后，直接根据name和age从存储引擎中筛选对应的数据，返回给server，不需要做索引过滤；原来在server层需要对age做过滤，现在下推到了存储引擎层过滤数据。

mysql 5.7之后默认开启索引下推，两个字段一起筛选，筛选的数据量少了，io量也有少了。

Hello大家好，我是枫哥。今天整理了一些关于MySQL索引的面试知识点，分享给大家。

1问：列举一些导致索引失效的场景？

1答：

• 查询条件包含or导致后面的索引失效
• like左模糊
• 字符串类型没加单引号
• 联合索引，查询时的条件列不是联合索引中的第一个列
• 在索引列上使用mysql的内置函数
• 对索引列运算（如，+、-、*、/）
• 索引字段上使用！= 或者 < >，not in
• 发生隐式类型转换会导致索引失效
• mysql估计使用全表扫描要比使用索引快,则不使用索引。这个是mysql自己决定的，发现后我们可以使用强制索引的语法让MySQL强制走索引

2问：索引不适合哪些场景？

2答：

• 表里数据量很少
• 更新频繁的字段
• 区分度很低的字段

3问：为什么用B+树而不是B树或二叉树或平衡二叉树？

3答：

首先，为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

其次：为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来了，查询效率就快了。

最后：那为什么不是B树而是B+树呢？

1）B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快。

2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

4问：聚簇索引与非聚簇索引的区别？

4答：

聚簇索引：索引的叶节点就是数据节点。所以不需要回表。主键id就是聚簇索引，有且仅有一个。

非聚簇索引：叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。要想更多的数据需要回表查，可以有多个非聚簇索引。

5问：如何写sql能够有效的使用到复合索引？

5答：确保最左匹配原则有效。

6问：Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

6答：

• B+树可以范围查询，Hash不行
• B+树可以复合索引的最左侧原则，Hash不行
• B+树可以支持order by排序，Hash不行
• B+树在等值查询上的效率不如Hash
• B+树支持右模糊查询走索引，hash索引根本无法进行模糊查询

7问：索引有哪几种类型？

7答：

• 主键索引：也是聚簇索引，一张表只能有一个 ，不允许为null且唯一，查询的时候不需要回表。
• 唯一索引：不允许为null且唯一。一张表可以有多个。
• 普通索引：普通索引。
• 全文索引：全文检索用，对文本内容进行分词搜索。
• 覆盖索引：select的字段正好是索引字段，这时候不需要回表操作。
• 组合索引：多个列值组成一个索引，使用的时候要遵循最左匹配原则。

8问：创建索引有什么原则呢？

8答：

• 最左前缀匹配
• 频繁作为查询条件的字段
• 繁更新的字段不适合
• 索引列不能参与计算和使用一些函数
• 优先考虑索引组合，而不是每次都新建索引
• 在order by或group by字句中，使用索引要遵循最左前缀匹配
• 区分度低的列不适合
• 大数据类型的列不适合（比如text等）

9问：什么是最左前缀原则？什么是最左匹配原则？

9答：

就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

比如

有组合索引（a,b），那么使用的时候只写where b = xxx会导致索引失效，因为a在前面丢了，但是写成这样where b = x and a = x;这样索引是可以生效的，因为优化器阶段会给优化成where a = x and b = x，不会让索引失效。

10问：覆盖索引、回表等这些，了解过吗？

10答：

• 覆盖索引：查询列就是所使用的索引列，这样不需要回表查询。
• 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

11问：使用索引查询一定能提高查询的性能吗？为什么？

11答：通常情况下是可以的，但是也有特例，比如你在区分度不高的字段上使用索引（比如性别），那就未必能提升性能，因为索引也需要物理存储空间的。

12问：count(1)、count(*) 与 count(列名) 的区别？

12答：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

效率上MySQL对count(*)做过优化， count(1)≈count(*)>count(列名)

13问：列值为NULL时，查询是否会用到索引？

13答：列值为NULL也是可以走索引的。

但是最好限制 not null ，并设置一个默认值，比如 0 和 '' 空字符串等，因为null有坑，比如where xx != '1';这时候x=null的值是不会被检索出来的，需要写成才可以：where xx != '1' or xx is null;

14问：非聚簇索引的查询都需要回表吗？

14答：不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询，比如覆盖索引。

15问：什么是索引下推？

15答：MySQL5.6新做的优化。

索引下推可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，比如：

在MySQL 5.6之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值是不是张开头且is_del=1。

在MySQL 5.6之后，索引下推可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，也就是回表查询的时候只回表查询有效结果集，也就是姓张的且is_del=1的这些数据进行回表，简单来说就是先过滤好需要的结果，然后去回表查询全部字段。

16问：频繁更新索引字段可以吗？为什么？

16答：可以但不推荐，因为每个索引都是一棵B+树，是按照大小排序的，更新索引字段的话可能会造成多个数据页的数据之间挪动。

17问：为什么group by 和 order by会使查询变慢 ？

17答：group by 和 order by操作通常需要创建一个临时表来处理查询的结果，所以如果查询结果很多的话会严重影响性能。

18问：怎么优化order by？

18答：

order by原理

两次扫描算法

1、排序时，只将排序列和主键值放入sort buffer中进行排序处理，此步骤为第一次扫描，顺序IO；

2、若sort buffer可以一次性存储所有需要排序字段，则直接在sort buffer中进行排序；

3、若sort_buffer_size无法满足一次性存储全部的排序字段，则会将每次读取到sort buffer中的排序记录固化到磁盘，多路归并排序算法，保证临时文件中记录是有序的。

4、根据排好序的记录通过主键回表查询，读取需要的表数据。由于此时是根据排序字段进行排序的，通过主键回表查询会产生大量的随机IO，所以MySQL会将这些记录放至缓冲区按照主键进行排序，缓冲区大小由read_rnd_buffer_size控制，最终通过排好序的主键进行回表扫描查询，此为第二次扫描。

5、一般需要排序记录较大超过max_length_for_sort_data设置值或者查询select中包含BLOB或者TEXT类型字段的情况下会使用该算法进行排序。

一次扫描算法

1、排序时，将查询的所有列（排序列以及非排序列）全部放入sort buffer进行排序，此为一次扫描；

2、若sort_buffer_size不够大，需要将每次排好序的记录固化到磁盘；

3、按照排好序的记录直接输出结果；

4、该算法下，只需要扫描一次表数据，避免了回表查询。只有当需要排序的记录小于max_length_for_sort_data定义参数大小时，MySQL才会优先使用一次扫描算法。

怎么优化order by呢？

• 在使用order by时，不要用select *，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。
• order by 里的字段可以组合索引，但是要么都加升序要么都加降序，不能部分字段升序，部分字段降序，这样走不了索引。
• 调大参数

增加sort_buffer_size参数的设置。
增大max_length_for_sort_data参数的设置。

19问：怎么优化group by？

19答：

先说说group by原理

• 1.创建内存临时表，表里有三个字段t , s , school_id。
• 2.扫描表dt_school主键索引，依次取出节点school_id和tea_reg, stu_reg，和time 的值。
• 3.如果time的值不在获取的时间范围内，丢弃。判断内存表中是否已经有school_id值的行，如果没有插入(school_id,tea_reg,stu_reg), 如果有在对应的行加上tea_reg 和stu_reg的值。
• 4.如果依次往内存表插数据的时候,发现内存表已满。新起一个innodb引擎的磁盘临时表，将数据挪到磁盘临时表中。
• 5.将对临时表的school_id排序，将结果集返回给客户端。

其实5这个步骤也不是必须的 。当使用索引school_id来遍历时，插入临时表的数据就默认是有序的，这就是为何数据量一大优化器就要选择school_id索引，因为它总是认为排序是耗时的，而使用school_id是不需要排序的。

上面这个流程，比较傻的就是第4步了，当内存临时表不够用时，我们再把内存临时表的数据挪到磁盘临时表。而使用SQL_BIG_RESULT的时候，优化器就会直接使用磁盘临时表。

对于

这个语句的执行流程是这样的：

• 1. 扫描表dt_school主键索引，依次取出节点school_id和tea_reg, stu_reg，和time 的值。
• 2. 如果time的值不在获取的时间范围内，丢弃。否则插入sort buffer中，如果sort_buffer 不够用，直接使用磁盘临时文件辅助排序。
• 3. 对sort buffer 的数据对school_id 进行排序 。
• 4. 扫描sort buffer 的数据，返回聚合结果。

这也就是为啥查看SQL_BIG_RESULT的语句的执行计划，Extra选项的值，没有使用临时表，但是需要using filesort 。当然我们加上这个school_id索引的话，这个filesort排序也不需要了。

怎么优化group by ？

• 索引采取，遵循最左前缀匹配就行。
• 采取SQL_BIG_RESULT（可以避免采取临时表，直接磁盘文件），比如 select SQL_BIG_RESULT sum(x) from table where a = 1 group by x；

20问：Join算法原理

20答：

整理于，如有侵权请联系~

https://zhuanlan.zhihu.com/p/54275505

21问：Simple Nested-Loop Join 简单的嵌套循环连接

21答：

简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果，当执行 select * from user tb1 left join level tb2 on tb1.id=tb2.user_id; 时，我们会按类似下面代码的思路进行数据匹配：

整个匹配过程会如下图：

特点：

Nested-Loop Join 简单粗暴容易理解，就是通过双层循环比较数据来获得结果，但是这种算法显然太过于粗鲁。

如果每个表有1万条数据，那么对数据比较的次数=1万 * 1万 =1亿次，很显然这种查询效率会非常慢。

当然mysql 肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-Loop Join 优化算法，在执行join 查询时mysql 会根据情况选择 后面的两种优join优化算法的一种进行join查询。

22问：Index Nested-Loop Join 索引嵌套循环连接

22答：

Index Nested-Loop Join其优化的思路 主要是为了减少内层表数据的匹配次数， 简单来说Index Nested-Loop Join 就是通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数，从原来的匹配次数=外层表行数 * 内层表行数,变成了外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。

举个例子：

如SQL：select * from user tb1 left join level tb2 on tb1.id=tb2.user_id;

当level 表的 user_id 为索引的时候执行过程会如下图：

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

23问：Block Nested-Loop Join 缓存块嵌套循环连接？

23答：

Block Nested-Loop Join 其优化思路是减少内层表的扫表次数，通过简单的嵌套循环查询的图，我们可以看到，左表的每一条记录都会对右表进行一次扫表，扫表的过程其实也就是从内存读取数据的过程，那么这个过程其实是比较消耗性能的。

所以缓存块嵌套循环连接算法意在通过一次性缓存外层表的多条数据，以此来减少内层表的扫表次数，从而达到提升性能的目的。

如果无法使用Index Nested-Loop Join的时候，数据库是默认使用的是Block Nested-Loop Join算法的。

当level 表的 user_id 不为索引的时候，默认会使用Block Nested-Loop Join算法，匹配的过程类似下图。

注意：

使用Block Nested-Loop Join 算法需要开启优化器管理配置的 optimizer_switch的设置block_nested_loop为on 默认为开启，如果关闭则使用Simple Nested-Loop Join 算法；

通过指令：Show variables like 'optimizer_switc%'; 查看配置。

Join算法总结

不论是Index Nested-Loop Join 还是 Block Nested-Loop Join 都是在Simple Nested-Loop Join的算法的基础上进行优化，这里 Index Nested-Loop Join 和Nested-Loop Join 算法是分别对Join过程中循环匹 配次数和IO 次数两个角度进行优化。Index Nested-Loop Join 是通过索引的机制减少内层表的循环匹配次数达到优化效果，而Block NestedLoop Join 是通过一次缓存多条数据批量匹配的方式来减少内层表的扫表IO次数，通过 理解join 的算法原理我们可以得出以下表连接查询的优化思路。

24问：如何优化join？

24答：

• 关联条件列建立索引
• 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
• 为匹配的条件增加索引(减少内层表的循环匹配次数)
• 增大join buffer size的大小

• 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

25问：从innodb的索引结构分析，为什么索引的key长度不能太长？

25答：key 太长会导致一个页当中能够存放的key的数目变少，间接导致索引树的页数目变多，索引层次增加，从而影响整体查询变更的效率。

最近在更新过关斩将系列—java面试题，全力帮助小白突破面试大关，目前已经更新至第16篇感兴趣可以关注IT枫斗者追更！干货满满！

《过关斩将之路》JAVA面试八股文

《过关斩将》系列是我一题一题整理出来的，质量非常高，小伙伴也反映看过之后面试能力直线上升！

总共涵盖了Spring、SpringMVC、Redis、分布式、多线程、JVM、Linux、docker、MQ等常见高频面试题，

跟线上其他粗制滥造的八股文还是有区别的。

《过关斩将》系列面试题整理的更系统，覆盖面更广，不仅有精挑细选出来的高频，而且每一个模块都包含开放式问答：面试场景相关，结合项目的问题总结。

比如：在项目中遇到缓存雪崩的问题你是如何解决的？

我作为面试官的时候也是喜欢结合项目问八股文，也有一些面试官会先问一些java基础的问题，所以，跳槽的时候八股文是必不可少的一项。

当然，面试当然就是根据简历上的技能特长来提问，能够写出来的技术栈，肯定是自己比较熟悉掌握的，要不然只会增加面试的不确定性。

《过关斩将之路》JAVA面试八股文

• HR
• 【过关斩将】HR高频
• MySQL
• 【过关斩将】MySQL索引
• 【过关斩将】MySQL基础和日志相关
• 【过关斩将】MySQL主从复制&&读写分离
• 【过关斩将】MySQL存储相关&&存储引擎
• 【过关斩将】MySQL事务&&锁&&MVCC
• Redis
• 【过关斩将】Redis事务
• 【过关斩将】Redis持久化
• 【过关斩将】Redis主从集群
• 【过关斩将】Redis场景问题
• 【过关斩将】Redis基础&&过期淘汰策略
• Spring
• 【过关斩将】Spring原理
• 【过关斩将】SpringIOC原理
• 【过关斩将】Spring Beans
• 网络基础
• 【过关斩将】网络基础

《过关斩将》还在持续更新中，希望能帮助到更多的小伙伴拿到offer，走上人生巅峰！感兴趣可以 点击这里进行追更！

要回答索引是怎么实现的，首先要搞清楚索引有哪些类型，不同类型的索引的实现方式和适用场景也是不一样的，从实现方式上来讲，索引可以分为:

1. B+树索引
2. 哈希索引
3. 空间索引（例如 R-树）
4. 全文索引
5. Bitmap索引

具体可参考：

一、什么是MySQL索引？
想象一下，你正在图书馆找一本特定的书。如果没有索引，你需要走过每一个书架，查看每一本书的标题，这会非常耗时。但如果有一个索引卡片，告诉你每本书的位置，你就可以直接走到那本书所在的书架，快速找到你想要的书。在MySQL数据库中，索引就类似于这个索引卡片，它帮助数据库快速定位到存储在表中的数据。
索引的好处

• 快速查找：就像索引卡片帮助快速找到图书馆的书一样，数据库索引可以加快查找数据的速度。
• 数据排序：索引可以帮助数据按照一定的顺序排列，这样当你需要按顺序查看数据时，数据库就可以更高效地提供。
• 提高效率：在执行数据库查询时，索引可以让数据库系统更快地完成任务，提高整体的工作效率。

索引的坏处

• 创建索引就像建立图书馆的索引卡片系统，需要额外的空间和资源。在数据库中，这意味着需要更多的存储空间和时间来维护索引。
• 当你在图书馆中添加或移除书籍时，索引卡片也需要更新。同样，在数据库中，当你添加、修改或删除数据时，索引也需要更新，这会增加额外的工作。
• 对于一些小的表或者不常被查询的表，索引可能不会带来太大帮助，有时候甚至可能因为维护索引而降低性能。

二、索引为什么会快？
1. 高效的数据结构:
索引使用的数据结构（如B+ree）允许快速地在磁盘上存储和检索数据。这种结构支持快速的插入、删除和查找操作，因为它总是保持平衡，确保任何数据的查找路径长度都大致相同。

这就像是拥有一个详尽的目录，可以迅速定位到书籍在图书馆中的位置，而不需要逐个书架查找。

2. 减少全表扫描:
当没有索引时，数据库必须执行全表扫描来查找满足查询条件的行，这称为表扫描。全表扫描需要逐行读取整个表的数据，对于大型表来说非常耗时。有了索引，数据库可以快速定位到相关的数据行，大大减少了需要读取的数据量。

例如，如果你有一个包含数百万行的订单表，并且根据订单日期进行查询，那么在订单日期列上创建索引将大大减少查询时间，因为数据库可以直接跳到相关日期的数据，而不是扫描所有行。

3. 磁盘I/O优化:
索引文件通常比实际的数据文件小，因为它们只包含关键信息和指向数据的指针。这意味着数据库在执行查询时，可以更快地从磁盘读取索引文件。较小的索引文件也更容易被缓存到内存中，从而减少对磁盘的访问次数。

例如，当查询一个特定ID的用户信息时，如果ID列上有索引，数据库可以快速读取索引并找到用户信息的位置，而不需要从表的开始处逐行读取。

4. 缓存效率:
索引提高了数据访问的局部性，使得相关的数据更有可能被同时缓存到内存中。当多个查询访问相同的数据时，这些数据可以被缓存，从而避免了重复的磁盘I/O操作。

例如，如果多个用户同时查询同一天的交易记录，而这一天的记录已经被索引并缓存，那么后续的查询可以直接从内存中获取数据，而不需要再次访问磁盘。

5.排序和分组:
索引还可以帮助数据库引擎在不需要额外排序操作的情况下返回有序的结果集。这是因为索引本身就按照某种顺序存储数据。

例如，如果你经常查询按照销售额降序排列的前十个销售代表，那么在销售额列上创建索引可以让数据库快速返回排序后的结果，而不需要对所有结果进行额外的排序处理。

三、索引为什么使用B+树？
B+树作为MySQL中InnoDB存储引擎的默认索引结构，因其独特的特性，在处理大量数据时提供了高效的查询性能。

1. 树的矮胖结构： B+树的非叶子节点仅存储键值，不存储实际数据。这种设计使得每个节点能够容纳更多的键值，从而降低了树的高度。在16KB的页大小下，B+树可以存储更多的索引项，使得树更矮更胖，减少了查找数据时的磁盘I/O次数，提高了查询效率。
2. 高效的范围查询： B+树的所有数据都存储在叶子节点，并且数据之间通过链表连接，形成了一个有序的结构。这使得范围查询、排序查找、分组查找以及去重查找变得非常简单和高效。相比之下，B树需要在多个节点间进行搜索，效率较低。
3. 稳定的磁盘I/O性能： 由于所有数据都存储在叶子节点，B+树的I/O次数在查询时更加稳定。这意味着即使在数据量不断增长的情况下，B+树索引的性能也不会出现大幅波动。
4. 强大的全局扫描能力： B+树的叶子节点存储了所有数据，并且通过链表连接，这使得全局扫描（全表扫描）操作只需要遍历叶子节点即可完成。与B树相比，后者需要遍历整个树结构，效率较低。
5. 优化的数据插入策略： 使用自增的整型数据作为主键可以减少数据插入时叶子节点分裂的问题，因为新插入的数据会自然地被添加到链表的末尾，避免了频繁的节点分裂和数据重组，从而提高了数据插入的效率。

四、索引失效场景
在使用MySQL数据库时，索引是提高查询效率的重要工具。然而，在某些情况下，索引可能不会生效，导致查询性能下降。以下是一些可能导致索引失效的常见场景，以及优化后的描述：

1. 使用OR条件： 当查询条件中包含OR时，MySQL可能无法有效地使用索引，因为它需要检查多个条件中的每一个，这可能导致全表扫描。
   
2. 字符串字段未用引号括起来： 如果查询条件中的字符串字段没有用单引号括起来，MySQL可能无法正确匹配索引中的值，从而导致索引失效。
   
3. 使用LIKE通配符： 当使用LIKE操作符时，尤其是当通配符位于字符串的开始位置（例如%keyword），MySQL可能无法利用索引进行快速查找。
   
4. 联合索引的条件列顺序问题： 如果查询条件中使用的列不是联合索引中的第一个列，MySQL可能不会使用索引，因为索引的使用依赖于查询条件与索引列的顺序匹配。
   
5. 在索引列上使用内置函数： 对索引列应用MySQL内置函数，如DATE()或UPPER()，会使得MySQL无法直接使用索引进行查找。
   
6. 索引列上的运算： 在索引列上执行算术运算（如加、减、乘、除）会使得MySQL无法利用索引进行数据查找。
   
7. 使用不等于或范围查询： 使用!=、<>、NOT IN等操作符进行查询时，MySQL可能不会使用索引，尤其是当这些操作符用于索引列的开头时。
   
8. 索引字段上的NULL检查： 使用IS NULL或IS NOT NULL检查索引字段可能导致索引失效，因为MySQL可能无法直接定位到NULL值的位置。
   
9. 连接查询中的字段编码不一致： 在左连接或右连接查询中，如果关联的字段编码格式不一致，MySQL可能无法使用索引进行有效的数据匹配。
   
10. MySQL优化器的选择： MySQL优化器会根据表的大小和索引的选择性来决定是否使用索引。如果优化器估计全表扫描比使用索引更快，它将选择全表扫描。
    

五、索引类型
索引是数据库中用于提高数据检索速度的重要工具。在MySQL中，有多种类型的索引，每种索引都有其特定的用途和优化场景。
主键索引：

• 主键索引是唯一的，不允许数据重复，并且不允许为NULL。
• 一个表中只能有一个主键索引，通常用于唯一标识表中的每条记录。
• 例如，用户表中的UserID列，每个用户都有一个唯一的ID，适合作为主键索引。

唯一索引：

• 唯一索引确保索引列中的值唯一，但允许有空值（NULL）。
• 一个表可以有多个唯一索引，适用于需要确保数据唯一性但允许某些记录值缺失的场景。
• 例如，在订单表中，OrderNumber列可以设置为唯一索引，以确保每个订单号只出现一次。

普通索引：

• 普通索引是最基本的索引类型，没有唯一性要求，允许重复值和NULL值。
• 适用于大多数查询场景，可以显著提高查询速度。
• 例如，如果经常根据CreatedAt列查询最近的记录，可以在此列上创建普通索引。

全文索引：

• 全文索引用于对文本内容进行高效搜索，支持分词和模糊匹配。
• 适用于搜索引擎和需要对大量文本数据进行搜索的场景。
• 例如，博客平台可以在文章内容上创建全文索引，以便用户能够通过关键词搜索相关文章。

覆盖索引：

• 覆盖索引是指查询中所需的所有列都包含在索引中，这样数据库引擎可以直接从索引中获取数据，无需访问数据行。
• 适用于查询只涉及索引列的情况，可以减少I/O操作，提高查询效率。
• 例如，如果查询经常只访问UserName和Email两列，可以在这两列上创建一个覆盖索引。

组合索引：

• 组合索引由多个列的值组成，用于优化多列的组合查询。
  
• 适用于经常需要根据多个列进行查询的场景，其效率通常高于单独为每个列创建索引。
  
• 例如，如果经常根据Country和City列进行查询，可以在这两个列上创建一个组合索引
  

六、聚簇索引和非聚簇索引
在MySQL的InnoDB存储引擎中，聚集索引（Clustered Index）是一种特殊类型的索引，它定义了表中数据的物理存储方式。聚集索引是基于表的主键构建的，因此每个表只能有一个聚集索引。在InnoDB中，数据行实际上是存储在B+树的叶子节点中的，这意味着聚集索引不仅定义了数据的索引结构，还决定了数据的物理存储顺序。
非聚集索引（Non-Clustered Index），也称为二级索引，是除了主键索引之外的其他索引。这些索引独立于数据的物理存储，它们的叶子节点包含索引键值和指向数据行的指针（通常是主键值），用于快速定位到数据行。
在InnoDB中，如果没有明确指定主键，InnoDB会自动创建一个隐藏的聚簇索引来存储表的数据行。通常建议使用自增ID（auto_increment）作为主键，因为这样可以保证数据的连续存储，从而提高写入和查询的性能。如果使用随机生成的ID（如UUID），可能会导致数据在磁盘上分散存储，增加随机I/O操作，降低性能。
聚集索引的优势在于它能够优化范围查询和排序操作，因为它按照索引键值的顺序存储数据。然而，由于每个表只能有一个聚集索引，因此在设计数据库时需要谨慎选择主键，以确保数据的有效组织和高效访问。同时，聚集索引的维护成本相对较高，尤其是在插入和更新操作频繁的情况下，可能会引起页分裂和数据重组，影响性能。因此，在选择合适的主键和索引策略时，需要根据应用的具体需求和数据访问模式进行权衡。

索引的常见模型？

• 哈希表：只适合等值查询
• 有序数组：查询最快，但更新数据成本太高
• 搜索树

InnoDB的索引为什么选择B+树？

索引需要保存到磁盘上，假设我们使用平衡二叉树来存储，一个100万个节点的二叉树高20，一次查询需要访问20个数据块，机械硬盘随机读取一个数据块大约需要10ms时间，因此单独访问一个行大约需要200ms时间。

为了让一个查询尽量减少读磁盘，就必须让查询过程访问尽量少的数据块， B+树可以很好的配合磁盘的读写特性，减少单次查询访问磁盘的次数。

B+树的叶子结点存储的是？

存储的是页，一个页里面有多个行。当我们通过索引定位页时，然后通过内部的有序数组再借助二分法去定位行。

InnoDB索引模型？

InnoDB中，表都是根据主键顺序以索引的形式存放，这种存储方式称为索引组织表。

InnoDB使用了B+树索引模型，所以数据都是存储在B+树中。

每一个索引在InnoDB中都对应一棵B+树。

create table t(
    id int primary key,
    name varchar(16),
    k int not null,
    index (k)
) engine = InnoDB;

insert into t(id, name, k) values
(1, 'Java', 100),
(2, 'Python', 200),
(3, 'Go', 300),
(5, 'MySQL', 500),
(6, 'Spark', 600)

上面我们添加了5行数据，按照ID大小我们可以记为R1~R5。上述语句中有两棵索引数，一棵是主键索引，另一棵为非主键索引。

主键索引和非主键索引的区别？

• 主键索引又称聚簇索引，主键索引的叶子节点存储的是整行数据
• 非主键索引又称二级索引，非主键索引的叶子结点存储的是主键的值

假设我们有以下两个SQL语句：

-- SQL1
select * from t where id = 5;

-- SQL2
select * from t where k = 500;

SQL1只需要搜索主键索引这棵B+树即可获得结果，但是SQL2需要先通过k索引树查到主键值，然后再去主键索引树查找最终的结果。

基于非主键索引的查询可能需要多扫描一棵索引树，因此我们在查询的时候尽量使用主键查询。

索引维护

B+树为了维护索引有序性，在插入新值时必须做必要的维护。当我们插入id为7的行记录时，只需要在R5后面新增一个记录。

但是如果插入id为4的行记录，此时就需要挪动R4及R4后面的数据，空出位置。此时如果R5所在的数据页已经满了，将会更加糟糕，因为此时需要申请一个新的数据页，然后挪动一部分数据过去（页分裂）。

页分裂除了影响性能以外，还会降低数据页的利用率，原本放在一个数据页的数据现在分到两个数据页，整体利用率下降50%。

当然相邻的两个页中间也会因为有些数据被删除，利用率过低以后会进行页合并。

覆盖索引

select id from t where k = 500;

上述语句中我们只需要查询id的值，id的值已经在k索引树上，因此可以直接提供查询结果，所以不需要回表操作。该索引k覆盖了我们的查询需求，因此称之为覆盖索引。

最左前缀原则

B+数索引结构，可以利用索引的最左前缀来定位记录。索引项是按照索引定义里面出现的字段顺序进行排序。

最左前缀可以是联合索引的最左的N个字段，也可以是字符串索引的最左M个字符。

索引下推

索引遍历过程中，会对索引的中包含的字段先进性判断，直接过滤掉不满足条件的记录，减少回表次数。

索引思考

-- 系统中的表如下
CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

-- 业务系统中如下的查询语句
select * from t where c=N order by a limit 1;
select * from t where c=N order by b limit 1;

ca和cb这两个索引有必要建立么？

• 如果c列的区分度很高，ca和cb其实都可以不用建立，因为通过索引c查出来的数据量会很少，order by进行排序还是很快的，此时ca和cb都不想要建立
• 如果c列的区分度很低，联合普通索引还是有必要要建立的，因为通过索引查出来的数据量会很大，order by将会很慢，并且很有可能需要借助磁盘临时表进行排序。如果没有cb索引，limi t 1仅仅表示返回给客户端1条数据，起不到限制扫描行数的作用，所以需要加cb索引；但是ca的索引由于满足最左前缀原则，所以不需要加，且c列是固定值，a列又有序，limit 1会只精准扫描一条数据。

limit的操作是在Server层进行完成，引擎每找到一条数据会返回给Server层，Server层进行数据的过滤，过滤完成以后会判断一下够不够limit的数，如果够了就结束循环，否则继续读取下一行。