MySQL中delete操作

在InnoDB中，delete操作并不会真的删除数据，mysql实际上只是给要删除的数据打了标记，标记为删除。磁盘所占空间不会变小，即表空间并没有真正被释放。

这样设计的思考

1. mysql的delete操作，只是做了逻辑上的标记删除，在磁盘上数据并没有被真正删除。
2. 这样的设计是因为：如果在磁盘上移除之后，很多其它的记录需要在磁盘上重新排列，这会消耗大量的性能。(例如：一个大表，存在索引，删除了其中一行，那么整个索引结构就会发生变化，随之而来的改变索引结构，必将带来磁盘IO)
3. 所有被删除的记录会组成一个垃圾链表，这个链表记录占用的空间叫可重用空间。新插入的记录可覆盖此空间。

---

一、复用表空间

被标记删除的行，是可以被复用的。下次有符合条件的记录可以直接插入到这个被标记的位置。

1. 行记录复用

比如在ID为300-600之间删除ID=500的记录，这条记录被标记为删除。下次如有ID=400的记录要插入，可以复用此ID=500被标记删除的位置。

2. 数据页复用

这种情况是指整个数据页都被标记为删除，所以整个数据页都可以被复用了。和行记录复用不同，数据页复用对要插入的数据几乎没有条件限制。

二、数据空洞

这些被标记为删除的记录，就是数据空洞。不仅浪费空间，还影响查询效率。

1. mysql底层是以数据页为单位来存储和读取数据的，每次向磁盘读一次数据就是读一个数据页，每访问一个数据页就对应一次IO操作，磁盘IO访问速度是很慢的。
2. 如果一个表上存在大量的数据空洞，原本只需要一个数据页就保存的数据，由于被很多空洞占用了空间。不得不增加其它数据页来保存数据，相应的mysql在查询相同数据的时候，就不得不增加磁盘IO操作，从而影响查询速度。
3. 不仅删除会造成数据空洞，插入和更新同样会造成数据空洞。因此一个表在经过大量频繁的增删改后，难免会产生数据空洞，影响查询效率。在生产环境中直接表现为原本查询很快的表变的越来越慢。

三、重建表

此命令的原理就是重建表。建立一个临时表B，然后把A(存在数据空洞的表)中的数据查询出来，全部重新插入到临时表B中。最后再用临时表B替换表A即可，这就是重建表的过程。

optimize table <table_name>;

1. 查询表占用空间的大小

use information_schema;

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='repeater' AND table_name='t_record';

2. alter重建表命令

推荐使用此命令重建表。

alter table <table_name> engine=InnoDB

3. alter与optimize的区别

alter table t engine=InnoDB (也就是recreate) , 而 optimize table t (等于是recreate + analyze)

四、Online DDL

ddl是dba的日常工作，执行ddl的时候会锁表。特别是执行ddl变更，导致库上大量的线程处于Waiting form meta data lock的状态。因此在MySQL5.5版本后引入了Online DDL

1. Online DDL推出以前

Online DDL推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。

相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。Online方式与前两种方式相比，不仅可以读，还可以支持写操作。

2. ALGORITHM与LOCK使用

执行Online DDL语句时，使用ALGORITHM与LOCK关键字，放在DDL语句最后，用逗号隔开即好

ALTER TABLE t_name ADD COLUMN username varchar(200) , ALGORITHM=INPLACE,LOKC=NONE;

3. ALGORITHM与LOCK

• ALGORITHM选项
  • INPLACE 替换 ：直接在原表上执行DDL操作
  • COPY 复制 : 使用一种临时表方式，克隆一个临时表，在临时表上执行DDL，然后在把数据导入临时表中，在重名名等。这期间需要多出一倍的磁盘空间来支持此操作。执行期间表不允许DML操作
  • DEFAULT 默认方式 : 由MYSQL自己选择，有先使用INPLACE方式
• LOCK选项
  • SHARE 共享锁 ：执行DDL的表可读，但不可写
  • NONE 没有任何限制 ：执行DDL的表可读可写
  • EXCLUSIVE 排它锁 ：执行DDL的表不可读，不可写
  • DEFAULT 默认方式 ：执行DDL的时不指定LOCK子句的时候使用的默认值。如果指定的值为DEFAULT，那就交给MYSQL子句去觉得锁表还是不锁表。不建议使用，如果你确定DDL语句不会锁表，可以不指定LOCK或指定它的值为DEFAULT，否则建议指定其它的类型。

执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。

OPTIMIZE TABLE 和 ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL，但依旧建议在业务访问量低的时候使用.

五、参考文档

mysql删除操作其实是假删除

MYSQL 表数据 delete 删除后，为何还占用存储空间？