数据库核心术语知识库

定义

索引提示 (Index Hint) 是 SQL 中的一种语法机制,允许开发人员显式地建议或强制优化器使用特定的索引。当优化器的自动选择不够理想时,可以通过索引提示来干预执行计划。

不同数据库的实现方式:

MySQL: FORCE INDEX, USE INDEX, IGNORE INDEX
SQL Server: WITH (INDEX(...))
PostgreSQL: 不支持直接的索引提示,需要通过其他方式间接控制

详细笔记

核心原理

为什么需要索引提示?

虽然现代数据库优化器非常智能,但在某些场景下仍可能做出次优选择:

统计信息过时:导致成本估算错误
参数嗅探:缓存的计划不适合当前参数
复杂查询:优化器无法准确预测最佳方案
数据分布变化:优化器基于历史统计信息,无法及时适应变化

原则:索引提示应该是最后的手段,优先应该:

更新统计信息
优化索引设计
重写查询
只有在上述方法无效时才使用索引提示

MySQL 索引提示

三种索引提示类型

sql

-- 表结构
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(100),
  INDEX idx_name (name),
  INDEX idx_age (age),
  INDEX idx_email (email)
);

1. USE INDEX:建议使用某个索引(优化器可以拒绝)

sql

SELECT * FROM users USE INDEX(idx_name)
WHERE name = '张三';

-- 含义:
-- "建议优化器优先考虑 idx_name 索引"
-- "但如果优化器认为其他方案更好,可以忽略此建议"

2. FORCE INDEX:强制使用某个索引(除非完全不可用)

sql

SELECT * FROM users FORCE INDEX(idx_name)
WHERE name = '张三';

-- 含义:
-- "必须使用 idx_name 索引,除非它根本无法用于此查询"
-- "即使优化器认为全表扫描更好,也会强制使用索引"

3. IGNORE INDEX:忽略某个索引

sql

SELECT * FROM users IGNORE INDEX(idx_name, idx_age)
WHERE name = '张三' AND age > 25;

-- 含义:
-- "不要使用 idx_name 和 idx_age 索引"
-- "优化器会从剩余索引中选择,或全表扫描"

实际示例对比

sql

-- 场景:100 万行数据,name='张三' 的记录有 50 万条(50%)

-- 方法一:不使用提示
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- 输出:
-- type: ALL (全表扫描)
-- key: NULL
-- 原因:优化器认为 50% 的数据用索引不如全表扫描

-- 方法二:USE INDEX
EXPLAIN SELECT * FROM users USE INDEX(idx_name) WHERE name = '张三';
-- 输出:
-- type: ref
-- key: idx_name
-- 含义:优化器接受了建议,但可能仍然认为成本较高

-- 方法三:FORCE INDEX
EXPLAIN SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';
-- 输出:
-- type: ref
-- key: idx_name
-- rows: 500000
-- 含义:强制使用索引,即使需要回表 50 万次

-- 性能对比:
-- 全表扫描:2 秒
-- FORCE INDEX:30 秒 (更慢!)

结论:这个例子中,强制使用索引反而更慢,说明优化器的原始选择是正确的。

正确的使用场景

sql

-- 场景:统计信息显示 name='张三' 只有 100 条记录(实际也是)
-- 但优化器错误地选择了全表扫描

-- 问题查询
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type: ALL (优化器误判)
-- rows: 1000000

-- 原因:统计信息过时

-- 解决方案一:更新统计信息(推荐)
ANALYZE TABLE users;

-- 重新 EXPLAIN
-- type: ref
-- key: idx_name
-- rows: 100 ✅

-- 解决方案二:如果无法立即更新统计信息,临时使用索引提示
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

SQL Server 索引提示

语法

sql

-- 基本语法
SELECT * FROM users WITH (INDEX(idx_name))
WHERE name = '张三';

-- 多个索引提示
SELECT * FROM users WITH (INDEX(idx_name, idx_age))
WHERE name = '张三' AND age > 25;

-- 强制全表扫描
SELECT * FROM users WITH (INDEX(0))
WHERE name = '张三';
-- INDEX(0):强制全表扫描
-- INDEX(1):强制聚集索引扫描

示例

sql

-- 场景:优化器选择了错误的索引

-- 问题查询
SELECT * FROM orders 
WHERE user_id = 123 AND status = 1;

-- 执行计划显示使用了 idx_status,但 idx_user_id 更好

-- 解决方案:强制使用特定索引
SELECT * FROM orders WITH (INDEX(idx_user_id))
WHERE user_id = 123 AND status = 1;

