MySQL 2000万级数据性能优化全攻略（附实战代码）

性能瓶颈诊断

1. 全表扫描：EXPLAIN出现type=ALL
2. 索引失效：联合索引顺序错误或未覆盖查询字段
3. 锁竞争：SHOW ENGINE INNODB STATUS显示大量锁等待
4. 缓冲池不足：Innodb_buffer_pool_reads磁盘读取激增
5. 连接风暴：Threads_connected突破500+

核心优化策略

1. 索引优化（关键操作）

-- 覆盖索引优化  
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);  

-- 前缀索引（文本字段优化）  
ALTER TABLE products ADD INDEX idx_name (name(20));  

-- 删除冗余索引  
SELECT * FROM sys.schema_redundant_indexes;  

2. 查询重写（典型场景）

-- 改写分页查询（避免OFFSET）  
SELECT * FROM logs  
WHERE id > 100000  
ORDER BY id LIMIT 50;  

-- IN子查询转JOIN  
SELECT t1.* FROM users t1  
JOIN (SELECT DISTINCT user_id FROM orders) t2  
ON t1.id = t2.user_id;  

3. InnoDB引擎调优

# my.cnf  
innodb_buffer_pool_size = 16G      # 物理内存的70%-80%  
innodb_flush_log_at_trx_commit = 2 # 非严格ACID场景可调优  
innodb_io_capacity = 20000         # SSD建议值  
innodb_read_io_threads = 16  
innodb_write_io_threads = 16  

进阶架构优化

1. 读写分离方案

-- 主库写操作  
INSERT INTO payments (...) VALUES (...);  

-- 从库读操作（负载均衡）  
/* mycat:balance */  
SELECT * FROM orders WHERE user_id = 123;  

2. 分库分表（Sharding）

// ShardingSphere配置示例  
rules:  
- !SHARDING  
  tables:  
    user_order:  
      actualDataNodes: db_${0..7}.order_${0..15}  
      databaseStrategy:  
        standard:  
          shardingColumn: user_id  
          shardingAlgorithmName: mod_hash  
      tableStrategy:  
        standard:  
          shardingColumn: order_time  
          shardingAlgorithmName: interval_month  

3. 冷热数据分离

-- 归档历史数据  
CREATE TABLE orders_2023 LIKE orders;  
INSERT INTO orders_2023 SELECT * FROM orders WHERE year=2023;  
DELETE FROM orders WHERE year=2023;  

-- 使用分区表自动管理  
ALTER TABLE orders PARTITION BY RANGE (year) (  
    PARTITION p2023 VALUES LESS THAN (2024),  
    PARTITION p2024 VALUES LESS THAN (2025)  
);  

** 高频问题解决方案**

1. 慢查询实时捕获

-- 开启慢日志（阈值500ms）  
SET GLOBAL slow_query_log = ON;  
SET GLOBAL long_query_time = 0.5;  

-- 分析工具  
mysqldumpslow -s t /var/log/mysql-slow.log  

2. 连接池优化

# 连接池配置（如HikariCP）  
maximumPoolSize=100  
minimumIdle=20  
idleTimeout=30000  
maxLifetime=1800000  

3. 紧急锁表处理

-- 查看阻塞进程  
SELECT  
  r.trx_id waiting_trx_id,  
  r.trx_query waiting_query,  
  b.trx_id blocking_trx_id,  
  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;  

-- 终止会话  
KILL CONNECTION [process_id];  

性能压测指标（参考值）

必备工具集

1. 诊断工具：pt-query-digest（慢日志分析）sysbench（压力测试）
2. 监控系统：Prometheus + MySQL ExporterPercona Monitoring and Management
3. 运维神器：pt-online-schema-change（在线DDL）gh-ost（无锁表结构变更）

未来扩展建议

1. TiDB融合方案：

sql

-- 同步MySQL数据到TiDB 
CREATE PLUGIN tidb SONAME 'ha_tidb.so';

2. 云原生架构：使用AWS Aurora或Aliyun PolarDB自动扩展
3. 内存计算层：引入Redis缓存热点数据（如用户Session）

优化口诀：

• 索引要精：覆盖查询+最左前缀
• 查询要快：避免全扫+减少JOIN
• 配置要狠：缓冲池给足+线程调优
• 架构要稳：读写分离+分库分表

通过以上优化，2000万级数据可稳定支撑日均百万级请求，复杂查询响应时间控制在1秒内。