How Ops Engineers Fix MySQL Slow Queries: A Step‑by‑Step Guide

Background and Applicable Scenarios

MySQL is the most common database for business systems. When CPU spikes, connections are exhausted, or query latency increases, the following steps help locate and resolve slow‑query problems.

CPU or memory suddenly maxed out, unknown offending SQL

Interface response time increases, suspect slow DB queries

Slow‑query log contains many statements, unsure which to optimize

Newly deployed SQL may have performance issues, need evaluation

Database configuration parameters are unknown

Replication lag suddenly grows

Connection pool appears insufficient or wasteful

MySQL Architecture Overview

Thread Model

InnoDB 主线程（负责刷新脏页、合并缓冲池）
InnoDB 读线程（负责预读）
InnoDB 写线程（负责刷脏页）
InnoDB 脏页刷新线程（page cleaner）
MySQL 连接管理线程（负责接收连接）
查询缓存（MySQL 8.0 已移除）

Connection flow: client → connection pool → parser → optimizer → executor → storage engine (InnoDB/MyISAM).

# 查看当前 MySQL 版本
mysql --version
mysql -u root -p -e "SELECT VERSION();"

# 查看 MySQL 进程
ps aux | grep mysqld
systemctl status mysql
systemctl status mysqld

# 查看 MySQL 配置（前 20 行）
mysql -u root -p -e "SHOW VARIABLES;" | head -20

# 查看最大连接数
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

InnoDB Core Concepts

Buffer Pool：缓存表数据和索引，减小磁盘 IO
redo log：事务日志，保证宕机后数据不丢失
undo log：回滚日志，支持事务回滚
change buffer：二级索引变更缓存，减少随机 IO
自适应哈希索引：InnoDB 自动优化的哈希索引

# 查看 InnoDB 状态
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

# 查看 Buffer Pool 大小（通常设置为机器内存的 60‑80%）
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# 查看 Buffer Pool 使用情况（MySQL 5.6+）
mysql -u root -p -e "SELECT pool_id, pool_size, free_buffers, database_pages, modified_db_pages FROM information_schema.INNODB_BUFFER_POOL_STATS;"

# 查看 redo log 大小和状态
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_log_file%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';"

Slow‑Query Log Configuration and Enabling

Enable Slow Query Log

# 临时开启（MySQL 重启后失效）
mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON';"
mysql -u root -p -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"
mysql -u root -p -e "SET GLOBAL long_query_time = 1;"  # 记录超过 1 秒的查询

# 永久开启（修改配置文件）
# /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1  # 记录未使用索引的查询
log_slow_admin_statements = 1       # 记录管理操作

# 创建日志目录并授权
mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql
touch /var/log/mysql/slow.log

# 重启 MySQL 使配置生效
systemctl restart mysql

# 验证配置
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'long_query_time';"

Analyzing with mysqldumpslow

# 输出最慢的 10 条
mysqldumpslow -t 10 /var/log/mysql/slow.log
# 按平均查询时间排序
mysqldumpslow -s at /var/log/mysql/slow.log
# 按总查询时间排序
mysqldumpslow -s t /var/log/mysql/slow.log
# 按锁时间排序
mysqldumpslow -s l /var/log/mysql/slow.log
# 忽略锁时间的排序
mysqldumpslow -s ar /var/log/mysql/slow.log
# 示例输出
# Count: 1  Time=5.23s (5s)  Lock=0.00s (0s)  Rows=1000000 (1000000)  SELECT * FROM orders
# Count: 1  Time=3.21s (3s)  Lock=0.00s (0s)  Rows=50000 (50000)  SELECT * FROM products

Analyzing with pt‑query‑digest (Percona Toolkit)

