🐬 MySQL Protocol 完整指南

⏱️ 閱讀時間： 12 分鐘 🎯 難度： ⭐⭐ (中等)

🎯 本篇重點

理解 MySQL 客戶端/伺服器協定的原理、連線建立流程、驗證過程、查詢執行機制，以及連線池如何優化效能。

🤔 什麼是 MySQL Protocol？

MySQL Protocol = MySQL 客戶端與伺服器之間的通訊協定

一句話解釋： MySQL Protocol 定義了客戶端（如 MySQL Workbench、程式）如何與 MySQL Server 建立連線、驗證身份、執行查詢的規則。

比喻：餐廳點餐流程
1. 進門（建立連線）
2. 出示會員卡（驗證身份）
3. 點餐（執行查詢）
4. 結帳離開（關閉連線）

MySQL Protocol 就是規範這整套流程的「標準作業程序」

🏗️ MySQL Protocol 在網路模型中的位置

OSI 7 層模型

┌──────────────────────────────┬─────────────────┐
│ 7. Application Layer (應用層) │  MySQL Protocol │ ← MySQL 在這裡
├──────────────────────────────┼─────────────────┤
│ 6. Presentation Layer (表示層)│  加密、壓縮      │
├──────────────────────────────┼─────────────────┤
│ 5. Session Layer (會話層)     │  建立、維護會話  │
├──────────────────────────────┼─────────────────┤
│ 4. Transport Layer (傳輸層)   │  TCP            │
├──────────────────────────────┼─────────────────┤
│ 3. Network Layer (網路層)     │  IP             │
├──────────────────────────────┼─────────────────┤
│ 2. Data Link Layer (資料鏈結層)│  Ethernet       │
├──────────────────────────────┼─────────────────┤
│ 1. Physical Layer (實體層)    │  網路線、光纖    │
└──────────────────────────────┴─────────────────┘

MySQL Protocol 位於第 7 層（應用層）

• MySQL Protocol 是應用層協定
• 提供資料庫查詢、資料傳輸服務
• 客戶端透過 MySQL Protocol 與資料庫伺服器互動

TCP/IP 4 層模型

┌─────────────────────────────┬─────────────────┐
│ 4. Application Layer (應用層) │  MySQL Protocol │ ← MySQL 在這裡
├─────────────────────────────┼─────────────────┤
│ 3. Transport Layer (傳輸層)  │  TCP            │
├─────────────────────────────┼─────────────────┤
│ 2. Internet Layer (網際網路層)│  IP             │
├─────────────────────────────┼─────────────────┤
│ 1. Network Access (網路存取層)│  Ethernet       │
└─────────────────────────────┴─────────────────┘

MySQL Protocol 位於第 4 層（應用層）

• 在 TCP/IP 模型中，MySQL Protocol 是應用層協定
• 使用 TCP 作為傳輸層協定（Port 3306）
• TCP 提供可靠的連線導向傳輸

對比表：

重點：

• MySQL Protocol 是應用層協定（兩種模型都是）
• 使用 TCP 作為傳輸層（Port 3306）
• TCP 提供可靠傳輸，MySQL Protocol 提供資料庫通訊
• 二進位協定（相對於 Redis 的文字協定）

🏗️ MySQL Protocol 架構

協定特性

為什麼 MySQL 選擇二進位協定？

1. ✅ 效能更好：數字直接用二進位表示，不需要字串轉換
2. ✅ 傳輸更小：整數 1000 只需 2-4 bytes，文字需要 4 bytes
3. ✅ 類型安全：明確標示資料類型

代價： ❌ 更難除錯（無法用 telnet 直接測試） ❌ 實作更複雜

📡 MySQL 通訊流程

