Mysql 删除数据后为释放物理空间

Mysql 删除数据后为释放物理空间

---

OPTIMIZE TABLE

当mysql使用delete删除表数据时，数据是已经删除了，但是物理空间并没有释放。这是因为删除操作后在数据文件中留下碎片所致。


OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据，
或者如果已经对含有可变长度行的表（含有 VARCHAR 、 BLOB 或 TEXT 列的表）进行了很多更改，就应该使用 OPTIMIZE TABLE 命令来进行表优化。
这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费 。
OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用 。
表优化的工作可以每周或者每月定期执行，对提高表的访问效率有一定的好处，但是需要注意的是，优化表期间会锁定表，所以一定要安排在空闲时段进行。


针对nagios数据库中的centreon_storage库来看。。。
前库：
-rw-rw---- 1 mysql mysql         8680 12月 10 2013 data_bin.frm
-rw-rw---- 1 mysql mysql   8456937916 2月   4 11:04 data_bin.MYD
-rw-rw---- 1 mysql mysql  13558496256 2月   4 11:04 data_bin.MYI
                     
-rw-rw---- 1 mysql mysql           65 12月 10 2013 db.opt


-rw-rw---- 1 mysql mysql        13220 1月  18 2014 index_data.frm
-rw-rw---- 1 mysql mysql       747676 2月   3 17:34 index_data.MYD
-rw-rw---- 1 mysql mysql       640000 2月   3 17:34 index_data.MYI


-rw-rw---- 1 mysql mysql      4134010 2月   4 06:00 log_archive_host.MYD
-rw-rw---- 1 mysql mysql      3618816 2月   4 06:00 log_archive_host.MYI


-rw-rw---- 1 mysql mysql         9862 9月  18 16:13 log_archive_service.frm
-rw-rw---- 1 mysql mysql     53492019 2月   4 06:00 log_archive_service.MYD
-rw-r----- 1 root  root      53075565 9月  18 15:38 log_archive_service.MYD.bak
-rw-rw---- 1 mysql mysql     42173440 2月   4 06:00 log_archive_service.MYI

-rw-rw---- 1 mysql mysql        13142 12月 10 2013 log.frm
-rw-rw---- 1 mysql mysql 108642538008 10月 20 03:08 log.MYD
-rw-rw---- 1 mysql mysql  17428612096 10月 20 03:09 log.MYI



-rw-rw---- 1 mysql mysql         9182 1月  18 2014 metrics.frm
-rw-rw---- 1 mysql mysql       784896 2月   4 11:04 metrics.MYD
-rw-rw---- 1 mysql mysql      1030144 2月   4 11:04 metrics.MYI


-rw-rw---- 1 mysql mysql         8696 12月 10 2013 nagios_stats.frm
-rw-rw---- 1 mysql mysql         9144 2月   4 11:00 nagios_stats.MYD
-rw-rw---- 1 mysql mysql         1024 2月   4 11:00 nagios_stats.MYI

备份恢复后的目标库：
-rw-rw----. 1 mysql mysql       8680 Feb  4 00:17 data_bin.frm
-rw-rw----. 1 mysql mysql 8439607876 Feb  4 01:17 data_bin.MYD
-rw-rw----. 1 mysql mysql 6817975296 Feb  4 04:59 data_bin.MYI




-rw-rw----. 1 mysql mysql         61 Feb  3 23:16 db.opt


-rw-rw----. 1 mysql mysql      13220 Feb  4 05:00 index_data.frm
-rw-rw----. 1 mysql mysql     667324 Feb  4 05:00 index_data.MYD
-rw-rw----. 1 mysql mysql     487424 Feb  4 05:00 index_data.MYI





-rw-rw----. 1 mysql mysql    3014316 Feb  4 05:01 log_archive_host.MYD
-rw-rw----. 1 mysql mysql    2391040 Feb  4 05:01 log_archive_host.MYI


-rw-rw----. 1 mysql mysql       9862 Feb  4 05:01 log_archive_service.frm
-rw-rw----. 1 mysql mysql   37724055 Feb  4 05:01 log_archive_service.MYD
-rw-rw----. 1 mysql mysql   28833792 Feb  4 05:01 log_archive_service.MYI


-rw-rw----. 1 mysql mysql      13142 Feb  4 05:01 log.frm
-rw-rw----. 1 mysql mysql          0 Feb  4 05:01 log.MYD
-rw-rw----. 1 mysql mysql       4096 Feb  4 05:01 log.MYI


-rw-rw----. 1 mysql mysql       9182 Feb  4 05:01 metrics.frm
-rw-rw----. 1 mysql mysql     633320 Feb  4 05:01 metrics.MYD
-rw-rw----. 1 mysql mysql     852992 Feb  4 05:01 metrics.MYI


-rw-rw----. 1 mysql mysql       8696 Feb  4 05:01 nagios_stats.frm
-rw-rw----. 1 mysql mysql       6336 Feb  4 05:01 nagios_stats.MYD
-rw-rw----. 1 mysql mysql       1024 Feb  4 05:01 nagios_stats.MYI

对比起来，差距显然意见了，原来库大概高达100多G，备份恢复后的库才20G，所以进行 OPTIMIZE TABLE  操作相当重要
特别对于使用MyISAM存储引擎的表，更应该进行optimize操作。


