2. 首页 > MySQL教程 > 正文

MySQL教程FG114-MySQL性能优化案例分析

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：4

Part01-基础概念与理论知识

慢查询是MySQL性能问题的常见原因，通过分析慢查询日志并进行优化，可以显著提升数据库性能。更多学习教程www.fgedu.net.cn

1.1 案例背景

某电商网站在促销活动期间，商品列表页面加载缓慢，用户体验差。通过查看慢查询日志，发现一条查询时间超过5秒的SQL语句。

# 查看慢查询日志
# tail -n 10 /var/lib/mysql/slow-query.log

# Time: 2026-04-01T10:00:00.000000Z
# User@Host: app[app] @ 192.168.1.100 [192.168.1.100]
# Query_time: 5.234567 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 1000000
SELECT * FROM products WHERE category_id = 1 AND price > 100 ORDER BY created_at DESC LIMIT 10;

1.2 问题分析

执行EXPLAIN分析该查询：

EXPLAIN SELECT * FROM products WHERE category_id = 1 AND price > 100 ORDER BY created_at DESC LIMIT 10;

# 输出结果
+—-+————-+———-+————+——+—————+——+———+——+———+———-+—————————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———-+————+——+—————+——+———+——+———+———-+—————————–+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 10.00 | Using where; Using filesort |
+—-+————-+———-+————+——+—————+——+———+——+———+———-+—————————–+

分析结果显示：

  • 没有使用索引（type: ALL）
  • 扫描了100万行数据（rows: 1000000）
  • 使用了文件排序（Using filesort）

1.3 优化方案

创建复合索引来覆盖查询条件和排序：

# 创建复合索引
CREATE INDEX idx_category_price_created ON products(category_id, price, created_at);

# 验证索引创建
SHOW INDEX FROM products;

# 输出结果
+———-+————+————————+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+———-+————+————————+————–+————-+———–+————-+———-+——–+——+————+———+
| products | 1 | idx_category_price_created | 1 | category_id | A | 100 | NULL | NULL | | BTREE | |
| products | 1 | idx_category_price_created | 2 | price | A | 10000 | NULL | NULL | | BTREE | |
| products | 1 | idx_category_price_created | 3 | created_at | A | 1000000 | NULL | NULL | | BTREE | |
+———-+————+————————+————–+————-+———–+————-+———-+——–+——+————+———+

1.4 优化效果

再次执行EXPLAIN分析：

EXPLAIN SELECT * FROM products WHERE category_id = 1 AND price > 100 ORDER BY created_at DESC LIMIT 10;

# 输出结果
+—-+————-+———-+————+——-+————————+————————+———+——+——-+———-+————————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———-+————+——-+————————+————————+———+——+——-+———-+————————–+
| 1 | SIMPLE | products | NULL | range | idx_category_price_created | idx_category_price_created | 10 | NULL | 10000 | 100.00 | Using where; Using index |
+—-+————-+———-+————+——-+————————+————————+———+——+——-+———-+————————–+

# 执行查询并查看执行时间
SELECT * FROM products WHERE category_id = 1 AND price > 100 ORDER BY created_at DESC LIMIT 10;

# 执行时间：0.012345秒

优化效果：查询时间从5.23秒减少到0.01秒，性能提升了425倍。通过创建复合索引，不仅避免了全表扫描，还利用了索引排序，消除了文件排序操作。

Part02-生产环境规划与建议

索引设计不合理会导致查询性能下降，正确的索引策略是提升MySQL性能的关键。学习交流加群风哥微信: itpux-com

2.1 案例背景

某企业ERP系统中，订单查询页面响应缓慢，特别是在按客户名称和订单日期查询时。

# 查看表结构
SHOW CREATE TABLE orders;

