甲方要求每个月整理一下MySQL的表碎片率，并汇报
查询表碎片sql
select t.table_schema, t.table_name, t.table_rows, t.data_length+ t.index_length data_size, t.index_length index_size, t.avg_row_length, t.avg_row_length * t.table_rows logic_size, s.FILE_SIZE, truncate(s.FILE_SIZE/ (t.data_length+ t.index_length)*1.1*2 ,0)tab_frag,(s.FILE_SIZE-(t.data_length+ t.index_length))/s.FILE_SIZE tab FROM information_schema.tables t, information_schema.INNODB_SYS_TABLESPACES s WHERE t.table_type = 'BASE TABLE' and concat(t.table_schema,'/',t.table_name)=s.name and t.table_schema not in ('sys','information_schema','mysql','test') and s.FILE_SIZE >102400000 and (t.data_length+ t.index_length)*1.1*2 < s.FILE_SIZE order by s.FILE_SIZE;
为什么会产生碎片
极客时间的mysql45讲说的是innodb的表索引都是b+树，这样搞的就是索引即数据，数据即索引，数据肯定是按照b+树的方式进行排列，且数据在b+树的最底一层，即叶子节点
image.png
当你删除90这个数据，并不是在Linux的文件系统把这个数据给抹掉，而是在MySQL的B+树标记为已删除，而并非是在Linux数据目录删除。
例如删除的是90，则90被标记为已删除，可复用，B+树是按照页为单位进行数据存储的，[90,99为一个数据页]，假如再插入一条数据为【79，99之间的】，则90的标记位可以复用，否则这个就是一个空洞，在Linux既不会删除，也不会复用。
所以当你delete整张表，那该表就会被标记为已删除，可复用，但是在Linux上这个表的xxx.ibd文件并不会变化。(可以自行测试)
那么知道了上述知识，聪明的我们该如何整理碎片呢？
对于innodb引擎的表 ，执行如下sql
alter table table_name engine=InnoDB；
网友评论