Mysql全局优化与Mysql8新特性

Mysql全局优化与Mysql8新特性

一、MySQL 全局优化总结

mysql可以从硬件、系统配置、库表结构、SQL 及索引等维度展开全局优化，但是其中SQL 及索引优化是效果最好、成本最低的手段。重点优化参数如下：

1. MySQL Server 系统参数（[mysqld] 标签下）

• max_connections=3000：最大连接数，需结合内存计算（每个连接最小 256K、最大 64M，3000 连接最小占 750M，最大 192G），避免连接过多导致磁盘 SWAP。
• max_user_connections=2980：用户最大连接数，预留部分连接供 DBA 管理。
• back_log=300：连接请求暂存队列大小，超过则拒绝新请求。
• wait_timeout=300、interactive_timeout=300：分别控制应用连接、客户端连接的空闲断开时间（默认 8 小时，优化为 5 分钟），减少无效连接。
• sort_buffer_size=4M：每个连接的排序缓冲区，过大 + 高并发可能耗尽内存（如 500 连接约占 2G），适当增加该值可以提高ORDER BY 或 GROUP BY效率。
• join_buffer_size=4M：表关联缓存，每个连接独享，需合理配置避免内存溢出。

2. InnoDB 参数

• innodb_thread_concurrency=64：InnoDB 线程并发数，建议设为 CPU 核心数或 2 倍，避免线程锁争用。
• innodb_buffer_pool_size=40G：缓冲池大小，通常为物理内存的 60%-70%，直接影响性能（内存越大，性能越好）。
  • 缓冲池命中率需≥99%，计算公式：Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads + Innodb_buffer_pool_read_ahead)。
• innodb_lock_wait_timeout=10：行锁超时时间（默认 50 秒），根据业务调整。
• innodb_flush_log_at_trx_commit=1：事务日志刷新策略（确保 ACID）。

3. Binlog 参数

• sync_binlog=1：控制 binlog 同步策略（确保日志安全）。

二、MySQL 8.0 新特性

文档重点介绍了 MySQL 8.0 的 16 项关键新特性，建议使用 8.0.17 及之后版本：

1. 新增降序索引

   • InnoDB 支持真正的降序索引（5.7 仅语法支持，实际为升序），可避免ORDER BY时的 filesort，提升排序性能。

2. GROUP BY 不再隐式排序

   • 5.7 中GROUP BY默认对结果排序，8.0 需显式加ORDER BY才排序，减少不必要的排序开销。

3. 增加隐藏索引

   • 用invisible关键字创建隐藏索引（主键不可隐藏），优化器默认不使用，但后台仍维护；可通过set session optimizer_switch="use_invisible_indexes=on"临时启用，适合软删除索引（避免直接删除后恢复的高成本）。

4. 新增函数索引

   • 支持基于函数 / 表达式的索引（如create index func_idx on t3((UPPER(c2)))），解决查询中使用函数导致索引失效的问题（基于虚拟列实现）。

5. InnoDB 的 SELECT FOR UPDATE 支持跳过锁等待

   • 新增NOWAIT（获取不到锁立即报错）和SKIP LOCKED（跳过锁定行，只返回未锁定行），适用于高并发场景（如余票查询），避免长时间锁等待。

6. 新增 innodb_dedicated_server 自适应参数

   • 自动根据服务器内存配置innodb_buffer_pool_size、innodb_log_file_size等参数，适合专用 MySQL 服务器（非专用服务器不建议开启）。

7. 死锁检查控制

   • 新增动态变量innodb_deadlock_detect（默认开启），高并发场景可关闭以提升性能（需确保极少死锁，同时调小锁等待超时）。

8. undo 文件不再使用系统表空间

   • 默认创建 2 个独立 UNDO 表空间，不再依赖系统表空间，提升管理灵活性。

9. binlog 日志过期时间精确到秒

   • 过期时间参数从expire_logs_days（天级）改为binlog_expire_logs_seconds（秒级），控制更精细。

10. 窗口函数（分析函数）

    • 支持SUM() OVER()、ROW_NUMBER()等窗口函数，可在分组后保留原表结构（不合并行），实现复杂统计分析（如分区求和、排序序号等）。

    序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

    分布函数：PERCENT_RANK()、CUME_DIST()

    前后函数：LAG()、LEAD()

    头尾函数：FIRST_VALUE()、LAST_VALUE()

    其它函数：NTH_VALUE()、NTILE()

11. 默认字符集改为 utf8mb4

    • 5.7 默认 latin1，utf8 指向 utf8mb3；8.0 默认 utf8mb4，utf8 指向 utf8mb4，更好支持 emoji 等字符。

12. MyISAM 系统表全部替换为 InnoDB

    • 系统表（如 mysql 库）和数据字典表均采用 InnoDB，默认实例无 MyISAM 表（除非手动创建）。

13. 元数据存储变动

    • 删除.frm 等元数据文件，元数据集中存储在mysql.ibd中，简化文件管理。

14. 自增变量持久化

    • 8.0 前重启后AUTO_INCREMENT会重置为max(primary key)+1，8.0 持久化该值，避免重启后主键冲突。

15. DDL 原子化

    • InnoDB 表的 DDL 支持事务完整性（要么全成功，要么全回滚），包括库、表、索引等的CREATE/ALTER/DROP及账户管理相关 DDL。

16. 参数修改持久化

    • 支持set persist关键字在线修改全局参数并持久化到mysqld-auto.cnf（JSON 格式），重启后生效（优先级高于 my.cnf），解决set global重启失效问题。