MySQL 索引 + AI 索引建议

楠风测开
0 阅读12分钟

楠风测开 · 测试工程师复习日记 Day16

从一个候选索引，到一份可以复核的变更记录

前言

朋友们好，我是楠风。

今天不以索引类型大全为起点，直接处理一个工程决策：订单列表需要按用户、状态和时间查询，AI给出一个联合索引，怎样把这个建议推进到可评审的变更。

我希望变更记录能回答三个问题：**它解决哪类访问？用什么证据证明收益？对其他读写有什么影响？**只贴一段DDL和一张耗时截图，很难让后来接手的人继续判断。

示例采用MySQL 8.4 LTS / InnoDB。**给出可执行实验，当前未提供本机实测性能数据。**完整初始化SQL放在文末，所有性能记录留待运行后填写，数据条数等确定性预期则按生成规则推导。

一、先定义变更边界

实验表nanfeng_mysql_lab.nf_order_bench只有五列：id、user_id、status、total_amount、created_at。初始只有主键，不改Day15的业务四表。

目标查询固定如下：

SELECT id, user_id, status, created_at
FROM nf_order_bench
WHERE user_id = 42
  AND status = 'PAID'
  AND created_at >= '2026-10-01'
  AND created_at < '2026-11-01'
ORDER BY created_at, id
LIMIT 20;

评审中先把“目标”和“暂不解决的问题”写清楚。本轮只改变一个索引；不改字段类型、不重写SQL、不调实例参数，也不把不同用户的结果混在同一组统计里。完整负载与并发验收将在Day18展开。

结果比较包含字段值和顺序。id作为第二个排序项，保证时间相同时仍有确定顺序。否则执行计划改变之后，前20条出现差异，可能首先是排序约定不完整，而不是索引损坏了数据。LIMIT排序边界

这份边界定义看起来简单，却能阻止很多无效比较。例如把SELECT里的金额删掉、把一个月缩成一天，再展示性能改善，这已经不再是同一任务。

二、提出候选时，要解释每一列的职责

先评估一个候选：

CREATE INDEX idx_user_status_created
ON nf_order_bench(user_id, status, created_at);

user_id和status对应等值过滤，created_at对应时间范围。两个等值条件固定以后，这个排列也值得检查是否能服务时间排序。列的职责不同，不宜只按“区分度高低”给所有SQL套同一种排列。

把用户放在前面还有一个需要验证的理由：项目是否确实存在大量只按用户筛选的查询。如果没有这个负载，理由就不成立。索引设计可以提出假设，但假设应写出来，让它有机会被后续数据否定。

联合索引的左侧连续前缀可以用于查找；WHERE里先写状态还是先写用户，不会更改索引列的组织顺序。联合索引规则

对固定查询，这三个索引列加上InnoDB二级索引记录中的主键，已经能够提供所需字段。id虽然没显式写入DDL，却不能当作缺失字段处理。索引扩展与主键

这也给AI评审提供了一条很具体的检查：如果它要求“为覆盖查询必须再把id加到末尾”，先让它解释InnoDB的存储条件。反之，也不要把这一结论推广到所有存储引擎、所有表结构。

三、把“能用”与“值得用”分开记录

候选在逻辑上适配，不代表优化器一定选择它；被选择，也不代表对所有参数都合算。

我会用下面的表组织证据：

层次要回答的问题证据
正确性内容与顺序有变化吗前后完整结果
计划实际访问了哪个索引、怎样排序EXPLAIN
执行实际处理情况符合估算吗EXPLAIN ANALYZE
测量重复运行是否有稳定变化同条件的耗时记录
代价写入和其他查询是否受影响独立的对照场景

对同一条固定SQL，先在只有主键时记录，再建候选索引重复记录。

-- 每次都在后面接上完整的固定SELECT。
EXPLAIN FORMAT=TRADITIONAL
SELECT id, user_id, status, created_at
FROM nf_order_bench
WHERE user_id = 42 AND status = 'PAID'
  AND created_at >= '2026-10-01'
  AND created_at < '2026-11-01'
