MySQL高并发处理策略：如何优化数据库性能以应对海量用户访问并解决慢查询与锁竞争问题

引言：理解高并发环境下的MySQL挑战

在当今互联网应用中，高并发访问已成为常态。无论是电商平台的秒杀活动、社交媒体的热点事件，还是金融系统的交易高峰期，MySQL数据库都面临着前所未有的压力。高并发环境下，数据库性能瓶颈主要体现在响应时间延长、吞吐量下降、甚至系统崩溃。根据最新统计，超过70%的互联网应用性能问题源于数据库层面，其中慢查询和锁竞争是两大核心痛点。

高并发场景下的MySQL优化不仅仅是简单的参数调整，而是一个系统工程，涉及架构设计、SQL优化、硬件资源、监控体系等多个维度。本文将深入探讨MySQL在高并发环境下的性能优化策略，重点解决慢查询和锁竞争问题，帮助您构建能够支撑百万级并发的数据库系统。

一、高并发场景下的MySQL性能瓶颈分析

1.1 CPU资源瓶颈

在高并发环境下，MySQL的CPU使用率往往率先达到瓶颈。主要表现为：

• 复杂查询计算：多表关联、排序、分组等操作消耗大量CPU周期
• 上下文切换频繁：大量线程竞争CPU资源，导致系统调用开销增大
• 解析与优化开销：SQL解析和执行计划生成占用CPU资源

1.2 I/O瓶颈

I/O瓶颈是高并发场景下的常见问题：

• 磁盘读写延迟：机械硬盘的随机I/O性能远低于顺序I/O
• 日志写入压力：redo log、binlog的频繁刷盘
• 缓冲区失效：InnoDB Buffer Pool命中率下降

1.3 内存瓶颈

内存不足会导致频繁的磁盘交换：

• Buffer Pool不足：热点数据无法缓存，增加磁盘I/O
• 连接数过高：每个连接占用内存，导致内存耗尽
• 临时表使用：大查询使用磁盘临时表，性能急剧下降

1.4 锁竞争问题

锁竞争是高并发下最棘手的问题：

• 行锁竞争：热点行更新导致事务等待
• 表锁阻塞：DDL操作或全表扫描锁定
• 死锁频繁：复杂事务逻辑导致死锁概率增加

二、架构层面的优化策略

2.1 读写分离架构

读写分离是应对高并发的首选架构方案。通过将读请求和写请求分离，可以显著提升系统吞吐量。

实现方案：

-- 主库配置（写操作）
-- my.cnf 配置
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

