一次排除因为自适应哈希索引（adaptive hash index）造成的MySQL数据库崩溃的经历，并探讨MySQL在何种情况下应该激活自适应哈希索引。
01
—
MySQL数据库崩溃
一个客户的MySQL数据库隔一段时间就会莫名其妙地重新启动，在错误日志对应的时间点里下面的记录：
代码语言：javascript复制--Thread 140508672222976 has waited at row0purge.cc line 113 for 241 seconds the semaphore:
X-lock on RW-latch at 0x7fcac68e7b80 created in file buf0buf.cc line 1474
a writer (thread id 140508724832000) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0sea.cc line 1037
Last time write locked in file /build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/buf/buf0flu.cc line 1213
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0
=====================================
2024-01-31 18:09:35 0x7fcaba7f0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 118905 srv_active, 0 srv_shutdown, 878621 srv_idle
srv_master_thread log flush and writes: 997526
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1346616
--Thread 140502119106304 has waited at row0upd.cc line 2882 for 236 seconds the semaphore:
X-lock on RW-latch at 0x7fcac6937708 created in file buf0buf.cc line 1474
a writer (thread id 140508724832000) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0sea.cc line 1037
Last time write locked in file /build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/buf/buf0flu.cc line 1213
--Thread 140508680615680 has waited at log0log.cc line 1746 for 198 seconds the semaphore:
S-lock on RW-latch at 0x579b7e0 created in file log0log.cc line 846
a writer (thread id 140508680615680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file log0log.cc line 1746
Last time write locked in file /build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/log/log0log.cc line 1638
--Thread 140508672222976 has waited at row0purge.cc line 113 for 242 seconds the semaphore:
X-lock on RW-latch at 0x7fcac68e7b80 created in file buf0buf.cc line 1474
a writer (thread id 140508724832000) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0sea.cc line 1037
Last time write locked in file /build/mysql-5.7-pMP6e2/mysql-5.7-5.7.42/storage/innobase/buf/buf0flu.cc line 1213
OS WAIT ARRAY INFO: signal count 1471450
RW-shared spins 0, rounds 971756, OS waits 214261
RW-excl spins 0, rounds 7944761, OS waits 111771
RW-sx spins 68624, rounds 1162189, OS waits 17279
Spin rounds per wait: 971756.00 RW-shared, 7944761.00 RW-excl, 16.94 RW-sx
------------
TRANSACTIONS
------------从出错信息看，是锁竞争造成线程等待引起的，查了一下资料，在官方文档的关于自适应哈希索引（https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html）中有下面的信息：
You can monitor adaptive hash index use and contention in the
SEMAPHORESsection ofSHOW ENGINE INNODB STATUSoutput. If there are numerous threads waiting on rw-latches created inbtr0sea.c, consider increasing the number of adaptive hash index partitions or disabling the adaptive hash index.
MySQL的错误日志中的确有很多关于btr0sea.cc的锁：
代码语言：javascript复制Last time read locked in file btr0sea.cc line 1037使用下面的命令把自适应哈希索引封住：mysql> set global innodb_adaptive_hash_index=0;然后故障排除
。
注意还要修改MySQL的配置文件my.cnf，使下次启动后修改继续生效。
02
—
适应哈希索引的适用场景
在分析了自适应哈希索引的原理后，姚远提醒您在使用这个特性时需要注意下面的问题：
- 只适用查询语句，并且判断条件是等于，不适用于like和between等非等于的操作符。
- 对于修改语句因为要维护自适应哈希索引的架构，反而增加了操作的成本。
自适应哈希索引默认是激活的，因为MySQL最初设计的场景是针对互联网的应用，互联网的应用的特点就是主要是查询操作，很少有修改。因为如果您的应用不是此类应用建议您关闭这个特性，方法是在启动时增加--skip-innodb-adaptive-hash-index这个参数。
关于号主，姚远：
- Oracle ACE（Oracle和MySQL数据库方向）
- 华为云最有价值专家
- 《MySQL 8.0运维与优化》的作者
- 拥有 Oracle 10g、12c和19c OCM等数十项数据库认证
- 曾任IBM公司数据库部门经理
- 20 年DBA经验，服务2万 客户
- 精通C和Java，发明两项计算机专利
