大表性能优化：从问题到解决方案

用户11397231

发布于 2025-01-27 08:23:30

7500

代码可运行

文章被收录于专栏：算法算法

运行总次数：0

代码可运行

大表性能优化：从问题到解决方案

一、为什么大表会慢？

在进行优化之前，我们需要先了解大表性能问题的根本原因。当数据量增大时，数据库的性能为什么会下降？

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 锁争用

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

二、性能优化的总体思路

性能优化的本质是减少不必要的 IO、计算和锁竞争，目标是让数据库尽量少做“无用功”。优化的总体思路可以总结为以下几点：

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

三、表结构设计优化

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

3.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) -- 限制最大长度
);

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

3.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的订单

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

四、索引优化

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

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';

注意隐式类型转换。错误：

sql复制

SELECT * FROM orders WHERE user_id = '123';

优化：

sql复制

SELECT * FROM orders WHERE user_id = 123;

五、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;

六、分库分表

6.1 水平分库分表

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

按用户ID取模。
按时间分区。

七、缓存与异步化

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小时
}