煮酒听雨

MySQL删除数据后，磁盘空间的释放情况

编辑时间：2019-07-28 编辑：忘尘阅读：441

###MySQL删除数据几种情况以及是否释放磁盘空间:
```
#数据表l_log文件空间情况（未drop前）
[root@izbp10njz4i1cqmwgzzfmez test]# ll
total 1204300
-rw-rw---- 1 mysql mysql         61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql      13153 Nov 14 11:06 l_log.frm  #表结构文件
-rw-rw---- 1 mysql mysql   50331648 Nov 14 11:07 l_log.ibd  #innodb表引擎的表数据、索引文件
```

#####1、drop table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM ;
```sql
#drop
MySQL [test]> drop table l_log;
Query OK, 0 rows affected (0.04 sec)
```
```linux
#数据表文件被删除
[root@izbp10njz4i1cqmwgzzfmez test]# ll
total 1155128
-rw-rw---- 1 mysql mysql         61 Nov 14 10:16 db.opt
```

#####2、truncate table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM 。
truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;

```sql
#truncate
MySQL [test]> truncate table l_log;
Query OK, 0 rows affected (0.05 sec)
```

```linux
#数据表文件存在，ibd文件大小变小
[root@izbp10njz4i1cqmwgzzfmez test]# ll
total 1106184
-rw-rw---- 1 mysql mysql         61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql      13153 Nov 14 11:10 l_log.frm
-rw-rw---- 1 mysql mysql     212992 Nov 14 11:35 l_log.ibd
```

#####3、delete from table_name删除表的全部数据，对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
######Table MyISAM type
```
#数据表l_log_1文件空间情况（未delete前）
[root@izbp10njz4i1cqmwgzzfmez test]# ll
total 1295116
-rw-rw---- 1 mysql mysql         61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql      13153 Nov 14 12:04 l_log_1.frm  #表结构文件
-rw-rw---- 1 mysql mysql   15484848 Nov 14 12:04 l_log_1.MYD  #表数据文件
-rw-rw---- 1 mysql mysql    4977664 Nov 14 12:04 l_log_1.MYI  #表索引信息文件
```
```sql
#delete MyISAM table
MySQL [test]> delete from l_log_1;
Query OK, 80313 rows affected (0.01 sec)
```

```linux
#数据表文件存在，其中frm文件大小不变， MYD文件清空，MYI变小
[root@izbp10njz4i1cqmwgzzfmez test]# ll
total 1275132
-rw-rw---- 1 mysql mysql         61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql      13153 Nov 14 12:04 l_log_1.frm   #表结构文件
-rw-rw---- 1 mysql mysql          0 Nov 14 12:08 l_log_1.MYD   #表数据文件
-rw-rw---- 1 mysql mysql       1024 Nov 14 12:08 l_log_1.MYI   #表索引信息文件
```

######Table InnoDB type
```sql
#delete INNODB table
MySQL [test]> delete from l_log;
Query OK, 80313 rows affected (14.04 sec)
```
```linux
#数据表文件存在，文件大小不变
[root@izbp10njz4i1cqmwgzzfmez test]# ll
total 1204304
-rw-rw---- 1 mysql mysql         61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql      13153 Nov 14 11:49 l_log.frm
-rw-rw---- 1 mysql mysql   50331648 Nov 14 11:59 l_log.ibd
```

#####4、对于delete from table_name where xxx带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;

#####5、delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。
>optimize执行时会将表锁住，所以不要在高峰期使用。也不要经常使用，根据项目情况操作频率,安排在空闲时段进行

#####6、delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

<br /><hr>
##使用optimize table 优化案例
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;当你在数据库表中删除了大量的数据后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。如果已经删除了表的一大部分数据，或者如果已经对含有`可变长度`行的表（含有 `VARCHAR` 、 `BLOB` 或 `TEXT` 列的表）进行了很多更改，可以使用 `OPTIMIZE TABLE `命令来进行表优化。这个命令可以将表中的空间碎片进行整理合并，并且可以消除由于删除或者更新造成的空间浪费 。表优化的工作可以每周或者每月定期执行，对提高表的访问效率有一定的好处，但是需要注意的是，优化表期间会`锁定`表，所以一定要安排在空闲时段进行。

