MSQL-聚簇索引与非聚簇索引的比较

聚簇索引详解

InnoDB 的聚簇索引特性

1. 表数据本身就是聚簇索引：

   • 数据行实际存储在聚簇索引的叶子节点中
   • "表就是索引，索引就是表"的结构
   • 每个InnoDB表有且只有一个聚簇索引

         聚簇索引的叶子节点存储的是：真实数据

1. 主键作为聚簇索引：

   CREATE TABLE users (id INT PRIMARY KEY,  -- 此主键自动成为聚簇索引name VARCHAR(100),email VARCHAR(100)
   );
   

2. 无显式主键时的处理：

   • 如果表没有定义主键，InnoDB会选择一个唯一的非空索引代替
   • 如果没有这样的索引，InnoDB会隐式创建一个6字节的ROWID作为聚簇索引

聚簇索引的优势

• 高效的主键查询：直接通过B+树定位到数据行
• 范围查询高效：连续的主键值物理存储也相邻
• 覆盖索引优势：需要的数据都在索引中时可避免二次查找

聚簇索引的缺点

• 插入速度依赖插入顺序：最好按主键顺序插入
• 更新主键代价高：可能导致行移动
• 全表扫描可能较慢：当主键不连续时

非聚簇索引(二级索引)

非聚簇索引结构

1. 索引结构与数据分离：

   • 索引B+树的叶子节点不包含完整行数据
   • 只存储主键值(或ROWID)

2. 查找过程需要回表：

   二级索引查找 -> 找到主键 -> 通过主键到聚簇索引中查找完整数据

非聚簇索引示例

CREATE INDEX idx_name ON users(name);  -- 创建非聚簇索引

非聚簇索引的特点

• 支持多个：一个表可以有多个非聚簇索引
• 存储内容：索引列值 + 主键值
• 覆盖索引优化：如果查询的列都在索引中，可避免回表

         非聚簇索引的叶子节点存储的是：索引列值 + 主键值

MyISAM 的非聚簇索引

MyISAM 存储引擎使用的是纯非聚簇索引结构：

• 数据.MYD文件：存储实际数据行
• 索引.MYI文件：存储所有索引
• 索引叶子节点存储的是数据行指针(物理地址)

对比总结

MyISAM 的非聚簇索引：叶子节点存储的数据文件指针（相对于InnoDB非聚簇索引，避免了回表）

实际应用建议

1. 合理设计主键：短、有序(如自增INT)，利用好聚簇索引特性

2. 避免随机主键：如UUID会导致大量页分裂和碎片

3. 覆盖索引优化：使查询只需访问索引避免回表

思考：

1. 多实例应用从分布式ID服务获取ID后，由于网络延迟、处理速度差异等原因，后获取到的ID可能先提交事务，导致数据库中ID不是严格递增的，而是呈现"插入"形式（即小ID的记录可能出现在大ID之后）

（其实业务能容忍小范围的不一致）

方案1：业务逻辑的提交操作，尽量靠后，避免提前获取id?