MySQL 性能调优

慢查询分析

开启与配置

-- 查看当前设置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

-- 开启（建议生产长期开启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;         -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

### profiling 精准定位

当慢查询日志记录太多或需要更细粒度的诊断时，使用 MySQL profiling：

```sql
-- 开启 profiling（会话级别，仅当前连接）
SET profiling = 1;

-- 执行怀疑慢的 SQL
SELECT ...;

-- 查看 profiling 结果
SHOW PROFILES;
-- 输出：Query_ID | Duration | Query

-- 查看特定查询的详细耗时分布
SHOW PROFILE CPU, BLOCK IO, SWAPS FOR QUERY 1;
-- cpu: 用户态+内核态耗时
-- block io: 磁盘读写耗时
-- swaps: 内存交换次数

performance_schema 最细粒度：

-- 查看等待事件（IO/锁/CPU 等分布）
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

查看慢查询

-- 从 mysql.slow_log 表查询
SELECT start_time, query_time, lock_time, rows_sent, rows_examined,
       LEFT(sql_text, 200)
FROM mysql.slow_log
ORDER BY start_time DESC LIMIT 10;

-- 从文件系统统计最慢的 SQL
cat /var/log/mysql/slow.log | grep -E "^# Time:|^# Query_time:" | head -50
awk '/^# Query_time:/ {gsub("Query_time: ",""); if($1>1) print $1, $0}' \
  /var/log/mysql/slow.log | sort -rn | head -20

EXPLAIN 分析执行计划

EXPLAIN SELECT u.id, u.username, o.order_id, o.total_amount
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01' AND o.status = 'pending';

-- 更详细（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT ...;

-- 查看表结构和索引
SHOW CREATE TABLE users\G
SHOW INDEX FROM orders;

关键指标：

• type=ALL → 全表扫描，需要加索引
• key=NULL → 没用到索引
• rows 值巨大 → 扫描行数过多

索引失效的典型场景

建了索引但查询不走，是慢查询最常见的误判。

① 对索引列使用函数或计算

-- ❌ 不走索引（对 create_time 用了 DATE 函数）
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- ✅ 走索引（范围查询）
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

② 隐式类型转换

-- ❌ 不走索引（user_id 是 VARCHAR，传了数字）
SELECT * FROM users WHERE user_id = 12345;
-- ✅ 走索引（正确传字符串）
SELECT * FROM users WHERE user_id = '12345';

③ LIKE 左模糊

-- ❌ 不走索引（% 在最前面）
SELECT * FROM products WHERE product_name LIKE '%手机%';
-- ✅ 走索引（% 在最后面）
SELECT * FROM products WHERE product_name LIKE '华为%';

④ OR 条件导致索引失效

-- ❌ OR 两侧只要有一个列没有索引，整个查询不走索引
SELECT * FROM orders WHERE status = 'pending' OR amount > 10000;
-- ✅ 改为 UNION
SELECT * FROM orders WHERE status = 'pending'
UNION
SELECT * FROM orders WHERE amount > 10000;

⑤ 联合索引违反最左前缀

-- 联合索引 (a, b, c)
-- ❌ 不走索引：WHERE b = 1 AND c = 2    （跳过了 a）
-- ❌ 不走索引：WHERE a > 100 AND c = 2  （a 范围查询后 b 不生效，c 也失效）
-- ✅ 走索引：WHERE a = 1 AND b = 2 AND c = 3

排查技巧： EXPLAIN 看 key 和 Extra 字段。Extra 中出现 Using where; Using index 说明使用了覆盖索引（好），出现 Using filesort 说明排序没用到索引（需优化）。

当前运行 SQL 与连接管理

-- 查看所有活跃连接（排除 Sleep）
SELECT p.ID, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME,
       p.STATE, LEFT(p.INFO, 100) AS current_sql,
       t.trx_started, t.trx_rows_locked
FROM information_schema.PROCESSLIST p
LEFT JOIN information_schema.INNODB_TRX t ON p.ID = t.trx_mysql_thread_id
WHERE p.COMMAND != 'Sleep'
ORDER BY p.TIME DESC;

-- 查看连接数使用率
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

-- 杀掉长时间占用的连接
SELECT CONCAT('KILL ', ID, ';')
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 300
INTO OUTFILE '/tmp/kill_slow.sql';

锁等待分析与死锁排查

InnoDB 锁类型一览

Next-Key Lock 是死锁的高发区。范围查询会锁定间隙，另一个事务试图插入该范围记录时被阻塞，长期积累导致死锁。

元数据锁（MDL）排查

MDL 不是行锁，是 DDL 操作（ALTER/ADD COLUMN 等）对表加的元数据级锁。特点：排他、阻塞所有后续查询，且不易从 INNODB_TRX 查到。

识别 MDL 阻塞：

-- 查看处于 Waiting for table metadata lock 的线程
SELECT p.ID, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME, p.STATE,
       LEFT(p.INFO, 100) AS current_sql
FROM information_schema.PROCESSLIST p
WHERE p.STATE = 'Waiting for table metadata lock';

典型场景：

-- 事务A未提交（未 COMMIT/ROLLBACK），但已修改了某表
BEGIN;
ALTER TABLE orders ADD COLUMN remark VARCHAR(255);  -- 对 orders 加 MDL 排他锁
-- 此时还没 COMMIT...

-- 事务B的普通查询被阻塞
SELECT * FROM orders WHERE id = 1;  -- 等待 MDL（被事务A阻塞）

排查思路：

1. 找到 Waiting for table metadata lock 的线程
2. 找到谁持有了该表的 MDL — 查 PROCESSLIST 中 Command=Sleep 但未提交的事务
3. KILL 阻塞事务的连接即可恢复
4. 根本解决： DDL 操作前确保没有长事务，或使用 pt-online-schema-change 做无锁 DDL

查看锁等待关系（谁在等谁）

-- MySQL 5.7
SELECT r.trx_id AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query,
       b.trx_started AS blocking_trx_started
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id
JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

-- MySQL 8.0
SELECT * FROM performance_schema.data_lock_waits;

-- 查看当前所有事务持有的锁
SELECT t.trx_id, t.trx_state, t.trx_started, t.trx_rows_locked,
       t.trx_query, l.lock_id, l.lock_mode, l.lock_type,
       l.lock_table, l.lock_index, l.lock_data
FROM information_schema.INNODB_TRX t
JOIN information_schema.INNODB_LOCKS l ON t.trx_id = l.lock_trx_id
ORDER BY t.trx_started;

开启死锁日志

-- 查看当前配置（默认 OFF）
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

-- 开启所有死锁信息输出到错误日志（需要 SUPER 权限）
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 查看 deadlock 统计
SHOW STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits: 总等待次数（长期基线指标）
-- Innodb_row_lock_time_avg: 平均等待时间（ms）

解读死锁日志

错误日志中的死锁报告关键解读点：

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec, LOCK WAIT
*** (1) HOLDS THE LOCK(S):
    lock_mode X locks rec but not gap   ← 持有记录锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec
*** (2) HOLDS THE LOCK(S):
    lock_mode X locks gap before rec    ← 持有间隙锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (2)        ← MySQL回滚的事务

关键： gap before rec = 间隙锁冲突，WE ROLL BACK TRANSACTION = MySQL 选择回滚的事务（通常是较晚开始、持有锁较少的事务）。

performance_schema 监控锁事件（MySQL 8.0）

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock%';

SELECT * FROM performance_schema.events_waits_history_long
WHERE event_name LIKE '%lock%'
ORDER BY TIMER_END DESC LIMIT 20;

死锁场景与解决方案

场景一：不同事务以不同顺序访问多行

问题： 事务A先锁定行1再行2，事务B先锁定行2再行1，形成循环等待。

解决： 确保所有事务以相同顺序访问资源。

# 正确：按ID顺序加锁，避免循环等待
def transfer_funds(from_id, to_id, amount):
    first_id, second_id = (from_id, to_id) if from_id < to_id else (to_id, from_id)
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (first_id,))
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (second_id,))

场景二：索引导致的间隙锁冲突

问题： 范围查询时 Next-Key Lock 锁定较宽区间，导致插入被阻塞。

方案A：覆盖索引减少锁范围

CREATE INDEX idx_user_id_covering ON orders(user_id, status, amount);

方案B：调整隔离级别

-- 从 REPEATABLE READ 降为 READ COMMITTED，减少 Gap Lock
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 或永久配置：transaction-isolation = READ-COMMITTED

场景三：大事务拆分

问题： 单事务处理过多数据，锁持有时间长，死锁窗口扩大。

# 正确：分批处理，每批500条，批后立即释放锁
def batch_update(ids, batch_size=500):
    for i in range(0, len(ids), batch_size):
        batch = ids[i:i + batch_size]
        cursor.execute("BEGIN")
        cursor.execute("UPDATE orders SET status = 'processed' WHERE id IN (%s)" % ",".join(["%s"] * len(batch)), batch)
        cursor.execute("COMMIT")

代码层面的防死锁设计

锁超时机制

-- 设置锁等待超时（默认50秒）
SET GLOBAL innodb_lock_wait_timeout = 10;

# 捕获锁等待超时异常（错误码1205）
from pymysql import OperationalError
try:
    cursor.execute("SELECT ... FOR UPDATE")
except OperationalError as e:
    if e.args[0] == 1205:
        raise RetryableError("Lock timeout, should retry")

重试机制

import time

def transfer_with_retry(from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute("BEGIN")
            # ... 锁定和更新逻辑 ...
            cursor.execute("COMMIT")
            return True
        except OperationalError as e:
            if e.args[0] == 1205:
                connection.rollback()
                time.sleep(0.5 * (attempt + 1))
                continue
            raise
    return False

防死锁检查清单

核心建议： 出现死锁时，第一时间导出 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三表快照，开启 innodb_print_all_deadlocks 抓取完整上下文。事后的日志分析比现场排查更有价值。

深度分页优化

分页越往后越慢的根本原因：LIMIT 100000, 20 需要 MySQL 先扫描前 100000 行再丢弃，扫描行数巨大。

方案一：子查询延迟关联

-- ❌ 传统分页（越往后越慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- ✅ 子查询延迟关联（先快速定位 ID，再回表取数据）
SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;

方案二：游标分页（推荐）

利用有序唯一列（通常是主键）记录上一页最后位置，避免 offset：

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 拿到最后一条的 id = 100

-- 第二页（不需要 offset）
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 20;
-- 拿到最后一条的 id = 120

-- 第三页
SELECT * FROM orders WHERE id > 120 ORDER BY id LIMIT 20;

优点： 无论多少页都只扫描 20 行，性能恒定。要求： 排序字段必须单调递增且唯一（主键或唯一索引）。

方案三：覆盖索引优化

当需要回表查询且无法用游标分页时，确保 SELECT 列都在索引中：

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(status, create_time, id, amount);

-- 查询直接用覆盖索引返回，无需回表
SELECT id, status, amount FROM orders WHERE status = 'pending'
ORDER BY create_time LIMIT 100000, 20;

深度分页的推荐选择

InnoDB 缓冲池

-- 查看缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看使用情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- 计算命中率
-- (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)
-- / Innodb_buffer_pool_read_requests * 100

推荐配置： 物理内存的 60%~80%，但要留足够内存给 OS。

磁盘 IO 与临时表

-- 查看大表（IO 消耗大户）
SELECT table_schema, table_name,
       (data_length + index_length) / 1024 / 1024 AS MB,
       table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY (data_length + index_length) DESC LIMIT 10;

-- 查看临时表和排序
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

生产配置模板

[mysqld]
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB 缓冲池（物理内存 60%~80%）

关联页面：

• ops-automation-scripts — 运维自动化脚本 5 件套（MySQL 自动备份脚本）
• mysql-replication-guide — MySQL 主从复制原理与故障排查（性能配置在主从环境下的特殊考量）
• mysql-slow-query-case-study — MySQL 慢查询排查案例复盘（从 4.7s 到 23ms 的优化实战）