小菜鸟

java菜鸟号正在起航

0%

SQL优化

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 3.8k 阅读时长 ≈ 3 分钟

SQL 优化全指南：从定位瓶颈到落地实践

SQL 优化是提升数据库性能的核心手段，但优化并非盲目调整，而是一套 “观察 - 分析 - 优化 - 验证” 的系统性流程。本文结合 MySQL 底层原理和实战工具，详细讲解 SQL 优化的步骤、原则及具体方法。

SQL 优化的核心步骤

优化的前提是 “找到问题”，而非盲目改写 SQL。完整的优化流程分为以下 6 步：

定位性能瓶颈：判断是 IO 还是 CPU 瓶颈

首先需明确 SQL 执行缓慢的根源 —— 是磁盘 IO 过多（如全表扫描），还是 CPU 计算密集（如复杂排序、多表连接）。

工具：performance_schema（替代废弃的 show profile）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 1. 开启事件收集（默认开启）
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- 2. 执行目标 SQL 后，查询其执行细节
SELECT 
  event_name AS 阶段,
  TIMER_WAIT / 1000000000 AS 耗时_ms  -- 转换为毫秒
FROM performance_schema.events_stages_history_long 
WHERE NESTING_EVENT_ID = (
  SELECT EVENT_ID 
  FROM performance_schema.events_statements_history_long 
  WHERE SQL_TEXT LIKE '%目标SQL%' 
  ORDER BY EVENT_ID DESC LIMIT 1
)
ORDER BY TIMER_START;
瓶颈判断：
  • IO 瓶颈：阶段包含 table scan（全表扫描）、reading data（大量磁盘读），且耗时占比高。
  • CPU 瓶颈：阶段包含 sorting result（排序）、joining tables（连接），且耗时占比高。

明确优化目标：设定可量化指标

优化需有具体目标，例如：

  • 执行时间从 5 秒降至 1 秒以内。
  • 扫描行数从 100 万行降至 1 万行以内。
  • 避免全表扫描，确保使用索引。

捕获慢查询：锁定优化对象

通过慢查询日志记录执行时间超过阈值的 SQL，聚焦需要优化的对象。

配置慢查询日志（参考前文）：
1
2
3
4
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # 阈值设为 2 秒
log_queries_not_using_indexes = 1  # 记录未用索引的 SQL
分析慢查询：

用 pt_query_digest 工具筛选高频、高耗时的 SQL：

1
pt_query_digest /var/log/mysql/slow.log --filter '$event->{arg} =~ m/^select/i' > slow_analysis.txt

执行计划分析：定位 SQL 低效点

对慢查询使用 EXPLAIN 分析执行计划，重点关注以下字段：

  • type：是否为 ALL（全表扫描）或 range（范围扫描），目标是 ref 或 const。
  • key：是否使用预期索引，若为 NULL 则索引失效。
  • Extra：是否存在 Using filesort（文件排序）、Using temporary（临时表）等低效操作。
示例：
1
EXPLAIN SELECT * FROM user WHERE age > 30 ORDER BY register_time;

若 type = ALL 且 Extra = Using filesort，说明存在全表扫描和文件排序，需优化。

细节分析：用 performance_schema 深挖执行阶段

针对执行计划无法解释的低效（如命中索引仍慢），用 performance_schema 分析具体阶段耗时：

  • 若 Sending data 耗时高：返回数据量过大，需限制字段（避免 SELECT *）。
  • 若 Creating tmp table 频繁：排序 / 分组无索引，需添加复合索引（如 (age, register_time)）。
  • 若 Waiting for table lock：表锁冲突，需改用 InnoDB 引擎或优化事务。

优化与验证：调整参数或改写 SQL

根据分析结果优化后，需重新执行并验证是否达到目标：

  • 对比优化前后的执行时间、扫描行数。
  • 再次用 EXPLAIN 和 performance_schema 确认问题已解决。

SQL 优化的核心原则

优化的本质是减少资源消耗（IO、CPU、网络），遵循以下原则可大幅提升效率：

