-- 列出所有已经产生碎片的表
select table_schema db, table_name, round(data_free/1024/1024, 2) data_free, engine,table_rows, round((data_length+index_length)/1024/1024,2) length
from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;
-- 查询并计算碎片率
select table_schema db, table_name, engine,table_rows, round(data_free/1024/1024, 2) data_free_M, round((data_length+index_length)/1024/1024,2) length_M , round(data_free/(data_free + data_length+index_length),2) rate
from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0 order by data_free_M desc ,rate desc;
--查看某张表的碎片率
mysql> show table status like 't_exception_log202005';
1) MySQL官方建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可;
2) OPTIMIZE TABLE只对MyISAM,BDB和InnoDB表起作用,尤其是MyISAM表的作用最为明显。此外,并不是所有表都需要进行碎片整理,一般只需要对包含上述可变长度的文本数据类型的表进行整理即可;
3) 在OPTIMIZE TABLE 运行过程中,MySQL会锁定表;
MySQL中的索引膨胀率可以通过以下命令查看:
show table status like 'table_name'G
其中,table_name为要查看的表名。在结果中,可以看到Avg_row_length和Data_length两个字段,通过计算Avg_row_length * Rows / Data_length,可以得到索引膨胀率。
判断一个索引是否需要重建,可以通过以下几个方面来考虑:
在MySQL中,可以通过以下几个指标来判断索引是否需要重建:
show index from table_name;
其中,table_name为要查看的表名。在结果中,可以看到Key_name和Seq_in_index两个字段,通过计算Seq_in_index的平均值,可以得到索引使用率。
show table status like 'table_name'G
其中,table_name为要查看的表名。在结果中,可以看到Index_length字段,即为索引大小。如果索引大小过大,需要考虑重建索引或者优化表结构。