MySQL聚簇/聚集索引(Cluster Index)

MySQL聚簇索引是一种键值的逻辑顺序决定了表中对应行的物理顺序的特殊索引,每个InnoDB表都有一个称为聚簇索引的特殊索引。

InnoDB使用B-Tree结构作为索引的数据结构,可以这么粗糙的理解聚簇索引:聚簇索引的叶节点就是行数据本身,而非聚簇索引的叶节点是行的主键。MySQL的聚簇索引中存储了行的数据。通常聚簇索引与主键同义 ,因为主键是有索引的,而聚簇索引通常以主键列做为索引列。

索引记录存储在B树或R树数据结构的叶子节点中(叶子页),索引页的默认大小为16KB。将新记录插入到InnoDB 聚簇索引中时, InnoDB会尝试将页面的1/16保留,以便将来插入和更新索引记录。

如果按顺序(升序或降序)插入索引记录,则生成的索引页对占满的定义大约为15/16。如果以随机顺序插入记录,则页面对占满的定义从1/2到15/16。InnoDB在创建或重建B树索引时执行批量加载。这种索引创建方法称为排序索引构建。

B-Tree数据结构

B树(B-tree)是一种自平衡的树,能够保证数据有序并自动维护树的深度(索引平衡),这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作,都在对数时间内完成。

B树适用于读写大数据块的存储系统,例如磁盘。B树能够减少定位记录时,所经历的中间过程,从而加快存取速度。B树这种数据结构可以用来描述外部存储。因此常被应用在数据库和文件系统的实现上。

有关B-Tree数据结构的工作过程,可以访问University of San Francisco制作的B-Tree在线演示程序,以了解更多B-Tree数据结构的工作原理。

聚簇和二级索引

1、如果表中定义了主键,InnoDB会用它作为聚簇索引,所以应该为每个表定义主键,如果没有逻辑唯一且非空的列或一组列,可以添加一个数值自增性的列。

2、如果表中没有定义主键,MySQL会从所有非空的唯一性索引列中选第一个作为InnoDB的聚簇索引。

3、如果表没有主键或没有合适的唯一性索引,则在InnoDB会在内部生成一个名为GEN_CLUST_INDEX的隐形的聚簇索引,这个聚簇索引建立在一个包含了行ID在内的合成列上。

行的排序依赖于InnoDB分配给表的行ID,行ID是一个6字节的字段,在插入新行时会自增。因此,行的排序由插入时的物理行ID决定。

聚簇索引如何加快查询速度

通过聚簇索引访问行会很快,因为索引直接指向包含行数据的页面。如果表很大,聚簇索引架构通常会避免,使用一个磁盘I/O操作来完成行存储,而是,使用与索引记录不同的页面存储行数据。

二级索引如何与聚集索引相关联

除聚集索引之外的所有索引都称为辅助索引(二级索引)。在InnoDB中,每条辅助索引记录都包含着该行的主键列,或专为辅助索引而定的列。InnoDB的聚簇索引使用主键值进行搜索得到行数据。如果主键很长,二级索引会占用更多空间,因此使用短的主键是有好处的。

B-Tree索引的特点

InnoDB使用的B-Tree索引比较适合作范围查询,可以在条件表达式中使用 =, >, >=, <, <=,或BETWEEN等操作。 如果LIKE的参数不以通配符开头,而是常量字符串,则索引也可用于LIKE条件。

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

而以下条件则不使用索引

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

以下语句使用索引

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

使用index = 1 或者 index = 2 做为索引

... WHERE index=1 OR A=10 AND index=2

优化like为 index_part1='hello' 

... WHERE index_part1='hello' AND index_part3=5

index1会使用索引但是index2、index3不会使用索引

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

以下语句不使用索引

index_part1索引不在查询条件中

... WHERE index_part2=1 AND index_part3=2

WHERE子句的两个部分都没有使用索引

... WHERE index=1 OR A=10

索引不能跨越行

... WHERE index_part1=1 OR index_part2=10