# 输出结果
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`customer_name` varchar(100) NOT NULL,
`order_date` datetime NOT NULL,
`amount` decimal(10,2) NOT NULL,
`status` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_customer_id` (`customer_id`),
KEY `idx_order_date` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 问题分析

执行EXPLAIN分析查询：

EXPLAIN SELECT * FROM orders WHERE customer_name = ‘张三’ AND order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’;

# 输出结果
+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+
| 1 | SIMPLE | orders | NULL | ALL | idx_order_date | NULL | NULL | NULL | 1000000 | 1.00 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

分析结果显示：

  • 没有使用索引（type: ALL）
  • 扫描了100万行数据（rows: 1000000）
  • 虽然有idx_order_date索引，但由于查询条件包含customer_name，无法使用该索引

2.3 优化方案

创建针对查询条件的复合索引：

# 创建复合索引
CREATE INDEX idx_customer_name_order_date ON orders(customer_name, order_date);

# 验证索引创建
SHOW INDEX FROM orders;

# 输出结果
+——–+————+—————————-+————–+—————+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+——–+————+—————————-+————–+—————+———–+————-+———-+——–+——+————+———+
| orders | 0 | PRIMARY | 1 | id | A | 1000000 | NULL | NULL | | BTREE | |
| orders | 1 | idx_customer_id | 1 | customer_id | A | 10000 | NULL | NULL | | BTREE | |
| orders | 1 | idx_order_date | 1 | order_date | A | 100000 | NULL | NULL | | BTREE | |
| orders | 1 | idx_customer_name_order_date | 1 | customer_name | A | 50000 | NULL | NULL | | BTREE | |
| orders | 1 | idx_customer_name_order_date | 2 | order_date | A | 200000 | NULL | NULL | | BTREE | |
+——–+————+—————————-+————–+—————+———–+————-+———-+——–+——+————+———+

2.4 优化效果

再次执行EXPLAIN分析：

EXPLAIN SELECT * FROM orders WHERE customer_name = ‘张三’ AND order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’;

# 输出结果
+—-+————-+——-+————+——-+———————————-+———————————-+———+——+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——-+———————————-+———————————-+———+——+——+———-+———————–+
| 1 | SIMPLE | orders | NULL | range | idx_order_date,idx_customer_name_order_date | idx_customer_name_order_date | 405 | NULL | 100 | 100.00 | Using index condition |
+—-+————-+——-+————+——-+———————————-+———————————-+———+——+——+———-+———————–+

# 执行查询并查看执行时间
SELECT * FROM orders WHERE customer_name = ‘张三’ AND order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’;

# 执行时间：0.008765秒

风哥提示：创建复合索引时，应将选择性高的列放在前面，这样可以更有效地缩小查询范围。对于包含多个条件的查询，复合索引比单个索引更有效。

Part03-生产环境项目实施方案

MySQL的配置参数直接影响数据库的性能，合理的参数配置可以显著提升系统性能。

3.1 案例背景

某网站在高峰期出现连接超时和查询缓慢的问题，服务器CPU和内存使用率较高。

# 查看当前配置
SHOW VARIABLES LIKE ‘%buffer%’;
SHOW VARIABLES LIKE ‘%cache%’;
SHOW VARIABLES LIKE ‘%max_connections%’;

# 输出结果
+————————-+—————-+—————-+——————-+
| Variable_name | Value | Variable_name | Value |
+————————-+—————-+—————-+——————-+
| innodb_buffer_pool_size | 134217728 | query_cache_size | 16777216 |
| key_buffer_size | 8388608 | max_connections | 151 |
+————————-+—————-+—————-+——————-+

3.2 问题分析

分析结果显示：

  • innodb_buffer_pool_size仅为128MB，对于生产环境来说过小
  • key_buffer_size仅为8MB，对于MyISAM表索引缓存不足
  • max_connections仅为151，可能无法满足高峰期的连接需求

3.3 优化方案

修改MySQL配置文件：

# 编辑my.cnf文件
vi /etc/my.cnf

# 添加或修改以下配置
[mysqld]
# 内存配置
innodb_buffer_pool_size = 4G
key_buffer_size = 256M

# 连接配置
max_connections = 500
wait_timeout = 60
interactive_timeout = 60

# 查询缓存（8.0版本已废弃）
query_cache_size = 0
query_cache_type = 0

# 日志配置
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 1

# 其他配置
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000

3.4 优化效果

重启MySQL服务并验证配置：

# 重启MySQL服务
systemctl restart mysqld

# 验证配置
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘max_connections’;

# 输出结果
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 4294967296 |
+————————-+————+

+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 500 |
+—————–+——-+

优化效果：系统在高峰期的响应速度显著提升，连接超时问题得到解决，CPU和内存使用率保持在合理水平。通过增加innodb_buffer_pool_size，减少了磁盘I/O操作，提高了数据访问速度。

Part04-生产案例与实战讲解

合理的表结构设计是MySQL性能优化的基础，不良的表结构会导致查询效率低下。

4.1 案例背景

某社交应用的用户表包含大量字段，查询时响应缓慢，特别是在筛选和排序操作时。

# 查看表结构
SHOW CREATE TABLE users;

# 输出结果
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(255) NOT NULL,
`full_name` varchar(100) NOT NULL,
`bio` text NOT NULL,
`avatar` varchar(255) NOT NULL,
`location` varchar(100) NOT NULL,
`website` varchar(255) NOT NULL,
`birthdate` date NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`last_login` datetime NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT ‘1’,
`is_admin` tinyint(1) NOT NULL DEFAULT ‘0’,
`profile_views` int(11) NOT NULL DEFAULT ‘0’,
`followers_count` int(11) NOT NULL DEFAULT ‘0’,
`following_count` int(11) NOT NULL DEFAULT ‘0’,
`posts_count` int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4.2 问题分析

分析结果显示：

  • 表包含19个字段，其中有些字段不常被查询
  • text类型的bio字段可能导致行过大
  • 多个计数字段（profile_views, followers_count等）需要频繁更新

4.3 优化方案

进行表结构优化，将不常用字段和频繁更新的字段分离：

# 创建用户扩展表
CREATE TABLE `user_profiles` (
`user_id` int(11) NOT NULL,
`bio` text NOT NULL,
`location` varchar(100) NOT NULL,
`website` varchar(255) NOT NULL,
`birthdate` date NOT NULL,
PRIMARY KEY (`user_id`),
CONSTRAINT `user_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 创建用户统计信息表
CREATE TABLE `user_stats` (
`user_id` int(11) NOT NULL,
`profile_views` int(11) NOT NULL DEFAULT ‘0’,
`followers_count` int(11) NOT NULL DEFAULT ‘0’,
`following_count` int(11) NOT NULL DEFAULT ‘0’,
`posts_count` int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`user_id`),
CONSTRAINT `user_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 修改用户表
ALTER TABLE `users` DROP COLUMN `bio`,
DROP COLUMN `location`,
DROP COLUMN `website`,
DROP COLUMN `birthdate`,
DROP COLUMN `profile_views`,
DROP COLUMN `followers_count`,
DROP COLUMN `following_count`,
DROP COLUMN `posts_count`;

4.4 优化效果

优化后，查询常用字段时的性能提升：

# 优化前查询
SELECT id, username, email, full_name, is_active FROM users WHERE is_active = 1 ORDER BY created_at DESC LIMIT 100;

# 执行时间：0.056789秒

# 优化后查询
SELECT u.id, u.username, u.email, u.full_name, u.is_active FROM users u WHERE u.is_active = 1 ORDER BY u.created_at DESC LIMIT 100;

# 执行时间：0.012345秒

风哥提示：通过垂直分表，将不常用字段和频繁更新的字段分离，可以减少行大小，提高查询效率，同时减少锁竞争，提升并发性能。

Part05-风哥经验总结与分享

对于大表，使用分区可以显著提升查询性能和管理效率。

5.1 案例背景

某系统的日志表包含超过1000万条记录，查询和维护变得越来越困难。

# 查看表结构
SHOW CREATE TABLE logs;

# 输出结果
CREATE TABLE `logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`action` varchar(50) NOT NULL,
`details` text NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 查看表大小
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = ‘mydb’ AND table_name = ‘logs’;

