優化核心思想

優化器只能優化「寫得好」的 SQL。糟糕的查詢計劃經優化器也難變高效。SQL 質量決定性能上限。

一、索引策略

建立索引原則

• ✅ 經常 JOIN 的列（非 FK 也要建）
• ✅ 經常 ORDER BY / GROUP BY 的列
• ✅ WHERE 條件中高選擇性列（唯一值多）
• ❌ 低選擇性列（如性別、狀態碼）單獨建索引無效
• ✅ 多列排序/分組 → 復合索引 INDEX(a,b,c)
• 🔧 定期 ANALYZE TABLE 更新統計信息
• 🔧 大量更新後 DROP + CREATE INDEX 重建，消除碎片

二、消除排序

• 利用索引天然有序：INDEX(a,b) 滿足 ORDER BY a,b
• 避免：索引缺列、順序不一致、跨表排序
• 不可避免時：縮小排序集（先過濾再排序）、增大 sort_buffer_size

三、消除大表順序掃描

嵌套循環殺手

-- 惡例：三層嵌套各 1000 行 → 10 億次訪問
SELECT * FROM a JOIN b ON a.id=b.a_id JOIN c ON b.id=c.b_id;

解決：連接列加索引

ALTER TABLE `b` ADD INDEX `idx_a_id` (`a_id`);
ALTER TABLE `c` ADD INDEX `idx_b_id` (`b_id`);

UNION 替代 OR 避免全掃

-- 原：強制全表掃描
SELECT * FROM orders WHERE (customer=104 AND order_id>1001) OR order_id=1008;

-- 優化：拆分走索引
SELECT * FROM orders WHERE customer=104 AND order_id>1001
UNION
SELECT * FROM orders WHERE order_id=1008;

四、避免相關子查詢

-- 慢：外層每行執行一次子查詢
SELECT * FROM outer WHERE col IN (SELECT col FROM inner WHERE inner.x = outer.x);

-- 快：JOIN 或 EXISTS
SELECT o.* FROM outer o JOIN inner i ON o.col = i.col WHERE i.x = o.x;
SELECT * FROM outer o WHERE EXISTS (SELECT 1 FROM inner i WHERE i.col = o.col AND i.x = o.x);

五、簡化正則/模糊匹配

慢	快	原理
LIKE '%abc%'	全文索引 / ES	倒排索引
LIKE 'abc%'	同左，但可走前綴索引	B+樹最左前綴
zip LIKE '98___'	zip > '98000' AND zip < '99000'	範圍查詢走索引
SUBSTR(zip,2,2) > '80'	zip > 'x8000' AND zip < 'x9000'	避免非首字符截取

六、臨時表加速複雜查詢

-- 原：多表 JOIN + 排序，重複執行
SELECT c.name, r.balance, ...
FROM cust c JOIN rcvbles r ON c.id=r.cust_id
WHERE r.balance>0 AND c.postcode>'98000'
ORDER BY c.name;

-- 優化：物化中間結果
CREATE TEMPORARY TABLE tmp_cust AS
SELECT c.name, r.balance, c.postcode
FROM cust c JOIN rcvbles r ON c.id=r.cust_id
WHERE r.balance>0
ORDER BY c.name;

-- 後續多次查詢直接用臨時表
SELECT * FROM tmp_cust WHERE postcode>'98000';

⚠️ 臨時表不感知主表變更，高頻寫入場景慎用。

七、分區表策略

-- 按日期範圍分區（最常用）
CREATE TABLE `logs` (
    `id` BIGINT UNSIGNED,
    `created_at` DATETIME NOT NULL,
    ...
    PRIMARY KEY (`id`, `created_at`)
) PARTITION BY RANGE (TO_DAYS(`created_at`)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查詢自動分區裁剪
EXPLAIN SELECT * FROM `logs` WHERE `created_at` >= '2024-01-15';

八、讀寫分離與緩存架構

# 架構圖
[寫主庫] --binlog--> [從庫1/從庫2] --查詢--> [應用]
                    |
                    +--> [Redis 緩存熱點數據]
                    +--> [ProxySQL 讀寫分離/查詢緩存/熔斷]

• 讀請求路由到從庫，寫請求走主庫
• 熱點數據（配置、字典、統計）進 Redis，TTL 可控
• ProxySQL 實現查詢路由、限流、緩存、故障切換

九、硬件與 OS 調優

• NVMe SSD + RAID 10 / 單盤
• innodb_flush_method=O_DIRECT 避免雙重緩存
• innodb_buffer_pool_size = 物理內存 60-70%
• vm.swappiness=1、關閉透明大頁、啟用 HugePages
• 網絡：萬兆網卡、關閉 TCP 慢啟動、調整 net.core.somaxconn

總結：海量數據優化路線圖

1. 慢查詢日誌 → 定位 Top SQL
2. EXPLAIN 分析執行計劃 → 缺索引/全掃/臨時表/文件排序
3. 加索引、改 SQL、調參數、分表分區
4. 架構層面：讀寫分離、緩存、異步化
5. 硬件層面：IOPS、內存、網絡
6. 建立監控告警：QPS、RT、慢查詢數、緩存命中率