客戶端                          MySQL Server
  │                                 │
  ├──────── 1. 建立 TCP 連線 ──────>│
  │                                 │
  │<─── 2. Server Greeting (握手) ──┤
  │         (Server 版本、能力、加密種子)
  │                                 │
  ├──── 3. Login Request (登入) ──>│
  │         (使用者名稱、密碼)
  │                                 │
  │<─────── 4. Auth Response ───────┤
  │         (OK / Error)
  │                                 │
  ├───── 5. Command Phase ─────────>│
  │         (SELECT, INSERT, etc.)
  │                                 │
  │<────── 6. Result Set ───────────┤
  │         (查詢結果)
  │                                 │
  ├────── 7. COM_QUIT (關閉) ──────>│
  │                                 │
  └──────── 8. 關閉 TCP 連線 ───────┘

🤝 階段 1：Server Greeting（握手）

流程

當客戶端連線到 MySQL Server 時：

1. 客戶端：建立 TCP 連線到 port 3306
2. Server：立即發送 Initial Handshake Packet

Initial Handshake Packet 內容

Server Greeting Packet 包含：

1. Protocol Version（協定版本）
   - 目前是 10

2. Server Version（伺服器版本）
   - 如："8.0.35-MySQL"

3. Connection ID（連線 ID）
   - 每個連線唯一編號

4. Auth Plugin Data（加密種子）
   - 用於密碼加密的隨機字串
   - 長度：20-21 bytes

5. Server Capabilities（伺服器能力）
   - 支援哪些功能（如：SSL、壓縮、prepared statements）

6. Character Set（字元集）
   - 預設字元集（如：utf8mb4）

7. Status Flags（狀態旗標）
   - 伺服器當前狀態

範例

Protocol: 10
Version: 8.0.35-MySQL
Connection ID: 12345
Auth Plugin Data: [20 random bytes]
Capabilities:
  - CLIENT_LONG_PASSWORD
  - CLIENT_PROTOCOL_41
  - CLIENT_SECURE_CONNECTION
  - CLIENT_PLUGIN_AUTH
  - CLIENT_SSL
Character Set: utf8mb4
Status: SERVER_STATUS_AUTOCOMMIT

重點：

• Server 主動發送，客戶端被動接收
• 客戶端根據 Server 能力決定使用哪些功能
• Auth Plugin Data 用於密碼加密（Challenge-Response）

🔐 階段 2：Authentication（驗證）

密碼加密流程

MySQL 使用 Challenge-Response 機制：

1. Server 發送 Random Seed（20 bytes）
2. 客戶端計算：
   hash = SHA1(password)
   double_hash = SHA1(hash)
   challenge = SHA1(seed + double_hash) XOR hash
3. 客戶端發送 challenge
4. Server 驗證：
   stored_hash = SHA1(SHA1(password))  ← 資料庫儲存的
   expected = SHA1(seed + stored_hash) XOR SHA1(password)
   if (challenge == expected) → 通過

為什麼這樣設計？ ✅ 密碼不會明文傳輸 ✅ 每次挑戰都不同（seed 隨機） ✅ 防止重放攻擊

Login Request Packet 內容

客戶端發送：

1. Client Capabilities（客戶端能力）
   - 客戶端支援的功能

2. Max Packet Size（最大封包大小）

3. Character Set（字元集）

4. Username（使用者名稱）
   - 明文

5. Auth Response（密碼雜湊）
   - SHA1 加密後的密碼

6. Database Name（資料庫名稱）
   - 可選，預設連線的資料庫

7. Auth Plugin Name（驗證插件）
   - 如："mysql_native_password"

驗證結果

成功：

OK Packet
  - affected_rows: 0
  - last_insert_id: 0
  - status_flags: SERVER_STATUS_AUTOCOMMIT
  - warnings: 0
  - info: ""

失敗：

ERR Packet
  - error_code: 1045
  - sql_state: "28000"
  - error_message: "Access denied for user 'root'@'localhost'"

💬 階段 3：Command Phase（命令階段）

常用命令類型

COM_QUERY（查詢命令）

封包格式：

[1 byte]  Command Type = 0x03
[n bytes] SQL Statement

範例：

SELECT * FROM users WHERE id = 1

封包：
0x03  "SELECT * FROM users WHERE id = 1"

Server 回應：

