MySQL 查询索引优化的常用方法

一、基本原则与索引类型

• 理解索引代价：读快（查找、排序、分组） vs 写慢（插入/更新/删除需维护索引）。
• 常用索引类型：
  • B-Tree 索引（默认，适合等值、范围查询、排序）
  • 哈希索引（Memory 引擎，等值查询）
  • 全文索引（FULLTEXT，文本搜索）
  • 前缀索引（字符串列做部分索引，节省空间）
  • 复合索引（多列联合，注意列顺序）
  • 覆盖索引（索引包含查询所需全部列，避免回表）

二、索引设计要点

• 用最常用的查询字段建索引（WHERE、JOIN、ORDER BY、GROUP BY、DISTINCT）
• 复合索引按查询中使用的列顺序和最左前缀原则设计：
  • 复合索引能被利用前提是查询条件包含最左列或前缀。
• 避免在索引列上做函数或运算（如 DATE(col)、col+1），会导致索引失效。
• 尽量用等值条件放在复合索引左侧，范围条件放右侧。
• 小数据类型优于大数据类型；短字符串索引优于长字符串；适当使用前缀索引。
• 主键最好为窄且稳定的列（自增整型常用）。

三、常见优化技巧

• 覆盖索引（索引列包含所需的所有 SELECT 列）能显著提升性能。
• 使用合适的 ORDER BY / LIMIT 配合索引，避免文件排序（Using filesort）。
• 对 JOIN 操作，给连接字段建立索引，注意驱动表选择与索引选择。
• 控制索引数量：索引过多影响写性能与维护成本，只保留必要索引。
• 使用分区（Partition）处理极大数据表，注意只在合适场景下使用（按范围、列表或哈希分区）。
• 使用稀疏/稠密索引策略：低基数列（如性别）通常不适合单独建索引，可与其他列组成复合索引。
• 避免索引选择器被阻断：NULL 值、隐式类型转换（字符串与数值比对）会影响索引利用。
• 对频繁更新的列慎用索引，评估写入性能影响。
• 定期重建/优化索引（OPTIMIZE TABLE）或重建碎片严重的表（尤其 MyISAM）。

四、SQL 编写注意

• 避免 SELECT *，明确列以便使用覆盖索引。
• 使用 EXISTS 替代 IN（尤其子查询返回大量数据时），或使用 JOIN。
• 对于 OR 条件，评估是否能改写为 UNION 或使用合适的复合索引。
• 对于模糊匹配，前缀匹配（col LIKE 'abc%') 可用索引；'%abc%' 无法用普通 B-Tree 索引（可用 FULLTEXT 或倒排/ElasticSearch）。
• 使用 LIMIT 时配合索引分页（基于索引的范围扫描优于 OFFSET 大值）。

五、诊断步骤与工具

• 使用 EXPLAIN / EXPLAIN ANALYZE 查看查询执行计划：
  • 关注 key（使用的索引）、key_len、rows（估算扫描行数）、Extra（Using index, Using where, Using filesort, Using temporary）。
• 使用 SHOW INDEX FROM table 查看索引定义与选择性（Cardinality）。
• 查看慢查询日志（slow query log）定位耗时 SQL。
• 使用 Performance Schema、INFORMATION_SCHEMA、pt-index-usage（percona-toolkit）等分析索引使用情况。
• 使用 ANALYZE TABLE 更新统计信息，帮助优化器选择更好索引。

六、常见问题与解决方法

• 索引没被使用：检查函数/算术操作、类型不一致、隐式转换、NULL、最左前缀未满足。
• 查询使用文件排序（Using filesort）：为 ORDER BY 列建立合适复合索引或调整查询顺序。
• 大量低选择性索引：合并为复合索引或删除无效索引。
• 回表（Using where 而非 Using index）：尝试扩展索引包含更多所需列实现覆盖索引。
• 过多索引导致写性能差：评估并删除不常用索引。

七、实践示例（简要）

• 复合索引：对 WHERE a=? AND b=? 做索引：CREATE INDEX idx_a_b ON t(a,b);
• 覆盖索引查询：SELECT a,b FROM t WHERE a=? 用 idx_a_b 可成为覆盖索引。
• 避免函数：不要写 WHERE DATE(col)= '2026-01-01'，改写为 WHERE col >= '2026-01-01' AND col < '2026-01-02'。
• 分页优化：SELECT ... WHERE id > last_id ORDER BY id LIMIT 100 比 OFFSET 快。

八、优化流程建议

1. 收集慢查询 -> 2. 用 EXPLAIN 分析 -> 3. 尝试添加/调整索引或改写 SQL -> 4. 复测 EXPLAIN/性能 -> 5. 部署并监控