MySQL CPU 与并发优化

核心概念

CPU 是数据库处理的最终执行者，合理的并发控制可以最大化 CPU 利用率。

CPU 瓶颈表现

MySQL 线程模型

MySQL 线程架构：
├── 前台线程（用户连接）
│   ├── 连接线程
│   ├── 查询线程
│   └── 事务线程
├── 后台线程（系统任务）
│   ├── IO 线程
│   ├── 清理线程
│   └── 页清理线程
└── 中断处理线程

并发控制优化

线程池（Thread Pool）

线程池用于控制并发连接数，减少上下文切换。

[mysqld]
# 开启线程池（MySQL 企业版或 MariaDB）
plugin_load_add = 'thread_pool.so'

# 线程池大小
thread_pool_size = 16
# 建议设置为 CPU 核心数

# 最大线程数
thread_pool_max_threads = 512

# 空闲线程超时（秒）
thread_pool_idle_timeout = 60

社区版替代方案：

# 限制最大连接数
max_connections = 500

# 线程缓存
thread_cache_size = 100

连接数优化

[mysqld]
# 最大连接数
max_connections = 500
# 根据业务需求调整
# 过多：内存浪费，上下文切换频繁
# 过少：连接等待

# 每连接缓冲区
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 4M

# 总内存估算
# max_connections * (sort_buffer + read_buffer + join_buffer)
# 500 * (2M + 2M + 4M) = 4G

并发查询限制

-- 限制并发查询数
SET GLOBAL max_connections = 500;

-- 限制单用户连接数
ALTER USER 'app_user'@'%' WITH MAX_USER_CONNECTIONS 50;

-- 使用资源组（MySQL 8.0+）
CREATE RESOURCE GROUP rg_high 
TYPE = USER 
VCPU = 0-3 
PRIORITY = HIGH;

CREATE RESOURCE GROUP rg_low 
TYPE = USER 
VCPU = 4-7 
PRIORITY = LOW;

-- 分配用户到资源组
ALTER USER 'vip_user'@'%' RESOURCE GROUP rg_high;
ALTER USER 'normal_user'@'%' RESOURCE GROUP rg_low;

CPU 优化配置

并行查询

[mysqld]
# 并行查询（MySQL 8.0+）
innodb_parallel_read_threads = 4

# 并行建索引
innodb_create_innodb_threads = 4

# 并行 DDL（8.0.14+）
innodb_ddl_threads = 4

后台线程优化

[mysqld]
# IO 线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 清理线程数
innodb_purge_threads = 4

# 页清理线程
innodb_page_cleaners = 4

# 线程并发
innodb_thread_concurrency = 0
# 0: 不限制
# N: 最大并发线程数

自旋锁优化

[mysqld]
# 自旋锁等待次数
innodb_sync_spin_loops = 30
# 减少自旋，降低 CPU 消耗

# 自旋等待延时
innodb_spin_wait_delay = 6
# 增加延时，减少 CPU 空转

锁竞争优化

减少锁等待

-- 监控锁等待
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- 优化长事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

减少热点行竞争

-- 问题：热点行更新（如计数器）
UPDATE counters SET value = value + 1 WHERE id = 1;

-- 优化 1：拆分热点
UPDATE counters SET value = value + 1 WHERE id = 1 AND shard = 0;
UPDATE counters SET value = value + 1 WHERE id = 1 AND shard = 1;
-- 读取时汇总：SELECT SUM(value) FROM counters WHERE id = 1;

-- 优化 2：Redis 缓存
-- Redis: INCR counter:1
-- 定期批量写入 MySQL

优化索引减少锁竞争

-- 无索引导致全表锁
UPDATE orders SET status = 1 WHERE user_id = 100;

-- 添加索引，使用行锁
CREATE INDEX idx_user_id ON orders(user_id);
UPDATE orders SET status = 1 WHERE user_id = 100;

CPU 监控与诊断

CPU 监控工具

# top - 查看 CPU 使用
top -H -p $(pgrep -d',' mysqld)

# mpstat - CPU 统计
mpstat -P ALL 1

# pidstat - 进程 CPU 统计
pidstat -p $(pgrep mysqld) 1

# perf - 性能分析
perf top -p $(pgrep mysqld)
perf record -p $(pgrep mysqld) -g -- sleep 30
perf report

MySQL CPU 监控

-- 查看线程状态
SHOW PROCESSLIST;

-- 查看活跃线程数
SHOW STATUS LIKE 'Threads_running';

-- 查看线程创建
SHOW STATUS LIKE 'Threads_created';

-- 查看并发连接
SHOW STATUS LIKE 'Threads_connected';

performance_schema 监控

-- 查看线程等待
SELECT 
    thread_id,
    event_name,
    timer_wait
FROM performance_schema.events_waits_current
WHERE timer_wait IS NOT NULL
ORDER BY timer_wait DESC
LIMIT 10;

-- 查看锁等待
SELECT 
    thread_id,
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_duration,
    lock_status
FROM performance_schema.data_locks;

并发优化实践

优化步骤

1. 监控 CPU 指标
   top, mpstat

2. 识别瓶颈
   - CPU 使用率 > 90%
   - 上下文切换 > 5000/s
   - 运行队列 > 2 倍核心数

3. 优化配置
   - 调整 max_connections
   - 开启线程池
   - 优化后台线程

4. 优化 SQL
   - 减少全表扫描
   - 添加索引
   - 避免大事务

5. 架构优化
   - 读写分离
   - 分库分表
   - 缓存热点数据

配置示例

# 16 核 CPU 优化配置
[mysqld]
# 连接数限制
max_connections = 500
thread_cache_size = 100

# 线程池（如有）
thread_pool_size = 16

# 后台线程
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4

# 并发控制
innodb_thread_concurrency = 0

# 自旋锁优化
innodb_sync_spin_loops = 30
innodb_spin_wait_delay = 6

# 并行查询
innodb_parallel_read_threads = 4

常见问题解决

问题 1：CPU 使用率高

症状：
- CPU 使用率 > 90%
- 查询慢

解决：
1. 查看慢查询，优化 SQL
2. 减少全表扫描
3. 添加合适的索引
4. 限制 max_connections

问题 2：上下文切换频繁

症状：
- 上下文切换 > 5000/s
- 系统负载高

解决：
1. 减小 max_connections
2. 增大 thread_cache_size
3. 使用线程池
4. 减少并发连接

问题 3：锁竞争严重

症状：
- 锁等待时间长
- 并发性能差

解决：
1. 优化事务，缩短持有时间
2. 添加索引减少锁范围
3. 避免热点行竞争
4. 使用乐观锁

最佳实践

配置检查清单

[ ] max_connections 根据业务需求设置
[ ] thread_cache_size = 100-200
[ ] innodb_thread_concurrency = 0（不限制）
[ ] 后台线程数根据 CPU 核心数设置
[ ] 开启慢查询日志
[ ] 监控锁等待
[ ] 使用连接池（应用层）

参考资料

• MySQL 官方文档 - 线程优化
• 《高性能 MySQL》第 8 章：MySQL 服务器性能剖析
• Percona Blog: CPU Optimization for MySQL