首发：www.leroyling.com/archives/my…

生活中，如果你想要快速的在一本书中找到某个你感兴趣的内容，一般来讲是会先看书的“索引”部分，也就是书的目录，找到对应的页码之后，再翻去看你感兴趣的具体内容。

在MySQL中，存储引擎也用的类似的方法使用索引，即现在索引中找到对应的值，然后根据匹配到的索引的记录找到对应的数据行。索引可以包含一个或多个列的值，如果索引含有多个列，那么如何放置列的顺序也是相当重要的，因为在MySQL中只能高效的使用索引的最左前缀列。所以在MySQL中创建一个包含两个列的索引，和单独创建两个各自只包含一个列的索引效果也会大不一样。

索引的类型

1.1 B-Tree索引

一般来说，当我们说到索引的时候，如果没有特别的指明类型，那么多半来说，默认说的就是B-Tree索引了（不同的存储引擎中，可能使用了不同的存储结构，如InnoDB引擎中使用的是B+Tree）。

使用B-Tree索引，意味着所存储的值是按照顺序存储的。使用索引之所以能加快访问数据的速度，是因为存储引擎不在需要对数据进行全表扫描了，而是从索引的根节点开始搜索，通过比较节点页的值和实际要查找的值，从而找到合适的指针进入下层的子节点进行查找。以一张简单的表为例：

CREATE TABLE ORDER(
order_id varchar(32) not null,
product_code varchar(20) not null,
order_time datetime not null，
order_price decimal(10,2) not null default 0,
address varchar(100) not null
key(order_id,product_code,order_time)
)

对于上面表中的每一条数据，索引中都包含了 order_id, product_code, order_time这三列的值。对于多个列的值进行排序的时候，排序规则是根据create语句中定义索引时使用的列顺序来进行的，以上表为例，即排序的顺序为order_id, product_code, order_time。

能够命中索引的查询方式

全值匹配指的是查询语句中where条件的列和索引中的所有列都匹配，比如前面表中的

select * from order where order_id ='o001' and product_code='p001' and order_time='2019-12-05';

最左前缀匹配指的是查询条件命中了索引从左到右的部分顺序

select * from order where order_id='o001';

select * from order where order_id='o001' and product_code='p001'

列前缀匹配只匹配到了索引中列的开头部分

select * from order where order_id like 'o001%';

select * from order where order_id ='o0001' and product_code like 'p001%';

列范围值匹配

select * from order where order_id >='o001';

select * from order where order_id = 'o001' and product_code >'p001';

select * from order where order_id = 'o001' and product_code ='p001' and order_time >'2019-12-01';

只访问索引的查询

即查询只需要访问索引而无需访问数据行，此时这种查询进化为一种名为“覆盖索引”查询。所谓的“覆盖索引”指的是如果一个索引包含（或者说覆盖）所有需要查询返回的字段的值，那么这个索引就称之为“覆盖索引”。此时查询结果可以使用索引来直接获取列的数据，不再需要回表读取数据行。

1.2 哈希索引

哈希索引是基于哈希表实现的，只能精准匹配索引的所有列的查询时才会生效。在Mysql中，只有Memory引擎显式的支持哈希索引，这也是Memory引擎的默认索引类型，同时Memory引擎也支持B-Tree索引。

因为哈希索引自身只需要存储对应的哈希值，所以索引的结构会十分的紧凑，这也让哈希索引的查找速度变的非常快。但是哈希索引在使用时也有它的一些限制：

哈希索引只包含哈希值和行指针，不存储字段值，因此不能使用索引中的值来避免读取行数据。不过由于访问内容中的行数据的速度很快，所以大部分的情况下这一点对性能的影响并不明显。
因为哈希索引的数据并不是按照索引值的顺序存储的，所以哈希索引无法用于排序。
因为哈希索引时所有列的哈希，因为不支持部分索引列的匹配查找。比如对于Index(key1,key2)的哈希索引而言，如果查询条件中只有key1，那么查询的时候将不会使用索引查询。
哈希索引只支持等值比较查询，包括=、in()、<=>，不支持热呢范围查询，比如 where order_time >'2019-12-01'。
访问哈希索引的数据速度非常快，除非有很多的哈希冲突（不同的索引列值却有着相同的哈希值）。当出现哈希冲突的时候，存储引擎必须要遍历链表中的所有行指针，对数据进行逐行比较，直到找到所有符合条件的数据。
如果哈希冲突很多的话，一些索引的维护操作代价会变得很高昂。比如删除表中的一条数据时，存储引擎需要遍历对应哈希表的链表中的每一行，找到并删除对应行的引用，此时，哈希冲突越多，代价越高昂。

在InnoDB引擎中有个特殊的功能叫“自适应哈希索引（adaptive hash index）”。说的是当InnoDB注意到某些索引值被应用的非常频繁的时候，它会在内存中基于B-Tree索引之上再建一个哈希索引，这样会让B-Tree索引也具有一部分的哈希索引的优点，比如快速的哈希查找。不过这是一个完全自动的引擎内部的行为，用户无法控制或配置。

1.3 空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。这类索引无需前缀查询，空间索引会从所有维度来索引数据。查询时，可以有效的使用任意维度来组合查询。但是必须要使用MYySQL的GIS相关函数来维护数据。MySQL的GIS支持的并不完善，因为大部分人不会去使用这个特性。

1.4 全文索引

全文索引时一种特殊类型的额索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。全文索引更类似于搜索引擎做的事情，而不是简单的where查询条件匹配。

在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突。

索引优化策略

建立索引的列不要参与数学计算或者函数计算。
需要索引很长字符的列时，通过验证索引的选择性，对该列进行前缀索引。
由于MySQL原生不支持反向索引，当遇到需要进行后缀数据索引查询时，可以通过将字符反转后存储，并基于此建立前缀索引。通过触发器来维护此类索引（自定义索引）。
当有多个列字段需要索引时，优先考虑联合索引，而不是将每个列单独的创建索引
多列索引中需要正确选择索引列的顺序，以便更好地满足排序和分组的需要。

《跬步千里系列》高性能MySql之索引到底是个啥