MySQL聚簇索引

MySQL Cluster index是一种索引类型,它根据键值对数据行进行排序,每个表只有一个聚集索引。

InnoDB使用B-Tree作为索引的数据结构,聚簇索引的叶节点就是行数据本身。聚簇索引中存储了行数据,通常聚簇索引与主键同义。

聚簇索引一个种索引类型,同时也是一种数据存储方式,它将具有相同键值的数据存行存放在一起。

B-Tree

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

B树适合读写大数据块的存储系统,如:磁盘。

B树能减少定位经历的中间过程加快存取。

B树可用来描述外部存储,常被应用在数据库和文件系统。

B-Tree在线演示程序

聚簇索引

1、若表中定义了主键,会用它作为聚簇索引,若没有逻辑唯一且非空的列/一组列,可添加一个自增数值列。

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

3、若表没有主键或没有合适的唯一性索引,会在内部生成一个GEN_CLUST_INDEX隐形聚簇索引,此索引建立在包含了行ID的合成列上,排序依赖于行ID,行ID是一个6字节字段。

加速查询

通过聚簇索引访问行会很快,因为,索引直接指向包含行数据的页面。

若表很大,聚簇索引架构通常能避免用一个磁盘I/O完成行存储,而是用与索引记录不同的页面存储行数据。

二级索引

聚集索引之外,其他索引都称为二级索引。

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