一、索引类型
- B-Tree索引
索引默认指的就是这种索引,大多数MySQL引擎都支持这种索引。B-Tree通常意味着所有的值都是按照顺序存储的,并且每个叶子页到根的距离相同。
B-Tree索引适用有下:
1)全值匹配:全值匹配指的是和索引中的所有列进行匹配。
2)匹配最左前缀:匹配索引左边起部分的列(遵循左前缀匹配规则)。
3)匹配列前缀:匹配索引值列值的前缀。
4)匹配范围值:匹配索引值列的范围(遵循左前缀匹配规则)。
5)精确匹配某一列并范围匹配另外一列:可以先匹配索引左前列值,并范围匹配紧跟后边索引列值(遵循左前缀匹配规则)。
6)只访问索引的查询:也称覆盖索引查询,查询数据只需要访问索引,无须访问数据行。
索引还可以用于查询中ORDER BY操作,如果ORDER BY子句中满足前边的集中查询类型,则这个索引可以满足对应的排序需求。
注意:不能跳过索引中的列,如果跳过,索引只会生效跳过列前边的列值;如果查询中有某个列的查询范围,则其右边所有列都无法使用索引(左前缀匹配规则)。 - 哈希索引
哈希索引只存储哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。
哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
哈希索引也不支持部分索引匹配查找。因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询,不支持任何范围查询。
访问哈希索引的数据非常快,当数据量大,有很多哈希冲突的时候,必须遍历链表进行比较,或者维护链表值。
创建自定义哈希索引:B-Tree索引情况下,可以利用哈希优点,为某些列多增加一个哈希值列,来加快查询。 - 空间数据索引R-Tree(很少用)
MyISAM表支持空间数据索引,它将会从所有维度来索引数据。 - 全文索引
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。在相同的列上同时创建全文索引和基于值的B-Tree索引不会冲突,全文索引适用于MATCH AGAINST操作,而不是普通的WHERE条件操作。
二、高性能索引策略
有时需要索引很长的字符列,这会让索引变得大而且慢,一个策略就是使用前边说的自定义哈希索引。
索引选择性是指,不重复的索引值(也称基数cardinality)和数据表的记录总数(#T)的比值,范围从1/#T~1之间。索引选择性越高则查询效率越高。
- 前缀索引,前缀索引就是索引很长值的列时,取这个列的值左边部分数据,取的长度按照索引选择性原则,维护一个新列,然后进行索引匹配,有缺点就是无法做覆盖查询,以及ORDER BY和GROUP BY。
- 选择合适的索引列顺序,当不考虑排序和分组的时,将选择性最高的列放在最前面通常是最好的。但也要根据情况,选择那些运行频率最高的条件来调整索引列的顺序。
- 聚簇索引,是一种数据存储方式。术语”聚簇索引“表示数据行和相邻的键值紧凑地存储在一起。如果没有定义主键,InnoDB会选择一个唯一的非空索引代替,如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。MyISAM按照插入的顺序存储在磁盘上。使用InnoDB时应该尽可能地按照主键顺序插入数据,并且尽可能地使用单调增加的聚簇键的值来插入新行。因为如果不按照这样,写入时乱序的,需要寻找目的页,导致大量的随机I/O,而且InnoDB不得不频繁做页分裂操作,最终导致数据碎片。
InnoDB二级索引的叶子节点都包含了主键的值。 - 顺序主键坏处:高并发工作负载,主键的上界可能成为“热点”,间隙锁竞争。另外一个可能是AUTO_INCREMENT锁机制。这时可以考虑重新设计表或应用,或者更改innodb_autoinc_lock_mode配置。
- 覆盖索引,如果一个索引包含(或者说覆盖)所有需要查询的字段的值,就是覆盖索引。
好处:索引条目通常远小于数据行大小、所以只需要读取索引,极大减少数据访问量。索引是按照列值顺序存储的,范围查询会比随机从磁盘读取每一行数据的I/O要快得多。InnoDB数据存储方式是聚簇索引,它二级索引在叶子节点保存行的主键值,如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。
使用EXPLAIN中,当Extra列有“using index”就是判定是覆盖索引查询(和type列没有关系,type显示查找行的方式)。
三、其他的一些建议
- 使用索引排序,MySQL有两种生成有序的结果:通过排序操作;或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”,说明MySQL使用了索引扫描来做排序。只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(正/倒序)都一样,才能使用索引排序。
- 避免冗余和重复索引,如果创建了索引(A,B),再创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。
- 尽量使用等值查询代替范围查询,对于范围条件查询,MySQL无法再使用范围列后边的其他索引列了,但是对于“多个等值条件查询”则没有这个限制(使用in代替>、<等范围查询)。
- 延迟关联,通过使用覆盖索引查询返回需要的主键,然后通过这些主键关联原表获取需要的行,延迟对列的访问(这是应对一些查询条件苛刻,通过使用覆盖索引,快速获取数据其他列的方式)。
- 减少索引和数据的碎片,表的数据存储有三种类型数据碎片,行碎片、行间碎片、剩余空间碎片。可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。先删除索引,然后再重新创建索引的方式也是一种消除索引碎片化的一种方式。