AgentSkillsCN

mysql

具备MySQL DBA技能，擅长进行表结构设计评审、索引优化、查询性能调优（通过EXPLAIN分析）、事务与隔离级别问题诊断、复制与高可用架构设计、备份与恢复操作，以及制定标准化的运维检查清单。适用于诊断慢查询、设计主键与索引、规划分区策略，以及安全地执行在线表结构变更等任务。

SKILL.md
--- frontmatter
name: mysql
description: MySQL DBA skill for schema design review, indexing, query performance tuning (EXPLAIN), transaction/isolation issues, replication/high availability, backup/restore, and operational checklists. Use for tasks like diagnosing slow queries, designing keys/indexes, partitioning strategy, and safe online schema changes.

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.
  1. •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.
  1. •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.
  1. •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.
  1. •Transactions & locking
  • •Confirm isolation level; analyze deadlocks and lock waits.
  • •Keep transactions short; avoid large gap locks where possible.
  1. •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.
  1. •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.