mysql-patterns

MySQL 模式

當您處理 MySQL 或 MariaDB 的 Schema 設計、遷移、慢查詢調查、佇列式交易、連線池或生產資料庫配置時，請使用此技能。在套用特定功能模式前，建議先確認確切的版本，因為 MySQL 和 MariaDB 在多個 SQL 細節上已有所分歧。

啟用時機

• 設計 MySQL 或 MariaDB 的資料表、索引和約束
• 在大型生產資料表上執行遷移前進行審查
• 除錯慢查詢、鎖等待、死結或連線耗盡問題
• 新增 keyset 分頁、upsert、全文檢索、JSON 欄位或佇列
• 配置應用程式連線池、讀取複本、TLS 或慢查詢日誌

版本檢查

開始前先確認引擎和版本：

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

當語法不同時，請區分 MySQL 和 MariaDB 的指引：

• MySQL 文件指出在 ON DUPLICATE KEY UPDATE 中，列別名是 VALUES(col) 的替代方案；VALUES(col) 在 MySQL 中已棄用。
• MariaDB 文件指出 VALUES(col) 是在 ON DUPLICATE KEY UPDATE 中引用插入值的支援方式；為求跨引擎相容性，請使用它。
• SKIP LOCKED 僅適用於類似佇列的工作。它會跳過鎖定的行，並可能傳回不一致的檢視，因此請勿將其用於一般的會計或完整性敏感的讀取。

Schema 預設值

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

預設選擇：

使用情境	偏好	避免
代理主鍵	BIGINT UNSIGNED AUTO_INCREMENT	對於可能成長超過 20 億行的資料表使用 INT
UUID 查詢鍵	BINARY(16) 搭配轉換輔助函式	在熱門資料表上使用 VARCHAR(36) 主鍵
貨幣和精確數量	DECIMAL(p, s)	FLOAT 或 DOUBLE
使用者面對的文字	utf8mb4 資料表和索引	MySQL 的 utf8 / utf8mb3 預設值
應用程式時間戳記	DATETIME 搭配應用程式管理的 UTC	假設 DATETIME 儲存時區中繼資料
軟刪除	deleted_at DATETIME NULL 加上限定範圍的索引	在沒有索引的情況下過濾軟刪除的行
可擴展的狀態值	查詢表或受限的 VARCHAR	當值經常變更時使用 ENUM

索引

複合索引的順序通常先放相等條件，再放範圍或排序欄位：

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

在新增或變更索引前使用 EXPLAIN：

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

需要調查的信號：

欄位	風險信號
type	在大型資料表上為 ALL
key	當存在選擇性條件時為 NULL
rows	對於互動式路徑而言，資料列估計值非常高
Extra	Using temporary、Using filesort 或廣泛的 Using where

避免盲目新增索引。每個索引都會增加寫入成本、遷移時間、備份大小和緩衝池壓力。

查詢模式

Upsert

跨引擎相容的形式：

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

MySQL 列別名形式：

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

僅在確認目標為 MySQL 後再使用列別名形式。對於 MariaDB 或混合 MySQL/MariaDB 的環境，請使用 VALUES(col)。

Keyset 分頁

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

搭配一個符合游標的索引：

CREATE INDEX idx_products_created_id ON products (created_at, id);

不要在大型資料表上使用深層 OFFSET 分頁；它會使伺服器掃描並丟棄許多資料列後才回傳頁面。

JSON 欄位

將 JSON 欄位用於擴展資料，而不是用於需要大量關聯式過濾或約束的欄位。

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

對於經常查詢的 JSON 路徑，請產生一個產生的欄位並為該欄位建立索引。保持外鍵、所有權、租戶和生命週期欄位為關聯式。

全文檢索

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

當您需要容錯、複雜排序、跨表 facets 或超越內建全文功能的特定語言分析時，請使用外部搜尋。

交易

保持交易簡短，並以一致的順序鎖定資料列：

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

死結和鎖等待檢查清單：

