MySQL 8.0，是2018年发布的版本，带来了很多新特性。比如窗口函数、CTE、不可见索引、降序索引等，性能也比5.7有了很大提升，越来越多的公司开始从5.7升级到8.0。或者直接用8.0部署新业务。

但是，从5.7升级到8.0。或者直接用8.0。并不是一帆风顺的，有很多坑需要踩。有些坑是8.0特有的，有些坑是优化过程中常见的。但是在8.0上表现得更明显。我在项目中。用MySQL 8.0，已经有两年多了。从最开始的调研、试用，到后来的大规模生产环境使用。踩了很多坑，有些坑甚至让我熬夜到凌晨，印象深刻。

之前，我写过一些MySQL优化的文章。分享了一些通用的优化经验。今天，这篇文章。专门分享MySQL 8.0优化过程中遇到的那些坑，以及解决方法。包括升级踩坑、性能优化踩坑、SQL优化踩坑、运维踩坑等方面，希望能给大家一些参考。避免踩同样的坑，少熬夜。

先说明一下，这些坑是我们在实际项目中遇到的。基于MySQL 8.0.x版本，不同的版本可能会有一些差异。大家要注意版本的差异。而且，这些坑的解决方法。是基于我们的场景总结的，不一定适合所有的场景，大家要根据自己的实际情况灵活参考。

一、升级踩坑

第一个大坑，就是从5.7升级到8.0的过程中遇到的坑。很多人觉得升级就是改个版本号，重启一下就行，其实不是，8.0和5.7有很多不兼容的地方，升级之前一定要做好充分的准备和测试。

1. 字符集默认值变化

MySQL 8.0的默认字符集，从5.7的latin1，改成了utf8mb4。这个变化看起来是好事，因为utf8mb4支持完整的Unicode，包括emoji表情。但是升级的时候，如果不注意。可能会导致乱码问题。

我们当时升级的时候，有一个老库。5.7的时候默认字符集是latin1。但是实际存的数据是utf8的，这是一个历史遗留问题。很多老系统都有这个问题。升级到8.0之后，默认字符集变成了utf8mb4。但是表的字符集还是latin1。连接字符集也没配对，结果查询出来的数据全是乱码。当时吓了一跳，以为数据丢了。后来发现是字符集的问题，调整了连接字符集和表字符集，才解决。

解决方法：升级之前，一定要检查所有库、表、字段的字符集，统一改成utf8mb4，不要用latin1或者utf8（MySQL的utf8是假的utf8，最多3字节，不支持emoji，要用utf8mb4）。同时，应用的连接字符集也要设置成utf8mb4，在JDBC连接串里加上characterEncoding=utf8，或者在my.cnf里设置default-character-set=utf8mb4。

2. 排序规则默认值变化

MySQL 8.0的默认排序规则，从5.7的utf8generalci，改成了utf8mb40900ai_ci。这个排序规则是8.0新的，基于Unicode 9.0，比旧的排序规则更准确，但是和旧的排序规则不兼容。

我们当时升级的时候，有一个表，5.7的时候用的是utf8generalci排序规则，升级到8.0之后，新创建的表默认是utf8mb40900ai_ci，结果两个表join的时候，因为排序规则不一致，报错"Illegal mix of collations"，当时查了半天才发现是排序规则的问题。

解决方法：升级之后，统一所有表的排序规则，建议都用utf8mb40900ai_ci，这是8.0的默认排序规则，也是推荐的。如果有老表是旧的排序规则，用ALTER TABLE命令改成新的排序规则。同时，注意join的两个表的字段排序规则要一致，否则会报错。

3. 密码认证插件变化

MySQL 8.0的默认密码认证插件，从5.7的mysqlnativepassword，改成了cachingsha2password。这个新的认证插件更安全，但是和旧的客户端不兼容，很多老版本的客户端连不上8.0的数据库，报认证错误。

我们当时升级的时候，有一个老的Java应用。用的是很老版本的MySQL Connector/J，连8.0的数据库。一直报认证错误，查了半天。发现是密码认证插件的问题，老版本的驱动不支持cachingsha2password。

