一、SQL语句性能优化：从执行计划到查询重写

1.1 执行计划分析（EXPLAIN）

执行计划是SQL优化的首要工具，通过EXPLAIN SELECT * FROM orders WHERE user_id=100可获取关键指标：

• type列：反映访问类型（const>eq_ref>ref>range>index>ALL），理想状态应达到range级别以上
• key列：显示实际使用的索引，NULL表示未使用索引
• rows列：预估扫描行数，数值过大需优化
• Extra列：包含”Using filesort”、”Using temporary”等警告信息

生产环境案例：某电商系统订单查询从全表扫描（type=ALL, rows=200万）优化为索引扫描（type=ref, rows=10）后，QPS提升15倍。

1.2 索引优化策略

• 复合索引设计原则：遵循最左前缀匹配，如INDEX(user_id, order_date)可支持WHERE user_id=100和WHERE user_id=100 AND order_date>'2023-01-01'
• 索引选择性计算：选择性=不重复值数/总行数，应优先为选择性高的列建索引
• 覆盖索引优化：通过SELECT user_id FROM orders WHERE user_id=100避免回表操作
• 索引维护成本：定期执行ANALYZE TABLE orders更新统计信息，避免索引失效

1.3 SQL重写技巧

• 避免SELECT *：明确指定字段可减少I/O，如SELECT id,order_no FROM orders
• JOIN优化：小表驱动大表，确保JOIN字段有索引
• 子查询改写：将SELECT * FROM a WHERE id IN (SELECT id FROM b)改为JOIN形式
• 批量操作优化：使用INSERT INTO orders VALUES (...),(...),(...)替代单条插入

二、InnoDB存储引擎核心参数配置

2.1 缓冲池（Buffer Pool）配置

• innodb_buffer_pool_size：建议设置为物理内存的50-70%，如32G服务器设为20G
• innodb_buffer_pool_instances：当buffer_pool>1G时，设置为8可减少锁竞争
• innodb_old_blocks_pct：默认37%，控制LRU算法中旧数据块比例
• innodb_old_blocks_time：默认1000ms，数据块需停留此时间后才移至热区

2.2 日志系统配置

• innodb_log_file_size：建议设置为256M-2G，过小会导致频繁checkpoint
• innodb_log_files_in_group：默认2，与log_file_size共同决定重做日志总量
• innodb_flush_log_at_trx_commit：
  • 1（默认）：每次提交都写入磁盘，保证ACID
  • 2：写入os缓存，每秒刷新，性能与安全性平衡
  • 0：每秒刷新，最高性能但可能丢数据
• sync_binlog：0表示由OS刷新，1表示每次提交都刷新，建议生产环境设为1

2.3 并发控制参数

• innodb_thread_concurrency：CPU核心数*2，限制并发线程数
• innodb_read_io_threads/innodb_write_io_threads：默认4，SSD环境可增至8-16
• innodb_io_capacity：根据存储设备设置，SSD建议2000-4000
• innodb_flush_neighbors：SSD环境设为0，禁用相邻页刷新

三、性能监控与诊断工具

3.1 慢查询日志分析

配置参数：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 单位秒
log_queries_not_using_indexes = 1

使用mysqldumpslow -s t /var/log/mysql/mysql-slow.log分析TOP慢查询

3.2 Performance Schema

启用关键监控项：

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'wait/%';
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_waits%';

查询锁等待：

SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE 'wait/lock%';

3.3 动态性能视图

• SHOW ENGINE INNODB STATUS：查看锁信息、事务状态
• SHOW GLOBAL STATUS：监控全局指标如Threads_connected、Innodb_buffer_pool_reads
• SHOW PROCESSLIST：实时查看连接状态

四、生产环境优化案例

4.1 高并发写入优化

某金融系统订单表每天写入500万条数据，优化方案：

1. 主键改用自增列替代UUID，减少页分裂
2. 关闭innodb_doublewrite（有备份环境时）
3. 调整innodb_flush_neighbors=0
4. 批量插入改为LOAD DATA INFILE
   结果：写入吞吐量从3000TPS提升至12000TPS

4.2 复杂查询优化

某报表查询涉及6表JOIN，执行时间12秒，优化步骤：

1. 为JOIN字段添加复合索引
2. 将子查询改写为临时表
3. 添加SQL_BIG_RESULT提示
4. 调整tmp_table_size=256M和max_heap_table_size=256M
   最终执行时间降至0.8秒

五、最佳实践建议

1. 基准测试：使用sysbench进行压力测试，建立性能基线
2. 渐进式优化：每次只修改一个参数，观察效果
3. 参数持久化：修改后执行SET PERSIST innodb_buffer_pool_size=4G
4. 监控告警：设置Connections、Threads_running等指标的阈值告警
5. 定期维护：每周执行OPTIMIZE TABLE（仅限MyISAM）和ANALYZE TABLE

通过系统化的性能调优，可使MySQL在相同硬件条件下实现3-10倍的性能提升。建议DBA建立完善的性能监控体系，结合业务特点制定个性化优化方案。