语法如下：
```sql
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
```

#####一，日志表数情况
1、查看表数据数量
```
MySQL [test]>  select count(*) as total from l_log;
+---------+
| total   |
+---------+
| 3279815 |  //300W+
+---------+
1 row in set (6.83 sec)
```

2、存放在硬盘中的表文件大小
```linux
[root@izbp10njz4i1cqmwgzzfmez test]# ll -h
-rw-rw---- 1 mysql mysql 8.9K Nov 14 10:35 l_log.frm  #表结构 8.9k
-rw-rw---- 1 mysql mysql 1.1G Nov 14 11:13 l_log.ibd  #表数据、索引文件占了1.1G
```

>Cardinality 是基数的意思，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。

#####二、删除一半表数据
```sql
#delete一半数据
MySQL [test]> delete from l_log where id > 1500000;
Query OK, 1779815 rows affected (4 min 34.83 sec)
```

```linux
#查看表文件大小，文件空间不变
[root@izbp10njz4i1cqmwgzzfmez test]# ll
-rw-rw---- 1 mysql mysql   61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql 8.9K Nov 14 10:35 l_log.frm
-rw-rw---- 1 mysql mysql 1.1G Nov 14 13:28 l_log.ibd
```

按常规思想来说，如果在数据库中删除了一半数据后，相对应的.ibd文件也应当变为之前的一半，但是目前删除一半数据后，.ibd文件大小不变。

对比Cardinality值，，这次索引查询和上次索引查询，里面的数据信息基本上是上次一次的`一半`，这结果还是合乎常理。

|Column_name  | Cardinality(删除前)  | Cardinality(删除后) |
| ------------ | ------------ | ------------ |
|id   | 3261502  |  1503819 |
|action       | 1436  | 667  |
|success_time |1087167| 751909  |
| run_time     |1630751  |751909   |

#####三，使用optimize table来优化一下

```sql
#删除数据后的优化  
MySQL [test]> optimize table l_log;
Query OK, 1500000 rows affected (38.67 sec)
Records: 1500000  Duplicates: 0  Warnings: 0
```

```linux
#查看一下.ibd文件的大小
[root@izbp10njz4i1cqmwgzzfmez test]# ll -h
-rw-rw---- 1 mysql mysql   61 Nov 14 10:16 db.opt
-rw-rw---- 1 mysql mysql 8.9K Nov 14 15:41 l_log.frm
-rw-rw---- 1 mysql mysql 468M Nov 14 15:41 l_log.ibd   #数据、索引文件差不多为优化前的一半
```

```
#查看一下索引信息 ,对比Cardinality值变化情况
MySQL [test]> show index from l_log;
```

#####四，结果
当你进行某些`删除`数据操作时，mysql并不会回收被已删除数据的占据的存储空间以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，如果没有数据来填补这个空缺，那就会造成机器资源浪费了。所以对于写比较频烦的表，要定期进行optimize，通常一个月一次，具体看项目实际情况而定了。

####五，手册中关于OPTIMIZE的一些用法和描述
- 如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有`VARCHAR`, `BLOB`或`TEXT`列的表）进行了很多更改，则应使用` OPTIMIZE TABLE`。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片

- 在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行

- OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用，尤其是`MyISAM`表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可

- 在OPTIMIZE TABLE运行过程中，MySQL会`锁定表`

- 默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示`「 Table does not support optimize, doing recreate + analyze instead」`的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

####那么怎么使用--skip-new呢？
查看mysql的进程
```linux
[root@izbp10njz4i1cqmwgzzfmez test]# ps aux | grep mysqld 
root      4030  0.0  0.1 113416  1612 pts/2    S    16:36   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/izbp10njz4i1cqmwgzzfmez.pid
mysql     4645 26.5 10.0 737264 101788 pts/2   Sl   16:36   0:19 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql   .....    --port=3306
root      4719  0.0  0.0 112816   976 pts/2    R+   16:37   0:00 grep --color=auto mysqld

```
>第一个是守护进程，第二个就是mysql的原始进程。我们只要在mysql原始进程最后追加--skip-new即可

先关闭mysqld，然后执行该命令
```linux
/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql   .....(补全)    --port=3306  --skip-new
```

来说两句吧