MySQL 索引设计：从一条 SQL 推导联合索引

商户 7 的订单列表只展示已支付订单，按创建时间和主键倒序，每页 20 条：

SELECT id, created_at
FROM orders
WHERE tenant_id = 7 AND status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20;

tenant_id 是商户编号，status 是订单状态，created_at 是创建时间，id 是主键。设计前先写清：

Composite Index（联合索引，也叫多列索引）把多个列按声明顺序拼成有序键。(tenant_id,status,created_at) 先比较商户，商户相同再比较状态，前两项相同才比较时间；这种逐列比较叫字典序。

Sargable（Search Argument Able，可用搜索参数定位）描述一种条件：数据库能把它转换成索引起点、终点或若干明确区间，而不是逐行计算后才知道是否满足。

-- 保留 created_at 自己的有序性，并覆盖全天
WHERE created_at >= '2026-09-01 00:00:00'
  AND created_at <  '2026-09-02 00:00:00'

Clustered Index（聚簇索引）是叶子直接包含完整行的索引；Secondary Index（二级索引）是叶子保存索引列和主键的其他索引。回表就是二级索引命中后，再按主键去聚簇索引取得完整行。Covering Index（覆盖索引）是某个查询的筛选列与返回列都能从同一索引得到，因此回表次数为 0；它不是一种新的树类型。

Index Condition Pushdown（ICP，索引条件下推）是存储引擎读取二级索引记录时，先检查索引中已有的附加条件，只让通过者回表。它减少回表，但没有保证回表为 0。

ORDER BY 要求结果有序。若索引扫描顺序与结果顺序一致，MySQL 可能边读边返回；否则通常需要 filesort。filesort 是 MySQL 对额外排序阶段的名称，不表示一定写磁盘。

InnoDB 会把主键追加到二级索引记录中。因此 (tenant_id,status,created_at) 对本查询可利用附带的 id，并可能反向扫描来满足 created_at DESC,id DESC。不要先凭感觉创建显式降序索引，先由执行计划确认。

深分页为什么慢

-- 返回 20 条，但要先经过并丢弃前 100000 条
LIMIT 100000, 20;

Keyset Pagination（游标分页）记住上一页最后一条的排序键，从边界继续读取：

WHERE tenant_id=7 AND status='PAID'
  AND (created_at,id) < ('2026-09-01 10:05:00',309)
ORDER BY created_at DESC, id DESC LIMIT 20;

1. 收集高频查询族、真实参数与延迟目标，不为一条偶发 SQL 定制索引。
2. 用等值、范围和排序条件推导候选区间，明确索引的每一列负责什么。
3. 估算扫描记录、回表次数、额外排序与返回数据量。
4. 检查新索引宽度、写入频率、磁盘和缓冲池占用。
5. 用执行计划比较改动前后；确认新联合索引是否覆盖旧索引用途后，才评估移除重复索引。

下面从建表开始准备同一批数据。两条查询都用 FORCE INDEX 固定选择 idx_created_at；这个提示只用于控制实验变量，不是生产优化的默认写法。

DROP TABLE IF EXISTS orders_index_demo;

CREATE TABLE orders_index_demo (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

INSERT INTO orders_index_demo VALUES
(101,7,'PAID','2026-08-31 23:50:00',18.00),
(205,7,'PAID','2026-09-01 09:20:00',88.00),
(309,7,'PAID','2026-09-01 10:05:00',39.00),
(412,7,'PAID','2026-09-01 11:40:00',120.00),
(490,7,'REFUND','2026-09-01 15:00:00',55.00),
(501,8,'NEW','2026-09-01 18:30:00',65.00),
(520,8,'PAID','2026-09-02 00:00:00',75.00),
(601,8,'PAID','2026-09-02 12:00:00',99.00);

-- A：先扫描索引记录，再逐条计算 DATE()
EXPLAIN ANALYZE SELECT id
FROM orders_index_demo FORCE INDEX (idx_created_at)
WHERE DATE(created_at)='2026-09-01';

-- B：直接形成左闭右开的时间范围
EXPLAIN ANALYZE SELECT id
FROM orders_index_demo FORCE INDEX (idx_created_at)
WHERE created_at>='2026-09-01 00:00:00'
  AND created_at<'2026-09-02 00:00:00';

预期现象：A 无法由普通列索引直接算出日期范围，通常要检查整棵指定索引中的 8 条记录；B 可以把扫描边界锁定在当天的 5 条记录。小数据用于验证计划形态，不代表生产耗时。

面试时可以这样答：“我会先固定高频查询的过滤、排序和返回列，再按联合索引的字典序推导候选怎样一步步缩小。等值列是否放前面要看它能否稳定固定业务分组；范围后的列不一定完全无效，还可能参与索引层过滤或覆盖。最后要比较扫描、回表和排序是否下降，并确认新增索引的写入与空间成本可接受。”

下一步：进入《MySQL 执行计划：10 分钟看懂 EXPLAIN》，学习从普通 EXPLAIN 输出判断索引访问、预计扫描量与额外操作。

• MySQL 8.4：Multiple-Column Indexes
• MySQL 8.4：Index Condition Pushdown
• MySQL 8.4：ORDER BY Optimization
• MySQL 8.4：Use of Index Extensions