-- 从库配置（读操作）
[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=1

应用层路由逻辑示例（Java）：

public class DataSourceRouter {
    private DataSource masterDataSource;
    private DataSource slaveDataSource;
    
    public Connection getConnection(boolean isWrite) throws SQLException {
        if (isWrite) {
            return masterDataSource.getConnection();
        } else {
            // 可以扩展为多个从库的负载均衡
            return slaveDataSource.getConnection();
        }
    }
    
    // 使用AOP自动路由
    @Around("@annotation(readOnly)")
    public Object routeDataSource(ProceedingJoinPoint pjp, ReadOnly readOnly) throws Throwable {
        try {
            if (readOnly.value()) {
                DataSourceContextHolder.setDbType("slave");
            } else {
                DataSourceContextHolder.setDbType("master");
            }
            return pjp.proceed();
        } finally {
            DataSourceContextHolder.clearDbType();
        }
    }
}

2.2 分库分表策略

当单表数据量超过千万级，或并发量超过单机处理能力时，需要考虑分库分表。

水平分表示例：

-- 按用户ID取模分表
-- 用户表分16张表
CREATE TABLE user_0 (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

CREATE TABLE user_1 (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);
-- ... user_2 到 user_15

-- 分表路由逻辑（Java）
public class TableSharding {
    private static final int TABLE_COUNT = 16;
    
    public String getTableName(Long userId) {
        int index = (int) (userId % TABLE_COUNT);
        return "user_" + index;
    }
    
    public String getDynamicQuery(Long userId, String baseQuery) {
        String tableName = getTableName(userId);
        return baseQuery.replace("user", tableName);
    }
}

分库分表中间件推荐：

• ShardingSphere：功能强大，支持多种分片算法
• MyCat：成熟的MySQL中间件
• Vitess：YouTube开源的分布式数据库解决方案

2.3 缓存层优化

引入Redis等缓存层，减少数据库直接访问。

缓存策略示例：

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    private static final String USER_CACHE_PREFIX = "user:";
    private static final long CACHE_TTL = 3600; // 1小时
    
    public User getUserById(Long id) {
        String cacheKey = USER_CACHE_PREFIX + id;
        
        // 1. 先从缓存获取
        User user = (User) redisTemplate.opsForValue().get(cacheKey);
        if (user != null) {
            return user;
        }
        
        // 2. 缓存未命中，查询数据库
        user = userRepository.findById(id).orElse(null);
        if (user != null) {
            // 3. 写入缓存
            redisTemplate.opsForValue().set(cacheKey, user, CACHE_TTL, TimeUnit.SECONDS);
        }
        
        return user;
    }
    
    @CacheEvict(value = "user", key = "#user.id")
    public void updateUser(User user) {
        userRepository.save(user);
        // 删除缓存，保证一致性
        redisTemplate.delete(USER_CACHE_PREFIX + user.getId());
    }
}

2.4 连接池优化

合理配置连接池参数至关重要。

HikariCP配置示例（Spring Boot）：

spring:
  datasource:
    hikari:
      # 连接池大小
      maximum-pool-size: 50
      minimum-idle: 10
      # 连接超时
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      # 连接测试
      connection-test-query: SELECT 1
      validation-timeout: 5000
      leak-detection-threshold: 60000

连接池大小计算公式：

连接数 = ((核心数 * 2) + 有效磁盘数)

对于高并发读场景，可以适当增大连接数；对于写场景，需要谨慎控制连接数以避免锁竞争加剧。

三、SQL优化：解决慢查询问题

3.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
log_slow_admin_statements = 1
log_slow_slave_statements = 1

使用Performance Schema监控：

-- 查看最近执行的慢查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec,
    MAX_TIMER_WAIT/1000000000000 as max_time_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1000000000000
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

3.2 索引优化策略

索引设计原则：

1. 最左前缀原则：复合索引必须遵循最左前缀匹配
2. 选择性原则：选择性高的列优先建立索引
3. 覆盖索引：查询列全部包含在索引中，避免回表

索引优化实例：

-- 问题SQL：查询用户订单，性能差
SELECT o.id, o.order_no, o.amount, u.username 
FROM orders o 
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' 
  AND o.created_at >= '2024-01-01'
  AND u.email LIKE '%@gmail.com';

-- 优化前执行计划（EXPLAIN）
-- type: ALL (全表扫描)
-- rows: 1000000
-- Extra: Using where; Using temporary; Using filesort

-- 优化方案：
-- 1. 在orders表创建复合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 2. 在users表创建索引
CREATE INDEX idx_email ON users(email);

-- 3. 改写SQL，使用显式连接和索引提示
SELECT o.id, o.order_no, o.amount, u.username 
FROM orders o 
STRAIGHT_JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' 
  AND o.created_at >= '2024-01-01'
  AND u.email LIKE '%@gmail.com';

-- 优化后执行计划
-- type: ref (索引查找)
-- rows: 5000
-- Extra: Using index condition; Using where

索引使用技巧：

-- 避免索引失效的写法
-- ❌ 索引失效：函数操作
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

-- ✅ 索引有效：范围查询
SELECT * FROM users WHERE created_at >= '2024-01-01' 
  AND created_at < '2024-01-02';

-- ❌ 索引失效：隐式类型转换
SELECT * FROM orders WHERE order_no = 12345; -- order_no是varchar

-- ✅ 索引有效：类型匹配
SELECT * FROM orders WHERE order_no = '12345';

-- ❌ 索引失效：前模糊查询
SELECT * FROM users WHERE username LIKE '%张%';

-- ✅ 索引有效：后模糊查询
SELECT * FROM users WHERE username LIKE '张%';

3.3 查询语句优化

避免SELECT *：

-- ❌ 性能差：返回所有列，增加网络传输和内存消耗
SELECT * FROM orders WHERE user_id = 123;

-- ✅ 性能好：只查询需要的列
SELECT id, order_no, amount, status FROM orders WHERE user_id = 123;

优化分页查询：

-- ❌ 深度分页性能差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 优化方案1：使用子查询
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) tmp
ON o.id = tmp.id;

