第五章“盛放记录的大箱子-InnoDB数据页结构”的完整学习笔记。

第五章：盛放记录的大箱子-InnoDB数据页结构学习笔记

一、数据页的总体概览

InnoDB 为了不同的目的设计了多种不同类型的“页”（如存放 undo 日志的页、存放系统数据的页等）。其中，我们平时插入的表数据记录存放的页被称为数据页（官方称为 Index 类型的页）。
一个标准的数据页大小默认为 16KB，它在物理结构上被严格划分为 7个不同的部分：

File Header（文件头部）：38字节
Page Header（页面头部）：56字节
Infimum + Supremum（最小记录和最大记录）：26字节
User Records（用户记录）：大小不确定
Free Space（空闲空间）：大小不确定
Page Directory（页目录）：大小不确定
File Trailer（文件尾部）：8字节

二、记录的存储区：User Records 与 Free Space

动态分配：一个新建的数据页是没有 User Records 的。当我们插入一条新记录时，InnoDB 会从 Free Space 中划分出一块空间分配到 User Records 里去。当 Free Space 被全部消耗完后，这个页就满了，需要申请新的数据页。
单向链表：页内的所有记录（包括后续提到的两条虚拟记录）会根据主键值从小到大，通过记录头信息中的 next_record 属性（下一条记录的相对位置偏移量）串联成一个单向链表。

三、页内的“门神”：Infimum 与 Supremum

每个数据页在初始化时，都会自动生成两条“虚拟记录”：

Infimum（最小记录）：代表该页中主键值最小的记录，它是页内单向链表的起点。
Supremum（最大记录）：代表该页中主键值最大的记录，它是页内单向链表的终点。
这两条记录是固定存在的，无论页里有没有用户数据，它们都在那里，且不属于 User Records，而是单独占据一部分空间。

四、提升查询效率的法宝：Page Directory (页目录)

如果在页内全靠单向链表顺序挨个比对主键来找记录，当页内记录多时性能极差。为了实现极速查找，InnoDB 在页内实现了类似于书本目录的结构——页目录。

分组规则：InnoDB 会将页内的所有正常记录（包含虚拟记录，但不含已经被标记为删除的记录）划分为若干个组。
- 最小记录（Infimum）自己单独算作 1 个组。
- 最大记录（Supremum）所在的分组包含 1~8 条记录。
- 其他剩下的正常记录分组，每组包含 4~8 条记录。
槽（Slot）的生成：每个分组中主键值最大的那条记录就是该组的“带头大哥”。InnoDB 会把这些“带头大哥”在页面中的相对位置偏移量提取出来，按顺序存放到 Page Directory 里。这些提取出来的地址偏移量就被称为槽 (Slot)。每个槽占用 2 个字节。
二分法极速查找：当要在一个页内根据主键查找记录时，底层会直接利用 Page Directory 里的槽进行二分查找。快速定位到目标记录可能所在的那个槽（分组）后，再找到该组内主键最小的第一条记录，沿着单向链表最多向后遍历 8 次，就能极快地找到目标记录。

Page Header 专门用于记录这个数据页自身的运行状态信息，占用 56 字节。
它里面保存了许多对该页至关重要的统筹数据，例如：

本页中目前有多少条记录（含被删除的）。
Free Space（空闲空间）的起始地址在哪里。
本页目前存了多少个槽（Slot）。
本页在 B+ 树中所处的层级大小。

六、串联全剧的关键：File Header (文件头部)

File Header 占用 38 字节。与 Page Header 专门管数据页内部不同，File Header 是所有类型的页都有的通用头部，用于描述各种页面的通用信息。
最核心的几个字段包括：

页号 (FIL_PAGE_OFFSET)：这是每一个页的唯一身份证号。
上一页与下一页指针 (FIL_PAGE_PREV 和 FIL_PAGE_NEXT)：虽然页内的记录是单向链表，但所有的纯数据页之间，正是通过这两个指针串联成了一个巨大的双向链表。
页类型 (FIL_PAGE_TYPE)：标记这个页到底是数据页、Undo日志页还是其他的什么页。