解决方法：有两种方式，一种是升级客户端驱动到支持cachingsha2password的版本，MySQL Connector/J 8.0以上都支持，这是推荐的方式。另一种是把用户的认证插件改回mysqlnativepassword，用ALTER USER命令修改，这种方式适合暂时没法升级驱动的老应用，但是不推荐长期用，因为新的认证插件更安全。

4. 系统表变化

MySQL 8.0的系统表，从5.7的MyISAM引擎，改成了InnoDB引擎。而且系统表的结构也有很大变化，很多5.7的系统表在8.0里没有了。或者改了名字。

我们当时升级的时候，有一个运维脚本。直接查询mysql.user表，获取用户信息。升级到8.0之后，这个脚本报错。因为mysql.user表的结构变了，很多字段没有了。后来改成用mysql.user系统视图。或者用SHOW命令，才解决。

解决方法：升级之前，检查所有直接访问系统表的脚本和应用，改成用系统视图或者官方命令。不要直接访问系统表，因为系统表的结构可能会变。同时，升级之前一定要在测试环境充分测试所有的脚本和应用，确保兼容。

5. 升级方式选择

升级方式也很重要，MySQL 8.0支持两种升级方式，原地升级（in-place upgrade）和逻辑升级（logical upgrade）。原地升级就是直接用8.0的二进制启动5.7的数据目录，自动升级系统表，这种方式快，但是风险大，因为数据文件直接转换，如果出问题可能回滚不了。逻辑升级就是用mysqldump或者xtrabackup导出数据，然后导入到8.0的新实例，这种方式慢，但是安全，因为原数据不动，出问题可以随时回滚。

我们当时升级的时候，最开始想用原地升级，因为快。但是测试的时候发现有一些兼容性问题。而且担心出问题回滚不了，最后改成了逻辑升级，用mysqldump导出数据，导入到新的8.0实例。然后切换应用连接，虽然慢了一点。但是很安全，出问题可以随时切回老实例。

解决方法：生产环境升级。建议用逻辑升级，更安全，虽然慢一点。但是可控。如果数据量特别大，逻辑升级时间太长。可以考虑用原地升级。但是一定要先做好备份，并且在测试环境充分测试，确保没问题再在生产环境操作。不管用哪种方式，升级之前一定要做全量备份，这是底线。

二、性能优化踩坑

升级完之后，就是性能优化的坑了。MySQL 8.0的性能整体比5.7好，但是也有一些特有的性能问题，需要注意。

1. 排序性能问题

MySQL 8.0的排序算法，和5.7不一样。8.0用了新的排序算法，在某些场景下。排序性能反而比5.7差。我们当时有一个查询，在5.7上跑0.5秒。升级到8.0之后，跑5秒。慢了10倍，当时很纳闷。查了半天，发现是排序的问题。

那个查询有一个ORDER BY，排序的字段没有索引，需要文件排序（filesort），8.0的filesort在某些情况下，内存分配和排序效率不如5.7，尤其是排序的数据量比较大的时候。后来我们给排序字段加了索引，避免了filesort，查询性能就上来了。

解决方法：MySQL 8.0的排序性能优化，最根本的还是让排序用上索引，避免filesort。如果确实没法用索引。需要filesort。可以调整sortbuffersize参数，增大排序缓冲区，减少磁盘临时文件的使用。同时。可以检查maxlengthforsortdata参数，这个参数在8.0里默认值变了。可能会影响排序方式，根据实际情况调整。另外。可以用EXPLAIN分析查询，看看是不是filesort，有没有用上索引。

2. 哈希连接性能问题

MySQL 8.0引入了哈希连接（Hash Join），这是一个很大的改进，之前的版本只有嵌套循环连接（Nested Loop Join），哈希连接在大表join的时候性能更好。但是哈希连接也不是万能的，在某些场景下，哈希连接的性能反而不如嵌套循环连接。

我们当时有一个查询，是小表join大表。在5.7上用嵌套循环连接，跑1秒。升级到8.0之后，优化器自动选择了哈希连接。结果跑10秒，慢了10倍。后来分析发现。小表join大表，嵌套循环连接其实更合适。因为小表作为驱动表，大表用索引查找。很快，而哈希连接需要把小表加载到内存里建哈希表。然后扫描大表，反而慢。

