浅谈为什么MySQL不建议delete删除数据

前言

我负责的有几个系统随着业务量的增长，存储在MySQL中的数据日益剧增，我当时就想现在的业务方不讲武德，搞偷袭，趁我没反应过来把很多表，很快，很快啊都打到了亿级别，我大意了，没有闪，这就导致跟其Join的表的SQL变得很慢，对的应用接口的response time也变长了，影响了用户体验。

事后我找到业务方，我批评了他们跟他们说要讲武德，连忙跟我道歉，这个事情才就此作罢，走的时候我对他们说下次不要这样了，耗子尾汁，好好反思。

骂归骂，事情还是得解决，时候我分析原因发现，发现有些表的数据量增长很快，对应SQL扫描了很多无效数据，导致SQL慢了下来，通过确认之后，这些大表都是一些流水、记录、日志类型数据，只需要保留1到3个月，此时需要对表做数据清理实现瘦身，一般都会想到用insert + delete的方式去清理。

这篇文章我会从InnoDB存储空间分布，delete对性能的影响，以及优化建议方面解释为什么不建议delete删除数据。

InnoDB存储架构

从这张图可以看到，InnoDB存储结构主要包括两部分：逻辑存储结构和物理存储结构。

逻辑上是由表空间tablespace —> 段segment或者inode —> 区Extent ——>数据页Page构成，Innodb逻辑管理单位是segment，空间分配的最小单位是extent，每个segment都会从表空间FREE_PAGE中分配32个page，当这32个page不够用时，会按照以下原则进行扩展：如果当前小于1个extent，则扩展到1个extent；当表空间小于32MB时，每次扩展一个extent；表空间大于32MB，每次扩展4个extent。

物理上主要由系统用户数据文件，日志文件组成，数据文件主要存储MySQL字典数据和用户数据，日志文件记录的是data page的变更记录，用于MySQL Crash时的恢复。

Innodb表空间

InnoDB存储包括三类表空间：系统表空间，用户表空间，Undo表空间。

**系统表空间：**主要存储MySQL内部的数据字典数据，如information_schema下的数据。

**用户表空间：**当开启innodb_file_per_table=1时，数据表从系统表空间独立出来存储在以table_name.ibd命令的数据文件中，结构信息存储在table_name.frm文件中。

**Undo表空间：**存储Undo信息，如快照一致读和flashback都是利用undo信息。

从MySQL 8.0开始允许用户自定义表空间，具体语法如下：

CREATE TABLESPACE tablespace_name  ADD DATAFILE 'file_name'        #数据文件名  USE LOGFILE GROUP logfile_group    #自定义日志文件组，一般每组2个logfile。  [EXTENT_SIZE [=] extent_size]     #区大小  [INITIAL_SIZE [=] initial_size]    #初始化大小   [AUTOEXTEND_SIZE [=] autoextend_size] #自动扩宽尺寸  [MAX_SIZE [=] max_size]        #单个文件最大size，最大是32G。  [NODEGROUP [=] nodegroup_id]      #节点组  [WAIT]  [COMMENT [=] comment_text]  ENGINE [=] engine_name

这样就可以将核心的业务表如用户表，订单表存储在高性能SSD盘上，一些日志，流水表存储在普通的HDD上，主要的操作步骤如下：

#创建热数据表空间create tablespace tbs_data_hot add datafile '/hot_data/tbs_data_hot01.dbf' max_size 20G;#创建核心业务表存储在热数据表空间create table booking(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_hot;#创建冷数据表空间create tablespace tbs_data_cold add datafile '/hot_data/tbs_data_cold01.dbf' max_size 20G;#创建日志，流水，备份类的表存储在冷数据表空间create table payment_log(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_cold;#可以移动表到另一个表空间alter table payment_log tablespace tbs_data_hot;

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibdpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000000, page type <Freshly Allocated Page>page offset 00000000, page type <Freshly Allocated Page>Total number of page: 6:   #总共分配的页数Freshly Allocated Page: 2   #可用的数据页Insert Buffer Bitmap: 1    #插入缓冲页File Space Header: 1     #文件空间头B-tree Node: 1        #数据页File Segment inode: 1     #文件端inonde，如果是在ibdata1.ibd上会有多个inode。

Innodb中的碎片

碎片的产生

