附录：附录：MySQL 速查手册

zjc 于 2026-02-05 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。本附录按生产场景组织常用命令和检查项。命令以 MySQL 8.0 为主线，部分 5.7 兼容命令会单独标注。

1. 连接与客户端

mysql -h 127.0.0.1 -P 3306 -u root -p

mysql -h 127.0.0.1 -u root -p \
      --database=shop --compress

mysqldump -h 127.0.0.1 -u backup_user -p \
  --single-transaction shop > shop.sql

mysqlbinlog --no-defaults \
  --start-position=157 --stop-position=1000 \
  mysql-bin.000001 > events.sql

常用客户端命令：

\h
\s
SHOW DATABASES;
USE shop;
SHOW TABLES;
SHOW CREATE TABLE orders\G
SELECT VERSION(), CURRENT_USER(), DATABASE();

2. 服务与参数

SELECT VERSION();
SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'socket';
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'gtid_mode';

修改会话参数：

SET SESSION transaction_isolation = 'READ-COMMITTED';
SET SESSION lock_wait_timeout = 10;
SET SESSION max_execution_time = 5000;

修改全局参数必须评估持久化、重启和回滚：

SET GLOBAL max_connections = 2000;
SET PERSIST max_connections = 2000;

3. 日常巡检

会话与连接：

SHOW FULL PROCESSLIST;

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Aborted_connects';

事务与锁：

SELECT trx_id, trx_state, trx_started,
       trx_rows_locked, trx_rows_modified,
       trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM performance_schema.data_locks;
SHOW ENGINE INNODB STATUS\G

容量：

SELECT
  table_schema,
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

大表：

SELECT
  table_schema,
  table_name,
  engine,
  table_rows,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
ORDER BY data_length + index_length DESC
LIMIT 20;

4. 性能诊断

状态指标：

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

Buffer Pool 命中率计算：

read_ahead = Innodb_buffer_pool_read_ahead
logical_read = Innodb_buffer_pool_read_requests
physical_read = Innodb_buffer_pool_reads

命中率 ≈ 1 - physical_read / logical_read

执行计划：

EXPLAIN
SELECT order_no, amount
FROM orders
WHERE user_id = 10001
  AND status = 'PAID';

EXPLAIN ANALYZE
SELECT order_no, amount
FROM orders
WHERE user_id = 10001
  AND status = 'PAID';

索引使用：

SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'shop'
ORDER BY count_star DESC;

表统计：

ANALYZE TABLE shop.orders;
SHOW TABLE STATUS LIKE 'orders'\G

5. 事务与隔离

查看隔离级别：

SELECT @@transaction_isolation;
SHOW VARIABLES LIKE 'transaction_isolation';

事务控制：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ROLLBACK;

保存点：

SAVEPOINT after_order;
ROLLBACK TO SAVEPOINT after_order;
RELEASE SAVEPOINT after_order;

当前读：

SELECT * FROM orders WHERE id = 1 FOR UPDATE;
SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;

MySQL 8.0 也可以使用：

SELECT * FROM orders WHERE id = 1 FOR SHARE;

6. 复制与高可用

主库：

SHOW MASTER STATUS;
SHOW BINARY LOGS;
SHOW PROCESSLIST;

较新版本中推荐使用更清晰的状态命令，具体可用性以当前版本文档为准：

SHOW BINARY LOG STATUS;

从库：

SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_applier_status_by_worker;
SELECT * FROM performance_schema.replication_connection_status;

GTID：

SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SELECT @@GLOBAL.gtid_executed;

复制错误处理前必须确认业务和数据一致性，不能盲目跳过：

STOP REPLICA;
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;

GTID 空事务方式跳过：

STOP REPLICA;
SET GTID_NEXT = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

7. 备份与恢复

逻辑备份：

mysqldump --single-transaction --set-gtid-purged=off \
  --triggers --routines --events \
  -h 127.0.0.1 -u backup_user -p shop > shop.sql

恢复：

mysql -h 127.0.0.1 -u root -p shop < shop.sql

查看 Binlog：

mysqlbinlog --no-defaults mysql-bin.000001
mysqlbinlog --no-defaults \
  --start-datetime='2026-08-25 10:00:00' \
  --stop-datetime='2026-08-25 11:00:00' \
  mysql-bin.000001 > restore.sql

清理 Binlog：

PURGE BINARY LOGS BEFORE '2026-08-18 00:00:00';

清理前必须确认：

所有从库已消费；
备份链路可用；
时间点恢复窗口允许；
磁盘压力不是因为复制中断堆积。

8. 用户与权限

账号：

SELECT user, host, plugin, account_locked
FROM mysql.user
ORDER BY user, host;

CREATE USER 'shop_app'@'10.0.%'
IDENTIFIED BY 'StrongPassword_2026';

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.*
TO 'shop_app'@'10.0.%';

SHOW GRANTS FOR 'shop_app'@'10.0.%';

角色：

CREATE ROLE shop_readonly;
GRANT SELECT ON shop.* TO shop_readonly;
GRANT shop_readonly TO 'report_user'@'10.20.%';
SET DEFAULT ROLE shop_readonly
TO 'report_user'@'10.20.%';