1.原始数据，使用test库中的test表

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  2621440 |           //262万数据
+----------+
1 row in set (0.00 sec)

mysql>


存储引擎是MyISAM存储引擎，数据文件初始大小：
-rw-rw---- 1 mysql mysql     8624 Feb  4 15:32 test.frm
-rw-rw---- 1 mysql mysql 60293120 Feb  4 15:33 test.MYD       //数据文件接近60M
-rw-rw---- 1 mysql mysql 29329408 Feb  4 15:33 test.MYI       //索引文件接近29M


查看一下索引信息：
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test  |          1 | dept     |            1 | DEPTNO      | A         |        5120 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.02 sec)
mysql>


索引信息中的列的信息:
Table :表的名称。
Non_unique :如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name :索引的名称。
Seq_in_index :索引中的列序列号，从1开始。
Column_name :列名称。
Collation :列以什么方式存储在索引中。在MySQLSHOW INDEX语法中，有值’A’（升序）或NULL（无分类）。
Cardinality :索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。
             基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。
         基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part :如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed :指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null :如果列含有NULL，则含有YES。如果没有，则为空。
Index_type ：存储索引数据结构方法（BTREE, FULLTEXT, HASH, RTREE）



关闭mysql的自动提交
(本想试一下回滚，MyISAM 无事务概念，这个探究没意义)
mysql> show variables like "%commit%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%commit%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql>


数据增加一倍：
mysql> insert into test select * from test;
Query OK, 2621440 rows affected (20.01 sec)
Records: 2621440  Duplicates: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  5242880 |
+----------+
1 row in set (0.00 sec)

mysql>

数据文件：
[root@mysqltest test]# ll
-rw-rw---- 1 mysql mysql      8624 Feb  4 15:32 test.frm
-rw-rw---- 1 mysql mysql 120586240 Feb  4 15:57 test.MYD
-rw-rw---- 1 mysql mysql  58659840 Feb  4 15:57 test.MYI




2. 删除一半数据
数据就是重复5条一样的数据：
mysql> select * from test limit 10;
+--------+------------+-----------+
| DEPTNO | DNAME      | LOC       |
+--------+------------+-----------+
| 10     | ACCOUNTING | LONDON    |
| 30     | SALES      | LIVERPOOL |
| 40     | OPERATIONS | STAFFORD  |
| 50     | MARKETING  | LUTON     |
| 20     | RESEARCH   | PERSTON   |

删除2/5数据
mysql> delete from test where deptno=10;
Query OK, 1048576 rows affected (16.06 sec)

mysql> delete from test where deptno=20;
Query OK, 1048576 rows affected (10.27 sec)

mysql>

 
查看物理文件：（大小没有一点变化）
[root@mysqltest test]# ll
-rw-rw---- 1 mysql mysql      8624 Feb  4 15:32 test.frm
-rw-rw---- 1 mysql mysql 120586240 Feb  4 16:05 test.MYD
-rw-rw---- 1 mysql mysql  58659840 Feb  4 16:05 test.MYI

按常规思想来说，如果在数据库中删除数据后，相对应的.MYD,.MYI文件也应当相应减小。但是删除2/5数据后，.MYD.MYI尽然连1KB都没有减少 ，这是多么的可怕啊。
我们在来看一看，索引信息
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test  |          1 | dept     |            1 | DEPTNO      | A         |        6144 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql>
对比一下，这次索引查询和上次索引查询，里面的数据信息基本上是上次一样的，这点还是合乎常理。







3.  用optimize table来优化一下

mysql> optimize table test;
+-----------+----------+----------+----------+
| Table     | Op       | Msg_type | Msg_text |
+-----------+----------+----------+----------+
| test.test | optimize | status   | OK       |
+-----------+----------+----------+----------+
1 row in set (4.55 sec)

mysql>
 
 
查看一下.MYD,.MYI文件的大小

[root@mysqltest test]# ll
total 95872
-rw-rw---- 1 mysql mysql     8624 Feb  4 15:32 test.frm
-rw-rw---- 1 mysql mysql 72351744 Feb  4 16:16 test.MYD
-rw-rw---- 1 mysql mysql 25697280 Feb  4 16:16 test.MYI
[root@mysqltest test]#


 2，查看一下索引信息
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test  |          1 | dept     |            1 | DEPTNO      | A         |           3 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql>    
从以上数据我们可以得出，索引机会提高了很多，这样效率提高了好多。




4.  删除剩下的数据
mysql> delete  from test;
Query OK, 3145728 rows affected (0.05 sec)

数据文件：（已全部删除数据，所已数据已变化为0了）
[root@mysqltest test]# ll
total 16
-rw-rw---- 1 mysql mysql 8624 Feb  4 15:32 test.frm
-rw-rw---- 1 mysql mysql    0 Feb  4 16:10 test.MYD
-rw-rw---- 1 mysql mysql 1024 Feb  4 16:10 test.MYI


小结
当你删除数据 时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。
而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。

所以对于写比较频烦的表，要定期进行optimize，一个月一次，看实际情况而定了。

关于OPTIMIZE的一些用法和描述
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新 利用未使用的空间，并整理数据文件的碎片。
在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次 即可，只对特定的表运行。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。对应用有很大影响

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

转载于:http://blog.itpub.net/29500582/viewspace-1426409/