ubuntu mysql如何优化

Ubuntu 上 MySQL 优化的实操路线图

一 基线评估与监控

• 明确硬件与负载：内存容量、CPU 核数、存储类型（优先 SSD）、业务读写比例与峰值并发。
• 建立监控基线：使用 Performance Schema 与状态变量（如 Threads_connected、Threads_created、Innodb_buffer_pool_reads/requests）观察连接与缓冲池命中率；命中率目标建议 >99%，计算方式：命中率 = (1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100%。
• 慢查询治理：开启慢查询日志，设置 long_query_time=1（单位秒），用 pt-query-digest 分析 TOP SQL；必要时用 EXPLAIN ANALYZE（MySQL 8.0+） 获取真实执行成本。

二 InnoDB 与关键内存参数

• 缓冲池大小：将 innodb_buffer_pool_size 设为物理内存的 50%–70%；当缓冲池 >1GB 时，将 innodb_buffer_pool_instances 设为 8 的倍数以降低锁竞争；重启后使用预热（如 LOAD INDEX INTO CACHE）避免性能骤降。
• 日志与持久性：提升 innodb_log_file_size（如 512MB–2GB）以减少检查点抖动；在允许一定持久性风险时，将 innodb_flush_log_at_trx_commit 设为 2（或 0）以降低提交延迟，设为 1 时安全性最高；写密集场景适当提高 sync_binlog 的组提交效率（如启用组提交相关参数）。
• 连接与线程：按并发模型设置 max_connections，并通过 thread_cache_size 减少线程创建开销；经验上可将线程缓存设为最大连接的 25%–50%，并观察 Threads_created/秒，超过 5 时增大缓存。
• 查询缓存：在 MySQL 5.7.20 后弃用、8.0 移除，高并发写场景建议直接关闭（query_cache_type=0/query_cache_size=0），避免失效锁竞争。

三 Ubuntu 配置路径与变更流程

• 配置文件路径：常见为 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf；可用命令查找：sudo find / -name my.cnf。
• 修改与生效：编辑后执行 sudo systemctl restart mysql；变更前备份原始配置，变更后逐项验证。
• 示例片段（仅示意，按实际内存与负载调整）：
  • [mysqld]
    • innodb_buffer_pool_size = 8G
    • innodb_log_file_size = 512M
    • innodb_flush_log_at_trx_commit = 2
    • max_connections = 500
    • slow_query_log = 1
    • long_query_time = 1
    • character-set-server = utf8mb4
    • collation-server = utf8mb4_unicode_ci
    • innodb_file_per_table = 1
    • innodb_flush_method = O_DIRECT
    • innodb_io_capacity = 800
• 动态设置示例（无需重启，立即生效）：SET GLOBAL innodb_buffer_pool_size=8589934592;（8GB）

四 查询与索引优化

• 索引策略：为高频过滤/排序/关联列建立合适索引；多列条件使用复合索引并遵循最左前缀；用 SHOW INDEX 检查冗余与缺失；用 EXPLAIN/EXPLAIN ANALYZE 验证执行计划是否走索引、是否出现文件排序或临时表。
• SQL 写法：避免 **SELECT ***，只取必要列；优先 JOIN 替代低效子查询；避免在 WHERE 中对列做函数计算（会阻断索引）；大批量导入时，临时关闭唯一性与外键检查（SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;）并在导入后恢复，可显著提速（务必在可控维护窗口执行）。

五 存储引擎与维护策略

• 引擎选择：默认使用 InnoDB（行级锁、MVCC、崩溃恢复）；仅在明确不需要事务/一致性的日志/统计类场景再考虑 MyISAM。
• 表空间：启用 innodb_file_per_table，便于空间回收与碎片管理。
• 维护与碎片：对高更新/删除表定期执行 ANALYZE TABLE（更新统计信息）与 OPTIMIZE TABLE（重建表与索引，释放碎片）；大表可结合分区/分片降低单表压力。
• 版本与特性：优先使用 MySQL 8.0+，可利用 直方图统计、资源组、InnoDB 自适配参数等新特性；在 NUMA 架构上结合 innodb_numa_interleave=1 提升吞吐。