回收与锁定：

REVOKE DROP ON shop.* FROM 'shop_app'@'10.0.%';
ALTER USER 'shop_app'@'10.0.%' ACCOUNT LOCK;
DROP USER 'shop_app'@'10.0.%';

强制 TLS：

ALTER USER 'shop_app'@'10.0.%' REQUIRE SSL;
ALTER USER 'shop_app'@'10.0.%' REQUIRE X509;

9. DDL 与在线变更

Instant 加列：

ALTER TABLE shop.orders
  ADD COLUMN channel VARCHAR(16) NOT NULL DEFAULT 'APP',
  ALGORITHM=INSTANT;

在线加索引：

ALTER TABLE shop.orders
  ADD INDEX idx_merchant_created (merchant_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

隐藏索引：

ALTER TABLE shop.orders ALTER INDEX idx_old INVISIBLE;
ALTER TABLE shop.orders ALTER INDEX idx_old VISIBLE;

gh-ost：

gh-ost \
  --host=127.0.0.1 --port=3306 --user=change_user \
  --database=shop --table=orders \
  --alter="ADD INDEX idx_status_created(status, created_at)" \
  --chunk-size=1000 \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=200 \
  --cut-over=default \
  --execute

pt-osc：

pt-online-schema-change \
  --alter "ADD INDEX idx_status_created(status, created_at)" \
  D=shop,t=orders \
  --host=127.0.0.1 --port=3306 \
  --user=change_user --ask-pass \
  --chunk-size=1000 \
  --execute

变更前检查：

表大小和增长
长事务与 MDL
主从延迟
磁盘与 IO 余量
支持的 DDL 算法
测试环境演练结果
停止条件
回滚方案

10. 常见故障处理

10.1 连不上

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Aborted_connects';
SHOW FULL PROCESSLIST;

检查：

MySQL 进程和端口；
安全组与防火墙；
账号 host；
密码和认证插件；
max_connections;
应用连接池；
被锁定或 blocked 的账号。

10.2 CPU 高

SELECT * FROM sys.session
ORDER BY statement_latency DESC
LIMIT 10;

处理：

KILL QUERY <processlist_id>;
KILL <processlist_id>;

杀连接前确认事务和操作人。止血还要考虑限流、回滚发布、停止批任务和切流。

10.3 锁等待

SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.innodb_trx;

判断阻塞源头、事务年龄、锁对象和等待 SQL，再决定是否终止事务。

10.4 主从延迟

SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_applier_status_by_worker;

检查大事务、Worker 状态、从库负载、表索引、网络和磁盘。

10.5 磁盘满

SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'log_bin_basename';
SHOW BINARY LOGS;

常见来源：Binlog、临时表、Relay Log、备份残留、错误日志、Undo 膨胀和大表空间。不要直接删除 MySQL 正在管理的文件。

11. 推荐参数起点

以下不是万能配置，必须结合机器规格、工作集和业务峰值调整：

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
default_storage_engine = InnoDB
max_connections = 2000
thread_cache_size = 100
innodb_buffer_pool_size = 40G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
transaction_isolation = READ-COMMITTED
slow_query_log = ON
long_query_time = 0.5
log_queries_not_using_indexes = OFF
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON

强持久性组合：

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

12. 上线检查清单

表设计：

[ ] 显式主键
[ ] 金额使用 DECIMAL
[ ] 时间类型和精度明确
[ ] 字符集和排序规则统一
[ ] 唯一约束覆盖业务规则
[ ] 高频查询有索引设计
[ ] 大字段单独评估
[ ] 表和字段有注释

SQL：

[ ] EXPLAIN 已评审
[ ] 无 SELECT * 依赖
[ ] UPDATE / DELETE 有 WHERE
[ ] 大事务已拆分
[ ] 深分页有方案
[ ] 超时和重试明确
[ ] 无隐式类型转换

发布：

[ ] 变更可回滚
[ ] 备份点可用
[ ] 低峰执行
[ ] 停止条件明确
[ ] 监控看板就绪
[ ] 通知相关团队

治理：

[ ] 慢查询采集开启
[ ] 主从延迟告警
[ ] 连接水位告警
[ ] 磁盘容量预测
[ ] 权限最小化
[ ] 恢复演练通过

13. 版本差异速记

主题	MySQL 5.7	MySQL 8.0
默认认证插件	mysql_native_password	caching_sha2_password
字符集	latin1	utf8mb4
数据字典	FRM 文件	InnoDB 数据字典
DDL	部分 Online	INSTANT / INPLACE 增强
角色管理	不支持	支持
CTE / 窗口函数	不支持	支持
EXPLAIN ANALYZE	不支持	支持
隐藏索引	不支持	支持
复制术语	MASTER / SLAVE	SOURCE / REPLICA 逐步推广

14. 学习地图

第一阶段：SQL、类型、建表、基础查询
第二阶段：EXPLAIN、B+Tree、索引、JOIN、子查询
第三阶段：事务、MVCC、锁、Redo、Undo、Binlog
第四阶段：Buffer Pool、复制、高可用、备份恢复
第五阶段：分库分表、容量、在线变更、监控治理
第六阶段：源码阅读、内核优化、大规模架构