MySQL中主键索引与聚焦索引之概念的学习教程

数据库  /  管理员 发布于 6年前   267

主键索引

主键索引，简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。

在MySQL中，InnoDB数据表的主键设计我们通常遵循几个原则：

采用一个没有业务用途的自增属性列作为主键；
主键字段值总是不更新，只有新增或者删除两种操作；
不选择会动态更新的类型，比如当前时间戳等。
这么做的好处有几点：

新增数据时，由于主键值是顺序增长的，innodb page发生分裂的概率降低了；可以参考以往的分享“[MySQL FAQ]系列 ― 为什么InnoDB表要建议用自增列做主键”；
业务数据有变更时，不修改主键值，物理存储位置发生变化的概率降低了，innodb page中产生碎片的概率也降低了。
MyISAM表因为是堆组织表，主键类型设计方面就可以这么讲究了。

辅助索引，就是我们常规所指的索引，原文是SECONDARY KEY。辅助索引里还可以再分为唯一索引，非唯一索引。

唯一索引其实应该叫做唯一性约束，它的作用是避免一列或多列值存在重复，是一种约束性索引。

在MyISAM引擎中，唯一索引除了key值允许存在NULL外，其余的和主键索引没有本质性区别。也就是说，在MyISAM引擎中，不允许存在NULL值的唯一索引，本质上和主键索引是一回事。

而在InnoDB引擎中，主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引，而唯一索引和普通辅助索引间除了唯一性约束外，在存储上没本质区别。

从查询性能上来说，在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的，在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程。InnoDB表基于普通索引的查找代价更高，因为每次检索到结果后，还需要至少再多检索一次才能确认是否还有更多符合条件的结果，主键索引和唯一索引就不需要这么做了。

经过测试，对100万行数据的MyISAM做随机检索（整数类型），主键和唯一索引的效率基本一样，普通索引的检索效率则慢了30%以上。换成InnoDB表的话，唯一索引比主键索引效率约慢9%，普通索引比主键索引约慢了50%以上。

聚集索引
在MySQL中，InnoDB引擎表是（聚集）索引组织表（clustered index organize table），而MyISAM引擎表则是堆组织表（heap organize table）。

也有人把聚集索引称为聚簇索引。

当然了，聚集索引的概念不是MySQL里特有的，其他数据库系统也同样有。

简言之，聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序，而非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。

我们先来看看两种存储形式的不同之处：
简单说，IOT表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。
IOT表相比HOT表的优势是：

范围查询效率更高；
数据频繁更新（聚集索引本身不更新）时，更不容易产生碎片；
特别适合有一小部分热点数据频繁读写的场景；
通过主键访问数据时快速可达；
IOT表的不足则有：

• 数据变化如果是离散为主的话，那么效率会比HOT表差；

HOT表的不足有：

• 索引回表读开销很大；
• 大部分数据读取时随机的，无法保证被顺序读取，开销大；
• 每张InnoDB表只能创建一个聚集索引，聚集索引可以由一列或多列组成。

上面说过，InnoDB是聚集索引组织表，它的聚集索引选择规则是这样的：

首先选择显式定义的主键索引做为聚集索引；
如果没有，则选择第一个不允许NULL的唯一索引；
还是没有的话，就采用InnoDB引擎内置的ROWID作为聚集索引；

可以看到，在这个索引结构的叶子节点中，节点key值是主键的值，而节点的value则存储其余列数据，以及额外的ROWID、rollback pointer、trx id等信息。

结合这个图，以及上面所述，我们可以知道：在InnoDB表中，其聚集索引相当于整张表，而整张表也是聚集索引。主键必然是聚集索引，而聚集索引则未必是主键。

MyISAM是堆组织表，它没有聚集索引的概念。

两者的比较

下面是一个简单的比较表