逻辑删除还是物理删除

大家好，又见面了，我是你们的朋友全栈君。

看到标题，有的童鞋心中暗想“数据删除有什么可提的呢？不就是执行个delete语句吗？有什么难的呀？”其实呢数据删除没有你想的这么简单，一般情况下公司会明确的要求数据只能逻辑删除，不能物理删除。那什么优势逻辑删除，什么又是物理删除呢？

物理删除

就是我们之前常用的DELETE、TRANCATE、DROP语句删除数据，不管你用哪种方式，都是将数据从硬盘上抹除， 这样数据表的体积就变小了，数据的读写性能就提上去了。
有个共识InnoDB引擎的数据表记录一旦超过2000万，它的读写性能就很差，我们用物理删除能缩小表的体积，提升读写性能，这挺好的，原则上也是这么一个道理，但是物理删除数据的代价你了解吗？

物理删除的代价一

由于物理删除是真的被删除的，当我们发现数据是被误删除的，那么恢复起来是非常困难的，恢复数据还要停掉数据库，对业务系统的影响是非常大的，所以误删除的后果是非常严重。

我有一个在携程网工作的DBA朋友，他说“如果那里的数据出了问题，停机维护成本还是挺大的，因为好多人都用携程app来买机票跟酒店” 他以前跟我说过一次，携程网停机一小时损失差不多是在一两百万的情形，如果业务系统停机还好说，因为业务系统都是分布式集群的部署的，前台系统和后台系统分别部署在不同的节点，挂掉几个对整体影响也不是很大。但是别看数据库也在搞集群，但是数据除了问题那么可是所有的MySQL节点要停机维护的，业务系统没有了数据库还运行个什么劲呢？所以数据库的运行可靠性是非常重要的，不能出现一丁点儿事儿的。

那么数据库误删除是怎么发生的呢？比如说，小王在程序里写了一个带有bug的delete语句，平时这个sql语句运行还算正常，但是传入特殊的数据导致bug运行，就会误删除很多的数据，这样的结果真的是非常严重。如果误删除了数据，有什么办法恢复数据吗？当然有了，如果误删除了数据，需要停掉数据库才行，对业务系统的影响是非常大的。

具体的执行时这样的：

平时我们数据库在执行SQL语句的时候是这样的，这些操作记录在binlog日志里面，恢复数据的时候，你们编写程序提取binlog日志里面的内容然后逐条分析，看看是那一条sql语句导致的误删除，导出binlog日志里面的sql语句，当然了这些sql语句里面是不包含误删除的那条sql语句，因为我们把它给抠掉了，接下来启动数据库，清空数据表里的所记录，然后把这些sql语句重新执行一遍，就相当于把以前的增删改查又执行了一遍，这样数据就恢复了。

除了这种方式外，还有一种恢复数据库的办法，那就是为MySQL配置延迟删除节点，也就是再弄一个MySQL数据库，让这两个数据库做延迟同步数据，当一个MySQL出现了误删除，因为有延迟同步，比如说延迟24小时数据再同步，也就是再24小时内，都可以从第二个MySQL节点来同步数据，然后恢复到第一个MySQL节点上面，这也是恢复删除的办法，像这些开脑洞的方法，在MySQL集群PXC方案都有

物理删除的代价二

会让数据的主键值变的不连续，不连续的主键值会让分页查询的速度变慢。我们看下面例子

正常的分页查询语句会在select语句后面加上Limit

SELECT ... FROM ... LIMIT 1000,20 ;

我想从1000条数据中取20条记录，这个语句就写成了limit 1000,20 ，那这条sql执行的时候，数据库可要从第一条计数，数到1000条的时候，开始往后查询20条数据，当然我现在要想查询10万条以后的数据， 数据库就得从头计数，从第一条数到10万条记录，然后再往后查询你想要的数据，越是数据越多，这个limit后面你所要查询的就越慢，所以我们要像一个办法去解决分页查询的问题。

于是我们想到了利用主键字段加速分页查询的办法，这个分页查询就写成了这个样子

select ... from ... where id>=1000 and id<=1020;

因为主键是自带索引的，索引就是对字段进行排序，那么查询的时候可以快速跳过很多的数据，直接定位到我们想要的数据，像英文词典，如果想要查找Object这个单词，直接就可以定位到O开头的单词去小范围的查找，这样就加快了查询的速度。如果英文单词的排序不是按照首字母排序，我查找Object的时候就得从第一页翻到最后一页，非常的耗时。
那我们再来看id>1000这个顺序，因为主键是按照顺序排序的，索引数据库可以很快的定位到id=1000这样的记录，查询的速度是非常快的，后续的查询也是做的这么个二叉树的查找，所以用主键去做分页查询的条件，个查询的速度比limit子句快的多。

但是现在有个问题，如果我们对数据做了物理删除，这个主键就变成不连续的了，也就是意味着利用主键来分页查找是不行的，因为从1000到1020这个主键范围之间可能只有几条数据而不是完整的20条记录。

这就是物理删除的第二个代价，没有办法用主键这种加速分页查询了。当然了，在一些必须要物理删除的情形下，还是有折中的加速分页查询的方法

什么样的数据不适合物理删除

核心业务表的数据不建议做物理删除，因为物理删除后，真的就只能停机恢复数据，这个代价真的非常大，反之我们可以对需要删除的记录做状态上的变更，而不是非要删除它们，这就是逻辑删除的原理

我们看下例子，给作废的订单设置为作废的状态，给注销的订单设置为注销的状态，给过期的优惠券设置为过期的状态，这都是可以的。
当然，有童鞋会说“如果表里存放了大量无效的数据，这个会占用表的空间，那么Innodb这种引擎，一张表的数据超过2000万性能就会下降的很快，那么用逻辑删除表是不是就会增长的很快呢？”，这种问题问的非常的好，这是童鞋是认真思考了，这个问题可以很优雅的解决，那就是再创建一张数据表历史表，这个表专门用来保存无效的数据，并且这个表还可以使用mongodb数据库，因为逻辑删除的数据重要性并不是那么高，主要保存下来即可。

最后来说下逻辑删除

• 其实刚才我们已经知道逻辑删除的原理了，我们可以给表添加一个字段（is_deleted），标记该字段已经逻辑删除，我们在查询数据的时候在where子句上加上is_deleted=0那么查询出的就是那些没有被物理删除的记录了

  select ... from ... where is_deleted=0;
  

• 核心数据表一定要采用逻辑删除，而不是物理删除
  像订单表、商品表都要采用逻辑删除。上面说把逻辑删除的记录都放到历史表，不就可以不用给数据表添加is_deleted字段了吗？
  不是多次一举，如果平时我们的数据库系统都是满负荷的工作，数据迁移这种工作我们可以放到后半夜去做，那样的数据库的负载比较低，我们给数据库设计了is_deleted字段后，标记了那些数据是要殴删除的，通过编写一个定时器程序，把要删除的数据转移到历史表，这样不是挺好的吗？也就是白天业务表还是要缓存一些逻辑删除的数据，等到后半夜我们再做系统的迁移。

我们给t_user表做逻辑删除

1. 创建历史表的sql语句：

   create table t_user_history LIKE t_user;
   

2. 添加一个is_delete字段