DBA常用SQL和命令整理备查原创

# DBA常用SQL和命令整理备查

这是一份速查清单，按需 Ctrl+F 取用，不打算讲原理。但下面几条有坑，取用前先看一眼：

information_schema.TABLES.table_rows 对 InnoDB 是估算值，不是真实行数。它来自索引采样，误差可以到 ±50%，且会随统计信息刷新而跳变。用它筛「大表」没问题，用它对账一定出错——要准确行数只能 SELECT COUNT(*)。

DATA_FREE 不等于「能回收的碎片」。它包含了表空间里已分配未使用的区，共享表空间下这个值更是整个表空间的，不属于单表。用它估碎片率只能看趋势，判断要不要重建见 OPTIMIZE TABLE 和 ANALYZE TABLE 的区别。

所有"拼接 SQL"类的语句只生成文本，不执行。这是刻意的：生成出来先自己读一遍再决定跑哪些。尤其是批量 KILL 和批量 ALTER，别直接管道灌回 mysql。

查 information_schema 在表非常多的实例上很慢，甚至会短暂持有元数据锁。生产高峰期慎用全库范围的查询，能加 table_schema 条件就加上。

版本说明

本文写于 2022-03。
速查类 SQL 与命令长期稳定，经 2026-07 复核仍适用。

# 新建表后校验

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.tables 
WHERE table_name = 'my_table_name';

1
2
3

# 修改表结构后校验

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'my_table_name' 
AND COLUMN_NAME IN ('my_column_name1', 'my_column_name2');

1
2
3
4

# 添加索引后校验

SELECT s.table_schema, s.table_name, s.index_name, s.column_name 
FROM information_schema.STATISTICS s 
WHERE index_name = 'idx_myindex';

1
2
3

# 查看表创建时间

-- 所有表
SELECT table_name, create_time 
FROM information_schema.TABLES;

-- 指定表
SELECT table_name, create_time 
FROM information_schema.TABLES 
WHERE table_name = 'table_name';

1
2
3
4
5
6
7
8

# 查看大于10000000行的表（`table_rows` 是估算值，仅用于筛选）

SELECT table_schema, table_name, table_rows 
FROM information_schema.TABLES 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
AND table_rows > 10000000 
ORDER BY table_rows DESC;

1
2
3
4
5

# KILL数据库链接

⚠️ 下面几条只生成 KILL 语句，不会执行。 真要批量 kill 之前想清楚两件事：KILL 一个正在写的事务会触发 InnoDB 回滚，大事务的回滚时间可能比它已经跑的时间还长，期间照样占着锁；而 Sleep 状态的连接 kill 掉是安全的（它没有在跑语句），但如果应用连接池不会重连，会直接引发业务报错。8.0 里更推荐用 wait_timeout 让空闲连接自然回收，而不是定期 kill。

# 杀掉空闲时间大于2000s的链接

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.`processlist` 
WHERE command = 'Sleep' AND time > 2000;

1
2
3

# 杀掉处于某状态的链接

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.`processlist` 
WHERE state LIKE 'Creating sort index';

1
2
3

# 杀掉某个用户的链接

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.`processlist` 
WHERE user = 'root';

1
2
3

# 拼接创建数据库语句（排除系统库）

SELECT CONCAT(
        'CREATE DATABASE ',
        '`',
        schema_name,
        '`',
        ' DEFAULT CHARACTER SET ',
        default_character_set_name,
        ';'
    ) AS CreateDatabaseQuery
FROM information_schema.schemata
WHERE schema_name NOT IN (
        'information_schema',
        'performance_schema',
        'mysql',
        'sys'
    );

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 拼接创建用户语句（排除系统用户）

注意这条生成的语句写死了 IDENTIFIED WITH 'mysql_native_password'。mysql_native_password 在 8.0 已弃用、在 MySQL 8.4 里默认不再启用、9.0 起被移除，迁移到新版本时这批语句会全部失败。目标实例是 8.4+ 时要改成 caching_sha2_password（此时哈希格式不同，不能直接搬 authentication_string，只能重设密码）。

