搜索结果

×

搜索结果将在这里显示。

SQL 快速参考手册(运维增强版)

SQL 快速参考手册(运维增强版)

整理自 菜鸟教程 SQL 快速参考,并补充运维日常管理命令(以 MySQL 为主,标注了版本差异)。
维护人:橙子柠檬 | 更新日期:2026-10-05

目录

一、数据查询(DQL)

语句 语法 说明
SELECT SELECT column_name(s) FROM table_name; 从表中选择数据
**SELECT *** SELECT * FROM table_name; 选择所有列
SELECT DISTINCT SELECT DISTINCT column_name(s) FROM table_name; 返回去重后的值
WHERE SELECT column_name(s) FROM table_name WHERE column operator value; 过滤记录
AND / OR SELECT ... WHERE condition AND\|OR condition; 逻辑与 / 逻辑或
ORDER BY SELECT ... ORDER BY column [ASC\|DESC]; 排序,ASC 升序(默认)、DESC 降序
LIKE SELECT ... WHERE column LIKE pattern; 模式匹配(% 任意字符,_ 单字符)
IN SELECT ... WHERE column IN (v1, v2, ...); 匹配集合中的值
BETWEEN SELECT ... WHERE column BETWEEN v1 AND v2; 范围筛选(含边界)
LIMIT (MySQL) SELECT ... LIMIT n OFFSET m; 或 LIMIT m, n; 限制返回行数(SQL Server 用 TOP,Oracle 用 ROWNUM)
别名 AS SELECT col AS c_alias FROM table AS t_alias; 列或表取别名
INNER JOIN SELECT ... FROM t1 INNER JOIN t2 ON t1.c=t2.c; 两表匹配的记录
LEFT JOIN SELECT ... FROM t1 LEFT JOIN t2 ON t1.c=t2.c; 左表全部 + 右表匹配
RIGHT JOIN SELECT ... FROM t1 RIGHT JOIN t2 ON t1.c=t2.c; 右表全部 + 左表匹配
FULL JOIN SELECT ... FROM t1 FULL JOIN t2 ON t1.c=t2.c; 两表全部记录(MySQL 不支持,用 UNION 模拟)
UNION SELECT ... FROM t1 UNION SELECT ... FROM t2; 合并结果集并去重
UNION ALL SELECT ... FROM t1 UNION ALL SELECT ... FROM t2; 合并结果集不去重(性能更好)
GROUP BY SELECT col, agg(col) FROM t GROUP BY col; 分组聚合
HAVING SELECT ... GROUP BY col HAVING agg(col) op value; 对分组后的结果过滤

常用聚合/函数

COUNT(*) / COUNT(col)   -- 计数
SUM(col) / AVG(col)     -- 求和 / 平均
MAX(col) / MIN(col)     -- 最大 / 最小
ROUND(x, d)             -- 四舍五入
NOW()                   -- 当前时间
IFNULL(col, 0)          -- NULL 转换(COALESCE 同理,支持多参数)
DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s')  -- 日期格式化(MySQL)

二、数据操作(DML)

语句 语法 说明
INSERT INTO INSERT INTO t (c1, c2) VALUES (v1, v2); 插入记录
INSERT INTO SELECT INSERT INTO t2 SELECT * FROM t1 WHERE ...; 从另一表批量导入
UPDATE UPDATE t SET c1=v1, c2=v2 WHERE condition; 修改记录(务必带 WHERE)
DELETE DELETE FROM t WHERE condition; 删除记录(务必带 WHERE)
TRUNCATE TRUNCATE TABLE t; 清空全表(保留结构,不可回滚,重置自增)

⚠️ UPDATE/DELETE 不带 WHERE 会影响全表;生产环境执行前先开事务验证,或先用 SELECT 确认影响范围。

三、数据定义(DDL)

