如何添加合适的索引：MySql 数据库索引认知

写在前面

---

• 博文内容涉及 Mysql 数据库索引简单认知，包括SQL执行过程，数据库数据存储原理。如何通过索引加快数据查询原理简单介绍
• 适合有一定SQL基础的开发运维小伙伴建立数据库索引认知，学会如何添加索引
• 理解不足小伙伴帮忙指正 😃,生活加油

99%的焦虑都来自于虚度时间和没有好好做事，所以唯一的解决办法就是行动起来，认真做完事情，战胜焦虑，战胜那些心里空荡荡的时刻，而不是选择逃避。不要站在原地想象困难，行动永远是改变现状的最佳方式

---

在学习索引之前，需要说明，索引并不是最好的解决问题手段，通过索引提高查询速度，本质上是空间换时间，只有当索引帮助存储引擎快速查找到需要的记录带来的好处大于其带来的额外的开销(物理存储，写操作，优化器计算开销)，索引才是有效的，对于非常小的表，大部分情况下全表扫描更高效，对于中大型的表，索引非常有效，对于特大型的表，建立和使用索引的代价增长，可以考虑使用分区等技术。

简单认识索引

对于索引的添加，一般情况下，大都会说添加到要查询的字段，但是具体怎么添加，还是有一些注意事项的，有时候可能会适得其反。在这之前，我们先通过一个单表索引的 Demo 来认识一些专有名词。

测试用的表，用于模拟业务表

SET profiling=1;
SELECT COUNT(*)  FROM ams_accounts_order;
/* 受影响记录行数: 0  已找到记录行: 1  警告: 0  持续时间 1 查询: 1.469 秒. */
SHOW PROFILE;

数据量： 6202700

ams_accounts_order
---
| COUNT(*) | 
| ---: | 
| 6202700 | 

表结构，这里省略了一些字段

