我使用的是MySQL数据库，其中一张表中的数据量有1000万，后台可能会进行列表查询，我应该怎样给这张表建立索引（面试有问到）

给 1000 万数据的表建索引，不能只看“列表查询”四个字，必须看具体 SQL：WHERE 条件、ORDER BY、分页方式、返回字段、并发量。核心原则是：让列表查询尽量走联合索引，并且尽量避免回表、filesort、深分页。

下面给你一套可落地的思路。

---

1. 先明确后台列表查询的典型 SQL

后台列表一般长这样：

SELECT id, order_no, user_id, status, amount, create_time
FROM orders
WHERE tenant_id = ?
  AND status = ?
  AND create_time >= ?
  AND create_time < ?
ORDER BY create_time DESC, id DESC
LIMIT 20 OFFSET 0;

你要先收集最常用的几条 SQL，尤其是：

• 默认列表查询
• 带筛选条件的查询
• 带时间范围的查询
• 排序字段是什么
• 是否要 COUNT(*) 总数
• 是否支持模糊搜索
• 是否分页很深

---

2. 联合索引设计原则

2.1 基本顺序

一般建议：

等值条件列 -> 排序字段 -> 范围条件字段 -> 覆盖字段

但实际要结合 SQL。更准确地说：

1. 等值过滤字段放最左
   例如 tenant_id = ? AND status = ?，它们通常放联合索引最左边。

2. 排序字段尽量紧跟等值字段
   因为 ORDER BY 如果不能用索引，就会产生 filesort。

3. 范围条件字段位置要小心
   联合索引中，一旦某个列用了范围条件，它后面的列通常不能继续用于精确过滤和排序。

4. 高频查询字段可做覆盖索引
   如果列表只返回少量字段，可以把这些字段加到索引里，避免回表。

5. 最左前缀原则
   索引 (a, b, c)，查询 WHERE b = ? 用不上这个索引。

---

3. 常见列表场景与索引示例

场景一：租户 + 状态 + 创建时间倒序

WHERE tenant_id = ?
  AND status = ?
  AND create_time >= ?
ORDER BY create_time DESC
LIMIT 20;

推荐索引：

ALTER TABLE orders
ADD INDEX idx_tenant_status_time (tenant_id, status, create_time DESC);

如果排序还要加 id：

ORDER BY create_time DESC, id DESC

推荐：

ALTER TABLE orders
ADD INDEX idx_tenant_status_time_id (tenant_id, status, create_time DESC, id DESC);

场景二：租户 + 状态 + 按主键 id 倒序

WHERE tenant_id = ?
  AND status = ?
ORDER BY id DESC
LIMIT 20;

推荐：

ALTER TABLE orders
ADD INDEX idx_tenant_status_id (tenant_id, status, id DESC);

场景三：按用户查询，按时间倒序

WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 20;

推荐：

ALTER TABLE orders
ADD INDEX idx_user_time (user_id, create_time DESC);

场景四：覆盖索引，避免回表

如果列表只查这几个字段：

SELECT id, order_no, status, create_time
FROM orders
WHERE tenant_id = ? AND status = ?
ORDER BY create_time DESC
LIMIT 20;

可以考虑覆盖索引：

ALTER TABLE orders
ADD INDEX idx_cover_list (
  tenant_id,
  status,
  create_time DESC,
  id,
  order_no
);

注意：覆盖索引越宽，占空间越大，写入越慢。不要盲目把所有字段都塞进去。

---

4. 深分页是后台列表最大的坑

不要这样写：

LIMIT 1000000, 20

MySQL 会扫描前 100 万行再丢掉，非常慢。

方案一：游标分页 / 连续分页

WHERE tenant_id = ?
  AND status = ?
  AND (create_time, id) < (?, ?)
ORDER BY create_time DESC, id DESC
LIMIT 20;

索引：

(tenant_id, status, create_time DESC, id DESC)

这种分页性能最好，适合“下一页”模式。

方案二：延迟关联

如果必须用 OFFSET：

SELECT t.*
FROM orders t
JOIN (
  SELECT id
  FROM orders
  WHERE tenant_id = ?
    AND status = ?
  ORDER BY create_time DESC, id DESC
  LIMIT 1000000, 20
) x ON t.id = x.id;

子查询走覆盖索引，只取主键，再回表拿完整数据。

方案三：总数优化

如果列表要显示总数：

SELECT COUNT(*) FROM orders WHERE ...;

大范围 COUNT 也可能很慢。可以考虑：

• 缓存总数
• 单独维护计数表
• 前端不显示精确总数
• 限制最大页数
• 异步统计

---

5. 哪些情况索引会失效

注意这些写法：

WHERE DATE(create_time) = '2024-01-01'   -- 函数导致失效
WHERE create_time + 1 > ?                -- 运算导致失效
WHERE name LIKE '%abc%'                  -- 前置模糊失效
WHERE status != 1                        -- 通常不走索引
WHERE a = 1 OR b = 2                     -- 可能失效，可改 UNION ALL

应改成：

WHERE create_time >= '2024-01-01'
  AND create_time < '2024-01-02'

模糊搜索如果很复杂，建议上 Elasticsearch，而不是硬扛 MySQL。

---

6. 不要建太多索引

1000 万数据不算特别大，但索引会带来写放大：

• 每个索引都会占磁盘
• INSERT / UPDATE / DELETE 都要维护索引
• 索引太多，优化器可能选错

一般单表索引控制在 5～6 个以内。写多读少的表还要更少。

---

7. 用 EXPLAIN 验证，而不是猜

建完索引后一定执行：

EXPLAIN SELECT ...;

重点看：

• type：最好 ref、range，避免 ALL
• key：实际用了哪个索引
• rows：预估扫描行数
• Extra：
  • Using index：覆盖索引，很好
  • Using where：正常过滤
  • Using filesort：需要额外排序，尽量消除
  • Using temporary：临时表，尽量消除

MySQL 8.0 可以用：

EXPLAIN ANALYZE SELECT ...;

看真实执行时间。

---

8. 一个推荐落地流程

1. 找出后台最常用的 3～5 条列表 SQL。
2. 提取每条的 WHERE 等值字段、范围字段、ORDER BY 字段、返回字段。
3. 按“等值字段 + 排序字段 + 范围字段”设计联合索引。
4. 如果返回字段少，考虑覆盖索引。
5. 分页改成游标分页或延迟关联。
6. 用 EXPLAIN 验证，观察 filesort 和扫描行数。
7. 上线后开慢查询日志，用 pt-query-digest 分析。
8. 定期 ANALYZE TABLE 更新统计信息。

---

9. 总结一句话

对于 1000 万的后台列表表，不要单独给每个字段建索引，而要根据最常用 SQL 建联合索引，顺序通常是：

等值条件列 -> 排序字段 -> 范围字段 -> 覆盖字段

例如：

ALTER TABLE orders
ADD INDEX idx_list (
  tenant_id,
  status,
  create_time DESC,
  id DESC
);

同时重点解决：

• 深分页
• 回表
• filesort
• COUNT 总数
• 模糊搜索

如果你能把具体的表结构和列表查询 SQL 发出来，我可以直接帮你设计索引和分页方案。