语句 语法 说明
CREATE DATABASE CREATE DATABASE db_name DEFAULT CHARSET utf8mb4; 建库(建议显式指定字符集)
CREATE TABLE CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL, ...); 建表
CREATE INDEX CREATE INDEX idx_name ON t (col); 普通索引
CREATE UNIQUE INDEX CREATE UNIQUE INDEX idx_name ON t (col); 唯一索引
CREATE VIEW CREATE VIEW v AS SELECT ... WHERE ...; 创建视图
ALTER TABLE 加列 ALTER TABLE t ADD COLUMN c datatype; 加列
ALTER TABLE 删列 ALTER TABLE t DROP COLUMN c; 删列
ALTER TABLE 改列 ALTER TABLE t MODIFY COLUMN c new_datatype; 改列类型
ALTER TABLE 改名 ALTER TABLE t RENAME TO new_t; 表改名
DROP DATABASE DROP DATABASE db_name; 删库(⚠️ 危险)
DROP TABLE DROP TABLE t; 删表(⚠️ 危险)
DROP INDEX ALTER TABLE t DROP INDEX idx_name; (MySQL) 删索引
DROP VIEW DROP VIEW v; 删视图

常用约束

NOT NULL            -- 非空
UNIQUE              -- 唯一
PRIMARY KEY         -- 主键(非空 + 唯一)
FOREIGN KEY         -- 外键:FOREIGN KEY (col) REFERENCES other(id)
CHECK (expr)        -- 检查约束
DEFAULT value       -- 默认值
AUTO_INCREMENT      -- 自增(MySQL)

四、用户与权限管理(DCL)

语句 语法 说明
CREATE USER CREATE USER 'user'@'host' IDENTIFIED BY 'password'; 创建用户(host 可用 % 表示任意)
GRANT GRANT SELECT, INSERT ON db.* TO 'user'@'host'; 授权
GRANT ALL GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' WITH GRANT OPTION; 全库全权限(慎用)
REVOKE REVOKE INSERT ON db.* FROM 'user'@'host'; 回收权限
SHOW GRANTS SHOW GRANTS FOR 'user'@'host'; 查看用户权限
ALTER USER ALTER USER 'user'@'host' IDENTIFIED BY 'new_password'; 改密码(MySQL 5.7+)
SET PASSWORD SET PASSWORD FOR 'user'@'host' = 'new_password'; 改密码(旧写法)
RENAME USER RENAME USER 'old'@'host' TO 'new'@'host'; 用户改名
DROP USER DROP USER 'user'@'host'; 删除用户
FLUSH PRIVILEGES FLUSH PRIVILEGES; 刷新权限表(直接改 grant 表后才需要)

💡 MySQL 8.0 起不再支持 GRANT 隐式创建用户,必须先 CREATE USER。
⚠️ 密码不要出现在 shell 命令行/脚本/日志里,用 mysql_config_editor 或交互输入。

五、会话与进程管理

运维高频:排查慢 SQL、连接堆积、杀会话。

语句 语法 说明
SHOW PROCESSLIST SHOW PROCESSLIST; 查看当前连接与正在执行的语句
SHOW FULL PROCESSLIST SHOW FULL PROCESSLIST; 同上,但显示完整 SQL(不截断)
增强版 SELECT * FROM information_schema.processlist; 信息更全（含执行中的 SQL 文本、进度），且能加条件过滤
KILL KILL <Id>; 杀掉指定连接
KILL QUERY KILL QUERY <Id>; 只终止该连接正在执行的语句,保留连接 
-- 筛选运行超过 60 秒的查询
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 60
ORDER BY time DESC;

-- 查看连接数概况
SHOW STATUS LIKE 'Threads_%';        -- Threads_connected / Threads_running
SHOW VARIABLES LIKE 'max_connections';

批量 KILL(生成 kill 语句再执行,不要盲目全杀):

-- 杀掉某用户的所有 Sleep 超时连接
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE user = 'app_user' AND command = 'Sleep' AND time > 600;

-- 拿到输出后逐条确认执行

六、状态与配置(运行时诊断)

