关闭AHI以后，查询就得读磁盘吗？分清InnoDB里的两种hash

关闭AHI以后查询就得读磁盘吗分清InnoDB里的两种hash在 InnoDB 内存结构笔记里有两句话放得很近按表空间 ID 和页号做 hash能找到内存中的页。自适应哈希索引也能帮助查询更快找到数据。看起来都在“用 hash 找东西”。那它们是不是同一个结构如果关掉自适应哈希索引原来缓存在内存里的数据还能不能继续用先不急着给这几个名词下定义。我们找一条能反复执行的查询把开关前后的变化看出来。先让同一条记录被查很多次实验表hot有 10000 行主键从 1 到 10000。v是主键的两倍另放一列填充数据。我们反复查这一行SELECT id, v FROM hot WHERE id777;每次都应该返回777、1554。这里故意只盯一条热点记录让相关页在缓冲池里反复被访问不是模拟真实业务的流量分布。本次使用独立的 MySQL 8.0.46 实例Buffer Pool 为 64 MiB。先打开 AHI查询 4000 次预热再单独记录接下来 2000 次查询的计数变化。随后关闭 AHI不重启、不清缓存仍执行同样的 2000 次查询。得到下面的结果。数字都是这一小段测试的增量不是服务器启动以来的累计值。每轮2000次查询 ON预热后 AHI搜索: 2000 B-tree搜索: 0 逻辑读请求: 4000 新增读页: 0 OFF AHI搜索: 0 B-tree搜索: 2000 逻辑读请求: 4000 新增读页: 0 再ON预热后 AHI搜索: 2000 B-tree搜索: 0 逻辑读请求: 4000 新增读页: 0关闭以后返回值一条没变。比较明显的变化是AHI 搜索不再增长B-tree 搜索开始增长。但Innodb_buffer_pool_reads仍然没有增加。这就有点意思了走 B-tree 查找并不意味着每查一次就得从文件读一次页。B-tree是在描述怎么找不是在规定从哪读先把 AHI 放一边。我们已知条件是id777不是“表空间 5 的第 23 页”。数据库仍要沿索引确定记录的位置。假设有一棵很小的树内部节点告诉我们该沿哪个范围继续向下找想找 id777 ↓ 根页读范围选下一层 ↓ 叶子页在页内定位777 ↓ 取出 v1554这是为了说明查找动作画的小树不是本次hot的实际页号或精确树高。关键在于根页和叶子页本身都可以已经在 Buffer Pool 中。用它们继续做比较、定位并不需要先把它们扔出去再从文件重新读回来。所以这两个问题要拆开问沿索引怎样找到目标记录此刻要访问的那个页内存里有没有把“没有走哈希快捷查找”直接推成“必然访问磁盘”就是把它们合并成了一个问题。原笔记里的按页号hash回答第二个问题沿树走的时候下一步需要哪个页是可以确定的。比如现在要访问某个表空间的第 23 页缓存管理器得找出它对应的内存页控制块。这里才轮到 Buffer Pool 的page_hash。示意一下已知需要哪个页 表空间5页23 ↓ page_hash ↓ 对应的页控制块 ↓ 内存中的数据页表空间和页号只是示意不是说本次 777 号记录就在这个位置。页控制块还涉及状态和同步不能把找到一个表项等同于任何时刻都能立刻读页。在 MySQL 8.0.46 的 buf0buf.h 中buf_pool_t的page_hash注释说明它用(space_id, offset)索引文件页的控制结构这里offset对应页号不是 SQL 里跳过多少行的OFFSET。你给page_hash的问题是“这个页在哪”不是“请根据 id777 给我一行”。它不替代前面根据索引键确定搜索位置的过程。AHI才是对反复索引查找加一条近路现在把 AHI 放回来。反复查询某些索引键时InnoDB 会观察搜索模式在适合的热点索引页上建立自适应哈希信息。这条近路利用的是索引键前缀等信息把查找引向内存中的索引记录位置而不是让你先交出页号。命中且通过相关检查时可以省去一部分常规 B-tree 定位过程不适用或尝试失败时仍然有常规搜索路径。MySQL AHI 说明用刚才那条查询对照常规定位 id777 → 沿B-tree找 → 记录 AHI快捷定位成功时 索引键的hash → 记录位置 两种方式最后都需要访问数据 页是否在内存是另一个问题不要把 AHI 画成“整条 SQL 文本 → 已算好的查询结果”。同一条 SQL 仍然要执行也仍要处理记录访问等工作AHI 不是结果缓存。若想接着看源码可以在同版本 btr0sea.cc 中找btr_search_guess_on_hash。它有适用性检查按搜索前缀等信息计算哈希再尝试取得记录。关停过程btr_search_disable清理的是 AHI 的相关状态不能看见函数名里的 hash 就把它误读为清空整个 Buffer Pool。现在回头看那组数字就顺了关闭 AHI 后热点记录还有常规 B-tree 路径可找所需页也还在缓存里于是搜索计数变了新增读页却仍然为零。为什么EXPLAIN看起来没变实验里两种状态都执行了EXPLAIN SELECT id, v FROM hot WHERE id777;两边都是主键PRIMARY访问类型const。如果只看这两列好像什么都没有发生。但执行计划告诉我们选择了什么访问方式和索引不能直接当作这次引擎内部 AHI 命中的证据。我们实际还看到了 AHI 和 B-tree 计数的变化两者并不矛盾。读指标时也得区分名字adaptive_hash_searches用来观察 AHI 成功搜索的计数。adaptive_hash_searches_btree观察常规 B-tree 搜索计数。Innodb_buffer_pool_read_requests逻辑读请求。Innodb_buffer_pool_reads无法由 Buffer Pool 满足而需要读入的读计数。本次关闭 AHI 后逻辑读请求还在增长新增读入为零。这个“零”只针对这段热查询窗口不能扩写成服务器没有任何文件写入、日志活动或机器完全没有 I/O。它也不是用来证明冷页都能凭空从内存找到。这些指标是实例级的。正式业务里有其他连接和后台活动不能把全局增量全算给自己的一条 SQL我们用独立实例就是为了减少这种混淆。AHI 指标的启用和状态读取方式见 INNODB_METRICS 文档Buffer Pool 读计数见 服务器状态变量。自己复现准备表再跑开关对照只在可丢弃的独立 MySQL 实例上做。innodb_adaptive_hash_index是全局变量会影响其他连接不能在共享开发库或生产库里为了读一篇文章随手切换。需要能设置全局变量和读取指标的实验账号。下面先创建新库如果同名库已存在CREATE DATABASE会报错请检查环境不要直接改成删库重来。本文未提供删除业务数据的命令。SET GLOBAL innodb_monitor_enablemodule_adaptive_hash; CREATE DATABASE blog_ahi; USE blog_ahi; CREATE TABLE hot ( id INT PRIMARY KEY, v INT NOT NULL, pad CHAR(200) NOT NULL ) ENGINEInnoDB; INSERT INTO hot (id, v, pad) WITH digits AS ( SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) SELECT 1a.d10*b.d100*c.d1000*d.d, 2*(1a.d10*b.d100*c.d1000*d.d), REPEAT(x,200) FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d;下面保存为probe_ahi.py。这是完整探针不负责安装或启动 MySQL也不会删库。它默认连接本机 Unix socket无密码 root 只适用于这里的隔离临时实例不是配置生产账号的建议。import json import os import subprocess if os.environ.get(BLOG_AHI_LAB) ! 1: raise SystemExit(Only run against a dedicated disposable MySQL instance; set BLOG_AHI_LAB1) client [os.environ.get(MYSQL_CLIENT, mysql), --no-defaults, --protocolSOCKET, --socket os.environ[MYSQL_SOCKET], -u, os.environ.get(MYSQL_USER, root), --batch, --skip-column-names, --connect-timeout2] def sql(text): result subprocess.run(client, inputtext, capture_outputTrue, textTrue, timeout120) if result.returncode ! 0 or result.stderr.strip(): raise RuntimeError(result.stdout result.stderr) return result.stdout.splitlines() def snapshot(): lines sql(SELECT NAME, COUNT FROM information_schema.innodb_metrics WHERE NAME IN (adaptive_hash_searches,adaptive_hash_searches_btree) ORDER BY NAME; SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME IN (Innodb_buffer_pool_read_requests,Innodb_buffer_pool_reads) ORDER BY VARIABLE_NAME;) return {name.lower(): int(count) for name, count in (line.split(\t) for line in lines)} def phase(name, count): before snapshot() results sql(USE blog_ahi;\n SELECT id, v FROM hot WHERE id777;\n * count) after snapshot() if results ! [777\t1554] * count: raise AssertionError(Query result or count changed) return {phase: name, queries: count, allResultsMatch: True, before: before, after: after, delta: {key: after[key] - before[key] for key in before}} original sql(SELECT global.innodb_adaptive_hash_index;)[0] record {version: sql(SELECT VERSION();)[0], phases: []} try: sql(SET GLOBAL innodb_adaptive_hash_indexON;) record[planOn] sql(USE blog_ahi; EXPLAIN SELECT id,v FROM hot WHERE id777;) record[phases].append(phase(on_training, 4000)) record[phases].append(phase(on_measured, 2000)) sql(SET GLOBAL innodb_adaptive_hash_indexOFF;) record[offValue] sql(SELECT global.innodb_adaptive_hash_index;)[0] record[planOff] sql(USE blog_ahi; EXPLAIN SELECT id,v FROM hot WHERE id777;) record[phases].append(phase(off_measured, 2000)) sql(SET GLOBAL innodb_adaptive_hash_indexON;) record[phases].append(phase(reenabled_training, 4000)) record[phases].append(phase(reenabled_measured, 2000)) record[metricsStatus] sql(SELECT NAME, STATUS FROM information_schema.innodb_metrics WHERE NAME IN (adaptive_hash_searches,adaptive_hash_searches_btree) ORDER BY NAME;) finally: sql(SET GLOBAL innodb_adaptive_hash_index (ON if original 1 else OFF) ;) record[restoredAhi] sql(SELECT global.innodb_adaptive_hash_index;)[0] if record[restoredAhi] ! original: raise AssertionError(AHI setting not restored) print(json.dumps(record, indent2))在 Linux/WSL 中把MYSQL_SOCKET改成实验实例的真实套接字确认没有指向日常数据库再运行BLOG_AHI_LAB1 MYSQL_SOCKET/path/to/lab/mysql.sock \ MYSQL_USERroot python3 probe_ahi.py若mysql不在 PATH可另设MYSQL_CLIENT为客户端程序的绝对路径。代码不在命令行写入密码需要密码的实例应先自行采用安全认证配置改造连接部分不能把此例直接套过去。探针会先读取原 AHI 开关依次执行 ON 预热、ON 测量、OFF 测量、再次 ON 预热和测量最后恢复开关。结果里保留每轮前后累计值和差值也检查每次返回的记录没有变。指标模块在建表步骤开启独立临时实例用完后一起停止本例没有承诺恢复所有监控配置。我们本次用的是不监听 TCP、64 MiB Buffer Pool、64 MiB redo 容量、关闭 binlog 的临时实例。关闭 binlog 是这个实验环境的条件不是 AHI 的必需条件。每轮都新建数据目录测试结束后 MySQL 已停止没有修改日常数据库配置。如果你的开启阶段没有观察到 AHI 增长先看版本、开关值、指标是否 enabled以及预热是否足够。不要因为执行了SET ...ON就在报告里写“已经命中”ON 只是允许使用不保证每一条查询都用上。那我该打开还是关闭这次只证明了一个区分AHI 的搜索近路与数据页是否已经缓存不是一回事。我们没有用单条热点查询的耗时推荐开关也没有测并发下的争用和维护成本。实际要调参应在自己的代表性负载下比较延迟、吞吐和等待不能把“有命中”直接当成“整体一定更快”。这篇来自原 InnoDB 内存笔记中尚未展开的两种 hash。原稿里固定分配80%内存等经验值不是本实验的前提也不应当作所有机器通用的设置。先分清它们各自在解决什么问题后面看配置才不会把一个开关当成整个缓存系统的开关。