1. 减少数据扫描：避免全表扫描，高效利用索引

  • 优先使用索引过滤：确保 WHERE、JOIN ON 条件字段有索引，避免索引失效（如函数操作、类型转换）。
    例：WHERE SUBSTR(name, 1, 3) = 'abc' 改为 WHERE name LIKE 'abc%'（利用前缀索引）。
  • 缩小扫描范围：用 LIMIT 限制返回行数，或用时间 / 范围条件提前过滤数据。
    例：SELECT * FROM log 改为 SELECT * FROM log WHERE create_time > '2024-01-01'。
  • 避免全表扫描的 “陷阱”：即使有索引，若 WHERE 条件过滤性差（如 age > 0），MySQL 可能选择全表扫描（认为比索引更高效），需强制使用索引（FORCE INDEX）或优化条件。

2. 返回更少数据：只取必要字段，减少传输与处理

  • 禁用 SELECT \*：只查询需要的字段，减少磁盘读和网络传输量。
    例：SELECT id, name FROM user 而非 SELECT * FROM user。
  • 利用覆盖索引：查询字段均为索引列时，无需回表取数据（Extra: Using index）。
    例：索引 (age, name)，查询 SELECT age, name FROM user WHERE age > 30 可触发覆盖索引。
  • 分页查询优化：用 LIMIT offset, size 时，offset 过大会扫描大量无用数据，可改用 “延迟关联”：
    例：SELECT * FROM user WHERE age > 30 LIMIT 100000, 10
    优化为：SELECT u.* FROM (SELECT id FROM user WHERE age > 30 LIMIT 100000, 10) t JOIN user u ON t.id = u.id。

3. 减少交互次数：批量操作替代循环单次操作

  • 批量插入 / 更新：用 INSERT INTO ... VALUES (...), (...) 替代多次 INSERT，减少网络往返和事务提交开销。
    例：单次插入 1000 条数据比 1000 次单条插入快 10 倍以上。
  • 合并查询：用 UNION ALL 合并多个独立查询，避免多次连接。
    例：SELECT * FROM user WHERE id=1 和 SELECT * FROM user WHERE id=2 合并为 SELECT * FROM user WHERE id IN (1,2)。
  • 利用缓存：高频访问且变化少的结果（如配置表），缓存到应用内存或 Redis 中，减少数据库查询次数。

4. 小表驱动大表：减少外层循环次数

“小表驱动大表” 即让数据量少的表作为外层循环，减少总迭代次数：

  • IN 适合子查询结果小：SELECT * FROM 大表 WHERE id IN (SELECT id FROM 小表)。
  • EXISTS 适合外层表小：SELECT * FROM 小表 WHERE EXISTS (SELECT 1 FROM 大表 WHERE 大表.id = 小表.id)。

例：用户表（1000 行）为小表，订单表（100 万行）为大表，查询 “有订单的用户”：
SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id)（外层小表驱动大表，效率更高）。

5. 简化查询逻辑：避免复杂 join 和子查询

  • 拆分复杂 join：多表连接（如 5 表以上）会导致优化器选择低效执行计划，可拆分为多个单表查询，在应用层合并结果。
  • 用 join 替代子查询：MySQL 对子查询优化较差，复杂子查询可改写为 join。
    例：SELECT * FROM user WHERE id IN (SELECT user_id FROM order)
    优化为：SELECT u.* FROM user u JOIN order o ON u.id = o.user_id GROUP BY u.id。
  • 避免 OR 和 NOT IN：OR 可能导致索引失效，改用 UNION；NOT IN 效率低，改用 LEFT JOIN ... IS NULL。
    例：SELECT * FROM user WHERE id=1 OR id=2 改为 SELECT * FROM user WHERE id=1 UNION ALL SELECT * FROM user WHERE id=2。

MySQL 参数调优（辅助优化）

除 SQL 本身，合理调整 MySQL 配置可提升基础性能：

  • innodb_buffer_pool_size：设为物理内存的 50%-70%，提升缓存命中率（减少磁盘 IO）。
  • sort_buffer_size：增大排序缓冲区（如 4M），减少文件排序（Using filesort）。
  • tmp_table_size / max_heap_table_size：增大临时表大小（如 64M），避免临时表转磁盘。
  • join_buffer_size：优化连接查询，避免全表扫描（Using join buffer）。
相关文章

欢迎关注我的其它发布渠道