# 输出结果
+————+————+————-+————–+
| table_name | table_rows | data_length | index_length |
+————+————+————-+————–+
| logs | 10000000 | 1073741824 | 536870912 |
+————+————+————-+————–+

5.2 问题分析

分析结果显示：

  • 表包含1000万条记录，数据大小超过1GB
  • 查询历史数据时性能下降明显
  • 维护操作（如备份、优化）耗时较长

5.3 优化方案

使用时间范围分区：

# 创建分区表
CREATE TABLE `logs_partitioned` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`action` varchar(50) NOT NULL,
`details` text NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`, `created_at`),
KEY `idx_user_id` (`user_id`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202601 VALUES LESS THAN (TO_DAYS(‘2026-02-01’)),
PARTITION p202602 VALUES LESS THAN (TO_DAYS(‘2026-03-01’)),
PARTITION p202603 VALUES LESS THAN (TO_DAYS(‘2026-04-01’)),
PARTITION p202604 VALUES LESS THAN (TO_DAYS(‘2026-05-01’)),
PARTITION p202605 VALUES LESS THAN (TO_DAYS(‘2026-06-01’)),
PARTITION p202606 VALUES LESS THAN (TO_DAYS(‘2026-07-01’)),
PARTITION p202607 VALUES LESS THAN (TO_DAYS(‘2026-08-01’)),
PARTITION p202608 VALUES LESS THAN (TO_DAYS(‘2026-09-01’)),
PARTITION p202609 VALUES LESS THAN (TO_DAYS(‘2026-10-01’)),
PARTITION p202610 VALUES LESS THAN (TO_DAYS(‘2026-11-01’)),
PARTITION p202611 VALUES LESS THAN (TO_DAYS(‘2026-12-01’)),
PARTITION p202612 VALUES LESS THAN (TO_DAYS(‘2027-01-01’)),
PARTITION p_max VALUES LESS THAN MAXVALUE
);

# 导入数据
INSERT INTO logs_partitioned SELECT * FROM logs;

# 重命名表
RENAME TABLE logs TO logs_old, logs_partitioned TO logs;

5.4 优化效果

分区后查询性能提升：

# 查询2026年3月的日志
SELECT * FROM logs WHERE created_at BETWEEN ‘2026-03-01’ AND ‘2026-03-31’ LIMIT 100;

# 执行时间：0.023456秒

# 查看分区使用情况
EXPLAIN PARTITIONS SELECT * FROM logs WHERE created_at BETWEEN ‘2026-03-01’ AND ‘2026-03-31’;

# 输出结果
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | logs | p202603 | ALL | idx_created_at | NULL | NULL | NULL | 833333 | 100.00 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

优化效果：查询性能提升了5倍以上，只扫描了相关分区的数据，而不是全表扫描。此外，分区表还便于数据管理，如可以快速删除旧分区数据。

6. 连接池优化案例

连接池管理不当会导致连接资源浪费和性能下降，合理的连接池配置可以提升系统性能。

6.1 案例背景

某应用在高峰期出现连接数达到上限，导致新连接无法建立的问题。

# 查看当前连接状态
SHOW STATUS LIKE ‘Threads%’;
SHOW VARIABLES LIKE ‘max_connections’;

# 输出结果
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 5 |
| Threads_connected | 150 |
| Threads_created | 1000 |
| Threads_running | 10 |
+——————-+——-+

+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+——-+

6.2 问题分析

分析结果显示：

  • 当前连接数已达到150，接近max_connections限制（151）
  • Threads_created值较高（1000），说明连接创建频繁
  • Threads_cached值较低（5），说明连接缓存不足

6.3 优化方案

优化MySQL连接池配置：

# 编辑my.cnf文件
vi /etc/my.cnf

# 添加或修改以下配置
[mysqld]
# 连接配置
max_connections = 500
max_connect_errors = 10000

# 连接缓存
thread_cache_size = 100

# 会话参数
wait_timeout = 60
interactive_timeout = 60

# 应用侧优化（以Java为例）
# 修改数据库连接池配置
# spring.datasource.hikari.maximum-pool-size=100
# spring.datasource.hikari.minimum-idle=20
# spring.datasource.hikari.idle-timeout=30000
# spring.datasource.hikari.connection-timeout=30000

6.4 优化效果

重启MySQL服务并验证配置：

# 重启MySQL服务
systemctl restart mysqld

# 验证配置
SHOW VARIABLES LIKE ‘max_connections’;
SHOW VARIABLES LIKE ‘thread_cache_size’;

# 输出结果
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 500 |
+—————–+——-+

+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 100 |
+——————-+——-+

# 高峰期连接状态
SHOW STATUS LIKE ‘Threads%’;

# 输出结果
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 80 |
| Threads_connected | 120 |
| Threads_created | 150 |
| Threads_running | 15 |
+——————-+——-+

风哥提示：通过增加max_connections和thread_cache_size，可以提高连接处理能力和连接复用率，减少连接创建的开销。同时，应用侧的连接池配置也需要相应调整，以与MySQL配置相匹配。

7. 缓存优化案例

合理使用缓存可以显著减少数据库负载，提升系统响应速度。

7.1 案例背景

某电商网站的商品详情页访问量较大，导致数据库压力增加。

# 查看当前查询缓存配置（8.0版本已废弃）
SHOW VARIABLES LIKE ‘query_cache%’;

# 输出结果
+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———-+

7.2 问题分析

分析结果显示：

  • 查询缓存已启用，但缓存大小仅为16MB
  • 商品详情页的查询没有使用应用级缓存
  • 频繁的商品更新导致查询缓存失效

7.3 优化方案

实施多级缓存策略：

# 1. 禁用MySQL查询缓存（8.0版本已废弃）
# vi /etc/my.cnf
# query_cache_size = 0
# query_cache_type = 0

# 2. 应用侧实现缓存（以Redis为例）
# 商品详情缓存
# public Product getProductById(Long id) {
# // 先从Redis缓存获取
# String key = “product:” + id;
# String productJson = redisTemplate.opsForValue().get(key);
# if (productJson != null) {
# return JSON.parseObject(productJson, Product.class);
# }
#
# // 缓存未命中，从数据库查询
# Product product = productMapper.selectById(id);
# if (product != null) {
# // 存入Redis缓存，设置过期时间
# redisTemplate.opsForValue().set(key, JSON.toJSONString(product), 30, TimeUnit.MINUTES);
# }
# return product;
# }

# 3. 缓存更新策略
# @Transactional
# public void updateProduct(Product product) {
# productMapper.updateById(product);
# // 更新缓存
# String key = “product:” + product.getId();
# redisTemplate.opsForValue().set(key, JSON.toJSONString(product), 30, TimeUnit.MINUTES);
# }

7.4 优化效果

缓存实施后性能提升：

# 优化前：直接查询数据库
# 平均响应时间：500ms

# 优化后：使用Redis缓存
# 缓存命中时响应时间：10ms
# 缓存未命中时响应时间：510ms
# 缓存命中率：95%

# 数据库查询次数减少：95%
# 数据库负载降低：80%

优化效果：通过实施多级缓存策略，商品详情页的响应时间从500ms减少到10ms，数据库负载降低了80%。使用Redis作为应用级缓存，比MySQL自带的查询缓存更灵活、更高效。

8. 复制性能优化案例

MySQL复制性能直接影响高可用架构的可靠性和数据一致性。

8.1 案例背景

某系统的主从复制出现延迟，导致从库数据与主库不一致。

# 查看复制状态
SHOW SLAVE STATUS\G;

# 输出结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 123456789
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 123456789
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 123400000
Relay_Log_Space: 123456789
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 300
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

8.2 问题分析

分析结果显示：

  • Seconds_Behind_Master为300秒，复制延迟严重
  • Slave_SQL_Running_State显示正在从 relay log 读取事件
  • 主库可能有大量写入操作

8.3 优化方案

优化复制配置和架构：

# 1. 主库优化
# vi /etc/my.cnf
[mysqld]
# 二进制日志配置
binlog_format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 2. 从库优化
# vi /etc/my.cnf
[mysqld]
# 复制配置
read_only = 1
skip_slave_start = 0
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

# 3. 网络优化
# 确保主从之间网络带宽充足
# 考虑使用专用网络进行复制

# 4. 架构优化
# 考虑使用级联复制，减轻主库压力
# 主库 -> 中间从库 -> 其他从库

8.4 优化效果

优化后复制状态：

# 查看复制状态
SHOW SLAVE STATUS\G;

# 输出结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 123456789
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 123456789
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 123456789
Relay_Log_Space: 123456789
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

优化效果：复制延迟从300秒减少到0秒，实现了实时复制。通过启用并行复制（slave_parallel_workers），从库可以并行应用事务，显著提高了复制速度。

9. 高并发优化案例

高并发场景下，MySQL的性能表现直接影响系统的可用性和用户体验。

9.1 案例背景

某秒杀系统在活动期间出现数据库连接数激增、响应缓慢的问题。

# 查看当前连接状态
SHOW GLOBAL STATUS LIKE ‘Threads%’;
SHOW GLOBAL STATUS LIKE ‘Connections’;

# 输出结果
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 10 |
| Threads_connected | 450 |
| Threads_created | 5000 |
| Threads_running | 100 |
+——————-+——-+

+—————+——-+
| Variable_name | Value |
+—————+——-+
| Connections | 10000 |
+—————+——-+

9.2 问题分析

分析结果显示：

  • 并发连接数达到450，接近max_connections限制
  • Threads_created值很高（5000），说明连接创建频繁
  • Threads_running值较高（100），说明有大量活跃查询

9.3 优化方案

实施高并发优化策略：

# 1. 数据库配置优化
# vi /etc/my.cnf
[mysqld]
# 连接配置
max_connections = 1000
thread_cache_size = 200

# 存储引擎配置
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# 事务配置
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1000

# 2. 应用侧优化
# 实现连接池管理
# 使用批量操作减少数据库交互
# 实施限流和熔断机制
# 使用Redis进行缓存和分布式锁

# 3. 架构优化
# 读写分离
# 分库分表
# 使用CDN缓存静态资源

9.4 优化效果

优化后系统表现：

# 高峰期连接状态
SHOW GLOBAL STATUS LIKE ‘Threads%’;
SHOW GLOBAL STATUS LIKE ‘Connections’;

# 输出结果
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 180 |
| Threads_connected | 500 |
| Threads_created | 800 |
| Threads_running | 50 |
+——————-+——-+

+—————+——-+
| Variable_name | Value |
+—————+——-+
| Connections | 15000 |
+—————+——-+

# 系统响应时间
# 优化前：平均响应时间 500ms
# 优化后：平均响应时间 100ms

# 系统稳定性
# 优化前：偶尔出现连接超时
# 优化后：稳定运行，无超时现象

风哥提示：高并发场景下，需要从多个层面进行优化，包括数据库配置、应用设计和系统架构。合理使用缓存、连接池和分布式架构，可以显著提升系统的并发处理能力。

10. 综合性能优化案例

综合考虑多个因素，实施全面的性能优化策略。

10.1 案例背景

某企业管理系统整体性能下降，多个模块响应缓慢，影响业务正常运行。

# 系统现状
# 1. 数据库服务器配置：8核16G内存
# 2. MySQL版本：5.7
# 3. 主要问题：
# – 页面加载缓慢
# – 报表生成时间长
# – 数据导入导出卡顿
# – 高峰期系统响应延迟

10.2 问题分析

通过性能分析工具进行全面诊断：

# 使用Percona Toolkit分析
pt-query-digest /var/lib/mysql/slow-query.log

# 主要慢查询
# 1. 报表查询：全表扫描，无索引
# 2. 数据导入：单条插入，无批量操作
# 3. 业务查询：复杂JOIN，缺少索引
# 4. 配置问题：innodb_buffer_pool_size过小

10.3 优化方案

实施综合优化策略：

# 1. 硬件升级
# 内存升级到32G
# 存储使用SSD

# 2. 数据库配置优化
# vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
max_connections = 500
thread_cache_size = 100

# 3. 索引优化
# 为报表查询创建合适的索引
CREATE INDEX idx_report_date ON sales(order_date);
CREATE INDEX idx_report_customer ON sales(customer_id);

# 4. SQL优化
# 优化报表查询
# 原查询：SELECT * FROM sales WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’;
# 优化后：SELECT id, customer_id, amount, order_date FROM sales WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’;

# 5. 应用优化
# 实现批量导入
# 使用分页查询
# 实现数据缓存

# 6. 架构优化
# 读写分离
# 报表数据汇总表
# 定时任务优化

10.4 优化效果

综合优化后系统性能提升：

# 系统响应时间
# 页面加载：5秒 → 0.5秒
# 报表生成：30秒 → 3秒
# 数据导入：10分钟 → 1分钟
# 业务查询：2秒 → 0.2秒

# 系统稳定性
# 高峰期CPU使用率：80% → 30%
# 内存使用率：70% → 40%
# 数据库连接数：400 → 150

# 用户体验
# 系统卡顿现象：频繁 → 消失
# 操作响应：延迟 → 即时
# 业务处理能力：提升5倍

优化效果：通过综合优化策略，系统性能得到了显著提升，各项操作的响应时间大幅减少，系统稳定性和用户体验得到了明显改善。综合优化需要从硬件、配置、索引、SQL、应用和架构等多个层面入手，才能达到最佳效果。
GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息