mysql-patterns

MySQL 模式

在涉及 MySQL 或 MariaDB schema 设计、迁移、慢查询排查、队列式事务、连接池或生产数据库配置时，使用本技能。由于 MySQL 和 MariaDB 在若干 SQL 细节上已出现分歧，建议在应用特定功能模式前，先精确检查数据库版本。

激活条件

• 设计 MySQL 或 MariaDB 的表、索引和约束
• 审查将要在大型生产表上运行的迁移
• 调试慢查询、锁等待、死锁或连接耗尽问题
• 添加键集分页、Upsert、全文搜索、JSON 列或队列
• 配置应用连接池、只读副本、TLS 或慢日志

版本检查

首先识别引擎和版本：

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

当语法不同时，请将 MySQL 和 MariaDB 的指导分开记录：

• MySQL 文档将行别名为 ON DUPLICATE KEY UPDATE 中 VALUES(col) 的替代方案；VALUES(col) 已弃用。
• 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
应用时间戳	使用 UTC 并由应用管理的 DATETIME	假设 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)。

键集分页

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;

当你需要容错、复杂排名、跨表分面或超出内置全文行为语言特定分析时，使用外部搜索。

事务

保持事务简短，并以一致的顺序锁定行：

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 分页	线性扫描和缓慢的页面	键集分页
外键连接上无索引	连接速度慢且删除时锁重	有意索引外键列
长事务	锁等待和大量的撤销历史	提交小工作单元
直接对 mysql.user 执行 DML	授权表损坏风险	使用 CREATE USER、ALTER USER、DROP USER
具有管理员权限的应用用户	高爆炸半径	最小权限运行时用户
回收时间高于 wait_timeout	过时的池化连接	在超时以下回收并预检
写后复制读取	过时的用户可见状态	将写后立即读的流程固定到主库

输出期望

当使用本技能进行审查时，返回：

1. 引擎/版本假设。
2. 最严重的正确性、锁、安全和迁移问题。
3. 安全路径的确切 SQL 或代码更改。
4. 验证计划：EXPLAIN、迁移预演、锁/死锁检查和回滚标准。
5. 任何影响建议的 MySQL/MariaDB 语法差异。

相关

• 技能：postgres-patterns - PostgreSQL 特定的 schema 和查询模式
• 技能：database-migrations - 迁移规划和上线安全
• 技能：backend-patterns - API 和服务层模式
• 技能：security-review - 密钥处理、认证和最小权限
• 代理：database-reviewer - 更广泛的数据库审查工作流