前言

大表優(yōu)化是一個老生常談的話題，但隨著業(yè)務(wù)規(guī)模的增長，總有人會“中招”。

很多小伙伴的數(shù)據(jù)庫在剛開始的時(shí)候表現(xiàn)良好，查詢也很流暢，但一旦表中的數(shù)據(jù)量上了千萬級，性能問題就開始浮現(xiàn)：查詢慢、寫入卡、分頁拖沓、甚至偶爾直接宕機(jī)。

這時(shí)大家可能會想，是不是數(shù)據(jù)庫不行？是不是需要升級到更強(qiáng)的硬件？

其實(shí)很多情況下，根本問題在于沒做好優(yōu)化。

今天，我們就從問題本質(zhì)講起，逐步分析大表常見的性能瓶頸，以及如何一步步優(yōu)化，希望對你會有所幫助。

1.為什么大表會慢？

在搞優(yōu)化之前，先搞清楚大表性能問題的根本原因。數(shù)據(jù)量大了，為什么數(shù)據(jù)庫就慢了？

1.1 磁盤IO瓶頸

大表的數(shù)據(jù)是存儲在磁盤上的，數(shù)據(jù)庫的查詢通常會涉及到數(shù)據(jù)塊的讀取。

當(dāng)數(shù)據(jù)量很大時(shí)，單次查詢可能需要從多個磁盤塊中讀取大量數(shù)據(jù)，磁盤的讀寫速度會直接限制查詢性能。

舉例：

假設(shè)有一張訂單表orders，里面存了5000萬條數(shù)據(jù)，你想要查詢某個用戶的最近10條訂單：

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

如果沒有索引，數(shù)據(jù)庫會掃描整個表的所有數(shù)據(jù)，再進(jìn)行排序，性能肯定會拉胯。

1.2 索引失效或沒有索引

如果表的查詢沒有命中索引，數(shù)據(jù)庫會進(jìn)行全表掃描（Full Table Scan），也就是把表里的所有數(shù)據(jù)逐行讀一遍。

這種操作在千萬級別的數(shù)據(jù)下非常消耗資源，性能會急劇下降。

舉例：

比如你在查詢時(shí)寫了這樣的條件：

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

這里用了DATE()函數(shù)，數(shù)據(jù)庫需要對所有記錄的order_time字段進(jìn)行計(jì)算，導(dǎo)致索引失效。

1.3 分頁性能下降

分頁查詢是大表中很常見的場景，但深度分頁（比如第100頁之后）會導(dǎo)致性能問題。

即使你只需要10條數(shù)據(jù)，但數(shù)據(jù)庫仍然需要先掃描出前面所有的記錄。

舉例：

查詢第1000頁的10條數(shù)據(jù)：

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

這條SQL實(shí)際上是讓數(shù)據(jù)庫先取出前9990條數(shù)據(jù)，然后丟掉，再返回后面的10條。

隨著頁碼的增加，查詢的性能會越來越差。

1.4 鎖爭用

在高并發(fā)場景下，多個線程同時(shí)對同一張表進(jìn)行增刪改查操作，會導(dǎo)致行鎖或表鎖的爭用，進(jìn)而影響性能。

2.性能優(yōu)化的總體思路

性能優(yōu)化的本質(zhì)是減少不必要的IO、計(jì)算和鎖競爭，目標(biāo)是讓數(shù)據(jù)庫盡量少做“無用功”。

優(yōu)化的總體思路可以總結(jié)為以下幾點(diǎn)：

1. 表結(jié)構(gòu)設(shè)計(jì)要合理：盡量避免不必要的字段，數(shù)據(jù)能拆分則拆分。
2. 索引要高效：設(shè)計(jì)合理的索引結(jié)構(gòu)，避免索引失效。
3. SQL要優(yōu)化：查詢條件精準(zhǔn)，盡量減少全表掃描。
4. 分庫分表：通過水平拆分、垂直拆分減少單表數(shù)據(jù)量。
5. 緩存和異步化：減少對數(shù)據(jù)庫的直接壓力。

接下來，我們逐一展開。

3.表結(jié)構(gòu)設(shè)計(jì)優(yōu)化

表結(jié)構(gòu)是數(shù)據(jù)庫性能優(yōu)化的基礎(chǔ)，設(shè)計(jì)不合理的表結(jié)構(gòu)會導(dǎo)致后續(xù)的查詢和存儲性能問題。

3.1 精簡字段類型

字段的類型決定了存儲的大小和查詢的性能。

• 能用INT的不要用BIGINT。
• 能用VARCHAR(100)的不要用TEXT。
• 時(shí)間字段建議用TIMESTAMP或DATETIME，不要用CHAR或VARCHAR來存時(shí)間。

舉例：

-- 不推薦
CREATETABLE orders (
    idBIGINT,
    user_id BIGINT,
    order_status VARCHAR(255),
    remarks TEXT
);

-- 優(yōu)化后
CREATETABLE orders (
    idBIGINT,
    user_id INTUNSIGNED,
    order_status TINYINT, -- 狀態(tài)用枚舉表示
    remarks VARCHAR(500) -- 限制最大長度
);

