MySQL性能调优实战指南：从问题发现到解决的完整流程
性能调优方法论
性能调优不是一蹴而就的，需要系统性的方法和持续的监控。很多人在遇到性能问题时，往往病急乱投医，盲目地调整参数或添加索引，结果可能适得其反。
调优的整体思路
1. 建立基线 在开始调优之前，必须先了解系统的当前状态。
-- 记录当前的关键性能指标
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
'Queries',
'Questions',
'Innodb_rows_read',
'Innodb_rows_inserted',
'Innodb_rows_updated',
'Innodb_rows_deleted',
'Threads_connected',
'Threads_running'
);
-- 记录当前的配置参数
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';2. 确定调优目标 明确要解决什么问题：
- 响应时间过长？
- 并发能力不足？
- 资源利用率过高？
- 特定查询慢？
3. 优先级排序 按照影响程度和改进难度确定优化顺序：
高影响 + 低难度 = 优先处理
高影响 + 高难度 = 重点关注
低影响 + 低难度 = 后续处理
低影响 + 高难度 = 暂缓处理调优的层次结构
应用层调优：
- SQL语句优化
- 业务逻辑优化
- 连接池配置
数据库层调优：
- 索引设计
- 表结构优化
- 配置参数调整
系统层调优：
- 硬件配置
- 操作系统参数
- 存储优化
架构层调优：
- 读写分离
- 分库分表
- 缓存架构
瓶颈发现和定位
系统层面的监控
CPU使用率监控：
# 查看CPU使用情况
top -p $(pgrep mysqld)
# 查看CPU详细信息
iostat -c 1
# 分析MySQL进程的CPU使用
pidstat -p $(pgrep mysqld) 1内存使用监控：
# 查看内存使用情况
free -h
# 查看MySQL内存使用
cat /proc/$(pgrep mysqld)/status | grep -E "(VmRSS|VmSize)"
# 使用pmap查看内存映射
pmap -d $(pgrep mysqld)磁盘I/O监控：
# 查看磁盘I/O情况
iostat -x 1
# 查看具体进程的I/O
iotop -p $(pgrep mysqld)
# 查看磁盘使用情况
df -h
du -sh /var/lib/mysql/*MySQL层面的关键指标
连接相关指标：
-- 查看连接状态
SHOW PROCESSLIST;
-- 查看连接统计
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads%';
-- 分析连接使用模式
SELECT
command,
COUNT(*) as count,
AVG(time) as avg_time
FROM information_schema.processlist
GROUP BY command;查询性能指标：
-- QPS和TPS统计
SHOW STATUS LIKE 'Com_select';
SHOW STATUS LIKE 'Com_insert';
SHOW STATUS LIKE 'Com_update';
SHOW STATUS LIKE 'Com_delete';
-- 慢查询统计
SHOW STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'long_query_time';
-- 查询缓存命中率
SHOW STATUS LIKE 'Qcache%';
SELECT
ROUND(Qcache_hits / (Qcache_hits + Com_select) * 100, 2) as hit_rate
FROM (
SELECT VARIABLE_VALUE as Qcache_hits FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Qcache_hits'
) t1,
(
SELECT VARIABLE_VALUE as Com_select FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Com_select'
) t2;InnoDB性能指标：
-- 缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 计算缓冲池命中率
SELECT
ROUND(
(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2
) as buffer_pool_hit_rate
FROM (
SELECT VARIABLE_VALUE as Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) t1,
(
SELECT VARIABLE_VALUE as Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) t2;
-- 锁等待情况
SHOW STATUS LIKE 'Innodb_row_lock%';
-- 日志写入情况
SHOW STATUS LIKE 'Innodb_log%';慢查询分析
开启慢查询日志：
[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_throttle_queries_not_using_indexes = 10分析慢查询日志：
# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 按执行时间排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 按执行次数排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log # 按锁定时间排序
# 使用pt-query-digest（更强大）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 实时监控慢查询
pt-query-digest --processlist h=localhost,u=root,p=password --print --no-report慢查询日志解读：
# Time: 2024-04-05T10:30:45.123456Z
# User@Host: app_user[app_user] @ [192.168.1.100]
# Thread_id: 12345 Schema: ecommerce QC_hit: No
# Query_time: 5.234567 Lock_time: 0.000234 Rows_sent: 1500 Rows_examined: 150000
# Rows_affected: 0 Bytes_sent: 125000
SET timestamp=1712310645;
SELECT o.*, u.username, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.created_time >= '2024-01-01'
ORDER BY o.created_time DESC
LIMIT 100;关键指标解释：
- Query_time：查询总执行时间
- Lock_time：等待锁的时间
- Rows_sent：返回给客户端的行数
- Rows_examined：扫描的行数
监控和分析工具
Performance Schema深度使用
Performance Schema是MySQL内置的性能监控工具，功能强大但很多人不会使用。
启用Performance Schema：
[mysqld]
performance_schema = ON
performance-schema-instrument = 'statement/%=ON'
performance-schema-consumer-events-statements-current = ON
performance-schema-consumer-events-statements-history = ON分析最耗时的SQL：
-- 查看最耗时的SQL语句
SELECT
DIGEST_TEXT,
COUNT_STAR as exec_count,
AVG_TIMER_WAIT/1000000000 as avg_time_ms,
MAX_TIMER_WAIT/1000000000 as max_time_ms,
SUM_TIMER_WAIT/1000000000 as total_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;分析表访问模式：
-- 查看表的访问统计
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ,
COUNT_WRITE,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY COUNT_READ + COUNT_WRITE DESC;分析索引使用情况：
-- 查看索引使用统计
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY COUNT_FETCH DESC;
-- 找出未使用的索引
SELECT
t.OBJECT_SCHEMA,
t.OBJECT_NAME,
t.INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage t
WHERE t.INDEX_NAME IS NOT NULL
AND t.COUNT_STAR = 0
AND t.OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema');EXPLAIN执行计划深度分析
EXPLAIN是分析查询性能最重要的工具，但很多细节容易被忽视。
EXPLAIN的扩展用法：
-- 基本EXPLAIN
EXPLAIN SELECT * FROM users WHERE age > 25;
-- 详细信息
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;
-- 分析实际执行情况（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
-- 查看优化器改写后的SQL
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age > 25;关键字段深度解读：
type字段性能排序：
system > const > eq_ref > ref > fulltext > ref_or_null >
index_merge > unique_subquery > index_subquery > range >
index > ALLExtra字段重要信息：
-- 需要特别关注的Extra信息：
-- Using filesort：需要额外排序，考虑添加索引
EXPLAIN SELECT * FROM users ORDER BY created_time;
-- Using temporary：使用临时表，通常出现在GROUP BY或DISTINCT中
EXPLAIN SELECT DISTINCT department FROM users;
-- Using where：WHERE条件没有使用索引
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Using index：覆盖索引，性能很好
EXPLAIN SELECT id, name FROM users WHERE name = 'John';
-- Using index condition：索引条件下推
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;第三方监控工具
Percona Toolkit：
# 安装Percona Toolkit
yum install percona-toolkit
# 分析慢查询
pt-query-digest slow.log
# 检查表结构
pt-table-checksum --databases=mydb h=localhost
# 在线修改表结构
pt-online-schema-change --databases=mydb --tables=users --alter="ADD COLUMN phone VARCHAR(20)" --execute
# 查找重复索引
pt-duplicate-key-checker --databases=mydb h=localhostMySQL Tuner：
# 下载并运行MySQL Tuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
# 会分析并给出优化建议Prometheus + Grafana监控：
# docker-compose.yml示例
version: '3.7'
services:
mysql-exporter:
image: prom/mysqld-exporter
environment:
DATA_SOURCE_NAME: "user:password@(mysql:3306)/"
ports:
- "9104:9104"
prometheus:
image: prom/prometheus
ports:
- "9090:9090"
volumes:
- ./prometheus.yml:/etc/prometheus/prometheus.yml
grafana:
image: grafana/grafana
ports:
- "3000:3000"查询优化实战
SQL语句优化技巧
**1. 避免SELECT ***
-- 不好的写法
SELECT * FROM users WHERE status = 'active';
-- 好的写法
SELECT id, username, email FROM users WHERE status = 'active';
-- 如果需要所有字段，至少要明确列出来
SELECT id, username, email, phone, created_time, updated_time
FROM users WHERE status = 'active';2. 合理使用WHERE条件
-- 避免在WHERE中使用函数
-- 不好的写法
SELECT * FROM orders WHERE YEAR(created_time) = 2024;
-- 好的写法
SELECT * FROM orders
WHERE created_time >= '2024-01-01'
AND created_time < '2025-01-01';
-- 避免隐式类型转换
-- 不好的写法（如果user_id是字符串类型）
SELECT * FROM users WHERE user_id = 123;
-- 好的写法
SELECT * FROM users WHERE user_id = '123';3. 优化JOIN操作
-- 确保JOIN字段有索引
-- 创建必要的索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON orders(product_id);
-- 小表驱动大表
-- 如果users表比orders表小，这样写更好：
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.username;
-- 而不是：
SELECT u.username, COUNT(o.id) as order_count
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.username;4. 子查询优化
-- 将相关子查询改写为JOIN
-- 不好的写法
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.status = 'completed'
);
-- 好的写法
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';
-- 使用IN替代EXISTS（在某些情况下）
-- 原写法
SELECT * FROM products p
WHERE EXISTS (
SELECT 1 FROM order_items oi
WHERE oi.product_id = p.id
);
-- 改写为IN
SELECT * FROM products p
WHERE p.id IN (
SELECT DISTINCT product_id FROM order_items
);分页查询优化
传统分页的问题：
-- 传统分页，偏移量大时性能很差
SELECT * FROM users ORDER BY id LIMIT 100000, 20;
-- 需要扫描并跳过前100000行优化方案：
-- 1. 使用游标分页
-- 第一页
SELECT * FROM users ORDER BY id LIMIT 20;
-- 第二页（假设第一页最后一条记录的id是20）
SELECT * FROM users WHERE id > 20 ORDER BY id LIMIT 20;
-- 2. 使用覆盖索引 + 子查询
SELECT * FROM users u
INNER JOIN (
SELECT id FROM users ORDER BY id LIMIT 100000, 20
) t ON u.id = t.id;
-- 3. 使用延迟关联
SELECT u.* FROM users u
INNER JOIN (
SELECT id FROM users
WHERE status = 'active'
ORDER BY created_time DESC
LIMIT 100000, 20
) t ON u.id = t.id;索引优化策略
索引设计原则
1. 选择性分析
-- 计算字段的选择性
SELECT
COUNT(DISTINCT column_name) / COUNT(*) as selectivity,
COUNT(DISTINCT column_name) as unique_values,
COUNT(*) as total_rows
FROM table_name;
-- 选择性越接近1，越适合建索引
-- 一般选择性大于0.1的字段适合建索引2. 复合索引设计
-- 分析查询模式
-- 如果经常有这样的查询：
SELECT * FROM users WHERE status = 'active' AND city = 'Beijing' ORDER BY created_time;
-- 索引应该这样建：
CREATE INDEX idx_status_city_time ON users(status, city, created_time);
-- 字段顺序很重要：
-- 1. 等值条件的字段放前面
-- 2. 范围条件的字段放后面
-- 3. ORDER BY字段放最后3. 前缀索引优化
-- 对于长字符串字段，使用前缀索引
-- 分析前缀长度的选择性
SELECT
COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as prefix_5,
COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as prefix_10,
COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) as prefix_15,
COUNT(DISTINCT email) / COUNT(*) as full_column
FROM users;
-- 选择合适长度的前缀
CREATE INDEX idx_email_prefix ON users(email(10));索引监控和维护
监控索引使用情况：
-- 查看索引统计信息
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
SEQ_IN_INDEX,
COLUMN_NAME,
CARDINALITY,
INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
-- 使用Performance Schema分析索引使用
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE,
SUM_TIMER_FETCH/1000000000 as total_fetch_time_ms
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_FETCH DESC;索引维护：
-- 重建索引（修复碎片）
ALTER TABLE table_name ENGINE=InnoDB;
-- 或者使用OPTIMIZE TABLE
OPTIMIZE TABLE table_name;
-- 分析表（更新统计信息）
ANALYZE TABLE table_name;
-- 检查表的完整性
CHECK TABLE table_name;配置参数调优
InnoDB缓冲池优化
[mysqld]
# 缓冲池大小（通常设置为物理内存的70-80%）
innodb_buffer_pool_size = 8G
# 缓冲池实例数（CPU核心数）
innodb_buffer_pool_instances = 8
# 预热缓冲池（MySQL 5.6+）
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
# 缓冲池预读
innodb_read_ahead_threshold = 56监控缓冲池效果：
-- 查看缓冲池状态
SHOW ENGINE INNODB STATUS\G
-- 计算缓冲池命中率
SELECT
ROUND(
(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2
) as hit_rate_percent
FROM (
SELECT VARIABLE_VALUE as Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) t1,
(
SELECT VARIABLE_VALUE as Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) t2;
-- 命中率应该在99%以上连接和并发优化
[mysqld]
# 最大连接数
max_connections = 500
# 连接超时设置
interactive_timeout = 300
wait_timeout = 300
connect_timeout = 10
# 线程缓存
thread_cache_size = 100
# InnoDB并发设置
innodb_thread_concurrency = 0 # 0表示不限制，让InnoDB自动管理
innodb_read_io_threads = 8
innodb_write_io_threads = 8日志和刷新策略优化
[mysqld]
# Redo Log配置
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
# 刷新策略
innodb_flush_log_at_trx_commit = 1 # 1=最安全，2=性能更好
sync_binlog = 1
# 刷新方法
innodb_flush_method = O_DIRECT
# 双写缓冲
innodb_doublewrite = 1架构层面优化
读写分离实现
使用ProxySQL实现读写分离：
-- ProxySQL配置示例
-- 添加MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(0, '192.168.1.10', 3306, 1000), -- 主库
(1, '192.168.1.11', 3306, 900), -- 从库1
(1, '192.168.1.12', 3306, 900); -- 从库2
-- 配置查询路由规则
INSERT INTO mysql_query_rules(rule_id, match_pattern, destination_hostgroup, apply) VALUES
(1, '^SELECT.*', 1, 1), -- 读操作路由到从库
(2, '^INSERT|UPDATE|DELETE.*', 0, 1); -- 写操作路由到主库
-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;应用层读写分离：
@Service
public class UserService {
@Autowired
@Qualifier("masterDataSource")
private DataSource masterDataSource;
@Autowired
@Qualifier("slaveDataSource")
private DataSource slaveDataSource;
// 写操作使用主库
@Transactional
public void createUser(User user) {
// 使用masterDataSource
userRepository.save(user);
}
// 读操作使用从库
@Transactional(readOnly = true)
public List<User> findActiveUsers() {
// 使用slaveDataSource
return userRepository.findByStatus("active");
}
}分库分表策略
水平分表示例：
-- 按用户ID分表
CREATE TABLE user_0 LIKE user_template;
CREATE TABLE user_1 LIKE user_template;
-- ... 创建16个分表
-- 路由逻辑
-- user_id % 16 = 表后缀
SELECT * FROM user_{user_id % 16} WHERE user_id = ?;分库分表中间件配置（Sharding-JDBC）：
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://localhost:3306/db0
ds1:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://localhost:3306/db1
rules:
sharding:
tables:
user:
actual-data-nodes: ds${0..1}.user_${0..15}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: user_table_inline
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: user_db_inline
sharding-algorithms:
user_db_inline:
type: INLINE
props:
algorithm-expression: ds${user_id % 2}
user_table_inline:
type: INLINE
props:
algorithm-expression: user_${user_id % 16}实战案例分析
案例1：电商系统订单查询优化
问题描述： 用户查看订单列表页面响应时间超过5秒，影响用户体验。
问题分析：
-- 原始查询
SELECT o.*, u.username, p.product_name, p.price
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.user_id = 12345
ORDER BY o.created_time DESC
LIMIT 20;
-- 执行计划分析
EXPLAIN FORMAT=JSON [上述查询];发现的问题：
- 缺少复合索引，导致JOIN效率低
- 查询了过多不必要的字段
- 多表JOIN增加了复杂度
优化方案：
-- 1. 创建必要的索引
CREATE INDEX idx_user_created ON orders(user_id, created_time);
CREATE INDEX idx_order_id ON order_items(order_id);
CREATE INDEX idx_product_id ON order_items(product_id);
-- 2. 优化查询语句
-- 先查询订单基本信息
SELECT
o.id,
o.order_no,
o.total_amount,
o.status,
o.created_time
FROM orders o
WHERE o.user_id = 12345
ORDER BY o.created_time DESC
LIMIT 20;
-- 3. 使用应用层分步查询或者优化JOIN
SELECT
o.id,
o.order_no,
o.total_amount,
o.status,
o.created_time,
u.username
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.user_id = 12345
ORDER BY o.created_time DESC
LIMIT 20;
-- 商品信息单独查询或使用缓存优化效果：
- 查询时间从5秒降低到50毫秒
- 减少了不必要的数据传输
- 提高了索引使用率
案例2：高并发场景下的库存扣减优化
问题描述： 秒杀活动中，库存扣减操作出现大量锁等待，导致超时。
原始实现：
-- 悲观锁实现（问题版本）
BEGIN;
SELECT stock FROM products WHERE id = 1001 FOR UPDATE;
-- 应用层判断库存
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;问题分析：
- 锁定时间过长
- 并发度低
- 容易产生死锁
优化方案：
-- 1. 乐观锁 + 原子操作
UPDATE products
SET stock = stock - 1
WHERE id = 1001 AND stock > 0;
-- 检查affected_rows来判断是否成功
-- 2. 使用Redis预扣减 + 异步入库
-- 在Redis中维护库存
EVAL "
local stock = redis.call('GET', KEYS[1])
if stock and tonumber(stock) > 0 then
return redis.call('DECR', KEYS[1])
else
return -1
end
" 1 product:1001:stock
-- 3. 分段库存
-- 将库存分成多段，减少竞争
CREATE TABLE product_stock_segments (
product_id BIGINT,
segment_id INT,
stock INT,
PRIMARY KEY (product_id, segment_id)
);
-- 随机选择段进行扣减
UPDATE product_stock_segments
SET stock = stock - 1
WHERE product_id = 1001
AND segment_id = FLOOR(RAND() * 10)
AND stock > 0
LIMIT 1;案例3：大表查询性能优化
问题描述： 用户行为日志表数据量达到1亿条，查询特定时间段的数据非常慢。
原始查询：
SELECT user_id, action, COUNT(*) as action_count
FROM user_actions
WHERE action_time >= '2024-03-01'
AND action_time < '2024-04-01'
AND action = 'purchase'
GROUP BY user_id, action;优化方案：
-- 1. 创建分区表
ALTER TABLE user_actions
PARTITION BY RANGE (TO_DAYS(action_time)) (
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
-- 其他分区...
);
-- 2. 创建合适的索引
CREATE INDEX idx_action_time_action ON user_actions(action_time, action, user_id);
-- 3. 使用覆盖索引
CREATE INDEX idx_covering ON user_actions(action, action_time, user_id);
-- 优化后的查询利用分区裁剪和覆盖索引
SELECT user_id, action, COUNT(*) as action_count
FROM user_actions
WHERE action = 'purchase'
AND action_time >= '2024-03-01'
AND action_time < '2024-04-01'
GROUP BY user_id, action;性能调优检查清单
日常监控检查
每日检查项：
#!/bin/bash
# MySQL性能检查脚本
echo "=== MySQL Performance Check ==="
echo "Date: $(date)"
# 1. 检查连接数
mysql -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';"
# 2. 检查慢查询
mysql -e "SHOW STATUS LIKE 'Slow_queries';"
# 3. 检查缓冲池命中率
mysql -e "
SELECT
ROUND((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2) as hit_rate
FROM (
SELECT VARIABLE_VALUE as Innodb_buffer_pool_reads FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) t1,
(
SELECT VARIABLE_VALUE as Innodb_buffer_pool_read_requests FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) t2;
"
# 4. 检查锁等待
mysql -e "SHOW STATUS LIKE 'Innodb_row_lock_waits'; SHOW STATUS LIKE 'Innodb_row_lock_time_avg';"
# 5. 检查磁盘空间
df -h /var/lib/mysql每周检查项：
- 分析慢查询日志
- 检查索引使用情况
- 审查配置参数
- 检查数据库大小增长趋势
性能调优决策树
性能问题
├── CPU使用率高？
│ ├── 是 → 检查慢查询 → 优化SQL/添加索引
│ └── 否 → 继续检查
├── 内存使用率高？
│ ├── 是 → 检查缓冲池配置 → 调整内存参数
│ └── 否 → 继续检查
├── 磁盘I/O高？
│ ├── 是 → 检查日志配置 → 优化存储/参数
│ └── 否 → 继续检查
├── 锁等待多？
│ ├── 是 → 分析锁冲突 → 优化事务/索引
│ └── 否 → 检查架构设计
└── 并发连接多？
├── 是 → 检查连接池 → 优化应用/读写分离
└── 否 → 深入分析业务逻辑总结
MySQL性能调优是一个系统性工程，需要从多个维度进行分析和优化：
核心原则：
- 先监控，后优化：建立完善的监控体系
- 先定位，后解决：准确找到性能瓶颈
- 先应用，后数据库：优先优化SQL和业务逻辑
- 先索引，后参数：索引优化通常效果最明显
实施建议：
- 建立性能基线和监控告警
- 定期分析慢查询日志
- 持续优化索引设计
- 根据业务增长调整架构
避免的误区：
- 盲目调整参数而不分析问题
- 过度索引导致写性能下降
- 忽略应用层优化只关注数据库
- 不考虑业务特点的通用优化
性能调优是一个持续的过程，需要结合具体的业务场景和数据特点来制定优化策略。通过系统性的方法和持续的监控，可以构建出高性能、高可用的MySQL数据库系统。