解决方法：哈希连接适合大表join大表，或者没有合适索引的场景，小表join大表，并且大表有索引的话，嵌套循环连接可能更快。如果优化器选错了连接方式，可以用Hint强制使用嵌套循环连接，比如/+ NOHASHJOIN(t1, t2) /，或者调整优化器的参数，比如optimizerswitch里的hashjoin=off，临时关闭哈希连接。当然，最好的方式还是给join字段加合适的索引，让优化器能做出正确的选择。

3. 窗口函数性能问题

窗口函数是MySQL 8.0的新特性，非常好用，能解决很多之前需要用复杂子查询或者变量才能解决的问题，比如分组取Top N、累计求和、排名等。但是窗口函数的性能，在某些场景下，也有坑。

我们当时有一个查询，用了窗口函数ROWNUMBER() OVER (PARTITION BY categoryid ORDER BY created_at DESC)，用来分组取最新的记录，数据量大概100万，结果这个查询跑了10秒，很慢。后来分析发现，窗口函数需要对数据进行排序和分区，如果数据量大，并且没有合适的索引，性能会很差。后来我们给PARTITION BY和ORDER BY的字段加了联合索引，查询性能提升到1秒以内。

解决方法：使用窗口函数的时候，一定要给PARTITION BY和ORDER BY的字段加合适的联合索引，这样窗口函数可以利用索引避免排序，性能会好很多。同时，窗口函数不要用在太大的数据集上，如果数据量特别大。可以考虑先过滤数据，再用窗口函数。另外。可以用EXPLAIN ANALYZE分析窗口函数的执行计划，看看有没有排序，有没有用上索引，针对性优化。

4. CTE性能问题

CTE（公共表表达式）也是MySQL 8.0的新特性，能让复杂的SQL更清晰，更易读，把复杂的查询拆成多个步骤。但是CTE的性能，也有坑。尤其是递归CTE。

我们当时有一个查询，用了非递归CTE。把一个复杂的子查询定义成CTE。然后多次引用，结果发现性能比直接用子查询还慢。后来分析发现。MySQL 8.0的CTE，默认是物化的。也就是把CTE的结果存到临时表里。然后多次查询临时表，如果CTE的数据量大。物化的开销很大，反而慢。而5.7的子查询。优化器可能会合并到外层查询，不需要物化，反而快。

解决方法：MySQL 8.0的CTE，有两种执行方式，物化和合并，优化器会自动选择。但是有时候会选错。如果CTE只引用一次，并且可以合并到外层查询。建议用子查询。或者用Hint强制合并。比如/+ MERGE(cte_name) /。如果CTE多次引用，并且数据量小，物化可能更合适。另外，递归CTE要特别注意性能，递归的深度不要太大，数据量不要太多。否则会很慢。甚至把内存撑爆。

三、SQL优化踩坑

SQL优化是MySQL优化的重点，也是最容易踩坑的地方。MySQL 8.0的优化器比5.7更智能，但是也有一些新的坑。

1. 不可见索引的坑

不可见索引（Invisible Index）是MySQL 8.0的新特性，可以把索引设置成不可见，优化器就不会用这个索引，但是索引还是会正常维护，适合用来测试删除索引的影响，或者临时禁用索引。这个特性很好用，但是也有坑。

我们当时有一个索引，因为性能测试。设置成了不可见，测试完之后忘了改回来。结果线上查询一直用不了这个索引，性能很差。查了很久才发现是索引不可见了，当时很无语，这么低级的错误。

解决方法：不可见索引是一把双刃剑，好用。但是容易忘。使用不可见索引的时候，一定要记录下来，测试完之后及时改回可见。同时。可以定期检查数据库里有没有不可见的索引，用SELECT * FROM informationschema.statistics WHERE isvisible = 'NO'，避免遗忘。另外，删除索引之前。可以先把索引设置成不可见，观察一段时间，确认没有影响再真正删除，这是一个很好的实践。但是一定要记得最后删除或者改回来。

2. 降序索引的坑

降序索引（Descending Index）也是MySQL 8.0的新特性，之前的版本，索引都是升序的，虽然可以用ORDER BY DESC反向扫描，但是在混合排序的场景，比如ORDER BY a ASC, b DESC，就没法用索引，只能filesort。8.0的降序索引，支持真正的降序索引，能解决混合排序的问题。

