1
-- 查看当前配置
2
SHOW VARIABLES LIKE '%slow_query%';
3
SHOW VARIABLES LIKE '%long_query_time%';
4

5
-- 动态开启
6
SET GLOBAL slow_query_log = 'ON';
7
SET GLOBAL long_query_time = 2;                            -- 阈值，单位：秒
8
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
9

10
-- 记录未命中索引的查询（不管是否超时）
11
SET GLOBAL log_queries_not_using_indexes = 'ON';
12

13
-- 记录慢管理语句（ALTER TABLE 等）
14
SET GLOBAL log_slow_admin_statements = 'ON';

1
[mysqld]
2
slow_query_log          = 1
3
slow_query_log_file     = /var/log/mysql/slow.log
4
long_query_time         = 2
5
log_queries_not_using_indexes = 1
6
log_slow_admin_statements     = 1

1
-- 仅对当前会话生效
2
SET profiling = 1;
3

4
-- 执行若干 SQL 后查看列表
5
SHOW PROFILES;

1
-- 查看最近一次查询各阶段耗时
2
SHOW PROFILE;
3

4
-- 查看指定 Query_ID
5
SHOW PROFILE FOR QUERY 3;
6

7
-- 同时展示 CPU 和块 IO 消耗
8
SHOW PROFILE CPU, BLOCK IO FOR QUERY 3;
9

10
-- 展示全部类型
11
SHOW PROFILE ALL FOR QUERY 3;

1
-- 标准表格输出
2
EXPLAIN SELECT * FROM users WHERE id = 1;
3

4
-- JSON 格式，包含 cost 信息
5
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;
6

7
-- 树形结构（MySQL 8.0.18+）
8
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;
9

10
-- 实际执行并附带真实统计数据（MySQL 8.0.18+）
11
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

1
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id)
2
FROM users u
3
LEFT JOIN orders o ON u.id = o.user_id
4
GROUP BY u.id;

1
-> Table scan on <temporary>  (cost=2.50..5.00 rows=3) (actual time=0.048..0.052 rows=3 loops=1)
2
    -> Aggregate using temporary table  (actual time=0.043..0.043 rows=3 loops=1)
3
        -> Nested loop left join  (cost=1.25 rows=3) (actual time=0.019..0.030 rows=4 loops=1)

1
SET GLOBAL general_log = 'ON';
2
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
3

4
SHOW VARIABLES LIKE 'general_log%';

1
-- 查看路径
2
SHOW VARIABLES LIKE 'log_error';
3

4
-- 日志详细程度（1=错误，2=警告，3=通知）
5
SHOW VARIABLES LIKE 'log_error_verbosity';

1
SHOW VARIABLES LIKE 'log_bin%';
2

3
-- 查看所有 binlog 文件
4
SHOW BINARY LOGS;
5

6
-- 查看指定文件的事件
7
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;

1
-- 总慢查询数（自启动以来累计）
2
SHOW STATUS LIKE 'Slow_queries';
3

4
-- 总请求数
5
SHOW STATUS LIKE 'Questions';
6

7
-- 临时表相关（大量磁盘临时表是问题信号）
8
SHOW STATUS LIKE 'Created_tmp%';
9

10
-- 排序操作
11
SHOW STATUS LIKE 'Sort%';
12

13
-- 当前连接数
14
SHOW STATUS LIKE 'Threads_connected';
15
SHOW VARIABLES LIKE 'max_connections';
16

17
-- 查看线程详情（找长时间运行的查询）
18
SHOW PROCESSLIST;
19

20
-- InnoDB 锁等待详情
21
SHOW ENGINE INNODB STATUS;

1
-- 查询耗时最长的 SQL TOP 10
2
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1e12 AS avg_sec
3
FROM performance_schema.events_statements_summary_by_digest
4
ORDER BY AVG_TIMER_WAIT DESC
5
LIMIT 10;
6

7
-- 查看当前等待事件
8
SELECT * FROM performance_schema.events_waits_current
9
WHERE EVENT_NAME != 'idle';
10

11
-- 查看锁等待
12
SELECT * FROM performance_schema.data_lock_waits;

1
-- 查看各表大小（MB）
2
SELECT
3
    table_schema                AS `数据库`,
4
    table_name                  AS `表名`,
5
    ROUND(data_length  / 1024 / 1024, 2) AS `数据(MB)`,
6
    ROUND(index_length / 1024 / 1024, 2) AS `索引(MB)`,
7
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `总计(MB)`
8
FROM information_schema.tables
9
WHERE table_schema = 'your_database'
10
ORDER BY (data_length + index_length) DESC;
11

12
-- 查看索引信息
13
SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY, NON_UNIQUE
14
FROM information_schema.STATISTICS
15
WHERE TABLE_SCHEMA = 'your_database'
16
  AND TABLE_NAME   = 'users'
17
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

1
-- ❌ 问题查询：SELECT * + LIKE '%xxx%' + LEFT JOIN 无 LIMIT
2
SELECT u.*, o.order_date, o.amount
3
FROM users u
4
LEFT JOIN orders o ON u.id = o.user_id
5
WHERE u.email LIKE '%@gmail.com'
6
ORDER BY o.order_date DESC;
7

8
-- ✅ 优化：指定列、改 INNER JOIN、加 LIMIT
9
SELECT u.id, u.name, u.email, o.order_date, o.amount
10
FROM users u
11
INNER JOIN orders o ON u.id = o.user_id
12
WHERE u.email LIKE 'support@%'      -- 改为前缀匹配，可命中索引
13
ORDER BY o.order_date DESC
14
LIMIT 100;

1
-- 查看执行计划
2
EXPLAIN SELECT * FROM orders
3
WHERE user_id = 123 AND order_date > '2023-01-01';
4
-- 若 key 为 NULL，说明缺少索引
5

6
-- 创建复合索引（注意顺序：等值列在前）
7
CREATE INDEX idx_user_date ON orders(user_id, order_date);
8

9
-- 再次 EXPLAIN 确认 type 变为 range，key 变为 idx_user_date
10
EXPLAIN SELECT * FROM orders
11
WHERE user_id = 123 AND order_date > '2023-01-01';

1
-- ❌ IN 子查询：优化器可能生成较差的计划
2
SELECT * FROM users
3
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
4

5
-- ✅ 改写为 JOIN：更可控
6
SELECT DISTINCT u.*
7
FROM users u
8
INNER JOIN orders o ON u.id = o.user_id
9
WHERE o.amount > 1000;

1
-- 关闭慢查询日志
2
SET GLOBAL slow_query_log = 'OFF';
3
-- （Shell 层备份并清空文件）
4
SET GLOBAL slow_query_log = 'ON';
5

6
-- 清理通用查询日志（同理）
7
SET GLOBAL general_log = 'OFF';
8
SET GLOBAL general_log = 'ON';