面试题：MySQL 数据库的性能优化方法有哪些？

MySQL 性能优化是一个系统工程，需要从多个层面进行分析和调优。以下是分层次的优化方法：

一、SQL 语句优化（最直接有效）

1. 查询优化

**避免 SELECT ***：只查询需要的列
合理使用索引：

  -- 创建合适的联合索引（注意最左前缀原则）
  CREATE INDEX idx_name_age ON users(name, age);

  -- 避免索引失效场景（使用函数、隐式转换等）
  -- 错误示例：WHERE YEAR(create_time) = 2023
  -- 正确示例：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

2. 复杂查询优化

分页优化：

  -- 低效：SELECT * FROM table LIMIT 10000, 20
  -- 高效：SELECT * FROM table WHERE id > last_id LIMIT 20

JOIN 优化：
小表驱动大表
确保关联字段有索引

3. 事务优化

控制事务粒度：避免长事务
合理设置隔离级别：通常使用 READ COMMITTED

二、索引优化（性能加速器）

1. 索引设计原则

高选择性列优先（区分度高的列）
联合索引列顺序：高频查询条件在前
覆盖索引：避免回表操作

  -- 使用覆盖索引
  SELECT id, name FROM users WHERE age > 20;
  -- 对应索引：INDEX (age, name, id)

2. 索引维护

定期分析表：

  ANALYZE TABLE users;

删除冗余索引：

  -- 使用 sys.schema_redundant_indexes 视图检查
  SELECT * FROM sys.schema_redundant_indexes;

三、数据库架构优化

1. 读写分离

主库负责写操作
从库负责读操作
使用中间件（如ProxySQL）自动路由

2. 分库分表

垂直拆分：按业务拆分
水平拆分：按数据范围/哈希拆分
使用分片中间件（如MyCat、ShardingSphere）

3. 缓存层

查询缓存（MySQL 8.0已移除）
应用缓存（Redis/Memcached）
缓冲池优化（InnoDB Buffer Pool）

四、服务器配置优化

1. InnoDB 关键参数

# my.cnf 配置示例
[mysqld]
innodb_buffer_pool_size = 12G  # 总内存的50-70%
innodb_buffer_pool_instances = 8  # 缓冲池实例数
innodb_io_capacity = 2000  # SSD建议2000+
innodb_flush_neighbors = 0  # SSD建议关闭

2. 全局参数

max_connections = 500  # 根据负载调整
thread_cache_size = 32  # 线程缓存
table_open_cache = 4000  # 表缓存

五、监控与维护

1. 性能监控工具

慢查询日志：

  SET GLOBAL slow_query_log = ON;
  SET GLOBAL long_query_time = 1;  # 超过1秒记录

Performance Schema：

  SELECT * FROM performance_schema.events_statements_summary_by_digest
  ORDER BY avg_timer_wait DESC LIMIT 10;

2. 定期维护

表维护：

  OPTIMIZE TABLE large_table;  # MyISAM
  ALTER TABLE innodb_table ENGINE=InnoDB;  # InnoDB重建

日志轮转：定期清理binlog/error log

六、高级优化技术

1. 执行计划干预

-- 使用优化器提示
SELECT /*+ INDEX(users idx_age) */ * FROM users WHERE age > 25;

2. 连接池优化

应用层使用连接池（HikariCP等）
配置合理的连接超时和回收策略

3. 批量操作优化

-- 批量插入代替单条插入
INSERT INTO users(name,age) VALUES ('a',20),('b',21),('c',22);

MySQL性能优化需要持续监控和迭代改进，建议使用如Percona Toolkit等专业工具辅助分析。

记住：优化前先测量，使用EXPLAIN、PROFILING等工具准确定位瓶颈。

THE END

MySQL 面试题