最近公司需要对一些旧表统一更改主键类型。之前表的主键都是int类型，这次要改成bigint。然后我整理的时候，发现有一张表，里面有5亿条数据。在排查问题之前优化了几个慢sql，但是这个表的查询不在慢sql列表中。有什么问题。后来找了很多3000w到8000w不等的表。里面的字段数比较正常，10个左右，用的还不错。提前避免优化，处理问题才是王道，因为你提前做的一些准备很可能是无用的，浪费情绪和精力。先说这张有5亿数据的表。当晚执行修改类型语句时，因为执行时间超过了自建sql平台的时间阈值（平台发现某条sql执行超过2小时会主动关闭连接）。而且这种修改类型的modify语句是不能单独执行的，只能一次性执行，所以比较尴尬。当时还有一个方案就是绕过sql平台，让dba直接在外面执行。由于来不及了，只好等到第二天。第二天分析表，发现之前的数据没有用，可以归档，就是把21年的数据移到另外一张表，只剩下22年的数据。该表是时间索引的。我查了下21年的数据，大概是3亿。删除这些数据后，估计修改时间可以减少一半以上，而且表本身也需要归档，但是今年忘记做了（也就是说我从来没有遇到过查询慢的问题).所以解决办法是先对数据进行归档，也就是把21年的数据插入到新表中，然后在这个表中删除21年的数据，然后修改改变类型。insertinto和delete语句很简单，但是由于数据量太大，为了避免长事务的问题，dba要求我们把sql语句拆分出来让他执行。当时我就在想：这个拆分也需要开发拆解吗？DBA只是一个无情的执行机器吗？好吧，我就拆了，然后我把insert拆成100块，delete拆成100块交给DBA。当天晚上，DBA又执行了一波，但是当时好几次删除都失败了。他问我，这个表还有要求改吗？我说不可能，因为这个表相当于一个流表，21年的数据都删掉了。21年的数据目前无法更改，但确实是错误。我检查了错误，锁超时了。当时就想为什么会出现锁等待超时，现在一个业务不可能操作21年的数据。后来发现DBA在并行执行多个delete语句。于是，我跟群里的DBA说，应该是因为你并行执行了多个delete，他们之前有竞争关系，一个delete删除了很多数据，所以等待锁超时了。DBA来了一句：有id范围限制的deletes之间应该没有冲突。我简化了，几个delete语句如下：deletefromyeswheredate<'2022-06-25'and(id>=1andid<10)deletefromyeswheredate<'2022-06-25'and(id>=10andid<20)deletefromyeswheredate<'2022-06-25'and(id>=20andid<30)好了，背景讲解完毕，看到这里可以好好想想，并行执行上面的delete语句，它们之间会不会存在竞争锁呢？开始执行当前事务隔离级别为：可重复读隔离级别，mysql5.7+版本。答案是他们会发生冲突，争抢锁。一切以事实为依据，而对于这个事实，首先要有一个表。CREATETABLE`yes`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`name`varchar(45)DEFAULTNULL,`address`varchar(45)DEFAULTNULL,`date`dateDEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_date`(`date`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4小数据量demo先来看小数据量，可以看到数据库只有5条数据。此时我在客户端A上执行删除语句：deletefromyeswheredate<'2022-06-25'and(id>1andid<3)而没有提交事务。这时在客户端B上又执行了一条delete语句：deletefromyeswheredate<'2022-06-25'and(id>=3andid<5)可以看到此时发生了阻塞：是不是a有点奇怪？好像他们之间没有冲突？让我们执行select*frominformation_schema.innodb_locks;查看锁的详细信息：可以看到lock_mode是X锁，表示是排它锁，lock_type是RECORD，表示是行锁。lock_index为PRIMARY，表示锁为主键索引，lock_data为3，即主键ID为3的记录被锁定。至此我们知道确实存在竞争，竞争的地方在主键索引上，使用行锁，冲突的行就是ID为3的行。我们执行select*fromsys.innodb_lock_waits\G;查看对应的lock_id是不是我们执行的语句：可以看到10586确实被阻塞了，对应客户端B执行的语句，本次实验已经和当晚的阻塞情况一致。当然，结果就是结果。弄清楚为什么会这样很重要。让我们继续。下面来解释一下这个delete语句：发现delete语句使用了主键索引，即使date列有索引，也可以覆盖条件字段（id），同样使用了主键索引。但是讲道理，连用的主键索引都不对。有个id<3的条件，为什么id=3这一行会被锁？因为在可重复读隔离级别下，rangelock(id>1)规则实际上是向后遍历，直到扫描到不满足条件即id=3的行，然后停止，所以这条语句最终扫描到id的行刚好是id=3的行，所以被锁了。这时再执行一条delete语句时，需要id=3的记录的行锁（这个有没有疑问？），所以存在竞争，而且因为第一条语句delete数据量大，执行时间比较长，从而触发第二次锁超时。嗯，通过小数据分析得到的结果，和当晚执行的结果是一致的。其实到这里就可以结束了，但是为了严谨起见，还是继续大数据量的实验吧。大数据量演示为了更真实，首先我多加了一些字段：然后随机插入了1000w条数据：这时我在客户端A上执行删除语句：deletefromyeswheredate<'2022-06-25'and(id>1andid<100000)不提交事务。这时在客户端B上又执行了一条delete语句：deletefromyeswheredate<'2022-06-25'and(id>=100000andid<200000)也被屏蔽了，同样使用主键。边界值的lineid:100000在竞争：嗯，大数据量也测试过，得出同样的结论，可以解释为什么同一天并行执行多条delete语句会出现锁超时.总结在可重复读隔离级别下，通过范围搜索条件执行带有索引键和主键的delete语句。不管数据量是大是小，mysql都会使用主键索引来扫描记录（我估计数据无论如何都会被删除，也就是原来要删除二级索引的数据和聚簇索引，那么干脆用主键索引扫描？）而范围扫描的加锁数据会扫描到第一条不满足条件的记录，也就是第一条不满足条件的记录也会被locked，所以并行删除时，由于边界值存在竞争关系，由于delete语句执行时间长，导致锁等待超时错误。好了，终于分析完了。对了，今天随机插入数据用了好久。。。写了一个存储过程插入，但是执行了半天，插入不了，一直在运行。很久。后来很奇怪，所以我创建了一个新表并在几分钟内插入它。于是又回头看之前的表，也看不出为什么，正想把这张表删掉，结果发现删不掉，最后发现在小程序中执行了一条语句锁定整个表的窗口。......所以什么都不能插入。前后3个多小时，最后执行结果2分钟……难受。话说回来，这个DBA还真是懒惰。他觉得自己做任何事情都不需要动脑筋。在sql平台上，我们提交sql，由我们技术总监审核。审核通过后，他可以点击界面执行。我们还要把上面说的非业务相关的SQL拆解，整理整齐，让他去执行。通常，我们进行监控和报警。当数据库CPU发生告警时，将告警发给我们。让我们看看是什么原因造成的。总之，不要太相信DBA，一切还是自己说了算，自己也可以，包括DBA告诉你的一些结论，或者你自己的实验是最靠谱的。
