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

很多小伙伴的数据库在刚开始的时候表现良好,查询也很流畅,但一旦表中的数据量上了千万级,性能问题就开始浮现:查询慢、写入卡、分页拖沓、甚至偶尔直接宕机。

这时大家可能会想,是不是数据库不行?是不是需要升级到更强的硬件?

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

今天,我们就从问题本质讲起,逐步分析大表常见的性能瓶颈,以及如何一步步优化,希望对你会有所帮助。

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

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

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

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

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_time DESC LIMIT 10;
如果没有索引,数据库会扫描整个表的所有数据,再进行排序,性能肯定会拉胯。

1.2 索引失效或没有索引
如果表的查询没有命中索引,数据库会进行全表扫描(Full Table Scan),也就是把表里的所有数据逐行读一遍。

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

举例:
比如你在查询时写了这样的条件:

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';
这里用了DATE()函数,数据库需要对所有记录的order_time字段进行计算,导致索引失效。

1.3 分页性能下降
分页查询是大表中很常见的场景,但深度分页(比如第100页之后)会导致性能问题。

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

举例:
查询第1000页的10条数据:

SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;
这条SQL实际上是让数据库先取出前9990条数据,然后丢掉,再返回后面的10条。

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

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

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

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

表结构设计要合理:尽量避免不必要的字段,数据能拆分则拆分。
索引要高效:设计合理的索引结构,避免索引失效。
SQL要优化:查询条件精准,尽量减少全表扫描。
分库分表:通过水平拆分、垂直拆分减少单表数据量。
缓存和异步化:减少对数据库的直接压力。
接下来,我们逐一展开。

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

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

能用INT的不要用BIGINT。
能用VARCHAR(100)的不要用TEXT。
时间字段建议用TIMESTAMP或DATETIME,不要用CHAR或VARCHAR来存时间。
举例:
-- 不推荐
CREATETABLE orders (
idBIGINT,
user_id BIGINT,
order_status VARCHAR(255),
remarks TEXT
);

-- 优化后
CREATETABLE orders (
idBIGINT,
user_id INTUNSIGNED,
order_status TINYINT, -- 状态用枚举表示
remarks VARCHAR(500) -- 限制最大长度
);
这样可以节省存储空间,查询时也更高效。

如果对表设计比较感兴趣,可以看看我之前的另一篇文章《表设计的18条军规》,里面有详细的介绍。

3.2 表拆分:垂直拆分与水平拆分
垂直拆分
当表中字段过多,某些字段并不是经常查询的,可以将表按照业务逻辑拆分为多个小表。

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

-- 基本信息表
CREATETABLE orders_basic (
idBIGINT PRIMARY KEY,
user_id INTUNSIGNED,
order_time TIMESTAMP
);

-- 详情表
CREATETABLE orders_details (
idBIGINT PRIMARY KEY,
remarks VARCHAR(500),
shipping_address VARCHAR(255)
);
水平拆分
当单表的数据量过大时,可以按一定规则拆分到多张表中。

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

orders_0 -- 存user_id % 2 = 0的订单
orders_1 -- 存user_id % 2 = 1的订单
拆分后每张表的数据量大幅减少,查询性能会显著提升。

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

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

示例:
CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time DESC);
上面的复合索引可以同时加速user_id和order_time的查询。

4.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;
如果对索引失效问题比较感兴趣,可以看看我之前的另一篇文章《聊聊索引失效的10种场景,太坑了》,里面有详细的介绍。

5 SQL优化
5.1 减少查询字段
只查询需要的字段,避免SELECT *。

-- 错误
SELECT * FROM orders WHERE user_id = 123;

-- 优化
SELECT id, order_time FROM orders WHERE user_id = 123;
5.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;
如果对SQL优化比较感兴趣,可以看看我之前的另一篇文章《聊聊sql优化的15个小技巧》,里面有详细的介绍。

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

常见的分库分表规则:
按用户ID取模。
按时间分区。
如果对分库分表比较感兴趣,可以看看我之前的另一篇文章《阿里二面:为什么要分库分表?》,里面有详细的介绍。

7 缓存与异步化
7.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小时
}
7.2 使用消息队列异步处理写操作
高并发写入时,可以将写操作放入消息队列(如Kafka),然后异步批量写入数据库,减轻数据库压力。

如果对Kafka的一些问题比较感兴趣,可以看看我之前的另一篇文章《我用kafka两年踩过的一些非比寻常的坑》,里面有详细的介绍。

8 实战案例
问题:
某电商系统的订单表存储了5000万条记录,用户查询订单详情时,页面加载时间超过10秒。

解决方案:
垂直拆分订单表:将订单详情字段拆分到另一个表中。
创建复合索引:为user_id和order_time创建索引。
使用Redis缓存:将最近30天的订单缓存到Redis中。
分页优化:使用search_after代替LIMIT深分页。
总结
大表性能优化是一个系统性工程,需要从表结构、索引、SQL到架构设计全方位考虑。

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

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

希望这些经验能帮到你!

最后说一句
如果这篇文章对您有所帮助,或者有所启发的话,帮忙关注一下我的同名公众号:苏三说技术,您的支持是我坚持写作最大的动力。
求一键三连:点赞、转发、在看。