MySQL性能优化

MySQL 性能优化不是看到慢 SQL 就立刻加索引。真正有效的优化过程，应该先确认问题范围，再定位瓶颈，再选择成本最低、收益最稳定的方案。很多线上问题不是某一条 SQL 写得差，而是数据量、索引设计、查询模型、分页方式、事务范围和业务流程共同导致的。

在供应链系统里，订单、库存、采购、出入库、结算这些表增长很快。系统刚上线时几万条数据跑得很快，半年后变成几千万条，原来的查询就可能从几十毫秒变成几秒。优化 MySQL 的关键是让数据库少扫描、少排序、少回表、少锁等待。

MySQL SQL 优化步骤流程

第一步：先确认是不是数据库慢

当用户反馈订单列表慢时，不要直接打开 SQL 改写。应该先确认耗时发生在哪里。

一次请求的耗时可能来自：

  • 网关排队。
  • 应用线程池不足。
  • 远程服务调用慢。
  • Redis 缓存超时。
  • MySQL 查询慢。
  • 返回数据过大导致网络传输慢。
  • 前端渲染慢。

可以通过接口日志、链路追踪、APM、慢查询日志来拆分耗时。如果接口总耗时 3 秒，其中 MySQL 查询只用了 80 毫秒，那优化 SQL 不是当前重点。

在 Java 应用里，建议每个核心接口记录 traceId、SQL 耗时、调用下游耗时、返回条数。没有这些数据，优化容易变成猜。

第二步：找到真实慢 SQL

确认瓶颈在数据库后，再打开 MySQL 慢查询日志或使用监控平台找慢 SQL。

慢 SQL 的判断不能只看单次耗时，还要看调用频率。一个报表 SQL 偶尔跑 5 秒不一定最急；一个订单列表 SQL 每次 300 毫秒、每分钟调用几千次，可能对系统压力更大。

常用观察指标包括：

  • 平均耗时。
  • P95、P99 耗时。
  • 扫描行数。
  • 返回行数。
  • 执行次数。
  • 是否频繁创建临时表。
  • 是否频繁 filesort。
  • 锁等待时间。

供应链系统中常见慢 SQL 包括订单多条件查询、库存流水查询、采购单明细关联、应收应付报表、仓库作业统计。

第三步：使用 EXPLAIN 分析执行计划

拿到慢 SQL 后，必须用 EXPLAIN 看执行计划。执行计划告诉我们 MySQL 准备怎么执行这条 SQL。

重点关注这些字段：

  • type：访问类型，至少应尽量达到 range、ref，避免大表 ALL。
  • key：实际使用的索引。
  • rows：预估扫描行数。
  • Extra：是否出现 Using temporary、Using filesort。
  • filtered：过滤比例。

比如订单表 scm_order 有 3000 万行，页面按客户、状态、创建时间查询：

1
2
3
4
5
6
7
SELECT id, order_no, customer_id, status, created_at, total_amount
FROM scm_order
WHERE customer_id = 10086
  AND status = 'WAIT_DELIVERY'
  AND created_at >= '2024-08-01'
ORDER BY created_at DESC
LIMIT 20;

如果只在 customer_id 上有单列索引，MySQL 可能先找出该客户所有订单，再过滤状态和时间，并排序。客户订单量大时，这个查询就会慢。

第四步：按查询模式设计联合索引

索引不是越多越好。索引会提升查询，也会增加写入成本和存储成本。优化时应该根据高频查询模式设计联合索引。

上面的订单查询可以考虑：

1
2
CREATE INDEX idx_customer_status_created
ON scm_order(customer_id, status, created_at);

这样 MySQL 可以先按客户过滤，再按状态过滤，再按时间范围扫描。由于排序字段也在索引里，ORDER BY created_at DESC LIMIT 20 的成本会明显降低。

联合索引设计要考虑最左前缀原则。等值条件通常放在前面，范围条件和排序字段放在后面。对于不同查询，不要为了每个页面都创建一个索引，而是合并相近查询，找最核心的访问路径。

供应链系统里的常见索引组合包括：

  • 订单：customer_id + status + created_at。
  • 采购单：supplier_id + status + created_at。
  • 库存流水：sku_id + warehouse_id + created_at。
  • 出库单：warehouse_id + status + planned_ship_time。
  • 财务单据：tenant_id + bill_type + bill_date。

第五步：避免 SELECT *

很多列表接口习惯写 SELECT *，这在数据量小时问题不明显，数据量大后会导致更多 IO、更多网络传输，也更容易触发回表。

列表页应该只查询展示需要的字段。详情页再根据主键查询完整信息。