1. Column Count (欄位數量)
2. Column Definitions (每個欄位的定義)
3. EOF Packet (欄位定義結束)
4. Rows (每一筆資料)
5. EOF Packet (資料結束)

📊 Result Set（結果集）

Text Protocol Result Set

完整流程：

SELECT id, name, email FROM users WHERE id < 3

Server 回應：

1. [Column Count Packet]
   column_count: 3

2. [Column Definition] × 3
   Column 1: id, INT, NOT NULL
   Column 2: name, VARCHAR(50), NOT NULL
   Column 3: email, VARCHAR(100), NULL

3. [EOF Packet]
   warnings: 0

4. [Row Data] × 2
   Row 1: "1", "Alice", "alice@example.com"
   Row 2: "2", "Bob", "bob@example.com"

5. [EOF Packet]
   status: SERVER_STATUS_AUTOCOMMIT

Column Definition Packet

包含：
1. catalog（目錄）: "def"
2. schema（資料庫）: "mydb"
3. table（表格）: "users"
4. org_table（原始表格）: "users"
5. name（欄位名稱）: "id"
6. org_name（原始欄位）: "id"
7. character_set: 63 (binary)
8. column_length: 11
9. type: MYSQL_TYPE_LONG (整數)
10. flags: NOT_NULL_FLAG
11. decimals: 0

🚀 Text Protocol vs Binary Protocol

Text Protocol（COM_QUERY）

特性：
✅ 簡單易用
✅ SQL 直接執行
❌ 所有資料都是字串
❌ 類型轉換開銷
❌ 較大的傳輸量

範例：
SELECT id, price FROM products

結果（全部字串）：
"1", "99.99"
"2", "49.50"

問題：
- 數字 99.99 傳輸為 5 bytes 字串
- 客戶端需要 parse 成 float

Binary Protocol（Prepared Statements）

特性：
✅ 類型安全（資料保留原始類型）
✅ 傳輸量小
✅ 效能更好
✅ 防止 SQL Injection
❌ 需要兩次往返（prepare + execute）

範例：
PREPARE stmt FROM "SELECT id, price FROM products WHERE id = ?"
EXECUTE stmt USING 1

結果（二進位）：
id: 0x01 (1 byte)
price: 0x42 0xC7 0xAE 0x14 (4 bytes float)

優點：
- 數字 99.99 只需 4 bytes
- 客戶端直接使用，無需轉換

效能對比

建議：

• 單次查詢 → Text Protocol
• 重複查詢 → Binary Protocol (Prepared Statements)

🏊 連線池（Connection Pool）

為什麼需要連線池？

問題：每次請求都建立新連線

用戶請求 → 建立連線（Handshake + Auth）→ 執行查詢 → 關閉連線
            ↑                                              ↑
          耗時 50-100ms                                 耗時 10ms

總時間：60-110ms
其中只有 10ms 在做真正的工作！

沒有連線池：

請求 1：建立連線（100ms）→ 查詢（10ms）→ 關閉
請求 2：建立連線（100ms）→ 查詢（10ms）→ 關閉
請求 3：建立連線（100ms）→ 查詢（10ms）→ 關閉
總時間：330ms

使用連線池：

初始化：建立 10 個連線（一次性成本）

請求 1：從池取連線 → 查詢（10ms）→ 歸還
請求 2：從池取連線 → 查詢（10ms）→ 歸還
請求 3：從池取連線 → 查詢（10ms）→ 歸還
總時間：30ms（省 90%！）

連線池原理

連線池架構：

┌─────────────────────────────────────┐
│         Connection Pool             │
│  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐  │
│  │Conn1│ │Conn2│ │Conn3│ │Conn4│  │ ← Min Connections
│  └─────┘ └─────┘ └─────┘ └─────┘  │
│  ┌─────┐ ┌─────┐                   │
│  │Conn5│ │Conn6│                   │ ← 動態增長
│  └─────┘ └─────┘                   │
│                                     │ ← Max Connections = 10
│  [閒置]   [使用中]                  │
└─────────────────────────────────────┘
         ↑            ↓
    歸還連線      借用連線
         ↑            ↓
      Application

