MySQL Performance Tuning: 10 Tips (English Version)

核心观点与关键数据

导入数据

• 推荐使用 MySQL Shell 的 Dump & Load Utility 进行数据导入，该工具支持并行导出和加载数据，并可选择导出完整实例、模式或表，同时支持 WHERE 子句过滤。
• 加速导入方法：禁用二进制日志和重做日志，调整 InnoDB 相关设置（如 innodb_max_dirty_pages_pct），需注意禁用日志需重启 MySQL 服务。

架构设计

• 主键索引：主键对 InnoDB 表性能至关重要，推荐使用顺序主键（如自增整数），避免使用非顺序主键导致随机 I/O 增加和页面重新平衡。未定义主键时，InnoDB 会创建隐藏主键，可能导致高并发写操作时的竞争问题。
• GIPK 模式：MySQL 8.0.30 及更高版本支持 GIPK 模式，自动生成不可见主键，优化性能。
• 索引管理：
  • 未使用索引：可通过 sys Schema 和 innodb_index_stats 识别未使用索引，建议删除以减少 I/O 开销。
  • 重复索引：需谨慎删除，建议先设置为不可见观察是否被使用。
  • 缺失索引：机器学习可自动化识别并建议创建，如 sys Schema 的 x$contains_95th_percentile_running_time 可定位耗时最长的查询。
• 并行索引创建：通过调整 innodb_ddl_threads、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 可显著加速索引创建，建议根据 CPU 核心数和内存配置优化参数。

内存管理

• InnoDB 缓冲池：保持工作集在内存中至关重要，推荐根据 innodb_buffer_pool_size 监控缓冲池使用率（满百分比、脏百分比），并确保磁盘读取比例低（如低于 5%）。
• 内存分配器：推荐使用 tcmalloc 替代默认的 glibc-malloc，安装方法及配置示例如下：

  sudo yum -y install gperftools-libs
  sudo vi /etc/systemd/system/mysqld.service.d/tcmalloc.conf
  [Service]
  Environment=LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so.4
  sudo systemctl daemon-reload
  sudo systemctl restart mysqld
  

查询优化

• 工作负载分析：通过 Performance Schema 和 sys Schema 分析查询模式（按表、按模式）的 I/O 等待统计，如 table_io_waits_summary_by_table 可展示读取/写入比例。
• 识别慢查询：使用 sys Schema 的 x$contains_95th_percentile_running_time 查找耗时最长的查询，如示例中 piday 模式的复杂 JOIN 查询。
• 自动化优化：MySQL 自动化索引功能（有限可用性）通过机器学习推荐索引创建/删除，提供性能和存储预测，并解释建议原因。

其他最佳实践

• InnoDB 重做日志：避免过度配置，推荐根据高峰流量估算容量：

  SELECT 
    ROUND(ABS(@a - @b) / 1024 / 1024, 2) AS MB_per_minute,
    ROUND(ABS(@a - @b) / 1024 / 1024 * 60, 2) AS GB_per_hour
  FROM (
    SELECT VARIABLE_VALUE AS current_lsn FROM performance_schema.global_status WHERE VARIABLE_NAME = 'InnoDB_redo_log_current_lsn' INTO @a;
    SELECT SLEEP(60);
    SELECT VARIABLE_VALUE AS current_lsn FROM performance_schema.global_status WHERE VARIABLE_NAME = 'InnoDB_redo_log_current_lsn' INTO @b;
  ) AS subquery;
  SET GLOBAL innodb_redo_log_capacity = 1.24 * 1024 * 1024 * 1024;
  

• InnoDB 专用服务器模式：通过 innodb_dedicated_server=1 启用，让 InnoDB 自动优化缓冲池和重做日志。

研究结论

• 通过合理配置主键、索引、内存分配器及自动化工具，可显著提升 MySQL 性能和稳定性。
• 机器学习在索引优化和慢查询识别中的应用，可减少人工干预并提高生产力。
• 监控和自动化是关键，推荐结合 sys Schema 和 Performance Schema 进行持续优化。