mysql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

MySQL

Use this skill to make safe, measurable MySQL/InnoDB changes.

使用本技能可安全、可衡量地对MySQL/InnoDB进行变更。

Workflow

工作流

Define workload and constraints (read/write mix, latency target, data volume, MySQL version, hosting platform).
Read only the relevant reference files linked in each section below.
Propose the smallest change that can solve the problem, including trade-offs.
Validate with evidence (
```
EXPLAIN
```
,
```
EXPLAIN ANALYZE
```
, lock/connection metrics, and production-safe rollout steps).
For production changes, include rollback and post-deploy verification.

定义工作负载和约束条件（读写比例、延迟目标、数据量、MySQL版本、托管平台）。
仅阅读以下各部分中链接的相关参考文件。
提出可解决问题的最小改动方案，包括权衡取舍。
用证据验证（
```
EXPLAIN
```
、
```
EXPLAIN ANALYZE
```
、锁/连接指标，以及生产环境安全的发布步骤）。
对于生产环境的改动，需包含回滚方案和部署后验证步骤。

Schema Design

Schema设计

Prefer narrow, monotonic PKs (
```
BIGINT UNSIGNED AUTO_INCREMENT
```
) for write-heavy OLTP tables.
Avoid random UUID values as clustered PKs; if external IDs are required, keep UUID in a secondary unique column.

Always

utf8mb4

utf8mb4_0900_ai_ci

. Prefer

NOT NULL

DATETIME

over

TIMESTAMP

Lookup tables over
```
ENUM
```
. Normalize to 3NF; denormalize only for measured hot paths.

References:

对于写密集型OLTP表，优先选择窄的、单调递增的主键（
```
BIGINT UNSIGNED AUTO_INCREMENT
```
）。
避免使用随机UUID作为聚簇主键；如果需要外部ID，将UUID存储在二级唯一列中。
始终使用
```
utf8mb4
```
/
```
utf8mb4_0900_ai_ci
```
字符集。优先使用
```
NOT NULL
```
，选择
```
DATETIME
```
而非
```
TIMESTAMP
```
。
使用查找表替代
```
ENUM
```
。规范化到3NF；仅针对已验证的热路径进行反规范化。

参考资料：

Indexing

索引设计

Composite order: equality first, then range/sort (leftmost prefix rule).
Range predicates stop index usage for subsequent columns.
Secondary indexes include PK implicitly. Prefix indexes for long strings.
Audit via
```
performance_schema
```
— drop indexes with
```
count_read = 0
```
.

References:

复合索引顺序：先等值条件，再范围/排序（最左前缀规则）。
范围谓词会终止后续列的索引使用。
二级索引会隐式包含主键。对长字符串使用前缀索引。
通过
```
performance_schema
```
审计索引 — 删除
```
count_read = 0
```
的索引。

参考资料：

Partitioning

分区设计

Partition time-series (>50M rows) or large tables (>100M rows). Plan early — retrofit = full rebuild.
Include partition column in every unique/PK. Always add a
```
MAXVALUE
```
catch-all.

References:

partitioning

对时间序列表（>5000万行）或大表（>1亿行）进行分区。尽早规划 — 后期改造需要全量重建。
分区列需包含在每个唯一键/主键中。始终添加
```
MAXVALUE
```
兜底分区。

参考资料：

partitioning

Query Optimization

查询优化

Check

EXPLAIN

— red flags:

type: ALL

Using filesort

Using temporary

Cursor pagination, not
```
OFFSET
```
. Avoid functions on indexed columns in
```
WHERE
```
.
Batch inserts (500–5000 rows).
```
UNION ALL
```
over
```
UNION
```
when dedup unnecessary.

References:

检查

EXPLAIN

结果 — 危险信号：

type: ALL

、

Using filesort

、

Using temporary

。

使用游标分页，而非
```
OFFSET
```
。避免在
```
WHERE
```
子句中对索引列使用函数。
批量插入（500–5000行）。当不需要去重时，使用
```
UNION ALL
```
而非
```
UNION
```
。

参考资料：

Transactions & Locking

事务与锁

Default:
```
REPEATABLE READ
```
(gap locks). Use
```
READ COMMITTED
```
for high contention.
Consistent row access order prevents deadlocks. Retry error 1213 with backoff.
Do I/O outside transactions. Use
```
SELECT ... FOR UPDATE
```
sparingly.

References:

默认隔离级别：
```
REPEATABLE READ
```
（间隙锁）。在高并发场景下使用
```
READ COMMITTED
```
。
一致的行访问顺序可防止死锁。遇到1213错误时，带退避策略重试。
在事务外执行I/O操作。谨慎使用
```
SELECT ... FOR UPDATE
```
。

参考资料：

Operations

运维操作

Use online DDL (
```
ALGORITHM=INPLACE
```
) when possible; test on replicas first.
Tune connection pooling — avoid
```
max_connections
```
exhaustion under load.
Monitor replication lag; avoid stale reads from replicas during writes.

References:

尽可能使用在线DDL（
```
ALGORITHM=INPLACE
```
）；先在副本上测试。
调优连接池 — 避免负载下
```
max_connections
```
耗尽。
监控复制延迟；在写入期间避免从副本读取过期数据。

参考资料：

Guardrails

防护准则

Prefer measured evidence over blanket rules of thumb.
Note MySQL-version-specific behavior when giving advice.
Ask for explicit human approval before destructive data operations (drops/deletes/truncates).

优先基于实测证据，而非通用经验法则。
给出建议时需注意MySQL版本特定的行为。
在执行破坏性数据操作（删除/清空/截断）前，需明确获得人工批准。