連線池參數

# 典型的連線池設定
pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,           # 連線池大小
    pool_reset_session=True,# 重置 session 狀態
    host="localhost",
    database="mydb",
    user="root",
    password="password"
)

# 使用連線
connection = pool.get_connection()
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

# 重要：歸還連線
connection.close()  # 實際上是歸還到池，不是真的關閉

連線池最佳實踐

1. 設定合適的池大小

公式：
Pool Size = (Core Count × 2) + Disk Count

範例：
4 核心 CPU + 1 硬碟 = (4 × 2) + 1 = 9
建議：10 個連線

過小：請求等待
過大：資源浪費、MySQL 負擔重

2. 設定連線超時

pool = MySQLConnectionPool(
    pool_size=10,
    # 連線最大閒置時間（秒）
    pool_max_idle_time=3600,  # 1 小時

    # 取得連線的超時時間
    get_timeout=30,  # 30 秒

    # MySQL 連線超時
    connect_timeout=10  # 10 秒
)

3. 連線健康檢查

# 定期 ping 測試連線是否存活
def health_check():
    try:
        conn = pool.get_connection()
        conn.ping(reconnect=True)  # 如果斷開，自動重連
        conn.close()
        return True
    except:
        return False

# 每 5 分鐘檢查一次
schedule.every(5).minutes.do(health_check)

4. 正確處理連線歸還

# ❌ 錯誤：沒有歸還連線
def bad_query():
    conn = pool.get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    return cursor.fetchall()
    # 忘記 conn.close() → 連線洩漏！

# ✅ 正確：使用 try-finally
def good_query():
    conn = pool.get_connection()
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users")
        return cursor.fetchall()
    finally:
        conn.close()  # 確保歸還

# ✅ 更好：使用 context manager
def best_query():
    with pool.get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM users")
            return cursor.fetchall()
    # 自動歸還連線

🎓 面試常見問題

Q1：MySQL Protocol 是文字協定還是二進位協定？

A：MySQL Protocol 是二進位協定

特性對比：

MySQL Protocol（二進位）：
✅ 效能高
✅ 傳輸量小
✅ 類型安全
❌ 除錯困難

Redis Protocol（文字）：
✅ 人類可讀
✅ 除錯容易
❌ 傳輸量大
❌ 需要類型轉換

為什麼 MySQL 選擇二進位：
1. 資料庫查詢量大，效能很重要
2. 需要精確的資料類型（INT、FLOAT、DATE）
3. 傳輸效率更高

範例：
整數 1000：
- 二進位：2 bytes (0x03 0xE8)
- 文字："1000" → 4 bytes

float 99.99：
- 二進位：4 bytes
- 文字："99.99" → 5 bytes

Q2：MySQL 連線建立的完整流程是什麼？

A：4 個階段

1. TCP 連線建立
   客戶端 → Server:3306（三次握手）

2. Server Greeting（伺服器握手）
   Server → 客戶端：
   - Protocol Version: 10
   - Server Version: "8.0.35-MySQL"
   - Connection ID: 12345
   - Auth Plugin Data（加密種子）
   - Server Capabilities
   - Character Set

3. Login Request（登入請求）
   客戶端 → Server：
   - Username: "root"
   - Password: SHA1(SHA1(password) XOR SHA1(seed + SHA1(SHA1(password))))
   - Database: "mydb"
   - Client Capabilities

4. Auth Response（驗證回應）
   Server → 客戶端：
   成功：OK Packet
   失敗：ERR Packet (1045, Access denied)

完成後進入 Command Phase

時間消耗：
- TCP 握手：1 RTT (10-50ms)
- Handshake + Auth：1-2 RTT (10-100ms)
- 總計：20-150ms

這就是為什麼需要連線池！

Q3：Text Protocol 和 Binary Protocol 有什麼差異？

A：兩種查詢執行方式

