核心内容摘要
17c..com：开启数字时代的新篇章，探索无限可能
简介本文档深入剖析数据库优化器Optimizer的工作原理解释全表扫描优于索引的场景并详细列出了强制数据库使用特定索引的语法Index Hints。
核心原理数据库是如何做决策的索引策略是其核心在于数据库的成本优化器 (CBO, Cost-Based Optimizer)。
1 决策逻辑 (Cost Calculation)数据库在执行 SQL 前会计算各种执行方案的“成本”Cost并选择成本最低的方案。
成本主要由I/O 成本读硬盘和CPU 成本处理数据组成。
步骤 1分析 SQL 语句WHERE 条件、JOIN 顺序。
步骤 2查看统计信息 (Statistics)如表有多少行、某个字段有多少个不同的值、索引的深度。
步骤 3预估走索引的成本 vs 全表扫描的成本。
步骤 4选择最优解。
⚠️ 关键点优化器并不总是对的。
如果“统计信息”过时比如你刚插入1万条数据数据库还没来得及更新统计优化器就会误判导致选错索引。
为什么有时候它会选错为什么要人工干预既然它是经过计算的为什么还会选错比如选了路径 B 而不是 A通常有以下几个“罪魁祸首”统计信息过期Stale Statistics数据库并不是每次查询都去实时统计全表数据。
它是依赖后台定期更新的“统计信息表”。
场景昨天表里只有 100 行数据今天突然插入了 100 万行。
统计信息还没更新优化器以为表很小坚持全表扫描结果卡死。
数据分布倾斜Data Skew场景假设status字段99% 是 Success1% 是 Failed。
如果你查Failed走索引很快如果你查Success走索引会导致大量回表不如全表扫描。
如果优化器没预判对具体的参数分布就容易选错。
索引干扰当一个表上有(a),(b),(a,b)多个索引时优化器可能会陷入选择困难错误地认为单列索引(a)比联合索引(a,b)成本更低。
优化提示 (Index Hints) 语法字典当数据库选错了索引比如有idx_a和idx_b它选了idx_b但实际上idx_a更快或者你想测试性能时可以使用Index Hints强制干预。
适用范围MySQL (InnoDB/MyISAM)。
位置通常放在表名之后。
1USE INDEX(建议)作用建议数据库使用指定的索引之一。
数据库可以忽略你的建议如果它觉得全表扫描更快。
语法SELECT ... FROM table_name USE INDEX (index_name) WHERE ...;案例-- 建议数据库考虑使用 idx_user_id 这个索引 SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id 100;
2IGNORE INDEX(屏蔽)作用告诉数据库禁止使用指定的索引。
常用于某个索引失效导致性能变差或者你想测试全表扫描的性能时。
语法SELECT ... FROM table_name IGNORE INDEX (index_name) WHERE ...;案例-- 强行忽略 idx_create_time即使它存在数据库也不会用它 SELECT * FROM orders IGNORE INDEX (idx_create_time) WHERE create_time
;
3FORCE INDEX(强制)作用强制数据库进行全表扫描的成本计算 vs 该索引的成本计算。
这比USE INDEX语气更重。
除非走该索引根本查不到数据否则数据库必须使用该索引即使全表扫描理论上更快。
语法SELECT ... FROM table_name FORCE INDEX (index_name) WHERE ...;案例-- 哪怕效率低也必须走 idx_user_id 索引常用于修复优化器发疯的情况 SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id 10;
复杂场景下的决策表当面对“多索引竞争”或“选错索引”时请参考下表决定使用哪种策略现象/场景原因分析解决方案/优化策略对应语法有索引但走全表扫描查询范围太大超过30%数据或表太小。
属于正常现象无需优化。
如果确信走索引更快可用强制。
FORCE INDEX选了错误的索引例如WHERE a1 AND b2有单列索引 A 和 B优化器选了 B 但 A 区分度更高。
提示使用 A或忽略 B。
USE INDEX(A)或IGNORE INDEX(B)统计信息过期数据大量增删改导致优化器依据旧数据做决策。
首选方案重新分析表更新统计信息。
SQL:ANALYZE TABLE table_name;索引互相干扰多个索引功能重复如idx_a和idx_a_b。
删除冗余索引。
DROP INDEX
MySQL
0 新特性 (Optimizer Hints)除了上述写在表名后的 HintMySQL
0 引入了更高级的注释风格 Hint写在SELECT后面控制力更强不仅限于索引。
语法风格/* HINT_NAME(param) */常用案例--
强制走索引 (等同于 FORCE INDEX) SELECT /* INDEX(orders idx_user_id) */ * FROM orders WHERE user_id 1; --
设置最大执行时间 (毫秒) - 防止慢 SQL 拖垮库 SELECT /* MAX_EXECUTION_TIME(
*/ * FROM orders WHERE ...; --
强制使用临时表排序 (不常用高级调优) SELECT /* BKA(t
*/ * FROM t1 JOIN t2 ON ...;
最佳实践与
注意事项虽然“索引提示”很强大但它是一把双刃剑。
风险如果你的代码里写死了FORCE INDEX (idx_a)下个月业务变更idx_a被删除了或者变得不再适用你的 SQL 代码就会报错或者性能极差因为代码与数据库架构产生了强耦合。
正确的优化决策流程先看 EXPLAIN确认真的选错了索引。
第一步更新统计信息推荐在 MySQL 中执行ANALYZE TABLE table_name;。
很多时候重新计算统计信息后优化器自己就变聪明了不需要改 SQL。
第二步优化 SQL 写法是否可以通过改写 SQL如用JOIN代替子查询或调整WHERE条件顺序虽通常无关但有时影响复杂逻辑来引导优化器。
第三步删除干扰索引如果某个索引长期误导优化器且本身用处不大直接删除它是最好的架构优化。
第四步最后手段使用 Index Hints如果在无法改动表结构且上述方法无效时再使用FORCE INDEX。