# 安装（CentOS）
yum install percona-toolkit -y
# 安装（Ubuntu/Debian）
apt-get install percona-toolkit -y

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log
# 只分析最近 24 小时的慢查询
pt-query-digest --since=24h /var/log/mysql/slow.log
# 指定时间段
pt-query-digest --since='2026-05-29 00:00:00' --until='2026-05-29 12:00:00' /var/log/mysql/slow.log
# 远程 MySQL
pt-query-digest --max-review 100 h=192.168.1.100,u=root,p=password --since=24h
# 输出到文件
pt-query-digest /var/log/mysql/slow.log > /tmp/query_analysis.txt
# 统计 SELECT 类型分布
pt-query-digest --filter '$event->{fingerprint} =~ m/^SELECT/i' /var/log/mysql/slow.log

pt‑query‑digest 输出示例：

# 400ms user=root host=localhost db=mydb
# Query_time: 5.234  Lock_time: 0.001  Rows_sent: 100  Rows_examined: 500000
select * from orders where status = 'completed' and created_at > '2026-01-01';
# 解读：
# Query_time: 5.234 秒 – 这条 SQL 执行了 5.234 秒
# Lock_time: 0.001 秒 – 表锁等了 0.001 秒
# Rows_sent: 100 – 返回了 100 行
# Rows_examined: 500000 – 扫描了 50 万行，典型的需要加索引场景

EXPLAIN Execution‑Plan Analysis

Basic Usage

-- 语法分析（不执行）
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 详细执行计划（MySQL 5.6+）
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 查看所有字段（MySQL 5.7+）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

Explain Output Fields

-- 创建测试表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL,
    user_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    UNIQUE INDEX idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 分析这条 SQL
EXPLAIN SELECT * FROM orders WHERE user_id = 1000 AND status = 'completed' AND created_at > '2026-01-01' ORDER BY created_at DESC LIMIT 20;

Key fields explained:

id: execution order number
select_type: SIMPLE / SUBQUERY / UNION …
table: table name
type: connection type (system > const > eq_ref > ref > range > index > ALL)
possible_keys: indexes that could be used
key: index actually used
key_len: length of the used index (shorter is better)
ref: column compared with the index
rows: estimated rows scanned (smaller is better)
filtered: percentage of rows that match the condition
Extra: additional info (Using where, Using filesort, Using temporary, Using index …)

type Field Details (Best → Worst)

system：表只有一行（系统表），最好
const：最多一行匹配，通常是主键或唯一索引查询
eq_ref：使用主键或唯一索引，只返回一行
ref：普通索引查询，返回多行
ref_or_null：类似 ref，但包含 NULL 查询
index_merge：使用多个索引合并
unique_subquery：子查询返回唯一值
index_subquery：子查询返回非唯一值
range：索引范围查询（BETWEEN、IN、>、<）
index：全索引扫描（只遍历索引，不回表）
ALL：全表扫描（最差，需要优化）

Extra Field Details

Using filesort：无法用索引排序，需要额外排序操作
Using temporary：需要临时表，常见于 DISTINCT、GROUP BY、UNION
Using index：覆盖索引，直接用索引返回数据，不需要回表
Using index condition：索引下推，用索引过滤部分条件
Using where：需要回表检查 WHERE 条件
Using join buffer：连接时使用 join buffer（通常意味着连接字段没索引）
Impossible WHERE：WHERE 条件永远为 FALSE
Select tables optimized away：优化器直接返回结果

Common SQL Issues and Analysis

-- 1. 全表扫描（type=ALL）
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- 问题：amount 字段没有索引
-- 解决：ALTER TABLE orders ADD INDEX idx_amount (amount);

-- 2. Using filesort（需要排序）
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- 问题：created_at 有索引但查询条件导致索引失效，或 ORDER BY 字段没有索引
-- 解决：SELECT * FROM orders WHERE status='completed' ORDER BY created_at DESC LIMIT 10;