但是降序索引也有坑，我们当时给一个表加了降序索引。ORDER BY a ASC, b DESC，性能确实提升了。但是后来发现。这个索引的维护成本比普通索引高，插入和更新的性能下降了一些。因为降序索引的结构和普通索引不一样，维护更复杂。而且。降序索引只能用于对应的降序排序，如果查询是升序排序。用不了降序索引，反而需要另外加升序索引。索引数量多了，维护成本更高。

解决方法：降序索引适合混合排序的场景。比如ORDER BY a ASC, b DESC，这种场景普通索引解决不了。必须用降序索引。如果只是单纯的ORDER BY DESC，普通索引反向扫描就够了，不需要降序索引。使用降序索引的时候，要评估写入性能的影响，如果写入频繁，要谨慎使用。同时。不要重复建索引，升序和降序索引不要同时建，除非确实需要。

3. 函数索引的坑

函数索引（Functional Index）也是MySQL 8.0的新特性，可以对表达式或者函数的结果建索引，比如INDEX((UPPER(name)))，这样查询WHERE UPPER(name) = 'ABC'的时候就能用上索引，解决了之前在字段上用函数导致索引失效的问题。这个特性很实用，但是也有坑。

我们当时给一个表加了函数索引，INDEX((DATE(createdat)))，用来按日期查询，性能确实提升了。但是后来发现，这个函数索引的维护成本很高，因为每次插入和更新，都需要计算函数的值。然后更新索引，写入性能下降明显。而且，函数索引只能用于完全匹配的表达式，如果查询里的表达式和索引里的不完全一样。比如用了DATEFORMAT(created_at, '%Y-%m-%d')，就用不了这个索引。

解决方法：函数索引适合在字段上用函数，并且查询频繁的场景，能解决索引失效的问题。但是要注意，函数索引的维护成本比普通索引高，写入频繁的表要谨慎使用。同时，查询里的表达式必须和索引里的完全一致，才能用上函数索引，差一点都不行。所以使用的时候要注意表达式的一致性。另外，很多时候，用生成列（Generated Column）加普通索引，也能达到同样的效果。而且更灵活。可以根据实际情况选择。

4. 优化器选错索引

MySQL 8.0的优化器比5.7更智能。但是还是会有选错索引的时候。尤其是数据分布不均匀。或者统计信息不准确的时候。我们当时有一个查询，有两个索引可以选，一个是时间索引，一个是状态索引，优化器选了状态索引，结果性能很差，因为状态的区分度很低，大部分数据都是某个状态，扫描了很多行。后来用Hint强制用时间索引，性能提升了很多。

优化器选错索引的原因，通常是统计信息不准确，或者数据分布不均匀，优化器的代价计算不准。MySQL 8.0的统计信息比5.7更准确，但是还是需要定期更新。

解决方法：优化器选错索引，首先可以用ANALYZE TABLE更新统计信息，让优化器有更准确的数据分布信息，很多时候更新完统计信息，优化器就能选对索引了。如果还是选错。可以用Hint强制使用某个索引。比如FORCE INDEX(indexname)。或者USE INDEX(indexname)。但是Hint是硬编码，不灵活，如果数据分布变了。可能Hint反而不好。更好的方式是调整索引。比如建更合适的联合索引，让优化器没得选，只能选对的。另外。可以用EXPLAIN和EXPLAIN ANALYZE分析查询的执行计划，看看优化器选了哪个索引，扫描了多少行，针对性优化。

四、运维踩坑

最后是运维的坑，MySQL 8.0的运维和5.7有一些不一样，也有一些新的坑。

1. 备份恢复的坑

备份是数据库运维的重中之重，MySQL 8.0的备份和5.7基本一样，用mysqldump或者xtrabackup，但是也有一些需要注意的地方。我们当时用xtrabackup备份8.0的数据库，恢复的时候发现报错，因为xtrabackup的版本太低，不支持8.0的一些新特性，比如系统表是InnoDB引擎。后来升级了xtrabackup到最新版本，才恢复成功。

还有一次，用mysqldump备份，恢复的时候，因为dump文件里有一些8.0特有的语法。比如CREATE TABLE里的加密选项，老版本的mysql客户端执行不了，报错。后来用对应版本的mysql客户端恢复，才解决。

