$ loading_
提供面向生产后端的 MySQL 与 MariaDB 设计、查询与运维模式建议。
复制安装指令，让 AI 自动完成配置 · 推荐新手
请帮我安装 askskill 上的 "mysql-patterns" 技能： 1. 下载 https://raw.githubusercontent.com/affaan-m/ECC/main/skills/mysql-patterns/SKILL.md 2. 保存为 ~/.claude/skills/mysql-patterns/SKILL.md 3. 装好后重载技能，告诉我可以用了
请为订单系统设计一套 MySQL 表结构，包含 users、orders、order_items 三张表。要求支持高并发写入、常见按用户和时间范围查询，并给出主键、索引、分库分表前的优化建议与注意事项。
输出结构化的表设计方案，附字段建议、索引策略及性能优化说明。
下面是一条慢查询，请分析性能瓶颈并给出优化方案：SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 50。请说明是否需要联合索引、覆盖索引或改写 SQL。
输出慢查询原因分析，并提供可落地的索引与 SQL 优化建议。
请为 MySQL 生产环境制定主从复制方案，要求包含读写分离、故障切换、复制延迟监控、连接池配置建议，以及常见事务一致性风险的处理方式。
输出一套生产可用的复制与连接管理实践清单及架构建议。
Use this skill when working on MySQL or MariaDB schema design, migrations, slow-query investigation, queue-style transactions, connection pools, or production database configuration. Prefer exact version checks before applying a feature-specific pattern because MySQL and MariaDB have diverged in several SQL details.
Start by identifying the engine and version:
SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';
Keep MySQL and MariaDB guidance separate when syntax differs:
VALUES(col) in
ON DUPLICATE KEY UPDATE; VALUES(col) is deprecated there.VALUES(col) as the supported way to reference inserted
values in ON DUPLICATE KEY UPDATE; use it for cross-engine compatibility.SKIP LOCKED is appropriate for queue-like work only. It skips locked rows
and can return an inconsistent view, so do not use it for general accounting
or integrity-sensitive reads.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;
Default choices:
| Use Case | Prefer | Avoid |
|---|---|---|
| Surrogate primary keys | BIGINT UNSIGNED AUTO_INCREMENT | INT for tables that can grow beyond 2B rows |
| UUID lookup keys | BINARY(16) with conversion helpers | VARCHAR(36) primary keys on hot tables |
| Money and exact quantities | DECIMAL(p, s) | FLOAT or DOUBLE |
| User-facing text | utf8mb4 tables and indexes | MySQL utf8 / utf8mb3 defaults |
| Application timestamps | DATETIME with UTC managed by the app | Assuming DATETIME stores time zone metadata |
| Soft deletes | deleted_at DATETIME NULL plus scoped indexes | Filtering soft-deleted rows without an index |
| Extensible status values | lookup table or constrained VARCHAR | ENUM when values change often |
Composite index order usually follows equality predicates first, then range or sort columns:
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;
Use EXPLAIN before adding or changing an index:
EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;
Signals to investigate:
| Field | Risk Signal |
|---|---|
type | ALL on a large table |
key | NULL when a selective predicate exists |
rows | Very high row estimate for an interactive path |
Extra | Using temporary, Using filesort, or broad Using where |
Avoid adding indexes blindly. Each index increases write cost, migration time, backup size, and buffer-pool pressure.
Cross-engine-compatible form:
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 row-alias form:
…
通过双评审智能体对结果进行对抗式校验，提升输出发布前的可靠性
帮助用户获取常见 MySQL 设计与查询模式，用于开发、优化与排错。