面對千萬甚至億級大表,單純加索引已無法解決所有問題。本文從**容量規劃、表結構設計、索引策略、SQL 改寫、參數調優、架構演進**六大維度,提供系統化大表優化方法論與實戰清單。
-- 核心查詢:WHERE user_id=? AND status=? ORDER BY created_at DESC LIMIT 20
-- 最優索引:
ALTER TABLE orders ADD INDEX idx_uid_status_created (user_id, status, created_at DESC); -- MySQL 8.0+ 支援降序索引
-- 覆蓋索引:SELECT id, user_id, status, created_at FROM ...
-- Extra: Using index (無回表)
索引設計原則:
ANALYZE TABLE 更新統計信息,配合 EXPLAIN 驗證。| 反模式 | 優化後 | 原理 |
|---|---|---|
SELECT * | SELECT col1, col2 | 減少網路/IO/回表 |
| 無 WHERE 全表掃描 | 加業務過濾條件 | 縮小掃描範圍 |
SELECT ... INTO temp WHERE 慢條件 | 拆分、加索引、或異步處理 | 避免鎖系統表 |
WHERE col = 123 (col 是 VARCHAR) | WHERE col = '123' | 避免隱式轉換導致索引失效 |
WHERE DATE(created_at) = '2024-01-01' | WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' | 函數/運算在索引列上失效 |
LIKE '%keyword%' | 全文索引 / Elasticsearch | 前導萬用字元無法用索引 |
| 多次 UPDATE 同一行 | 合併:UPDATE SET a=1, b=2 WHERE id=3 | 減少鎖持有時間、Redo Log |
| DELETE + INSERT 代替 UPDATE | 直接 UPDATE | 避免雙倍寫入、主鍵跳號 |
無意義查詢 WHERE 1=2 | 移除或應用層攔截 | 浪費解析/優化資源 |
| 子查詢 IN (大結果集) | JOIN / EXISTS / 臨時表 / CTE | MySQL 子查詢優化較弱 |
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 或 READ COMMITTED。[mysqld]
# 記憶體 (物理記憶體 60-80%)
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 128M
# Redo Log (寫入性能關鍵)
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1 # 雙1最安全,極致性能可設 2
innodb_flush_method = O_DIRECT
# 並發
innodb_thread_concurrency = 0 # 0=不限制
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4
# 連接
max_connections = 5000
thread_cache_size = 200
# 臨時表
tmp_table_size = 256M
max_heap_table_size = 256M
# 查詢緩存 (MySQL 8.0 已移除)
# query_cache_type = 0
DROP PARTITION 秒級歸檔,查詢分區裁剪。千萬級大表優化無銀彈,**架構設計 (分區/分表/異構) 解決量級,索引策略 (複合/覆蓋/降序) 解決查詢,SQL 改寫 (避免全掃/函數/隱式轉換) 解決常態,參數調優 (Buffer Pool/Redo Log/IO 線程) 解決極限**。建立**慢查詢日誌分析 → EXPLAIN 驗證 → 索引調整 → 壓測驗證 → 上線觀測**閉環,持續迭代。