MYSQL索引

前言：

索引是存储引擎用于快速找到记录的一种数据结构，索引对于良好的性能非常关键，索引能够轻易将查询性能提高几个数量级。索引的概念和一本书的目录是一样的，想要在一本书中快速的找到特定主题，一般是先看书的目录，然后再去特定的页码，在MYSQL中也是一样的，存储引擎用类似的方法使用索引，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。下面会介绍一些索引相关的知识。

一.索引的类型

在MYSQL中，索引是在存储引擎层而不是服务器层实现的，所以并没有统一的索引标准，不同存储引擎的索引工作方式并不一样。下面是MYSQL支持的几种索引：

1.B-Tree 索引

大家一般没有指明特定索引名称的时候，大部分情况说的都是B-Tree索引，很明显B-Tree索引是用B-Tree数据结构来存储数据的。B-Tree索引能够加快访问速度，因为存储引擎不需要进行全表扫描来获取需要的数据，取而代之的是从索引的跟节点开始搜索，通过比较节点页的值和要查找的值可以找到合适的指针进入下一层节点。B-tree叶子节点会指向被索引的数据，而不是其他节点。B-Tree对索引列是顺序存储的，所以非常适合查找范围数据。如找到所有以1到K开头的名字，B-Tree适用于全键值，键值范围，键值前缀查找。

以下几种查询可以用B-Tree索引：

如对于索引（first_name,last_name,age）

（1）全值匹配：指的是和索引中的所有列进行匹配，可用于查找姓张名扬今年24岁的人。

（2）匹配最左前缀：指的是可以只使用最左边的列匹配，可用于查询姓张名扬的人。

（3）匹配列列前缀：指的是可以只使用某一列的开头部分来匹配，可用于查询姓以Z开头的人。

（4）匹配范围值：如用于查找姓在Allen和BarryMore之间的人，这里只使用索的第一列

（5）精确匹配某一列并范围匹配另外一列：如查找索引姓为Allen，名字是K开头的人。

（6）只访问索引的查询：指的是索引覆盖的情况，即要查的字段都已经被索引了，无需再查找数据行。

并且因为索引树种的节点是有序的，所以除了按值查找之外，索引还能用户按顺序查找（Order By），所以一般来说，如果B-Tree可以按照某种方式查找到值，那也可以按照这种方式来排序。

使用B-Tree索引也有一下限制：

（1）只能从索引的最左列开始查找，如果只要查找名字是扬的人，上面的索引就是无效的。

（2）必须按顺序使用索引，如查询姓张，年级是24岁的人，上面的索引也是无效的。

（3）如果查询中有某列是 范围查询，则它右边的列都无法使用查询，如查询姓是Z开头的，那么只有first_name列能被使用，索引的后面俩列无法被使用。

从上面这些我们也能看出来，对于B-Tree索引来说，定义的顺序非常重要，可以将范围查询条件放到查询的最后。

2.哈希索引

哈希索引是基于哈希表实现的，和JAVA里的MAP是一样一样的，就是根据Hash来找到数据，所以哈希索引只有做精确值查询的时候才有效。哈希索引也有一些限制：

（1）哈希索引只包含哈希值和指针，所以它必须要访问数据行。

（2）哈希索引不是按顺序存储的，所以没法用于排序。

（3）哈希索引不能用户部分查询和范围查询。

（4）如果哈希冲突很高，那么查询效率是很低的。

在InnoDB引擎中有一个特殊的功能叫“自适应哈希索引”，当InnoDB发现某些索引值被使用的非常频繁时，它会在B-Tree索引上再创建一个哈希索引，这样就能快速的找到对应的B-Tree节点。

上面俩种是比较主要的索引类型，MYSQL还支持空间数据索引（没听过），全文索引（需要单独研究）等等。

二.索引的优点

综合看索引有一下三个优点：

（1）索引大大减少了服务器需要扫描的数据量。

（2）索引可以帮助服务器避免排序和临时表。

（3）索引可以将随机IO变为顺序IO。