语句 语法 说明
SHOW STATUS SHOW GLOBAL STATUS; 运行状态计数器(QPS 相关、连接数等)
SHOW VARIABLES SHOW GLOBAL VARIABLES LIKE '%buffer%'; 查看配置参数
SET GLOBAL SET GLOBAL max_connections = 2000; 在线改参数(重启失效,需同步改 my.cnf)
SHOW ENGINES SHOW ENGINES; 查看支持的存储引擎
SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB STATUS\G InnoDB 详细状态(锁、死锁、缓冲池、IO)
SHOW CHARACTER SET SHOW CHARACTER SET; 支持的字符集
SHOW COLLATION SHOW COLLATION; 支持的排序规则 
-- 运维常用状态项
SHOW GLOBAL STATUS LIKE 'Threads_connected';   -- 当前连接数
SHOW GLOBAL STATUS LIKE 'Threads_running';     -- 活跃线程数
SHOW GLOBAL STATUS LIKE 'Max_used_connections';-- 历史峰值连接
SHOW GLOBAL STATUS LIKE 'Slow_queries';        -- 慢查询累计次数
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_%';   -- 行锁等待统计
SHOW GLOBAL STATUS LIKE 'Uptime';              -- 实例运行时长

七、库表元数据查看

语句 语法 说明
SHOW DATABASES SHOW DATABASES; 列出所有库
USE USE db_name; 切换库
SHOW TABLES SHOW TABLES; / SHOW TABLES FROM db; 列出表
DESCRIBE DESC t; 或 SHOW COLUMNS FROM t; 查看表结构
SHOW CREATE TABLE SHOW CREATE TABLE t\G 查看完整建表语句(含引擎/字符集)
SHOW CREATE DATABASE SHOW CREATE DATABASE db\G 查看建库语句
SHOW INDEX SHOW INDEX FROM t; 查看表的索引
SHOW TABLE STATUS SHOW TABLE STATUS LIKE 't%'\G 表的引擎/行数/大小等 
-- 用 information_schema 统计:各库大小
SELECT table_schema AS db,
       ROUND(SUM(data_length+index_length)/1024/1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

-- 某库下最大的 10 张表
SELECT table_name,
       table_rows,
       ROUND((data_length+index_length)/1024/1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY data_length+index_length DESC
LIMIT 10;

-- 查找无主键的表(复制与大数据量运维隐患)
SELECT table_schema, table_name
FROM information_schema.tables t
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND NOT EXISTS (SELECT 1 FROM information_schema.table_constraints c
                WHERE c.table_schema=t.table_schema AND c.table_name=t.table_name
                AND c.constraint_type='PRIMARY KEY');

八、锁与事务排查

线上卡顿常见原因:长事务、锁等待。排查顺序:先看正在执行的事务 → 再看锁等待 → 定位阻塞源头。

-- 1. 当前运行中的事务(重点看运行时间长的)
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id AS pid,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- 2. 锁等待关系(MySQL 5.7)
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_locks;

-- 3. 锁等待关系(MySQL 8.0,新数据字典)
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

-- 4. 一站式:谁在等谁的锁(8.0 常用)
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.innodb_lock_waits\G

-- 5. 杀掉阻塞源头(用上面查到的 blocking_pid)
KILL <blocking_pid>;

显式锁相关语句:

SELECT * FROM t WHERE id=1 FOR UPDATE;      -- 行排他锁(事务内)
SELECT * FROM t WHERE id=1 FOR SHARE;       -- 行共享锁(8.0,旧写法 LOCK IN SHARE MODE)
LOCK TABLES t READ;                          -- 表读锁
LOCK TABLES t WRITE;                         -- 表写锁
UNLOCK TABLES;                               -- 解锁
FLUSH TABLES WITH READ LOCK;                 -- 全局读锁(备份用,尽快释放)

⚠️ FLUSH TABLES WITH READ LOCK 会阻塞所有写操作,生产库慎用;备份优先用 mysqldump --single-transaction(仅 InnoDB)。

九、主从复制管理

语句 语法 说明
SHOW MASTER STATUS SHOW MASTER STATUS; 主库当前 binlog 位点
SHOW SLAVE STATUS\G SHOW SLAVE STATUS\G 从库复制状态(运维必会)
SHOW REPLICAS SHOW REPLICAS; 主库看已连接的从库(8.0.22+)
START/STOP REPLICA START REPLICA; / STOP REPLICA; 启停复制(旧写法 START SLAVE)
RESET REPLICA RESET REPLICA ALL; 清除复制配置(⚠️ 慎重)
CHANGE REPLICATION SOURCE TO CHANGE REPLICATION SOURCE TO SOURCE_HOST='x', SOURCE_USER='r', SOURCE_PASSWORD='p', SOURCE_LOG_FILE='binlog.000001', SOURCE_LOG_POS=157; 配置复制源(8.0.22+,旧写法 CHANGE MASTER TO)
SHOW BINARY LOGS SHOW BINARY LOGS; 列出 binlog 文件与大小
SHOW BINLOG EVENTS SHOW BINLOG EVENTS IN 'binlog.000001' LIMIT 10; 查看 binlog 内容
PURGE BINARY LOGS PURGE BINARY LOGS BEFORE '2026-09-01 00:00:00'; 清理 binlog(⚠️ 确认从库已消费)
FLUSH LOGS FLUSH BINARY LOGS; 手动滚动 binlog 
-- 从库健康检查三要素
SHOW SLAVE STATUS\G
-- 1) Slave_IO_Running: Yes
-- 2) Slave_SQL_Running: Yes
-- 3) Seconds_Behind_Master: 0   (延迟秒数)

