MySQL 5.7与8.0在索引优化器算法上有什么显著改进？-mysql教程-PHP中文网

mysql 8.0 的 cost model 基于统计信息动态估算 i/o 和 cpu 成本选择索引，而非 5.7 的固定规则；若未执行 analyze table 导致统计陈旧，反而引发执行计划劣化。

mysql 5.7与8.0在索引优化器算法上有什么显著改进？

MySQL 8.0 的 Cost Model 怎么影响索引选择

5.7 的优化器基本靠“有索引就走索引”这类固定规则，不评估实际代价；8.0 默认启用 Cost Model，会基于统计信息估算不同索引路径的 I/O 和 CPU 成本，再选总 cost 更低的方案。比如 WHERE a = ? AND b > ?，5.7 可能死守 idx_a，而 8.0 会权衡用 idx_a 还是 idx_b 或联合索引更省。

但这个机制依赖准确的统计信息：不跑 ANALYZE TABLE，Cost Model 就会瞎估——升级后执行计划反而变差，常见于大表未及时更新统计信息的场景。

SELECT @@optimizer_switch LIKE '%cost_model=on%' 检查是否启用
EXPLAIN FORMAT=TREE 在 8.0 中可直接看到各节点预估 cost 值，5.7 不支持
强制关闭仅用于调试：SET optimizer_switch='cost_model=off'

函数索引和降序索引在 8.0 中真能生效吗

5.7 对 CREATE INDEX idx ON t ((UPPER(name))) 直接报 ERROR 1064，因为解析器根本不认函数表达式；8.0.13+ 才真正支持双括号语法，且底层自动映射为不可见虚拟列 + B+ 树索引——但它仍受限于 B+ 树特性，只对 UPPER(name) = 'ABC' 有效，对 UPPER(name) LIKE '%abc%' 无效。

降序索引同理：5.7 允许写 INDEX (a DESC, b ASC)，但 SHOW CREATE TABLE 会悄悄抹掉 DESC，实际仍是升序组织；8.0 是物理级降序存储，ORDER BY a DESC, b ASC 才能免 Using filesort，但前提是 WHERE 条件覆盖最左前缀（如 WHERE a > 100）。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

方向必须严格一致：查询 ORDER BY a DESC, b DESC 而索引是 (a DESC, b ASC)，不命中
函数必须确定性：NOW()、RAND() 等非确定函数不允许建函数索引
空格、括号嵌套层级都要和索引定义完全一致，不是模糊匹配

为什么 8.0 的子查询索引利用更可靠

5.7 中 IN 或 = 后跟子查询，只要引用外层字段（如 WHERE t1.id = (SELECT ref_id FROM t2 WHERE t2.id = t1.id)），就大概率标记为 DEPENDENT SUBQUERY，导致外层每行都重执行一次子查询，I/O 爆炸；8.0 引入基于代价的物化决策（subquery_materialization_cost_based=ON），并支持 /*+ MATERIALIZE */ 提示，让子查询结果一次性计算、后续哈希连接。

但别默认它“自动生效”：标量子查询（如 SELECT (SELECT name FROM users WHERE id = o.user_id)）仍不会物化，必须手动改写；且物化临时表受 sort_buffer_size 和 join_buffer_size 影响更大——缓冲区太小会退化为磁盘临时表，比 5.7 更卡。

盯住 EXPLAIN FORMAT=TREE 输出里有没有 materialize 节点
检查物化后的 rows 估算是否合理，否则提示可能被绕过
升级后若发现慢查询变多，先确认是否因物化策略变化导致计划回退

不可见索引和隐藏索引的实际用途是什么

INVISIBLE 索引不是性能功能，而是灰度验证工具：设为不可见后，优化器彻底不选它，但索引照常维护、占用空间、不影响 ANALYZE TABLE 统计。你可以先 ALTER TABLE t1 ALTER INDEX idx_c1 INVISIBLE，观察慢查询是否复现，再决定删还是留。

5.7 完全不识别 INVISIBLE 语法，加了就报错；8.0 支持建表时指定或运行时修改。但容易忽略的是：不可见索引仍拖慢写入（INSERT/UPDATE 仍要更新它），且 mysqldump 默认导出时带 INVISIBLE 属性——若恢复到 5.7 库，直接失败。