• 在整個程式碼路徑中以確定性的順序鎖定資料列。
• 在開啟交易之前執行外部 API 呼叫，而不是在交易內部。
• 為 UPDATE、DELETE 和鎖定讀取中使用的條件建立索引。
• 發生死結時，回滾並以有限的重試預算重試整個交易。
• 在死結發生後立即擷取 SHOW ENGINE INNODB STATUS\G；它會被後續事件覆蓋。

佇列式 Worker 宣告：

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

僅對跳過鎖定資料列是可接受的佇列型工作負載使用 SKIP LOCKED。它不能替代正常的交易一致性。

連線池

SQLAlchemy 範例：

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:[email protected]/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Node.js mysql2 範例：

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

保持應用程式連線池的回收時間低於伺服器的 wait_timeout。如果伺服器使用 wait_timeout = 300，則 pool_recycle 設為 240 秒左右是合理的；pool_pre_ping 仍然有助於從網路和容錯移轉事件中恢復。

診斷

有用的初步指令：

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

在受控環境中啟用慢查詢日誌：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

僅在安全執行查詢時才使用 EXPLAIN ANALYZE。它會執行該陳述式，並且在生產規模的資料上可能成本高昂。

複寫

讀取複本可能會延遲。請勿在寫入後立即將讀取自己寫入、結帳流程、權限檢查或冪等性鍵讀取路由到複本。

-- MySQL 舊版術語，在現有環境中仍然常見
SHOW SLAVE STATUS\G;

-- 支援的較新術語
SHOW REPLICA STATUS\G;

在標準化使用一個指令之前，請檢查引擎/版本。監控複本 SQL 執行緒健康狀態、IO 執行緒健康狀態和延遲，而不僅僅是 TCP 連線是否存活。

安全性

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

安全審查要點：

• 不要對應用程式使用者授予 ALL PRIVILEGES 或 *.*。
• 當流量跨越主機或網路時，要求應用程式使用者使用 TLS。
• 將憑證存放在平台機密管理器中，而不是放在範例、腳本或儲存庫檔案中。
• 將遷移/管理使用者與執行階段應用程式使用者分開。
• 在調整效能之前，先稽核公開網路暴露和繫結位址。

配置

專用資料庫主機的起點範例：

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

將配置值視為審查的提示，而非通用的預設值。根據工作負載、硬體、備份策略和復原目標來設定記憶體、連線、日誌保留和持久性設定的大小。

反模式

反模式	風險	較佳模式
在熱門路徑中使用 SELECT *	過度擷取和脆弱的客戶端	明確選擇欄位
深層 OFFSET 分頁	線性掃描和緩慢的頁面	Keyset 分頁
外鍵關聯上沒有索引	緩慢的關聯和鎖密集的刪除	有意圖地為 FK 欄位建立索引
長時間交易	鎖等待和大量的復原歷史	提交小單位的工作
直接對 mysql.user 執行 DML	授權表損毀風險	使用 CREATE USER、ALTER USER、DROP USER
應用程式使用者具有管理員權限	高爆炸半徑	最小權限的執行階段使用者
連線池回收時間高於 wait_timeout	陳舊的池化連線	在 timeout 以下回收並預先 ping
寫入後讀取複本	陳舊的使用者面向狀態	將讀取自己寫入的流程固定到主庫

輸出期望

當此技能用於審查時，請回傳：

1. 引擎/版本假設。
2. 最高風險的正確性、鎖定、安全性和遷移問題。
3. 安全路徑的確切 SQL 或程式碼變更。
4. 驗證計畫：EXPLAIN、遷移空跑、鎖定/死結檢查和回滾標準。
5. 任何會影響建議的 MySQL/MariaDB 語法差異。

相關

• 技能：postgres-patterns - PostgreSQL 特有的 Schema 和查詢模式
• 技能：database-migrations - 遷移規劃和上線安全性
• 技能：backend-patterns - API 和服務層模式
• 技能：security-review - 機密處理、認證和最小權限
• 代理：database-reviewer - 更廣泛的資料庫審查工作流程