七、守护数据完整的哨兵：File Trailer (文件尾部)

由于数据都是先在内存中修改，再刷新到磁盘的。如果刷盘刷到一半突然断电，会导致页面数据损坏（前半截是新的，后半截是旧的）。
File Trailer 占用 8 个字节，专用于校验数据的完整性：

它包含校验和（Checksum）与日志序列号（LSN）的后4字节。
数据页首尾呼应：每次刷盘时，系统会计算页面的最新校验和及 LSN，分别写入页头的 File Header 和页尾的 File Trailer 中。如果在读取该页时，发现头尾的这两项数据不一致，就说明这个页损坏了，从而避免使用脏数据。

本章核心总结：
本章全景拆解了 16KB 数据页的内部精密构造。你可以把数据页想象成一节火车车厢：File Header 是两头的挂钩，把无数个车厢连成双向链表；车厢里的乘客（记录）按主键排队手拉手（单向链表）；车厢里配备了乘务员座位表（页目录槽）方便二分查找；最后，每节车厢的门窗上贴了封条（File Trailer），以防止在行驶（刷盘）过程中发生断裂损坏。整个设计极其严谨，为后续搭建庞大的 B+ 树索引结构打下了微观基础。

第六章：快速查询的秘籍-B+树索引学习笔记

一、没有索引时查找数据的问题

在没有索引的情况下，如果我们要在一张表中查找某条记录：

在一个页中查找：如果数据量很少，只占用一个数据页，InnoDB 可以通过数据页内的“页目录（Page Directory）”使用二分法快速定位。
在很多页中查找：当数据量很大，分布在多个数据页时，由于没有任何目录结构，MySQL 只能从第一页开始，顺着双向链表一页一页地遍历所有数据（全表扫描）。这种方式在海量数据下是非常缓慢的。

二、索引的演进：从目录到 B+ 树

为了解决多页查找效率低下的问题，InnoDB 引入了“目录”的思路：给所有的数据页建立一个目录。

目录项记录（Directory Record）
- InnoDB 将每个数据页中主键值最小的那条记录的主键提取出来，连同该数据页的页号，组成一条“目录项记录”。
- 目录项记录与普通的用户记录极其相似，它们也是存放在 16KB 的数据页中的，只是记录头信息中的 record_type 属性不同（普通记录是 0，目录项记录是 1）。
B+ 树的形成
- 当目录项记录变多，一个目录页装不下时，就会产生多个目录页。
- 为了管理这多个目录页，InnoDB 会再往上抽出一层“更高级的目录”，存储这些子目录页的最小主键和页号。
- 依此类推，这种“自底向上”不断抽取目录的结构，最终形成了一个倒置的树状图，这就是著名的 B+ 树（B+ Tree）。
B+ 树的特点
- 叶子节点（最底层的节点）：存放的是真正的用户数据记录。
- 非叶子节点（内节点/根节点）：存放的是目录项记录，仅仅起到索引和导航的作用。

三、聚簇索引 (Clustered Index)

这是 InnoDB 存储引擎中最核心的一种索引，也是默认建立的索引。它具有两个非常显著的特点：

排序规则：
- 页内的记录按照主键大小排成单向链表。
- 各个存放用户记录的数据页按照主键大小排成双向链表。
- 各个存放目录项的目录页也按照主键大小排成双向链表。
数据完整性：
- B+ 树的叶子节点包含了完整的用户记录（即包括隐藏列在内的所有列的数据）。

核心概念：在 InnoDB 中，数据即索引，索引即数据。我们创建的表本身就是一棵以主键为核心构建的 B+ 树聚簇索引。

四、二级索引 (Secondary Index / 辅助索引)

