MySQL索引

一、介绍

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。

 

二、索引的类型

1.B-Tree索引

最常用的索引，它使用B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引，Archive引擎是一个例外：5.1之前Archive不支持任务索引，直到5.1才支持单个自增列（AUTO_INCREMENT）的索引。

我们使用术语“B-Tree”，MySQL在CREATE TABLE和其他语句也使用该关键字。不过底层的存储引擎也可能使用不同的存储结构，例如，NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引；InnoDB则使用的是B+Tree。

存储引擎以不同的方式使用B-Tree索引，性能也各不相同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

索引结构如图所示：

    B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。B-Tree索引适合的查询类型如下：

• 全职匹配

全职匹配指的是和索引中的所有列进行匹配，例如查找“name = '张三'”。

• 匹配最左前缀

创建联合索引时，如（name，age）,查询条件是 “name = '张三'”，或者“name = '张三' and age = 1”。

• 匹配列前缀

也可以只匹配某一列的值的开头部门。比如姓名以A开头人名。

• 匹配范围值

可以匹配某一个值的范围值，如出生日期在“2000-01-01”到“2000-01-02”之间的人。

• 精确匹配某一列并范围匹配另外一列

比如查询条件是 “name = '张三' and age > 99”。

• 只访问索引的查询

B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行。

 

备注：索引生效情况如下图

  

2.哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。

在MySQL中，只有Memory引擎显示支持哈希索引，这也是Memory引擎的默认索引类型。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，如果多个列的哈希码相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引使用限制：

• 哈希索引值存储哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行数据；
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
• 哈希索引不支持部分索引列匹配查询，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
• 哈希索引只支持等值比较查询，包括=、IN()、<=>（支持 NULL = NULL） 。不支持任何范围查询；
• 访问哈希索引的数据非常快，除非出现哈希冲突，此时存储引擎需要遍历链表中所有行指针，逐行进行比较；
• 如果哈希冲突非常多的话，一些索引维护操作的成本也会非常高。

    适用场景：

比如存放网站的URL,适用B-Tree索引，会使索引存储的内容很大，而且也不需要使用排序特性。

如果存储引擎不支持哈希索引，也可以创建一个哈希列，用来存储索引。如CRC32()，FNV64()(哈希值为64位)，不建议使用SHA1()和MD5()（他们加密后的串太长，会浪费空间，速度也会变慢）。

3.空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。这类索引无须前缀查询，会从所有维度来索引数据。可以使用任意维度来组合查询，必须使用MySQL的GIS相关的函数如MBRCONTAINS()等来维护数据。但是MySQL的GIS支持并不完善，很少人使用。开源关系数据库系统中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

4.全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样，类似于搜索引擎所做的事情。在相同的列上同时创建全文索引和基于值的 B-Tree 索引不会有冲突；全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件操作。

三、索引的优点

最常见的 B-Tree 索引，按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的，所以 B-Tree 也就会将相关的列值都存储在一起。最后，因为索引中存储了实际列的值，所以某些查询只使用索引就能够完成全部查询。

索引的三大优点：

• 索引大大减少了服务器需要扫描的数据量；
• 索引可以帮助服务器避免排序和临时表；
• 索引可以将随机I/O变为顺序I/O。

三星系统——如何评价一个索引是否符合某个查询索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排序顺序一致则获得两星；如果索引中的列包含了查询中的所有列则获得三星（即覆盖索引）。

总的来说，只有当索引帮助存储引擎快速地找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。

四、高性能的索引策略

1.独立的列——索引列不能是表达式的一部分，也不能是函数的参数。

例如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5;

或者SELECT actor_id FROM actor WHERE f(actor_id) = 5;

2.前缀索引和索引选择性——有时候需要索引很长的字符列，这会让索引变得很大且很慢。

此时可以有两个策略，一个是自定义哈希索引，另一个就是前缀索引；前缀索引能大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性（索引选择性——不重复的索引值和数据表记录总数的比值）；

索引前缀长度的选择——计算法。

例如：LELECT COUNT(DISTINCT city)/COUNT(*) AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel2, ...; 如果前缀的选择性接近1就可以使用了。有时候只看平均选择型也不靠谱，还需要做进一步判断。

缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；有时候也可以使用前缀索引——可将对应列的字符串反序存储，并创建前缀索引。

3.多列索引——为多列创建合适的索引

多列索引。例如：key(col1, col2, col3)；

MySQL5.0之后的版本引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位表中的行；

索引合并策略有时候是一种优化后的结果，但实际上更说明表上的索引建得很糟糕。当出现服务器对多个索引做相交操作时（多个AND），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引;当服务器需要对多个索引做联合操作时（多个OR），通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。

4.选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；

索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求。

索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）；

避免随机I/O和排序；

对于某些特殊用户和分组，避免其使用普通的索引查询。

5.聚簇索引——聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，即索引叶子节点包含了全部数据，如InnoDB的主键索引。

6.覆盖索引——如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称为“覆盖索引”。

7.使用索引扫描排序——MySQL有两种方式可以生成有序结果：通过排序操作；按照索引顺序扫描。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（升序/降序）都一样时，MySQL才能使用索引来对结果做排序；

当查询需要关联多张表时，只有当ORDER BY子句引用的字段全部来自第一张表时，才能使用索引排序；

ORDER BY子句中的字段需要满足索引的最左前缀的要求，才能使用索引排序；

当索引的前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求也能使用索引排序。例如：key(rental_date, inventory_id, customer_id)；... where rental_data='2018-01-08' ORDER BY inventory_id DESC;

8.压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。

默认只压缩字符串，可以通过参数设置对整数做压缩，在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。

压缩块使用更少的空间，代价是某些操作可能更慢，因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度不错，但是如果是倒序扫描就不是很好了。

9.冗余和重复索引

MySQL允许在相同列上创建多个索引，但是需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。例如，PRIMARY(ID)和UNIQUE(ID)是重复索引，KEY(col)和FULLTEXT(col)(全文索引)则不是重复索引。

冗余索引是指如果创建了索引(A, B)，再创建索引(A)就是冗余索引，而(B, A)或(B)则不是冗余索引。

10.索引和锁

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE字句。

五、维护索引和表

维护表有三个目的：找到并修复损坏的表；维护准确的索引统计信息；减少碎片。

1.更新索引统计信息——MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，已决定如何使用索引信息。（1）、records_in_range();（2）、info()。如果存储引擎向优化器提供的索引统计信息不准确，就会导致优化器做出错误的优化决定，这会严重影响查询性能。可通过执行ANALYZE TABLE 来重新生成统计信息以解决这个问题。

2.减少索引和数据的碎片

B-Tree索引可能会碎片化，碎片化的索引可能会以很差或无序的方式存储在磁盘上，这会降低查询效率；

表数据存储也可能碎片化。主要有行碎片、行间碎片、剩余空间碎片三种。对于MyISAM表，这三类碎片都可能发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行，并重写到一个片段中。

【维护方法】可通过执行POTIMIZE TABLE或者导出再导入来重新整理数据；对于那些不支持POTIMIZE TABLE命令的引擎，可以执行ALTER TABLE操作来重建表。只需要将表的存储引擎改为当前的引擎即可。例如：ALTER TABLE <table> ENGINE=<engine>;