CREATE TABLE `ams_accounts_order` (`accounts_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',`room_code` VARCHAR(32) NULL DEFAULT NULL COMMENT '房间编码' COLLATE 'utf8mb4_bin',`archives_no` VARCHAR(32) NULL DEFAULT NULL COMMENT '档案号' COLLATE 'utf8mb4_bin',`room_order_no` VARCHAR(50) NULL DEFAULT NULL COMMENT '订单对应入住订单号' COLLATE 'utf8mb4_bin',
............................`extended1` VARCHAR(50) NULL DEFAULT NULL COMMENT '扩展字段1' COLLATE 'utf8mb4_bin',`extended2` VARCHAR(50) NULL DEFAULT NULL COMMENT '扩展字段2' COLLATE 'utf8mb4_bin',`extended3` VARCHAR(50) NULL DEFAULT NULL COMMENT '扩展字段3' COLLATE 'utf8mb4_bin',`create_by` VARCHAR(64) NULL DEFAULT '' COMMENT '创建者' COLLATE 'utf8mb4_bin',`create_time` DATETIME NULL DEFAULT NULL COMMENT '创建时间',`update_by` VARCHAR(64) NULL DEFAULT '' COMMENT '更新者' COLLATE 'utf8mb4_bin',`update_time` DATETIME NULL DEFAULT NULL COMMENT '更新时间',`hotel_id` INT(11) NOT NULL DEFAULT '0' COMMENT '公寓唯一ID',PRIMARY KEY (`accounts_id`) USING BTREE
)
COMMENT='账务表'
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
AUTO_INCREMENT=6211026
;

默认情况下只有主键，查询字段和索引没什么关系，所以默认会全表扫描, 对于下面的SQL ，两个查询条件的查询时间 为 6.250 秒

SET profiling=1;
SELECT * from ams_accounts_order where hotel_id = 10029 
AND room_order_no = 'UDDH807015895560880128' ORDER BY accounts_id DESC 
;
/* 受影响记录行数: 0  已找到记录行: 18  警告: 0  持续时间 1 查询: 6.250 秒. */
SHOW PROFILE;
SET profiling=0;

什么是全表扫描？

在 Mysql 中 默认使用 InnDB 存储引擎，表中的数据存储在一个数据结构树(B+树)的所有叶子节点,每次需要依次访问一遍所有的叶子节点就叫做全表扫描，对于上面的SQL，hotel_id 和 room_order_no 都不是主键，也不是二级索引，因此需要访问一遍所以叶子节点(整张表全部数据)才能过滤出需要的数据，所以说上面的 SQL 会全表扫描。

那么如何避免全表扫描，在认知角度，查询数据最先想到二分法之类，所以需要对查询的字段排序，我们需要用某个值来标识数据，通过这个值来排序，在数据库角度这个标识就是索引，这里我们对其中一个查询条件添加索引，给 酒店ID hotel_id 添加索引

ALTER TABLE `ams_accounts_order`DROP INDEX `hotel_id`,ADD INDEX `hotel_id` (`hotel_id`);

这里的索引是一个二级索引(在 MySQL 的 InnoDB 存储引擎中所有非主键索引都是 二级索引)，或者叫非聚簇索引，对于二级索引，数据的存储方式也是树（B+树），但是区别于主键和行数据的存储，存储的是索引字段的值和对应主键的指针（accounts_id）。

二级索引的工作机制 ： 使用二级索引定位到主键值。再根据主键值回表（通过主键索引,上面存储数据的B+树）查找完整记录。

开销: 如果查询只涉及二级索引中的字段，无需回表，性能较高。如果查询包含非索引字段，则需要进行回表操作，增加了一次磁盘 I/O。

再次查询

SET profiling=1;
SELECT * from ams_accounts_order where hotel_id = 10029 AND room_order_no = 'UDDH807015895560880128' ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 18  警告: 0  持续时间 1 查询: 18.547 秒. */
SHOW PROFILE;
SET profiling=0;

我们可以发现加了不如不加，这是为什么？ 时间是原来的 3 倍多 18.547 秒

查看 EXPLAIN 结果中的 key 和 Extra 字段,确认使用了创建的索引，表示 MySQL 查询优化器选择了 hotel_id 索引来执行查询

EXPLAIN，用于显示 SQL 查询的执行计划， MySQL 查询优化器如何执行一个 SQL 语句，下文我们会详细介绍

EXPLAIN

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ams_accounts_order	\N	ref	hotel_id	hotel_id	4	const	3069172	10.00	Using where

• rows 字段：3069172，MySQL 估算需要扫描 3069172 行记录，
• filtered 字段：10.00，表示 MySQL 估算匹配条件的过滤比例为 10%。在匹配 hotel_id = 10029 的 3069172 行中，只有大约 10% 的记录会满足 room_order_no 条件
• Extra 字段：Using where，在使用索引后仍需进一步通过 WHERE 子句过滤记录

查询效率低的原因主要是单字段索引不足以过滤大量数据，导致大量无效回表操作和额外排序开销。

用通俗的话讲，虽然索引 hotel_id 快速找到 hotel_id = 10029 的记录，但由于匹配的记录量太大（3069172 行），需要频繁回表读取完整记录进行进一步过滤 room_order_no 。room_order_no 条件不在索引中，导致大量索引查找到的记录未被充分过滤。过滤比例（filtered = 10%）较低，意味着绝大多数匹配 hotel_id 的记录是无效的，进一步增加了查询开销。

回表又是什么意思？

上面的SQL根据 hotel_id 这个二级索引快速定位到所有满足 hotel_id = 10029 的记录。但是因为有 room_order_no 这个条件，对于每条匹配记录，MySQL 需要通过主键指针回到主表（ “回表”）读取完整的行数据。这个操作就是回表，然后根据 room_order_no 进一步过滤。即通过非聚簇索引（hotel_id）找到主键索引（accounts_id），再通过主键索引（accounts_id）找到行记录的过程就是回表

这里通过创建复合索引并优化查询，可以显著提高查询性能。复合索引可同时过滤 hotel_id 和 room_order_no，大幅减少需要回表的记录数量,添加复合索引，查询条件全部添加索引

ALTER TABLE `ams_accounts_order`DROP INDEX `hotel_id`,ADD INDEX `hotel_id` (`hotel_id`, `room_order_no`) USING BTREE;

时间远远小于最初的没有索引时间 0.015 秒

SET profiling=1;
SELECT * from ams_accounts_order where hotel_id = 10029 AND room_order_no = 'UDDH807015895560880128' ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 18  警告: 0  持续时间 1 查询: 0.015 秒. */
SHOW PROFILE;
SET profiling=0;

EXPLAIN 分析SQL

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ams_accounts_order	\N	ref	hotel_id	hotel_id	207	const,const	18	100.00	Using where

• key_len: 值为 207，表明 MySQL 使用了索引的多个列。如果 hotel_id 是单列索引，key_len 通常为 4（对应 INT 类型）。这里值为 207

• ref: 显示 const,const，表示查询条件中的两个常量值被用来查找

• rows: MySQL 预估需要扫描的记录数。这里是 18，说明查询范围已经很小，这是索引优化的结果。

在上面 SQL 的基础上，在添加一个查询条件，时间较之前变慢了

SELECT * from ams_accounts_order where hotel_id = 10029 AND room_order_no = 'UDDH807015895560880128'  
AND room_code='RFJ797273148247506944'  ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 3  警告: 0  持续时间 1 查询: 0.031 秒. */

修改复合索引添加对应的字段

ALTER TABLE `ams_accounts_order`DROP INDEX `hotel_id`,ADD INDEX `hotel_id` (`hotel_id`, `room_order_no`, `room_code`) USING BTREE;

可以看到时间又变快了，所以通过上面的测试我们可以知道，对于查询条件来讲，索引的添加是希望是近可能的获取少量子集数据，避免全表扫描和大量的回表。

SELECT * from ams_accounts_order where hotel_id = 10029 AND room_order_no = 'UDDH807015895560880128'  
AND room_code='RFJ797273148247506944'  ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 3  警告: 0  持续时间 1 查询: 0.016 秒. */

添加了三个索引字段，但是查询用到了两个字段

SET profiling=1;
SELECT * from ams_accounts_order where hotel_id = 10029 AND room_order_no = 'UDDH807015895560880128'    ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 18  警告: 0  持续时间 1 查询: 0.015 秒. */
SHOW PROFILE;
SET profiling=0;

EXPLAIN 分析可以看到,聚合索引最左原则，使用了索引。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ams_accounts_order	\N	ref	hotel_id	hotel_id	207	const,const	18	100.00	Using index condition; Using filesort

这里的 Extra 里面的部分字段我们看一下

Using index vs Using index condition：

• Using index：表示查询能够通过索引提供的字段获取所有需要的数据，不需要回表。这通常发生在覆盖索引的情况下。
• Using index condition：表示查询能通过索引过滤出符合条件的行，但是如果查询的字段不完全在索引中，MySQL 可能仍然需要回表来获取那些不在索引中的字段。

Using filesort 表示查询需要对结果集进行额外的排序操作。尽管使用了索引，结果集还是需要按照 accounts_id DESC 进行排序。此时的排序操作并不是通过索引来实现的（因为排序列 accounts_id 没有出现在索引的尾部），而是通过一个额外的排序步骤进行。即使 accounts_id 是主键。

更改一下 SQL 条件顺序，可以看到，如果多个索引符合最左原则

SET profiling=1;
SELECT * from ams_accounts_order where  room_order_no = 'UDDH807015895560880128'  AND hotel_id = 10029  ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 18  警告: 0  持续时间 1 查询: 0.031 秒. */
SHOW PROFILE;
SET profiling=0;

SQL 的查询条件顺序不影响索引命中情况

EXPLAIN

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ams_accounts_order	\N	ref	hotel_id	hotel_id	207	const,const	18	100.00	Using index condition; Using filesort

缺少条件，即不满足最左原则的情况

SET profiling=1;
SELECT * from ams_accounts_order where  room_order_no = 'UDDH807015895560880128'   AND room_code='RFJ797273148247506944'  ORDER BY accounts_id DESC ;
/* 受影响记录行数: 0  已找到记录行: 3  警告: 0  持续时间 1 查询: 6.282 秒. */
SHOW PROFILE;
SET profiling=0;

我们可以看到 二级索引没有被命中，还是走的一级索引,及直接走的主键

EXPLAIN

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ams_accounts_order	\N	index	\N	PRIMARY	8	\N	6138344	1.00	Using where

上面是一个 单表索引的 Demo，实际上生产中的索引的添加还要考虑其他的场景：

• 索引会占用额外的存储空间,因此不能滥用索引
• 索引会加快查询速度,但会降低插入/更新/删除的速度

在系统的学习索引之前，我们需要简单了解一下SQL的执行过程，我们上面用到了 EXPLAIN 来优化SQL ，为什么要用，它是干什么的，下面的内容回答这个问题

SQL执行过程

SQL 提交到数据库过程

1. 通过连接器把 SQL 语句给语法分析器
2. 语法分析器对SQL进行解析，生成一个抽象的语法树AST
3. AST 经过语义分析与优化器进行语意优化
4. 得到执行计划，把执行计划交给具体的执行引擎进行计算
5. SQL 执行引擎根据执行计划，调用存储引擎接口获取数据，执行表连接，排序等操作，生成结果集。
6. 结果通过连接器中返回给应用程序

  应用程序|  (1) 提交 SQL 查询|连接器 ----> 语法分析器 ----> 语义分析 & 优化器 ----> 执行计划生成|                 ||                 V|              执行引擎 ------> 存储引擎 (实际数据操作)|  (2) 返回查询结果应用程序

依次看一下这些步骤

连接器 Connector

接收应用程序发出的 SQL 查询请求。它建立并管理客户端和数据库之间的连接，并将 SQL 查询传递给数据库的后续处理阶段。

连接器会为每个连接分配一个专用的内存空间用于会话上下文管理，建立和释放连接对数据库比较重，所以一般会通过连接池维护连接,同理对于应用也是一样，往往会重复利用现有连接，可以为服务架构减少服务建立的连接，频发的建立连接断开连接，消耗时间的同时，对资源也有一定的考验。

连接一旦建立，不管是否有SQL执行，都会消耗一定的数据库内存资源，所以对于一个大规模互联网应用集群来说，如果启动了很多应用程序实例，这些程序每个都会和数据库建立若干个连接，即使不提交 SQL 到数据库执行，也就会对数据库产生很大的压力。可以根据实际情况对连接数进行限制。把相同的表的业务拆成微服务，可以有效避免这个问题。

语法分析器 Parser

连接器收到 SQL 以后，会将 SQL 交给语法分析器进行处理，确认 SQL 的语法正确性，工作比较简单，根据规则生成对应的抽象语法树（AST，Abstract Syntax Tree），语法错误是在构建语法树的时候发现的

对于 SQL 查询

SELECT name, age FROM users WHERE age > 30 AND name = 'Alice'

输出的 AST

抽象语法树（AST）:
SELECT: name, age
FROM: users
WHERE: age > 30 name = 'Alice'

语义分析与优化器（Semantic Analyzer & Optimizer）

语义分析和优化器会对抽象语法树进一步做语义优化，保证SQL 语义 不变的情况下进行语义等价交换，使最后的计算量和中间过程数据量尽可能小

语义分析 ：确认 SQL 语句的逻辑正确性，检查表和列是否存在，确保字段类型匹配等。

优化器 ：优化查询计划，尽量减少查询的执行时间和资源消耗。优化器会尝试多种可能的执行计划并选择最优的一个。它会考虑多种优化策略.

select *  from orders f where f.user_id=(select id from users)

语义等价转化后

select * from orders f join users u  on f.user_id=u.id

SQL语义分析与优化器 就是要将各种复杂嵌套的SQL进行语义等价转化，得到有限几种关系代数计算结构，并利用索引等信息进一步进行优化。这个过程称为查询优化。

执行计划

语义分析与优化器最后会输出一个执行计划，由执行引擎完成数据查询或者更新。 我们上面用到的 EXPLAIN 就是这个优化后的执行计划

执行计划是查询优化器为 SQL 查询生成的一个详细步骤集合，描述了如何从数据表中获取数据，如何进行连接、排序、过滤等操作。执行引擎是可替换的.

最后一步就是通过执行计划生成结果然后通过连接器中返回给应用程序。

我们l来看一个和上面的执行计划相关的面试题。

为什么要使用 PrepareStatement 执行SQL？

这是在校招中一道很经典的面试题，在 Java 程序中访问数据库的时候，JDBC 有两种提交SQL语句的方式，

• 一种是通过Statement直接提交SQL；
• 另一种是先通过PrepareStatement预编译SQL，然后设置可变参数再提交执行

我们经常讲，使用第二种，原理是什么？,我记得背面试题，一是防止SQL注入，二是执行速度快，在问为什么，说会把 SQL 预编译缓存到数据库，答案并没有错，但是实际的原理一直不清晰。

主要有两个好处 :

• 执行速度快是因为 PrepareStatement 会预先提交带占位符的SQL到数据库进行预处理，提前生成执行计划，当给定占位符参数，真正执行SQL的时候，执行引擎可以直接执行，效率更好一点。
• 防止SQL注入攻击也是同样的原因，提前生成执行计划是一个填词造句，一个执行计划对应一个SQL，而 Statement 是直接造句，可以任意断句。

Statement 提交SQL 注入的一个 Demo

select * from users where username = 'liruilomg';

但是如果用户输入的是这样一个字符串

'liruilomg';drop table users;--

那么生成的SQL就是这样的

select * from users where username = 'liruilomg';drop table users;--';

SQL注入攻击,会被当做两条SQL执行.如果使用 PrepareStatement 是下面的样子

select * from users where username = ?;

回到我们今天讲的索引

数据库数据存储原理

我们都知道数据库通过通过索引进行查询能加快查询速度，实际是如何查询的，原理是什么？

索引查询能加快查询速度的原理是什么？

根本原因是存储引擎不需要全表扫描获取数据，而是从索引的根节点快速定位数据。

为什么用 B+ 树做存储索引结构

MySQL的 InnoDB 存储引擎主要使用B+树作为其索引结构，B+树是一种N叉排序树，树的每个节点包含N个数据,按顺序排好，两个数据之间是一个指向子节点的指针，而子节点(内部节点，非叶子节点)的数据则在这两个数据大小之间,类似一种分治的思想，不存储实际的数据记录，只用于导航，叶子节点包含实际的键值和对应的数据记录指针（或数据本身，取决于存储方式）。

在数据查找的过程中，Mysql 根据索引值可以通过上面讲的 B+树 结构快速的导航到数据节点，通俗理解类似一种二分法的思想。那么为什么使用 B+ 树做其索引结构？

为什么用 B+ 树？

一般需求中需要做范围查询、遍历。会使用红黑树，hash表等数据结构，但是哈希表不适用范围查找，所以使用红黑树比较多，红黑树的时间复杂度是 O(logN)。如果红黑树过大，内存中会放不下时，所以改用 B 树，将部分索引存放在磁盘上（每个节点是一个索引页）。但是磁盘访问速度要比内存慢很多， 所以B 树充分考虑了机械磁盘寻址慢、顺序读写快的特点，通过多分支 降低了树高，减少了磁盘读写次数。但是B 树的数据同时存在于非叶子节点，每个节点既存储键值对，也存储数据，范围查询性能较差（搜索路径可能会停在任意节点。每次查找的数据结果可能提前结束），所以 Mysql 会使用数据只存在于叶子节点的B+ 树，叶子节点通过指针形成一个有序的链表，在进行范围查询和顺序遍历时非常高效

索引到底是什么结构？

B+ 树索引是一种 Key-Value 结构，通过 Key 可以快速查找到对应的 Value。B+ 树索引由根页面(Root)、分支页面(Branch)和叶子页面(Leaf)组成一棵树的结构。InnoDB 中，索引页面的大小由参数 innodb_page_size 控制，默认为16K。

每个索引页面内存储了一系列索引条目，格式为（Key，Value），这些记录按 Key 的顺序排列。每个索引页面里可容纳的条目数量跟条目的长度相关。一个索引页内最少存储 2 行记录，因为如果索引页内只有 1 行记录，就无法构成树的结构了.

InnoDB 为什么限制一行记录的最大长度？

通过上面的描述可以知道 行记录的最大长度与索引页的大小密切相关。由于每个索引页通常大小为16KB，因此 每个索引页的条目大小 会直接影响到页的容量。如果条目过大，则一个索引页能容纳的条目数会很少，甚至可能只能容纳一两个条目，这会影响索引的性能和结构。

索引中的 key 也就是我们创建的索引对应的字段值，如果为组合索引，那么多个索引值会组合到一起构成 key

value 中存放了下一层索引页面的编号 Page No，即在数据文件中的地址，在叶子节点中，Vlaue 是表里的所有字段，即实际的数据。

索引页内的 n 条记录，把 Key 的 取值划分为 n+1 个区间。索引记录在页面中有序存放，同时每个索引页通过 Next 和 Prev 指针指向相邻的页面

在 InnoDB 的实现上，每一层最左侧页面中的第一个索引条目有一点特殊，Key 值比k(1)小的记录，也要到这个索引条目指向的下一层页面中查找。

而下一层的索引页面中，每个页面中的索引条目，又将区间划分为更小的范围。假设我们需要查找 value 为 1 的记录。查找的路径会根据索引层次逐步进行。

索引查找又是如何发生的？

我们假设 value = 1 是需要查找的目标SQL条件，而这个值的键值为 k(1) 索引对应的Key。查找的过程通常是按以下步骤进行的：

1. 从最顶层的索引开始,最顶层的索引页会有 n 个条目，划分了 n+1 个区间。这些区间的边界分别由索引条目给定。如果我们需要查找 k(1)，那么最顶层的索引页会根据 k(1) 的值决定从哪一个区间开始查找。

• 假设最顶层索引页的索引条目为 [k(1), k(2), ..., k(n)]，那么它将会把 Key 划分成以下 n+1 个区间(实际还要考虑区间的闭合)：
  • 区间 1: [ -∞ , k(1)]
  • 区间 2: [k(1), k(2)]
  • …
  • 区间 n: [k(n-1), k(n)]
  • 区间 n+1: [k(n), +∞]

我们需要找到 value = 1 对应的区间。如果 k(1) 是 1，那么我们会进入区间 [ -∞ , k(1)]（即第一个区间）。这个区间的指针会指向下一层的一个索引页面。

2. 进入下一层的索引页面，接下来，我们进入下一层的索引页面。这个页面可能会包含多个条目，每个条目又将数据划分为更细小的区间。

• 假设该层的条目为 [k(1), k(2), ..., k(n)]，同样会划分出 n+1 个区间。
  • 区间 1: [ -∞ , k(1)]
  • 区间 2: [k(1), k(2)]
  • …
  • 区间 n+1: [k(n), +∞]

根据 k(1) = 1，我们会进入第一个区间，指向更深一层的索引页面。

3. 继续向下查找，查找过程会一直向下，直到到达叶子节点。每次进入下一层时，我们会根据 value = 1 所处的区间定位到下一层的页面。最终，在叶子节点中，我们会查找到具体的数据条目，返回记录，如果是范围查找，会根据叶子节点的链表依次遍历。

索引页是如何存储的？

我们上面有讲到，每个索引页面由格式为（Key，Value）的索引条目按Key的顺序排列构成，那么索引是如何存储的，一个索引页对应一个文件么？

InnoDB 使用 表空间（Tablespace） 来存储所有的表和索引。表空间是 InnoDB 存储引擎中用于组织存储数据的物理文件或逻辑结构。

• 共享表空间（General Tablespace）：这是一个共享的表空间，也称为 系统表空间,多个表可以存储在同一个表空间文件中,默认的共享表空间文件是 ibdata1
• 独立表空间（File-Per-Table Tablespace）：每个表有自己的物理文件（.ibd 文件），以便独立存储表的数据和索引。每个表都有自己的独立文件，文件名通常以表名命名，扩展名为 .ibd

InnoDB 中，表的数据以B+树的方式，存储在一个数据段（Segment）中，一个数据段由一系列区块（Extent）组成。每个区块由 64 个 16K 的连续的页面组成，数据段也是一个逻辑上的存储单位，位于表空间中的不同物理文件内。

这里的页面就是我们上面讲的索引页，也叫数据页

数据页是数据表中数据存储的基本单位，一个数据页的大小通常是 4K，8k，16K，32K。

在 InnoDB 中，默认的页面大小为 16K。记录以行的形式存储在数据页中，每行记录在数据页中占用一段连续的空间。通常 1 行记录可能占用几十字节到几百或几千字节。每个数据页能容纳的记录数一般在几行到几百行之间。

InnoDB 对行的长度有一定的限制，每行记录的长度不能超过页面大小的一半。对于 16K 的页面大小，1 行记录最长大概在 8000 字节多一点。如果 1 行记录平均长度为 200 字节，那么一个页面最多可以容纳八十多行记录。

每个表由一系列的数据页组成。一个表的数据页数量主要由表中的记录数决定。如果记录平均长度为 200 字节，每个数据页存 80 行记录，那么存储 1000 万行记录，大致需要 12.5 万个数据页

多个数据页在组织上通过索引(B+ 树索引)来组织，不管是叶子节点还是非叶子节点，都是通过上面的数据页存储的

如何通过索引加快数据库记录的查询速度呢？

实际上 Mysql 中数据库索引有两种：

聚簇索引

聚簇索引，聚簇索引的数据库记录和索引存储在一起，我们上面一直在讲的索引就是 聚簇索引，也叫一级索引，在 Mysql InnoDB 中，数据库表的主键就是聚簇索引，表里的数据就是按聚簇索引的形式存储。聚簇索引的 Key 字段为表结构定义中 Primary Key（主键） 指定的字段。也就是下面这样

在叶子节点，索引1和记录行r1存储在一起，查找到索引就是查找到数据库记录，主键ID和所在的记录行存储在一起。所以 MySQL的数据库文件实际上是以主键作为中间节点，行记录作为叶子节点的一颗B+树。

B+树的节点存储在磁盘上，如果对于非叶子节点，每个节点存储1000多个索引数据，这样树的深度最多只要4层，就可存储数亿的数据。如果将树的根节点缓存在内存中，则最多只需要三次磁盘访问就可以检索到需要的索引数据。

如果不指定 Primay Key，则会以非空的唯一索引作为 Primary Key，或者 InnoDB 自动生成一个隐藏字段作为 Primary Key。

分支页面中的索引条目为 (id, page num)，叶子页面中，索引条目为(id, db_trx_id, db_roll_ptr, user_name, …）。这里的 db_trx_id、db_roll_ptr 是 InnoDB 中的隐藏字段，

非聚簇索引

除了主键索引之外，其他的索引（主动添加的索引）被称为二级索引，或者叫非聚簇索引。非聚簇索引，非聚簇索引在叶子节点记录的就不是数据行记录，而是聚簇索引（在InnoDB中通常是一个书签记录，即聚簇索引的键值），也就是主键，如下图。

通过B+树在叶子节点找到非聚簇索引a，和索引a在一起存储的是主键1，再根据主键1通过主键（聚簇）索引就可以找到对应的记录r1，这种通过非聚簇索引找到主键索引，再通过主键索引找到行记录的过程也被称作回表。

所以通过索引，可以快速查询到需要的记录，而如果要查询的字段上没有建索引，就只能扫描整张表了，查询速度就会慢很多。

下面是索引 idx_username 的一个示意图，分支页面中，索引条目为 (username, id, page num），叶子页面中，索引条目为 (username, id）。

虽然索引 idx_username 的定义中只有 username 字段，但是我们把 username 和 id 拼在一起看作是 Key 字段。username 可能有重复值，但是 username 和 id 拼在一起，就不会重复了。

这里拼在一起也叫做组合索引，索引中包含多个字段的索引，下面这个例子中的 idx_abc 就是一个组合索引

组合索引的结构实际上和单列索引是一样的，只不过索引条目由更多的字段组成。我们来看一下组合索引 idx_abc 局部结构的示意图

组合索引对于非唯一索引，索引记录中 Key 的值可能存在重复值。但是索引记录中还包括了主键字段，加上主键字段后，整条索引记录就不会重复了。

在索引查询的场景中，有下面几种场景：

• 直接通过 聚簇索引 获取数据，在只有主键索引的情况下，全表扫描
• 通过 非聚簇索引获取数据对应的 聚簇索引(主键)，然后在通过 聚簇索引 获取数据，需要回表
• 直接通过 非聚簇索引 获取数据，覆盖索引，不需要回表

索引访问路径

SQL 语句查询数据时，通过在 WHERE 子句中指定字段需要满足的条件来获取的数据，不需要指定数据的物理属性。数据库引擎需要将逻辑的 SQL 语句转换为物理的访问路径，从表中获取数据。

在只有主键索引的情况下，InnoDB 中，表的数据存储在聚簇索引的叶子页面中。我们最开始讲的Demo，查询时需要全表扫描，需要依次访问每一个叶子页面。大表的全表扫描会大量消耗 CPU（数据过滤的逻辑处理） 和 IO（加载多个页到内存中），应当尽量避免。有些情况下可给查询字段建立合适的索引，避免全表扫描。当然有的场景下，业务可能就是需要获取整个表的所有数据，比如数据仓库需要同步整个表的数据做数据分析。可以考虑在业务低峰期执行这类全表扫描的 SQL，或者建立读库，专门执行这类 SQL

那么对于有索引的查询又是如何处理的？对于使用 B+树的索引来讲，适用全键值，键值范围和键前缀（最左原则）的查找。

全值匹配

select * from tab where a = 'a' and b = 'b' and c = 'c'

最左匹配

select * from tab where a = 'a';
select * from tab where a = 'a' and b = 'b'

但是下面这几个查询就无法使用索引匹配了，因为缺少了字段 a 的等于条件。

select * from tab where b = 'b'
select * from tab where c = 'c'
select * from tab where b = 'b' and c = 'c'

匹配列前缀

select * from tab where a like  'a%';

对于下面这个 SQL，可以用到索引前缀字段 A 进行等值匹配，但是字段 C 则无法用到索引等值匹配中。

select * from tab where a = 'a' and c = 'c'

检索步骤：

1. 根据 where 条件，定位到记录所在的最开始的那个叶子页面。
2. 在叶子页面中定位到第 1 条满足条件的记录。如果使用的是二级索引，则还需要根据索引记录中的主键值，到聚簇索引查找数据。获取到记录后，检查该记录是否满足 WHERE 子句中的其他条件。若满足条件，则将这一行记录返回给 Server 层处理。
   
3. 处理下一条的记录。如果当前页面的记录已经处理完了，则继续处理下一个相邻页面中的记录。
4. 如果获取到的记录不满足索引条件（where A = Aj），则说明没有更多的数据了，停止扫描。

索引范围匹配

索引范围匹配可以分为几种情况：

1. 只限制了范围的最大值，没有限制最小值，如where A <= Aj，只限制了范围的最小值，没有限制最大值，如 where A >= Aj。
2. 限制了范围的最小值和最大值，如 where A >= Ai and A <= Aj。
3. 精确匹配和某一列，范围匹配另外一列 ,如 where A = a AND b <= Bj

每种情况下，还要看是否包含边界值。使用大于（>）和小于（<）条件时，不包含边界值。索引范围扫描和索引等值匹配的执行过程比较相似，主要的区别在于如何确定扫描的边界。如果没有限制最小值，则要从索引中的第 1 条记录开始扫描。如果没有限制最大值，则需要一直扫描到索引的最后一个叶子页面。

覆盖索引

一个查询涉及到的字段全都包含在一个索引中，则可以使用索引来满足查询，不需要回表。

index(C1, C2, C3, C4, C5)select C1,C2 from tab where C1=x order by C5

索引逆序扫描

索引还支持逆序扫描，比如下面这个 SQL 中，使用了 order b desc。

select * from tab where A = Aj order by B desc

由于索引中的条目都是有序的，在字段 A 的值固定的情况下，字段 B 是有序的，因此只需要按索引条目的顺序反向扫描就可以了。

逆序扫描有几个特点：

1. 逆序扫描从区间的最大值处开始。如果 where 条件中没有限制最大值，则从索引的最后一个页面开始扫描。
2. 在 InnoDB 的实现上，逆序扫描比顺序扫描成本要更高一些。索引页面中，索引条目顺序组成一个单向的链表，逆序访问时，需要做更多的计算。

无法使用索引的一些情况

1. 组合索引中，缺少前缀字段的查询条件,上面有说明过这种情况
2. where 子句中，在索引字段上进行了运算，则无法使用索引。比如下面这几个 SQL，虽然字段 A 上建有索引，但是 WHERE 子句，对字段 A 做了运算，所以无法使用到索引。

select * from tab where a+0 = 1;
select * from tab where to_char(a) = '1';

3. 索引字段存在隐式转换。如果索引字段和传入的参数类型不匹配，可能会在索引字段上发生类型隐式转换 b varchar(30) // select * from tab where b = 1;，这会导致索引无法使用。

博文部分内容参考

© 文中涉及参考链接内容版权归原作者所有，如有侵权请告知 😃

---

《高性能 Mysql》

极客时间 Mysql 相关收费专栏

---

© 2018-至今 liruilonger@gmail.com, 保持署名-非商用-相同方式共享(CC BY-NC-SA 4.0)