Summer Reverie

外观

SQL优化最佳实践

约 1859 字大约 6 分钟

2025-08-30

慢 SQL 的定位

MySQL 提供了慢查询日志（Slow Query Log）功能，可以记录所有执行时间超过 long_query_time 设置值的 SQL 语句。通过分析慢查询日志，可以快速定位到需要优化的 SQL。

慢 SQL 的分析

慢 SQL 通常由以下原因导致：

  1. 数据量过大：单表数据量超过千万级，导致查询效率下降。
  2. 高并发请求：瞬时大量请求导致数据库资源争用。
  3. 索引问题：
    • 未建立索引
    • 索引失效（如函数操作、隐式类型转换）
    • 索引选择不当
  4. SQL 语句缺陷：
    • 低效的 JOIN 或子查询
    • 未充分利用索引覆盖
    • 冗余计算或数据传输
  5. 表结构设计缺陷：
    • 字段类型不合理（如字符串存储日期）
    • 过度范式化导致多表关联
    • 存在 NULL 字段或 TEXT/BLOB 大字段
  6. 业务逻辑不合理：
    • 深度分页查询（如 LIMIT 1000000, 10）
    • 实时计算可延迟的报表
  7. 服务器配置不足：
    • CPU、内存、磁盘 I/O 瓶颈

慢 SQL 的优化方案

索引优化

  • 控制索引数量：单表索引不超过 5 个。过多索引增加写操作成本，且优化器选择执行计划时消耗更多资源。
  • 联合索引设计：
    • 区分度最高的列放在最左侧（如 user_id 比 gender 区分度高）。
    • 高频查询条件优先左置。
    • 覆盖排序需求：若查询含 WHERE 和 ORDER BY，可合并为联合索引。
  • 避免索引失效场景：
    • 禁止对索引列使用函数（如 YEAR(create_time)）。
    • 避免隐式类型转换（如字符串列用数字查询）。

对 WHERE 和 ORDER BY 字段建立联合索引

对于查询：

SELECT age, city, name FROM user WHERE age = 20 AND city = 'Beijing' ORDER BY name;

建立 (age, city, name) 的联合索引，可以避免回表操作，并且 ORDER BY 操作可以直接利用索引的有序性，无需额外排序。

SQL 语句优化

  • 禁止 SELECT *：仅查询必要字段，减少网络传输与解析开销，提高覆盖索引利用率。
  • 小表驱动大表：JOIN 时确保小表作为驱动表。例如 1,000 行表 JOIN 1,000,000 行表时，小表驱动仅需 1,000 次大表索引扫描。
  • 用 UNION ALL 替代 UNION：UNION 自带去重排序，数据量大时效率低；若无需去重，优先使用 UNION ALL。
  • 分页优化：避免深度分页，改用游标或条件过滤。
    -- 低效
SELECT * FROM table LIMIT 1000000, 10
-- 高效
SELECT * FROM table WHERE id > 1000000 LIMIT 10

表结构优化

  • 范式与反范式设计：
    • 范式设计： 减少数据冗余，保证数据一致性。
    • 反范式设计： 在某些场景下，为了避免多表 JOIN 带来的开销，可以适度引入冗余字段（反范式设计），将经常一起使用的列放在同一张表中。
  • 选择最小数据类型： 优先选择能满足存储需求的最小数据类型。数据占用空间越小，磁盘 I/O 越少，内存缓存效率越高，性能越好。
  • 避免使用 TEXT、BLOB 数据类型存储大字段： 这类大字段会严重影响数据库性能。如果必须存储，应考虑将大字段分离到单独的扩展表中，主表只存储其引用地址。
  • 尽可能将列定义为 NOT NULL：
    • 允许 NULL 值的列需要额外的存储空间来记录 NULL 标志位。
    • 对 NULL 值进行比较和计算时，需要特殊处理，增加了复杂性和性能开销。
  • 避免使用字符串存储日期： 使用 DATETIME、TIMESTAMP 或数值型时间戳存储日期。字符串存储日期占用空间更大，比较和计算效率低，且无法直接使用日期相关的函数。
  • 单表字段数量适中： 单表字段过多会降低查询效率。可以考虑将冷门字段数据与热门字段数据进行“冷热分离”，分成两张表。

订单表冗余用户名称

  • 正常范式化设计：订单表（orders）只存储用户 ID（user_id），需要通过联表查询用户表（users）来获取用户名称（user_name）。
  • 适度冗余设计：在订单表中直接存储用户名称（user_name），这样查询订单详情时无需联表，提高查询效率。

业务优化

  • 合理化业务需求： 重新评估和修改不合理的业务需求，例如避免深度分页（查询第 1000 页）。
  • 数据预计算与缓存： 对于报表数据（如 T+1 报表），不应实时计算，而是在业务低峰期提前计算好并存储或缓存。

架构优化

  • 读写分离： 将读操作和写操作分发到不同的数据库实例。主库负责写，从库负责读，可以显著提升数据库的并发处理能力。
  • 分库分表： 当单表数据量过大或并发量极高时，可以将数据分散到多个数据库实例或数据表中。这可以降低单表数据量，提升查询效率，但会引入数据路由、分布式事务等复杂性，需谨慎权衡。
  • 缓存机制： 使用 Redis、Memcached 等缓存中间件，将热点数据缓存到内存中，减轻数据库压力，提高响应速度。
  • 分布式数据库： 考虑使用 TiDB 等分布式数据库解决方案，它们原生支持高并发和大数据量，提供水平扩展能力。

总结

怎么优化慢 SQL？

  1. 定位问题

    • 开启 慢查询日志，结合 EXPLAIN、SHOW PROFILE、performance_schema 等工具分析 SQL 的执行计划，确认是否是索引缺失、索引失效，还是表扫描、排序、锁等待等问题。
    • 同时会关注 慢 SQL 出现场景（高并发还是单条查询就慢），确认是 SQL 本身问题 还是 资源瓶颈。

  2. 优化 SQL 和索引

    • 索引优化：检查是否能通过增加或调整联合索引覆盖查询，避免函数操作和隐式类型转换导致索引失效。
    • SQL 优化：避免 SELECT *，合理使用 JOIN（小表驱动大表），避免深度分页（用条件过滤或游标方式），能用 UNION ALL 不用 UNION。

  3. 优化表结构

    • 检查字段类型，避免字符串存日期、避免 TEXT/BLOB 大字段影响性能。
    • 适度 反范式化，在热点查询中引入冗余字段，减少频繁的多表 JOIN。
    • 将大字段或冷数据拆分（冷热分离）。

  4. 从业务层优化

    • 深度分页查询（LIMIT 1000000,10）改用业务游标方式。
    • 报表类场景采用 预计算/缓存，而不是实时跑复杂 SQL。

  5. 从架构层面优化

    • 如果问题出在高并发或数据量过大，可以采用 读写分离、分库分表、缓存（Redis/Memcached）。
    • 在数据量和并发都极高的场景下，考虑分布式数据库方案。

示例

有一次我们遇到一个订单查询接口特别慢，我先通过慢查询日志定位到 SQL，然后用 EXPLAIN 看执行计划，发现 WHERE create_time 上没有索引，导致全表扫描。最后我加了 (user_id, create_time) 联合索引，查询时间大幅减少。