如果我们想按照非主键列（例如 name 列）进行条件搜索，聚簇索引就帮不上忙了，因为聚簇索引是按主键排序的。此时我们需要建立“二级索引”。

结构特点：
- 二级索引也是一棵独立的 B+ 树。
- 它的排序规则变成了按照你指定的索引列（如 name）来排序。
- 最关键的区别：二级索引的叶子节点不存储完整的用户记录，而是只存储 “该索引列的值 + 主键的值”。
回表（Table Lookup）：
- 当我们通过二级索引查找到目标记录时，我们只拿到了该记录的 name 和 主键 ID。
- 为了获取这条记录的其他列信息，MySQL 需要拿着这个查到的 主键 ID，重新回到聚簇索引那棵 B+ 树中再去查一遍。这个根据主键在聚簇索引中获取完整记录的过程，就被称为回表。

五、联合索引 (Composite Index)

我们还可以同时为多个列（例如 c2 和 c3 列）建立一个索引，这就叫联合索引。

本质：联合索引本质上还是一个二级索引，它并不是为每个列都单独建一棵树，而是多列共用一棵树。
排序规则：它会先严格按照第一列（c2）的大小进行排序；如果第一列的值相同，才会接着按照第二列（c3）进行排序。
叶子节点存储的是：c2的值 + c3的值 + 主键的值。

六、索引的代价

虽然索引能极大提升查询速度，但它绝不是越多越好，因为每一棵 B+ 树都有着高昂的代价：

空间代价：
- 每一个索引都是一棵 B+ 树，每一个节点都是一个 16KB 的数据页。索引越多，占用的磁盘物理空间就越大。
时间代价（维护成本）：
- 每次对表中的数据进行增、删、改（INSERT、DELETE、UPDATE）操作时，都需要去修改各个 B+ 树。
- 尤其是当数据页满了还需要发生页分裂（Page Split），或者数据删除过多导致页合并（Page Merge），这会带来极大的性能消耗。

本章核心总结：
为了避免全表扫描的龟速，InnoDB 采用 B+ 树 结构来组织数据。以主键构建的、叶子节点包含所有数据的叫聚簇索引；以非主键构建的、叶子节点只包含“索引列+主键”的叫二级索引，用二级索引查数据通常需要回表；多个列一起构建的叫联合索引。理解 B+ 树的层次结构和数据分布，是写出高性能 SQL 语句、进行索引优化的最核心理论基础。

实践操作:

准备环境，建立三个索引，另外还有默认的主键索引

DROP TABLE IF EXISTS t_index;

CREATE TABLE t_index (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  age INT,
  city VARCHAR(20),
  INDEX idx_name(name),
  INDEX idx_age(age),
  INDEX idx_name_age(name, age)
) ENGINE=InnoDB;

插入数据：

INSERT INTO t_index (name, age, city) VALUES
('alice', 18, 'beijing'),
('bob', 20, 'shanghai'),
('alice', 25, 'shenzhen'),
('tom', 30, 'beijing'),
('jerry', 22, 'shanghai'),
('alice', 18, 'hangzhou');

查看该表的索引：

mysql> SHOW INDEX FROM t_index;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index |          0 | PRIMARY      |            1 | id          | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_index |          1 | idx_name     |            1 | name        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index |          1 | idx_age      |            1 | age         | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index |          1 | idx_name_age |            1 | name        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index |          1 | idx_name_age |            2 | age         | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

查看具体sql语句的执行计划：

mysql> EXPLAIN SELECT * FROM t_index WHERE name='alice';
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_name,idx_name_age | idx_name | 83      | const |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

ref表示使用索引；如果没有索引，这里会是全表扫描ALL；
possible_keys表示可能用到的索引

覆盖索引：

mysql> EXPLAIN SELECT name FROM t_index WHERE name='alice';
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_name,idx_name_age | idx_name | 83      | const |    3 |   100.00 | Using index |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+

查询得列name就在二级索引中，因此不需要回表，直接在二级索引B+Tree中查询到，然后返回，因此Extra是Using index