ORDER BY created_at, id LIMIT 20;

普通计划中的rows是估算，possible_keys是可能用于查找的候选，key才是实际选用情况。type=index可能是在扫描索引，并不自动等于少量定位。执行计划字段

把同一SQL前缀换成EXPLAIN ANALYZE FORMAT=TREE，可以继续查看真实运行信息。它会执行SELECT，带有测量开销；节点时间还包含子节点关系和循环因素，不能把所有数字直接相加当接口耗时。EXPLAIN ANALYZE

还有一个常见误读：Using filesort表示额外排序过程，不等于必然产生磁盘排序。索引是否消除了排序，需要结合计划观察；即使消除了排序，也要继续检查读取工作和整体耗时。ORDER BY优化

四、比较协议比漂亮数字更重要

把运行条件写进记录，比事后解释更省事。建议至少固定数据库版本、表结构、数据批次、客户端、查询参数、后台负载和结果获取方式。

先预热，再进行相同次数的重复查询，保存全部样本。首次运行单独记录；索引创建时间单独记录；同一轮不要又刷新统计信息、又改客户端连接设置。重复次数需要结合波动决定，不能因为运行了几次就宣称已经覆盖生产环境。

对于本文生成数据，100000行分布在1000个用户，用户42有50条满足状态与月份条件的记录，最终返回20条。这里的50来自生成规则，并不预先规定MySQL会读取50行。优化器的路径、提前停止与过滤行为，需要由计划和执行信息说明。

这组数据也有明显限制：用户数据量均匀，金额与用户存在生成规则带来的关联。它很适合检查脚本、字段与比较流程，却不适合声称“已经验证热点用户”。后续需要单独准备偏斜分布，不能给同一组均匀数据换一个“高并发真实场景”的标题。

如果索引前后结果一致、计划改变，但耗时变化小于运行波动，本轮可以记为“路径变化已确认，性能收益尚不能确认”。这是有效结论，不需要为了给实验收尾而选择最有利的样本。

结果文件与计划文件也要记录同一批次标识，避免把旧数据的结果和新结构的计划拼在一起。对照过程中停止无关写入；业务数据持续变化时，应另行设计一致的数据快照，不能把数据变化误判成查询不等价。

五、不能只验收一个用户的一次查询

索引上线后的服务对象不是一条截图。继续用同一张表补几个查询变体，逐项改变条件即可：

变体关注点
固定用户与状态，缩短时间窗口范围变化是否影响访问与排序
用户存在，但结果为空空结果路径是否符合预期
只保留用户与时间条件状态列不固定时，时间访问与排序怎样变化
额外返回金额覆盖条件变化后是否仍值得采用候选

最后两个变体特别有价值：候选索引中存在某个字段，不代表这个字段总能同样有效地缩小查找范围；当前查询能够覆盖，也不意味着添加任何返回列之后仍然覆盖。

缺少最左字段时，也不要把“不满足常规前缀查找”写成“绝对不可能访问这个索引”。覆盖扫描及满足限制的Skip Scan等路径需要具体区分。工程上不必依赖特殊路径救场，但评审结论要能解释实际计划。Skip Scan限制

六、写入代价需要进入同一份评审

状态更新会修改候选索引列；插入和删除也会维护相应索引。新增索引占用存储，宽度和数量增加还会带来维护代价。索引成本

在实验表上分别准备插入、状态更新和金额更新的对照，保持数据量、事务大小、提交方式一致。金额当前不在候选索引中，若为了另一个查询将金额加入索引，金额更新就需要重新评估。

“加上所有返回列，保证任何列表都覆盖”的方案很容易失控。业务增加一个字段，索引就再变宽；而某些返回字段的变化频率可能远高于查询频率。应该先确认具体查询的重要性，再判断减少读取所获收益是否值得交换维护成本。

这里不预设读写比例，也不编造阈值。评审表里可以保留“产品要求”和“当前测量”两列，让业务时延要求、写入量及资源预算共同参与决定。缺少关键写入数据时，结论应该保持为候选，而不是已完成验收。

七、给AI一份输入合同，再审查它的结论

