我最近MyISAM
通过在枚举字段中添加新值来修改了一个大表(8000 万个条目)。完成此操作后,我在用于复制主服务器的 2 个从属服务器上检查了表的运行状况。我注意到MYI
其中一个从属服务器上的文件小得多。我使用和修复了表,myisamchk
但REPAIR
索引的大小没有改变。
myisamchk -dvv
请参见以下在这 3 个服务器上运行“密钥文件长度”返回的结果:
Master Server
Auto increment key: 1 Last value: 80098340
Data records: 79375556 Deleted blocks: 0
Datafile parts: 79375556 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 9635014668 **Keyfile length: 18945252352**
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 1110
Slave Server 1
Auto increment key: 1 Last value: 80097611
Data records: 79374828 Deleted blocks: 0
Datafile parts: 79394418 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 9635788652 **Keyfile length: 18024821760**
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 1110
Slave Server 2 - Here the size of Keyfile is much smaller
Auto increment key: 1 Last value: 80098312
Data records: 79375002 Deleted blocks: 0
Datafile parts: 79375002 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 9634942908 **Keyfile length: 11092404224**
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
什么原因可能导致如此显著的差异?
答案1
如果您对 MyISAM 运行了修复,它应该会减小的大小MYI
。为什么?
当 mysqldump 创建并加载 MyISAM 表时,请考虑要执行的机械步骤mytable
:
CREATE TABLE mytable ...
LOCK TABLE mytable ...
ALTER TABLE mytable DISABLE KEYS; (shuts off updates to non-unique indexes)
INSERT INTO ...
INSERT INTO ...
.
.
.
ALTER TABLE mytable ENABLE KEYS; (rebuild all indexes)
UNLOCK TABLES;
期间ALTER TABLE ... ENABLE KEYS
,您运行SHOW PROCESSLIST;
您将看到该进程的信息Repair by sorting
完成后,您应该拥有一个 MYI,其中 95% 以上的 BTREE 节点已填满。
为什么修复后那么多空间消失了?看看相反的情况。
您正在按数字顺序加载某个 auto_increment id 上的表。按某种有序方式加载数据会导致 BTREE 索引中的键分布不平衡。在某些情况下,所有节点的碎片率可能高达 45%。
示例:如果您按顺序加载二叉树(最差的 BTREE),您将得到一个一直向右倾斜的二叉树,它看起来像一个具有负斜率的链表。
我曾经在我的 DBA StackExchange 帖子中提到过这种疯狂的现象
Aug 03, 2011
:我应该何时重建索引?Jun 28, 2012
:MySQL 中 BTREE 的优点Oct 26, 2012
:当出现无序插入时,innodb 碎片化有多严重?
REPAIR TABLE
在 mysql 客户端中运行,运行myisamchk -r
或重新加载 MyISAM 的 mysqldump 应该总是会生成较小的 MyISAM 索引文件。