-- ✅ 优化方案2：使用位置记录
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id LIMIT 20;

-- ✅ 优化方案3：使用ES等搜索引擎处理深度分页

优化JOIN查询：

-- ❌ 性能差：小表驱动大表
SELECT * FROM large_table l
JOIN small_table s ON l.id = s.large_id
WHERE s.type = 'VIP';

-- ✅ 性能好：大表驱动小表，使用索引
SELECT l.* FROM large_table l
JOIN small_table s ON l.id = s.large_id
WHERE l.status = 'ACTIVE' AND s.type = 'VIP';

3.4 执行计划分析

EXPLAIN详解：

-- 示例SQL
EXPLAIN SELECT o.id, o.order_no, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND u.age > 25;

-- 输出结果解读：
-- id: 1 (查询序号)
-- select_type: SIMPLE (简单查询)
-- table: o (第一张表)
-- type: ref (索引查找)
-- possible_keys: idx_user_status, idx_user_id (可能使用的索引)
-- key: idx_user_status (实际使用的索引)
-- key_len: 5 (索引长度)
-- ref: const (常量)
-- rows: 1000 (预估扫描行数)
-- Extra: Using index condition (使用索引条件下推)

-- 第二行（users表）
-- table: u
-- type: eq_ref (唯一索引查找)
-- key: PRIMARY
-- rows: 1
-- Extra: Using where

使用EXPLAIN ANALYZE（MySQL 8.0+）：

-- MySQL 8.0+提供实际执行统计
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID';

-- 输出包含实际执行时间和行数统计
-- -> Filter: (o.status = 'PAID')  (cost=1000 rows=5000) (actual time=0.1..100.5 rows=5000)

四、锁竞争问题深度解决方案

4.1 锁类型与竞争场景分析

InnoDB锁类型：

• 共享锁（S锁）：读锁，多个事务可以同时持有
• 排他锁（X锁）：写锁，只有一个事务可以持有
• 意向锁（IS/IX锁）：表级锁，表示事务将要加的行锁类型

常见锁竞争场景：

1. 热点行更新：秒杀场景下同一商品的库存更新
2. 唯一索引冲突：并发插入重复键值
3. 外键检查：父表删除时检查子表
4. DDL操作：ALTER TABLE阻塞DML操作

4.2 优化锁竞争的SQL策略

减少锁持有时间：

-- ❌ 锁持有时间长：先查询再更新
SELECT stock FROM products WHERE id = 100;
-- 应用层计算
UPDATE products SET stock = stock - 1 WHERE id = 100;

-- ✅ 锁持有时间短：直接更新，减少交互
UPDATE products SET stock = stock - 1 
WHERE id = 100 AND stock > 0;

-- 检查影响行数
-- 如果affected_rows=0，说明库存不足

避免死锁的SQL写法：

-- 场景：转账操作，容易死锁
-- ❌ 错误顺序：A->B和B->A同时发生
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';

-- ✅ 正确顺序：按固定顺序加锁
-- 方案1：按user_id排序
-- 如果user_id='A' < user_id='B'，总是先更新A再更新B
-- 如果user_id='B' < user_id='A'，总是先更新B再更新A

-- 方案2：使用SELECT ... FOR UPDATE显式加锁
START TRANSACTION;
-- 先锁定两个账户
SELECT * FROM accounts WHERE user_id IN ('A', 'B') ORDER BY user_id FOR UPDATE;
-- 然后执行更新
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';
COMMIT;

优化唯一索引冲突：

-- ❌ 高并发下容易死锁
INSERT INTO users (username, email) VALUES ('test', 'test@example.com');

-- ✅ 使用INSERT IGNORE或ON DUPLICATE KEY UPDATE
INSERT IGNORE INTO users (username, email) VALUES ('test', 'test@example.com');
-- 或者
INSERT INTO users (username, email) VALUES ('test', 'test@example.com')
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- ✅ 使用Redis预分配唯一ID
-- 在Redis中使用INCR生成唯一ID，避免数据库唯一索引冲突

4.3 事务隔离级别优化

隔离级别选择：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 或者在my.cnf中全局设置
[mysqld]
transaction-isolation = READ-COMMITTED

不同隔离级别的适用场景：