例如订单列表只需要订单号、客户、状态、金额、创建时间，就不要把备注、扩展 JSON、收货地址、审批意见一起查出来。供应链系统里很多表有大字段，比如合同备注、物流轨迹、扩展属性，这些字段对列表查询非常不友好。

如果查询字段都在索引里，还可能形成覆盖索引，减少回表。

第六步：优化分页方式

深分页是 MySQL 性能问题的高发点。

1
2
3
4
5
SELECT id, order_no, created_at
FROM scm_order
WHERE status = 'FINISHED'
ORDER BY created_at DESC
LIMIT 100000, 20;

这条 SQL 需要先跳过 100000 行，再返回 20 行。页码越深，成本越高。

更好的方式是基于游标分页：

1
2
3
4
5
6
SELECT id, order_no, created_at
FROM scm_order
WHERE status = 'FINISHED'
  AND created_at < '2024-08-24 14:00:00'
ORDER BY created_at DESC
LIMIT 20;

页面保存上一页最后一条记录的创建时间和 id，下一页从这个位置继续查。对于订单流水、库存流水、操作日志这类按时间滚动查看的数据，游标分页更稳定。

如果必须支持跳页，可以用延迟关联优化：先通过索引查出主键，再回表查详情。

第七步：减少大表 JOIN 和复杂统计

供应链系统经常有多表关联：订单表、客户表、商品表、库存表、仓库表、结算表。开发时写一个大 JOIN 很方便，但数据量上来后很难优化。

优化思路包括：

  • 把高频展示字段冗余到主表，例如客户名称、供应商名称、仓库名称。
  • 明细表和主表分开查询，避免列表页一次 JOIN 大量明细。
  • 报表类查询走宽表、汇总表或数仓，不要压在交易库上。
  • 对低频复杂查询做异步导出。

例如订单列表需要展示客户名称，可以在订单表冗余 customer_name_snapshot。客户改名时不影响历史订单展示，也减少订单列表 JOIN 客户表。

第八步：控制事务范围和锁

SQL 慢不一定是扫描慢，也可能是锁等待。

供应链系统里库存扣减、采购入库、出库确认都可能更新同一批库存行。如果事务里先调用外部接口，再更新库存，就会把锁持有时间拉长，导致并发请求排队。

优化原则是：

  • 事务内只做必要的数据库操作。
  • 外部接口调用放在事务外。
  • 固定多表更新顺序，减少死锁。
  • 库存扣减使用条件更新，避免先查后改的并发漏洞。
  • 对热点库存考虑分仓、分批次、分桶。

库存扣减可以写成：

1
2
3
4
5
6
UPDATE scm_inventory
SET available_qty = available_qty - 10,
    locked_qty = locked_qty + 10
WHERE sku_id = 123
  AND warehouse_id = 8
  AND available_qty >= 10;

然后根据影响行数判断是否锁定成功。这比先查询库存再更新更安全。

第九步：用业务模型减少查询压力

有些性能问题不能只靠 SQL 调优解决，而要调整业务模型。

例如 ERP 首页要展示今天订单数、待发货数、库存预警数、采购待审数。如果每次打开首页都实时扫订单表、库存表、采购表，数据库压力会很大。

更合理的方式是把这些指标做成统计表：

1
2
3
4
5
6
7
scm_dashboard_metric
- metric_date
- tenant_id
- wait_delivery_count
- low_stock_sku_count
- pending_purchase_count
- updated_at

业务事件发生后异步更新统计表，首页直接读统计结果。这样牺牲几秒一致性，换来稳定的查询性能。

第十步：验证优化效果

优化完成后，必须验证，而不是只看执行计划。

验证内容包括：

  • 优化前后 SQL 耗时对比。
  • 扫描行数是否下降。
  • 是否仍然出现临时表和 filesort。
  • 高并发下连接池、CPU、IO 是否改善。
  • 写入性能是否被新增索引拖慢。
  • 结果是否和优化前一致。

对于核心 SQL，建议把优化结论记录下来：原 SQL、问题原因、改造方案、索引变更、回滚方式、压测结果。这样后续团队维护时不会重复踩坑。

总结

MySQL 性能优化的步骤可以概括为：确认瓶颈、定位慢 SQL、分析执行计划、设计索引、改写 SQL、优化分页、减少大 JOIN、控制事务、调整业务模型、验证效果。

在供应链系统里，性能优化不能只看数据库技巧，还要理解业务访问模式。订单列表、库存流水、采购审批、财务报表的查询目标不同，优化策略也不同。真正稳定的优化方案，是让 SQL、索引和业务模型匹配起来。