SQL 大表索引优化与查询加速实践

大表查询慢，核心问题往往不在sql写法本身，而在索引是否匹配查询模式、数据分布是否合理、以及执行计划是否被真实驱动。优化不是加索引越多越好，而是让每一条索引真正被用上、且用得高效。

明确查询模式，再设计索引

盲目建索引是常见误区。先看实际高频查询的WHERE条件、JOIN字段、ORDER BY和GROUP BY列。例如：

• 查询常带 WHERE status = ? AND created_at > ? ORDER BY updated_at DESC，适合建联合索引 (status, created_at, updated_at)；
• 若同时存在 WHERE user_id = ? AND type = ? 和 WHERE type = ? AND user_id = ?，可统一建 (user_id, type) 或 (type, user_id)，取决于字段选择性——高选择性字段（如user_id）放前面更利于快速过滤；
• 覆盖索引能避免回表：在索引中包含select所需字段，如 INDEX (order_id, status, amount) 支持 SELECT status, amount FROM orders WHERE order_id = ?。

警惕索引失效的典型场景

即使建了索引，也可能因写法或数据特性被优化器忽略：

• 对索引字段使用函数或表达式：WHERE YEAR(created_at) = 2024 → 改为 WHERE created_at >= ‘2024-01-01’ AND created_at ；
• 隐式类型转换：WHERE mobile = 13800138000（mobile是VARCHAR）→ 改为字符串引号包裹，否则可能全表扫描；
• LIKE以通配符开头：WHERE name LIKE ‘%abc’ 无法走索引 → 如业务允许，改用全文索引或倒排结构；
• OR连接多个非索引字段条件，或未将所有OR分支都纳入索引，也可能退化为全表扫描。

分页深翻与大偏移优化

大表 LIMIT 1000000, 20 这类深分页极慢，因为mysql仍需扫描前100万行。实用解法有：

• 游标分页（推荐）：用上一页最后一条记录的排序字段值作为下一页起点，如 WHERE id > 123456 ORDER BY id LIMIT 20；
• 延迟关联：先用索引查主键，再JOIN原表取数据，减少临时表和排序开销，例如：
  SELECT t.* FROM large_table t INNER JOIN (SELECT id FROM large_table WHERE status=1 ORDER BY id LIMIT 1000000,20) AS tmp ON t.id = tmp.id;
• 对超大历史表，考虑按时间归档冷数据，减少在线表体积。

验证与持续观察不可少

优化后必须验证效果，不能只看“用了索引”：

• 用 EXPLAIN format=json 查看实际执行计划，关注 key（是否命中索引）、rows（预估扫描行数）、filtered（过滤率）、Extra（是否using filesort/Using temporary）；
• 开启慢查询日志，设置 long_query_time = 1，结合pt-query-digest分析真实负载热点；
• 监控索引使用率：MySQL 8.0+ 可查 sys.schema_unused_indexes 和 performance_schema.table_io_waits_summary_by_index_usage，及时清理低效索引。