大表性能优化：从原理到入土笔记

共计 4424 个字符，预计需要花费 12 分钟才能阅读完成。

1. 为什么大表会慢？

1.1 磁盘 I/O 成为瓶颈

数据量一旦远超内存，就不可避免地落到 磁盘随机 I/O，而数据库每次查询都要在缓冲池命中失败时触盘读取数据页（典型 16KB）。提升 innodb_buffer_pool_size 可显著减少物理 I/O；若 CPU 利用率 < 70% 而时延仍高，往往说明I/O 受限。(dev.mysql.com)

相关：如何优化 InnoDB 磁盘 I/O、SSD vs HDD 的 I/O 特性与参数建议。(mysql.net.cn)

1.2 没有索引 / 索引失效导致全表扫描

对大表来说，全表扫描是灾难。MySQL 文档明确建议：对大表尽量 避免优化器选中表扫，必要时更新统计、使用索引提示或限制 max_seeks_for_key。(dev.mysql.com)

常见索引失效场景：

在索引列上做函数 / 表达式（如 DATE(col)）；若必须，可用 生成列 + 索引 或函数索引（8.0 引入）化解。(dev.mysql.com)
模糊匹配前缀为 % 的 LIKE '%term%' 不可用 B-Tree 走定界范围，需全文索引或倒排；尽量改成 前缀匹配 或专用检索方案。(use-the-index-luke.com)
连接条件类型不一致（隐式转换）会破坏可索引性（sargability）。(dev.mysql.com)

1.3 深度分页开销爆炸

OFFSET 会让服务器 读取并丢弃 前 N 行，页码越深，浪费越大。推荐改为 Keyset/Seek 分页（“上一页 / 下一页”游标式），稳定且与页深度无关。(use-the-index-luke.com)

1.4 锁争用

高并发下写多表 / 行引发锁等待。理解 InnoDB 的锁类型与隔离级别，并用 跳过锁 等技术降低互斥。MySQL/PG 文档可作为权威参考；在 PG 可用 FOR UPDATE SKIP LOCKED 做队列型并发消费。(docs.oracle.com)

2. 优化总思路（把“无用功”砍掉）

表结构 合理化（类型精简、冷热拆分 / 垂直拆分、分区 / 分表）。
正确且少而精的索引（复合索引、覆盖索引、生成列 / 函数索引、统计信息）。
可索引的 SQL（写成“范围 + 等值”的 sargable 谓词；避免函数 / 隐式转换）。
分页改造（Keyset/Seek）。
分库分表 / 分区（按时间 / 主键哈希切片，减少单表体量）。(dev.mysql.com)
缓存与异步（Redis Cache-Aside、消息队列削峰）。(Microsoft Learn)

3. 表结构设计

3.1 字段类型精简

窄行 = 更高的缓存命中与 I/O 吞吐。尽量用恰当最小类型（如 INT/TINYINT 表示状态，时间用 DATETIME/TIMESTAMP）。

3.2 垂直拆分（冷热分离）

把 低频 / 大字段（JSON、TEXT、备注等）移到 ..._details 表，主表只保留查询热点列，减少回表代价与缓存污染。

3.3 分区与分表

MySQL 分区 ：按 RANGE/LIST/HASH 等切分，配合 分区修剪 减少扫描范围。适用于时间序列 / 归档场景。(dev.mysql.com)
PostgreSQL 声明式分区：内建 RANGE/LIST/HASH，父表为“虚表”，数据存于子分区；大表管理和维护体验优秀。(PostgreSQL)
进一步水平切分（Sharding）：用户维度分片 + 路由（如 Vitess）。(vitess.io)

4. 索引优化（实战配方）

4.1 复合索引与最左前缀

常见高频查询 WHERE user_id = ? AND order_time BETWEEN ... ORDER BY order_time DESC LIMIT 10：
建索引：(user_id, order_time DESC)，既加速过滤又可 避免排序 / 减少 filesort。MySQL 对多列索引遵循 最左前缀 原则。(dev.mysql.com)

4.2 覆盖索引

查询字段尽量包含在索引里，触发 Using index，避免回表。例如只取 id, order_time 且索引覆盖这两列。(dev.mysql.com)

4.3 可索引谓词（Sargable）写法

避免 DATE(order_time) = '2023-01-01'，改为 半开区间：order_time >= '2023-01-01' AND order_time < '2023-01-02'。
需要表达式时，优先 生成列 + 索引 / 函数索引（MySQL 8）。(docs.oracle.com)
避免 隐式类型转换（比如把字符串同数值比）。(dev.mysql.com)
LIKE '%xxx%' 走不动 B-Tree，尽量变更为前缀或全文 / 倒排搜索引擎。(use-the-index-luke.com)

4.4 EXPLAIN & 优化器行为