Text Protocol（COM_QUERY）：
- 命令：COM_QUERY (0x03)
- 用法：直接執行 SQL 字串
- 結果：所有資料都是字串
- 優點：簡單、無需準備
- 缺點：類型轉換、傳輸量大

範例：
SELECT id, price FROM products
結果：
  id: "1" (字串)
  price: "99.99" (字串)

Binary Protocol（Prepared Statements）：
- 命令：COM_STMT_PREPARE + COM_STMT_EXECUTE
- 用法：先準備，再執行
- 結果：保留原始類型
- 優點：類型安全、傳輸小、防 SQL Injection
- 缺點：需要 2 次往返

範例：
PREPARE stmt FROM "SELECT id, price FROM products WHERE id = ?"
EXECUTE stmt USING 1
結果：
  id: 1 (二進位整數，1 byte)
  price: 99.99 (二進位 float，4 bytes)

何時使用：
- 單次查詢 → Text Protocol
- 重複查詢 → Binary Protocol
- 用戶輸入 → Binary Protocol（防 SQL Injection）

Q4：為什麼需要連線池？連線池如何優化效能？

A：建立連線成本高，連線池重複利用

成本分析：

建立 MySQL 連線需要：
1. TCP 三次握手：10-50ms
2. Server Greeting：10-30ms
3. Auth（密碼加密驗證）：10-50ms
4. 初始化 session 變數：5-20ms
總計：35-150ms

執行一個簡單查詢：
SELECT * FROM users WHERE id = 1
只需：1-10ms

問題：
連線建立時間 >> 查詢執行時間
浪費 90% 以上的時間在建立/關閉連線！

連線池解決方案：
1. 預先建立 N 個連線（一次性成本）
2. 請求來了，從池中借用
3. 用完歸還（不關閉）
4. 下次請求繼續使用

效能提升：
沒有連線池：每個請求 35-150ms
使用連線池：每個請求 1-10ms
提升：5-15 倍！

連線池參數：
pool_size = (CPU 核心數 × 2) + 硬碟數
範例：4 核 CPU → 9-10 個連線

注意：
❌ 連線洩漏：忘記歸還 → 池耗盡
✅ 使用 try-finally 或 with 確保歸還

Q5：MySQL 如何防止 SQL Injection？

A：使用 Prepared Statements（Binary Protocol）

SQL Injection 範例：

❌ 不安全（Text Protocol）：
user_input = "1 OR 1=1"
sql = f"SELECT * FROM users WHERE id = {user_input}"
→ SELECT * FROM users WHERE id = 1 OR 1=1
→ 回傳所有用戶！

更危險：
user_input = "1; DROP TABLE users; --"
sql = f"SELECT * FROM users WHERE id = {user_input}"
→ SELECT * FROM users WHERE id = 1; DROP TABLE users; --
→ 刪除整個表格！

✅ 安全（Prepared Statements）：
sql = "SELECT * FROM users WHERE id = ?"
cursor.execute(sql, (user_input,))

原理：
1. Prepare 階段：
   Server 解析 SQL 結構
   參數位置用 ? 佔位

2. Execute 階段：
   參數用 Binary Protocol 傳送
   Server 知道這是「資料」，不是「SQL 指令」

即使輸入：
user_input = "1 OR 1=1"

Server 會把它當成字串 "1 OR 1=1"，不會執行
WHERE id = "1 OR 1=1"
→ 找不到 id 為此值的記錄，安全！

額外好處：
✅ 防止 SQL Injection
✅ 效能更好（重複查詢）
✅ 類型安全

建議：
永遠使用 Prepared Statements 處理用戶輸入！

Q6：連線池常見的坑有哪些？如何避免？

A：5 個常見問題

1️⃣ 連線洩漏（Connection Leak）
問題：
def bad_query():
    conn = pool.get_connection()
    cursor.execute("SELECT ...")
    return cursor.fetchall()
    # 忘記 conn.close() → 連線永遠不歸還

結果：
pool_size = 10，洩漏 10 次後，池耗盡
後續請求永遠等待！