-- 3. Using temporary（需要临时表）
EXPLAIN SELECT DISTINCT user_id FROM orders WHERE status='completed';
-- 问题：user_id 没有索引或优化器选择不当
-- 解决：ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 4. 索引失效（明明有索引但没用上）
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 问题：对索引字段使用函数导致索引失效
-- 解决：SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 5. 多表连接顺序不当
EXPLAIN SELECT a.*, b.name FROM orders a JOIN users b ON a.user_id = b.id WHERE a.status='pending';
-- 注意查看 id 字段，数字大的先执行

Index Optimization

Design Principles

1. 为频繁出现在 WHERE 条件的字段加索引
2. 为频繁出现在 ORDER BY、GROUP BY 的字段加索引
3. 高区分度字段适合加索引（性别等低区分度字段不适合）
4. 复合索引遵循最左前缀原则
5. 索引不是越多越好，维护有开销
6. 存储空间有限时，优先在高频查询表上建索引

Composite Index Design

-- 场景：经常查询某个用户的订单，按状态和时间排序
SELECT * FROM orders WHERE user_id = 1000 AND status = 'completed' ORDER BY created_at DESC;

-- 正确的复合索引（最左前缀）
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

-- 验证索引是否被使用
EXPLAIN SELECT * FROM orders WHERE user_id = 1000 AND status = 'completed' ORDER BY created_at DESC;

-- 最左前缀验证示例
-- 1. user_id = 1000               → 使用索引
-- 2. user_id = 1000 AND status='completed' → 使用索引
-- 3. user_id = 1000 AND status='completed' AND created_at > '2026-01-01' → 使用索引
-- 4. status='completed'           → 不使用索引（未从最左开始）
-- 5. user_id > 1000               → 使用索引，但范围查询后不走索引

Viewing Existing Indexes

-- 查看表的所有索引
SHOW INDEX FROM orders;

-- 格式化输出（MySQL 5.6+）
SHOW INDEX FROM orders\G

-- 从 information_schema 查看
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Index Analysis Tools

# 使用 EXPLAIN 分析慢查询
# 1. 从慢查询日志或 pt‑query‑digest 找到需要优化的 SQL
# 2. 用 EXPLAIN 分析
mysql -u root -p -e "EXPLAIN SELECT ..." database

# 使用 optimizer_trace（MySQL 5.6+）
mysql -u root -p -e "SET optimizer_trace='enabled=on';"
mysql -u root -p -e "SELECT ..." database
mysql -u root -p -e "SELECT * FROM information_schema.OPTIMIZER_TRACE\G"
mysql -u root -p -e "SET optimizer_trace='enabled=off';"

# 使用 performance_schema（MySQL 5.6+）
-- 开启监控
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name IN ('statements_digest');

-- 查看最耗时的 SQL
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS sum_seconds,
       AVG_TIMER_WAIT/1000000000000 AS avg_seconds, SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY sum_seconds DESC
LIMIT 10;

Common Slow‑Query Optimization Cases

Case 1: SELECT * Hazard

-- 问题 SQL
SELECT * FROM orders WHERE user_id = 1000;

-- EXPLAIN 分析
EXPLAIN SELECT * FROM orders WHERE user_id = 1000;
-- type: ref, key: idx_user_id, rows: 50, Extra: Using index condition
-- 虽然走了索引，但 SELECT * 会回表读取所有列

-- 优化：只查询需要的列
SELECT id, order_no, status, amount, created_at FROM orders WHERE user_id = 1000;

-- 更好：创建覆盖索引避免回表
ALTER TABLE orders ADD INDEX idx_user_cover (user_id, id, order_no, status, amount, created_at);
EXPLAIN SELECT id, order_no, status, amount, created_at FROM orders WHERE user_id = 1000;
-- Extra: Using index（覆盖索引）

Case 2: Pagination Optimization

-- 深分页问题
SELECT * FROM orders WHERE status='completed' ORDER BY id DESC LIMIT 1000000, 20;
-- EXPLAIN 显示 type: ALL，rows: 5000000，Extra: Using filesort

