008.mysql-mysql高水位线问题，删除数据、插入数据索引不连续带来的页内数据空洞，表实际占用空间增大

 

mysql高水位线问题：

删除数据、

插入数据索引不连续带来的页内数据空洞，表实际占用空间增大

 

优化：

alter table table_name  engine = InnoDB

相当于建立临时表，把表删除后,重新插入数据

 

原理：

InnoDB引擎只会把这个记录标记为删除，如果要复用，必须要插入相同id的，否则空间不会回收

如果删除的是page页，页的复用没有对id相同的要求

 

案例：

模拟数据  不断插入删除数据

 

CREATE TABLE `gaoshuiwei` (
  `rid` int(20) NOT NULL AUTO_INCREMENT,
  `org_name` varchar(255) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rid`),
  UNIQUE KEY `org_id` (`rid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=109592 DEFAULT CHARSET=utf8mb4;

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_gaoshuiwei`()
BEGIN

    DECLARE i int(4) DEFAULT 0;
    
    while i < 10000 do
        insert into gaoshuiwei(
        org_name 
        )
        select  '循环一般在存储过程和存储函数中使用' ;

        set i = i+1;
    end while;
    
    DELETE from gaoshuiwei where  rid %2=1;
    

END

查看表大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data

from information_schema.TABLES

where table_schema='test' and table_name='gaoshuiwei';

 

 

 

 

清理脏数据后-查看表大小

alter table table_name  engine = InnoDB

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data

from information_schema.TABLES

where table_schema='test' and table_name='gaoshuiwei';