MySQL 实战：从建表到慢查询优化，做一遍订单列表

适用版本：MySQL 8.0+，存储引擎：InnoDB。本文用“查询某个用户的订单列表”串起表结构设计、索引、执行计划、分页和事务。示例中的状态值仅作演示，实际项目应统一定义并维护状态字典。

一、先把需求说清楚

假设业务需要支持：

• 按用户查看订单，可选按订单状态筛选；
• 按创建时间倒序排列；
• 支持翻页；
• 下单时扣库存并创建订单，不能出现超卖。

优化 SQL 前先确认数据量、查询频率、排序规则和一致性要求。数据库不是靠“多加几个索引”就能自动变快，索引也会增加写入和存储成本。

二、设计一张能用的订单表

CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    order_no    VARCHAR(32) NOT NULL,
    status      TINYINT UNSIGNED NOT NULL COMMENT '0待支付 1已支付 2已取消',
    amount      DECIMAL(12, 2) NOT NULL,
    created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_status_created (user_id, status, created_at, id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

几个落地要点：

1. amount 用 DECIMAL 表示金额，别用 FLOAT / DOUBLE 存钱。
2. created_at 统一按 UTC 写入更省心；展示时再按业务时区转换。DATETIME 本身不携带时区信息，团队要约定清楚。
3. order_no 是业务唯一键，数据库用唯一索引兜底，不能只靠应用层“先查再插”防重复。
4. 二级索引会带上 InnoDB 主键值。这里把 id 显式写进复合索引，是为了让索引定义更清楚；实际设计要结合执行计划和版本验证，不要机械地给每个索引都追加主键。
5. status 单独区分度通常不高，但与 user_id 组合后，可服务这个具体查询路径。

三、索引要跟着查询走

查询某用户最近的已支付订单：

SELECT id, order_no, amount, created_at
FROM orders
WHERE user_id = 10001
  AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

索引 (user_id, status, created_at, id) 的前两列用于等值过滤，后两列用于排序。复合索引要考虑列的顺序和查询条件：通常先放稳定的等值条件，再根据范围过滤、排序和覆盖需求调整。最终仍以真实 SQL 和 EXPLAIN 为准。

常见索引失效或效果差的写法

对索引列套函数：

-- 不推荐：对 created_at 做 DATE()，普通索引难以直接定位范围
SELECT id, order_no
FROM orders
WHERE DATE(created_at) = '2026-11-03';

改成半开区间：

SELECT id, order_no
FROM orders
WHERE created_at >= '2026-11-03 00:00:00'
  AND created_at <  '2026-11-04 00:00:00';

前置通配符模糊查询： LIKE '%abc' 通常无法利用普通 B-Tree 索引做前缀定位。若是商品全文检索，评估全文索引或专门的搜索系统；别指望给字段随手加索引就能解决。

隐式类型转换： 比较字段和参数类型要一致。比如字符串列不要拿数字参数去比，避免转换影响索引使用或结果语义。

无脑 SELECT *： 只取页面真正要展示的列，减少网络传输和回表成本；高频查询可以评估覆盖索引，但覆盖索引也会变宽。

四、用执行计划验证，而不是猜

EXPLAIN
SELECT id, order_no, amount, created_at
FROM orders
WHERE user_id = 10001
  AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

优先关注：

• key：实际选择的索引；
• type：访问方式。const、ref、range 常见于较有针对性的访问；ALL 表示全表扫描，但小表全扫未必就是问题；
• rows：优化器估算要读取的行数，不是精确值；
• Extra：留意 Using filesort、Using temporary 等信号，并结合数据规模判断。

MySQL 8.0.18+ 可用 EXPLAIN ANALYZE 查看实际执行耗时和行数。它会真正执行查询，线上对大查询使用时要评估负载：

EXPLAIN ANALYZE
SELECT id, order_no, amount, created_at
FROM orders
WHERE user_id = 10001
  AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

如果估算和实际行数差距很大，先检查统计信息及数据分布；需要时在评估影响后执行 ANALYZE TABLE orders 更新统计信息。别只盯着某一个字段就断言查询快慢，执行计划要和业务数据量一起看。

五、深分页改成游标分页

LIMIT 100000, 20 往往要扫描并丢弃大量记录，页数越深越吃力。后台管理列表、信息流等可以用上一页最后一条记录作为游标。

假设上一页最后一条记录是 created_at = '2026-11-03 09:30:00.123'、id = 9000：

SELECT id, order_no, amount, created_at
FROM orders
WHERE user_id = 10001
  AND status = 1
  AND (created_at, id) < ('2026-11-03 09:30:00.123', 9000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

游标里的排序字段必须与 ORDER BY 一致，并用唯一键（这里是 id）打破同一时间戳的并列。游标分页适合连续向后翻页；若产品必须支持任意跳到第 N 页，则需权衡体验和查询成本。

六、事务里做库存扣减

先看库存表：

CREATE TABLE inventory (
    sku_id      BIGINT UNSIGNED NOT NULL,
    available   INT UNSIGNED NOT NULL,
    updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (sku_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

简单的“先读库存、再在应用里判断、最后更新”会有并发竞态。可以把条件放进原子更新里，并检查受影响行数：

START TRANSACTION;

UPDATE inventory
SET available = available - 1
WHERE sku_id = 20001
  AND available > 0;

-- 应用检查上一条 UPDATE 的 affected rows：必须恰好为 1。
-- 若为 0，回滚并返回库存不足；若为 1，再创建订单。
INSERT INTO orders (user_id, order_no, status, amount)
VALUES (10001, 'ORD202611030001', 0, 129.00);

COMMIT;

应用代码要保证：库存更新失败时不插订单；插订单失败时回滚库存更新；数据库异常时执行回滚。订单号唯一索引用于处理重复提交带来的幂等问题，重复请求如何返回结果也要由业务层定义。

事务尽量短：不要在事务里调用远程 API、等待用户输入或做耗时计算。多行更新时尽可能按固定顺序访问资源，降低死锁概率。死锁是并发系统可能出现的正常冲突，应用需要识别死锁错误并对整个事务做有限次数重试；不要只重放事务中的最后一条 SQL。

七、上线前的检查清单

• [ ] SQL 的过滤条件、排序方式和索引列顺序匹配。
• [ ] 用接近真实的数据量验证 EXPLAIN，并记录关键查询的延迟。
• [ ] 新增索引会增加磁盘占用和写入成本；生产变更前确认执行方式、锁影响和回滚方案。
• [ ] 批量写入分批提交，避免单个事务过大、长时间占锁或产生过量 undo log。
• [ ] 慢查询日志按需开启并控制保留、采集和分析方式，先找最影响业务的 SQL。
• [ ] 备份不是“配置好了就结束”：定期做恢复演练，确认能恢复到预期时间点。
• [ ] 数据库连接使用参数化查询；不要用字符串拼接用户输入构造 SQL。

结语

MySQL 优化不是索引越多越好，而是让数据模型、查询路径和并发语义互相对上。先把业务查询写清楚，再用执行计划验证；事务保持短小，错误路径和重试策略也一起设计。这样做出来的 SQL，才不只是“本机能跑”，而是线上扛得住。