-- 优化 1：利用主键范围
SELECT * FROM orders WHERE id > 1000000 AND status='completed' ORDER BY id DESC LIMIT 20;

-- 优化 2：延迟关联
SELECT o.* FROM orders o JOIN (
    SELECT id FROM orders WHERE status='completed' ORDER BY id DESC LIMIT 1000000, 20
) t ON o.id = t.id;

-- 优化 3：游标分页（适合大数据量）
SELECT * FROM orders WHERE status='completed' AND id < 1000020 ORDER BY id DESC LIMIT 20;

Case 3: COUNT(*) Optimization

-- 问题 SQL
SELECT COUNT(*) FROM orders WHERE status='pending';
-- EXPLAIN 显示 type: ALL，rows: 5000000

-- 优化 1：添加索引
ALTER TABLE orders ADD INDEX idx_status (status);
EXPLAIN SELECT COUNT(*) FROM orders WHERE status='pending';
-- type: ref，rows: 500

-- 优化 2：使用 information_schema 近似值
SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_database' AND TABLE_NAME='orders';

-- 优化 3：维护汇总表（实时性要求不高）
CREATE TABLE orders_status_count (
    status VARCHAR(32) PRIMARY KEY,
    cnt BIGINT NOT NULL DEFAULT 0
);
-- 定时更新汇总表（示例）
UPDATE orders_status_count t JOIN (
    SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status
) s ON t.status = s.status SET t.cnt = s.cnt;

Case 4: JOIN Optimization

-- 多表 JOIN 示例
SELECT o.id, o.order_no, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status='completed' AND p.category='electronics'
LIMIT 100;

-- EXPLAIN 可能出现全表扫描
-- 优化原则：
-- 1. 确保连接字段都有索引
-- 2. 过滤数据量大的表优先执行
-- 3. 小表驱动大表（MySQL 优化器通常自动选择）

-- 查看各表数据量
SELECT COUNT(*) FROM orders;      -- 500万
SELECT COUNT(*) FROM users;       -- 100万
SELECT COUNT(*) FROM order_items;-- 2000万
SELECT COUNT(*) FROM products;   -- 10万

-- 调整 JOIN 顺序让小表先驱动
SELECT o.id, o.order_no, u.name, u.email
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
JOIN users u ON o.user_id = u.id
WHERE p.category='electronics' AND o.status='completed'
LIMIT 100;

-- 确保索引完整
ALTER TABLE order_items ADD INDEX idx_order_product (order_id, product_id);
ALTER TABLE orders ADD INDEX idx_status (status);

Case 5: OR Condition Optimization

-- OR 条件可能导致索引失效
SELECT * FROM users WHERE name='张三' OR email='[email protected]' OR phone='13800138000';
-- EXPLAIN 可能显示 type: ALL

-- 优化 1：改写为 UNION
SELECT * FROM users WHERE name='张三'
UNION
SELECT * FROM users WHERE email='[email protected]'
UNION
SELECT * FROM users WHERE phone='13800138000';

-- 优化 2：为每个列单独建索引
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD INDEX idx_phone (phone);

-- 优化 3：使用 IN（语义更清晰）
SELECT * FROM users WHERE name IN ('张三') OR email IN ('[email protected]') OR phone IN ('13800138000');

Case 6: IN + Subquery Optimization

-- IN 子查询示例
SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE created_at > '2026-01-01'
);
-- EXPLAIN 可能先执行子查询，再执行外层查询

-- 优化 1：使用 EXISTS
SELECT * FROM orders o WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id AND u.created_at > '2026-01-01'
);

-- 优化 2：使用 JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.created_at > '2026-01-01';

-- 优化 3：MySQL 8.0 可能自动选择 MaterializeLookup 策略

MySQL Configuration Parameter Tuning

Core Parameters

# 连接到 MySQL
mysql -u root -p

# 查看关键参数值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_log_file_size';

常用调参（放入 my.cnf ）：

[mysqld]
# 内存配置（机器内存的 60‑80%）
innodb_buffer_pool_size = 8G

