关于
MySQL 和 MariaDB 的 Schema、查询、索引、事务、复制和连接池模式，适用于生产后端
name: mysql-patterns description: MySQL 和 MariaDB 的模式设计、查询、索引、事务、复制和连接池模式，适用于生产后端。 origin: ECC
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)在 MySQL 中已弃用。 - 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 列存储扩展数据，而非需要大量关系过滤的字段。