-- 或使用表提示
SELECT * FROM orders WITH (FORCESEEK)
WHERE user_id = 123 AND status = 1;
-- FORCESEEK:强制使用索引查找,避免扫描

PostgreSQL:无直接索引提示

PostgreSQL 设计理念:不相信用户比优化器更聪明,因此不提供直接的索引提示。

替代方案

方案一:禁用特定索引类型

sql

-- 临时禁用顺序扫描,强制使用索引
SET enable_seqscan = OFF;
SELECT * FROM users WHERE name = '张三';
SET enable_seqscan = ON;  -- 恢复

-- 其他可禁用的扫描方式:
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF;

方案二:修改查询以影响优化器

sql

-- 原查询:优化器选择全表扫描
SELECT * FROM users WHERE name = '张三';

-- 修改:添加 LIMIT,优化器更可能使用索引
SELECT * FROM users WHERE name = '张三' LIMIT 100;

-- 或使用子查询
SELECT * FROM (
  SELECT * FROM users WHERE name = '张三'
) AS tmp;

方案三:使用 pg_hint_plan 扩展

sql

-- 安装扩展
CREATE EXTENSION pg_hint_plan;

-- 使用注释形式的提示
/*+ SeqScan(users) */
SELECT * FROM users WHERE name = '张三';

/*+ IndexScan(users idx_name) */
SELECT * FROM users WHERE name = '张三';

/*+ Set(enable_seqscan off) */
SELECT * FROM users WHERE name = '张三';

索引提示的使用场景

场景一:统计信息过时(临时方案)

sql

-- 问题:大批量导入后,统计信息未更新

-- 长期方案:更新统计信息
ANALYZE TABLE users;

-- 临时方案:使用索引提示
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

场景二:优化器 Bug 或限制

sql

-- 某些复杂查询,优化器可能无法找到最优计划

-- 示例:多表 JOIN + 子查询
SELECT * FROM orders o
FORCE INDEX(idx_user_id)
INNER JOIN users u ON o.user_id = u.id
WHERE u.age > 25;

-- 通过提示确保使用正确的连接顺序和索引

场景三:A/B 测试不同索引

sql

-- 测试两个索引的性能

-- 测试索引一
SELECT SQL_NO_CACHE * FROM users USE INDEX(idx_name) WHERE name = '张三';
-- 执行时间:100ms

-- 测试索引二
SELECT SQL_NO_CACHE * FROM users USE INDEX(idx_name_age) WHERE name = '张三' AND age > 25;
-- 执行时间:50ms

-- 结论:idx_name_age 更好

场景四:应急处理生产问题

sql

-- 生产环境突然出现慢查询
-- 根本原因分析需要时间
-- 先用索引提示快速恢复服务

SELECT * FROM orders FORCE INDEX(idx_create_time)
WHERE create_time > '2024-01-01'
ORDER BY create_time DESC
LIMIT 100;

-- 后续再深入分析并实施永久解决方案

索引提示的风险

风险一:数据分布变化后失效

sql

-- 今天:name='张三' 只有 100 条,FORCE INDEX 很好用
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

-- 明天:业务变化,name='张三' 变成 50 万条
-- 但 SQL 仍然强制使用索引,性能急剧下降

-- 教训:索引提示会"固化"执行计划,失去适应性

风险二:维护成本增加

sql

-- 代码中大量使用索引提示
SELECT * FROM users FORCE INDEX(idx_name) ...;
SELECT * FROM orders FORCE INDEX(idx_user_id) ...;
SELECT * FROM products FORCE INDEX(idx_category) ...;

-- 问题:
-- 1. 索引重命名后,SQL 需要修改
-- 2. 索引删除后,SQL 报错
-- 3. 新人不理解为什么用这些提示
-- 4. 难以判断哪些提示仍然必要

风险三:掩盖真正的问题

sql

-- 真正问题:统计信息过时
-- 临时方案:使用索引提示
-- 结果:一直没更新统计信息,问题被掩盖

-- 正确做法:找出为什么统计信息没自动更新
--     修复根本原因

最佳实践

原则一:优先解决根本问题

问题诊断流程:

1. EXPLAIN 分析执行计划
 ↓
2. 检查统计信息是否过时
 → 如果是:ANALYZE TABLE
 ↓
3. 检查索引设计是否合理
 → 如果不合理:创建/调整索引
 ↓
4. 检查查询是否可以优化
 → 如果可以:重写查询
 ↓
5. 最后考虑:索引提示

原则二:文档化所有索引提示

sql

-- 不好的做法
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

