mysql truncate，drop，delete的区别

不同点:

1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.


3.delete语句不影响表所占用的extent
drop语句将表所占用的空间全部释放

truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage.

4.速度,一般来说: drop> truncate > delete


5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。

1.

delete释放表空间：

想通过optimize table来释放下空间：

mysql> optimize table pending_22;

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| np002.pending_22 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| np002.pending_22 | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (7.49 sec)

该表是innodb类型。

解决办法：

mysql> alter table pending_22 engine=innodb;
Query OK, 8299 rows affected (6.63 sec)
Records: 8299  Duplicates: 0  Warnings: 0

再观察下其表空间大小变成了27262976。

注意：优化的前提是独立表空间模式（参数innodb_file_per_table为ON)，否则没有任何效果的。

2.

truncate table tablename;

该命令可以清空一个表里的所有数据，并归1自增ID的值。

但myisam的表和innodb的表在使用上有一定的区别。

myisam表会清空所有数据，并释放表空间，即硬盘空间会得到释放。

innodb表也会清空所有数据，但不释放表空间。

Innodb数据库对于已经删除的数据只是标记为删除，并不真正释放所占用的磁盘空间，这就导致InnoDB数据库文件不断增长。如果想彻底释放这些已经删除的数据，需要把数据库导出，删除InnoDB数据库文件，然后再倒入。 下面是基本的步骤： 

1 使用mysqldump命令将InnoDB数据库导出 

2 停止MySQL 

3 删除所有InnoDB数据库文件和日志 

4 启动MySQL并自动重建InnoDB数据库文件和日志文件 

5 导入前面备份的数据库文件

 具体命令：

# 备份数据库：

mysqldump -uroot -proot --quick --force --all-databases > mysqldump.sql

# 停止数据库

service mysqld stop

# 删除这些大文件

rm /usr/local/mysql/var/ibdata1

rm /usr/local/mysql/var/ib_logfile*

# 手动删除除Mysql之外所有数据库文件夹，然后启动数据库

service mysqld start

# 还原数据

mysql -uroot -proot < mysqldump.sql