千万级大表的优化技巧

大家好，我是苏三，又跟大家见面了。

 

前言

大表优化是一个老生常谈的话题，但随着业务规模的增长，总有人会“中招”。
很多小伙伴的数据库在刚开始的时候表现良好，查询也很流畅，但一旦表中的数据量上了千万级，性能问题就开始浮现：查询慢、写入卡、分页拖沓、甚至偶尔直接宕机。
这时大家可能会想，是不是数据库不行？是不是需要升级到更强的硬件？
其实很多情况下，根本问题在于没做好优化。
今天，我们就从问题本质讲起，逐步分析大表常见的性能瓶颈，以及如何一步步优化，希望对你会有所帮助。
1 为什么大表会慢？

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

大表的数据是存储在磁盘上的，数据库的查询通常会涉及到数据块的读取。
当数据量很大时，单次查询可能需要从多个磁盘块中读取大量数据，磁盘的读写速度会直接限制查询性能。
举例：

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

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

复制代码

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

如果表的查询没有命中索引，数据库会进行全表扫描（Full Table Scan），也就是把表里的所有数据逐行读一遍。
这种操作在千万级别的数据下非常消耗资源，性能会急剧下降。
举例：

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

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

复制代码

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

分页查询是大表中很常见的场景，但深度分页（比如第100页之后）会导致性能问题。
即使你只需要10条数据，但数据库仍然需要先扫描出前面所有的记录。
举例：

查询第1000页的10条数据：

1. 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来存时间。
  

举例：

1. -- 不推荐
2. CREATETABLE orders (
3.     idBIGINT,
4.     user_id BIGINT,
5.     order_status VARCHAR(255),
6.     remarks TEXT
7. );
9. -- 优化后
10. CREATETABLE orders (
11.     idBIGINT,
12.     user_id INTUNSIGNED,
13.     order_status TINYINT, -- 状态用枚举表示
14.     remarks VARCHAR(500) -- 限制最大长度
15. );

复制代码

这样可以节省存储空间，查询时也更高效。
如果对表设计比较感兴趣，可以看看我之前的另一篇文章《表设计的18条军规》，里面有详细的介绍。
3.2  表拆分：垂直拆分与水平拆分

垂直拆分

当表中字段过多，某些字段并不是经常查询的，可以将表按照业务逻辑拆分为多个小表。
示例： 将订单表分为两个表：orders_basic 和 orders_details。

1. -- 基本信息表
2. CREATETABLE orders_basic (
3.     idBIGINT PRIMARY KEY,
4.     user_id INTUNSIGNED,
5.     order_time TIMESTAMP
6. );
8. -- 详情表
9. CREATETABLE orders_details (
10.     idBIGINT PRIMARY KEY,
11.     remarks VARCHAR(500),
12.     shipping_address VARCHAR(255)
13. );

复制代码

水平拆分

当单表的数据量过大时，可以按一定规则拆分到多张表中。
示例： 假设我们按用户ID对订单表进行水平拆分：

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

复制代码

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

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

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

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

复制代码

上面的复合索引可以同时加速user_id和order_time的查询。
4.2  避免索引失效

<ul >别对索引字段使用函数或运算。
错误：

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

复制代码

优化：
[code]SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00'  AND order_time