mysql删除数据⽆效_为什么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] #区⼤⼩
下载mysql为什么下载不了[INITIAL_SIZE [=] initial_size] #初始化⼤⼩
[AUTOEXTEND_SIZE [=] autoextend_size] #⾃动扩宽尺⼨
[MAX_SIZE [=] max_size] #单个⽂件最⼤size，最⼤是32G。
[NODEGROUP [=] nodegroup_id] #节点组
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name
复制代码
这样的好处是可以做到数据的冷热分离，分别⽤HDD和SSD来存储，既能实现数据的⾼效访问，⼜能节约成本，⽐如可以添加两块500G硬盘，经过创建卷组vg，划分逻辑卷lv，创建数据⽬录并mount相应的lv，假设划分的两个⽬录分别是/hot_data 和 /cold_data。
这样就可以将核⼼的业务表如⽤户表，订单表存储在⾼性能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;
复制代码
Inndob存储分布
创建空表查看空间变化mysql> create table user(id bigint not null primary key auto_increment,
-> name varchar(20) not null default '' comment '姓名',
-> age tinyint not null default 0 comment 'age',
-> gender char(1) not null default 'M' comment '性别',
-> phone varchar(16) not null default '' comment '⼿机号',
-> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
-> ) engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '⽤户信息表';
Query OK, 0 rows affected (0.26 sec)
复制代码# ls -lh user1.ibd
-rw-r----- 1 mysql mysql 96K Nov 6 12:48 user.ibd
复制代码
设置参数innodb_file_per_table=1时，创建表时会⾃动创建⼀个segment，同时分配⼀个extent，包含32个data page的来存储数据，这样创建的空表默认⼤⼩就是96KB，extent使⽤完之后会申请64个连接页，这样对于⼀些⼩表，或者undo segment，可以在开始时申请较少的空间，节省磁盘容量的开销。# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0000>
page offset 00000000, page type
page offset 00000000, page type
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。
复制代码
插⼊数据后的空间变化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)
复制代码# ls -lh user.ibd
-rw-r----- 1 mysql mysql 14M Nov 6 10:58 /data2/mysql/test/user.ibd
# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0001> #增加了⼀个⾮叶⼦节点，树的⾼度从1变为2.
........................................................
page offset 00000000, page type
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
复制代码
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
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0001> ........................................................
page offset 00000000, page type
Total number of page: 896:
Freshly Allocated Page: 493
Insert Buffer Bitmap: 1
File Space Header: 1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。