mysql 常用命令
CREATE
命令格式
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'auth_string'
| IDENTIFIED BY PASSWORD 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}示例
# user
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'jeffrey'@'localhost' identified with mysql_native_password by 'password';
# database
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;ALTER
命令格式
ALTER USER [IF EXISTS]
user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
ALTER USER [IF EXISTS]
USER() IDENTIFIED BY 'auth_string'
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}示例
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
ALTER USER
'jeffrey'@'localhost' IDENTIFIED BY 'new_password',
'jeanne'@'localhost'
REQUIRE SSL WITH MAX_USER_CONNECTIONS 2;DROP
drop user wgs@'%';grant
grant all privileges on *.* to wgs@'%' identified by 'xxxxxxxx' require ssl;修改密码
set password for root@'localhost' = password('123456');清理binlog
MySQL [(none)]> purge binary logs to 'mysql-bin.001334';
Query OK, 0 rows affected (0.40 sec)MySQL [(none)]> show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.001334 | 1073742113 |
| mysql-bin.001335 | 690368167 |
+------------------+------------+
2 rows in set (0.01 sec)MySQL 进程监控查询
SELECT
ID AS 连接ID,
USER AS 用户名,
CASE
WHEN HOST LIKE 'localhost%' THEN '本地'
ELSE SUBSTRING_INDEX(HOST, ':', 1)
END AS 主机,
IFNULL(DB, '-') AS 数据库,
COMMAND AS 命令类型,
-- 格式化运行时间
CASE
WHEN TIME >= 86400 THEN CONCAT(FLOOR(TIME/86400), '天', FLOOR(MOD(TIME,86400)/3600), '时')
WHEN TIME >= 3600 THEN CONCAT(FLOOR(TIME/3600), '时', FLOOR(MOD(TIME,3600)/60), '分')
WHEN TIME >= 60 THEN CONCAT(FLOOR(TIME/60), '分', MOD(TIME,60), '秒')
ELSE CONCAT(TIME, '秒')
END AS 运行时间,
IFNULL(STATE, '运行中') AS 状态,
LEFT(INFO, 100) AS 查询语句
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
AND USER NOT IN ('system user', 'event_scheduler')
AND ID != CONNECTION_ID() -- 排除当前查询自己
ORDER BY TIME DESC
LIMIT 30;|
字段名 |
中文解释 |
详细说明 |
|---|---|---|
|
ID |
连接ID |
每个客户端连接的唯一标识符，可用于终止连接（KILL ID） |
|
USER |
用户 |
建立连接的用户名 |
|
HOST |
主机地址 |
客户端连接来源的主机名和端口（如： |
|
DB |
数据库 |
当前连接的默认数据库，NULL表示未选择数据库 |
|
COMMAND |
命令类型 |
连接正在执行的命令类型，常见值： |
|
TIME |
执行时间 |
该状态的持续时间（秒），对于查询表示已执行的时间 |
|
STATE |
状态 |
线程的详细状态，常见值： |
|
QUERY |
查询语句 |
正在执行的SQL语句前100个字符 |
|
STATE |
含义 |
处理方法 |
|---|---|---|
|
|
查询处理中 |
查看QUERY字段，优化慢查询 |
|
|
结果排序中 |
检查ORDER BY和索引 |
|
|
元数据锁等待 |
检查DDL操作和长事务 |
|
|
表锁等待 |
检查MyISAM表或显式锁 |
|
|
创建排序索引 |
临时表太大，需优化查询 |
|
|
创建临时表 |
检查GROUP BY和JOIN |
查找长时间运行查询
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
LEFT(INFO, 200) AS QUERY,
-- 生成EXPLAIN链接
CONCAT('EXPLAIN ', INFO) AS EXPLAIN_QUERY,
-- 生成优化建议
CASE
WHEN INFO LIKE '%SELECT%' AND TIME > 10 THEN '检查索引和WHERE条件'
WHEN INFO LIKE '%UPDATE%' AND TIME > 10 THEN '检查行锁和事务大小'
WHEN INFO LIKE '%DELETE%' AND TIME > 10 THEN '考虑分批删除'
ELSE '正常'
END AS 优化建议
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Query'
AND TIME > 10
AND INFO IS NOT NULL
AND INFO NOT LIKE '%PROCESSLIST%' -- 排除自身查询
ORDER BY TIME DESC
LIMIT 20;按状态分组统计
SELECT
STATE AS 状态,
COUNT(*) AS 进程数,
AVG(TIME) AS 平均运行秒,
MAX(TIME) AS 最长运行秒,
-- 状态说明
CASE STATE
WHEN 'Sending data' THEN '正在发送数据到客户端'
WHEN 'Sorting result' THEN '排序结果集'
WHEN 'Copying to tmp table' THEN '创建临时表'
WHEN 'Writing to net' THEN '写入网络'
WHEN 'Locked' THEN '等待表锁'
WHEN 'Creating sort index' THEN '创建排序索引'
WHEN NULL THEN '空闲'
ELSE STATE
END AS 状态说明
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
AND USER NOT IN ('system user', 'event_scheduler')
GROUP BY STATE
HAVING COUNT(*) > 0
ORDER BY 进程数 DESC, 最长运行秒 DESC;MySQL 锁阻塞分析查询
SELECT
-- 阻塞者信息
b.trx_mysql_thread_id AS 阻塞进程ID,
b.trx_query AS 阻塞查询,
b.trx_started AS 阻塞开始时间,
TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS 阻塞秒数,
-- 被阻塞者信息
COUNT(DISTINCT r.trx_id) AS 阻塞数量,
GROUP_CONCAT(DISTINCT r.trx_mysql_thread_id) AS 被阻塞进程ID列表,
-- 锁信息
GROUP_CONCAT(DISTINCT w.requested_lock_type) AS 等待锁类型,
GROUP_CONCAT(DISTINCT l.lock_table) AS 锁表列表,
-- 时间统计
MIN(r.trx_wait_started) AS 最早等待时间,
MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW())) AS 最长等待秒数
FROM information_schema.INNODB_TRX b
INNER JOIN information_schema.INNODB_LOCK_WAITS w ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
LEFT JOIN information_schema.INNODB_LOCKS l ON b.trx_id = l.lock_trx_id
WHERE b.trx_state = 'RUNNING'
GROUP BY b.trx_mysql_thread_id, b.trx_query, b.trx_started
HAVING COUNT(r.trx_id) > 0
ORDER BY 阻塞数量 DESC, 阻塞秒数 DESC
LIMIT 10;blocking_pid 阻塞进程ID
blocking_query 阻塞查询
trx_started 事务开始时间
blocking_sec 阻塞时长(秒)
waiting_count 等待数量常见阻塞场景识别
-- 识别不同类型的锁阻塞
SELECT
CASE
WHEN b.trx_query LIKE '%UPDATE%' AND r.trx_query LIKE '%SELECT%' THEN '写阻塞读'
WHEN b.trx_query LIKE '%SELECT% FOR UPDATE%' THEN 'SELECT FOR UPDATE阻塞'
WHEN b.trx_query LIKE '%ALTER TABLE%' THEN 'DDL操作阻塞'
WHEN b.trx_query LIKE '%DELETE%' AND b.trx_rows_locked > 1000 THEN '大事务删除'
ELSE '其他阻塞'
END AS 阻塞类型,
COUNT(*) AS 发生次数,
AVG(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW())) AS 平均等待秒
FROM information_schema.INNODB_TRX b
JOIN information_schema.INNODB_LOCK_WAITS w ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
GROUP BY 阻塞类型
ORDER BY 平均等待秒 DESC;MySQL 客户端连接数分析查询
SELECT
SUBSTRING_INDEX(HOST, ':', 1) AS 客户端IP,
USER AS 用户名,
COUNT(*) AS 连接数,
-- 状态统计
SUM(CASE WHEN COMMAND = 'Sleep' THEN 1 ELSE 0 END) AS 空闲连接,
SUM(CASE WHEN COMMAND = 'Query' THEN 1 ELSE 0 END) AS 活跃查询,
SUM(CASE WHEN COMMAND = 'Binlog Dump' THEN 1 ELSE 0 END) AS 复制连接,
-- 数据库使用统计
GROUP_CONCAT(DISTINCT DB) AS 使用数据库列表,
COUNT(DISTINCT DB) AS 使用数据库数,
-- 运行时间统计
MAX(TIME) AS 最长运行秒,
AVG(TIME) AS 平均运行秒,
-- 客户端信息
GROUP_CONCAT(DISTINCT
CASE
WHEN STATE IS NOT NULL AND STATE != '' THEN STATE
ELSE COMMAND
END
) AS 状态分布
FROM information_schema.PROCESSLIST
WHERE USER NOT IN ('system user', 'event_scheduler')
AND HOST NOT LIKE 'localhost%' -- 排除本地连接
GROUP BY 客户端IP, 用户名
HAVING 连接数 > 1
ORDER BY 连接数 DESC, 最长运行秒 DESC
LIMIT 20;MySQL 无索引查询分析
SELECT
SCHEMA_NAME AS 数据库,
LEFT(DIGEST_TEXT, 200) AS 查询模板,
COUNT_STAR AS 执行次数,
-- 性能指标
FORMAT(SUM_ROWS_EXAMINED, 0) AS 总扫描行,
FORMAT(SUM_ROWS_SENT, 0) AS 总返回行,
SUM_NO_INDEX_USED AS 无索引次数,
-- 计算平均每次扫描行数
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS 均扫描行,
-- 效率比：返回行/扫描行
ROUND(
CASE
WHEN SUM_ROWS_EXAMINED = 0 THEN 0
ELSE SUM_ROWS_SENT * 100.0 / SUM_ROWS_EXAMINED
END, 2
) AS 行效率百分比,
-- 无索引执行比例
ROUND(SUM_NO_INDEX_USED * 100.0 / COUNT_STAR, 1) AS 无索引比例,
-- 时间信息
DATE_FORMAT(FIRST_SEEN, '%H:%i:%s') AS 首次执行,
DATE_FORMAT(LAST_SEEN, '%H:%i:%s') AS 最后执行,
-- 优化建议
CASE
WHEN SUM_ROWS_EXAMINED / COUNT_STAR > 10000 THEN '🔴 急需添加索引'
WHEN SUM_NO_INDEX_USED * 100.0 / COUNT_STAR > 50 THEN '🔴 索引缺失严重'
WHEN SUM_ROWS_EXAMINED / COUNT_STAR > 1000 THEN '🟡 建议添加索引'
ELSE '🟢 监控观察'
END AS 优化优先级
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
AND SCHEMA_NAME IS NOT NULL
AND COUNT_STAR > 0
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;识别高风险查询
-- 高风险无索引查询：扫描行多、执行频繁
SELECT
SCHEMA_NAME,
LEFT(DIGEST_TEXT, 150) AS 查询模板,
COUNT_STAR AS 执行次数,
FORMAT(SUM_ROWS_EXAMINED, 0) AS 扫描行,
FORMAT(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS 均扫描行,
SUM_NO_INDEX_USED AS 无索引次数,
-- 风险评分（1-10分）
CASE
WHEN SUM_ROWS_EXAMINED / COUNT_STAR > 100000 THEN 10
WHEN SUM_ROWS_EXAMINED / COUNT_STAR > 10000 THEN 8
WHEN SUM_ROWS_EXAMINED / COUNT_STAR > 1000 THEN 6
WHEN COUNT_STAR > 1000 THEN 4
ELSE 2
END AS 风险评分
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
AND COUNT_STAR > 10
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 5;索引优化检查清单
-- 检查表级索引缺失情况
SELECT
TABLE_SCHEMA AS 数据库,
TABLE_NAME AS 表名,
COUNT(*) AS 无索引查询数,
SUM(COUNT_STAR) AS 总执行次数,
SUM(SUM_ROWS_EXAMINED) AS 总扫描行,
-- 平均每次扫描行
ROUND(SUM(SUM_ROWS_EXAMINED) / SUM(COUNT_STAR), 0) AS 均扫描行,
-- 优化建议
CASE
WHEN SUM(SUM_ROWS_EXAMINED) / SUM(COUNT_STAR) > 10000 THEN '立即优化'
WHEN COUNT(*) > 5 THEN '高频无索引'
ELSE '可优化'
END AS 优化建议
FROM (
SELECT
SCHEMA_NAME,
CASE
WHEN DIGEST_TEXT LIKE '% FROM %' THEN
TRIM(BOTH '`' FROM SUBSTRING_INDEX(
SUBSTRING_INDEX(DIGEST_TEXT, 'FROM ', -1),
' ',
1
))
ELSE '未知'
END AS TABLE_NAME,
COUNT_STAR,
SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
AND DIGEST_TEXT LIKE 'SELECT%'
) AS t
WHERE TABLE_NAME != '未知'
GROUP BY TABLE_SCHEMA, TABLE_NAME
ORDER BY 总扫描行 DESC
LIMIT 10;MySQL 查询性能分析
SELECT
SCHEMA_NAME AS 数据库,
-- 提取表名
CASE
WHEN DIGEST_TEXT LIKE '% FROM %' THEN
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(DIGEST_TEXT, 'FROM ', -1),
' ',
1
),
'`',
-1
)
ELSE '未知表'
END AS 涉及主表,
-- 查询模板（简化）
LEFT(
REGEXP_REPLACE(
REGEXP_REPLACE(DIGEST_TEXT, '\\s+', ' '),
'(SELECT|FROM|WHERE|GROUP BY|ORDER BY|LIMIT)',
'\n\\1'
),
200
) AS 查询结构,
-- 执行统计
COUNT_STAR AS 执行次数,
ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS 总耗时_秒,
ROUND(AVG_TIMER_WAIT/1000000000, 2) AS 平均耗时_毫秒,
ROUND(MAX_TIMER_WAIT/1000000000, 2) AS 最大耗时_毫秒,
-- 行统计
FORMAT(SUM_ROWS_EXAMINED, 0) AS 扫描行,
FORMAT(SUM_ROWS_SENT, 0) AS 返回行,
-- 计算平均每次
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS 均扫描行,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS 均返回行,
-- 索引使用
SUM_NO_INDEX_USED AS 无索引次数,
ROUND(SUM_NO_INDEX_USED * 100.0 / COUNT_STAR, 1) AS 无索引占比_百分比,
-- 效率比
ROUND(
CASE
WHEN SUM_ROWS_EXAMINED = 0 THEN 0
ELSE SUM_ROWS_SENT * 100.0 / SUM_ROWS_EXAMINED
END, 2
) AS 返回扫描比_百分比,
-- 时间分布
DATE_FORMAT(FIRST_SEEN, '%H:%i:%s') AS 首次执行,
DATE_FORMAT(LAST_SEEN, '%H:%i:%s') AS 最后执行,
TIMESTAMPDIFF(SECOND, FIRST_SEEN, LAST_SEEN) AS 时间跨度_秒,
-- 自动优化建议
CASE
WHEN ROUND(AVG_TIMER_WAIT/1000000000, 2) > 100 THEN
CASE
WHEN SUM_NO_INDEX_USED > 0 THEN '添加索引'
WHEN SUM_ROWS_EXAMINED / COUNT_STAR > 10000 THEN '优化查询条件'
WHEN DIGEST_TEXT LIKE '%SELECT *%' THEN '避免SELECT *'
WHEN DIGEST_TEXT LIKE '%LIKE %' AND DIGEST_TEXT NOT LIKE '%=%' THEN '避免前模糊匹配'
ELSE '检查执行计划'
END
WHEN SUM_NO_INDEX_USED > 0 THEN '考虑添加索引'
WHEN SUM_ROWS_EXAMINED / SUM_ROWS_SENT > 100 THEN '结果集过大，考虑分页'
ELSE '正常'
END AS 优化建议,
-- 紧急程度
CASE
WHEN ROUND(AVG_TIMER_WAIT/1000000000, 2) > 1000 AND COUNT_STAR > 10 THEN '🔴 立即优化'
WHEN ROUND(AVG_TIMER_WAIT/1000000000, 2) > 100 AND COUNT_STAR > 50 THEN '🟠 高优先级'
WHEN SUM_NO_INDEX_USED * 100.0 / COUNT_STAR > 50 THEN '🟡 中优先级'
ELSE '🟢 低优先级'
END AS 优化优先级
FROM performance_schema.events_statements_summary_by_digest
WHERE LAST_SEEN > DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND SCHEMA_NAME IS NOT NULL
AND DIGEST_TEXT IS NOT NULL
AND COUNT_STAR > 0
AND SCHEMA_NAME NOT IN ('sys', 'mysql', 'performance_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20\GMySQL InnoDB 关键性能指标分析
性能指标
SELECT
-- 指标名称中文
CASE NAME
WHEN 'buffer_pool_reads' THEN '物理磁盘读次数'
WHEN 'buffer_pool_read_requests' THEN '缓冲池读请求数'
WHEN 'buffer_pool_write_requests' THEN '缓冲池写请求数'
WHEN 'buffer_pool_pages_dirty' THEN '脏页数量'
WHEN 'lock_deadlocks' THEN '死锁次数'
WHEN 'lock_timeouts' THEN '锁超时次数'
WHEN 'trx_rseg_history_len' THEN '事务历史长度'
ELSE NAME
END AS 指标名称,
-- 原始值
COUNT AS 原始值,
-- 指标格式化
CASE
WHEN NAME LIKE 'buffer_pool_%' AND COUNT >= 1000000
THEN CONCAT(ROUND(COUNT/1000000, 2), 'M')
WHEN NAME LIKE 'buffer_pool_%' AND COUNT >= 1000
THEN CONCAT(ROUND(COUNT/1000, 1), 'K')
ELSE COUNT
END AS 格式化值,
-- 计算命中率
CASE
WHEN NAME = 'buffer_pool_reads' THEN
(SELECT
CONCAT(
ROUND(
(1 - m1.COUNT /
(SELECT COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'buffer_pool_read_requests')
) * 100, 2
), '%'
)
FROM information_schema.INNODB_METRICS m1
WHERE m1.NAME = 'buffer_pool_reads')
WHEN NAME = 'buffer_pool_read_requests' THEN
(SELECT
CONCAT(
ROUND(
(1 - (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') /
m2.COUNT) * 100, 2
), '%'
)
FROM information_schema.INNODB_METRICS m2
WHERE m2.NAME = 'buffer_pool_read_requests')
ELSE 'N/A'
END AS 相关比率,
-- 健康状态评估
CASE
WHEN NAME = 'buffer_pool_pages_dirty' AND COUNT > 10000 THEN '🟡 注意'
WHEN NAME = 'buffer_pool_pages_dirty' AND COUNT > 50000 THEN '🔴 警告'
WHEN NAME = 'lock_deadlocks' AND COUNT > 10 THEN '🔴 警告'
WHEN NAME = 'lock_timeouts' AND COUNT > 100 THEN '🟡 注意'
WHEN NAME = 'trx_rseg_history_len' AND COUNT > 1000000 THEN '🟡 注意'
ELSE '🟢 正常'
END AS 健康状态,
-- 优化建议
CASE
WHEN NAME = 'buffer_pool_pages_dirty' AND COUNT > 50000 THEN '增加innodb_io_capacity，检查checkpoint'
WHEN NAME = 'lock_deadlocks' AND COUNT > 10 THEN '检查事务隔离级别和索引'
WHEN NAME = 'lock_timeouts' AND COUNT > 100 THEN '优化查询，减少锁竞争'
WHEN NAME = 'trx_rseg_history_len' AND COUNT > 1000000 THEN '检查长事务，考虑purge线程优化'
ELSE '正常'
END AS 优化建议
FROM information_schema.INNODB_METRICS
WHERE NAME IN (
'buffer_pool_reads',
'buffer_pool_read_requests',
'buffer_pool_write_requests',
'buffer_pool_pages_dirty',
'lock_deadlocks',
'lock_timeouts',
'trx_rseg_history_len'
)
ORDER BY FIELD(NAME,
'buffer_pool_reads',
'buffer_pool_read_requests',
'buffer_pool_write_requests',
'buffer_pool_pages_dirty',
'lock_deadlocks',
'lock_timeouts',
'trx_rseg_history_len'
);建议
-- 获取所有相关指标并计算关键比率
SELECT
'📊 缓冲池性能' AS 类别,
-- 缓冲池命中率
CONCAT(
ROUND(
(1 -
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0)
) * 100, 2
), '%'
) AS 缓冲池命中率,
-- 物理读比率
CONCAT(
ROUND(
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') * 100.0 /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0), 2
), '%'
) AS 物理读比率,
-- 脏页比例（假设总缓冲池页数为4193792）
CONCAT(
ROUND(
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_pages_dirty') * 100.0 / 4193792, 2
), '%'
) AS 脏页比例,
-- 读写比例
CONCAT(
ROUND(
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_write_requests') * 100.0 /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0), 1
), '%'
) AS 读写比
UNION ALL
SELECT
'🔒 锁与事务' AS 类别,
-- 死锁和超时
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_deadlocks') AS 死锁次数,
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_timeouts') AS 锁超时次数,
(SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'trx_rseg_history_len') AS 事务历史长度,
NULL AS 读写比
UNION ALL
SELECT
'📈 性能评估' AS 类别,
-- 整体评估
CASE
WHEN (1 - (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0)) < 0.95
THEN '🔴 命中率过低'
WHEN (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_pages_dirty') > 50000
THEN '🔴 脏页过多'
WHEN (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_deadlocks') > 10
THEN '🔴 死锁频繁'
ELSE '🟢 性能良好'
END AS 整体状态,
-- 建议操作
CASE
WHEN (1 - (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0)) < 0.95
THEN '增加缓冲池大小，优化查询'
WHEN (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_pages_dirty') > 50000
THEN '提高innodb_io_capacity，优化checkpoint'
WHEN (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_deadlocks') > 10
THEN '检查事务代码，优化索引'
ELSE '保持监控'
END AS 建议操作,
-- 紧急程度
CASE
WHEN (1 - (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0)) < 0.90
OR (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_deadlocks') > 20
THEN '🔴 紧急'
WHEN (1 - (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_reads') /
NULLIF((SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_read_requests'), 0)) < 0.95
OR (SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'buffer_pool_pages_dirty') > 30000
THEN '🟡 警告'
ELSE '🟢 正常'
END AS 紧急程度,
NULL AS 读写比;MySQL 临时表使用分析
分析
SELECT
SCHEMA_NAME AS 数据库,
LEFT(DIGEST_TEXT, 150) AS 查询模板,
-- 执行统计
COUNT_STAR AS 执行次数,
-- 临时表使用统计
SUM_CREATED_TMP_TABLES AS 临时表总数,
SUM_CREATED_TMP_DISK_TABLES AS 磁盘临时表数,
-- 计算磁盘临时表比例
ROUND(
CASE
WHEN SUM_CREATED_TMP_TABLES = 0 THEN 0
ELSE SUM_CREATED_TMP_DISK_TABLES * 100.0 / SUM_CREATED_TMP_TABLES
END, 2
) AS 磁盘临时表比例_百分比,
-- 排序统计
SUM_SORT_ROWS AS 排序行数,
SUM_SORT_MERGE_PASSES AS 排序合并次数,
SUM_SORT_SCAN AS 排序扫描次数,
-- 计算平均每次排序行数
ROUND(SUM_SORT_ROWS / COUNT_STAR, 0) AS 均排序行,
-- 其他性能指标
FORMAT(SUM_ROWS_EXAMINED, 0) AS 扫描行,
FORMAT(SUM_ROWS_SENT, 0) AS 返回行,
-- 时间指标
ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS 总耗时_秒,
ROUND(AVG_TIMER_WAIT/1000000000, 2) AS 平均耗时_毫秒,
ROUND(MAX_TIMER_WAIT/1000000000, 2) AS 最大耗时_毫秒,
-- 风险等级评估
CASE
WHEN SUM_CREATED_TMP_DISK_TABLES * 100.0 / NULLIF(SUM_CREATED_TMP_TABLES, 0) > 50
AND ROUND(AVG_TIMER_WAIT/1000000000, 2) > 100 THEN '🔴 高风险'
WHEN SUM_CREATED_TMP_DISK_TABLES * 100.0 / NULLIF(SUM_CREATED_TMP_TABLES, 0) > 30 THEN '🟡 中风险'
WHEN SUM_CREATED_TMP_DISK_TABLES > 0 THEN '🟢 低风险'
ELSE '正常'
END AS 风险等级,
-- 优化建议
CASE
WHEN SUM_CREATED_TMP_DISK_TABLES * 100.0 / NULLIF(SUM_CREATED_TMP_TABLES, 0) > 50 THEN
'增加tmp_table_size和max_heap_table_size'
WHEN SUM_SORT_ROWS / COUNT_STAR > 10000 THEN
'为ORDER BY字段添加索引'
WHEN DIGEST_TEXT LIKE '%GROUP BY%' AND SUM_CREATED_TMP_DISK_TABLES > 0 THEN
'检查GROUP BY字段索引'
WHEN DIGEST_TEXT LIKE '%UNION%' THEN
'考虑使用UNION ALL替代UNION'
WHEN SUM_SORT_MERGE_PASSES > 0 THEN
'增加sort_buffer_size'
ELSE '监控观察'
END AS 优化建议
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
AND SCHEMA_NAME IS NOT NULL
AND COUNT_STAR > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 15\G优化
SELECT
SCHEMA_NAME AS 数据库,
-- 查询类型识别
CASE
WHEN DIGEST_TEXT LIKE '%GROUP BY%' AND DIGEST_TEXT LIKE '%ORDER BY%' THEN '分组排序'
WHEN DIGEST_TEXT LIKE '%GROUP BY%' THEN '分组查询'
WHEN DIGEST_TEXT LIKE '%ORDER BY%' THEN '排序查询'
WHEN DIGEST_TEXT LIKE '%DISTINCT%' THEN '去重查询'
WHEN DIGEST_TEXT LIKE '%UNION%' THEN '联合查询'
WHEN DIGEST_TEXT LIKE '%JOIN%' THEN '连接查询'
ELSE '其他'
END AS 查询类型,
LEFT(DIGEST_TEXT, 120) AS 查询模板,
-- 临时表统计
CONCAT(
SUM_CREATED_TMP_DISK_TABLES,
'/',
SUM_CREATED_TMP_TABLES
) AS 磁盘_临时表_总数,
ROUND(
SUM_CREATED_TMP_DISK_TABLES * 100.0 /
NULLIF(SUM_CREATED_TMP_TABLES, 0), 1
) AS 磁盘临时表占比_百分比,
-- 排序统计
FORMAT(SUM_SORT_ROWS, 0) AS 排序行,
FORMAT(SUM_SORT_ROWS / COUNT_STAR, 0) AS 均排序行,
-- 内存使用估算（临时表）
CONCAT(
ROUND(
(SUM_SORT_ROWS / COUNT_STAR) * 100 / 1024 / 1024, 2
), ' MB'
) AS 预估临时表大小,
-- 当前配置检查
(SELECT @@tmp_table_size) AS 当前tmp_table_size,
(SELECT @@max_heap_table_size) AS 当前max_heap_table_size,
(SELECT @@sort_buffer_size) AS 当前sort_buffer_size,
-- 配置建议
CASE
WHEN (SUM_SORT_ROWS / COUNT_STAR) * 100 > @@tmp_table_size THEN
CONCAT('建议tmp_table_size > ',
FORMAT((SUM_SORT_ROWS / COUNT_STAR) * 100 * 1.2, 0))
WHEN (SUM_SORT_ROWS / COUNT_STAR) * 100 > @@max_heap_table_size THEN
CONCAT('建议max_heap_table_size > ',
FORMAT((SUM_SORT_ROWS / COUNT_STAR) * 100 * 1.2, 0))
ELSE '配置正常'
END AS 配置调整建议,
-- 紧急程度
CASE
WHEN SUM_CREATED_TMP_DISK_TABLES * 100.0 /
NULLIF(SUM_CREATED_TMP_TABLES, 0) > 80 THEN '🔴 立即优化'
WHEN SUM_CREATED_TMP_DISK_TABLES * 100.0 /
NULLIF(SUM_CREATED_TMP_TABLES, 0) > 50 THEN '🟡 建议优化'
ELSE '🟢 监控观察'
END AS 优化优先级
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
AND SCHEMA_NAME IS NOT NULL
AND COUNT_STAR > 10
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 12\G
浙公网安备 33010602011771号