資料庫往往是系統效能的瓶頸所在。本章涵蓋 SQL 最佳化、索引設計、連線池組態、分表分庫等核心主題。

SQL 最佳化技巧#

慢查詢分析#

-- 開啟慢查詢日誌
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超過1秒記錄

-- 分析執行計劃
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

執行計劃關鍵欄位：

欄位說明最佳化目標
type訪問類型system > const > eq_ref > ref > range > index > ALL
key使用的索引應有合適的索引
rows預估掃描行數越小越好
Extra額外資訊避免 Using filesort、Using temporary

SQL 編寫規範#

-- 1. 避免 SELECT *
-- 不推薦
SELECT * FROM orders WHERE id = 1;
-- 推薦
SELECT id, user_id, amount FROM orders WHERE id = 1;

-- 2. 避免在 WHERE 中對欄位進行函式運算
-- 不推薦（無法使用索引）
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- 推薦
SELECT * FROM orders WHERE create_time >= '2024-01-01'
                       AND create_time < '2025-01-01';

-- 3. 避免使用 OR，改用 IN 或 UNION
-- 不推薦
SELECT * FROM users WHERE status = 1 OR status = 2;
-- 推薦
SELECT * FROM users WHERE status IN (1, 2);

-- 4. LIKE 查詢避免左模糊
-- 不推薦（無法使用索引）
SELECT * FROM users WHERE name LIKE '%張';
-- 推薦（可以使用索引）
SELECT * FROM users WHERE name LIKE '張%';

JOIN 最佳化#

-- 1. 小表驅動大表
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.ref_id;

-- 2. 避免過多的 JOIN
-- 不推薦：超過 3 個表的 JOIN
-- 考慮分解為多個查詢或冗餘字段

-- 3. 確保 JOIN 欄位有索引
CREATE INDEX idx_ref_id ON large_table(ref_id);

索引設計原則#

索引類型#

類型說明適用場景
B+樹索引MySQL 默認索引範圍查詢、排序
Hash 索引Memory 引擎支持等值查詢
全文索引文本搜索大段文字搜索

索引設計規範#

-- 1. 選擇區分度高的欄位作為索引
-- 區分度 = COUNT(DISTINCT column) / COUNT(*)
-- 區分度 > 0.1 才有意義

-- 2. 聯合索引遵循最左前綴原則
CREATE INDEX idx_a_b_c ON table(a, b, c);
-- 可以利用索引的查詢：
-- WHERE a = 1
-- WHERE a = 1 AND b = 2
-- WHERE a = 1 AND b = 2 AND c = 3
-- 無法利用索引的查詢：
-- WHERE b = 2
-- WHERE c = 3
-- WHERE b = 2 AND c = 3

-- 3. 覆蓋索引避免回表
-- 查詢的欄位都在索引中
SELECT a, b FROM table WHERE a = 1;  -- 只需訪問索引，無需回表

索引失效場景#

以下情況會導致索引失效：

-- 1. 對索引欄位進行計算或函式操作
WHERE price * 2 > 100  -- 失效
WHERE price > 50       -- 有效

-- 2. 隱式類型轉換
-- 假設 phone 是 VARCHAR 類型
WHERE phone = 13800138000  -- 失效（數字）
WHERE phone = '13800138000' -- 有效（字串）

-- 3. 使用 != 或 NOT IN
WHERE status != 1  -- 可能失效

-- 4. IS NULL / IS NOT NULL（視情況）
WHERE name IS NULL  -- 視資料分佈而定

連線池組態#

為什麼需要連線池#

沒有連線池
每次請求 → 創建連線 → 執行 SQL → 關閉連線
              ↓
         創建連線耗時約 100ms

使用連線池
初始化 → 創建 N 個連線 → 放入池中
請求 → 從池中獲取連線 → 執行 SQL → 歸還連線
              ↓
         獲取連線耗時 < 1ms

HikariCP 組態#

spring:
  datasource:
    hikari:
      minimum-idle: 5 # 最小空閒連線數
      maximum-pool-size: 20 # 最大連線數
      idle-timeout: 300000 # 空閒連線逾時（5分鐘）
      max-lifetime: 1800000 # 連線最大存活時間（30分鐘）
      connection-timeout: 30000 # 獲取連線逾時（30秒）

連線數組態原則#

最大連線數 = (核心數 * 2) + 有效磁盤數

例如：4 核 CPU + 1 個 SSD
最大連線數 ≈ (4 * 2) + 1 = 9

實際組態需根據壓力測試調整

連線池不是越大越好，過多的連線反而會增加上下文切換開銷。

批量操作最佳化#

INSERT 批量最佳化#

// 不推薦：逐條插入
for (User user : users) {
    userMapper.insert(user);
}

// 推薦：批量插入
userMapper.batchInsert(users);
<!-- MyBatis 批量插入 -->
<insert id="batchInsert">
    INSERT INTO users (name, email, age) VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.email}, #{user.age})
    </foreach>
</insert>

UPDATE 批量最佳化#

// 使用 CASE WHEN 批量更新
UPDATE users SET status = CASE id
    WHEN 1 THEN 'active'
    WHEN 2 THEN 'inactive'
    WHEN 3 THEN 'active'
END
WHERE id IN (1, 2, 3);

批量大小控制#

// 分批處理，避免單次操作資料量過大
int batchSize = 1000;
List<List<User>> batches = Lists.partition(users, batchSize);

for (List<User> batch : batches) {
    userMapper.batchInsert(batch);
}

讀寫分離#