• READ UNCOMMITTED：极少使用，可能读到脏数据
• READ COMMITTED：适合大多数读多写少场景，避免脏读
• REPEATABLE READ（默认）：适合需要数据一致性的场景，但可能产生幻读
• SERIALIZABLE：最高隔离级别，性能最差，仅在极端场景使用

RC级别优化示例：

-- 在READ COMMITTED级别下，gap锁减少，可以降低锁竞争
-- 但需要注意不可重复读问题

-- 应用层需要处理可能的数据变化
START TRANSACTION;
-- 第一次读取
SELECT balance FROM accounts WHERE user_id = 123;
-- ... 业务逻辑 ...
-- 第二次读取可能不同（其他事务已修改）
SELECT balance FROM accounts WHERE user_id = 123;
COMMIT;

4.4 乐观锁与悲观锁应用

乐观锁实现：

-- 添加版本号字段
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

-- 更新时检查版本号
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 100 AND version = #{oldVersion};

-- 检查影响行数
-- 如果affected_rows=0，说明版本号已变化，需要重试

悲观锁实现：

-- 使用SELECT ... FOR UPDATE
START TRANSACTION;
-- 锁定行
SELECT stock FROM products WHERE id = 100 FOR UPDATE;
-- 执行更新
UPDATE products SET stock = stock - 1 WHERE id = 100;
COMMIT;

性能对比：

• 乐观锁：适合读多写少，冲突概率低的场景
• 悲观锁：适合写多读少，冲突概率高的场景

4.5 死锁检测与处理

死锁日志分析：

-- 开启死锁日志
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 查看最近死锁信息
SHOW ENGINE INNODB STATUS\G

-- 关注LATEST DETECTED DEADLOCK部分

死锁处理策略：

// 应用层重试机制
public <T> T executeWithRetry(TransactionCallback<T> callback, int maxRetries) {
    int attempt = 0;
    while (attempt < maxRetries) {
        try {
            return callback.doInTransaction();
        } catch (DeadlockException e) {
            attempt++;
            if (attempt >= maxRetries) {
                throw e;
            }
            // 指数退避
            try {
                Thread.sleep((long) (Math.random() * Math.pow(2, attempt) * 100));
            } catch (InterruptedException ie) {
                Thread.currentThread().interrupt();
                throw new RuntimeException(ie);
            }
        }
    }
    throw new RuntimeException("Max retries exceeded");
}

五、MySQL配置参数优化

5.1 InnoDB核心参数

缓冲区配置：

# my.cnf
[mysqld]
# 缓冲池大小：通常设置为物理内存的50%-70%
innodb_buffer_pool_size = 16G

# 缓冲池实例数：大于1G时设置为多个实例
innodb_buffer_pool_instances = 8

# 页大小：默认16K，对于大字段较多可考虑8K
innodb_page_size = 16384

# 缓冲池预热：启动时加载热数据
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

日志文件配置：

# 日志文件大小：通常设置为1-2G，太大恢复时间长
innodb_log_file_size = 2G

# 日志组数量：通常为2
innodb_log_files_in_group = 2

# 日志缓冲区大小
innodb_log_buffer_size = 64M

# 刷盘策略
innodb_flush_log_at_trx_commit = 1  # 1:每次commit刷盘，0:每秒刷盘

并发与线程配置：

# 最大连接数
max_connections = 1000

# 线程缓存
thread_cache_size = 50

# 每个连接的线程栈大小
thread_stack = 256K

# 并发插入锁
innodb_thread_concurrency = 0  # 0表示不限制

5.2 查询缓存与优化器

查询缓存（MySQL 8.0已移除）：

# MySQL 5.7及以下
query_cache_type = 0  # 建议关闭，高并发下性能反而下降
query_cache_size = 0

优化器参数：

# 优化器搜索深度：复杂查询可能需要增大
optimizer_search_depth = 0  # 0表示自动选择

# 优化器修剪级别
optimizer_prune_level = 1

# 优化器成本模型
optimizer_cost_model = 'default'

# MRR（Multi-Range Read）优化
optimizer_switch = 'mrr=on,mrr_cost_based=on'

5.3 I/O相关参数

刷新策略：

# 刷新相邻页：有助于提升顺序I/O性能
innodb_flush_neighbors = 1  # 机械硬盘设为1，SSD设为0

