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會鎖定表。對應用有很大影響
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql InnoDB刪除資料後釋放磁碟空間的方法MySql
- MySQL 5.7的表刪除資料後的磁碟空間釋放MySql
- Oracle 刪除資料後釋放資料檔案所佔磁碟空間Oracle
- hpux刪除檔案後空間不釋放UX
- 歸檔日誌物理刪除後閃回恢復區空間未釋放
- Linux 刪除檔案後空間不釋放Linux
- oracle刪除(釋放)資料檔案/表空間流程Oracle
- RM刪除檔案空間釋放詳解
- Linux檔案刪除空間未釋放Linux
- （轉載）刪除檔案後硬碟空間不釋放的問題硬碟
- 記錄刪除後，資料塊空間不釋放，請大家幫忙看看分析一下
- [待整理]oracle10g刪除(釋放)資料檔案/表空間流程Oracle
- 處理Linux刪除檔案後空間未釋放的問題Linux
- 解決linux刪除檔案後空間沒有釋放問題Linux
- 刪除檔案後，磁碟空間沒有釋放的處理記錄
- linux下檔案刪除之後，空間沒有釋放問題Linux
- 解決linux下刪除檔案或oracle表空間後空間不釋放的問題LinuxOracle
- 刪除資料庫表空間資料庫
- OS 刪除temp表空間 而磁碟空間未釋放的解決方案
- 解決刪除檔案後 WSL2 磁碟空間不釋放的問題
- oracle 失誤刪掉資料檔案後，刪除表空間操作Oracle
- Linux檔案刪除但空間不釋放問題篇Linux
- 刪除正在使用的檔案，空間不釋放的問題
- Linux下資料檔案刪除檔案系統空間不釋放的問題Linux
- Win10電腦刪除Windows.old資料夾釋放C盤空間的技巧Win10Windows
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- 刪除表空間，資料檔案也刪除後，但作業系統層面上空閒空間不見增加。作業系統
- MySQL什麼情況下刪除資料會釋放空間MySql
- drop表空間以及對應的資料檔案後空間不釋放的問題
- delete/truncate刪除資料索引空間問題delete索引
- 臨時表空間資料刪除問題
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- Sqlserver delete表部分資料釋放資料檔案空間SQLServerdelete
- 如何釋放Mac空間？釋放Mac系統空間小技巧Mac
- Oracle delete資料後的釋放表空間問題的解決 --轉Oracledelete
- 【實驗】兩種方法刪除表中的列與空間儲存釋放
- oracle誤刪除表空間的資料檔案Oracle
- 刪除空資料檔案