全网最全mysql查询优化大全

MySQL 查询优化方法大全

适用于日常业务系统中常见的 MySQL 查询优化场景，可直接作为学习笔记或团队内部分享文档。

---

1. 优化的总体思路

在谈具体技巧之前，先记住一个整体思路：先找到慢 SQL，再对症下药。不要一上来就乱改配置。

常见优化目标：

• 降低响应时间（RT）：让一次查询尽量快。
• 减少扫描行数（Rows Examined）：让 MySQL 少干无用功。
• 降低资源消耗：CPU、IO、内存占用更少。
• 提高系统吞吐量（QPS/TPS）：同一时间能扛更多请求。

排查基本步骤：

1. 找出慢 SQL（慢查询日志 / 监控平台）。
2. 使用 EXPLAIN 查看执行计划。
3. 判断是否走索引、走了哪个索引、扫描多少行、是否临时表 / filesort。
4. 从索引、SQL 写法、表结构和数据分布三个角度优化。
5. 再次 EXPLAIN 和压测验证效果。

---

2. 找到需要优化的查询

2.1 开启慢查询日志

在 MySQL 配置文件 my.cnf（或 my.ini）中：

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1        # 超过 1 秒的认为是慢 SQL
log_queries_not_using_indexes = ON  # 可选：记录未使用索引的 SQL

常用查看方式：

• 直接 cat /var/log/mysql/slow.log 分析。
• 使用 mysqldumpslow 或 pt-query-digest 对慢日志进行聚合分析，找出最“费劲”的那几条 SQL。

2.2 使用 EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM order_info WHERE user_id = 10001 AND status = 1;

重点关注字段：

• type：访问类型（从好到坏大致：system > const > eq_ref > ref > range > index > ALL）。
  • ALL 表示全表扫描，通常需要优化。
• key：实际使用的索引。
• rows：预估扫描行数。
• Extra：Using index、Using where、Using temporary、Using filesort 等。
  • Using temporary / Using filesort 一般说明有排序 / 分组需要进一步关注。

---

3. 索引层面的优化

绝大多数查询问题，都可以用一句话总结：索引没建好，或者索引用得不对。

3.1 正确设计索引

1. 为高选择性列建索引

   • 选择性 = 去重后行数 / 总行数，越接近 1 越好。
   • 例如：status 只有 0/1 两种值，单独给它建索引意义不大（大部分情况下）。

2. WHERE / JOIN / ORDER BY / GROUP BY 中频繁使用的列应考虑建索引

   • 对只出现在 SELECT 列表中的字段，一般不单独建索引。

3. 联合索引优于多个单列索引

   • 例如经常有 WHERE user_id = ? AND status = ?，优先建：

     INDEX idx_user_status (user_id, status)
     

   • 不建议分别建 idx_user(user_id) 和 idx_status(status)，合并能力有限。

3.2 最左前缀原则

对于联合索引 (a, b, c)，能用到索引的场景：

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?
• WHERE a = ? AND b > ?
• WHERE a IN (...) AND b = ?

但不能充分利用或索引失效的情况：

• WHERE b = ?（跳过了 a）
• WHERE c = ?（跳过 a、b）

3.3 覆盖索引（Covering Index）

如果查询的列都包含在索引中，则可以只通过索引就拿到结果，无需回表：

-- 索引
INDEX idx_user_status_create (user_id, status, create_time)

-- 查询
SELECT user_id, status, create_time
FROM order_info
WHERE user_id = ? AND status = ?;

Extra 中显示 Using index 时，说明是典型的覆盖索引场景，性能会非常好。

3.4 索引失效的常见写法

1. 条件列上使用函数或表达式：

WHERE DATE(create_time) = '2023-01-01'      -- 索引失效
WHERE create_time >= '2023-01-01' 
  AND create_time <  '2023-01-02'           -- 推荐

2. 列上做计算：

WHERE amount * 100 > 1000     -- 不推荐
WHERE amount > 10             -- 推荐

3. 隐式类型转换：

WHERE mobile = 13800138000    -- mobile 是 varchar，这样会发生隐式转换
WHERE mobile = '13800138000'  -- 推荐：类型一致

4. 模糊查询前缀是 %：

WHERE name LIKE '%abc'        -- 索引无法利用
WHERE name LIKE 'abc%'        -- 可以用到前缀索引

5. 不等于 / 范围太大：