# I/O线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 刷新方法：O_DIRECT绕过OS缓存
innodb_flush_method = O_DIRECT

# 刷脏页比例
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10

六、监控与诊断体系

6.1 实时监控指标

MySQL内置监控：

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_TRX;

-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 查看性能模式
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/io/file/innodb/%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

关键监控指标：

• QPS/TPS：每秒查询/事务数
• 连接数：Threads_connected / max_connections
• 缓存命中率：Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)
• 锁等待时间：Innodb_row_lock_waits / Innodb_row_lock_time_avg
• 慢查询比例：Slow_queries / Questions

6.2 慢查询分析工具

Percona Toolkit使用：

# 安装Percona Toolkit
sudo apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 实时监控
pt-mysql-summary --user=root --password

# 索引建议
pt-index-usage /var/log/mysql/slow.log --host localhost

pt-query-digest输出示例：

# 120s time average, 10s max
# 50.0% (6000/12000) [ID 0xABCDEF] 5s average, 10s max
SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT ?, ?

6.3 性能模式（Performance Schema）

启用性能模式：

-- MySQL 5.6+默认启用
-- 检查是否启用
SELECT * FROM performance_schema.setup_instruments 
WHERE NAME LIKE 'statement/%';

-- 开启特定监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/%';

诊断慢查询：

-- 查看最近执行的SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec,
    SUM_ROWS_SENT,
    SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

6.4 第三方监控工具

Prometheus + Grafana：

# mysqld_exporter配置
# docker-compose.yml
version: '3'
services:
  mysqld_exporter:
    image: prom/mysqld-exporter
    environment:
      DATA_SOURCE_NAME: "user:password@(localhost:3306)/"
    ports:
      - "9104:9104"

关键监控面板：

• MySQL Overview
• InnoDB Metrics
• Replication Lag
• Query Performance

七、高级优化技巧

7.1 批量操作优化

批量插入：

-- ❌ 性能差：逐条插入
INSERT INTO orders (user_id, amount) VALUES (1, 100);
INSERT INTO orders (user_id, amount) VALUES (2, 200);
INSERT INTO orders (user_id, amount) VALUES (3, 300);

-- ✅ 性能好：批量插入
INSERT INTO orders (user_id, amount) VALUES 
(1, 100),
(2, 200),
(3, 300),
-- ... 更多值
(1000, 500);

-- ✅ 性能更好：使用LOAD DATA（百万级数据）
LOAD DATA LOCAL INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(user_id, amount);

批量更新：

-- ❌ 性能差：多个UPDATE
UPDATE products SET stock = 10 WHERE id = 1;
UPDATE products SET stock = 20 WHERE id = 2;

-- ✅ 性能好：单个CASE WHEN
UPDATE products SET stock = CASE id
    WHEN 1 THEN 10
    WHEN 2 THEN 20
    WHEN 3 THEN 30
END
WHERE id IN (1, 2, 3);

-- ✅ 使用临时表
CREATE TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    stock INT
);
INSERT INTO temp_updates VALUES (1,10), (2,20), (3,30);
UPDATE products p
JOIN temp_updates t ON p.id = t.id
SET p.stock = t.stock;

7.2 表结构优化

数据类型选择：

-- ❌ 浪费空间
username VARCHAR(255)  -- 实际平均长度20字符

-- ✅ 合理长度
username VARCHAR(50)

-- ❌ 使用TEXT/BLOB
description TEXT  -- 大文本存储

-- ✅ 使用VARCHAR或JSON
description VARCHAR(1000)  -- 1000字符足够
-- 或者
properties JSON  -- 结构化数据

范式与反范式平衡：

-- 范式化：减少冗余
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    INDEX idx_user (user_id)
);

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50)
);

-- 反范式化：减少JOIN
CREATE TABLE orders_denormalized (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(50),  -- 冗余字段
    amount DECIMAL(10,2)
);

7.3 分区表优化

Range分区：

CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    log_time DATETIME,
    message TEXT,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询时自动分区裁剪
SELECT * FROM logs WHERE log_time >= '2024-01-01';  -- 只扫描p2024分区

Hash分区：

CREATE TABLE user_behavior (
    id BIGINT,
    user_id BIGINT,
    action VARCHAR(50),
    created_at TIMESTAMP
) PARTITION BY HASH(user_id) PARTITIONS 16;

