InnoDB的自适应哈希索引是由数据库创建的，DBA不能对它进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此，对于SELECT .. FROM TABLE WHERE COL=XX的查询速度非常快，但对于范围查找，则无能为力。
通过命令SHOW ENGINE INNODB STATUS，可以查看自适应哈希索引的使用情况。
FILE I/O
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
119187 OS file reads, 151798763 OS file writes, 928485 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 5, seg size 7,
18802 inserts, 18802 merged recs, 1359 merges
Hash table size 4425293, node heap has 770 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
可以通过参数innodb_adaptive_hash_index来控制是否开启自适应哈希索引。
参考《MySQL技术内幕 -InnoDB存储引擎》整理，如侵权请联系vinin@163.com。