架構設計#

            ┌─────────────┐
            │  應用服務器  │
            └──────┬──────┘
                   │
            ┌──────┴──────┐
            │  資料源路由  │
            └──────┬──────┘
           ┌───────┴───────┐
           ↓               ↓
    ┌────────────┐  ┌────────────┐
    │ Master(寫) │→→│ Slave(讀)  │
    └────────────┘  └────────────┘
                    └────────────┐
                    │ Slave(讀)  │
                    └────────────┘

Spring 實現#

// 自定義資料源路由
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

// 使用註解標記讀寫
@ReadOnly  // 走從庫
public User getUser(Long id) { ... }

@Transactional  // 走主庫
public void updateUser(User user) { ... }

主從延遲問題#

主從同步存在延遲，可能導致剛寫入的資料讀不到。

解決方案：

  1. 強制走主庫：關鍵查詢強制讀主庫
  2. 快取補償：寫入後同時更新快取
  3. 延遲讀取：非實時場景使用延遲讀從庫

分表分庫#

何時需要分表分庫#

條件考慮方案
單表資料 > 5000 萬行考慮分表
單表資料 > 100GB考慮分表
資料庫連線數不足考慮分庫
I/O 或網路瓶頸考慮分庫

切分方式#

垂直切分
┌──────────────────────┐
│       用戶表         │
│ id│name│email│detail│
└──────────────────────┘
          ↓
┌────────────┐  ┌────────────┐
│ 用戶基本表 │  │ 用戶詳情表 │
│ id│name   │  │ id│detail  │
└────────────┘  └────────────┘

水平切分
┌────────────────────┐
│      訂單表        │
│ 1000萬條資料      │
└────────────────────┘
          ↓
┌──────────┐  ┌──────────┐  ┌──────────┐
│ 訂單表_0 │  │ 訂單表_1 │  │ 訂單表_2 │
│ id % 3=0 │  │ id % 3=1 │  │ id % 3=2 │
└──────────┘  └──────────┘  └──────────┘

分片鍵選擇#

// 常見分片策略
// 1. Hash 取模
int shardIndex = userId.hashCode() % 8;

// 2. 範圍分片
// 2024年資料在表1，2025年資料在表2

// 3. 一致性 Hash（擴容友好）
ConsistentHash<String> hash = new ConsistentHash<>(nodes);
String targetNode = hash.get(key);

分表分庫的問題#

問題解決方案
分散式事務2PC、TCC、Seata
跨庫 JOIN冗餘表、應用層組裝
跨庫分頁二次查詢、ES 輔助
全局 IDSnowflake、UUID
擴容困難一致性 Hash、預分片

Snowflake ID 生成#

64 位 ID 結構
┌─────────────┬──────────────┬──────────┬───────────┐
│ 1 bit 符號位 │ 41 bit 時間戳 │ 10 bit 機器 │ 12 bit 序列 │
└─────────────┴──────────────┴──────────┴───────────┘
              │              │           │
              └─ 69 年       └─ 1024 台   └─ 4096/ms
// Hutool 實現
Snowflake snowflake = IdUtil.getSnowflake(workerId, datacenterId);
long id = snowflake.nextId();

MySQL 參數調校#

InnoDB Buffer Pool#

# 緩衝池大小（推薦物理記憶體的 60-80%）
innodb_buffer_pool_size = 8G

# 緩衝池實體數（建議等於 CPU 核心數）
innodb_buffer_pool_instances = 8

日誌組態#

# redo log 大小
innodb_log_file_size = 1G

# redo log 緩衝區
innodb_log_buffer_size = 16M

# 日誌刷盤策略
# 0: 每秒刷盤，可能丟失1秒資料
# 1: 每次提交刷盤，最安全
# 2: 每次提交寫入 OS 快取
innodb_flush_log_at_trx_commit = 1

連線組態#

# 最大連線數
max_connections = 500

# 連線逾時
wait_timeout = 28800
interactive_timeout = 28800

InnoDB 存儲結構#

邏輯結構#

表空間 (Tablespace)
├── 段 (Segment)
│   ├── 資料段（B+樹葉子節點）
│   └── 索引段（B+樹非葉子節點）
└── 區 (Extent，1MB)
    └── 頁 (Page，16KB)
        └── 行 (Row)

LRU 淘汰策略#

InnoDB 使用改進的 LRU 演算法：

               midpoint(5/8處)
                    ↓
┌─────────────────────────────────────────┐
│ young 區（熱資料）│ old 區（新資料/冷資料）│
└─────────────────────────────────────────┘

新資料先放入 old 區，只有在 old 區停留超過一定時間（innodb_old_blocks_time）後被再次訪問，才會移入 young 區。這樣避免全表掃描等操作把熱資料沖掉。

總結#

最佳化方向關鍵點
SQL 最佳化避免全表掃描、減少 JOIN、使用覆蓋索引
索引設計選擇高區分度欄位、注意最左前綴原則
連線池合理組態連線數、設置逾時時間
批量操作使用批量 INSERT/UPDATE
讀寫分離分散讀壓力、注意主從延遲
分表分庫能不分就不分、選好分片鍵
資料庫最佳化檢查清單
  • 是否開啟慢查詢日誌？
  • 核心 SQL 是否分析過執行計劃？
  • 索引設計是否合理？
  • 連線池組態是否經過壓力測試？
  • 是否有批量操作最佳化空間？
  • 是否需要讀寫分離？
  • 是否需要分表分庫？
  • 參數組態是否根據場景調校？
Backward 非同步處理 Spring 生態系統 Forward