面试题：在 MySQL 中建索引时需要注意哪些事项？ - 程序百科-程序百科

面试题：在 MySQL 中建索引时需要注意哪些事项？

34天前更新

2014

在 MySQL 中建索引时，需要注意以下关键事项，以确保索引的有效性和性能优化：

一、索引设计的核心原则

1. 明确目标查询

只为高频、关键查询建索引：
索引应服务于 WHERE、JOIN、ORDER BY、GROUP BY 等高频查询条件。
- 示例：对 SELECT * FROM orders WHERE user_id = 100，需在 user_id 上建索引。
避免为低效查询建索引：
例如，对低基数字段（如性别、状态）或全表扫描的查询（如 SELECT * FROM table），索引可能无效。

2. 遵循最左前缀原则

联合索引的顺序决定有效性：
联合索引 (col1, col2, col3) 只能匹配以下查询：
- WHERE col1 = ...
- WHERE col1 = ... AND col2 = ...
- WHERE col1 = ... AND col2 = ... AND col3 = ...

无效场景：

WHERE col2 = ...（跳过最左列 col1）
WHERE col3 = ...（未使用最左列）

将区分度高的列放在联合索引左侧：
例如，(user_id, status) 比 (status, user_id) 更优（假设 user_id 唯一性更高）。

3. 优先使用覆盖索引

让索引包含查询所需的所有字段：
避免回表查询，直接从索引获取数据。
- 示例：SELECT name, age FROM users WHERE city = '北京';创建覆盖索引：CREATE INDEX idx_city_name_age ON users (city, name, age);

4. 控制索引数量，避免冗余

单表索引数量不宜过多：
每个索引会增加写操作的开销（插入、更新、删除时需维护索引）。
删除冗余索引：
例如，已存在 INDEX(a, b)，再建 INDEX(a) 是冗余的（前缀索引已复用）。
- 工具：使用 pt-duplicate-key-checker 检测冗余索引。

二、索引创建的技术细节

1. 优化长文本字段的索引

使用前缀索引：
对 VARCHAR/TEXT 列，仅索引前 N 个字符，减少存储开销。
- 示例：CREATE INDEX idx_desc ON products(description(20));
- 计算合适长度：SELECT COUNT(DISTINCT LEFT(column, 10)) / COUNT(*) AS selectivity FROM table;

2. 为排序和分组优化索引

索引顺序与查询一致：
例如，ORDER BY a DESC, b DESC 需建 INDEX(a DESC, b DESC)。
避免混合升序/降序：
ORDER BY a ASC, b DESC 无法完全利用索引。

3. 避免索引失效的常见操作

不在索引列上做计算或函数：
例如，WHERE YEAR(create_time) = 2023 会导致索引失效。
优化：改写为范围查询：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
避免隐式类型转换：
例如，WHERE age = '25'（age 是 INT 类型）会导致索引失效。
优化：确保查询值与字段类型一致。

4. 数据量小的表无需索引

小表全表扫描更快：
若表仅有几十行数据，索引查找 + 回表的成本可能高于直接全表扫描。

三、索引维护与监控

1. 定期更新统计信息

使用 ANALYZE TABLE：
确保优化器基于最新的统计信息选择索引。ANALYZE TABLE orders;

2. 监控索引使用情况

通过 EXPLAIN 分析执行计划：
- 关注 key（实际使用的索引）、rows（预估扫描行数）、Extra（是否回表）。
检查系统状态变量：SHOW STATUS LIKE 'Handler_read%';
- Handler_read_key：通过索引读取的请求数。
- Handler_read_rnd_next：全表扫描的请求数。

3. 定期清理和优化索引

删除无用索引：
通过慢查询日志和 EXPLAIN 分析，移除未使用的索引。
整理表碎片：OPTIMIZE TABLE orders; -- 会锁表 ALTER TABLE orders ENGINE=InnoDB; -- 在线重建（5.6+）

四、MySQL 8.0 的新特性

隐藏索引：
用于测试索引效果，标记为不可见：ALTER TABLE orders ALTER INDEX idx_test INVISIBLE;
降序索引：
优化 DESC 排序查询：CREATE INDEX idx_time_desc ON logs(create_time DESC);
函数索引：
对表达式建立索引：CREATE INDEX idx_name_lower ON users((LOWER(name)));
JSON 索引：
支持 JSON 文档路径索引：CREATE INDEX idx_profile_location ON users( (CAST(profile->'$.address.city' AS CHAR(20))) );

五、总结：索引设计的核心建议

注意事项	优化策略
索引并非越多越好	控制单表索引数量（通常 ≤5 个）
低基数列避免建索引	如性别、状态字段
联合索引遵循最左前缀原则	调整查询条件或索引顺序
使用覆盖索引减少回表	索引包含查询所需字段
避免索引列上的计算或函数	改写为范围查询
定期维护索引	更新统计信息、清理冗余索引

六、示例：索引失效与优化

失效场景

-- 索引失效：使用函数
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023;
-- type=ALL，key=NULL

优化后

-- 改写为范围查询
EXPLAIN SELECT * FROM orders 
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
-- type=range，key=index_name

七、最终结论

索引设计需要结合业务场景和查询模式，没有一成不变的最优方案。通过合理设计索引、监控使用效果并定期优化，才能实现查询性能与系统开销的平衡。

THE END

MySQL 面试题

喜欢就支持一下吧

相关推荐