网站推广软件下载,东莞网站设计风格,青岛做网站建设多少钱,搭建网站兼职在 MySQL InnoDB 存储引擎 中#xff0c;索引主要分为 聚簇索引#xff08;Clustered Index#xff09; 和 非聚簇索引#xff08;Secondary Index#xff09;。它们的主要区别如下#xff1a; 1. 聚簇索引#xff08;Clustered Index#xff09;
定义
聚簇索引是表数…在 MySQL InnoDB 存储引擎 中索引主要分为 聚簇索引Clustered Index 和 非聚簇索引Secondary Index。它们的主要区别如下 1. 聚簇索引Clustered Index
定义
聚簇索引是表数据存储的方式即 索引和数据在一起叶子节点存储的是完整的行记录。InnoDB 中的主键索引就是聚簇索引如果没有主键InnoDB 会选择一个 UNIQUE 索引作为聚簇索引如果没有 UNIQUEInnoDB 会自动生成一个隐藏的 rowid。
存储结构
使用 B 树 结构存储叶子节点存放 完整的行数据。数据物理存储顺序和主键索引顺序一致。
特点
访问主键索引时直接获取整行数据查询速度快。数据存储在主键索引的叶子节点上表本身就是一棵 B 树。适用于 基于主键的查询、范围查询。更新、删除、插入操作可能导致数据的页分裂影响性能。
示例
CREATE TABLE users (id INT PRIMARY KEY, -- 主键自动成为聚簇索引name VARCHAR(50),age INT
);在 users 表中id 作为 聚簇索引数据在磁盘上的存储顺序与 id 值的顺序相同。 2. 非聚簇索引Secondary Index / 辅助索引
定义
非聚簇索引是独立于数据存储的索引叶子节点存储的是主键值而不是完整的行数据。访问非聚簇索引时需要先查找索引再通过主键回表查询数据这种过程称为 回表查询回溯查询Bookmark Lookup。
存储结构
也是 B 树 结构但叶子节点存储的是主键值而非完整的行记录。
特点
加速非主键列的查询适用于 WHERE 条件非主键字段。需要二次查询先通过非聚簇索引找到主键值再回表查询完整数据。索引占用空间较大需要额外存储主键值。
示例
CREATE TABLE users (id INT PRIMARY KEY, -- 主键聚簇索引name VARCHAR(50),age INT,INDEX idx_name (name) -- 非聚簇索引
);如果执行
SELECT * FROM users WHERE name Alice;查询过程如下
先在 非聚簇索引 idx_name 中查找 Alice获取其 主键 id。使用 id 在 聚簇索引 中回表查找完整的行数据。 3. 聚簇索引 vs. 非聚簇索引
对比项聚簇索引Clustered Index非聚簇索引Secondary Index存储结构叶子节点存储 完整行数据叶子节点存储 主键值查询速度主键查询快无需回表非主键查询慢需要回表插入速度慢影响数据存储顺序较快不影响主键顺序更新操作影响数据物理存储顺序可能导致页分裂更新可能导致回表查询增加占用空间只存储数据本身需要存储 索引列 主键值占用空间大适用场景主键查询范围查询查询非主键列提高查询性能 4. 什么时候使用聚簇索引 vs. 非聚簇索引
✅ 适合使用聚簇索引的场景
主键查询多如 WHERE id 100;范围查询多如 BETWEEN 100 AND 200;数据表的主键是有序递增的如 AUTO_INCREMENT避免页分裂。
✅ 适合使用非聚簇索引的场景
查询非主键字段时如 WHERE name Alice;涉及联合查询如 INDEX (name, age);表很大不希望频繁回表查询可以使用 覆盖索引避免回表。 5. 可能的优化方案 覆盖索引Covering Index 避免回表提高查询性能。 优化方法如果查询的数据只包含索引字段则直接从索引中获取数据不需要回表。 示例 CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name Alice; -- 只查 name 和 age不回表使用合适的主键 采用 自增主键避免页分裂提高插入性能。避免使用 UUID、随机值作为主键会导致索引失效、性能下降。 减少回表查询 索引覆盖查询如 SELECT name FROM users WHERE name Alice;适当调整索引列如 INDEX (name, age) 避免多次回表。 总结
InnoDB 中的主键索引是聚簇索引数据存储在 B 树的叶子节点上查询主键时速度最快。非聚簇索引存储的是主键值查询时需要回表查询完整数据适用于非主键查询场景。优化索引可以减少回表查询提高 MySQL 查询性能。