AI适合整理候选和补充遗漏，但需要得到真实输入。下面是提问模板，不代表已经调用模型取得结果：

环境：MySQL 8.4 / InnoDB。
输入：SHOW CREATE TABLE、SHOW INDEX、固定SQL、
代表性参数、数据分布、EXPLAIN和实际执行记录。

请提出一个优先验证的索引，并用以下格式回答：
依据：字段分别用于等值、范围、排序还是覆盖；
假设：哪些工作负载与分布尚未确认；
验证：结果一致性、执行计划、重复耗时、写入代价；
风险：冗余、宽度、维护成本和其他查询影响；
恢复：如何显式撤销本次实验索引。
不编造性能结果，不默认FORCE INDEX，不把DDL当可回滚操作。

如果建议与预期不同，先检查它引用的输入。是在讨论另一条查询，还是漏了已有索引？是在说估算下降，还是声称实际耗时下降？把这些层次分开，比直接让AI“再想想”更容易定位错误。

最终可以用五句话完成变更记录：解决什么查询；试了哪个候选；结果与路径怎样变化；读写代价是什么；哪些场景仍未验证。即便暂时不保留这个索引，这份记录也能避免下一次重复猜测。

八、可复制的初始化与恢复

以下只用于首次创建自建实验表。已有同名表时先检查，避免重复插入；不要清空业务表来配合教程。

CREATE DATABASE IF NOT EXISTS nanfeng_mysql_lab;
USE nanfeng_mysql_lab;
CREATE TABLE nf_order_bench (
  id BIGINT NOT NULL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(16) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

INSERT INTO nf_order_bench
  (id,user_id,status,total_amount,created_at)
WITH digits AS (
  SELECT 0 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
), nums AS (
  SELECT a.d+10*b.d+100*c.d+1000*d.d+10000*e.d n
  FROM digits a CROSS JOIN digits b CROSS JOIN digits c
  CROSS JOIN digits d CROSS JOIN digits e
)
SELECT n+1, MOD(n,1000)+1,
  CASE MOD(FLOOR(n/1000),4)
    WHEN 0 THEN 'CREATED' WHEN 1 THEN 'PAID'
    WHEN 2 THEN 'PAID' ELSE 'CANCELLED' END,
  CAST(99.90+MOD(n,100) AS DECIMAL(10,2)),
  TIMESTAMP('2026-10-01 00:00:00')
    + INTERVAL MOD(n,30) DAY
    + INTERVAL MOD(n,86400) SECOND
FROM nums;

SELECT COUNT(*) FROM nf_order_bench;
SHOW INDEX FROM nf_order_bench;

五个数字集合生成0—99999，首次成功初始化应为10万行。这里使用MySQL支持的INSERT ... WITH ... SELECT位置。CTE写法

记录基线后，执行前面的CREATE INDEX，再重复查询与计划。需要恢复时，只删除这张实验表上本次创建的候选索引：

ALTER TABLE nanfeng_mysql_lab.nf_order_bench
DROP INDEX idx_user_status_created;

建索引属于会触发隐式提交的DDL操作，不能靠ROLLBACK恢复。数据事务先结束，索引实验再独立进行。DDL隐式提交

验收清单

  • 变更只针对一个明确查询目标，输入数据和参数可复现。
  • 结果、顺序、计划与实际执行信息分别核对。
  • 多次运行保留原始记录，不以最小耗时代表整体。
  • 至少检查返回金额、少一个条件等相关变体。
  • 写入代价、其他负载与未验证范围进入评审结论。
  • AI建议有依据、有验证动作，恢复操作明确。

**讨论题：**一份索引变更评审里，最容易缺失的是查询样本、写入数据，还是“哪些情况没有验证”的说明？

下一篇：Day17 · MySQL 慢查询 + AI 智能分析。从慢日志、执行计划与等待证据，把“查询慢”拆成可以逐项核对的问题。

关注「楠风测开」，继续一起把自动生成的建议变成可维护的工程结论。

楠风测开 · 测试工程师复习日记

avatar
文章
阅读
粉丝