用案例分析MySQL 5.7分区表性能下降的根本原因

最新推荐文章于 2024-08-16 18:00:00 发布

it阿布

最新推荐文章于 2024-08-16 18:00:00 发布

阅读量294

点赞数 2

分类专栏：进阶PHP月薪30k 文章标签： mysql php

本文链接：https://blog.csdn.net/weixin_49163826/article/details/108717529

版权

本文分析了MySQL 5.7.18版本中分区表性能下降的问题，通过测试发现，更新主键时会对分区表相同数量的行上锁，导致锁冲突和性能下降。这个问题在5.6.21版本中不存在，可能是5.7的一个regression。

摘要由CSDN通过智能技术生成

前言：希望通过本文，使MySQL5.7.18的使用者知晓分区表使用中存在的陷阱，避免在该版本上继续踩坑。同时通过对源码的分享，升级MySQL5.7.18时分区表性能下降的根本原因，向MySQL源码爱好者展示分区表实现中锁的运用。

问题描述

MySQL 5.7版本中，性能相关的改进非常多。包括临时表相关的性能改进，连接建立速度的优化和复制分发相关的性能改进等等。基本上不需要做配置修改，只需要升级到5.7版本，就能带来不少性能的提升。

我们在测试环境，把数据库升级到5.7.18版本，验证MySQL 5.7.18版本是否符合我们的预期。观察运行了一段时间，有开发反馈，数据库的性能比之前的5.6.21版本有下降。主要的表现特征是遇到比较多的锁超时情况。开发另外反馈，性能下降相关的表都是分区表。更新走的都是主键。这个反馈引起了我们重视。我们做了如下尝试：

数据库的版本为5.7.18, 保留分区表，性能会下降。
数据库版本为5.7.18，把表调整为非分区表，性能正常。
把数据库的版本回退到5.6.21版本，保留分区表，性能也是正常

通过上述测试，我们大致判定，这个性能下降和MySQL5.7版本升级有关。

问题重现

测试环境的数据库表结构比较多，并且调用关系也比较复杂。为了进一步分析并定位问题，我们抽丝剥茧，构建了如下一个简单的重现过程

// 创建一个测试分区表t2: 
 CREATE TABLE `t2`( 
 
  `id` INT(11) NOT NULL, 
 
  `dt` DATETIME NOT NULL, 
 
  `data` VARCHAR(10) DEFAULT NULL, 
 
  PRIMARYKEY (`id`,`dt`), 
 
  KEY`idx_dt`(`dt`) 
 
) ENGINE=INNODB