我们知道数据存储在文件系统上的，总是不能100%利用分配给它的物理空间，删除数据会在页面上留下一些”空洞”，或者随机写入（聚集索引非线性增加）会导致页分裂，页分裂导致页面的利用空间少于50%，另外对表进行增删改会引起对应的二级索引值的随机的增删改，也会导致索引结构中的数据页面上留下一些"空洞"，虽然这些空洞有可能会被重复利用，但终究会导致部分物理空间未被使用，也就是碎片。

同时，即便是设置了填充因子为100%，Innodb也会主动留下page页面1/16的空间作为预留使用（An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth）防止update带来的行溢出。

mysql> select table_schema,  ->    table_name,ENGINE,  ->    round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb,TABLE_ROWS,  ->    round(DATA_LENGTH/1024/1024) data_mb, round(INDEX_LENGTH/1024/1024) index_mb, round(DATA_FREE/1024/1024) free_mb, round(DATA_FREE/DATA_LENGTH*100,2) free_ratio  -> from information_schema.TABLES where TABLE_SCHEMA= 'test'  -> and TABLE_NAME= 'user';+--------------+------------+--------+----------+------------+---------+----------+---------+------------+| table_schema | table_name | ENGINE | total_mb | TABLE_ROWS | data_mb | index_mb | free_mb | free_ratio |+--------------+------------+--------+----------+------------+---------+----------+---------+------------+| test     | user   | InnoDB |    4 |   50000 |    4 |    0 |    6 |   149.42 |+--------------+------------+--------+----------+------------+---------+----------+---------+------------+1 row in set (0.00 sec)

碎片的回收

对于InnoDB的表，可以通过以下命令来回收碎片，释放空间，这个是随机读IO操作，会比较耗时，也会阻塞表上正常的DML运行，同时需要占用额外更多的磁盘空间，对于RDS来说，可能会导致磁盘空间瞬间爆满，实例瞬间被锁定，应用无法做DML操作，所以禁止在线上环境去执行。

#执行InnoDB的碎片回收mysql> alter table user engine=InnoDB;Query OK, 0 rows affected (9.00 sec)Records: 0 Duplicates: 0 Warnings: 0##执行完之后，数据文件大小从14MB降低到10M。# ls -lh /data2/mysql/test/user1.ibd -rw-r----- 1 mysql mysql 10M Nov 6 16:18 /data2/mysql/test/user.ibd

操作	COST	物理读次数	逻辑读次数	扫描行数	返回行数	执行时间
初始化插入100W	10.499000	7868409	7855239	22226	11111	30ms
100W随机删除50W	10.499000	7868409	7855239	22226	0	50ms

这也说明对普通的大表，想要通过delete数据来对表进行瘦身是不现实的，所以在任何时候不要用delete去删除数据，应该使用优雅的标记删除。

delete优化建议

控制业务账号权限

对于一个大的系统来说，需要根据业务特点去拆分子系统，每个子系统可以看做是一个service，例如美团APP，上面有很多服务，核心的服务有用户服务user-service，搜索服务search-service，商品product-service，位置服务location-service，价格服务price-service等。每个服务对应一个数据库，为该数据库创建单独账号，同时只授予DML权限且没有delete权限，同时禁止跨库访问。

#创建用户数据库并授权create database mt_user charset utf8mb4;grant USAGE, SELECT, INSERT, UPDATE ON mt_user.* to 'w_user'@'%' identified by 't$W*g@gaHTGi123456';flush privileges;

一些查询业务场景都会有一个默认的时间段，比如7天或者一个月，都是通过create_time去过滤，走索引扫描更快。
一些核心的业务表需要以T +1的方式抽取数据仓库中，比如每天晚上00:30抽取前一天的数据，都是通过create_time过滤的。

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否逻辑删除：0：未删除，1：已删除',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'#有了删除标记，业务接口的delete操作就可以转换为updateupdate user set is_deleted = 1 where user_id = 1213;#查询的时候需要带上is_deleted过滤select id, age ,phone from user where is_deleted = 0 and name like 'lyn12%';

总结

通过从InnoDB存储空间分布，delete对性能的影响可以看到，delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；

同时在碎片回收时，会耗用大量的CPU，磁盘空间，影响表上正常的DML操作。

在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以用采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。

另外一个比较好的方案采用Clickhouse，对有生命周期的数据表可以使用Clickhouse存储，利用其TTL特性实现无效数据自动清理。

前言