MySQL (MariaDB) 模式

MySQL 模式

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

啟用時機

• 設計 MySQL 或 MariaDB 的資料表、索引與限制條件
• 在大型生產資料表上執行遷移前進行審查
• 除錯慢查詢、鎖定等待、死結或連線耗盡問題
• 新增鍵集分頁、不存在則插入、全文檢索、JSON 欄位或佇列功能
• 配置應用程式的連線池、讀取複本、TLS 或慢查詢日誌

版本檢查

首先，確認引擎與版本：

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

當語法不同時，請將 MySQL 與 MariaDB 的指引分開處理：

• MySQL 文檔將行別名 (row aliases) 作為 ON DUPLICATE KEY UPDATE 中 VALUES(col) 的替代方案；VALUES(col) 在該處已被棄用。
• MariaDB 文檔則說明 VALUES(col) 是在 ON DUPLICATE KEY UPDATE 中參考插入值的支援方式；為求跨引擎相容性，請使用此方法。
• SKIP LOCKED 僅適用於佇列類型的作業。它會跳過被鎖定的行，並可能返回不一致的檢視，因此請勿將其用於一般的會計或資料完整性敏感的讀取操作。

綱要預設值

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	INT (對於可能成長超過 20 億行的資料表)
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 Pagination)

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 路徑，請建立一個生成欄位並為其建立索引。保持外鍵、所有權、多租戶和生命週期欄位的關聯式特性。

全文檢索 (Full-Text Search)

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;

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

交易

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

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；它可能會被後續事件覆蓋。

佇列類型的工作者認領：

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 分頁	線性掃描和緩慢的頁面	鍵集分頁
外鍵連接沒有索引	慢速連接和鎖定繁重的刪除操作	有目的地為 FK 欄位建立索引
長時間交易	鎖定等待和大量的撤銷歷史	提交小單位的工作
直接對 mysql.user 執行 DML	授權表損毀風險	使用 CREATE USER、ALTER USER、DROP USER
應用程式使用者擁有管理員權限	高爆炸半徑	最小權限的執行時期使用者
連線池回收時間高於 wait_timeout	過時的池化連線	回收時間低於逾時並加上 pre-ping
寫入後立即從複本讀取	過時的使用者面向狀態	將寫入後讀取流程固定到主庫

輸出預期

當使用此技能進行審查時，請返回：

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

相關技能

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