引言：理解高并发场景下的MySQL挑战
在当今互联网应用中，高并发访问已经成为常态。无论是电商平台的秒杀活动、社交媒体的热点事件，还是金融系统的交易处理，MySQL数据库都面临着前所未有的压力。高并发场景下，最常见的两个问题就是慢查询和锁等待，它们往往相互关联，形成恶性循环：慢查询持有锁的时间过长，导致其他事务等待；而锁等待又会进一步加剧系统负载，产生更多的慢查询。
本文将从架构优化、索引调优、查询优化、锁机制理解和监控诊断五个维度，系统性地讲解MySQL高并发处理策略。我们将通过实际案例和可操作的代码示例，帮助你构建一个高性能、高可用的MySQL系统。
一、架构层面的优化策略
1.1 读写分离架构
读写分离是提升MySQL并发处理能力的基础架构优化方案。其核心思想是将读操作和写操作分离到不同的数据库实例上执行。
架构图示：
应用层 → 读写分离中间件 → 主库（写） + 从库（读）
实现方式：
-- 1. 配置主从复制（主库配置）
-- my.cnf 或 my.ini
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
-- 2. 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 3. 在从库上配置
[mysqld]
server-id = 2
relay_log = mysql-relay-bin
read_only = 1 -- 从库设置为只读
-- 4. 启动从库复制
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
START SLAVE;
应用层路由示例（Java + ShardingSphere）：
// 配置读写分离规则
@Configuration
public class DataSourceConfig {
@Bean
public DataSource dataSource() {
// 配置主从数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 主库（写）
HikariDataSource master = new HikariDataSource();
master.setJdbcUrl("jdbc:mysql://master:3306/test");
master.setUsername("root");
master.setPassword("password");
dataSourceMap.put("master", master);
// 从库（读）
HikariDataSource slave = new HikariDataSource();
slave.setJdbcUrl("jdbc:mysql://slave:3306/test");
slave.setUsername("root");
slave.setPassword("password");
dataSourceMap.put("slave", slave);
// 配置读写分离规则
DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, "master");
// 配置分片规则（读写分离）
ShardingRule shardingRule = ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.build();
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRule, new Properties());
}
}
读写分离的注意事项：
- 主从延迟问题：从库数据可能存在延迟，对于强一致性要求的读操作，需要强制走主库
- 事务内读写：同一个事务内的读操作必须走主库，避免脏读
- 缓存与数据库同步：读写分离时，缓存更新策略需要特别注意
1.2 分库分表策略
当单表数据量超过千万级或单库连接数成为瓶颈时，需要考虑分库分表。
水平分表示例：
-- 按用户ID取模分表（16张表）
-- 订单表：order_0 ~ order_15
-- 创建分表函数
DELIMITER $$
CREATE FUNCTION get_order_table_name(user_id BIGINT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE table_index INT;
SET table_index = user_id % 16;
RETURN CONCAT('order_', table_index);
END$$
DELIMITER ;
-- 应用层分表路由（Java示例）
public class OrderShardingService {
private static final int TABLE_COUNT = 16;
public String getTableName(Long userId) {
int index = (int) (userId % TABLE_COUNT);
return "order_" + index;
}
public void insertOrder(Order order) {
String tableName = getTableName(order.getUserId());
String sql = String.format(
"INSERT INTO %s (user_id, order_no, amount, status) VALUES (?, ?, ?, ?)",
tableName
);
// 执行插入
jdbcTemplate.update(sql, order.getUserId(), order.getOrderNo(),
order.getAmount(), order.getStatus());
}
public Order queryOrder(Long userId, String orderNo) {
String tableName = getTableName(userId);
String sql = String.format(
"SELECT * FROM %s WHERE user_id = ? AND order_no = ?",
tableName
);
return jdbcTemplate.queryForObject(sql, new Object[]{userId, orderNo},
new OrderRowMapper());
}
}
分库分表中间件（ShardingSphere配置）：
# sharding.yaml
dataSources:
ds_0: !!com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.1.10:3306/db_0
username: root
password: password
ds_1: !!com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.1.11:3306/db_1
username: root
password: password
shardingRule:
tables:
order:
actualDataNodes: ds_${0..1}.order_${0..15}
tableStrategy:
inline:
shardingColumn: user_id
algorithmExpression: order_${user_id % 16}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
bindingTables:
- order
1.3 连接池优化
连接池配置直接影响高并发下的数据库性能。
HikariCP最佳配置示例：
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
// 核心配置参数
config.setDriverClassName("com.mysql.cj.jdbc.Driver");
config.setPoolName("HighConcurrencyPool");
// 连接池大小配置（根据CPU核心数和业务特点）
// 公式：连接数 = ((核心数 * 2) + 有效磁盘数)
config.setMaximumPoolSize(50); // 最大连接数
config.setMinimumIdle(10); // 最小空闲连接
config.setConnectionTimeout(30000); // 连接超时30秒
config.setIdleTimeout(600000); // 空闲超时10分钟
config.setMaxLifetime(1800000); // 连接最大存活时间30分钟
config.setLeakDetectionThreshold(60000); // 泄漏检测阈值60秒
// 优化性能参数
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("useServerPrepStmts", "true");
config.addDataSourceProperty("useLocalSessionState", "true");
config.addDataSourceProperty("rewriteBatchedStatements", "true");
config.addDataSourceProperty("cacheResultSetMetadata", "true");
config.addDataSourceProperty("cacheServerConfiguration", "true");
config.addDataSourceProperty("elideSetAutoCommits", "true");
config.addDataSourceProperty("maintainTimeStats", "false");
HikariDataSource dataSource = new HikariDataSource(config);
连接池监控指标：
- 活跃连接数（ActiveConnections）
- 空闲连接数（IdleConnections）
- 等待连接数（WaitCount）
- 平均等待时间（WaitTime）
二、索引优化策略
2.1 索引设计原则
索引是解决慢查询的最有效手段，但不当的索引会成为性能杀手。
索引设计黄金法则：
- 最左前缀原则：复合索引必须从左开始匹配
- 选择性原则：选择性高的列放在索引前面
- 覆盖索引：尽量让查询的列都在索引中
- 避免冗余索引：定期清理未使用的索引
索引设计实例：
-- 订单表结构
CREATE TABLE `order` (
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`user_id` BIGINT NOT NULL,
`merchant_id` BIGINT NOT NULL,
`order_no` VARCHAR(64) NOT NULL,
`status` TINYINT NOT NULL,
`amount` DECIMAL(10,2) NOT NULL,
`create_time` DATETIME NOT NULL,
`update_time` DATETIME NOT NULL,
INDEX idx_user_status (`user_id`, `status`), -- 用户状态查询
INDEX idx_merchant_time (`merchant_id`, `create_time`), -- 商户时间范围查询
INDEX idx_order_no (`order_no`), -- 订单号唯一查询
INDEX idx_user_amount (`user_id`, `amount`) -- 用户金额范围查询
) ENGINE=InnoDB;
-- 好的查询（命中索引）
EXPLAIN SELECT * FROM `order`
WHERE user_id = 123 AND status = 1; -- 命中 idx_user_status
EXPLAIN SELECT * FROM `order`
WHERE merchant_id = 456 AND create_time >= '2024-01-01'; -- 命中 idx_merchant_time
-- 不好的查询（索引失效）
EXPLAIN SELECT * FROM `order`
WHERE status = 1 AND user_id = 123; -- 索引失效，违反最左前缀
EXPLAIN SELECT * FROM `order`
WHERE user_id = 123 AND amount > 100; -- 范围查询后索引失效
2.2 索引优化实战
案例：电商订单查询优化
-- 优化前（慢查询）
EXPLAIN SELECT
o.order_no,
o.amount,
o.status,
u.username,
p.product_name
FROM `order` o
JOIN user u ON o.user_id = u.id
JOIN order_item oi ON o.id = oi.order_id
JOIN product p ON oi.product_id = p.id
WHERE o.status = 1
AND o.create_time >= '2024-01-01'
AND o.amount > 100
AND u.username LIKE '%张%';
-- 执行计划分析：
-- type: ALL（全表扫描）
-- rows: 1000000
-- Extra: Using where; Using temporary; Using filesort
-- 预计执行时间：> 30秒
-- 优化步骤1：添加必要索引
ALTER TABLE `order` ADD INDEX idx_status_time_amount (`status`, `create_time`, `amount`);
ALTER TABLE `user` ADD INDEX idx_username (`username`);
ALTER TABLE `order_item` ADD INDEX idx_order_product (`order_id`, `product_id`);
-- 优化步骤2：重写查询（避免LIKE前缀通配符）
EXPLAIN SELECT
o.order_no,
o.amount,
o.status,
u.username,
p.product_name
FROM `order` o
STRAIGHT_JOIN user u ON o.user_id = u.id
STRAIGHT_JOIN order_item oi ON o.id = oi.order_id
STRAIGHT_JOIN product p ON oi.product_id = p.id
WHERE o.status = 1
AND o.create_time >= '2024-01-01'
AND o.amount > 100
AND u.username LIKE '张%'; -- 改为后缀匹配或全匹配
-- 优化后执行计划：
-- type: ref
-- rows: 50
-- Extra: Using index condition
-- 预计执行时间：< 100ms
索引优化工具：
-- 1. 查看索引使用情况
SELECT
t.TABLE_NAME,
t.INDEX_NAME,
t.SEQ_IN_INDEX,
t.COLUMN_NAME,
t.CARDINALITY,
s.ROWS,
s.DATA_LENGTH
FROM information_schema.STATISTICS t
JOIN information_schema.TABLES s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
ORDER BY t.TABLE_NAME, t.INDEX_NAME, t.SEQ_IN_INDEX;
-- 2. 查找冗余索引
SELECT
a.TABLE_SCHEMA,
a.TABLE_NAME,
a.INDEX_NAME AS first_index,
b.INDEX_NAME AS second_index,
a.COLUMN_NAME
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.COLUMN_NAME = b.COLUMN_NAME
AND a.SEQ_IN_INDEX = 1
AND b.SEQ_IN_INDEX > 1
WHERE a.TABLE_SCHEMA = 'your_database';
-- 3. 查找未使用的索引（需要开启userstat）
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_WRITE,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
AND INDEX_NAME IS NOT NULL
AND COUNT_READ = 0
AND COUNT_WRITE = 0;
2.3 覆盖索引优化
覆盖索引可以避免回表操作，极大提升查询性能。
-- 订单表覆盖索引设计
ALTER TABLE `order` ADD INDEX idx_cover_user (
user_id,
status,
create_time,
amount,
order_no
);
-- 覆盖索引查询示例
EXPLAIN SELECT
order_no,
amount,
status,
create_time
FROM `order`
WHERE user_id = 123
AND status = 1
AND create_time >= '2024-01-01';
-- 执行计划显示：
-- type: ref
-- Extra: Using index（关键：表示使用了覆盖索引，无需回表）
-- rows: 10
-- 对比未使用覆盖索引的查询
EXPLAIN SELECT
order_no,
amount,
status,
create_time,
merchant_id -- 不在覆盖索引中，需要回表
FROM `order`
WHERE user_id = 123
AND status = 1
AND create_time >= '2024-01-01';
-- 执行计划显示：
-- type: ref
-- Extra: NULL（需要回表）
-- rows: 10
三、查询语句优化
3.1 避免索引失效的常见场景
-- 1. 避免在索引列上使用函数
-- 错误示例
SELECT * FROM `order` WHERE DATE(create_time) = '2024-01-01';
-- 正确示例
SELECT * FROM `order` WHERE create_time >= '2024-01-01 00:00:00'
AND create_time < '2024-01-02 00:00:00';
-- 2. 避免隐式类型转换
-- 错误示例（phone字段是varchar，传入数字）
SELECT * FROM user WHERE phone = 13800138000;
-- 正确示例
SELECT * FROM user WHERE phone = '13800138000';
-- 3. 避免OR条件导致索引失效
-- 错误示例
SELECT * FROM `order` WHERE user_id = 123 OR amount > 1000;
-- 正确示例（使用UNION ALL）
SELECT * FROM `order` WHERE user_id = 123
UNION ALL
SELECT * FROM `order` WHERE amount > 1000 AND user_id != 123;
-- 4. 避免LIKE以%开头
-- 错误示例
SELECT * FROM user WHERE username LIKE '%张三';
-- 正确示例（使用全文索引或倒排索引）
ALTER TABLE user ADD FULLTEXT INDEX ft_username (username);
SELECT * FROM user WHERE MATCH(username) AGAINST('+张三' IN BOOLEAN MODE);
3.2 JOIN优化策略
-- 优化前（嵌套循环JOIN，性能差）
EXPLAIN SELECT
o.order_no,
o.amount,
u.username
FROM `order` o
JOIN user u ON o.user_id = u.id
WHERE o.create_time >= '2024-01-01'
AND u.status = 1;
-- 执行计划：
-- id: 1
-- select_type: SIMPLE
-- table: o
-- type: ALL（全表扫描）
-- rows: 1000000
-- table: u
-- type: eq_ref
-- rows: 1
-- 优化后（强制指定JOIN顺序）
EXPLAIN SELECT
o.order_no,
o.amount,
u.username
FROM `order` o
STRAIGHT_JOIN user u ON o.user_id = u.id
WHERE o.create_time >= '2024-01-01'
AND u.status = 1;
-- 执行计划：
-- id: 1
-- select_type: SIMPLE
-- table: o
-- type: range（使用索引）
-- rows: 1000
-- table: u
-- type: eq_ref
-- rows: 1
-- 大表JOIN优化：使用IN + 子查询
EXPLAIN SELECT
o.order_no,
o.amount
FROM `order` o
WHERE o.user_id IN (
SELECT id FROM user WHERE status = 1
)
AND o.create_time >= '2024-01-01';
3.3 分页查询优化
-- 传统分页（深度分页问题）
SELECT * FROM `order`
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 1000000, 20; -- 扫描100万行，性能极差
-- 优化方案1：延迟关联（覆盖索引）
SELECT o.* FROM `order` o
JOIN (
SELECT id FROM `order`
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 1000000, 20
) t ON o.id = t.id;
-- 优化方案2：书签法（记录上一页最后一条记录）
-- 第一页
SELECT * FROM `order`
WHERE user_id = 123
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 第二页（假设上一页最后一条记录的create_time=2024-01-01 10:00:00, id=12345）
SELECT * FROM `order`
WHERE user_id = 123
AND (create_time < '2024-01-01 10:00:00'
OR (create_time = '2024-01-01 10:00:00' AND id < 12345))
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 优化方案3：使用ES或ClickHouse处理深度分页
3.4 批量操作优化
-- 批量插入优化
-- 错误示例（逐条插入）
INSERT INTO `order` (user_id, order_no, amount) VALUES (1, 'NO001', 100);
INSERT INTO `order` (user_id, order_no, amount) VALUES (2, 'NO002', 200);
-- ... 1000条
-- 正确示例（批量插入）
INSERT INTO `order` (user_id, order_no, amount) VALUES
(1, 'NO001', 100),
(2, 'NO002', 200),
(3, 'NO003', 300),
-- ... 1000条
(1000, 'NO1000', 1000);
-- 批量更新优化
-- 错误示例
UPDATE `order` SET status = 2 WHERE id = 1;
UPDATE `order` SET status = 2 WHERE id = 2;
-- ...
-- 正确示例（批量更新）
UPDATE `order`
SET status = CASE id
WHEN 1 THEN 2
WHEN 2 THEN 2
WHEN 3 THEN 2
END
WHERE id IN (1, 2, 3);
-- 或使用INSERT ON DUPLICATE KEY UPDATE
INSERT INTO `order` (id, user_id, order_no, amount, status) VALUES
(1, 1, 'NO001', 100, 2),
(2, 2, 'NO002', 200, 2)
ON DUPLICATE KEY UPDATE
status = VALUES(status),
amount = VALUES(amount);
四、锁机制与并发控制
4.1 MySQL锁类型详解
-- 1. 共享锁（S锁）- 读锁
SELECT * FROM `order` WHERE id = 1 LOCK IN SHARE MODE;
-- 2. 排他锁（X锁）- 写锁
SELECT * FROM `order` WHERE id = 1 FOR UPDATE;
-- 3. 意向锁（Intention Locks）
-- InnoDB自动管理，无需手动控制
-- 4. 间隙锁（Gap Locks）
-- 在可重复读隔离级别下，防止幻读
-- 示例：范围查询会锁定间隙
BEGIN;
SELECT * FROM `order` WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- 此时其他事务无法插入id在10-20之间的记录
COMMIT;
-- 5. 临键锁（Next-Key Locks）= 记录锁 + 间隙锁
4.2 行锁优化策略
减少锁持有时间：
-- 优化前（长事务，锁持有时间长）
BEGIN;
-- 查询订单
SELECT * FROM `order` WHERE id = 123 FOR UPDATE;
-- 调用外部API（耗时1秒）
-- ... 调用支付接口 ...
-- 更新订单状态
UPDATE `order` SET status = 2 WHERE id = 123;
COMMIT;
-- 优化后（拆分事务，减少锁时间）
-- 步骤1：快速获取锁并更新
BEGIN;
UPDATE `order` SET status = 2 WHERE id = 123 AND status = 1;
-- 检查是否更新成功
SELECT ROW_COUNT();
COMMIT;
-- 步骤2：异步处理后续业务
-- 使用消息队列或异步任务处理支付回调
避免死锁的最佳实践：
-- 死锁场景：两个事务交叉锁定
-- 事务A
BEGIN;
UPDATE `order` SET status = 2 WHERE id = 1;
-- 事务B
BEGIN;
UPDATE `order` SET status = 2 WHERE id = 2;
-- 事务A
UPDATE `order` SET status = 2 WHERE id = 2; -- 等待锁
-- 事务B
UPDATE `order` SET status = 2 WHERE id = 1; -- 死锁！
-- 解决方案1：固定加锁顺序
-- 所有事务按id顺序加锁
BEGIN;
UPDATE `order` SET status = 2 WHERE id IN (1, 2) ORDER BY id;
COMMIT;
-- 解决方案2：使用SELECT ... FOR UPDATE NOWAIT
BEGIN;
-- 如果获取锁失败立即返回错误，而不是等待
SELECT * FROM `order` WHERE id = 1 FOR UPDATE NOWAIT;
-- 或者 WAIT 5（等待5秒）
SELECT * FROM `order` WHERE id = 1 FOR UPDATE WAIT 5;
COMMIT;
4.3 乐观锁与悲观锁应用
乐观锁（适合读多写少场景）：
-- 表结构增加版本号字段
CREATE TABLE `order` (
`id` BIGINT PRIMARY KEY,
`amount` DECIMAL(10,2),
`version` INT DEFAULT 0,
-- 其他字段...
);
-- 更新操作
UPDATE `order`
SET amount = 200, version = version + 1
WHERE id = 123 AND version = 5; -- 版本号校验
-- 检查影响行数
-- 如果影响行数为0，说明数据已被其他事务修改，需要重试
悲观锁（适合写多读少场景）：
-- 使用FOR UPDATE锁定记录
BEGIN;
SELECT * FROM `order` WHERE id = 123 FOR UPDATE;
-- 业务处理...
UPDATE `order` SET amount = 200 WHERE id = 123;
COMMIT;
4.4 隔离级别选择
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 不同隔离级别的选择策略：
-- 1. 读未提交（READ UNCOMMITTED）
-- 优点：无锁，性能最高
-- 缺点：脏读、不可重复读、幻读
-- 适用：对数据一致性要求极低的统计查询
-- 2. 读已提交（READ COMMITTED）- MySQL默认
-- 优点：避免脏读，性能较好
-- 缺点：不可重复读、幻读
-- 适用：大多数OLTP场景
-- 3. 可重复读（REPEATABLE READ）- InnoDB默认
-- 优点：避免脏读、不可重复读
-- 缺点：幻读（间隙锁解决）
-- 适用：需要事务内数据一致性的场景
-- 4. 串行化（SERIALIZABLE）
-- 优点：完全隔离
-- 缺点：性能最差，大量锁等待
-- 适用：极少使用，仅用于强一致性要求
-- 实际应用示例
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 业务操作...
COMMIT;
五、监控与诊断工具
5.1 慢查询日志分析
# 1. 开启慢查询日志
# my.cnf 配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 -- 记录超过1秒的查询
log_queries_not_using_indexes = 1 -- 记录未使用索引的查询
min_examined_row_limit = 1000 -- 至少检查1000行才记录
# 2. 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s: 排序方式（t=时间, c=次数, r=行数）
# -t: 显示前N条
# 3. 使用pt-query-digest（更强大）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
慢查询日志示例：
# Time: 2024-01-01T10:00:00.123456Z
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 12345 Schema: test QC_hit: No
# Query_time: 2.345678 Lock_time: 0.123456 Rows_sent: 10 Rows_examined: 1000000
# Rows_affected: 0 Bytes_sent: 1234
SET timestamp=1704067200;
SELECT * FROM `order` WHERE status = 1 AND create_time >= '2024-01-01';
5.2 Performance Schema监控
-- 1. 查看最慢的查询
SELECT
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT/1000000000000 AS avg_time_sec,
MAX_TIMER_WAIT/1000000000000 AS max_time_sec,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
-- 2. 查看表I/O统计
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ,
COUNT_WRITE,
SUM_TIMER_READ/1000000000000 AS read_time_sec,
SUM_TIMER_WRITE/1000000000000 AS write_time_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY SUM_TIMER_WAIT DESC;
-- 3. 查看索引使用情况
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ DESC;
-- 4. 查看锁等待
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
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
5.3 实时诊断工具
-- 1. 查看当前运行的查询
SHOW FULL PROCESSLIST;
-- 2. 查看InnoDB状态（包含锁信息）
SHOW ENGINE INNODB STATUS\G
-- 3. 查看当前锁信息
SELECT
r.trx_id,
r.trx_state,
r.trx_started,
r.trx_query,
l.lock_mode,
l.lock_type,
l.lock_table,
l.lock_index
FROM information_schema.innodb_trx r
LEFT JOIN information_schema.innodb_locks l ON r.trx_id = l.trx_id;
-- 4. 查看缓冲池状态
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
-- 5. 查看临时表使用情况
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
5.4 性能监控脚本
#!/bin/bash
# MySQL性能监控脚本
MYSQL_CMD="mysql -u root -p'password' -e"
while true; do
echo "=== $(date) ==="
# 活跃连接数
$MYSQL_CMD "SHOW STATUS LIKE 'Threads_connected';" | grep -v "Threads_connected"
# 慢查询数
$MYSQL_CMD "SHOW STATUS LIKE 'Slow_queries';" | grep -v "Slow_queries"
# InnoDB锁等待
$MYSQL_CMD "SELECT COUNT(*) FROM information_schema.innodb_lock_waits;" | tail -1
# 缓冲池命中率
$MYSQL_CMD "SELECT
(1 - (SUM(VARIABLE_VALUE) /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'))) * 100 AS hit_rate
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';" | tail -1
sleep 10
done
六、综合案例：秒杀系统优化
6.1 问题场景
业务需求： 10000人同时抢购100件商品，数据库频繁出现锁等待和慢查询。
原始方案：
-- 秒杀表
CREATE TABLE seckill (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
goods_id BIGINT,
stock INT,
version INT
);
-- 秒杀逻辑
BEGIN;
SELECT stock FROM seckill WHERE goods_id = 1;
-- 判断库存...
UPDATE seckill SET stock = stock - 1 WHERE goods_id = 1;
COMMIT;
问题分析：
- 高并发下大量锁等待
- 每次都要查询库存，数据库压力大
- 容易出现超卖
6.2 优化方案
1. 数据库层面优化：
-- 优化后的秒杀表
CREATE TABLE seckill (
id BIGINT PRIMARY KEY,
goods_id BIGINT UNIQUE,
stock INT NOT NULL,
version INT NOT NULL DEFAULT 0,
start_time DATETIME,
end_time DATETIME,
INDEX idx_time (start_time, end_time)
) ENGINE=InnoDB;
-- 优化后的秒杀逻辑（使用乐观锁+批量处理）
BEGIN;
-- 1. 先判断是否在秒杀时间范围内
SELECT COUNT(*) FROM seckill
WHERE goods_id = 1
AND NOW() BETWEEN start_time AND end_time;
-- 2. 使用乐观锁更新（减少锁等待）
UPDATE seckill
SET stock = stock - 1, version = version + 1
WHERE goods_id = 1
AND stock > 0
AND version = (SELECT version FROM seckill WHERE goods_id = 1);
-- 3. 检查影响行数
SELECT ROW_COUNT();
COMMIT;
2. 应用层优化（Redis+消息队列）：
@Service
public class SeckillService {
@Autowired
private StringRedisTemplate redisTemplate;
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private RabbitTemplate rabbitTemplate;
private static final String STOCK_KEY = "seckill:stock:";
private static final String STOCK_VERSION = "seckill:version:";
/**
* 秒杀请求处理
*/
public SeckillResult seckill(Long userId, Long goodsId) {
String stockKey = STOCK_KEY + goodsId;
String versionKey = STOCK_VERSION + goodsId;
// 1. Redis预减库存（原子操作）
Long stock = redisTemplate.opsForValue().decrement(stockKey);
if (stock < 0) {
redisTemplate.opsForValue().increment(stockKey); // 回滚
return SeckillResult.fail("库存不足");
}
// 2. 判断是否已秒杀过
String userKey = "seckill:user:" + goodsId;
if (redisTemplate.opsForSet().add(userKey, userId) == 0) {
return SeckillResult.fail("不能重复秒杀");
}
// 3. 发送消息到队列，异步处理数据库
SeckillMessage message = new SeckillMessage();
message.setUserId(userId);
message.setGoodsId(goodsId);
message.setStock(stock);
rabbitTemplate.convertAndSend("seckill.exchange", "seckill.key", message);
return SeckillResult.success("秒杀成功，正在处理订单");
}
/**
* 消费者处理数据库
*/
@RabbitListener(queues = "seckill.queue")
public void handleSeckillMessage(SeckillMessage message) {
try {
// 1. 检查数据库库存
Integer dbStock = jdbcTemplate.queryForObject(
"SELECT stock FROM seckill WHERE goods_id = ?",
Integer.class, message.getGoodsId()
);
if (dbStock <= 0) {
// 库存不足，回滚Redis
redisTemplate.opsForValue().increment(STOCK_KEY + message.getGoodsId());
return;
}
// 2. 使用乐观锁更新数据库
int updateCount = jdbcTemplate.update(
"UPDATE seckill SET stock = stock - 1, version = version + 1 " +
"WHERE goods_id = ? AND stock > 0 AND version = ?",
message.getGoodsId(), message.getStock() + 1
);
if (updateCount == 0) {
// 更新失败，回滚Redis
redisTemplate.opsForValue().increment(STOCK_KEY + message.getGoodsId());
return;
}
// 3. 创建订单
createOrder(message.getUserId(), message.getGoodsId());
} catch (Exception e) {
// 异常处理，回滚Redis
redisTemplate.opsForValue().increment(STOCK_KEY + message.getGoodsId());
log.error("秒杀处理失败", e);
}
}
}
3. 数据库最终优化方案：
-- 1. 创建秒杀专用表（独立表空间）
CREATE TABLE seckill_stock (
goods_id BIGINT PRIMARY KEY,
stock INT NOT NULL,
version INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
-- 2. 创建秒杀订单表（分表）
CREATE TABLE seckill_order_0 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
goods_id BIGINT,
order_no VARCHAR(64),
create_time DATETIME,
INDEX idx_user (user_id),
INDEX idx_goods (goods_id)
) ENGINE=InnoDB;
-- 3. 创建秒杀订单表（分表）
CREATE TABLE seckill_order_1 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
goods_id BIGINT,
order_no VARCHAR(64),
create_time DATETIME,
INDEX idx_user (user_id),
INDEX idx_goods (goods_id)
) ENGINE=InnoDB;
-- 4. 分表路由函数
DELIMITER $$
CREATE FUNCTION get_seckill_order_table(user_id BIGINT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
RETURN CONCAT('seckill_order_', user_id % 2);
END$$
DELIMITER ;
6.3 压测与监控
# 使用sysbench进行压测
sysbench oltp_read_write \
--mysql-host=localhost \
--mysql-user=root \
--mysql-password=password \
--mysql-db=test \
--tables=10 \
--table-size=100000 \
--threads=100 \
--time=60 \
--report-interval=10 \
run
# 监控指标
# - QPS（每秒查询数）
# - TPS（每秒事务数）
# - 平均响应时间
# - 95%响应时间
# - 错误率
# - 连接数
# - 慢查询数
七、总结与最佳实践
7.1 高并发优化检查清单
架构层面：
- [ ] 是否实施读写分离？
- [ ] 数据量是否超过千万级（考虑分库分表）？
- [ ] 连接池配置是否合理？
- [ ] 是否使用缓存（Redis）减少数据库压力？
索引层面：
- [ ] 所有查询是否都有合适的索引？
- [ ] 是否遵循最左前缀原则？
- [ ] 是否使用覆盖索引减少回表？
- [ ] 是否定期清理无用索引？
查询层面：
- [ ] 避免SELECT *，只查询需要的字段
- [ ] 避免索引列上使用函数
- [ ] 避免隐式类型转换
- [ ] 避免深度分页
- [ ] 批量操作代替单条操作
锁层面：
- [ ] 事务是否足够小（短事务）？
- [ ] 是否合理使用乐观锁/悲观锁？
- [ ] 是否避免了死锁（固定加锁顺序）？
- [ ] 是否选择了合适的隔离级别？
监控层面：
- [ ] 是否开启慢查询日志？
- [ ] 是否定期分析慢查询？
- [ ] 是否监控锁等待？
- [ ] 是否监控连接池状态？
7.2 性能优化黄金法则
- 先测量，再优化：使用监控工具定位真正的性能瓶颈
- 80/20原则：优化20%的慢查询解决80%的性能问题
- 分层优化：从架构 → 索引 → 查询 → 锁，逐层深入
- 避免过度优化：保持代码可读性，不要为不存在的瓶颈优化
- 持续监控：性能优化是持续过程，不是一次性工作
7.3 常见误区提醒
- 误区1：索引越多越好 → 正确：索引会降低写性能
- 误区2：事务越大越好 → 正确：事务越小越好，减少锁持有时间
- 误区3：连接数越大越好 → 正确：连接数过多会导致上下文切换开销
- 误区4：内存越大越好 → 正确：需要合理配置缓冲池大小
- 误区5：分库分表是银弹 → 正确：分库分表带来复杂度，应最后考虑
通过以上系统性的优化策略，可以有效解决MySQL高并发下的慢查询和锁等待问题。记住，性能优化是一个系统工程，需要从架构、设计、实现到监控的全方位考虑。