最左前缀原则

mysql> EXPLAIN SELECT * FROM t_index WHERE name='alice' AND age=18;
+----+-------------+---------+------------+------+-------------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                 | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_name,idx_age,idx_name_age | idx_age | 5       | const |    2 |    50.00 | Using where |
+----+-------------+---------+------------+------+-------------------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM t_index WHERE age=18;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_age       | idx_age | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

where中有两个联合索引的列，或者有第一个列，都可以使用到联合索引，并且符合覆盖索引，但只有第二个列age时，不满足最左前缀原则，不能使用联合索引。

范围查询影响索引以及索引下推

mysql> EXPLAIN SELECT * FROM t_index WHERE name='alice' AND age > 20;
+----+-------------+---------+------------+-------+-------------------------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                 | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+-------------------------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_index | NULL       | range | idx_name,idx_age,idx_name_age | idx_name_age | 88      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

两个关注点，一时type是range，表示范围查找；而是使用了Using index condition（索引条件下推），也就是二级索引中直接筛选数据，然后再将过滤后的主键进行回表，这样就可以少回表很多数据。正常是全部回表，查找所有数据，再根据where条件筛选记录。等于现在的过滤操作再索引阶段，而不在server层。

mysql> EXPLAIN SELECT * FROM t_index WHERE name='alice' AND age=18;
+----+-------------+---------+------------+------+-------------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                 | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_name,idx_age,idx_name_age | idx_age | 5       | const |    2 |    50.00 | Using where |
+----+-------------+---------+------------+------+-------------------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这个要重点分析，根据sql语句，可以发现应该也是索引条件下推。但EXPLAIN输出执行计划，发现sql选择了最简单的idx_age索引，并没有走联合索引，更别提ICP了。
核心在于，EXPLAIN输出的sql引擎优化过后的sql执行计划。也就是说sql觉得，这个sql语句没必要走联合索引，一个简单的idx_age就可以了。宁可多回表然后server过滤，也不联合索引ICP。我们可以强制使用联合索引：

mysql> EXPLAIN SELECT * FROM t_index
    -> FORCE INDEX(idx_name_age)
    -> WHERE name='alice' AND age=18;
+----+-------------+---------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_index | NULL       | ref  | idx_name_age  | idx_name_age | 88      | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

但发现也没有ICP，因为where的两个是具体的过滤，已经得到目标记录了，并不需要ICP再筛选，整个逻辑是：

B+树查找 (alice,18)
  ↓
直接命中叶子节点
  ↓
拿到主键
  ↓
回表

ICP只有在走的联合索引，并且需要扫描多个索引记录时，例如上面的范围查找，才有意义。

最后：手撕一棵真实的 InnoDB B+树

在 .ibd 里还原一棵 InnoDB B+树（以二级索引为例）

先创建表，然后插入大量数据:
在.ibd中查看所有页分布

root@VM-0-6-ubuntu:/var/lib/mysql/testDB# innodb_space -f t_bptree.ibd space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           3           1           SDI                 
4           15          12          INDEX               
16          16          1           FREE (ALLOCATED)

可以发现从页4到页15共有12个B+树的数据页，肯定不止一棵树。有主键索引的和二级索引的。
3. 查看所有索引页的“level”字段：

==== PAGE 4 ====
 level=1,
==== PAGE 5 ====
 level=1,
==== PAGE 6 ====
 level=0,
==== PAGE 7 ====
 level=0,
==== PAGE 8 ====
 level=0,
==== PAGE 9 ====
 level=0,
==== PAGE 10 ====
 level=0,
==== PAGE 11 ====
 level=0,
==== PAGE 12 ====
 level=0,
==== PAGE 13 ====
 level=0,
==== PAGE 14 ====
 level=0,
==== PAGE 15 ====
 level=0,