解决方法：备份和恢复的工具版本，一定要和数据库版本匹配。尤其是8.0，要用支持8.0的最新版本的xtrabackup和mysqldump。同时，备份完之后，一定要定期做恢复测试，确保备份是可用的，很多人只备份不恢复，真出问题的时候发现备份不可用，那就惨了。另外，8.0的备份要注意备份系统表和用户权限，mysqldump默认会备份。但是xtrabackup要注意。

2. 主从复制的坑

主从复制是MySQL高可用的基础，8.0的主从复制比5.7有了很大改进。比如GTID更完善。支持并行复制。但是也有坑。我们当时搭建8.0的主从复制，用了GTID。结果从库一直报错，说GTID不一致。查了半天，发现是主库上有一些事务没有写入binlog。因为设置了sqllogbin=0，导致GTID不连续，从库没法同步。

还有一次，并行复制的配置有问题。slaveparallelworkers设置得太大，导致从库延迟很大。因为并行复制的协调线程开销大，反而不如单线程快。后来调整成合适的并行度，延迟就降下来了。

解决方法：8.0的主从复制。建议用GTID，更方便，更可靠。但是要注意。不要在主库上随便设置sqllogbin=0，会导致GTID不连续，从库同步失败。如果确实需要跳过某些事务，要用正确的方式。比如设置gtidnext。并行复制要合理配置，slaveparallel_workers不要设置太大。一般4-8个就够了，根据CPU核数和事务量调整。不是越大越好。同时，要监控主从延迟，及时发现问题。

3. 内存配置的坑

MySQL 8.0的内存配置和5.7基本一样，最重要的是innodbbufferpool_size。但是8.0有一些新的内存相关的参数，也有坑。我们当时升级到8.0之后，发现数据库的内存占用比5.7高很多。甚至出现了OOM，后来查了半天，发现是8.0的一些新特性。比如哈希连接、窗口函数，会用更多的内存。而且默认的内存限制比较宽松，导致内存占用高。

还有一次，innodbbufferpool_size设置得太大。占了系统大部分内存，结果操作系统的内存不够。开始用swap，数据库性能急剧下降。后来调整了buffer pool的大小，给操作系统留了足够的内存，才解决。

解决方法：MySQL 8.0的内存配置，innodbbufferpoolsize一般设置成物理内存的50%-70%，不要太大，给操作系统和其他进程留足够的内存，避免用swap。同时，要注意8.0新特性的内存占用，比如哈希连接的内存，由joinbuffersize控制，窗口函数的内存，由sortbuffersize等控制，这些参数不要设置太大，避免内存溢出。可以用performanceschema监控内存使用，看看哪些线程或者功能占用内存多，针对性调整。另外，建议开启cgroup或者其他内存限制，避免数据库OOM影响整个系统。

五、写在最后

以上，就是我在MySQL 8.0优化过程中遇到的那些坑。以及解决方法，包括升级踩坑、性能优化踩坑、SQL优化踩坑、运维踩坑等方面。都是我们在实际项目中踩过的，有些坑确实让我熬夜到凌晨，印象深刻。

MySQL 8.0是一个很优秀的版本，新特性很多。性能也比5.7有了很大提升，是未来的主流。现在5.7已经快停止维护了，升级到8.0是大势所趋。但是升级和使用的过程中。确实有很多坑需要注意。不能掉以轻心，一定要做好充分的测试和准备。

其实，不管是MySQL 8.0。还是其他技术，踩坑都是不可避免的。重要的是踩坑之后要总结经验，避免下次再踩同样的坑。同时把经验分享出来。帮助别人少踩坑。这也是我写这篇文章的目的，希望能给大家一些参考。避免踩同样的坑，少熬夜，多陪家人。

最后。建议大家在使用MySQL 8.0的时候，一定要仔细阅读官方文档。了解新特性和变化，做好测试。遇到问题多查文档，多分析。不要盲目操作。同时。要做好备份，这是底线。不管什么时候，备份都不能少。

如果你也在使用MySQL 8.0。或者打算升级，有什么问题或者经验。欢迎在评论区留言交流，一起学习，一起进步。

祝大家的MySQL都能跑得又快又稳，少踩坑，不熬夜。