解決：
def good_query():
    conn = None
    try:
        conn = pool.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT ...")
        return cursor.fetchall()
    finally:
        if conn:
            conn.close()  # 確保歸還

2️⃣ 池大小設定不當
問題：
pool_size = 1000  ← 太大！
→ MySQL 負擔重，記憶體浪費

pool_size = 1  ← 太小！
→ 所有請求排隊，效能差

解決：
合理公式：(CPU 核心數 × 2) + 硬碟數
4 核心 → 9-10 個連線

3️⃣ 長時間不用，連線被 Server 關閉
問題：
MySQL 預設 wait_timeout = 8 小時
超過時間，Server 關閉連線
但客戶端不知道，下次使用會報錯

解決：
# 定期 ping
conn.ping(reconnect=True)

# 或設定 pool 參數
pool_reset_session=True
pool_recycle=3600  # 1 小時回收

4️⃣ 沒有設定超時
問題：
pool.get_connection()  # 如果池滿，永遠等待

解決：
pool.get_connection(timeout=30)  # 30 秒超時

5️⃣ 事務沒有 commit/rollback
問題：
conn = pool.get_connection()
cursor.execute("INSERT ...")
conn.close()  # 沒有 commit！
→ 資料沒寫入，連線歸還時仍在事務中

解決：
try:
    cursor.execute("INSERT ...")
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滾
finally:
    conn.close()

記憶口訣：「借必還，用必收」
借用（get）必歸還（close）
事務必提交（commit）或回滾（rollback）

💡 實戰建議

1. 使用 Wireshark 觀察 MySQL Protocol

1. 啟動 Wireshark
2. 篩選：tcp.port == 3306
3. 執行 MySQL 查詢
4. 查看封包內容

觀察重點：
- Server Greeting Packet
- Login Request Packet
- COM_QUERY (0x03)
- Result Set

2. 使用 tcpdump 抓包

# 抓取 MySQL 流量
sudo tcpdump -i any -s 0 -w mysql.pcap port 3306

# 用 Wireshark 打開分析
wireshark mysql.pcap

3. 連線池監控

import time
from mysql.connector import pooling

# 建立連線池
pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host="localhost",
    database="test",
    user="root",
    password="password"
)

# 監控連線池狀態
def monitor_pool():
    print(f"Pool size: {pool.pool_size}")
    print(f"Active connections: {pool._cnx_queue.qsize()}")

# 測試連線洩漏偵測
def test_leak():
    connections = []
    for i in range(10):
        try:
            conn = pool.get_connection(timeout=1)
            connections.append(conn)
            print(f"Got connection {i+1}")
        except Exception as e:
            print(f"Failed to get connection: {e}")
            break

test_leak()
monitor_pool()

# 清理
for conn in connections:
    conn.close()

✅ 重點回顧

MySQL Protocol 特性：

• 二進位協定（vs Redis 文字協定）
• 效能高、傳輸小、類型安全
• 除錯較困難（需要工具）

連線流程：

1. TCP 連線建立
2. Server Greeting（握手）
3. Login Request（驗證）
4. Command Phase（執行查詢）

兩種協定：

• Text Protocol：簡單、所有資料都是字串
• Binary Protocol：高效、類型安全、防 SQL Injection

連線池：

• 重複利用連線，省去建立/關閉開銷
• 效能提升 5-15 倍
• 注意連線洩漏、池大小設定

面試重點：

• ✅ MySQL 是二進位協定
• ✅ 連線建立流程（4 階段）
• ✅ Text vs Binary Protocol
• ✅ 連線池原理與優化
• ✅ Prepared Statements 防 SQL Injection
• ✅ 連線池常見問題（洩漏、超時、事務）

記憶口訣：

• 「握、驗、詢、關」= Server Greeting, Auth, Query, Quit
• 「借必還，用必收」= 連線池管理

上一篇： 04-3. WebSocket 實戰應用 下一篇： 05-2. PostgreSQL Protocol

相關文章：

• 05-3. Redis Protocol (RESP)
• 02-1. HTTP 基礎概念

最後更新：2025-01-15