WHERE status != 1
WHERE score > 0               -- 几乎全表命中，意义有限

---

4. SQL 写法与执行逻辑优化

4.1 避免 SELECT *

坏处：

• 返回了很多不需要的列，导致网络传输和 IO 变大。
• 使得覆盖索引难以生效。

推荐只查需要的列：

SELECT id, user_id, create_time 
FROM order_info 
WHERE user_id = ? AND status = 1;

4.2 合理使用 LIMIT，控制返回行数

接口只需要一页数据，就不要查全表：

SELECT ...
FROM order_info
WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 20;

4.3 分页查询的优化

经典写法：

SELECT ...
FROM order_info
WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 100000, 20;   -- 深分页，性能非常差

深分页问题：MySQL 实际会扫描前面 100000+20 行再丢弃前 100000 行。

常见优化方法：

1. 基于上次的游标分页（推荐）

   -- 第一页
   SELECT id, user_id, create_time
   FROM order_info
   WHERE user_id = ?
   ORDER BY create_time DESC
   LIMIT 20;
   
   -- 后续页（带上上一次最后一条记录的 create_time 和 id）
   SELECT id, user_id, create_time
   FROM order_info
   WHERE user_id = ?
     AND (create_time < ? 
          OR (create_time = ? AND id < ?))
   ORDER BY create_time DESC
   LIMIT 20;
   

2. 先用覆盖索引查出主键，再回表

   SELECT id
   FROM order_info
   WHERE user_id = ?
   ORDER BY create_time DESC
   LIMIT 100000, 20;
   
   SELECT *
   FROM order_info
   WHERE id IN (...);   -- 二次查询
   

4.4 合理使用 JOIN

1. 给 JOIN 关联列建索引，并保证两边字段类型一致。
2. 私有技巧：小表驱动大表 – 让小结果集去关联大表。
3. 避免在大表上做 LEFT JOIN 的复杂子查询，可以考虑拆成多次查询或中间表。

示例：

SELECT u.id, u.name, o.amount
FROM user u
JOIN order_info o ON u.id = o.user_id
WHERE u.status = 1
  AND o.status = 1;

• 建议在 user(id, status) 和 order_info(user_id, status) 上建立索引。

4.5 减少不必要的排序和临时表

• 尽量让 ORDER BY 使用索引顺序完成，而不是额外的 filesort。
• 对 GROUP BY / DISTINCT 而言，先用索引过滤，再分组，比扫全表再分组强很多。

---

5. 表结构与数据设计优化

5.1 选择合适的数据类型

1. 整型：TINYINT、INT、BIGINT 尽量用最小可覆盖范围的类型。
2. 字符串：
   • 固定长度用 CHAR，变长用 VARCHAR。
   • 长文档用 TEXT / LONGTEXT，不建议经常参与查询条件。
3. 金额、精度要求高的数据用 DECIMAL。

原则：更小的类型意味着更少的存储、更好的缓存命中率和更快的比较。

5.2 大表拆分

当单表数据量过大（例如 > 1000 万行），常见问题：

• 查询变慢，索引变大，内存难以缓存全部索引。
• 备份、迁移、DDL 操作都变得非常痛苦。

拆分思路：

1. 冷热数据分离
   • 例如：最近 3 个月的数据放在主表，历史归档表单独存放。
2. 垂直拆分（按字段拆）
   • 大字段（如 remark, content）拆到附表，主表只保留高频查询字段。
3. 水平拆分（按行拆）
   • 按用户 ID / 业务 ID 做哈希分表或按时间分表。

5.3 适度冗余，减少复杂 Join

对于读多写少、又经常需要多表 Join 的场景，可以把必要的冗余字段写到主表，减少查询时的 Join 次数。

---

6. 事务与锁对性能的影响

6.1 减少长事务

• 长事务会导致：
  • undo 日志膨胀
  • 老版本数据长期无法清理，影响 MVCC
  • 持有锁时间过长，阻塞其它语句

建议：

• 事务中只做必要操作，不要在事务里做复杂计算 / 远程调用。
• 尽快 COMMIT 或 ROLLBACK。

6.2 合理使用索引避免锁全表

-- 如果没有索引，会锁住大量行，甚至全表
UPDATE order_info 
SET status = 2 
WHERE user_id = 10001 AND status = 1;

给 user_id, status 建立联合索引，可以把锁范围控制到最小。

6.3 固定访问顺序减少死锁

