优化 where, group by, order by 的联合索引
最新回答
病系少女
2022-08-08 19:49:39
优化 WHERE、GROUP BY、ORDER BY 的联合索引需遵循最左匹配原则，通过合理设计索引列顺序避免 Filesort 和临时表操作，核心是利用索引的有序性提升查询效率。
一、索引设计原则- 最左匹配原则：联合索引的列顺序必须从左到右依次匹配查询条件。例如，若查询条件为 WHERE c1=1 AND c2=2 ORDER BY c3，则索引应设计为 (c1, c2, c3)，确保 WHERE 和 ORDER BY 均能利用索引。
- 列优先级：若 WHERE 和 ORDER BY 使用相同列，优先使用单索引；若列不同，则需将 WHERE 条件列放在联合索引左侧，ORDER BY 列紧随其后。例如，WHERE c1=1 ORDER BY c2 的索引应为 (c1, c2)。
- ORDER BY 利用索引排序：当 ORDER BY 的列是索引的最左前列时，MySQL 会直接使用索引排序（标记为 using index），而非内存或磁盘排序（using filesort）。例如，索引 (c1, c2) 可优化 ORDER BY c1, c2。
- WHERE 与 ORDER BY 组合：WHERE 子句的条件列需满足最左匹配法则（如 WHERE c1=1），且 ORDER BY 列需紧接在 WHERE 列之后。若 WHERE 条件为 c1=1 AND c2=2，则 ORDER BY c3 无法利用索引排序。
若查询仅需索引列（如 SELECT c1, c2 而非 SELECT *），且 ORDER BY 使用索引列，则数据可直接从二级索引获取，无需回表（覆盖索引）。例如，索引 (c1, c2) 可优化 SELECT c1, c2 FROM testc ORDER BY c1, c2，避免回橡慧衫表操作。
四、GROUP BY 优化- 分组与排序的关系：GROUP BY 实质是先排序后分组，若数据已通过索引有序梁腔，则不会触发 Using temporary 或 Using filesort。例如，索引 (c1, c2) 可优化 GROUP BY c1, c2。
- 联合索引包含分组列：联合索引需包含 GROUP BY 的所有列，且顺序需与分组逻辑一致。例如，GROUP BY c1, c2 的索引应为 (c1, c2)。
- 数据量影响：表数据量较小时（如仅 10 条），MySQL 优化器可能忽略索引，直接全表扫描。
- 回表成本碧塌：若 WHERE 条件导致回表数据量过大（如 SELECT * 且无覆盖索引），优化器可能主动放弃索引，转而使用 Filesort 或临时表。
总结：通过设计联合索引 (WHERE列, ORDER BY列, GROUP BY列)，并确保查询条件匹配索引最左前缀，可显著减少 Filesort 和临时表操作，提升查询效率。实际优化时需结合数据分布和查询场景调整索引策略。
热门标签
