MySQL数据存储- 索引组织表_mysql 索引组织表-CSDN博客

本文链接：https://blog.csdn.net/qq_45852626/article/details/145488386

索引组织表

前言

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`。