千万级的大表，如何做性能调优？

前言

大表优化是一个老生常谈的话题，但随着业务规模的增长，总有人会“中招”。

很多小伙伴的数据库在刚开始的时候表现良好，查询也很流畅，但一旦表中的数据量上了千万级，性能问题就开始浮现，查询慢、写入卡、分页拖沓、甚至偶尔直接宕机。这时大家可能会想，是不是数据库不行？是不是需要升级到更强的硬件？

其实很多情况下，根本问题在于没做好优化。

今天，我们就从问题本质讲起，逐步分析大表常见的性能瓶颈，以及如何一步步优化。

一、为什么大表会慢？

在搞优化之前，先搞清楚大表性能问题的根本原因。数据量大了，为什么数据库就慢了？

1. 磁盘 IO 瓶颈

大表的数据是存储在磁盘上的，数据库的查询通常会涉及到数据块的读取。

当数据量很大时，单次查询可能需要从多个磁盘块中读取大量数据，磁盘的读写速度会直接限制查询性能。

举例：

假设有一张订单表orders，里面存了 5000 万条数据，你想要查询某个用户的最近 10 条订单：

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_time DESC LIMIT 10;

如果没有索引，数据库会扫描整个表的所有数据，再进行排序，性能肯定会拉胯。

2. 索引失效或没有索引

如果表的查询没有命中索引，数据库会进行全表扫描（Full Table Scan），也就是把表里的所有数据逐行读一遍。

这种操作在千万级别的数据下非常消耗资源，性能会急剧下降。

举例：

比如你在查询时写了这样的条件：

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

这里用了DATE()函数，数据库需要对所有记录的order_time字段进行计算，导致索引失效。

3. 分页性能下降

分页查询是大表中很常见的场景，但深度分页（比如第 100 页之后）会导致性能问题。

即使你只需要 10 条数据，但数据库仍然需要先扫描出前面所有的记录。

举例：

查询第 1000 页的 10 条数据：

SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

这条 SQL 实际上是让数据库先取出前 9990 条数据，然后丢掉，再返回后面的 10 条。

随着页码的增加，查询的性能会越来越差。

4. 锁争用

在高并发场景下，多个线程同时对同一张表进行增删改查操作，会导致行锁或表锁的争用，进而影响性能。

二、性能优化的总体思路

性能优化的本质是减少不必要的 IO、计算和锁竞争，目标是让数据库尽量少做“无用功”。

优化的总体思路可以总结为以下几点：

1. 表结构设计要合理：尽量避免不必要的字段，数据能拆分则拆分。

2. 索引要高效：设计合理的索引结构，避免索引失效。

3. SQL 要优化：查询条件精准，尽量减少全表扫描。

4. 分库分表：通过水平拆分、垂直拆分减少单表数据量。

5. 缓存和异步化：减少对数据库的直接压力。

接下来，我们逐一展开。

三、表结构设计优化

表结构是数据库性能优化的基础，设计不合理的表结构会导致后续的查询和存储性能问题。

1. 精简字段类型

字段的类型决定了存储的大小和查询的性能。

• 能用INT的不要用BIGINT。

• 能用VARCHAR(100)的不要用TEXT。

• 时间字段建议用TIMESTAMP或DATETIME，不要用CHAR或VARCHAR来存时间。

举例：

-- 不推荐CREATE TABLE orders (    id BIGINT,    user_id BIGINT,    order_status VARCHAR(255),    remarks TEXT);
-- 优化后CREATE TABLE orders (    id BIGINT,    user_id INT UNSIGNED,    order_status TINYINT, -- 状态用枚举表示    remarks VARCHAR(500) -- 限制最大长度);

这样可以节省存储空间，查询时也更高效。

2. 表拆分：垂直拆分与水平拆分

垂直拆分

当表中字段过多，某些字段并不是经常查询的，可以将表按照业务逻辑拆分为多个小表。

示例：将订单表分为两个表：orders_basic 和 orders_details。

-- 基本信息表CREATE TABLE orders_basic (    id BIGINT PRIMARY KEY,    user_id INT UNSIGNED,    order_time TIMESTAMP);
-- 详情表CREATE TABLE orders_details (    id BIGINT PRIMARY KEY,    remarks VARCHAR(500),    shipping_address VARCHAR(255));

水平拆分

当单表的数据量过大时，可以按一定规则拆分到多张表中。

示例：假设我们按用户 ID 对订单表进行水平拆分：

orders_0 -- 存user_id % 2 = 0的订单orders_1 -- 存user_id % 2 = 1的订单

拆分后每张表的数据量大幅减少，查询性能会显著提升。

四、索引优化

索引是数据库性能优化的“第一杀器”，但很多人对索引的使用并不熟悉，导致性能不升反降。

1. 创建合适的索引

为高频查询的字段创建索引，比如主键、外键、查询条件字段。

示例：

CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time DESC);

上面的复合索引可以同时加速user_id和order_time的查询。

2. 避免索引失效

• 别对索引字段使用函数或运算。

错误：

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

优化：

SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00'  AND order_time < '2023-01-02 00:00:00';

• 注意隐式类型转换。

错误：

SELECT * FROM orders WHERE user_id = '123';

优化：

SELECT * FROM orders WHERE user_id = 123;

五、SQL 优化

1. 减少查询字段

只查询需要的字段，避免SELECT *。

-- 错误SELECT * FROM orders WHERE user_id = 123;
-- 优化SELECT id, order_time FROM orders WHERE user_id = 123;

2. 分页优化

深度分页时，使用“延迟游标”的方式避免扫描过多数据。

-- 深分页（性能较差）SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;
-- 优化：使用游标SELECT * FROM orders WHERE order_time < '2023-01-01 12:00:00'  ORDER BY order_time DESC LIMIT 10;

六、分库分表

1. 水平分库分表

当单表拆分后仍无法满足性能需求，可以通过分库分表将数据分散到多个数据库中。

常见的分库分表规则：

• 按用户 ID 取模。

• 按时间分区。

七、缓存与异步化

1. 使用 Redis 缓存热点数据

对高频查询的数据可以存储到 Redis 中，减少对数据库的直接访问。

示例：

// 从缓存读取数据String result = redis.get("orders:user:123");if (result == null) {    result = database.query("SELECT * FROM orders WHERE user_id = 123");    redis.set("orders:user:123", result, 3600); // 设置缓存1小时}

2. 使用消息队列异步处理写操作

高并发写入时，可以将写操作放入消息队列（如 Kafka），然后异步批量写入数据库，减轻数据库压力。

八、实战案例

问题：

某电商系统的订单表存储了 5000 万条记录，用户查询订单详情时，页面加载时间超过 10 秒。

解决方案：

1. 垂直拆分订单表：将订单详情字段拆分到另一个表中。

2. 创建复合索引：为user_id和order_time创建索引。

3. 使用 Redis 缓存：将最近 30 天的订单缓存到 Redis 中。

4. 分页优化：使用search_after代替LIMIT深分页。

九、总结

大表性能优化是一个系统性工程，需要从表结构、索引、SQL 到架构设计全方位考虑。

千万级别的数据量看似庞大，但通过合理的拆分、索引设计和缓存策略，可以让数据库轻松应对。

最重要的是，根据业务特点选择合适的优化策略，切勿盲目追求“高大上”的方案。

希望这些经验能帮到你！

文章转载自：苏三说技术

原文链接：https://www.cnblogs.com/12lisu/p/18680990

体验地址：http://www.jnpfsoft.com/?from=001YH