mysql上的一次删除

场景是这样的：生产上要清理mysql（版本5.6）几张表，每张表大概有4千万数据量。清理之前需要备份，只是为了以防万一。备份方案想了下面几种：

1、create table as select * from
2、create table like ; insert into table select * from
3、mysqldump --single-transaction --master-data=2

--single-transaction选项和lock-all-tables选项是二选一的，lock-all-tables一开始就会flush tables with read lock，加全局读锁，直到dump完毕。single-transaction则是一开始加锁并使用一致性快照开始事务，然后马上unlock table，再执行导出，不影响其他操作，但导出的是dump开始时间点的数据。

原本我想的方案是1和2，后来发现前面两种方式都是会锁表的。第一个方案就是直接锁表，直到整个复制结束，并且总的来说，1总是不如2的，具体可参见https://www.percona.com/blog/2018/01/10/why-avoid-create-table-as-select-statement/。
第二个方案是在insert into的时候，有可能会阻塞dml操作。这里的有可能是指：
① 直接插入，不加入排序字段（即默认的主键排序）。这种情况是逐步锁定已经扫描过的记录，即如果你进行dml操作的时候还没有加锁，那么是可以成功的；
② 按照非主键排序插入，即select的时候order by 非主键，这样会直接全表加锁

生产上的表只会做插入操作，并且插入的主键都是新的，但是新插入的记录主键不是在原来的基础上递增的。为了保险起见最后的方案用了mysqldump导出，原本一开始的导出也很快，大概15分钟导出一张4千万行的表，大小跟ibd文件是差不多的，但是到后面突然无法导出了，一直卡在flush local table，导致其他操作无法获取到连接而失败。研究后怀疑是因为业务量较大，导致mysqldump无法获取到表锁，一直无法flush local table。所以在业务高峰期不要对数据库进行flush table，alter table，rename table，repair table，analyze table或者optimize table等操作，备份、ddl语句等操作也尽量避开业务高峰期。

20190430更新：

只使用–master-data=2参数为了获取show master status，会执行FLUSH TABLES WITH READ LOCK的全局锁，在业务低峰期，mysql获取全局锁会很快,但在业务高峰期又有很多库表的情况下是不建议执行全局锁的，因此mysqldump备份尽量放在业务低峰期做，或者备份时直接去掉–master-data=2参数。如果需要binlog信息则使用 master_data，这样其实我们是不需要加上–master-data=2这个参数的，如果只是简单的mysqldump导出备份的话。

然后就是数据的清理，单表要删除的数据有3600万（总数据量也只有4000万），网上找了一下，比较赞同的方案是是新建一张表，然后把需要保留的数据插到新表，再改一下表名，实现交换，旧表可以直接truncate或者drop掉，但业务不方便停，所以想着还是直接做删除，慢点就慢点。也定了两个方案，一是给删除条件做索引。理论上按主键删数据是最快的，但是无法将主键作为删除条件，按二级索引删数据的话，优化器如果认为修改的数据量大于20%，就会全表扫描，不会走索引，于是这个方案否定了。第二个是delete limit，减少一次性的事务，这个做了一下测试，确实很快，于是写了个定时任务，1秒删除5000行，4000万数据，也就8000秒，两个多小时就可以搞定了！但两个小时之后一看，怎么才执行到1000多秒？show processlist看了一下，发现事务要用几十秒甚至1分钟！打开慢查询日志看了一下，发现后面删除的时候扫描的行数越来越多了。想了一下，毕竟不是按照主键删除，当可以删除的数据越来越少，按照主键排序下来删除数据的分布是越来越稀疏的，要找齐5000条删除的数据越来越难，所以要扫描的行数就越来越多了，删除也就越来越慢。这个时候考虑增加limit的数量，或者直接一次性删除。如果一次性删除的话需要注意事务也不能太大：

“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog能够使用的最大cache内存大小。当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan’max_binlog_cache_size’bytesofstorage”的错误。

我这边的统计数据是，用limit 5000删除到剩2500万时已经需要30秒左右了，然后我改成了100000，删除一次大概用1分钟10秒左右。这个效率其实提升了很多了，所以其实一开始“limit 5000”这个条件还是有待商榷的，我想了一下这个跟要删除的数据的密度有点关系，跟删除数据在主键顺序下的分布也有关系。这个要详细说的话就有点复杂了，有空了再来分析分析。