一、基础配置优化：参数调优的黄金法则

1.1 内存参数配置

内存是MySQL性能的核心资源，需根据服务器总内存合理分配：

• innodb_buffer_pool_size：建议设置为可用物理内存的50%-70%。例如32GB内存服务器可配置为20GB：

  SET GLOBAL innodb_buffer_pool_size = 20480 * 1024 * 1024; -- 20GB

• key_buffer_size（MyISAM引擎）：若使用MyISAM表，建议配置为总内存的25%
• query_cache_size：MySQL 8.0已移除查询缓存，低版本建议关闭（query_cache_type=0）

1.2 连接与线程配置

• max_connections：根据并发量设置，建议值=并发峰值*1.2。例如预期500并发可设为600：

  SET GLOBAL max_connections = 600;

• thread_cache_size：建议设置为max_connections的25%-50%，减少线程创建开销
• table_open_cache：控制打开表描述符数量，建议值=表数量*2+max_connections

1.3 日志配置优化

• binlog_format：生产环境建议使用ROW模式，保证数据一致性：

  SET GLOBAL binlog_format = 'ROW';

• sync_binlog：设置为1保证事务安全，但影响性能；可设为100平衡安全性与性能
• innodb_log_file_size：建议设置为256MB-2GB，过小会导致频繁checkpoint

二、索引优化：从设计到维护

2.1 索引设计原则

• 复合索引顺序：遵循最左前缀原则，将高选择性列放在左侧
• 避免过度索引：每个索引会增加10%左右的写入开销
• 覆盖索引：尽量让查询通过索引即可获取数据，减少回表操作

2.2 索引维护命令

• 分析索引使用情况：

  SELECT * FROM sys.schema_unused_indexes; -- 查找未使用索引
  SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

• 重建碎片化索引：当索引碎片率超过30%时重建：

  ALTER TABLE orders ENGINE=InnoDB; -- 重建表及所有索引

2.3 特殊索引类型

• 全文索引：适用于文本搜索，需配置ft_min_word_len（中文需改用ngram插件）
• 函数索引：MySQL 8.0+支持，可对计算列创建索引：

  CREATE INDEX idx_lower_name ON users((LOWER(name)));

三、查询优化：从EXPLAIN到重构

3.1 查询分析流程

1. 使用EXPLAIN分析执行计划：

   EXPLAIN SELECT * FROM orders WHERE customer_id=100 AND order_date>'2023-01-01';

2. 重点关注type列（ALL表示全表扫描）、key列（是否使用索引）、rows列（预估扫描行数）

3.2 常见优化手段

• 避免SELECT *：只查询必要字段，减少IO开销
• 合理使用JOIN：小表驱动大表，确保JOIN字段有索引

• 分页优化：避免大偏移量分页，改用游标分页：

  -- 传统分页（性能差）
  SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
  -- 游标分页（性能优）
  SELECT * FROM orders WHERE id > last_id ORDER BY id LIMIT 20;

3.3 慢查询治理

• 开启慢查询日志：

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询

• 使用pt-query-digest等工具分析慢查询日志

四、架构优化：从单机到分布式

4.1 读写分离方案

• 主从复制：配置一主多从架构，分散读压力

  # my.cnf主库配置
  [mysqld]
  server-id=1
  log-bin=mysql-bin
  binlog-format=ROW
  # 从库配置
  [mysqld]
  server-id=2
  read_only=ON

• 代理层选择：可使用ProxySQL或MySQL Router实现自动路由

4.2 分库分表策略

• 水平分表：按范围、哈希或时间分片，例如按用户ID哈希分10库：

  -- 分片函数示例（应用层实现）
  function getShardId(userId) {
    return userId % 10;
  }

• 垂直分表：将大表按字段拆分，例如将用户表拆分为基础信息表和扩展信息表

4.3 缓存层设计

• 多级缓存架构：本地缓存（Guava）+ 分布式缓存（Redis）
• 缓存策略：
  • 热点数据缓存
  • 查询结果缓存
  • 避免缓存穿透（空值缓存）和雪崩（随机过期时间）

五、监控与维护：持续优化

5.1 关键指标监控

• QPS/TPS：每秒查询/事务数
• 连接数：当前活动连接数
• 缓存命中率：InnoDB buffer pool命中率应>99%
• 锁等待：通过SHOW ENGINE INNODB STATUS查看

5.2 定期维护任务

• 表维护：每月执行一次ANALYZE TABLE更新统计信息
• 日志轮转：配置logrotate自动轮转错误日志和慢查询日志
• 备份验证：定期恢复备份验证可用性

5.3 版本升级策略

• 小版本升级（如5.7.x→5.7.y）：可在线升级
• 大版本升级（如5.7→8.0）：需测试兼容性，重点关注：
  • 字符集默认值变化
  • 废弃参数处理
  • 视图/存储过程兼容性

六、实战案例：电商系统优化

案例背景

某电商系统订单查询接口响应时间达3秒，TPS仅50

优化步骤

1. 诊断问题：通过慢查询日志发现以下SQL：

   SELECT * FROM orders 
   WHERE user_id=123 
   AND status='paid' 
   AND create_time BETWEEN '2023-01-01' AND '2023-12-31'
   ORDER BY id DESC 
   LIMIT 1000, 20;

2. 优化方案：

   • 添加复合索引：(user_id, status, create_time, id)
   • 改写为游标分页
   • 限制返回字段

3. 优化后SQL：

   SELECT id, order_no, amount 
   FROM orders 
   WHERE user_id=123 
   AND status='paid' 
   AND create_time < '2023-01-01' -- 通过上一页最后一条记录的create_time
   ORDER BY create_time DESC, id DESC 
   LIMIT 20;

4. 效果验证：响应时间降至80ms，TPS提升至300+

通过系统性的优化方法论，结合具体业务场景的定制化调整，可实现MySQL性能的显著提升。建议建立持续优化机制，定期评估性能指标，及时调整优化策略。