北京中科医院假 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