# 连接配置
max_connections = 2000
max_allowed_packet = 64M

# 日志配置（redo log）
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1  # 1=最安全，2=性能最佳，0=折中

# 临时表和排序缓冲
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M

# 线程、表缓存
thread_cache_size = 64
table_open_cache = 4000

# InnoDB 其他设置
innodb_page_cleaners = 4  # 通常为 CPU 核心数 / 4
innodb_file_per_table = 1
innodb_thread_concurrency = 0  # 自动设置为 CPU 核心数
log_queries_not_using_indexes = 1

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Parameter Adjustment Verification

# 验证修改后的参数
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# 查看 Buffer Pool 使用情况
mysql -u root -p -e "SELECT ROUND(POOL_SIZE * PAGE_SIZE / 1024 / 1024, 2) AS pool_size_mb,
               ROUND(FREE_BUFFERS * PAGE_SIZE / 1024 / 1024, 2) AS free_mb,
               ROUND(DATABASE_PAGES * PAGE_SIZE / 1024 / 1024, 2) AS used_mb
        FROM information_schema.INNODB_BUFFER_POOL_STATS WHERE pool_id = 0;"

# 查看连接数使用情况
mysql -u root -p -e "SELECT MAX_USED_CONNECTIONS,
               MAX_USED_CONNECTIONS / MAX_CONNECTIONS * 100 AS usage_pct,
               THREADS_CONNECTED,
               THREADS_RUNNING
        FROM information_schema.GLOBAL_STATUS
        WHERE VARIABLE_NAME IN ('Max_used_connections', 'Threads_connected', 'Threads_running');"

# 查看当前进程列表
mysql -u root -p -e "SHOW PROCESSLIST;"
mysql -u root -p -e "SHOW FULL PROCESSLIST;"

Configuration Change Procedure

# 1. 备份原配置文件
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak.$(date +%Y%m%d)

# 2. 编辑配置文件
vim /etc/mysql/my.cnf

# 3. 检查语法
mysqld --validate-config

# 4. 动态修改可在线生效的参数（示例）
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 8589934592;"
# 5. 需要重启的参数执行完整重启
systemctl restart mysql

# 6. 验证新设置
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# 7. 监控错误日志
tail -f /var/log/mysql/error.log

Master‑Slave Replication and Lag Monitoring

View Replication Status

# MySQL 5.7
SHOW SLAVE STATUS\G

# MySQL 8.0+
SHOW REPLICA STATUS\G

# 关键字段（两版本通用）
-- Slave_IO_Running / Replica_IO_Running: IO 线程状态
-- Slave_SQL_Running / Replica_SQL_Running: SQL 线程状态
-- Seconds_Behind_Master / Seconds_Behind_Source: 延迟秒数
-- Relay_Log_Pos / Replica_Log_Pos: 中继日志位置
-- Exec_Master_Log_Pos: 主库 binlog 位置
-- Last_Error: 最近错误信息

Typical Lag Causes and Diagnosis

# 1. 大事务导致延迟
-- 监控：Seconds_Behind_Master 突然增大
SHOW PROCESSLIST;  -- 找到 SQL 线程并查看正在执行的语句

# 2. 从库负载过高
-- 监控 CPU、IO 使用率
SHOW STATUS LIKE 'Handler_read%';

# 3. 网络延迟
-- 可启用压缩传输
STOP REPLICA;
SET GLOBAL slave_compressed_protocol = 1;
START REPLICA;

# 4. 大表 DDL 导致阻塞
-- 使用 pt-online-schema-change 或 gh-ost 在线加索引
pt-online-schema-change --alter "ADD INDEX idx_name (name)" --user=root --password=xxx D=mydb,t=orders --execute;

# 5. 错误导致跳过
SHOW REPLICA STATUS\G  -- 查看 Last_Errno 和 Last_Error

Replication Lag Monitoring SQL

