高性能Mysql - 创建高性能的索引(下)(聚簇索引,覆盖索引,全文索引)

时间:2021-07-20 19:04:42

一、聚簇索引

定义 并不是一种单独索引类型,而是一种数据存储方式。

聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一页就是P开头的部分,这就是物理存储顺序(聚簇索引);而不用你到目录,找到汉字“爬”所在的页码,然后根据页码找到这个字(非聚簇索引)。

具体的细节依赖于其实现方式,但InnoDB的聚簇索引实际上在用一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中。InnoDB 将通过主键聚集数据,没有定义主键,InnoDB会选择一个唯一的非空索引代替,如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇主键可能对性能有帮助,也可能导致严重的性能问题。

聚簇的数据优点:
1. 可以把相关数据保存在一起
2. 数据访问更快
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点:
1. 如果数据全部都放在内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势了。
2. 插入速度严重依赖于插入顺序
3. 更新聚簇索引列的代价很高。会强制InnoDB将每个被更新的行移动到新的位置。
4. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间。
5. 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏。
6. 二级索引(非聚簇索引)可能比想象汇总的要大,因为在二级索引的叶子节点包含了引用行的主键列。
7. 二级索引访问需要两次索引查找,而不是一次。
二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。
二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找到对应的行,两次B-Tree查找。

InnoDB 和 MyISAM的数据分布对比

MyISAM按照数据插入的顺序存储在磁盘上,因为行是定长的,所以MyISAM可以从表的开头跳过所需的字节找到需要的行。
而InnoDB支持聚簇索引,每一个叶子节点都包含了主键值,事务ID,用于事务和MVCC的回滚指针以及所有的剩余列。
高性能Mysql - 创建高性能的索引(下)(聚簇索引,覆盖索引,全文索引)


在InnoDB表中按主键顺序插入行


如果正在使用InnoDB表并且没有什么数据需要聚簇,那么可以定义一个代理键作为主键,并且设置为AUTO_INCREMENT 自增列。这样就可以保证数据行是按顺序写入的,对于根据主键做关联操作的性能会更好。实际上很多人都是这么干的,但并不明白为什么。

最好避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是I/O密集型应用。

二、覆盖索引

如果一个索引包含(覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。
在InnoDB如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。
哈希索引,空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引。


三、全文索引

全文索引是目前搜索引擎广泛使用的搜索技术,顾名思义,就是在全文快速匹配到具体的某个词。

原理是先定义一个词库,然后在文章中查找每个词条(term)出现的频率和位置,把这样的频率和位置信息按照词库的顺序归纳,这样就相当于对文件建立了一个以词库为目录的索引,这样查找某个词的时候就能很快的定位到该词出现的位置。

关于如果建立一个词库,如果是英文的话,可以根据空格来划分,但中文就要涉及到分词技术了。
全文索引对于模糊查询的性能提升是非常大的。

三、总结

引用别人[姜敏(http://www.cnblogs.com/aspnet2008/)]曾经总结过的几句话来描述一下索引的使用原则:

1:不要索引数据量不大的表,对于小表来讲,表扫描的成本并不高。
2:不要设置过多的索引,在没有聚集索引的表中,最大可以设置249个非聚集索引,过多的索引首先会带来更大的磁盘空间,而且在数据发生修改时,对索引的维护是特别消耗性能的。
3:合理应用复合索引,有某些情况下可以考虑创建包含所有输出列的覆盖索引。(覆盖索引的速度是非常快的)
4:对经常使用范围查询的字段,可能考虑聚集索引。
5:避免对不常用的列,逻辑性列,大字段列创建索引。