MySQL支持的索引类型（B-Tree索引、hash索引）

　　索引，是存储引擎用于快速找到记录的一种数据结构。尤其是在表中的数据量越来越大时，索引对于性能的提升非常关键。今天先聊一聊MySQL支持的两种主要的索引类型。
　　
　　在MySQL中，存储引擎在使用索引时，会先在索引中找到对应值，然后根据所匹配的索引记录找到对应的数据行。例如：

select name from user where id = 10;

　　若在id列上建有索引，则mysql将使用该索引找到id = 10的行，然后返回所有包含该值的数据行。

B-Tree索引

　　大多数的MySQL引擎都支持B-Tree索引，它底层使用的是B+Tree这种数据结构来存储数据的。若是有人对B-Tree这种数据结构不太熟悉的话，可以参考我之前的一篇博文简单剖析B-Tree与B+Tree。
　　
　　B-Tree索引是一个平衡查找树，叶子到根部的节点距离相等。所有的记录都是按照键值的大小排列，叶子结点由指针连接。

B-Tree索引的特点

1、B-tree索引可以加快数据的查询速度
　　存储引擎不需要进行全表扫描来获得需要的数据，取而代之的是从索引的根节点开始进行搜索。然后根据指针逐层向下查找，通过比较节点页的值和有目标值就可以找到合适的指针进入下层节点，而这些指针实际上定义了子节点页中值的上限和下限。
　　
2、B-tree索引更适合进行范围查询
　　因为前面说过，B-tree对索引是顺序组织存储的，所以就很适合进行查找范围数据。
　　

B-tree索引的使用场景

1、全值匹配的查询
　　指的是和索引中的所有列进行匹配，比如查询字段 name = ‘tom’;
　　
2、匹配最左前缀的查询
　　比如为ａ列和ｂ列设置联合索引，只要联合索引的第一列（ａ列）符合查询条件，索引就会被用到，若只是第二列（ｂ列）符合条件则不会被用到该索引。
　　
３、匹配列前缀的查询
　　只匹配某一列的值的开头部分
　　
4、匹配范围值

5、精准匹配某一列并范围匹配另外一列　

6、只访问索引的查询
　　在这里指的就是覆盖索引，即只需要访问索引，而无需访问数据行　
　　
7、用于查询中的order by 操作
　　索引树中的节点是有序的。一般来说，若B-Tree可以按照某种方式查找到该值，那么也可以用这种方式用于排序。所以，如果 order by 子句中满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

B-Tree索引的限制

1、若不是按照索引的最左列开始查找，则无法使用该索引
　　比如建立联合索引（name 、phone_num），若搜索phone_num则无法使用该索引
　　
2、使用索引时，不能跳过索引中的列
　　比如建立联合索引（name 、phone_num 、addr），若搜索name和addr 则无法使用该索引只能使用那么过滤

3、not in 和 <> 操作无法使用该索引

4、若查询中有某个列的范围查询，则其右边的所有列都无法使用索引

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

　　至此，我们基本已经将B-Tree索引介绍完了，下面我们来了解另外的一种MySQL的索引类型：HASH索引。

HASH索引

　　在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。

hash索引的特点

1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。

2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

3、hash索引包括键值、hash码和指针 。

　　因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash索引的限制

1、Hash索引必须进行二次查找
　　使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
　　
2、hash索引不能用于外排序
　　hash索引存储的是hash码而不是键值，所以无法用于外排序
　　
3、hash索引不支持部分索引查找也不支持范围查找
　　只能用到等值查询，不能范围和模糊查询
　　
4、hash索引中的hash码的计算可能存在hash冲突
　　当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）
　　
　　上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

创建自定义hash索引

　　若存储引擎不支持hash索引，又想拥有hash索引所带来的性能提升，则可以模拟InnoDB一样创建哈希索引。
　　思路也比较简单，就是在B-tree基础上创建一个伪哈希索引。这和真正的hash索引不是一回事，因为还是采用B-Tree进行查找，但是它使用的是hash值而不是键本身进行查找。只需要在查询的where子句中手动指定使用hash函数即可。下面举个简单的例子：

　　比如：当我们需要存储大量的URL，并需要根据URL进行搜索查找。若用B-Tree来存储URL，存储的内容就会很大。此时的查询语句就是：

select id from url where url = "www.baidu.com";

若删除原来的url列上的索引，而新增一个被索引的url_crc列，使用crc32做hash函数，则可以使用如下方式查询：

select id from url where url = "www.baidu.com" and url_crc=CRC32("www.baidu.com");

　　这样做的话，性能就会有很大提升，因为mysql优化器会使用这个选择性高而体积很小的基于url_crc列的多音来完成查找。即使有多个记录相同的索引值，查找仍然很快，只需要根据hash值做快速的整数比较就能找到索引条目，然后一一返回对应的行。

缺点
1、需要维护hash值，可以手动维护，也可以使用触发器实现。
2、若数据表非常大的话，CRC32（）会出现大量hash冲突，则可以自己实现一个64位的hash函数，这个自定义的hash函数要返回整数而不是字符串，因为范围整数，对此效率更高。一个简单的办法就是使用MD5（）函数返回值的一部分来作为自定义的hash函数。但是这可能比自己写一个hash算法性能要差一些。