# MySQL 5.7
SHOW SLAVE STATUS\G   -- 查看 Seconds_Behind_Master

# MySQL 8.0
SHOW REPLICA STATUS\G

# 更详细的 performance_schema 查询（MySQL 8.0+）
SELECT slave_server_id, source_uuid, service_state, last_heartbeat_timestamp,
       received_heartbeat_set, last_error_number, last_error_message
FROM performance_schema.replication_connection_status\G;

SELECT slave_server_id, service_state, count_transactions_retries
FROM performance_schema.replication_applier_status\G;

Performance Issue Troubleshooting Flow

General Diagnosis Steps for Sudden Slowdowns

1. 查看当前连接和查询
   SHOW PROCESSLIST;
   SHOW FULL PROCESSLIST;

2. 检查慢查询日志
   mysqldumpslow -t 10 /var/log/mysql/slow.log;

3. 查看锁等待
   SELECT * FROM information_schema.INNODB_LOCK_WAITS;
   SELECT * FROM information_schema.INNODB_TRX;

4. 检查资源使用
   SHOW STATUS LIKE 'Threads%';
   SHOW STATUS LIKE 'Connections';

5. 查看表锁统计
   SHOW STATUS LIKE 'Table_locks%';

Real‑Time Diagnostic Commands

# 当前执行的 SQL
mysql -u root -p -e "SHOW PROCESSLIST\G"
mysql -u root -p -e "SHOW FULL PROCESSLIST\G"

# 锁等待详情
mysql -u root -p -e "SELECT r.trx_id, r.trx_mysql_thread_id, r.trx_query,
                      b.blocking_query, b.blocking_id
               FROM information_schema.INNODB_TRX r
               LEFT JOIN (SELECT trx_id, trx_mysql_thread_id AS blocking_id,
                                 trx_query AS blocking_query
                          FROM information_schema.INNODB_TRX) b
               ON b.blocking_id = r.trx_mysql_thread_id
               WHERE r.trx_state = 'LOCK WAIT';"

# 连接数统计
mysql -u root -p -e "SELECT VARIABLE_NAME, VARIABLE_VALUE
               FROM information_schema.GLOBAL_STATUS
               WHERE VARIABLE_NAME IN ('MAX_USED_CONNECTIONS', 'THREADS_CONNECTED');"

# Buffer Pool 命中率
mysql -u root -p -e "SELECT POOL_ID, PAGES_DATA, PAGES_DIRTY, PAGES_FLUSH,
                      NUMBER_PAGES_GET, HASH_SCANS, HASH_ROWS_GET,
                      HASH_ROWS_UPDATED, HASH_ROWS_DELETED
               FROM information_schema.INNODB_BUFFER_PAGE;"

# 临时表使用情况
mysql -u root -p -e "SELECT SUM(CREATED_TMP_TABLES) AS created_tmp_tables,
                      SUM(CREATED_TMP_DISK_TABLES) AS created_tmp_disk_tables,
                      SUM(CREATED_TMP_FILES) AS created_tmp_files
               FROM information_schema.GLOBAL_STATUS
               WHERE VARIABLE_NAME LIKE 'Created_tmp%';"

Quick‑Check FAQ

# 1. 连接数打满
SHOW PROCESSLIST;
KILL <code>thread_id</code>;  # 替换为实际线程 ID
SET GLOBAL max_connections = 3000;

# 2. CPU 饱和
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
EXPLAIN <code>slow_sql</code>;  # 将 <code>slow_sql</code> 替换为实际慢查询语句
ALTER TABLE orders ADD INDEX idx_xxx (xxx);

# 3. 磁盘 IO 饱和
SET GLOBAL innodb_buffer_pool_size = 16*1024*1024*1024;   # 16GB
SET GLOBAL innodb_flush_log_at_trx_commit = 2;   # 减少 fsync 频率

# 4. 内存使用过高
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Created_tmp%';
SET GLOBAL tmp_table_size = 128*1024*1024;

