☰

MySql：优化表查询

tag-icon • tag-icon mysql
MySql：优化表查询

能否查明“优化”命令能够对给定表进行多少碎片整理？我只是想提前查明这些信息。

答案1

我对这个答案有一些疑惑 :-) 实际上，这就像查找值一样简单 - 一切都在 information_schema.tables 中！查找名为 data_free 的字段，它具有正确的值，并且在我所做的示例中也表现正确：

use test;
create table test1 as select * from information_schema.tables;
alter table test1 engine = myisam; -- not even necessary
select * from information_schema.tables where table_name = 'test1'\G
delete from test1 limit 10;
select * from information_schema.tables where table_name = 'test1'\G
analyze table test1;
select * from information_schema.tables where table_name = 'test1'\G
optimize table test1;
select * from information_schema.tables where table_name = 'test1'\G

答案2

information_schema.tables 始终显示正确的数据长度，因此您可以将 data_length 字段与所需数据长度的粗略估计（avg_row_length * table_rows）进行比较，但只有在使用分析表更新统计信息之后才能这样做：

analyze table 'TABLE_TO_LOOK_UP'; -- to get row count etc. right

SELECT table_name,
concat( round((data_length - (avg_row_length * table_rows)) / 1024 / 1024, 2) , 'M' ) very_theoretical_size_difference_in_MB
FROM information_schema.tables
WHERE table_name = 'TABLE_TO_LOOK_UP';

相关内容