千萬級的大表，如何做性能優化？
前言
大表優化是一個老生常談的話題，但隨著業務規模的增長，總有人會“中招”。
很多小伙伴的數據庫在剛開始的時候表現良好，查詢也很流暢，但一旦表中的數據量上了千萬級，性能問題就開始浮現：查詢慢、寫入卡、分頁拖沓、甚至偶爾直接宕機。
這時大家可能會想，是不是數據庫不行？是不是需要升級到更強的硬件？
其實很多情況下，根本問題在于沒做好優化。
今天，我們就從問題本質講起，逐步分析大表常見的性能瓶頸，以及如何一步步優化，希望對你會有所幫助。
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到架構設計全方位考慮。
千萬級別的數據量看似龐大，但通過合理的拆分、索引設計和緩存策略，可以讓數據庫輕松應對。
