優化方法論:由內而外、由點到面
MySQL 優化不是調幾個參數,而是系統工程。遵循「測量 → 分析 → 優化 → 驗證 → 固化」閉環。
第一層:Schema 與數據類型 (基石)
1. 最小化數據類型
TINYINT(1) 代替 INT 存布爾/狀態 (1 byte vs 4 bytes)
MEDIUMINT 代替 INT 存百萬級 ID (3 bytes vs 4 bytes)
CHAR(6) 存郵編,CHAR(32) 存 MD5,CHAR(64) 存 SHA256
VARCHAR(N) 只存可變長文本,N 為業務最大長度
DECIMAL(12,2) 或 BIGINT(分) 存金額,禁用 FLOAT/DOUBLE
DATETIME(3) 存毫秒時間,TIMESTAMP 存自動更新時間戳
2. NOT NULL + DEFAULT
-- ✅ 優化器友好,統計信息準確,節省 NULL 位圖空間
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
3. 短索引 / 前綴索引
-- 長字符串列只索引前綴 (區分度足夠時)
ALTER TABLE users ADD INDEX idx_email_prefix (email(19)); -- email 區分度前 19 字符足夠
-- 驗證區分度
SELECT COUNT(DISTINCT LEFT(email, 19)) / COUNT(*) FROM users; -- > 0.9 可接受
第二層:索引設計 (加速器)
1. 索引創建原則
- WHERE/JOIN/ORDER BY/GROUP BY 涉及的列 → 候選索引列
- 高區分度列放前 (性別/狀態低區分度放後或不建單獨索引)
- 聯合索引遵循「最左前綴」+「等值在前、範圍在後」
- 覆蓋索引避免回表:
INDEX idx_a_b_c (a, b, c) 覆蓋 SELECT a,b,c FROM t WHERE a=1 AND b=2
- 單表索引數 ≤ 5 (寫入開銷、優化器選擇成本)
2. 索引失效場景避坑
| 失效模式 |
錯誤示例 |
修正 |
| 函數/運算包裹列 |
WHERE YEAR(dt) = 2024 |
WHERE dt >= '2024-01-01' AND dt < '2025-01-01' |
| 隱式類型轉換 |
WHERE varchar_col = 123 |
WHERE varchar_col = '123' |
| LIKE 以 % 開頭 |
WHERE name LIKE '%abc' |
全文索引 / 反轉存儲 / 搜索引擎 |
| OR 條件跨列無索引 |
WHERE a=1 OR b=2 |
UNION ALL 分開查詢 |
| IS NULL/IS NOT NULL |
WHERE col IS NULL |
給默認值 0/'',查 WHERE col = 0 |
| 聯合索引非最左 |
INDEX(a,b,c) WHERE b=1 |
建 INDEX(b) 或調整順序 |
第三層:SQL 重寫與執行計劃
1. EXPLAIN 核心列解讀
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1 ORDER BY created_at DESC LIMIT 20;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_uid_status| idx_uid_status| 5 | const | 100 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
關鍵列:
type: system > const > eq_ref > ref > range > index > ALL (由好到壞)
key: 實際使用的索引
rows: 預估掃描行數 (越小越好)
filtered: 過濾後剩餘比例 (越高越好)
Extra: Using index(覆蓋) / Using where / Using filesort(排序) / Using temporary(臨時表)
2. 優化器提示 (MySQL 8.0+)
-- 強制使用索引
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM orders
WHERE user_id = 100 /*+ INDEX(orders idx_uid_status) */;
-- 禁用索引
SELECT * FROM orders IGNORE INDEX (idx_uid_status) WHERE user_id = 100;
-- 指定連接順序
SELECT /*+ JOIN_ORDER(t1, t2) */ * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
3. 典型慢 SQL 改寫模板
-- 1. 大表分頁
-- ❌ LIMIT 100000, 20
-- ✅ 延遲關聯
SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 100000, 1) ORDER BY id LIMIT 20;
-- 2. COUNT(*) 優化
-- ❌ SELECT COUNT(*) FROM huge_table WHERE status = 1
-- ✅ 計數表 / Redis 緩存 / 近似值 SHOW TABLE STATUS
-- 3. 隨機取樣
-- ❌ ORDER BY RAND() LIMIT 10
-- ✅ WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t))) LIMIT 10
-- 4. 多表關聯排序
-- ❌ SELECT * FROM a JOIN b ON a.id=b.a_id ORDER BY a.created_at LIMIT 10
-- ✅ 先排序主表再 JOIN
SELECT * FROM (SELECT * FROM a ORDER BY created_at DESC LIMIT 10) a JOIN b ON a.id=b.a_id;
第四層:服務器配置 (引擎室)
內存分配公式 (專用 DB 服務器)
Total RAM = 64GB
OS Reserve = 4GB
MySQL Reserve = 60GB
innodb_buffer_pool_size = 48GB (75%)
innodb_log_file_size = 4GB
innodb_log_buffer_size = 256M
key_buffer_size = 128M (MyISAM 系統表)
sort_buffer_size = 4M (per thread)
read_buffer_size = 2M
join_buffer_size = 4M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_cache_size = 200
max_connections = 2000
關鍵參數影響對照表
| 參數 |
調大收益 |
調大風險 |
建議值 |
| innodb_buffer_pool_size |
緩存命中率↑、磁盤 IO↓ |
OS OOM、Swap 狂飆 |
物理內存 60-80% |
| innodb_log_file_size |
檢查點衝刷頻率↓、寫入性能↑ |
崩潰恢復時間↑ |
1-4GB |
| innodb_flush_log_at_trx_commit |
1→2 寫入性能↑ 10x |
掉電丟 1 秒數據 |
核心業務=1,日志/統計=2 |
| max_connections |
支撐更多並發 |
線程上下文切換、內存暴增 |
配合連接池,峰值*1.2 |
| sort_buffer_size |
大排序避免磁盤臨時表 |
每連接分配,連接多時 OOM |
2-8MB,會話級調大 |
第五層:架構擴展 (終極方案)
1. 讀寫分離
# ProxySQL 配置片段
mysql_servers:
- { hostgroup: 10, hostname: "master", port: 3306, weight: 100 }
- { hostgroup: 20, hostname: "slave1", port: 3306, weight: 100 }
- { hostgroup: 20, hostname: "slave2", port: 3306, weight: 100 }
mysql_query_rules:
- { rule_id: 1, active: 1, match_pattern: "^SELECT .* FOR UPDATE", destination_hostgroup: 10, apply: 1 }
- { rule_id: 2, active: 1, match_pattern: "^SELECT ", destination_hostgroup: 20, apply: 1 }
- { rule_id: 3, active: 1, match_pattern: "^(INSERT|UPDATE|DELETE|REPLACE)", destination_hostgroup: 10, apply: 1 }
2. 分庫分表 (Sharding)
- 垂直拆分:業務模塊解耦 (用戶庫、訂單庫、商品庫)
- 水平分表:按
user_id % 1024 或範圍分片,中間件路由
- 分布式事務:Seata AT 模式 / 本地消息表 / 事務性外發
3. 異構存儲分流
| 數據類型 |
推薦存儲 |
同步方式 |
| 核心交易 (OLTP) |
MySQL InnoDB |
主從 / Group Replication |
| 日志/審計/點擊流 |
ClickHouse / Apache Doris |
Canal + Kafka → Flink → OLAP |
| 全文搜索/日志分析 |
Elasticsearch / OpenSearch |
Canal 同步 Binlog |
| 熱點 Key-Value / 計數器 |
Redis Cluster |
應用雙寫 / Redis 源碼讀取 |
| 文件/圖片/視頻 |
S3 / MinIO / OSS |
應用直傳,DB 存 URL |
優化檢查清單 (上線前必驗)
- ☐ 所有表有主鍵 (最好是自增 BIGINT)
- ☐ 所有外鍵列有索引
- ☐ 無冗餘/未使用索引 (
sys.schema_unused_indexes)
- ☐ 慢查詢日志開啟,
long_query_time=0.5
- ☐ 核心業務 SQL 經過 EXPLAIN 驗證走索引
- ☐ 連接池配置:
maxLifetime < wait_timeout、validationQuery
- ☐ 備份恢復演練近 30 天內通過
- ☐ 監控覆蓋:QPS、TPS、延遲 P99、連接數、緩存命中率、磁盤 IOPS、CPU/內存
總結:MySQL 優化是「千層餅」,Schema 是地基,索引是骨架,SQL 是肌肉,配置是氣血,架構是神經。不要為了優化而優化,要以業務 SLA (可用性、延遲、吞吐) 為導向,投入產出比最高的優化先做。