-- 主从延迟对应关系(从库执行,用于精确追位点)
SHOW REPLICA STATUS\G  -- 看 Retrieved_Gtid_Set / Executed_Gtid_Set(启用 GTID 时)

⚠️ PURGE BINARY LOGS 前必须确认所有从库已消费对应 binlog;更推荐设置 expire_logs_days / binlog_expire_logs_seconds 自动清理。

十、备份与恢复

Shell 层工具为主,SQL 层配合。

# 全库逻辑备份(InnoDB 推荐 --single-transaction,不锁表)
mysqldump -uroot -p --single-transaction --routines --triggers \
  --master-data=2 --all-databases > full_backup_$(date +%F).sql

# 单库 / 单表
mysqldump -uroot -p db_name > db.sql
mysqldump -uroot -p db_name table1 table2 > tables.sql

# 仅备份表结构 / 仅数据
mysqldump -uroot -p --no-data db_name > schema.sql
mysqldump -uroot -p --no-create-info db_name > data.sql

# 恢复
mysql -uroot -p db_name < db.sql

# 物理备份:percona xtrabackup(大库首选)
xtrabackup --backup --target-dir=/backup/base --user=root --password=xxx

# binlog 解析(指定库、时间范围)
mysqlbinlog --start-datetime="2026-10-01 00:00:00" \
  --stop-datetime="2026-10-02 00:00:00" binlog.000001 > binlog.sql
-- SQL 层配合
SOURCE /path/to/dump.sql;             -- 客户端内导入
SELECT ... INTO OUTFILE '/tmp/x.csv'; -- 导出数据到文件(需 secure_file_priv 许可)
LOAD DATA INFILE '/tmp/x.csv' INTO TABLE t;  -- 从文件导入
FLUSH TABLES WITH READ LOCK;          -- 备份期间短暂加全局读锁(非 InnoDB 表)
UNLOCK TABLES;

⚠️ SELECT ... INTO OUTFILE/LOAD DATA INFILE 受 secure_file_priv 限制;恢复大数据量时先关闭 binlog(SET sql_log_bin=0)可提速,但注意复制环境一致性。

十一、表维护与优化

