本文共 2008 字，大约阅读时间需要 6 分钟。

MySQL 索引优化实战（二）

在数据库性能优化的实践中，索引是提升查询速度的重要工具。然而，索引的使用并非简单的“索引越多越好”，而是需要根据实际需求谨慎选择和管理。本文将深入探讨MySQL索引的优化规则，并结合实际案例分析。

1. 前缀索引的应用

前缀索引是一种通过使用列的前缀来代替整个列作为索引的技术。当前缀长度合理时，可以在索引大小和查询效率之间找到平衡点。使用 count(distinct left(列名, 索引长度))/count(*) 可以计算前缀索引的区分度。前缀索引的优点是索引文件较小，维护开销低，但其缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。

例如，对于以下 SQL 语句：

SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

我们可以建立索引：(first_name, last_name(4))。

2. 索引列的选择原则

• 单列索引：不允许存 null 值，建议使用 NOT NULL 约束或设置默认值。
• 复合索引：不允许全为 null 的值，确保查询的高效性。

3. 延迟关联与子查询优化

在处理超大数据量的分页场景时，直接使用 LIMIT 会导致性能严重下降。建议通过先快速定位需要关联的记录，再进行子查询来优化效率。例如：

SELECT a.* FROM 表1 a, (SELECT id FROM 表1 WHERE 条件 LIMIT 100000, 20) b WHERE a.id = b.id;

4. 业务唯一性与唯一索引

业务中具有唯一特性的字段，即使是多个字段的组合，也应建立唯一索引。唯一索引不仅能显著提升查询效率，还能防止脏数据的产生。

5. 关联查询的最佳实践

• 单表索引：建议控制在 5 个以内。
• 字段数限制：单个索引字段数不应超过 5 个，超过后索引的实际作用会大打折扣。

6. 索引误区的避免

• 不盲目增索：一个查询不一定需要一个索引，索引文件占用存储空间且会影响更新性能。
• 抵制唯一索引：业务唯一性问题应通过唯一索引解决，而非仅依赖应用逻辑。
• 过早优化：在没有全面了解系统之前，不要盲目进行优化。

问题详解

1. 如何建立以下 SQL 的索引？

WHERE a=1 AND b=1 WHERE b=1 WHERE b=1 ORDER BY time DESC

答：建议建立两个索引：idx_a_b(a, b) 和 idx_b_time(b, time)。MySQL 查询优化器会自动调整 WHERE 子句的条件顺序以利用适合的索引，但建议字段顺序与联合索引保持一致。

2. 联合索引 emp_no、title、from_date 是否可用？

SELECT * FROM employees.titles WHERE emp_no BETWEEN '10001' AND '10010' AND title='Senior Engineer' AND from_date BETWEEN '1986-01-01' AND '1986-12-31'

答：可以使用索引，涵盖全部三个列。注意 emp_no 的范围查询可能会被误解为多值精确匹配，需谨慎处理。

3. 是否需要为所有查询建立索引？

答：不是。索引虽然加快查询速度，但也存在成本。索引文件占用存储空间，且插入、更新操作会加重负担。建议根据实际需求和数据区分度决定是否建立索引。

4. 主键与聚集索引的关系

在 InnoDB 引擎中，主键必然是聚集索引。如果没有定义主键，InnoDB 会使用唯一非空索引作为替代。MyISAM 引擎表没有聚集索引概念。

5. 外键关联优化

对于一个 6亿行的表 a 和 3亿行的表 b，通过外键 tid 关联，如何快速查询 50000 到 50200 行？

答：

• 如果 tid 是连续的，优化方法是直接在 b 表上建立索引，并在 a 表上使用相应的 tid 值进行快速定位。
• 如果 tid 不是连续的，需使用覆盖索引，确保 tid 是主键或辅助索引。

6. 文章表索引建议

SELECT author_id, title, content FROM `article` WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

答：建议建立联合索引 idx_category_id_views(category_id, views)。创建该索引后，EXPLAIN 结果显示可以利用索引加快查询速度。

通过以上优化策略和实践案例，可以显著提升 MySQL 数据库的查询性能。索引的使用需要综合考虑业务需求、数据特性和性能目标，避免盲目增索或过早优化。

转载地址：http://pebfk.baihongyu.com/