MySQL数据碎片的整理和分析

最新推荐文章于 2025-03-08 13:13:25 发布

转载最新推荐文章于 2025-03-08 13:13:25 发布 · 189 阅读

本文通过实验说明MySQL数据库在删除数据后产生的存储空间碎片及其对读取效率的影响，并介绍如何使用OPTIMIZETABLE命令来减少碎片。

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

C:\Users\duansf>mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建一个测试库:

mysql> create database frag_test;
Query OK, 1 row affected (0.01 sec)

mysql> use frag_test;
Database changed
mysql> create table frag_test (c1 varchar(64));
Query OK, 0 rows affected (0.27 sec)

插入几行数据:
mysql> insert into frag_test values ('this is row 1');
Query OK, 1 row affected (0.21 sec)

mysql> insert into frag_test values ('this is row 2');
Query OK, 1 row affected (0.05 sec)

mysql> insert into frag_test values ('this is row 3');
Query OK, 1 row affected (0.03 sec)

现在我们进行碎片查看:
mysql> show table status from frag_test\G;
*************************** 1. row ***************************
Name: frag_test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2016-03-17 16:36:04
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

删除一行，并再次检测:

mysql> delete from frag_test where c1 = 'this is row 2';
Query OK, 1 row affected (0.07 sec)

mysql> show table status from frag_test\G;
*************************** 1. row ***************************
Name: frag_test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2016-03-17 16:36:04
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

Data_free: 10485760 我们看到Data_free的值并没有减小

清理碎片试试:

mysql> optimize table frag_test;
+---------------------+----------+----------+-----------------------------------
--------------------------------+
| Table | Op | Msg_type | Msg_text
|
+---------------------+----------+----------+-----------------------------------
--------------------------------+
| frag_test.frag_test | optimize | note | Table does not support optimize, d
oing recreate + analyze instead |
| frag_test.frag_test | optimize | status | OK
|
+---------------------+----------+----------+-----------------------------------
--------------------------------+
2 rows in set (0.66 sec)

mysql> show table status from frag_test\G;
*************************** 1. row ***************************
Name: frag_test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 9437184
Auto_increment: NULL
Create_time: 2016-03-17 16:36:04
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

Data_free: 9437184 清理碎片后，Data_free减小了。

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

备注：
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，
一般只需要对包含可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。
这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2058947/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2058947/