逻辑删除和唯一索引

发表于 | 更新于: | 分类于 |

背景

现在基本上开发过程中都会使用到逻辑删除，但当逻辑删除与唯一索引一起存在时可能会导致一些问题。

问题起因

一天收到测试提的一个bug，然后对问题进行排查，发现是由于唯一索引冲突导致的无法插入数据造成的问题，当时很费解，因为该唯一索引字段已经在业务层做了唯一性校验理论上不应该出现索引冲突的问题啊！后来仔细一想发现应该是逻辑删除和唯一索引一起使用时导致的问题。下面开始对问题进行分析，为方便分析对表结构进行了简化。

问题分析

表结构

1
2
3
4
5
6
CREATE TABLE `test`.`user`  (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `is_deleted` tinyint(4) NULL COMMENT '逻辑删除',
  `username` varchar(255) NULL COMMENT '用户名（唯一）',
  PRIMARY KEY (`id`)
);

表索引

1
2
ALTER TABLE `test`.`user` 
ADD UNIQUE INDEX `uk_username`(`username`) USING BTREE COMMENT '用户名唯一索引';

说明：is_deleted字段为逻辑删除字段，0为未删除，1：为已删除

首先我们先插入一条数据：

1
INSERT INTO user ( id, is_deleted, username ) VALUES ( 1299617941335060482, 0, "hello" )

然后我们再将这条数据以逻辑删除的方式删掉：

1
UPDATE user SET is_deleted=1 WHERE id=1299617941335060482 AND is_deleted=0

当我们想再次插入username为”hello”的数据时就会发生唯一索引冲突：

1
INSERT INTO user ( id, is_deleted, username ) VALUES ( 1299619108853121025, 0, "hello" )

Duplicate entry ‘hello’ for key ‘uk_username’

此时数据库数据状态：

id is_deleted username
1299617941335060482 1 hello

原因相信大家都很清楚，这是由于逻辑删除并未真正的删除表中的数据，哪怕此时我们在业务层做了唯一性校验依然会出现这种问题。因为唯一性校验的SQL默认会拼接上逻辑索引字段，导致无法查出相应数据，最终引发了Duplicate entry 'hello' for key 'uk_username'

唯一校验SQL：

1
SELECT id,is_deleted,username FROM user WHERE is_deleted=0 AND (username = "hello")

如何处理？

初步解决方案

一开始想着既然是由于唯一索引冲突导致的，那么我们就把is_deleted和username建立成联合唯一索引不就好了吗？

1
2
3
ALTER TABLE `test`.`user` 
DROP INDEX `uk_username`,
ADD UNIQUE INDEX `uk_username_and_is_deleted`(`username`, `is_deleted`) USING BTREE COMMENT '用户名唯一索引';

此时数据库数据：

id is_deleted username
1299617941335060482 1 hello
1299621533785911298 0 hello

加上唯一索引之后测试完插入语句发现确实没问题了，由于联合索引的缘故不会再出现和已删除的数据冲突的情况，但此时删除数据又会出现问题：

1
UPDATE user SET is_deleted=1 WHERE id=1299621533785911298 AND is_deleted=0

当我们想删除id为1299621533785911298的数据时又会发生唯一索引冲突。

最终解决方案

  1. 去除唯一索引，业务层做完善校验（不推荐）

    原因：业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（阿里巴巴Java开发规范）

    除非现有表结构中已经积累了大量数据，导致做全库DDL（DDL语句会锁整张表，不要轻易在线上做DDL操作）及数据迁移困难，否则不推荐采取该方案。

    说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

  2. 建立联合索引，is_deteled字段不要用单纯的0和1来表示，可以用0来表示正常数据，逻辑删除数据可以有以下几种方案来表示（推荐）

    1. 将删除标识的正常设为固定值,删除值使用叠加值(-1,-2,-3)（该方案需保证字段值为全局递增或全局递减，分布式环境下实现复杂）

    2. 将删除标识的删除状态设为id（推荐，使用主键确保不会发生索引冲突，并且实现简单）

      若使用的是mybatis-plus框架可将logic-delete-value设置为如下：

      1
      logic-delete-value: id

    3. 将删除标识设为当前时间戳（时间戳在极端情况下依旧有索引冲突的风险）

      若使用的是mybatis-plus框架可将logic-delete-value设置为如下：

      1
      logic-delete-value: REPLACE(unix_timestamp(current_timestamp(3)),'.','')

    若采用该方案我们需对表结构进行一定的修改，因为原来的逻辑删除字段为tinyint(4)类型，长度不足以支撑该解决方案

    1
    2
    ALTER TABLE `test`.`user` 
    MODIFY COLUMN `is_deleted` bigint(20) NULL DEFAULT NULL COMMENT '逻辑删除' AFTER `id`;