mysql为什么不用hash_MySql BTree和Hash索引的比较，为什么InnoDB不使用Hash索引

MYSQL 索引分析

1.B+Tree

之所以Btree能加快访问数据的速度，是因为存储引擎不再需要进行全表扫描

只需要从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，

存储引擎会根据这些指针向下层查找，而这些指针实际上定义了子节点页中值的上限和下线

比如：

create table people(

xing varchar(20) not null,

ming varchar(20) not null,

birthday date not null,

gender enum('male','unknow','female') not null,

key(last_name ,first_name,birthday)

)

索引如果查询多条数据，也是可以排序的

2. BTREE索引使用的限制

1. 如果不是按照最左列开始查找，无法使用索引

例如无法用于查找 ming为XXX的，也无法查找 birthday为XXX的

ABC三个组成的索引，只能A 、AB、 ABC

其它的组合都是无法用到索引的

2. 如果中间有一列是范围查询，则之后的列不能用于索引查询

比如

WHERE A =XXX AND B like 'XXX%' and C =XXX

这个索引只能用到前两列

HASH索引

在InnoDB上面创建HASH索引

我们利用

先查询当前数据库的版本

mysql> select version();

查询当前实际表结构

show create table peopleHash ;

mysql> show create table peopleHash ;

| peopleHash | CREATE TABLE `peoplehash` (

`xing` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,

`ming` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,

`birthday` date NOT NULL,

`gender` enum('male','unknow','female') COLLATE utf8mb4_general_ci NOT NULL,

KEY `ming` (`ming`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

2. 1 为什么在InnoDB创建HASH索引失败？

结果并没有出现我们想要的USING HASH ，也就是说创建HASH索引失败

为什么呢？

下面是MYSQL官方的回答：

1.不支持HASH索引(但是InnoDB在内部利用哈希索引来实现其自适应哈希索引功能。)

2.也就是InnoDB会根据表的使用情况自动为表生成hash索引，不能人为干预是否在InnoDB一张表中创建HASH索引

3.或者说，如果InnoDB注意到某些索引值被使用的特别频繁时，

它会在内存中基于Btree的索引之上再创建一个HASH索引，这样BTREE索引也具备了HASH索引的一些优点

但是也没有支持HSAH索引的引擎呢？

答案是肯定的：

MEMORY引擎是支持的

2.2 为什么InnoDB和MyISAM引擎不支持HASH索引？

1. HASH索引本身只存储对应的HASH值和行指针，而不是存储字段值

2. HASH索引并不是按照索引顺序来存储的，因此无法排序

3. HASH索引不支持部分索引列查找，因为HASH索引是使用全部的内容来计算HASH值的

例如在(A,B)两列建立索引，只查询A无法使用索引

4.HASH索引只支持等值比较查询，包括 =、IN()不能进行任何的范围查询

HASH冲突

5. 最严重的是既然HASH值是数字，肯定会出现相同的，也就是HASH冲突

6. 出现HASH冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行

7. 当HASH冲突特别多的时候，维护操作的成本就会变大，比如一次数据的删除

引擎需要遍历对应HASH值链表上的每一行，找到并删除对应的引用，冲突越多，代价越大

3. 模拟一个HASH索引

比如只有两个字段 ID 和URL

我们想查询URL是什么的 select id from XXX where url ='http://XXXXXXXX'

如果用BTREE来存储URL，则需要用到大量的空间，那我们还想用索引，怎么办呢？

删除URL原来的索引，新增一个字段 url_crc 使用CRC32做HASH

select id from XXX where url ='http://XXXXXXXX' and url_crc =CRC32('http://XXXXXXXX')

1.只要在插入和更新数据的时候，对url_crc进行更新就可以了

2. 但是如果用这个方式，记住不要用SHA1() MD5()作为HASH函数

3. 因为那两个函数的hash值非常长，会浪费大量的空间，但是如果数据量非常大，CRC32()会出现大量冲突

4. 自定义64位的哈希函数

简单的做法是：用MD5()函数返回值的一部分作为自定义哈希函数

select CONV(RIGHT(MD5('http://XXXXXXX'),16),16,10)AS HASH64;

插入和更新的时候都可以用这个了

insert into XXX

(id,url,url_crc) values

(XXX,XXX ,CONV(RIGHT(MD5('http://XXXXXXX'),16),16,10));

4. 索引是最好的解决方案吗

当然不是

1.对于数据量特别小的表，全表扫描更高效，没必要索引

2.中到大型的表，索引很高效，没有的话，速度很低

3.超大型表，由于数据量大，索引使用的代价会很大，这种情况下可以使用

分区技术