索引并不一定是最好的优化方案，对于数据量非常小的表，使用索引不如全表扫描，对于数据量非常大的表建索引的代价会十分昂贵，可以使用分区表来技术（需要单独研究），所以感觉所以适合中型数据量的表。

三.高性能的索引策略

知道了索引的基本情况后，思考一下如何最高性能的使用索引，下面有一些索引策略：

（1）独立的列：如果查询中的列不是独立的，而是一个表达式或者是函数，那么无法使用该索引，如Select actor_id from actor where actor_id+1=5。

（2）前缀索引和索引的选择性：有时候需要索引很长的字符串，这就会使索引变得大且慢，上文提到的哈希索引是一个方法。还有一种方法是只索引字符串的部分字符，这样可以节约索引空间提高效率。但这样会降低索引的选择性（一张表里不重复的索引值越多，选择性就越大，选择性越高，查询效率就会越高，所以主键索引是最快的）。对于BLOB，TEXT这样的类型，必须使用前缀索引。

使用前缀索引也有缺点，就是无法用作Order By和Group By，也不能用来做覆盖扫描。

概念上还有一个后缀索引，比如查找域名是.com结尾的，这个 MYSQL是不支持的，但是在存数据的时候可以把字符串反过来，然后用前缀索引去查询。

（3）多列索引

对于索引一个常见的错误是为每个列都创建独立的索引，或者按照错误的顺序创建多列索引。

在多个列上创建独立的索引大部分情况下并不能提高MYSQL的查询效率。在MYSQL5.0和更新的版本中，查询能对俩个单列索引进行联合扫描，比如

EXPLAIN SELECT film_id,actor_id from film_actor where actor_id=1 or film_id=1

在Extra中的信息会会是Using union（PRIMARY，idx_fx_film_id）;Using where

这表示MYSQL对这个sql进行了优化，这种优化通常代表了当前的索引策略非常的糟糕，需要进行优化，以下几点原则可以作为参考：

当服务器出现对多个索引做多个相交操作时（通常有多个AND条件），通常需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

当服务器 需要做多个联合操作时（通常是有多个OR操作），通常需要耗费大量资源，特别是当其中有些索引选择性不高，需要合并扫描返回的大量数据的时候。

并且优化器不会把这些计算到查询成本之中，这种情况会被人忽略。

四.聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。在innoDB中，聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行。实际上，在innoDB中可以直接把聚簇索引看成是一张表。

聚簇主键可能对性能有帮助，单也可能导致严重的性能问题，聚簇主键有下面一些优点：

（1）可以把相关数据保存在一起，这样只需要从磁盘读取少数的数据页就能获取数据。

（2）数据访问更快，因为索引和数据都在一起。

（3）使用覆盖索引扫描的查询可以直接使用页节点的主键值。

但也有一些缺点：

（1）聚簇数据最大限度的提高了IO密集型应用的性能，但是如果数据全都放在内存中，则访问的顺序也没那么重要了，聚簇索引 也就没什么优势了。

（2）插入速度严重依赖插入顺序。

（3）可能会导致全表扫描变慢。

（4）二级索引可能比想象的要大，因为在二级索引的叶子节点包含了引用行的主键列。

（5）二级索引访问需要俩次索引查找，而不是一次。

总的来看感觉好像弊大于利，不知道什么情况用这个，还需要研究研究。

索引是一个非常复杂的话题，在MYSQL中，大多数情况下都会使用B-Tree索引，其他类型的索引大多数只适用于其他目的。在使用索引时，有几个原则：

（1）单行访问时很慢的，如果服务器从存储中读取一个数据块只是为了获取其中一行，那么久浪费了很多工作，最好读取的块中能包含尽量多所需要的行，使用索引可以创建位置引用以提升效率。

（2）按顺序访问范围数据是很快的，并且GROUP BY查询也无需再做排序和将行按组进行聚合计算。

（3）索引覆盖查询是很快的。

总的来说，查询语句应该尽可能选择合适的索引以避免单行查找，尽可能使用数据原生顺序从而避免额外的排序操作，并尽可能的使用索引覆盖查询。