-- 数据均匀分布，适合高并发写入

7.4 全文索引优化

MySQL全文索引：

-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT idx_content (title, content)
) ENGINE=InnoDB;

-- 使用全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

-- 布尔模式
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+database -performance' IN BOOLEAN MODE);

替代方案：

• Elasticsearch：更强大的全文搜索
• Manticore Search：高性能全文搜索引擎

八、实战案例：秒杀系统优化

8.1 问题场景

业务需求：100万用户同时抢购1000件商品，数据库频繁出现锁等待和慢查询。

8.2 优化方案

1. 缓存预热与库存扣减：

// Redis预减库存
public boolean seckill(Long productId, Long userId) {
    String stockKey = "seckill:stock:" + productId;
    String userKey = "seckill:user:" + productId;
    
    // 1. Redis预减库存（原子操作）
    Long stock = redisTemplate.opsForValue().decrement(stockKey);
    if (stock < 0) {
        redisTemplate.opsForValue().increment(stockKey); // 回滚
        return false; // 库存不足
    }
    
    // 2. 检查是否已购买
    if (redisTemplate.opsForSet().isMember(userKey, userId)) {
        redisTemplate.opsForValue().increment(stockKey); // 回滚
        return false; // 重复购买
    }
    
    // 3. 异步写入数据库
    sendSeckillMessage(productId, userId);
    
    return true;
}

// 消息队列处理异步下单
@RabbitListener(queues = "seckill.queue")
public void processSeckillMessage(SeckillMessage message) {
    try {
        // 批量插入订单
        batchInsertOrders(message.getOrders());
    } catch (Exception e) {
        // 失败补偿：回滚Redis库存
        redisTemplate.opsForValue().increment("seckill:stock:" + message.getProductId());
    }
}

2. 数据库表结构优化：

-- 库存表
CREATE TABLE product_stock (
    product_id BIGINT PRIMARY KEY,
    stock INT NOT NULL,
    version INT DEFAULT 0,  -- 乐观锁版本号
    INDEX idx_stock (stock)  -- 库存索引
) ENGINE=InnoDB;

-- 订单表（分表）
CREATE TABLE seckill_order_0 (
    id BIGINT PRIMARY KEY,
    product_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(10,2),
    created_at TIMESTAMP,
    INDEX idx_user_product (user_id, product_id)
) ENGINE=InnoDB;

-- 分表路由
-- 按user_id取模分16张表

3. MySQL参数调整：

# 针对秒杀场景的特殊配置
[mysqld]
# 减少锁等待超时
innodb_lock_wait_timeout = 5

# 增大redo log，减少刷盘频率
innodb_log_file_size = 4G
innodb_log_buffer_size = 128M

# 优化并发
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# 关闭查询缓存（高并发写入场景）
query_cache_type = 0
query_cache_size = 0

4. 监控与降级：

// 熔断降级
@SentinelResource(value = "seckill", blockHandler = "seckillBlockHandler")
public boolean seckill(Long productId, Long userId) {
    // 正常逻辑
}

public boolean seckillBlockHandler(Long productId, Long userId, BlockException ex) {
    // 降级逻辑：直接返回失败或使用备用库存
    return false;
}

8.3 优化效果

• QPS：从500提升到5000+
• 响应时间：从2s降低到100ms
• 锁等待：减少90%
• 成功率：从60%提升到95%

九、总结与最佳实践

9.1 优化优先级

1. 架构优化：读写分离、缓存、分库分表
2. SQL优化：索引、查询改写、执行计划
3. 配置优化：参数调优、资源分配
4. 硬件升级：SSD、内存、CPU

9.2 持续优化建议

• 建立监控体系：实时发现性能问题
• 定期慢查询分析：每周review慢查询日志
• 压测与容量规划：提前发现瓶颈
• 知识库建设：记录优化案例和经验

9.3 避免常见误区

• ❌ 盲目增加索引（会降低写性能）
• ❌ 过度分库分表（增加复杂度）
• ❌ 忽视应用层优化（只关注数据库）
• ❌ 不监控就优化（没有数据支撑）

通过以上策略的综合应用，可以有效应对高并发场景下的MySQL性能挑战，构建稳定、高效的数据库系统。记住，优化是一个持续的过程，需要根据业务发展和数据增长不断调整策略。