Mysql

mysql

Use this skill for MySQL 数据库设计、性能与运维（DBA）相关任务。

Defaults / assumptions to confirm

• MySQL version (5.7 / 8.0)
• Storage engine (InnoDB)
• Character set/collation (utf8mb4)
• Replication topology (single-primary, multi-source, etc.)

Workflow

1. Understand workload and constraints

• Read/write ratio, QPS, data size growth.
• Latency SLO, peak patterns, critical tables/queries.

2. Schema review

• Primary key strategy (avoid random PK for hot inserts unless needed; be explicit).
• Data types: avoid oversized VARCHAR/TEXT; use proper INT/BIGINT/DECIMAL.
• Nullability and defaults aligned with business meaning.
• Avoid over-normalization that forces heavy joins on critical paths.

3. Index design

• Add indexes for query patterns (WHERE, JOIN, ORDER BY, GROUP BY).
• Prefer composite indexes that match left-prefix usage.
• Avoid redundant indexes; keep write amplification in mind.
• Unique constraints where required; ensure consistent naming.

4. Query tuning

• Use EXPLAIN / EXPLAIN ANALYZE (8.0) to verify index usage.
• Watch for full table scans, filesort, temporary tables.
• Fix N+1 queries at application layer when possible.

5. Transactions & locking

• Confirm isolation level; analyze deadlocks and lock waits.
• Keep transactions short; avoid large gap locks where possible.

6. Partitioning / sharding (when needed)

• Partition only with clear pruning benefits and operational plan.
• Prefer application-level sharding with stable keys when scaling beyond single instance.

7. Operations

• Backups: logical + physical, restore drills, retention.
• Replication monitoring: lag, GTID, failover procedures.
• Online schema changes: gh-ost/pt-online-schema-change where appropriate.

Outputs

• Index plan: which queries, which indexes, expected benefit, trade-offs.
• Migration plan: DDL, rollout steps, backout plan, verification queries.
• Performance report: top queries, proposed fixes, metrics to watch.