一、MySQL 全局优化1. 优化优先级SQL 及索引的优化效果最好且成本最低因此工作中应优先投入时间在 SQL 语句和索引设计上。2. 服务端核心参数my.ini / my.cnf以下参数均位于配置文件的 [mysqld] 标签下假设服务器配置为 CPU 32 核、内存 64G、磁盘 2T SSD。max_connections允许用户连接的最大数量剩余连接数用作 DBA 管理。一个连接最少占用 256K 内存最大 64M。若 3000 个用户同时连接最小需 750M最大可达 192G连接数过高不一定提升吞吐量反而可能占用更多系统资源。max_user_connections单个用户允许的最大连接数。back_logMySQL 能够暂存的连接数量。当连接数达到 max_connections 时新请求会进入堆栈等待超过 back_log 则被拒绝。wait_timeout / interactive_timeout分别针对 JDBC 连接和 mysql client 连接空闲超过设定秒数后断开默认 28800 秒8 小时。sort_buffer_size每个需要排序的线程分配的缓冲区大小增加该值可加速 ORDER BY 或 GROUP BY。该参数是 connection 级参数并非越大越好过高设置加高并发可能耗尽系统内存。join_buffer_size用于表关联缓存的大小与 sort_buffer_size 一样每个连接独享。3. InnoDB 相关参数innodb_thread_concurrency设置 InnoDB 线程并发数默认 0 表示不限制。若设置建议与 CPU 核心数相同或为其 2 倍不宜过大否则线程间锁争用严重。innodb_buffer_pool_sizeInnoDB 缓冲池大小一般为物理内存的 60%-70%内存大小直接反映数据库性能。innodb_lock_wait_timeout行锁锁定时间默认 50 秒根据公司业务而定没有标准值。innodb_flush_log_at_trx_commit控制事务提交时日志刷盘策略。4. 缓冲池命中率判断通过查看服务器状态比较物理磁盘读取和内存读取的比例来判断缓冲池命中率通常 InnoDB 缓冲池命中率不应小于 99%。相关状态参数包括Innodb_buffer_pool_reads从物理磁盘读取页的次数。Innodb_buffer_pool_read_ahead预读的次数。Innodb_buffer_pool_read_ahead_evicted预读但未被读取就被替换的页数量用于判断预读效率。Innodb_buffer_pool_read_requests从缓冲池中读取页的次数。Innodb_data_reads发起读取请求的次数每次读取可能涉及多个页。5. Binlog 相关参数sync_binlog控制 binlog 刷盘策略。binlog_expire_logs_seconds8.0 版本中 binlog 日志过期时间精确到秒替代了旧版的 expire_logs_days 参数。二、MySQL 8.0 新特性详解1. 新增降序索引MySQL 在语法上很早就支持降序索引但 5.7 实际创建的仍是升序索引。8.0 开始真正支持降序索引且只有 InnoDB 存储引擎支持。排序必须按照每个字段定义的排序或相反顺序才能充分利用索引否则会出现 filesort 文件排序。-- MySQL 8.0 创建降序索引 CREATE TABLE t1(c1 int, c2 int, INDEX idx_c1_c2(c1, c2 DESC)); -- 充分利用降序索引Extra 无 filesort EXPLAIN SELECT * FROM t1 ORDER BY c1, c2 DESC; -- 反向扫描索引 EXPLAIN SELECT * FROM t1 ORDER BY c1 DESC, c2; -- Extra: Backward index scan; Using index2. GROUP BY 不再隐式排序MySQL 8.0 中 GROUP BY 字段不再隐式排序如需要排序必须显式加上 ORDER BY 子句。-- 8.0 中 group by 不再默认排序 SELECT count(*), c2 FROM t1 GROUP BY c2; -- 需要排序时显式加 order by SELECT count(*), c2 FROM t1 GROUP BY c2 ORDER BY c2;3. 增加隐藏索引使用 invisible 关键字可将索引设置为隐藏索引。索引隐藏后数据库后台仍会维护但查询时优化器不会使用即使使用 force index 也不会生效。主键不能设置为 invisible。隐藏索引适合软删除场景例如不确定索引是否还有用时先隐藏确认无用后再删除避免大表操作的高成本。-- 创建隐藏索引 CREATE TABLE t2(c1 int, c2 int, INDEX idx_c1(c1), INDEX idx_c2(c2) INVISIBLE); -- 查看索引可见性 SHOW INDEX FROM t2; -- 会话级别开启优化器使用隐藏索引 SET SESSION optimizer_switchuse_invisible_indexeson; -- 恢复索引可见 ALTER TABLE t2 ALTER INDEX idx_c2 VISIBLE;4. 函数索引MySQL 8.0.13 开始支持在索引中使用函数表达式的值。函数索引基于虚拟列功能实现相当于新增一个列该列根据函数计算结果使用函数索引时用这个计算后的列作为索引。5. 新增 innodb_dedicated_server 自适应参数该参数能够让 InnoDB 根据服务器检测到的内存大小自动配置 innodb_buffer_pool_size、innodb_log_file_size 等参数尽可能占用系统资源提升性能解决非专业人员安装数据库后默认参数偏低的问题。前提是服务器专用于 MySQL若还有其他软件或多实例 MySQL 则不建议开启。6. 死锁检查控制MySQL 8.0 新增动态变量 innodb_deadlock_detect默认开启。死锁检测会耗费数据库性能高并发系统可关闭该功能提升性能但需确保系统极少发生死锁同时调小锁等待超时参数以防死锁等待过久。7. UNDO 文件不再使用系统表空间默认创建 2 个 UNDO 表空间不再使用系统表空间。8. 窗口函数Window Functions窗口函数也称分析函数与 SUM()、COUNT() 等分组聚合函数类似在聚合函数后加上 over() 即变成窗口函数。窗口函数即便分组也不会将多行查询结果合并为一行而是将结果放回多行中不需要使用 GROUP BY。-- 分组聚合函数每组只返回一条数据 SELECT name, SUM(balance) FROM account_channel GROUP BY name; -- 窗口函数保留原有表数据结构 SELECT name, channel, balance, SUM(balance) OVER(PARTITION BY name) AS sum_balance FROM account_channel; -- over() 中不加条件则默认使用整个表的数据做运算 SELECT name, channel, balance, SUM(balance) OVER() AS sum_balance FROM account_channel;专用窗口函数分类序号函数ROW_NUMBER()、RANK()、DENSE_RANK()分布函数PERCENT_RANK()、CUME_DIST()前后函数LAG()、LEAD()头尾函数FIRST_VALUE()、LAST_VALUE()其它函数NTH_VALUE()、NTILE()9. 默认字符集由 latin1 变为 utf8mb48.0 之前默认字符集为 latin1utf8 指向 utf8mb38.0 版本默认字符集为 utf8mb4utf8 默认指向 utf8mb4。10. MyISAM 系统表全部换成 InnoDB 表系统表mysql和数据字典表全部改为 InnoDB 存储引擎默认 MySQL 实例不包含 MyISAM 表除非手动创建。11. 元数据存储变动MySQL 8.0 删除了之前版本的元数据文件如表结构 .frm 文件全部集中放入 mysql.ibd 文件里。12. 自增变量持久化8.0 之前自增主键 AUTO_INCREMENT 的值如果大于 max(primary key)1重启后会重置为 max(primary key)1可能导致业务主键冲突。8.0 对 AUTO_INCREMENT 值进行持久化重启后该值不会改变。-- MySQL 5.7重启后自增 id 重置可能产生主键冲突 -- MySQL 8.0重启后自增 id 持久化不会重置13. DDL 原子化MySQL 8.0 开始支持原子 DDL 操作与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子 DDL 操作内容包括更新数据字典、存储引擎层操作、在 binlog 中记录 DDL 操作。支持数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE 等操作要么成功要么回滚。14. 参数修改持久化MySQL 8.0 支持在线修改全局参数并持久化通过加上 PERSIST 关键字可将修改的参数持久化到新的配置文件mysqld-auto.cnf中重启 MySQL 时从该文件获取最新配置。set global 设置的变量在重启后会失效。当 my.cnf 和 mysqld-auto.cnf 同时存在时后者具有更高优先级。-- 持久化修改参数 SET PERSIST innodb_lock_wait_timeout25;三、总结MySQL 8.0 在索引、排序、存储引擎、DDL 事务性、参数持久化等方面均有显著改进。降序索引、隐藏索引、函数索引、窗口函数等新特性为开发者提供了更强大的查询能力原子 DDL 和参数持久化提升了运维的可靠性。全局优化方面应重点关注连接数、缓冲池大小、排序缓冲区等参数的合理配置并结合实际业务场景进行调优。四、面试提问问题 1MySQL 8.0 的降序索引与 5.7 有何区别答MySQL 5.7 虽然在语法上支持降序索引但实际创建的仍是升序索引查询时可能出现 filesort 文件排序。MySQL 8.0 真正支持降序索引且只有 InnoDB 存储引擎支持。排序必须按照每个字段定义的排序或相反顺序才能充分利用索引否则会出现 filesort。问题 2什么是隐藏索引有什么应用场景答隐藏索引通过 invisible 关键字设置索引隐藏后数据库后台仍会维护但查询时优化器不会使用即使 force index 也不会生效。主键不能设置为 invisible。应用场景主要是软删除例如不确定索引是否还有用时先隐藏确认无用后再删除避免大表操作的高成本。问题 3MySQL 8.0 的窗口函数与 GROUP BY 聚合函数有何区别答窗口函数在聚合函数后加上 over() 即变成窗口函数即便分组也不会将多行查询结果合并为一行而是将结果放回多行中不需要使用 GROUP BY。窗口函数可以保留原有表数据的结构而分组聚合函数每组只返回一条数据。问题 4MySQL 8.0 的原子 DDL 是什么答MySQL 8.0 开始支持原子 DDL 操作与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子 DDL 操作内容包括更新数据字典、存储引擎层操作、在 binlog 中记录 DDL 操作要么成功要么回滚。例如 drop table t1,t2 时若 t2 不存在5.7 会删除 t1 并报错8.0 则会回滚t1 依然存在。问题 5如何判断 InnoDB 缓冲池是否达到瓶颈答通过查看服务器状态比较物理磁盘读取和内存读取的比例来判断缓冲池命中率通常 InnoDB 缓冲池命中率不应小于 99%。相关参数包括 Innodb_buffer_pool_reads从物理磁盘读取页的次数、Innodb_buffer_pool_read_requests从缓冲池中读取页的次数等。问题 6sort_buffer_size 是否越大越好答不是。sort_buffer_size 是 connection 级参数在每个连接第一次需要使用该 buffer 时一次性分配设置的内存。过大的设置加高并发可能耗尽系统内存资源例如 500 个连接将消耗 500 * sort_buffer_size(4M) 2G 内存。问题 7MySQL 8.0 参数持久化如何实现答通过 SET PERSIST 关键字在线修改全局参数并持久化到 mysqld-auto.cnf 文件中重启 MySQL 时从该文件获取最新配置。set global 设置的变量在重启后会失效。当 my.cnf 和 mysqld-auto.cnf 同时存在时后者具有更高优先级。
