MySQL-聚集索引和非聚集索引

• 查看MySQL存储引擎

show variables like '%storage_engine%';

 

1、聚集索引

• 聚集索引特征

1. 索引的数据的物理存放顺序和索引的顺序一致(数据的存放顺序根据索引顺序存放)
2. 每张表只能有一个聚集索引(一个表中的记录只能以一种物理顺序存放)
3. 索引是通过二叉树的数据结构来描述的，可以理解为：索引的叶节点就是数据节点
4. 聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），但查询数据比非聚集数据的速度快
5. 对聚集索引列进行排序，这会带来磁盘IO性能非常大的损耗(因为数据也会跟着排序)

• 聚集索引选择规则

1. 如果显式定义了主键，那这个主键就作为聚集索引
2. 如果没有显式定义主键，那选择该表第一个非null唯一索引作为聚集索引
3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引

• 聚集索引适用场景（只针对innodb存储引擎,myisam不存在这说法）

1. 主键列,该列在where子句中使用并且插入是随机的。
2. 按范围查询的列
3. group by或order by的列
4. 不经常修改的列，不能建立在自增列上
5. 在连接操作中使用的列

2、非聚集索引

• 非聚集索引特征

1. 索引的数据的物理存放顺序不按照索引排序(非聚集索引就是普通索引，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序)
2. 每张表可以有多个非聚集索引
3. 非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

3、存储引擎 MyISAM 和 InnoDB：

1. MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址
2. InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引

4、MySQL主键的选取原则：

1. 非分布式架构直接套用自增id做主键
2. 小规模分布式架构用uuid或者自增id+步长做主键
3. 大规模分布式架构用自建的id生成器做主键