湘潭学校网站建设 磐石网络第一,有些网站下方只有版权没有ICP,it软件网站建设,网站开发什么语言比较快索引组织表 前言数据存储堆表索引组织表 二级索引二级索引的性能评估#x1f539;为什么 idx_name 的性能开销最大#xff1f;#x1f539; 为什么 idx_last_modify_date 更新频繁会影响性能#xff1f;分析二级索引性能表格为什么主键应该“紧凑且顺序”#xff1f;二级索… 索引组织表 前言数据存储堆表索引组织表 二级索引二级索引的性能评估为什么 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 字节的顺序 UUIDBINARY(16)而不是 36 字节的字符串 UUIDCHAR(36)。如果可以使用自增 IDBIGINT作为主键查询效率最高。
二级索引总结
优化点建议二级索引插入性能避免创建随机插入的索引如 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业务).
