前言

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

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

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

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

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

1.為什么大表會(huì)慢？

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

1.1 磁盤(pán)IO瓶頸

大表的數(shù)據(jù)是存儲(chǔ)在磁盤(pán)上的，數(shù)據(jù)庫(kù)的查詢通常會(huì)涉及到數(shù)據(jù)塊的讀取。

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

舉例：

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

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

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

1.2 索引失效或沒(méi)有索引

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

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

舉例：

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

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

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

1.3 分頁(yè)性能下降

分頁(yè)查詢是大表中很常見(jiàn)的場(chǎng)景，但深度分頁(yè)（比如第100頁(yè)之后）會(huì)導(dǎo)致性能問(wèn)題。

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

舉例：

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

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

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

隨著頁(yè)碼的增加，查詢的性能會(huì)越來(lái)越差。

1.4 鎖爭(zhēng)用

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

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

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

優(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. 分庫(kù)分表：通過(guò)水平拆分、垂直拆分減少單表數(shù)據(jù)量。
5. 緩存和異步化：減少對(duì)數(shù)據(jù)庫(kù)的直接壓力。

接下來(lái)，我們逐一展開(kāi)。

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

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

3.1 精簡(jiǎn)字段類型

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

• 能用INT的不要用BIGINT。
• 能用VARCHAR(100)的不要用TEXT。
• 時(shí)間字段建議用TIMESTAMP或DATETIME，不要用CHAR或VARCHAR來(lái)存時(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) -- 限制最大長(zhǎng)度
);

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

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

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

垂直拆分

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

示例： 將訂單表分為兩個(gè)表：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ù)量過(guò)大時(shí)，可以按一定規(guī)則拆分到多張表中。

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

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

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

4.索引優(yōu)化

索引是數(shù)據(jù)庫(kù)性能優(yōu)化的“第一殺器”，但很多人對(duì)索引的使用并不熟悉，導(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  避免索引失效

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

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)換。錯(cuò)誤：

SELECT * FROM orders WHERE user_id = '123';

優(yōu)化：

SELECT * FROM orders WHERE user_id = 123;

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

5.SQL優(yōu)化

5.1 減少查詢字段

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

-- 錯(cuò)誤
SELECT * FROM orders WHERE user_id = 123;

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

5.2 分頁(yè)優(yōu)化

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

-- 深分頁(yè)（性能較差）
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;

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

6.分庫(kù)分表

6.1 水平分庫(kù)分表

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

常見(jiàn)的分庫(kù)分表規(guī)則：

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

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

7.緩存與異步化

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

對(duì)高頻查詢的數(shù)據(jù)可以存儲(chǔ)到Redis中，減少對(duì)數(shù)據(jù)庫(kù)的直接訪問(wèn)。

示例：

// 從緩存讀取數(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ì)列異步處理寫(xiě)操作

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

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

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

問(wèn)題：

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

解決方案：

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

總結(jié)

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

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