SELECT CONCAT(
        'CREATE USER \'',
        user,
        '\'@\'',
        host,
        '\' IDENTIFIED WITH \'mysql_native_password\' AS \'',
        authentication_string,
        '\' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;'
    )
FROM mysql.`user`
WHERE `User` NOT IN ('root', 'mysql.session', 'mysql.sys');

1
2
3
4
5
6
7
8
9
10
11

# 用于批量修改密码

⚠️ 这条 SQL 的产物不能直接用。 它拿 authentication_string（密码哈希）做 base64 再截 12 位当新密码——生成的字符串确实随机，但密码强度取决于哈希的可猜测性，且同一个用户每次生成的结果相同，本质上不是随机密码。更要紧的是它把密码哈希读出来放进了 SQL 文本里，这个文本会进 binlog、进历史记录、进你的终端 scrollback。

需要批量重置密码时，正确做法是在脚本外部生成随机密码（openssl rand -base64 12），逐个 ALTER USER ... IDENTIFIED BY，并且执行前 SET SESSION sql_log_bin = 0 避免明文进 binlog。下面这条保留在这里仅作为「读 mysql.user 拼语句」的写法示例：

SELECT CONCAT(
        'CREATE USER ',
        user,
        '_v1@',
        '`',
        host,
        '`',
        ' IDENTIFIED BY ',
        "'",
        RIGHT(TO_BASE64(authentication_string), 12),
        "';"
    )
FROM mysql.user
WHERE host NOT IN ('localhost', '127.0.0.1')
AND user NOT LIKE '%_v1'
ORDER BY user;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 「重命名库」——MySQL 没有这个命令，只能搬表

MySQL 没有 RENAME DATABASE（5.1 短暂存在过，因会丢数据被移除）。改库名的实际做法是：建新库 → 把表逐个 RENAME TABLE old_db.t TO new_db.t 搬过去 → 删旧库。RENAME TABLE 是元数据操作，跨库搬表不复制数据、秒级完成（前提是同一个文件系统）。

生成搬表语句：

SELECT CONCAT('RENAME TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,
              ' TO ', TABLE_SCHEMA, '_new.', TABLE_NAME, ';')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'old_db' AND TABLE_TYPE = 'BASE TABLE';

1
2
3
4

⚠️ RENAME TABLE 不会带走视图、存储过程、触发器和权限：视图里写死的库名会失效，存储过程要重建，GRANT 要按新库名重新授权。搬完记得核对这四样。

下面这条生成的是建空表结构的语句（CREATE TABLE ... LIKE，只复制结构不复制数据），用途是先在备份库里建好骨架：

SELECT CONCAT(
        "CREATE TABLE ", TABLE_SCHEMA, "_bak.", TABLE_NAME, " LIKE ", TABLE_SCHEMA, ".", TABLE_NAME, ";"
    ) 
FROM information_schema.columns 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
GROUP BY TABLE_SCHEMA, TABLE_NAME;

1
2
3
4
5
6

# 查看整个实例空间占用大小