分析结果，level 1有两个，表示有两个根节点，也就是page4和page5是两个B+树的根节点。页6~15都是叶子节点，但是哪颗树的不知道，然后查看page4的具体信息：

#<Innodb::Page::Index:0x00007b562eb9bba8>:

fil header:
#<struct Innodb::Page::FilHeader checksum=2811936413, offset=4, prev=nil, next=nil, lsn=20240562, type=:INDEX, flush_lsn=0, space_id=4>

fil trailer:
#<struct Innodb::Page::FilTrailer checksum=2811936413, lsn_low32=20240562>

page header:
#<struct Innodb::Page::Index::PageHeader
 n_dir_slots=2,
 heap_top=204,
 n_heap_format=32776,
 n_heap=8,
 format=:compact,
 garbage_offset=0,
 garbage_size=0,
 last_insert_offset=196,
 direction=:right,
 n_direction=5,
 n_recs=6,
 max_trx_id=0,
 level=1,
 index_id=158>

n_recs = 6说明这个B+树有6个分支，那两个B+树的结构就都清楚了。
n_heap = 8 一共有8个总记录。两个系统记录（最大最小），和6个用户记录，这里的记录是索引，不是真实数据，也就对应上面的6个分支了。基本的结构已经清楚了，现在需要区分，两个B+树，哪一个是主键，哪一个是二级索引
Mysql8.0更新了表空间，把结构相关信息都放在ibd里了；5.6的话表结构信息都在ibdata1中，指定该数据即可。
8.0得话，就得手动写解析器rb文件。分别写两个，问题是也不知道那个对应哪个，得试，一般第一个都是主键，如果解析不对应，页号都会出现错误，整个数据错位，输出明显是错的。
解析后的输出

