本文共 1995 字，大约阅读时间需要 6 分钟。

MySQL数据库性能优化指南

1. 数据文件大小控制

数据库中数据量的增加会直接导致数据文件和索引文件的体积膨胀，进而影响系统性能。为了维持数据库的高效运行，建议对单表数据量和数据文件大小进行适当限制。这样可以防止索引文件过大，避免性能急剧下降。

2. 碎片空洞管理

在删除数据时，数据库可能不会立即释放空间，而是通过标记空洞来记录。这会导致存储空间浪费。在实际应用中，建议定期检查数据库表的碎片情况。

• 检查方法：使用 SHOW TABLE STATUS 命令，查看 data_length 和 data_free 的比例。如果 data_free 占比过大，表明存在明显的碎片问题。

• 优化方法：使用 OPTIMIZE TABLE 命令或通过 ALTER TABLE命令重建表空间，确保数据文件占用合理。

3. 行存储格式选择

MySQL 5.7.9 及以上版本默认使用 innodb 表的动态行存储格式。相比于之前的紧凑格式，动态格式提供了更好的灵活性，但在存储非Unicode数据时仍能保持较低的存储占用。

4. 索引使用规范

索引能够显著提升查询性能，但也会影响数据写入速度和文件体积。索引的合理使用至关重要。

• 索引设计建议：

  • 主键字段应使用较小的整数类型（如 INT），避免使用 BIGINT。
  • 对于字符串字段，建议使用前缀索引，以减少索引大小。
  • 避免重复索引和冗余索引，确保每个索引都有明确的查询优化作用。

• 索引使用注意事项：

  • MySQL采用最左匹配原则，复合索引的查询会优先使用左边的字段。
  • 在 JOIN操作中，若字段类型不一致，索引无法生效。

5. 系统参数优化

1. general_log

此参数用于记录所有SQL语句提交到MySQL的日志文件。建议在非测试环境下关闭该参数，以避免日志占用过多空间。仅在需要分析异常查询时，临时开启并截取相关日志。

2. query_cache_size

用于缓存SQL语句和结果。频繁变化的数据表会导致缓存过期问题，因此建议将该参数设置为 0，禁用缓存功能。

3. tmp_table_size

在 GROUP BY 或 DISTINCT 操作中，若未使用索引，MySQL会生成临时表存储中间结果。建议适当增加此参数值，以避免临时表溢出到磁盘，影响性能。

4. innodb_buffer_pool_size

建议将其设置为物理内存的 50%-75%，以最大限度利用缓存空间。

5. innodb_buffer_pool_instances

将单个缓冲池分割为多个独立的内存块，提高并发处理能力，提升系统性能。

6. innodb_log_file_size 和 innodb_log_files_in_group

这两个参数共同决定了重做日志的文件大小和数量。增大文件大小可减少日志写入频率，但需权衡是否影响数据库恢复时间。

7. innodb_old_blocks_pct 和 innodb_old_blocks_time

通过设置这些参数，可以优化缓存管理，减少全表扫描带来的大规模更新对缓存的影响。

8. innodb_numa_interleave

防止内存被交换到 swap 分区，提升性能表现。

9. innodb_autoinc_lock_mode

在插入自增字段时，设置为 2 可避免 auto-inc 锁死锁问题，提升插入性能。

10. innodb_flush_method

建议在使用 SSD 等高性能存储时，设置为 O_DIRECT，以减少日志写入开销。

11. innodb_doublewrite

如果底层存储支持原子写操作，可以关闭双写，提升性能。

12. innodb_io_capacity

在使用 SSD 时，可适当提高此参数，提升 flush 操作的频率。

13. innodb_thread_concurrency

在高并发环境下，适当增加该参数，可以降低并发线程切换的开销，提升系统吞吐量。

14. innodb_flush_log_at_trx_commit

建议设置为 1，既保证数据安全性，又在磁盘足够好的情况下维持较高的性能。

15. sync_binlog

建议设置为 1，确保 binlog 数据安全同步到磁盘。

16. binlog_format

MySQL 5.7.7 及以上版本已默认支持 ROW 格式，建议采用该格式以确保数据一致性。

17. binlog_order_commits

根据实际需求决定是否开启，以权衡事务提交顺序和性能。

18. tx_isolation

建议设置为 READ COMMITTED，以在保证性能的前提下，确保数据一致性。

19. slave_parallel_workers

在主从复制过程中，设置为非零值可开启多线程复制，提升同步性能。

转载地址：http://gabfk.baihongyu.com/