SELECT 
    CONCAT(ROUND(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_length_MB,
    CONCAT(ROUND(SUM(index_length) / 1024 / 1024, 2), ' MB') AS index_length_MB
FROM information_schema.`TABLES`;

1
2
3
4

# 查看各个库占用大小

SELECT 
    TABLE_SCHEMA,
    CONCAT(TRUNCATE(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_size,
    CONCAT(TRUNCATE(SUM(index_length) / 1024 / 1024, 2), ' MB') AS index_size
FROM information_schema.`TABLES`
GROUP BY TABLE_SCHEMA;

1
2
3
4
5
6

# 查看单个库占用空间大小

SELECT 
    CONCAT(ROUND(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_length_MB,
    CONCAT(ROUND(SUM(index_length) / 1024 / 1024, 2), ' MB') AS index_length_MB
FROM information_schema.`TABLES`
WHERE table_schema = 'test_db';

1
2
3
4
5

# 查看单个表占用空间大小

SELECT 
    CONCAT(ROUND(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_length_MB,
    CONCAT(ROUND(SUM(index_length) / 1024 / 1024, 2), ' MB') AS index_length_MB
FROM information_schema.`TABLES`
WHERE table_schema = 'test_db'
AND table_name = 'tbname';

1
2
3
4
5
6

# 查看指定数据库各表容量大小

SELECT 
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE(data_length / 1024 / 1024, 2) AS '数据容量(MB)',
    TRUNCATE(index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM information_schema.tables
WHERE table_schema = 'mysql'
ORDER BY data_length DESC, index_length DESC;

1
2
3
4
5
6
7
8
9

# 查看某个库下所有表的碎片情况

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.TABLE_ROWS,
    CONCAT(ROUND(t.DATA_LENGTH / 1024 / 1024, 2), ' MB') AS size,
    t.INDEX_LENGTH,
    CONCAT(ROUND(t.DATA_FREE / 1024 / 1024, 2), ' MB') AS datafree
FROM information_schema.`TABLES` t
WHERE t.TABLE_SCHEMA = 'callcenter'
ORDER BY datafree DESC;

1
2
3
4
5
6
7
8
9
10

# 收缩表，减少碎片

ALTER TABLE tb_name ENGINE = InnoDB;
OPTIMIZE TABLE tb_name;

1
2

# 查找某一个库无主键表

SELECT 
    table_schema,
    table_name
FROM information_schema.`TABLES`
WHERE table_schema = 'test_db'
AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.table_constraints t
    JOIN information_schema.key_column_usage k USING (constraint_name, table_schema, table_name)
    WHERE t.constraint_type = 'PRIMARY KEY'
    AND t.table_schema = 'test_db'
);

1
2
3
4
5
6
7
8
9
10
11
12

# 查找除系统库外无主键表

SELECT 
    t1.table_schema,
    t1.table_name
FROM information_schema.`TABLES` t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');

1
2
3
4
5
6
7
8
9

# 一些更该用的替代品

这份清单里不少查询在 MySQL 8.0 有更省事的现成视图，sys schema 里直接有：

-- 各库占用空间（替代手工 SUM data_length）
SELECT * FROM sys.schema_table_statistics LIMIT 10;

-- 没被用过的索引（清理冗余索引的依据，比看 information_schema 直观）
SELECT * FROM sys.schema_unused_indexes;

-- 冗余/重复索引
SELECT * FROM sys.schema_redundant_indexes;

-- 全表扫描最多的表
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 当前谁在等谁的锁
SELECT * FROM sys.innodb_lock_waits;

-- 按语句摘要聚合的执行统计（比慢日志更全，不用改配置）
SELECT * FROM sys.statement_analysis LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

sys 库是 8.0 默认自带的，本质是 performance_schema 上的一层视图，查它对生产的影响远小于扫 information_schema。日常排障优先从这里开始。

#性能优化 #SRE

上次更新: 9/11/2026

← MySQL执行计划分析单表数据同步方案选型：为什么不该用 mysqldump 做「实时同步」→

DBA常用SQL和命令整理备查原创

# DBA常用SQL和命令整理备查

# 新建表后校验

# 修改表结构后校验

# 添加索引后校验

# 查看表创建时间

# 查看大于10000000行的表（table_rows 是估算值，仅用于筛选）

# KILL数据库链接

# 杀掉空闲时间大于2000s的链接

# 杀掉处于某状态的链接

# 杀掉某个用户的链接

# 拼接创建数据库语句（排除系统库）

# 拼接创建用户语句（排除系统用户）

# 用于批量修改密码

# 「重命名库」——MySQL 没有这个命令，只能搬表

# 查看整个实例空间占用大小

# 查看各个库占用大小

# 查看单个库占用空间大小

# 查看单个表占用空间大小

# 查看指定数据库各表容量大小

# 查看某个库下所有表的碎片情况

# 收缩表，减少碎片

# 查找某一个库无主键表

# 查找除系统库外无主键表

# 一些更该用的替代品

# 查看大于10000000行的表（`table_rows` 是估算值，仅用于筛选）