AI Skills
mysql-patterns

mysql-patterns

熱門

适用于线上生产环境后端的 MySQL 与 MariaDB 资料表结构设计（Schema）、查询优化、索引策略、事务交易（Transaction）、数据库复制（Replication）及连线池（Connection Pool）最佳实践模式。

24萬星標
3.6萬分支
更新於 2026/7/29
獲取技能源代碼

相關技能

api-security-best-practices4.3萬security-audit7萬exploratory-data-analysis3.8萬
SKILL.md
唯讀
名稱
mysql-patterns
描述

适用于线上生产环境后端的 MySQL 与 MariaDB 资料表结构设计（Schema）、查询优化、索引策略、事务交易（Transaction）、数据库复制（Replication）及连线池（Connection Pool）最佳实践模式。

MySQL Patterns

在处理 MySQL 或 MariaDB 的 Schema 设计、资料库迁移（Migration）、慢查询（Slow Query）排查、队列型交易、连线池或生产环境资料库设定时，请使用此 Skill。在应用特定版本的语法或功能前，建议先确认准确的资料库引擎版本，因为 MySQL 与 MariaDB 在许多 SQL 语法细节上已有分化。

时机与场景（Activation）

  • 设计 MySQL 或 MariaDB 的资料表、索引与约束条件（Constraints）
  • 在大型生产环境资料表上执行 Migration 前进行 Code Review
  • 调试慢查询、锁等待（Lock Wait）、死锁（Deadlock）或连线耗尽问题
  • 引入 Keyset 分页、Upsert、全文搜寻（Full-Text Search）、JSON 栏位或队列
  • 设定应用程式连线池、读取副本（Read Replicas）、TLS 传输或慢查询日志

版本确认（Version Check）

请先确认使用的资料库引擎与具体版本：

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

当语法存在差异时，请区分 MySQL 与 MariaDB 的指引：

  • 在 ON DUPLICATE KEY UPDATE 语法中，MySQL 已将 VALUES(col) 标示为弃用（Deprecated），官方推荐改用资料列别名（Row Aliases）。
  • MariaDB 官方仍保留 VALUES(col) 作为引用插入值的标准做法；若需兼顾跨引擎相容性，请使用此写法。
  • SKIP LOCKED 仅适用于队列型任务。它会跳过被锁定的资料列，并可能返回不一致的快照资料，因此切勿将其用于一般会计统计或对资料完整性敏感的读取场景。

Schema 默认设计模式（Schema Defaults）

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;

预设选择建议：

应用场景 推荐做法 应避免做法
代理主键（Surrogate PK） BIGINT UNSIGNED AUTO_INCREMENT 在预估会突破 20 亿笔资料的表上使用 INT
UUID 查找主键 搭配转换工具函式的 BINARY(16) 在高频存取的资料表上使用 VARCHAR(36) 作为主键
金额与精确数值 DECIMAL(p, s) FLOAT 或 DOUBLE
面向用户的文字资料 utf8mb4 资料表与索引 使用 MySQL 预设的 utf8 / utf8mb3
应用程式时间戳记 由应用程式统一维护 UTC 的 DATETIME 误以为 DATETIME 会自动储存时区中继资料
软删除（Soft Deletes） deleted_at DATETIME NULL 并建立涵盖此栏位的索引 未使用索引直接过滤软删除资料列
可扩展的状态值 关联查找表（Lookup Table）或有约束的 VARCHAR 在状态值频繁变动时使用 ENUM

索引模式（Indexing）

复合索引的栏位顺序通常遵循：等值条件（Equality）优先，其次为范围条件（Range）或排序栏位（Sort）：

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

切勿盲目添加索引。每个索引都会增加写入开销、 Migration 执行时间、备份大小以及 Buffer Pool 缓冲池的压力。

查询模式（Query Patterns）

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 游标分页（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 路径，可以建立生成栏位（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;

如果业务需要容错拼写（Typo Tolerance）、复杂排序算法、跨表多维筛选（Facets）或特定语言分析，超出了内置全文检索的功能范畴，请使用外部搜索引擎。

事务与交易（Transactions）

保持事务简短，并以固定一致的顺序对资料列加锁：

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 呼叫移至开启事务之前，切勿在事务内部发起外部 HTTP 呼叫。
  • 确保 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。它无法替代正常的事务一致性保证。

连线池（Connection Pools）

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],
);

确保应用程式连线池的回收周期（Recycle Time）低于数据库伺服器的 wait_timeout。若伺服器使用 wait_timeout = 300，将 pool_recycle 设定在 240 秒左右较为合理；开启 pool_pre_ping 有助于从网络中断或故障转移（Failover）事件中自我修复。

诊断工具（Diagnostics）

常用排查指令：

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，因为它会真正运行查询语句，在生产大表上可能开销极大。

主从复制（Replication）

读取副本（Read Replicas）可能会有同步延迟。写后即读（Read-your-own-write）、结帐流程、权限校验或幂等键（Idempotency Key）读取等关键路径，切勿在写入后立即路由至读取副本。

-- MySQL 旧版术语，在现有环境仍很常见
SHOW SLAVE STATUS\G;

-- 新版支持的术语
SHOW REPLICA STATUS\G;

在统一规范使用指令前，请先确认资料库引擎与版本。应持续监控副本的 SQL 线程健康度、IO 线程健康度以及同步延迟（Lag），而非仅关注 TCP 连线是否正常。

安全性（Security）

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 中，绝不要出现在范例代码、脚本或 Repo 版本库控制档案中。
  • 明确隔离 Migration/管理账号与运行时应用程式账号。
  • 在调整性能参数前，先审计公开网络暴露状况与 Bind 监听地址。

配置设置（Configuration）

专用的资料库主机配置参考起点：

[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 分页 线性扫描且页面响应缓慢 使用 Keyset 游标分页
外键关联关联栏位未建立索引 Join 效能差且删除时引发大范围锁等待 针对外键栏位有意识地建立索引
长时间运行的事务 产生锁等待与庞大的 Undo 历史版本 将大任务拆解为小单位批次 Commit
直接对 mysql.user 执行 DML 可能导致 Grant 权限表损坏风险 统一使用 CREATE USER、ALTER USER、DROP USER
应用程式使用者拥有 Admin 权限 发生风险时影响范围（Blast Radius）过大 遵循最小权限原则配置运行时账号
连线池 Recycle 设定高于 wait_timeout 容易拿到废弃失效的数据库连线 将 Recycle 设在 Timeout 之下并启用 Pre-ping
写完后立即从 Replica 节点读取 面向使用者的状态呈现陈旧过时 将“写后即读”的业务流程固定指向主库（Primary）

输出规范（Output Expectations）

当此 Skill 用于审查时，请提供：

  1. 引擎与版本的先决假设。
  2. 风险最高的数据正确性、锁机制、安全性及 Migration 问题。
  3. 针对安全修复路径的精确 SQL 或程式码变动。
  4. 验证计划：EXPLAIN、Migration 模拟运行（Dry Run）、锁/死锁检查及回滚标准。
  5. 任何影响最终建议的 MySQL/MariaDB 语法差异说明。

相关关联（Related）

  • Skill: postgres-patterns - PostgreSQL 专属的 Schema 与查询模式
  • Skill: database-migrations - 资料库 Migration 规划与上线安全
  • Skill: backend-patterns - API 与 Service 层设计模式
  • Skill: security-review - 凭证密钥管理、身份验证与最小权限原则

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