语句 语法 说明
ANALYZE TABLE ANALYZE TABLE t; 重新收集统计信息(优化器用)
OPTIMIZE TABLE OPTIMIZE TABLE t; 整理碎片空间(实质重建表,⚠️ 大表会长时间锁)
CHECK TABLE CHECK TABLE t; 检查表完整性
REPAIR TABLE REPAIR TABLE t; 修复表(仅 MyISAM 有效)
FLUSH TABLES FLUSH TABLES; 关闭所有打开的表
FLUSH STATUS FLUSH STATUS; 重置会话状态计数器 
-- 查看表碎片率,决定是否需要 OPTIMIZE
SELECT table_name, data_free/1024/1024 AS frag_mb
FROM information_schema.tables
WHERE table_schema='your_db' AND data_free > 0
ORDER BY data_free DESC;

十二、性能分析

-- 1. 执行计划(优化第一入口)
EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...\G     -- 详细计划(含 cost)
EXPLAIN ANALYZE SELECT ...;          -- 8.0.18+ 实际执行并统计,最真实

-- 2. 慢查询日志(动态开启,无需重启)
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;      -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

-- 3. 语句分析(MySQL 5.7 前常用)
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 4. performance_schema:开销最大的 SQL(8.0)
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_sorts LIMIT 10;

-- 5. sys schema 现成视图(8.0 强烈推荐)
SELECT * FROM sys.schema_tables_with_full_table_scans;  -- 全表扫描多的表
SELECT * FROM sys.innodb_buffer_stats_by_table;         -- 缓冲池占用
SELECT * FROM sys.user_summary_by_statement_type;       -- 按用户统计语句

EXPLAIN 关键列速查:

列 关注点
type 访问类型,从好到差:system > const > eq_ref > ref > range > index > ALL,出现 ALL(全表扫描)要警惕
key 实际使用的索引,NULL 表示没用上索引
rows 预估扫描行数,越大越慢
Extra Using filesort(额外排序)、Using temporary(临时表)都是优化信号

十三、常用运维场景速查

场景 1:连接数暴涨 / too many connections

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
SELECT user, host, count(*) AS cnt FROM information_schema.processlist
GROUP BY user, host ORDER BY cnt DESC;          -- 谁占的连接
SET GLOBAL max_connections = 2000;              -- 临时扩容(治标)
-- 治本:排查应用连接池配置 / 连接泄漏

场景 2:某条 SQL 把库拖慢了

SHOW FULL PROCESSLIST;                          -- 找到 long time 的 Query
KILL <id>;                                      -- 终止
-- 再用 EXPLAIN 分析该 SQL,补索引

场景 3:表被锁,业务卡住

SELECT * FROM sys.innodb_lock_waits\G           -- 找 blocking_pid
KILL <blocking_pid>;
SELECT * FROM information_schema.innodb_trx;    -- 复查是否还有长事务

场景 4:主从延迟高

SHOW SLAVE STATUS\G                             -- 看 Seconds_Behind_Master
-- 大事务 → 拆小;单线程回放慢 → 8.0 开并行复制
-- SHOW VARIABLES LIKE 'replica_parallel_workers';

场景 5:磁盘被 binlog 占满

SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;  -- 确认从库已同步再清

场景 6:找回误删数据(有 binlog 前提)

# 定位误操作 binlog → 反向生成补偿 SQL(工具:my2sql / binlog2sql)
mysqlbinlog --start-position=xxx --stop-position=yyy binlog.000005 > recover.sql

附:危险操作清单

以下命令生产环境执行前必须走变更工单 + 二次确认:

操作 风险
DROP DATABASE / DROP TABLE 不可恢复(除非有备份)
TRUNCATE TABLE 清空全表,隐式提交,不可回滚
无 WHERE 的 UPDATE / DELETE 全表数据被改/删
KILL 大事务连接 回滚可能耗时更长,先评估
RESET MASTER / RESET REPLICA 清空 binlog / 复制位点,破坏复制链
PURGE BINARY LOGS 从库未消费会导致复制中断
FLUSH TABLES WITH READ LOCK 全库写阻塞
OPTIMIZE TABLE(大表) 长时间锁表、占用双倍磁盘空间
SET GLOBAL 内存相关参数 调大 buffer 可能引发 OOM

参考链接