MySQL索引

前言

 前面我们提到MySQL的加锁，锁是作用于索引的。那么本篇文章说下索引。

聚簇索引(Clustered Indexes)

InnoDB存储引擎的数据组织方式,是聚簇索引表：完整的记录,存储在主键索引中,通过主键索引,就可以获取记录所有的列.

每个InnoDB的表有一个特殊的索引称之为聚簇索引,每行的数据就是存储在聚簇索引中.通常,聚簇索引和主键同义.

当你在你的表上面定义一个主键时,InnoDB将其作为聚簇索引.建议为你的表都创建一个主键.如果没有唯一并且非空的一列或者多列(用来做你的主键),那么可以创建一个自动填充的自增列(比如ID)

如果你的表没有定义主键,MySQL会将第一个所有列都非空的UNIQUE索引作为聚簇索引.

如果你的表不存在这样的UNIQUE索引(见上),InnoDB内部会自动隐式生成一个包含行ID的列并在其上面建立聚簇索引.这一列按行ID排序.行ID是一个6-byte的严格单调自增的字段.因此,按照行在物理上是按照插入顺序排序的.

总额来说聚簇索引是由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。

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

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

之所以这么做的几点优势：

1. 新增数据时，由于主键值是顺序增长的，innodb page发生分裂的概率降低了；
2. 业务数据有变更时，不修改主键值，物理存储位置发生变化的概率降低了，innodb page中产生碎片的概率也降低了。

聚簇所有是如何加速查询的呢?

通过聚簇所有访问一行非常快,这是因为在聚簇索引上搜索会直接定位到包含你需要的行的数据所在的页上(page).

二级索引(Secondary Indexes)

除了聚簇索引其他索引都是二级索引.其可以划分为：唯一索引、非唯一索引。

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

在InnoDB中每个二级索引记录都包含了这一行的主键列和当前这个二级索引包含的列.

InnoDB使用二级索引中包含的主键取索引这一行对应的聚簇索引,进而找到这一行完整的数据.

注意：如果主键很长,则二级索引会占有更多的空间,因此建议使用短的列做主键.

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