這樣可以節(jié)省存儲空間，查詢時(shí)也更高效。

如果對表設(shè)計(jì)比較感興趣，可以看看我之前的另一篇文章《表設(shè)計(jì)的18條軍規(guī)》，里面有詳細(xì)的介紹。

3.2  表拆分：垂直拆分與水平拆分

垂直拆分

當(dāng)表中字段過多，某些字段并不是經(jīng)常查詢的，可以將表按照業(yè)務(wù)邏輯拆分為多個小表。

示例： 將訂單表分為兩個表：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)
);

水平拆分

當(dāng)單表的數(shù)據(jù)量過大時(shí)，可以按一定規(guī)則拆分到多張表中。

示例： 假設(shè)我們按用戶ID對訂單表進(jìn)行水平拆分：

orders_0 -- 存user_id % 2 = 0的訂單
orders_1 -- 存user_id % 2 = 1的訂單

拆分后每張表的數(shù)據(jù)量大幅減少，查詢性能會顯著提升。

4.索引優(yōu)化

索引是數(shù)據(jù)庫性能優(yōu)化的“第一殺器”，但很多人對索引的使用并不熟悉，導(dǎo)致性能不升反降。

4.1  創(chuàng)建合適的索引

為高頻查詢的字段創(chuàng)建索引，比如主鍵、外鍵、查詢條件字段。

示例：

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

上面的復(fù)合索引可以同時(shí)加速user_id和order_time的查詢。

4.2  避免索引失效

• 別對索引字段使用函數(shù)或運(yùn)算。錯誤：

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

優(yōu)化：

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

• 注意隱式類型轉(zhuǎn)換。錯誤：

SELECT * FROM orders WHERE user_id = '123';

優(yōu)化：

SELECT * FROM orders WHERE user_id = 123;

如果對索引失效問題比較感興趣，可以看看我之前的另一篇文章《聊聊索引失效的10種場景，太坑了》，里面有詳細(xì)的介紹。

5.SQL優(yōu)化

5.1 減少查詢字段

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

-- 錯誤
SELECT * FROM orders WHERE user_id = 123;

-- 優(yōu)化
SELECT id, order_time FROM orders WHERE user_id = 123;

5.2 分頁優(yōu)化

深度分頁時(shí)，使用“延遲游標(biāo)”的方式避免掃描過多數(shù)據(jù)。

-- 深分頁（性能較差）
SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

-- 優(yōu)化：使用游標(biāo)
SELECT * FROM orders WHERE order_time < '2023-01-01 12:00:00'
  ORDER BY order_time DESC LIMIT 10;

如果對SQL優(yōu)化比較感興趣，可以看看我之前的另一篇文章《聊聊sql優(yōu)化的15個小技巧》，里面有詳細(xì)的介紹。

6.分庫分表

6.1 水平分庫分表

當(dāng)單表拆分后仍無法滿足性能需求，可以通過分庫分表將數(shù)據(jù)分散到多個數(shù)據(jù)庫中。

常見的分庫分表規(guī)則：

• 按用戶ID取模。
• 按時(shí)間分區(qū)。

如果對分庫分表比較感興趣，可以看看我之前的另一篇文章《阿里二面：為什么要分庫分表？》，里面有詳細(xì)的介紹。

7.緩存與異步化

7.1 使用Redis緩存熱點(diǎn)數(shù)據(jù)

對高頻查詢的數(shù)據(jù)可以存儲到Redis中，減少對數(shù)據(jù)庫的直接訪問。

示例：

// 從緩存讀取數(shù)據(jù)
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); // 設(shè)置緩存1小時(shí)
}

7.2 使用消息隊(duì)列異步處理寫操作

高并發(fā)寫入時(shí)，可以將寫操作放入消息隊(duì)列（如Kafka），然后異步批量寫入數(shù)據(jù)庫，減輕數(shù)據(jù)庫壓力。

如果對Kafka的一些問題比較感興趣，可以看看我之前的另一篇文章《我用kafka兩年踩過的一些非比尋常的坑》，里面有詳細(xì)的介紹。

8.實(shí)戰(zhàn)案例

問題：

某電商系統(tǒng)的訂單表存儲了5000萬條記錄，用戶查詢訂單詳情時(shí)，頁面加載時(shí)間超過10秒。

解決方案：

1. 垂直拆分訂單表：將訂單詳情字段拆分到另一個表中。
2. 創(chuàng)建復(fù)合索引：為user_id和order_time創(chuàng)建索引。
3. 使用Redis緩存：將最近30天的訂單緩存到Redis中。
4. 分頁優(yōu)化：使用search_after代替LIMIT深分頁。

總結(jié)

大表性能優(yōu)化是一個系統(tǒng)性工程，需要從表結(jié)構(gòu)、索引、SQL到架構(gòu)設(shè)計(jì)全方位考慮。

千萬級別的數(shù)據(jù)量看似龐大，但通過合理的拆分、索引設(shè)計(jì)和緩存策略，可以讓數(shù)據(jù)庫輕松應(yīng)對。