-- 好的做法
-- 2024-01-15:由于统计信息更新延迟(见 ISSUE#123),
-- 临时强制使用 idx_name 索引。
-- 计划在下个版本修复统计信息自动更新逻辑后移除此提示。
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

原则三:定期审查索引提示

sql

-- 创建索引提示清单
-- 文件名:index_hints_audit.md

| 表名 | 索引提示 | 添加日期 | 原因 | 状态 | 计划移除日期 |
|------|---------|---------|------|------|------------|
| users | FORCE INDEX(idx_name) | 2024-01-15 | 统计信息延迟 | 使用中 | 2024-02-01 |
| orders | USE INDEX(idx_user_id) | 2023-12-01 | 优化器选择错误 | 待验证 | 2024-01-31 |

-- 每月审查一次,移除不再需要的提示

原则四:使用注释说明

sql

-- MySQL:使用注释
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * 
FROM users 
FORCE INDEX(idx_name)  -- TODO:移除提示,等待 ISSUE#123 修复
WHERE name = '张三';

-- SQL Server:使用注释
SELECT * FROM users WITH (INDEX(idx_name))  -- 临时方案,见 CONNECT#456
WHERE name = '张三';

原则五:测试不同场景

sql

-- 不要只在一种数据分布下测试

-- 测试一:小结果集
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '稀有名字';
-- 执行时间:10ms ✅

-- 测试二:中等结果集
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '常见名字';
-- 执行时间:500ms ⚠️

-- 测试三:大结果集
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '非常常见的名字';
-- 执行时间:30秒 ❌ (全表扫描只需 2 秒)

-- 结论:索引提示只在小结果集时有效,需要条件判断

监控索引提示的使用

MySQL:查找使用索引提示的查询

sql

-- Performance Schema(MySQL 8.0+)
SELECT 
  DIGEST_TEXT,
  COUNT_STAR,
  AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%FORCE INDEX%'
 OR DIGEST_TEXT LIKE '%USE INDEX%'
 OR DIGEST_TEXT LIKE '%IGNORE INDEX%';

SQL Server:查找使用 Hint 的查询

sql

SELECT 
  qs.execution_count,
  qs.total_worker_time / qs.execution_count AS avg_cpu_time,
  st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%WITH (INDEX%';

实际案例

案例一:电商商品搜索

sql

-- 问题:商品搜索偶尔很慢

-- 原始查询
SELECT * FROM products 
WHERE category_id = 100 
  AND brand_id = 50 
  AND price BETWEEN 100 AND 500
ORDER BY sales DESC
LIMIT 20;

-- EXPLAIN 显示有时使用 idx_category,有时使用 idx_sales
-- 原因:参数嗅探,不同参数的数据分布差异大

-- 临时方案:使用索引提示
SELECT * FROM products FORCE INDEX(idx_category_brand_price)
WHERE category_id = 100 
  AND brand_id = 50 
  AND price BETWEEN 100 AND 500
ORDER BY sales DESC
LIMIT 20;

-- 长期方案:创建合适的复合索引
CREATE INDEX idx_cat_brand_price ON products(category_id, brand_id, price);

-- 并启用统计信息自动更新
SET GLOBAL innodb_stats_auto_recalc = ON;

关联术语

[[执行计划]]
[[统计信息]]
[[参数嗅探]]
[[索引选择性]]

参考资料

MySQL 官方文档: Index Hints
Microsoft Docs: Table Hints
PostgreSQL Wiki: Why PostgreSQL Doesn't Support Index Hints
Use The Index, Luke: Hinting

定义 ​

详细笔记 ​

核心原理 ​

为什么需要索引提示? ​

MySQL 索引提示 ​

三种索引提示类型 ​

实际示例对比 ​

正确的使用场景 ​

SQL Server 索引提示 ​

语法 ​

示例 ​

PostgreSQL:无直接索引提示 ​

替代方案 ​

索引提示的使用场景 ​

场景一:统计信息过时(临时方案) ​

场景二:优化器 Bug 或限制 ​

场景三:A/B 测试不同索引 ​

场景四:应急处理生产问题 ​

索引提示的风险 ​

风险一:数据分布变化后失效 ​

风险二:维护成本增加 ​

风险三:掩盖真正的问题 ​

最佳实践 ​

原则一:优先解决根本问题 ​

原则二:文档化所有索引提示 ​

原则三:定期审查索引提示 ​

原则四:使用注释说明 ​

原则五:测试不同场景 ​

监控索引提示的使用 ​

MySQL:查找使用索引提示的查询 ​

SQL Server:查找使用 Hint 的查询 ​

实际案例 ​

案例一:电商商品搜索 ​

关联术语 ​

参考资料 ​

定义