Risk Warning and Rollback Plan

Production‑Operation Guidelines

# 1. DDL 前备份表
mysqldump -u root -p --single-transaction mydb orders > orders_backup_$(date +%Y%m%d).sql

# 2. 大表加索引使用 pt‑online-schema-change（在线不锁表）
pt-online-schema-change \
    --alter "ADD INDEX idx_name (name)" \
    --user=root --password=xxx \
    D=mydb,t=orders \
    --execute;

# 3. 避免在业务高峰期执行 DDL，选择低流量窗口并提前通知

# 4. 配置变更前备份原配置文件
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak.$(date +%Y%m%d)

# 5. 重大变更在测试环境验证（使用与生产相同的配置和数据量）

# 6. 回滚方案准备
#    DDL 回滚：使用备份恢复或 DROP INDEX
#    配置回滚：恢复原 my.cnf 文件并重启

Dangerous Operations Checklist

# 1. DELETE / UPDATE 必须带 WHERE 条件
-- 危险示例：DELETE FROM orders;  # 删除整表
-- 安全示例：DELETE FROM orders WHERE id > 1000000 AND created_at < '2025-01-01';

# 2. TRUNCATE 和 DROP TABLE 极度危险（不可恢复）
-- TRUNCATE TABLE orders;  # 立即清空表
-- DROP TABLE orders;      # 删除表结构

# 3. ALTER TABLE 可能长时间锁表（尤其是大表）
-- 推荐使用 pt-online-schema-change 或 gh-ost 进行在线变更

# 4. 禁止在生产环境执行无限制的 JOIN（可能导致内存爆炸）

# 5. 禁止在生产环境执行无 LIMIT 的查询（可能返回大量行）

# 6. 调整 max_connections 时需估算内存需求：
-- max_connections * max_allowed_packet ≈ 最大内存使用

Backup Strategy

# 1. 全量备份
mysqldump -u root -p --single-transaction --routines --triggers --events --master-data=2 mydb \
    > mydb_full_backup_$(date +%Y%m%d).sql

# 2. 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup_$(date +%Y%m%d).sql.gz

# 3. 仅备份表结构
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# 4. 仅备份数据
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

# 5. XtraBackup（生产环境热备）
innobackupex --user=root --password=xxx /backup/
innobackupex --apply-log /backup/2026-05-29_xx-xx-xx/

# 6. 恢复示例
mysql -u root -p mydb < mydb_full_backup_20260529.sql

# 7. 定时备份脚本示例（每日 3 点执行）
cat > /opt/scripts/mysql_backup.sh <<'EOF'
#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="xxx"
MYSQL_HOST="localhost"
mkdir -p ${BACKUP_DIR}
mysqldump -h ${MYSQL_HOST} -u ${MYSQL_USER} -p${MYSQL_PASS} \
    --single-transaction --routines --triggers --events mydb | gzip > ${BACKUP_DIR}/mydb_${DATE}.sql.gz
# 删除 7 天前的备份
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +7 -delete
echo "Backup completed: ${DATE}"
EOF
chmod +x /opt/scripts/mysql_backup.sh
# 加入 crontab: 0 3 * * * /opt/scripts/mysql_backup.sh

Conclusion

MySQL slow‑query optimization requires a systematic workflow: enable and analyze the slow‑query log (mysqldumpslow, pt‑query‑digest), use EXPLAIN to inspect execution plans (focus on type and Extra), design effective indexes (single‑column, composite, covering) respecting the left‑most prefix rule, tune core configuration parameters (buffer pool, log size, connection limits) based on workload, monitor replication lag and diagnose common causes, and follow safe operational practices (backup before DDL, test in staging, perform changes during low‑traffic windows, and have rollback procedures ready). When performance problems arise, follow the troubleshooting flow: check connections and slow SQL, analyze execution plans, then tune indexes and configuration. Always back up before changes and verify impact afterward.