單機 MySQL 通常支撐 500 萬行 / 10GB 以內資料。超過此閾值需分庫分表。本文從面試高頻與實戰落地雙視角，系統梳理單機優化五大維度 25 個具體動作。
一、表結構優化（設計期決定 80% 效能）
1.1 字符集：統一 utf8mb4
雖 GBK 省空間，但 utf8mb4 相容全語言，遷移成本極高，存儲用錢解決。
1.2 主鍵：自增 BIGINT
InnoDB 聚集索引 = 主鍵順序。自增主鍵 → 順序寫入葉節點 → 無頁分裂 → 寫入極快。UUID/雪花算法主鍵 → 隨機寫入 → 頻繁分裂 → 效能殺手。
1.3 欄位規範
- 索引列 NOT NULL + DEFAULT
- 金額用
DECIMAL，禁 FLOAT/DOUBLE
- 大文本（文章/日誌）外存對象儲存，庫只存 URL
VARCHAR ≤ 8K，超長拆表或外存- 時間用
DATETIME（8B，無時區、範圍大），不用 TIMESTAMP（4B、2038 問題、時區敏感）
- 必加
gmt_create、gmt_modified 審計欄位
1.4 索引啟動原則
- 初期僅為「確定高頻」欄位建普通索引
- InnoDB 單列 ≤ 767B，組合 ≤ 3072B
二、SQL 優化（日常迭代核心）
2.1 基本查詢
- 禁
SELECT *，按需取列
- 高頻查詢（>10 萬/日）必走索引，否則加索引
- 低頻查詢（<100/日）可接受全表掃描
2.2 高效分頁
-- 傳統 LIMIT 100000,10 慢
-- 優化：子查詢定位 ID + 範圍掃描
SELECT id, name, age FROM t
WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 100000, 1)
LIMIT 10;
2.3 範圍查詢
IN 列表過大 → 全表掃描，拆分或改 JOIN 臨時表BETWEEN/>/< 放在索引列之後
2.4 模糊查詢
LIKE '%kw%' 不走索引，大數據量上 Elasticsearch- 實在要用：前置高選擇性索引列縮小掃描範圍
2.5 多表 JOIN
- 子查詢 → 改
JOIN（Nested Loop Join）
- 小結果集驅動大表，JOIN 欄位建索引
- 不可行 → 拆分為兩次簡單查詢，應用層組裝
- 最多 3 表 JOIN，超過必拆
三、連線池優化（抗高併發護城河）
以 DBCP 為例，關鍵參數建議值：
| 參數 | 建議值 | 說明 |
|---|
| initialSize | 歷史平均併發 | 首次取連線時建立 |
| minIdle | 5（極小併發可 1） | 保留最小空閒 |
| maxIdle | 併發高峰值 | 高峰後緩衝，避免頻繁建銷 |
| maxActive | 單機可接受極值（如 100） | 防惡意併發打爆 DB |
| maxWait | 3 秒 | 快速失敗，釋放執行緒 |
| minEvictableIdleTimeMillis | 30 分鐘 | 空閒回收閾值 |
| validationQuery | SELECT 1 | 檢測連線有效性 |
| testOnBorrow | false | 嚴重影響效能 |
| testOnReturn | false | 嚴重影響效能 |
| testWhileIdle | true | 後台清理時檢測，不影響前台 |
| numTestsPerEvictionRun | = maxActive | 每次檢測全量 |
啟動預熱：應用啟動時預執行簡單查詢，填滿連線池。
四、索引優化三級跳
- 一級：
WHERE 條件列建索引（單列/組合），遵循最左前綴
- 二級：
ORDER BY/GROUP BY 列建索引，利用天然有序避免排序
- 三級：查詢列全覆蓋（Covering Index），避免回表 I/O，極端場景慎用
選擇性原則：高選擇性列（如姓名、手機號）優先建索引，低選擇性（性別、狀態）不建或放組合索引末尾。
五、歷史資料歸檔（緩增長業務的低成本方案）
- 生命週期結束資料（如 6 個月前）定時歸檔至 HBase/ClickHouse/冷存儲
- Quartz 凌晨排程
SELECT ... WHERE gmt_modified < 6月前 批次導出
- 保留查詢介面，業務透明
- 避開分庫分表復雜度，單機再戰 1-2 年
總結：單機優化檢查清單
[ ] 字符集 utf8mb4
[ ] 自增 BIGINT 主鍵
[ ] 索引列 NOT NULL DEFAULT
[ ] 金額 DECIMAL
[ ] 大文本外存
[ ] 時間 DATETIME + 審計欄位
[ ] 高頻 SQL 走索引
[ ] 深分頁優化/游標分頁
[ ] 範圍查詢後置
[ ] 模糊查詢上 ES
[ ] JOIN ≤ 3 表 + 小驅動大
[ ] 連線池參數調優 + 預熱
[ ] 索引三級跳 + 選擇性分析
[ ] 歷史資料歸檔排程
[ ] 慢查詢日誌定期分析
單機優化是系統演進的必修課，做透了，分庫分表自然水到渠成。
- 上一篇文章： MySQL my.cnf 修改預設字符集為 UTF-8 完整配置與 unicode/ general/ bin 排序規則選型指南
- 下一篇文章： MySQL InnoDB 啟動失敗無法重啟：Checksum Mismatch 災難恢復實戰手冊
Copyright ©
程式碼之家 All Rights Reserved
