网站模板代码下载seo中介平台
索引组织表
- 前言
- 数据存储
- 堆表
- 索引组织表
- 二级索引
- 二级索引的性能评估
- 🔹为什么 idx_name 的性能开销最大？
- 🔹 为什么 idx_last_modify_date 更新频繁会影响性能？
- 分析二级索引性能表格
- 为什么主键应该“紧凑且顺序”？
- 二级索引总结
- 堆表和索引组织表有什么区别,分别的应用场景是什么?
前言
InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎，在海量大并发的 OLTP 业务中，InnoDB 必选。这一章我们聊一聊 MySQL InnoDB 存储引擎的索引结构。
数据存储
数据存储有堆表和索引组织表两种方式.
堆表
堆表中的数据无序存放， 数据的排序完全依赖于索引.（Oracle、Microsoft SQL Server、PostgreSQL 早期默认支持的数据存储都是堆表结构）。
数据和索引分开存储。索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，特别是对于 OLTP 业务。
索引组织表
，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。在索引组织表中，数据即索引，索引即数据。
MySQL InnoDB 存储引擎就是这样的数据组织方式；Oracle、Microsoft SQL Server 后期也推出了支持索引组织表的存储方式。
数据按照主键的顺序存储在 B+ 树索引中，而不是采用传统的堆表（Heap Table）存储方式。这意味着：
- 数据是按主键排序存储的，不像普通表那样是无序的。
- 数据和索引存储在一起，不需要额外的索引来定位数据。
- 查询主键时更快，因为主键索引本身就包含数据。
表 User 的主键是 id，所以表中的数据根据 id 排序存储，叶子节点存放了表中完整的记录，可以看到表中的数据存放在索引中，即表就是索引，索引就是表。
二级索引
InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index）， 或非聚集索引（None Clustered Index）。
二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。
举个例子:
CREATE TABLE User (id BIGINT AUTO_INCREMENT,name VARCHAR(128) NOT NULL,sex CHAR(6) NOT NULL,registerDate DATETIME NOT NULL,...PRIMARY KEY(id), -- 主键索引KEY idx_name(name) -- 二级索引)
如果用户通过列 name 进行查询，比如下面的 SQL：
SELECT * FROM User WHERE name = 'David'，
整个流程是:
通过二级索引 idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。**这种“二级索引通过主键索引进行再一次查询”的操作叫作“回表”，**你可以通过下图理解二级索引的查询：
二级索引的设计有个非常大的好处:
如果记录发生修改,其他索引无须进行维护,除非记录的主键发生修改.(因为二级索引存储的是主键，而不是数据的物理地址！)
举个例子:
UPDATE employees SET salary = salary * 1.1 WHERE emp_id = 1001;
salary 发生了变化，但 emp_id 没变，因此二级索引无需更新。
📌 索引组织表的二级索引维护情况总结
| 情况 | 是否需要更新二级索引？ | 原因 |
|---|---|---|
非主键字段更新（如 salary） | ❌ 无需更新 | 二级索引指向主键，不受物理位置影响 |
| 数据物理位置变更 | ❌ 无需更新 | 物理存储位置变了，但主键没变 |
主键 emp_id 变更 | ✅ 需要更新 | 因为二级索引存的是主键值 |
| 删除记录 | ✅ 需要更新 | 需要删除二级索引中的主键引用 |
| 插入新记录 | ✅ 需要更新 | 需要在二级索引中添加新主键 |
👉 结论：索引组织表的二级索引维护成本低，除非主键变更，否则二级索引无需维护！
你会发现索引组织表在存在大量变更的场景下，性能优势会非常明显，因为大部分情况下都不需要维护其他二级索引。🚀
二级索引的性能评估
我们给出一个业务的表User:
CREATE TABLE User (id BINARY(16) NOT NULL,name VARCHAR(255) NOT NULL,sex CHAR(1) NOT NULL,password VARCHAR(1024) NOT NULL,money BIG INT NOT NULL DEFAULT 0,register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),uuid CHAR(36) AS (BIN_TO_UUID(id)),CHECK (sex = 'M' OR sex = 'F'),CHECK (IS_UUID(UUID)),PRIMARY KEY(id),UNIQUE KEY idx_name(name),KEY idx_register_date(register_date),KEY idx_last_modify_date(last_modify_date));
在 User 表中，有三个二级索引：
- idx_name(name)
- idx_register_date(register_date)
- idx_last_modify_date(last_modify_date)
🔹为什么 idx_name 的性能开销最大？
二级索引 idx_name 的数据是无序的，导致 B+ 树频繁分裂，影响性能。
🌟 解决方案：限制用户每天/每年可修改昵称的次数，减少 idx_name 的更新次数，从而减少二级索引的维护开销。
🔹 为什么 idx_last_modify_date 更新频繁会影响性能？
每次用户修改 money，都会导致 last_modify_date 更新，从而更新 idx_last_modify_date 索引。
🌟 解决方案：
业务层优化：如果某些操作不需要 last_modify_date 频繁更新，可以在业务代码中做条件判断，避免无意义的更新。
批量更新：对于高并发写入场景，可以考虑 定时批量更新 last_modify_date，而不是每次更新 money 都更新时间戳。
分析二级索引性能表格
二级索引的 插入性能 主要受 插入数据的顺序性 影响，插入是否有序决定了 索引的维护成本。
📌 索引的维护成本分析
| 索引 | 插入成本 | 更新成本 | 维护成本原因 |
|---|---|---|---|
PRIMARY KEY (id) | ✅ 低 | ✅ 低 | 主键索引是顺序插入，且不会发生更新（除非主键变更）。 |
idx_name(name) | ❌ 高 | ❌ 高 | 随机插入，导致 B+ 树频繁分裂，影响插入性能；用户可修改昵称，更新时需要维护索引。 |
idx_register_date(register_date) | ✅ 低 | ✅ 低 | 顺序插入，不会更新，因此维护成本较低。 |
idx_last_modify_date(last_modify_date) | ✅ 低 | ❌ 高 | 顺序插入但更新频繁，每次用户数据变更都会触发索引更新，影响性能。 |
为什么主键应该“紧凑且顺序”？
主键的设计影响索引的存储和查询性能。
在 InnoDB 引擎下：
- 索引组织表 使用 B+ 树存储数据，主键决定了数据的存储顺序。
- 所有二级索引都会存储主键值，查询时先在二级索引查找，再回表通过主键找到完整数据。
因此：
- 主键应该尽量顺序（如自增 ID、顺序 UUID）
- 这样数据插入是 顺序填充 B+ 树，不会造成索引频繁分裂，提高插入性能。
- 主键应该尽量紧凑（占用字节少）
- 二级索引存储的是“索引列 + 主键”，如果主键太大（如 36 字节的 UUID），会导致索引数据量变大，影响查询性能。
🌟 解决方案： - 推荐使用 16 字节的顺序 UUID（BINARY(16)），而不是 36 字节的字符串 UUID（CHAR(36)）。
- 如果可以，使用自增 ID（BIGINT）作为主键，查询效率最高。
二级索引总结
| 优化点 | 建议 |
|---|---|
| 二级索引插入性能 | 避免创建随机插入的索引（如 idx_name），如果必须使用，尽量限制更新频率。 |
| 二级索引更新开销 | 频繁更新的字段（如 last_modify_date）的索引可能影响性能，可以减少无意义的更新或批量更新。 |
| 主键设计 | 主键应该尽量紧凑（如 BINARY(16) 的顺序 UUID），避免使用大字段（如 CHAR(36) 的 UUID）。 |
| 索引顺序性 | 选择顺序性高的字段作为索引，提高索引插入效率，如 register_date。 |
📌 结论
- 尽量使用顺序插入的索引（如
register_date），避免随机插入的索引（如name）。 - 减少不必要的索引更新，如
last_modify_date，可以通过业务优化降低更新频率。 - 主键设计要紧凑且顺序，这样可以减少二级索引的存储和查询开销，提高整体性能。 🚀
堆表和索引组织表有什么区别,分别的应用场景是什么?
堆表（Heap Table）数据无序存储，是MyISAM使用的存储方式.主键索引存的是数据的物理地址，查询时需要回表获取完整数据.
因为所有索引都是平级,增删改性能较好.
但是查询需要回表,所以查询较慢,查询没有索引组织表好,且由于只支持行锁,适用于低并发场景,
索引组织表（IOT）数据按主键顺序存储，是InnoDB使用的存储方式.
主键索引的叶子节点直接存整行数据，主键查询不需要回表，但插入性能较低.
不过高并发场景下性能远高于堆表方式,适用于CRUD比较多的高并发场景(包括OLTP业务).