通过 EXPLAIN 观察 type/key/rows/Extra（是否 Using index/filesort）。
遇到 ORDER BY ... LIMIT 场景，了解 8.0+ 的 prefer_ordering_index 策略开关及 ORDER BY 优化细节。(dev.mysql.com)
极端情况下用 索引提示（JOIN_INDEX/ORDER_INDEX 等）微调优化器选择。(mysql.net.cn)

5. SQL 写法优化

5.1“只拿需要的列”

避免 SELECT *，让覆盖索引有用武之地，降低 I/O 与网络成本（MySQL 官方优化建议）。(dev.mysql.com)

5.2 深度分页 ⇒ Keyset/Seek

把

-- 慢：读取并丢弃前 9990 行
SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

改为（基于“上一页最后一条”的锚点）：

SELECT id, order_time, ... 
FROM orders 
WHERE order_time < :last_order_time 
ORDER BY order_time DESC 
LIMIT 10;

稳定、可扩展，且与页深无关。(use-the-index-luke.com)

6. 并发与锁（简要策略）

读多写少：读写分离、二级缓存。
队列 / 任务表：PG 可用 FOR UPDATE SKIP LOCKED 多消费者并发拉取，避免互斥等待；MySQL 则通过范围切分 / 批处理与短事务化降低锁持有时间。(PostgreSQL)

7. 分库分表与归档

时间分区 + 自动归档：历史分区转移到冷存储，在线分区只保留近 N 月。(dev.mysql.com)
Sharding（Vitess 等）：按用户 / 租户哈希路由写入单分片，降低单库热度与锁竞争。(vitess.io)

8. 缓存与异步化

**Cache-Aside（旁路缓存）** 是通用落地做法：读 miss → 回源 DB → 回填缓存；写入时处理一致性与失效策略。(Microsoft Learn)
写入削峰：将非强一致的写操作放入 Kafka 异步消费、批量入库。(Apache Kafka)

9. 一个电商订单“大表”实战清单（5000 万行示例）

表与索引

-- 主表（高频字段）CREATE TABLE orders_basic (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  status TINYINT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  KEY idx_user_time (user_id, order_time DESC),
  KEY idx_time (order_time DESC)
);

-- 低频 / 大字段单独放
CREATE TABLE orders_details (
  id BIGINT PRIMARY KEY,
  remarks VARCHAR(500),
  shipping_address VARCHAR(255)
);

idx_user_time 既加速“某用户最近 N 条”又免排序；idx_time 服务全站按时间倒序检索。(dev.mysql.com)

查询改写

最近 10 条订单：SELECT id, order_time, status, total_amount FROM orders_basic WHERE user_id = :uid ORDER BY order_time DESC LIMIT 10; 命中 (user_id, order_time)，避免文件排序。(dev.mysql.com)
翻页（Keyset）：SELECT id, order_time, status, total_amount FROM orders_basic WHERE user_id = :uid AND order_time < :last_time ORDER BY order_time DESC LIMIT 10; 避免 OFFSET 的丢弃扫描。(use-the-index-luke.com)
日期过滤：改 DATE(order_time)=... 为半开区间，确保可索引。(dev.mysql.com)

缓存

用户“最近 30 天订单”做 Cache-Aside（Redis Key：orders:uid:{uid}:last30d），失效用 写后删缓存 策略，避免脏读。(Microsoft Learn)

归档 / 分区

orders_basic 做月分区或把历史迁到 orders_basic_2024xx 表，只保留近 6–12 个月在线分区，减少活跃索引体积。(dev.mysql.com)

并发处理

队列 / 任务领用在 PG 可采用 FOR UPDATE SKIP LOCKED 防止“抢同一条”。(PostgreSQL)

10. 速查清单（Checklist）

热查询是否 命中复合索引 并避免排序 / 回表？（看 EXPLAIN 的 key/Extra）(dev.mysql.com)
是否把 函数 / 隐式转换 移到右侧常量 / 生成列，保证 sargable？(dev.mysql.com)
是否把深分页改为 Keyset/Seek？(use-the-index-luke.com)
是否为大表启用 分区 / 归档，控制活跃数据窗口？(dev.mysql.com)
是否用 Redis Cache-Aside 缓住热点，辅以消息队列削峰？(Microsoft Learn)
InnoDB 缓冲池和 I/O 参数是否合理（I/O 不是瓶颈）？(dev.mysql.com)

参考（关键条目）

MySQL 官方手册：WHERE 优化、ORDER BY/LIMIT 优化、避免全表扫描、多列索引、生成列 / 函数索引、锁与事务模型。(dev.mysql.com)
Use The Index, Luke!：Keyset/Seek 分页、LIKE 与索引、排序与分组等系统性索引实践。(use-the-index-luke.com)
PostgreSQL 文档：SELECT ... FOR UPDATE SKIP LOCKED、声明式分区。(PostgreSQL)
MySQL 分区文档与实践：分区类型、修剪与限制。(dev.mysql.com)
Vitess：MySQL 分片路由与伸缩。(vitess.io)
缓存模式（Cache-Aside 等）：Microsoft/AWS 白皮书。(Microsoft Learn)

打赏赞