前言
吐槽一下 公司 定期进行的 对 sql流水表进行的瘦身操作 最近接手了一个 公司的老项目，之前是外包出去的，目前外包撤走，接手到自己手里，代码层面先不吐槽了，发现了一个比较有意思的操作，给大家分享一下，项目里有些表的数据量增长很快，这些大表都是一些流水、记录、日志类型数据，只需要查看一下最近的一些数据即可，由于数据量增长太大，查询时会扫到许多没用的数据，导致sql很慢，于是项目里就出现了这样一个操作，物理删除，写了一个定时脚本，定期去删除表内 3个月之外的数据，以保证“数据瘦身”；
本身打算自己写的找资料的时候发现了一篇大佬的文章发现写的超级的好在这里引用一下
这篇文章我会从InnoDB存储空间分布，delete对性能的影响，以及优化提议方面解释为什么不提议delete删除数据。
为什么Mysql不提议delete操作
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
直接上代码来看一下，我们先插入一批数据然后在删除，看一下mysql空间的变化
- 插入数据后的空间变化
mysql> DELIMITER $$
mysql> CREATE PROCEDURE insert_user_data(num INTEGER)
-> BEGIN
-> DECLARE v_i int unsigned DEFAULT 0;
-> set autocommit= 0;
-> WHILE v_i < num DO
-> insert into user(`name`, age, gender, phone) values (CONCAT( lyn ,v_i), mod(v_i,120), M , CONCAT( 152 ,ROUND(RAND(1)*100000000)));
-> SET v_i = v_i+1;
-> END WHILE;
-> commit;
-> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
#插入10w数据
mysql> call insert_user_data(100000);
Query OK, 0 rows affected (6.69 sec)
- delete数据后的空间变化
mysql> select min(id),max(id),count(*) from user;
+---------+---------+----------+
| min(id) | max(id) | count(*) |
+---------+---------+----------+
| 1 | 100000 | 100000 |
+---------+---------+----------+
1 row in set (0.05 sec)
#删除50000条数据，理论上空间应该从14MB变长7MB左右。
mysql> delete from user limit 50000;
Query OK, 50000 rows affected (0.25 sec)
#数据文件大小依然是14MB，没有缩小。
# ls -lh /data2/mysql/test/user1.ibd
-rw-r----- 1 mysql mysql 14M Nov 6 13:22 /data2/mysql/test/user.ibd
#数据页没有被回收。
# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page 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 <0001>
........................................................
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 896:
Freshly Allocated Page: 493
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 400
File Segment inode: 1
#在MySQL内部是标记删除，
-
结论：
MySQL内部不会真正删除空间，而且做标记删除，即将delflag:N修改为delflag:Y，commit之后会会被purge进入删除链表，如果下一次insert更大的记录，delete之后的空间不会被重用，如果插入的记录小于等于delete的记录空会被重用，这块内容可以通过知数堂的innblock工具进行分析。 -
分析 delete对sql的影响
delete前
#插入100W数据
mysql> call insert_user_data(1000000);
Query OK, 0 rows affected (35.99 sec)
#添加相关索引
mysql> alter table user add index idx_name(name), add index idx_phone(phone);
Query OK, 0 rows affected (6.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#表上索引统计信息
mysql> show index from user;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 996757 | NULL | NULL | | BTREE | | |
| user | 1 | idx_name | 1 | name | A | 996757 | NULL | NULL | | BTREE | | |
| user | 1 | idx_phone | 1 | phone | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
#重置状态变量计数
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
#执行SQL语句
mysql> select id, age ,phone from user where name like lyn12% ;
+--------+-----+-------------+
| id | age | phone |
+--------+-----+-------------+
| 124 | 3 | 15240540354 |
| 1231 | 30 | 15240540354 |
| 12301 | 60 | 15240540354 |
.............................
| 129998 | 37 | 15240540354 |
| 129999 | 38 | 15240540354 |
| 130000 | 39 | 15240540354 |
+--------+-----+-------------+
11111 rows in set (0.03 sec)
mysql> explain select id, age ,phone from user where name like lyn12% ;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
| 1 | SIMPLE | user | range | idx_name | idx_name | 82 | NULL | 22226 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
#查看相关状态呢变量
mysql> select * from information_schema.session_status where variable_name in( Last_query_cost , Handler_read_next , Innodb_pages_read , Innodb_data_reads , Innodb_pages_read );
+-------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------+----------------+
| HANDLER_READ_NEXT | 11111 | #请求读的行数
| INNODB_DATA_READS | 7868409 | #数据物理读的总数
| INNODB_PAGES_READ | 7855239 | #逻辑读的总数
| LAST_QUERY_COST | 10.499000 | #SQL语句的成本COST，主要包括IO_COST和CPU_COST。
+-------------------+----------------+
4 rows in set (0.00 sec)
delete后
#删除50w数据
mysql> delete from user limit 500000;
Query OK, 500000 rows affected (3.70 sec)
#分析表统计信息
mysql> analyze table user;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.user | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.01 sec)
#重置状态变量计数
mysql> flush status;
Query OK, 0 rows affected (0.01 sec)
mysql> select id, age ,phone from user where name like lyn12% ;
Empty set (0.05 sec)
mysql> explain select id, age ,phone from user where name like lyn12% ;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
| 1 | SIMPLE | user | range | idx_name | idx_name | 82 | NULL | 22226 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.session_status where variable_name in( Last_query_cost , Handler_read_next , Innodb_pages_read , Innodb_data_reads , Innodb_pages_read );
+-------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------+----------------+
| HANDLER_READ_NEXT | 0 |
| INNODB_DATA_READS | 7868409 |
| INNODB_PAGES_READ | 7855239 |
| LAST_QUERY_COST | 10.499000 |
+-------------------+----------------+
4 rows in set (0.00 sec)
这也说明对普通的大表，想要通过delete数据来对表进行瘦身是不现实的，所以在任何时候不要用delete去删除数据，应该使用优雅的标记删除。
总结：
- 通过从InnoDB存储空间分布，delete对性能的影响可以看到，delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；
- 同时在碎片回收时，会耗用大量的CPU，磁盘空间，影响表上正常的DML操作。
- 在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以用采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。
- 另外一个比较好的方案采用Clickhouse，对有生命周期的数据表可以使用Clickhouse存储，利用其TTL特性实现无效数据自动清理。
大佬原文链接：https://juejin.cn/post/6913695663028961293
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
