为什么MySQL不建议delete删除数

北京中科医院假 http://www.yushiels.com/npxjk/npxhy/746.html
我负责的有几个系统随着业务量的增长,存储在MySQL中的数据日益剧增,我当时就想现在的业务方不讲武德,搞偷袭,趁我没反应过来把很多表,很快,很快啊都打到了亿级别,我大意了,没有闪,这就导致跟其Join的表的SQL变得很慢,对的应用接口的responsetime也变长了,影响了用户体验。事后我找到业务方,我批评了他们跟他们说要讲武德,连忙跟我道歉,这个事情才就此作罢,走的时候我对他们说下次不要这样了,耗子尾汁,好好反思。骂归骂,事情还是得解决,时候我分析原因发现,发现有些表的数据量增长很快,对应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字典数据和用户数据,日志文件记录的是datapage的变更记录,用于MySQLCrash时的恢复。Innodb表空间InnoDB存储包括三类表空间:系统表空间,用户表空间,Undo表空间。**系统表空间:**主要存储MySQL内部的数据字典数据,如information_schema下的数据。**用户表空间:**当开启innodb_file_per_table=1时,数据表从系统表空间独立出来存储在以table_name.ibd命令的数据文件中,结构信息存储在table_name.frm文件中。**Undo表空间:**存储Undo信息,如快照一致读和flashback都是利用undo信息。从MySQL8.0开始允许用户自定义表空间,具体语法如下:这样的好处是可以做到数据的冷热分离,分别用HDD和SSD来存储,既能实现数据的高效访问,又能节约成本,比如可以添加两块G硬盘,经过创建卷组vg,划分逻辑卷lv,创建数据目录并mount相应的lv,假设划分的两个目录分别是/hot_data和/cold_data。这样就可以将核心的业务表如用户表,订单表存储在高性能SSD盘上,一些日志,流水表存储在普通的HDD上,主要的操作步骤如下:Inndob存储分布创建空表查看空间变化设置参数innodb_file_per_table=1时,创建表时会自动创建一个segment,同时分配一个extent,包含32个datapage的来存储数据,这样创建的空表默认大小就是96KB,extent使用完之后会申请64个连接页,这样对于一些小表,或者undosegment,可以在开始时申请较少的空间,节省磁盘容量的开销。插入数据后的空间变化delete数据后的空间变化MySQL内部不会真正删除空间,而且做标记删除,即将delflag:N修改为delflag:Y,


转载请注明:http://www.aierlanlan.com/cyrz/7390.html