my30_表碎片整理

确认表的类型与存储引擎，是否全部是innodb

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS from information_schema.tables where lower(ENGINE)='innodb' and TABLE_SCHEMA not in ('mysql','information_schema','performance_schema');

 

#查看库中碎片率超过30%的表，排除了mysql库

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,
round((DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1024/1024) tblsize_M,
round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) tblfree_pct from information_schema.tables 
where lower(ENGINE)='innodb' and table_schema not in('mysql') and round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) > 30
order by TABLE_ROWS ;

 

表碎片率，指定库或表

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,
round((DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1024/1024) tblsize_M,
round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) tblfree_pct from information_schema.tables 
where lower(ENGINE)='innodb' and TABLE_SCHEMA='vodb';

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,
round((DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1024/1024) tblsize_M,
round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) tblfree_pct from information_schema.tables 
where lower(ENGINE)='innodb' and TABLE_NAME='ad_account_record_log_242';
 

 

#空间收缩

select concat('alter table ',tbl.tblname , ' ENGINE = ''InnoDB''; ') sqltext from (
select concat(TABLE_SCHEMA,'.',TABLE_NAME) tblname,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,
round((DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1024/1024) tblsize_M,
round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) tblfree_pct from information_schema.tables 
where lower(ENGINE)='innodb' and table_schema not in('mysql') and round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) > 30) tbl;

 

#统计分析更新统计信息

select concat('analyze table ',tbl.tblname , ' ; ') sqltext from (
select concat(TABLE_SCHEMA,'.',TABLE_NAME) tblname,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,
round((DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1024/1024) tblsize_M,
round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) tblfree_pct from information_schema.tables 
where lower(ENGINE)='innodb' and table_schema not in('mysql') and round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) > 30) tbl;

 

analyze table sbtest1;

比如在下面的语句中，在不进行analyze的情况下，TABLE_ROWS与真实数据相差不大，但大小相关字段会有比较大的出入
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,
round((DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1024/1024) tblsize_M,
round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) tblfree_pct from information_schema.tables
where lower(ENGINE)='innodb' and TABLE_SCHEMA='vodb' and round(DATA_FREE*100.0/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )) > 50;

如果是主从结果，主库进行空间收缩，从库随之收缩。