MySQL 模式

MySQL 模式 是做什么的？

在处理 MySQL 或 MariaDB 模式设计、迁移、慢查询调查、队列式事务、连接池或生产数据库配置时，请使用此技能。在应用特定功能模式之前，首选精确检查版本，因为 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 仅适用于队列式工作。它会跳过锁定的行并可能返回不一致的视图，因此不要将其用于一般的会计或完整性敏感的读取。

模式默认设置

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)。

键集分页

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 特有的模式和查询模式
• 技能：database-migrations - 迁移规划和发布安全性
• 技能：backend-patterns - API 和服务层模式
• 技能：security-review - 秘密处理、身份验证和最小权限
• Agent：database-reviewer - 更广泛的数据库审查工作流程