详解MySQL调优

目录

1. SQL 语句优

1.1 避免低效查询

 1.2 索引优化

1.3 分析执行计划

2. 数据库配置优化

2.1 核心参数调整

2.2 表结构与存储引擎

2.3 存储引擎选择

3. 事务与锁优化

3.1 事务控制

3.2 锁机制优化

3.3 批量操作优化

4. 其他优化手段

4.1 监控与分析工具

4.2 读写分离与分库分表

5. 总结

---

1. SQL 语句优

1.1 避免低效查询

• 禁止 SELECT *：明确指定所需字段，减少数据传输和内存消耗。

• 合理使用 LIMIT：分页查询时通过 LIMIT offset, size 限制返回行数，避免全表扫描。

• 优化子查询：将部分子查询改写为 JOIN（尤其是关联子查询），例如：

  -- 低效
  SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
  -- 优化后
  SELECT users.* FROM users JOIN orders ON users.id = orders.user_id;

 1.2 索引优化

• 创建有效索引：对 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列建立索引。

• 避免索引失效常见索引失效原因及解决方案：

  • 不在索引列上使用函数或运算（如 WHERE YEAR(create_time) = 2023）。

  • 注意最左前缀原则，联合索引需按顺序使用。

• 覆盖索引：通过索引直接返回数据，避免回表（如 SELECT id, name FROM users WHERE age=30，若 (age, name) 是联合索引，则无需查表）。

1.3 分析执行计划

• 使用 EXPLAIN查看执行计划EXPLAIN详解：

  • type 字段：避免 ALL（全表扫描），追求systyem、const、eq_ref、ref、index。

  • Extra 字段：警惕 Using filesort（未利用索引排序）和 Using temporary（临时表）。

2. 数据库配置优化

2.1 核心参数调整

• 缓冲池大小：innodb_buffer_pool_size 设置为物理内存的 70%~80%，确保热点数据在内存中。

• 连接数配置：合理设置 max_connections（默认 151），避免连接耗尽或资源浪费。

• 日志优化：增大 innodb_log_file_size（如 1G）和 innodb_log_buffer_size（如 64M），减少磁盘 I/O。

  innodb_buffer_pool_size = 16G
  max_connections = 500
  innodb_log_file_size = 1G

2.2 表结构与存储引擎

• 选择合适的数据类型：如用 INT 而非 VARCHAR 存储数字，用 DATETIME 替代 TIMESTAMP（需时区时例外）。

• 范式与反范式平衡：适度冗余减少 JOIN，如高频查询的用户名可冗余到订单表。

• 分区表：对超大数据表按时间或范围分区，提升查询效率。

2.3 存储引擎选择

• InnoDB：支持事务、行级锁，适合高并发写入场景（如订单系统）。

• MyISAM：仅适合读多写少且无需事务的场景（如日志表），因表锁和崩溃恢复能力差。

3. 事务与锁优化

3.1 事务控制

• 短事务原则：尽早提交事务，避免长事务占用锁资源。

• 隔离级别选择：默认 REPEATABLE READ 平衡一致性与性能，若允许幻读可降级到 READ COMMITTED。

3.2 锁机制优化

• 行锁升级问题：确保 WHERE 条件走索引，否则 InnoDB 退化为表锁。

• 死锁预防：

  • 按固定顺序访问多张表（如先 A 后 B）。

  • 批量更新时按主键排序。

3.3 批量操作优化

• 分批提交：如每 1000 条数据 COMMIT 一次，减少锁持有时间。

• 高效导入：用 LOAD DATA INFILE 替代 INSERT，速度提升 10~100 倍。

4. 其他优化手段

4.1 监控与分析工具

• 慢查询日志：开启 slow_query_log，捕获执行时间超过 long_query_time（如 2s）的 SQL。

  slow_query_log = 1
  long_query_time = 2

• Performance Schema：监控锁、I/O、线程等资源使用情况。

4.2 读写分离与分库分表

• 读多写少时，通过主从复制分散读请求。

• 数据量极大时，使用分库分表（如 ShardingSphere）。

5. 总结

        MySQL 调优需结合具体场景，通过 分析慢查询、调整配置、优化事务逻辑 逐步实施。关键点包括：

• SQL 是核心：低效 SQL 可能抵消所有配置优化。

• 索引是双刃剑：过多索引影响写入性能。

• 监控驱动优化：持续观察数据库状态，针对性调整。