浅析MySQL数据碎片的产生

vlambda
2020-08-12

浅析MySQL数据碎片的产生

MySQL列表，包括MyISAM和InnoDB这两种最常见的类型，而根据经验来说，其碎片的产生及消除都是随机的。碎片会在你的表格中留下明显的空白，而这会给列表扫描工作带来相当大的困扰。对你的列表进行优化，这样会使列表的全面及分区扫描工作进行得更有效率。

碎片——实例

MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

这种额外的破碎的存储空间在读取效率方面比正常占用的空间要低得多。让我们看一个实例。

我们将创建一个数据库（有时也称其为大纲）及一个测试用的列表:

(root@localhost) [test]> create database frag_test;
Query OK, 1 row affected (0.03 sec)
(root@localhost) [test]> use frag_test;
Database changed
(root@localhost) [frag_test]> create table frag_test (c1 varchar(64));
Query OK, 0 rows affected (0.05 sec)

现在让我们在列表中加入如下几行:

(root@localhost) [frag_test]> insert into frag_test values ('this is row 1');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [frag_test]> insert into frag_test values ('this is row 2');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [frag_test]> insert into frag_test values ('this is row 3');
Query OK, 1 row affected (0.00 sec)

现在我们进行碎片查看:

(root@localhost) [frag_test]> show table status from frag_test\G;
*************************** 1. row ***************************
Name: frag_test
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 20
Data_length: 60
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2011-02-23 14:55:27
Update_time: 2011-02-23 15:06:55
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

现在我们删除一行，并再次检测:

(root@localhost) [frag_test]> delete from frag_test where c1 = 'this is row 2';
Query OK, 1 row affected (0.00 sec)
(root@localhost) [frag_test]> show table status from frag_test\G;
*************************** 1. row ***************************
Name: frag_test
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 20
Data_length: 60
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 20
Auto_increment: NULL
Create_time: 2011-02-23 14:55:27
Update_time: 2011-02-23 15:07:49
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

需要注意的是，“data_free”一栏显示出了我们删除第二行后所产生的留空空间。想象一下如果你有两万行指令的话，结果是什么样的。以此推算，它们将耗费四十万字节的存储空间。现在如果你将两万条命令行删到只剩一行，列表中有用的内容将只占二十字节，但MySQL在读取中会仍然将其视同于一个容量为四十万字节的列表进行处理，并且除二十字节以外，其它空间都被白白浪费了。

清理碎片

幸运的是一旦你锁定了这一问题，MySQL提供了一种简便的修正方法。这就是所谓的优化列表，具体内容如下:

(root@localhost) [frag_test]> optimize table frag_test;
+---------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+----------+
| frag_test.frag_test | optimize | status | OK |
+---------------------+----------+----------+----------+
1 row in set (0.00 sec)
(root@localhost) [frag_test]> show table status from frag_test\G;
*************************** 1. row ***************************
Name: frag_test
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 20
Data_length: 40
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2011-02-23 14:55:27
Update_time: 2011-02-23 15:11:05
Check_time: 2011-02-23 15:11:05
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

性能考量

“优化列表”功能在进行中会对整个列表进行锁定。对于小型列表，这一功能的效果非常好，因为整个列表的读取和修改速度都会很快。但对于那些体积巨大的列表来说，这一过程将消耗很长时间，并且其间会中断或减少可用的应用程序数量。怎么办？

再一次，MySQL幸运地提供了一项堪称伟大的功能，名为“主-主复制”。在这种配置之下，你的后台数据库实际上成为两个单独的数据库，一个主动可调用的，一个被动可调整的。这两个数据库在各方面来说都是完全相同的。要实现各种在线操作——包括“优化列表”操作——只需在你的被动数据库中即可进行。这将不会对你的应用程序造成丝毫影响。一旦优化操作完成，主、被动数据库将互相转换，以便应用程序直接指向二号数据库，对还未进行优化的主动数据库部分自动开始优化工作。

这时，两套数据库的角色已经互换，而应用程序也将顺利指向二号数据库，执行与在一号数据库上相同的列表优化。而现在主动已经转换为被动，因此不会中断主要任务处理。

其它命令

显示你数据库中存在碎片的全部列表:

(root@localhost) [(none)]> select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;
+--------------+-----------------------------+-----------+--------+
| table_schema | table_name | data_free | engine |
+--------------+-----------------------------+-----------+--------+
| aitc | wp_comments | 346536 | MyISAM |
| aitc | wp_options | 64308 | MyISAM |
| aitc | wp_postmeta | 124 | MyISAM |
| cactidb | poller_item | 160 | MyISAM |
| cactidb | poller_output | 384 | MyISAM |
| drupal | sessions | 30976 | MyISAM |
| drupal | users | 92 | MyISAM |
| drupal | variable | 20 | MyISAM |
| gg | wp_comments | 232 | MyISAM |
| gg | wp_options | 696 | MyISAM |
| gg | wp_postmeta | 560 | MyISAM |
| ihi | wp_comments | 536 | MyISAM |
| ihi | wp_options | 444 | MyISAM |
| ihi | wp_postmeta | 288 | MyISAM |
| ihi | wp_redirection_items | 1292 | MyISAM |
| ihi | wp_redirection_logs | 140352 | MyISAM |
| nds | wp_comments | 4704 | MyISAM |
| nds | wp_options | 150580 | MyISAM |
| nds | wp_postmeta | 76 | MyISAM |
| oos | wp_comments | 317124 | MyISAM |
| oos | wp_options | 88196 | MyISAM |
| oos | wp_postmeta | 76 | MyISAM |
| phplist | phplist_listuser | 252 | MyISAM |
| phplist | phplist_sendprocess | 52 | MyISAM |
| phplist | phplist_user_user | 32248 | MyISAM |
| phplist | phplist_user_user_attribute | 120 | MyISAM |
| phplist | phplist_user_user_history | 288 | MyISAM |
| phplist | phplist_usermessage | 1428 | MyISAM |
| pn_nds | nuke_session_info | 12916 | MyISAM |
| psa | exp_event | 10024 | MyISAM |
| test | active_sessions | 30144 | MyISAM |
+--------------+-----------------------------+-----------+--------+
31 rows in set (0.26 sec)