主键索引：
key=[id=222]
二级索引：
key=
  [#<struct Innodb::Page::Index::FieldDescriptor name="name", type="VARCHAR(10)", value="name0", extern=nil>,
   #<struct Innodb::Page::Index::FieldDescriptor name="age", type="INT", value=0, extern=nil>],
 row=[#<struct Innodb::Page::Index::FieldDescriptor name="id", type="INT", value=100, extern=nil>],

则两棵树分别是：

主键索引：
                [ROOT page 4]
   ------------------------------------------------
   |   1   |   222   |   666   | 1110 | 1553 | 1996 |
   ------------------------------------------------
     /        /        /         /      /       \
   p6       p7       p8       p11     p14      p15
二级索引：
                         [ROOT page 5]

   ---------------------------------------------------------
   | ("name0",0) | ("name2",2) | ("name30",80) | ("name42",42) |
   ---------------------------------------------------------
        /              |               |               \
     page9          page13          page10          page12

第七章：好东西也得先学会怎么用-B+树索引的使用学习笔记

本章是日常数据库开发中最实用的一章，主要讲解在拥有了 B+ 树索引后，如何编写正确的 SQL 语句才能真正发挥出索引的威力，以及如何设计更好的索引。

一、索引适用的条件（以联合索引为例）

假设我们为某张表建立了一个联合索引 idx_name_birthday_phone(name, birthday, phone)。B+树会优先按 name 排序，name 相同按 birthday 排序，都相同再按 phone 排序。
在此规则下，索引的适用情况如下：

全值匹配：查询条件和索引列完全一致（三个列都用上了）。这是最理想的情况。注：优化器非常聪明，你写的 WHERE 条件顺序乱了也没关系，它会自动调整顺序以匹配联合索引。
匹配左边的列（最左前缀原则）：在联合索引中，搜索条件必须包含最左边的列才能用上索引。你可以只查 name，或者 name + birthday，这都能用上索引。但如果跳过左边的列（比如直接查 birthday 或 phone），则完全无法使用该联合索引。
匹配列前缀（字符串匹配）：对于字符串类型的列，B+树是按照字符逐个比较大小的。因此，只匹配字符串的左边部分（如 LIKE '张%'）可以极快地用到索引；但如果是后缀匹配（如 LIKE '%张'）或中间匹配，则索引失效。
匹配范围值：利用 B+ 树按顺序排列的特性，范围查询（如 >、<、BETWEEN）可以利用索引极快地定位区间并顺序遍历。前提是范围查询的列也必须符合“最左前缀原则”。
精确匹配左列 + 范围匹配右列：如果左边的列是精确相等匹配，右边的列进行范围匹配（如 name = '张三' AND birthday > '1990-01-01'），依然可以顺畅使用索引。

二、索引在排序和分组中的神级应用

用于排序（ORDER BY）：在 MySQL 中，如果无法利用索引，数据需要在内存或磁盘中重新进行排序（这被称为文件排序 FileSort，极其消耗性能）。但如果 ORDER BY 的列顺序与联合索引的列顺序完全一致，就可以直接利用索引底层已经排好序的双向链表，顺着拿数据即可，直接省去繁重的排序过程。
用于分组（GROUP BY）：分组的底层原理也是先排序再将相同的值聚合。因此，只要分组列的顺序严格符合联合索引的最左前缀原则，就可以直接利用 B+ 树索引进行极速分组。

三、性能分水岭：回表与覆盖索引

回表的巨大代价：我们在第六章学过，使用二级索引查到数据后，还需要拿主键去聚簇索引里再查一遍完整记录，这就是“回表”。回表操作本质上是随机 I/O。如果通过二级索引筛选后发现要回表的记录非常多，优化器会经过成本计算后认为：与其做大量缓慢的随机 I/O 回表，还不如直接去聚簇索引做一次全表扫描（顺序 I/O）。此时，二级索引会直接失效。
覆盖索引（Covering Index）：为了彻底消除回表带来的性能损耗，我们在查询时，应尽量只查询索引中已经包含的列。也就是说，我们要查的所有字段（SELECT 后面跟的字段）都刚好在这个联合索引的节点里，这样到底层叶子节点拿到数据后就能直接返回，完全不需要回表找完整记录。
- 核心警告：这也是为什么所有规范都强烈禁止写 SELECT * 的根本原因。SELECT * 几乎必然导致无法使用覆盖索引，从而引发大量的回表操作。

四、如何建立“好”的索引（建索引的铁律）

索引虽然好，但由于占用空间且增加增删改的维护成本，绝不能滥用。

好钢用在刀刃上：只为出现在 WHERE、ORDER BY、GROUP BY 中的列建立索引。那些只会出现在 SELECT 列表中的列，不需要建索引。
考量列的基数（Cardinality）：基数是指列中不重复数据的个数。基数越大，区分度越高，建立索引的效果才越好。比如给“性别”列建立索引毫无意义，因为只有男女，区分度极低，查出来大量重复数据最终还是会导致严重的回表。
索引列的类型尽量小：数据类型越小（如用 TINYINT 代替 INT），占用的存储空间越少，一个 16KB 的数据页里能装下的目录项就越多，B+树的层级就会越矮胖，最终查询时的磁盘 I/O 次数就越少。
字符串前缀索引：如果字符串极长，全部作为索引极其浪费空间。我们可以只截取字符串前几个字符建立索引（如 name(10)）。缺点是：使用了前缀索引的列，就无法再利用它来做 ORDER BY 排序了。
让索引列在比较表达式中“保持单身”：千万不要对索引列进行任何函数计算或数学运算！例如 WHERE my_col * 2 < 4，这会导致 MySQL 内部无法识别原值，只能乖乖走全表扫描。正确的写法永远是计算常量：WHERE my_col < 4 / 2。

五、主键的终极设计原则：顺序插入

InnoDB 的聚簇索引是严格按照主键大小排序的。

如果我们使用自增整数作为主键，每次插入新记录都是在数据页的末尾追加，速度极快。
如果我们使用非递增的随机值（如 UUID）作为主键，新插入的记录大概率需要强行插在现有个别页的中间。如果那个数据页已经满了，InnoDB 被迫要把这个页的数据挪动，劈成两半，这被称为页分裂（Page Split）。页分裂不仅极其耗费性能，还会造成大量的磁盘碎片。
结论：强烈建议业务表的主键使用具备自增属性的整数类型。

牧樵工坊

数据页结构和索引

第五章：盛放记录的大箱子-InnoDB数据页结构学习笔记

一、数据页的总体概览

二、记录的存储区：User Records 与 Free Space

三、页内的“门神”：Infimum 与 Supremum

四、提升查询效率的法宝：Page Directory (页目录)

五、记录页面状态：Page Header (页面头部)

六、串联全剧的关键：File Header (文件头部)

七、守护数据完整的哨兵：File Trailer (文件尾部)

第六章：快速查询的秘籍-B+树索引学习笔记

一、没有索引时查找数据的问题

二、索引的演进：从目录到 B+ 树

三、聚簇索引 (Clustered Index)

四、二级索引 (Secondary Index / 辅助索引)

五、联合索引 (Composite Index)

六、索引的代价

实践操作:

覆盖索引：

最左前缀原则

范围查询影响索引以及索引下推

最后：手撕一棵真实的 InnoDB B+树

第七章：好东西也得先学会怎么用-B+树索引的使用学习笔记

一、索引适用的条件（以联合索引为例）

二、索引在排序和分组中的神级应用

三、性能分水岭：回表与覆盖索引

四、如何建立“好”的索引（建索引的铁律）

五、主键的终极设计原则：顺序插入

牧樵

第五章：盛放记录的大箱子-InnoDB数据页结构 学习笔记

一、 数据页的总体概览

二、 记录的存储区：User Records 与 Free Space

三、 页内的“门神”：Infimum 与 Supremum

四、 提升查询效率的法宝：Page Directory (页目录)

五、 记录页面状态：Page Header (页面头部)

六、 串联全剧的关键：File Header (文件头部)

七、 守护数据完整的哨兵：File Trailer (文件尾部)

第六章：快速查询的秘籍-B+树索引 学习笔记

一、 没有索引时查找数据的问题

二、 索引的演进：从目录到 B+ 树

三、 聚簇索引 (Clustered Index)

四、 二级索引 (Secondary Index / 辅助索引)

五、 联合索引 (Composite Index)

六、 索引的代价

实践操作:

覆盖索引：

最左前缀原则

范围查询影响索引以及索引下推

最后：手撕一棵真实的 InnoDB B+树

第七章：好东西也得先学会怎么用-B+树索引的使用 学习笔记

一、 索引适用的条件（以联合索引为例）

二、 索引在排序和分组中的神级应用

三、 性能分水岭：回表与覆盖索引

四、 如何建立“好”的索引（建索引的铁律）

五、 主键的终极设计原则：顺序插入

linux-内存

innodb引擎

牧樵

第五章：盛放记录的大箱子-InnoDB数据页结构学习笔记

一、数据页的总体概览

二、记录的存储区：User Records 与 Free Space

三、页内的“门神”：Infimum 与 Supremum

四、提升查询效率的法宝：Page Directory (页目录)

五、记录页面状态：Page Header (页面头部)

六、串联全剧的关键：File Header (文件头部)

七、守护数据完整的哨兵：File Trailer (文件尾部)

第六章：快速查询的秘籍-B+树索引学习笔记

一、没有索引时查找数据的问题

二、索引的演进：从目录到 B+ 树

三、聚簇索引 (Clustered Index)

四、二级索引 (Secondary Index / 辅助索引)

五、联合索引 (Composite Index)

六、索引的代价

第七章：好东西也得先学会怎么用-B+树索引的使用学习笔记

一、索引适用的条件（以联合索引为例）

二、索引在排序和分组中的神级应用

三、性能分水岭：回表与覆盖索引

四、如何建立“好”的索引（建索引的铁律）

五、主键的终极设计原则：顺序插入