• 多个事务访问多个表时，保持统一的访问顺序（例如先 A 表再 B 表），可以显著减少死锁发生概率。

---

7. 配置层面的常见优化（简要）

配置调整通常是“锦上添花”，不是解决问题的起点。先优化 SQL 和索引再说。

部分关键参数（以 InnoDB 为例）：

• innodb_buffer_pool_size

  • InnoDB 的核心缓存区，负责缓存数据页和索引页。
  • 一般可设置为：物理内存的 50%~70%（需要视机器上还有多少其它服务而定）。

• innodb_log_file_size / innodb_log_files_in_group

  • 影响写入性能和崩溃恢复时间。

• max_connections

  • 太小会出现“Too many connections”；太大会导致机器被打爆。
  • 搭配连接池一起综合考虑。

• innodb_flush_log_at_trx_commit

  • 1 最安全但写入 IOPS 高；
  • 2 / 0 可以得到更高写入性能，但有少量数据丢失风险。

配置优化比较偏 DBA 范畴，日常开发同学更多要关注的是：SQL 写法 + 索引 + 表结构。

---

8. 使用缓存与架构层优化

8.1 应用层缓存（本地缓存 / Redis）

• 对于热点数据、读取远多于写入的数据，可以放到缓存中：
  • 缓解数据库压力；
  • 降低访问延迟。

注意：

• 需要设计好 缓存更新策略：旁路缓存、读写穿透、消息通知等。
• 要注意缓存击穿、缓存雪崩等问题：
  • 热点 key 设置互斥锁；
  • key 过期时间加随机；
  • 重要数据可以使用永不过期 + 主动失效。

8.2 读写分离

• 通过主从复制，让写走主库，读走从库。
• 对读高写低的业务非常有用。

涉及的问题：

• 主从延迟（读到旧数据）。
• 需要根据业务一致性等级，决定哪些查询必须走主库。

8.3 分库分表

当单库 QPS / 单表数据量都太大时，需要考虑：

• 按业务垂直拆库（订单库、用户库、营销库等）。
• 按用户或业务 ID 进行水平分库分表。

---

9. 一个完整的优化示例

原始 SQL：

SELECT *
FROM order_info
WHERE status = 1
ORDER BY create_time DESC
LIMIT 20;

问题：

1. 无 WHERE 高选择性条件，status = 1 命中行过多，扫描行数大。
2. SELECT * 返回不必要的字段。
3. 索引设计不合理时，ORDER BY create_time 可能触发 filesort。

优化方案：

1. 分析业务是否可以增加过滤条件，例如按用户、按时间区间：

   SELECT id, user_id, amount, create_time
   FROM order_info
   WHERE user_id = ?
     AND status = 1
     AND create_time >= '2023-01-01'
   ORDER BY create_time DESC
   LIMIT 20;
   

2. 为此查询建立合适索引：

   INDEX idx_user_status_create (user_id, status, create_time)
   

3. 再次 EXPLAIN，理想情况：

   • type 为 range 或 ref / const。
   • rows 数量明显下降。
   • Extra 中无 Using filesort，有 Using index（覆盖索引）更佳。

---

10. 实战中的检查清单（Checklist）

在优化 MySQL 查询时，可以按照这份小清单逐条检查：

1. ✅ 是否通过慢查询日志找到了真正的瓶颈 SQL？
2. ✅ 是否使用 EXPLAIN 分析执行计划，并看懂 type / rows / Extra？
3. ✅ 是否为核心查询设计了合适的单列/联合索引？
4. ✅ 是否避免了索引失效的写法（函数、隐式转换、前缀 % 等）？
5. ✅ 是否去掉了 SELECT *，只查业务需要的列？
6. ✅ 分页是否存在深分页问题？如果有，是否做了游标分页等优化？
7. ✅ 大表是否考虑了冷热分离 / 分库分表 / 大字段拆分？
8. ✅ 是否检查了事务范围，避免长时间持有锁？
9. ✅ 对于热点数据，是否有做缓存、读写分离等架构级优化？
10. ✅ 修改后是否经过压测或线上监控，确认性能确实变好？

---

结语

MySQL 查询优化没有一招鲜，更多是一套系统化的思维方式：

• 先 定位慢 SQL；
• 再从 索引、SQL 写法、表结构、数据分布 几个维度逐一排查；
• 如果单机极限到了，再考虑 缓存、读写分离、分库分表。