mysql-patterns

MySQL 设计模式与最佳实践

当你在设计 MySQL 或 MariaDB 的表结构与索引、编写数据库变更脚本（Migrations）、排查慢查询与死锁、实现高并发队列事务、优化连接池，或是配置生产环境数据库参数时，请使用本 Skill。在应用特定版本的语法或特性之前，请先确认数据库的具体版本，因为 MySQL 与 MariaDB 在部分 SQL 细节上已存在差异。

触发场景

• 设计 MySQL 或 MariaDB 的数据表、索引与约束
• 在大表上上线 Migration 变更前进行 Code Review
• 排查慢查询（Slow Query）、锁等待、死锁或连接池耗尽（Connection Exhaustion）等问题
• 实现深度分页优化（Keyset Pagination）、Upsert（存在即更新）、全文检索、JSON 列或任务队列
• 配置应用端连接池、只读副本（Read Replicas）、TLS 组网或慢查询日志

版本确认

首先确认数据库引擎与具体的版本信息：

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

当语法存在差异时，请区分 MySQL 和 MariaDB 的处理方式：

• 在 ON DUPLICATE KEY UPDATE 语句中，MySQL 推荐使用行别名（Row Aliases）来替代已废弃的 VALUES(col)。
• MariaDB 官方仍保留 VALUES(col) 作为引用插入值的标准写法，如果需要兼容两种引擎，请优先使用 VALUES(col)。
• 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	存在高选择性条件但 key 为 NULL
rows	核心在线接口的预估扫描行数过高
Extra	包含 Using temporary、Using filesort 或范围过大的 Using where

切勿盲目创建索引。每一个索引都会增加写入开销、延长 Migration 时间、增大备份体积并挤占 Buffer Pool 内存。

常见查询模式

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;

配套创建支持 Cursor 的复合索引：

CREATE INDEX idx_products_created_id ON products (created_at, id);

在大表上避免使用大偏移量的 OFFSET 分页（如 LIMIT 100000, 50），这会导致数据库扫描并丢弃大量无用行。

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 路径，可以创建虚拟生成列（Generated Column）并建立索引。外键、归属权、租户隔离与生命周期状态等字段应继续保持关系化设计。

全文检索（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;

当业务需要拼写容错、复杂相关性打分、跨表聚合筛选或特定分词算法时，应引入外部搜索引擎（如 Elasticsearch），而非依赖内置全文检索。

事务与锁控制

控制事务耗时，并在涉及多行锁定时保持一致的加锁顺序：

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:secret@db.internal/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],
);

请确保客户端连接池的回收时间（pool_recycle）小于服务端的 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';

仅在允许真实执行该 SQL 的场景下使用 EXPLAIN ANALYZE。因为它会实际运行语句，在生产环境大数据量上开销较大。

主从复制与读写分离

只读副本存在主从延迟风险。写后即读（Read-Your-Own-Write）流程、结算下单、权限校验及幂等键读取等关键业务路径，切勿在写入后立即路由至只读副本。

-- 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 加密。
• 数据库凭据应统一存储在平台的密钥管理系统（Secret Manager）中，禁止硬编码在代码、脚本或 Git 仓库里。
• 将数据库 Migration/管理员账号与日常运行的应用服务账号严格分离。
• 在调优数据库性能前，优先审计公网暴露情况与绑定监听地址。

参数配置

独立数据库服务器的推荐初始配置：

[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

配置参数应作为调整参考而非通用万能模板。内存大小、最大连接数、日志保留策略与持久化级别需结合实际业务负载、硬件配置、备份机制与 RTO/RPO 恢复目标综合评估。

反模式（Anti-Patterns）

典型反模式	潜在风险	推荐替代方案
高频接口中使用 SELECT *	数据传输冗余、客户端脆弱	显式声明所需查询字段
深度 OFFSET 分页	导致线性全表扫描、页面响应变慢	采用 Keysets 游标分页
外键关联列未建索引	关联查询缓慢、引发大范围锁及锁删除	为外键列建立专门索引
事务长时间不提交	导致锁等待与 Undo Log 膨胀	将大事务拆分为小批次提交
直接用 DML 修改 mysql.user	可能破坏权限表底层结构	统一使用 CREATE/ALTER/DROP USER 语句
给应用层账号赋予管理员权限	安全爆炸半径过大	遵循最小权限原则配置运行时账号
连接池 pool_recycle 大于 wait_timeout	产生断开的无效连接	回收策略小于超时时间，并开启 pre-ping 探活
写完数据后立即从读库查询	产生读延迟导致的数据不一致	强一致写后即读流程锁定主库查询

输出规范

使用本 Skill 进行 Code Review 或方案评估时，请给出：

1. 引擎与版本前提假设。
2. 最高风险的数据正确性、死锁/锁等待、安全及数据库变更隐患。
3. 针对安全路线给出的具体 SQL 或代码修改方案。
4. 验证与上线计划：EXPLAIN 执行计划、Migration 预演测试、锁/死锁排查及回滚标准。
5. 涉及影响建议方案的 MySQL / MariaDB 语法细节差异说明。

相关关联

• Skill: postgres-patterns - PostgreSQL 架构设计与查询优化模式
• Skill: database-migrations - 数据库变更规划与平滑上线安全指南
• Skill: backend-patterns - API 接口与服务层架构模式
• Skill: security-review - 密钥管理、身份认证与最小权限原则

<!-- truncated for translation batch; full body continues in source -->