侧边栏壁纸
博主头像
沧海月明

解决了什么问题，做了什么优化

  • 累计撰写 149 篇文章
  • 累计创建 33 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
《MySQL是怎样运行的：从根上理解MySQL》 数据库

B+树索引的原理

FatFish1
FatFish1
2025-05-05 / 0 评论 / 0 点赞 / 16 阅读 / 0 字 / 正在检测是否收录...

根据前面的内容，InnoDB将数据以16KB的页为单位存储，每个页中有多行记录

一个页中的全部记录分成不同的组，用页目录和槽做了一个目录，每个槽存储每个组中主键最大的记录的地址偏移量

通过这个目录，就可以实现在一个页中做二分查找，快速定位想要的记录

那如果现在数据量很大，分了好多个页，想要定位数据在哪个页中，也是一个很费劲的事情，因此也需要给页做个目录，这就是索引

InnoDB的索引方案

以如下表为例：

CREATE TABLE index_demo(
	INT,
	INT,
	CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

在不同的页中插入数据

还记得在一个页中插入数据，实际上是放入组中，如果一个组满了，就分裂成两个组继续插入，页也是类似的逻辑

假设一个页中只能存放3条记录，算上Infimum + Supremum，一个页实际上有5条记录

现在执行INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y'); ，一个页就满了

现在想再执行INSERT INTO index_demo VALUES(4, 4, 'a'); ，就需要分配一个新页了

但是为了满足数据呈主键由小到大排列的单向链表这一原则，必须将数据(5, 3, 'y') 做后移操作，即实际上(5, 3, 'y') 去了新页，而(4, 4, 'a') 被插入到老页中

如下图：

页号不连续这是因为mysql存储地址实际是不一定连续的，只是逻辑上的连续，通过指针维系双向链表

InnoDB聚簇索引的由来

如果页分的多了，想要查找数据在哪个页中也是个费劲的事情，因此要给页建立目录

在InnoDB引擎中，页目录也是一个普通的数据页，与正常的数据页格式完全相同：Infimum + Supremum + 真实数据行

但是这个真实数据行中的数据与存入的表数据是有差异的：

  • record_type = 1，区别于普通用户记录的record_type = 0，Infimum的record_type = 2，Supremum的record_type = 3 ，值为1代表这条记录是InnoDB自动生成的索引数据

  • 记录内容除去添加的哪些隐藏列，还有额外两列，一列存页编号，一列存这一页中主键值最小的记录的主键值

如图所示：

这样构造的页目录，就叫索引

但由于页目录也是数据页，存储容量就也有上限，如果一页存满了，就得再开一页，同时不同的页存储的主键值也得按照由大到小的单向链表存储，但是如果数据量太大了，目录查起来就也麻烦了，怎么办？那就在目录上面再建目录，这样层数越来越多，但是一层中的页数越来越小，最终形成了一个树形结构，这就是B+树结构

根据B+树的由来，可以发现B+树的重要特性：

  • 实际用户记录其实都存放在B+树的最底层的节点（叶子节点），非叶子节点用来存放目录项

  • 页内的记录是按主键大小顺序排成的单向链表

  • 同一层级的页是根据页中用户记录的主键大小排成的双向链表

在日常使用过程中，一般构建的都是4层的B+树就够用了，而且这个树是在建表存入数据时系统自动构建出来的，概括来讲，就是一个以主键排列的，存放了全量用户真实数据的数，这个树就是聚簇索引，也叫主键索引

除了主键索引，InnoDB还会为UNIQUE KEY自动构建索引

二级索引的由来

有了聚簇索引，按照主键查数据就已经很方便很快了，但是想按照其他字段查询，还是没办法

因此当用户对哪个字段有查询需求，就主动指定索引列，按照哪个字段进行排序，再构建一个B+树出来，结构上完全一样，可以实现对这个查询字段的二分查找

但是这个树的叶子节点不是存储全量数据，而是存储索引字段和主键值，因此，按照索引字段查询，必须先检索二级索引树，找到对应的主键值，然后再检索聚簇索引树，这个过程就叫回表

使用回表而非直接存用户数据的目的是为了减少存储空间开销，同时，一个表索引越多，就会建立越多的B+树结构，对存储也不利，同时也影响插入性能

这个树就叫二级索引，其特性包括：

  • 页内按照索引字段有小到大排列成单向链表

  • 同一层级的页根据也记录的索引字段大小排成双向链表

  • 叶子节点存放的是索引列和主键值，按照索引列排序，相同索引列值的，内部按主键值排序

  • 目录页存放的是索引列、主键值、页编号，这一点与聚簇索引是不同的，聚簇索引只存放主键值和页编号。这样设计的目的是，索引列可能是非Unique列，有可能有重复值，这样新数据存入时，如果两个页都有相同值的数据，新值就不知道存哪个页了，因此还要再根据主键值判断一次

联合索引

联合索引也是一种特殊的二级索引，是一种多列组成的索引，与二级索引类似：

  • 二级索引叶子存索引列+主键值，联合索引就存多个索引列+主键值

  • 二级索引目录页存索引列+主键值+页号，联合索引就存多个索引列+主键值+页号，也是为了防重复

而其排序规则也跟二级索引一样，先按照第一索引列排序，第一索引列相同的值再按照第二索引列排序，依次排完所有索引列还相同，最后按主键排序

InnoDB的B+索引创建流程

简单看下，在不管是聚簇索引还是二级索引的创建过程中，始终有一个地址确定的根节点（一棵树一个），然后按以下三个流程不断循环：

  • 表创建，随表创建根节点，其中既没有用户记录，也没有目 录项记录，地址也永远不会变，并且直接记录到数据字典中，当要查询这个表，InnoDB就会去数据字典中把这个根节点找出来，用于定位

  • 当用户向表插入数据时，就会先向这个根节点对应的页存数据，当这个页满了，就会将其中所有记录复制到一个新分配的页（比如页a），然后对页a做页分裂操作，变成页a+页b，这样两个新页各占一半数据，新数据再判断向哪个页插入即可，而根节点则升级成目录页，存放页a和页b的目录数据

  • 当根节点中的目录也存不下了，根节点再次发生复制，获得页c，页c分裂成页d，插入新目录值，而根节点再次晋升为目录的目录

根据这种目录结构和创建流程，InnoDB要求每个页中至少存储2条记录，是为了防止树的层级变的很深，提升查询效率

MyISAM索引结构

MyISAM引擎中，数据和索引是分开存储的

MyISAM的用户数据全部存储在一个数据文件中，并且不做排序，通过行号对每个记录进行唯一性标记

MyISAM的索引结构和InnoDB类似，但是不会在叶子节点中存储真实数据，而是存储数据的主键与行号的对应关系。因此MyISAM的主键索引本质上也是一个二级索引，进而在MyISAM中使用主键查询，也需要一次回表

索引的应用

索引的代价

根据前面索引的由来，我们可以得出结论，索引的代价包括：

  • 一个索引一个树，会产生空间开销，尽管索引存的只有索引列+主键，但是也是实打实的记录；同时也可以得出一个结论，索引中的列不宜过多，太多了也会影响性能

  • 每次执行增删改操作，不仅要修改聚簇索引树，还得修改每一个二级索引，因此过多的索引会导致DML性能变差

全值匹配和最左列匹配原则

以如下为例，创建一个表：

CREATE TABLE person_info(
 id INT NOT NULL auto_increment,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
 PRIMARY KEY (id),
 KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

索引列是name、birthday、phone_number，因此在构建二级索引树的时候，排序方案是先排name、相同name排birthday、相同birthday排phone_number

因此查询语句覆盖三个列时：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

这种场景就叫全值匹配，可以匹配到索引，且这三个顺序无所谓，因为会在InnoDB引擎执行过程中完成优化（执行三步骤：编译、优化、引擎）

但是如果查询语句跳过了name，可以知道，如果不看name，那birthday本质上就是无序的，无法走二分查找了，这时候就会出现索引失效。这是索引失效场景1的原理

要解决这个问题，可以识别birthday作为常用查询条件，以birthday作为索引即可

如果查询语句使用了name、phone_number，这时候还能匹配到索引吗，实际上是可以的，但是由于跳过了birthday，在引擎看来，phone_number也是无序的，没法二分查找了，因此会损失部分索引的性能，但由于name已经缩小了范围，再做遍历查找，也好过全表扫描。这是索引半失效场景1的原理

因此得到结论：如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中 从最左边连续的列

最左前缀匹配原则

name是字符串，以name为索引的排序，本质上就是字符串排序，而字符串排序规则是先比较首字符，首字符一样则比较二字符，依次类推

因此看如下sql：

SELECT * FROM person_info WHERE name like 'As%';

匹配的是连续的左前缀，因此语句可以命中索引

但是如果改下：

SELECT * FROM person_info WHERE name like '%ow';

变成这种形式，从后面开始匹配，因为没有经过前面字符串的依次排序比较，引擎也只能认为后面是无序的，也就没法使用二分查找，只能遍历，因此这个语句无法命中索引，这就是索引失效场景2的原理

如果在开发中真实存在这种场景，需要从后向前查询，可以将数据倒过来存储，业务正向查询到结果，再将结果倒序排回来，就可以提升性能了

范围匹配原则

看如下sql：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

按照这种查询形式，引擎可以快速定位到name为Asa和Barlow的值，并且将中间值逐个取出来就可以了，本质上也是执行了两次等于，可以应用二分查找

但是如下sql：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

引擎首先定位出name取值在Asa和Barlow之间的值，命中了name的索引，但是由于其中有多个name，在引擎看来在这些值中birthday也是无序的（因为只有同一个name中的birthday才是有序的），因此第二个不等式就无法应用索引，同样的，总比全表扫描性能好一点，这是索引半失效场景2的原理

同样的，改下sql：

SELECT * FROM person_info WHERE name = 'Asa' AND birthday > '1980-01-01';

这种场景下就可以应用索引了，因为相同name中birthday是排序的

索引用于排序

执行ORDER BY子句时，一般MySQL会优先选取内存排序，如果排序数据量太大，内存排不了，则会进行磁盘排序，应用排序文件处理

但是如果ORDER BY的对象是索引列，本身在库中是有序的，MySQL就无需再进行额外的排序动作，直接返回数据即可，这样可以提升查询性能

联合索引的排序特性

对于联合索引，排序也要按照索引列顺序，例如

SELECT * FROM person_info ORDER BY name, birthday, phone_number;

或者name为定值查询，对birthday, phone_number排序，都可以应用到索引

但是跳过name，直接对后者排序，就是索引半失效的场景了

asc、desc混用的场景

如果联合索引且是asc、desc混用的场景，逻辑上也是可以应用索引的：

SELECT * FROM person_info ORDER BY name, birthday desc, phone_number asc;

只需要先按name排序左查，再按birthday排序右查，再按phone_number排序左查，理论上也是可以的

但是衡量其开销与使用排序文件相比，使用排序文件反而更快，因此MySQL就优化其逻辑不再匹配索引了，这就是索引失效场景3的原理

WHERE子句或ORDER BY中出现非索引列条件

SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

如果是这个语句，查询country列本身就无法命中索引了，MySQL在已经全表扫描一轮的场景下，再按name索引排序已经没有意义了，因此该场景无法命中索引，这就是索引失效场景4的原理

同样，以下场景也无法命中索引

SELECT * FROM person_info ORDER BY name, country LIMIT 10;

使用复杂表达式修饰索引列

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

数据是按原始值排序的，加了函数或其他表达式，得出来的结论不一定就是原始的排序顺序（虽然UPPER后排序可能还与一前一样，但是不能笼统判定其他场景也是），因此这种情况下MySQL只能选择全表扫描了，这就是索引失效场景5的原理

在WHERE子句中使用复杂表达式，效果是一样的

索引用于分组

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

按索引列顺序的分组，也可以应用索引

这是因为按索引列排序的过程，其实本质上也是在分组了，这样就可以免于内存分组，也就是应用到了索引优化

总结索引失效和半失效场景

  • 索引失效场景1：联合索引条件查询违背最左前匹配原则，跳过左列直接查询右列

  • 索引失效场景2：字符串索引值使用like条件，但违背最左前缀匹配，对字符串的中间或后面部分使用like

  • 索引失效场景3：联合索引复杂排序，例如desc、asc混用，理论上可以应用索引，但使用索引开销可能还要超过文件排序，因此MySQL可能优化不使用索引

  • 索引失效场景4：ORDER BY针对索引列，WHERE中包含非索引列，必须要全表扫描一次，因此使用索引不再有意义

  • 索引失效场景5：使用复杂函数或表达式修饰索引列，得出来的结果可能与原始列排序不同，MySQL自动对这些处理后的列不应用索引

  • 索引半失效场景1：联合索引条件查询不按顺序使用，跳过中间列直接使用右侧列，只能引用到左列的二分查找

  • 索引半失效场景2：联合索引条件查询使用范围匹配，在左边列非定值查询的情况下直接对右边列进行范围匹配，只能应用到左列的二分查找

优化索引

  • 只为用于搜索、排序或分组的列创建索引，考虑业务场景或日常运维查询使用哪些列，为它们创建索引即可，如果一般是组合查询，则为其创建联合索引。不要笼统创建一大堆索引，增加空间开销和增删改查性能

  • 考虑列基数，如果一个列值差异很小，极端情况下只有1个值或2个值，没必要创建索引，因为这种情况下，排序等于没排序

  • 索引列尽量使用小的类型，例如对VARCHAR(256)创建索引就比VARCHAR(1024)好，INT就比VARCHAR好，因为类型越小，MySQL做排序操作性能就越高，同时二级索引树占用空间也越少。但是同时要考虑业务场景。

  • 索引字符串的前缀，对于使用字符串列做索引的场景，如果字符串很长，可以使用前缀做索引，例如KEY idx_name (name(10)) ，可以提升性能，但是如果用这种形式，ORDER BY就无法被优化了，因为从10后面开始，值都是没进行排序的，ORDER BY的结果还是要走一次内存排序或文件排序

  • 让索引列在表达式中单独出现，这是符合索引失效场景5，即推荐使用WHERE my_col < 4 / 2 而非WHERE my_col * 2 < 4

  • 主键索引使用INT类型的id+AUTO_INCREMENT可以避免一些复杂的主键给INSERT语句增加分页的性能损耗。但是这一点也需要看业务场景，不能一味套公式

  • 清理冗余和重复索引，例如使用了name+birthday+phone_number的联合索引，就没必要为name单独再建立索引了

0
MySQL 数据库
版权归属： FatFish1
本文链接： http://localhost:8090/archives/whybplus
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区