乐趣区

关于java:面试官数据库加了唯一索引就不会有重复数据了吗

jiezi

前言

前段时间我踩过一个坑：在 mysql8 的一张 innodb 引擎的 表中，加了 惟一索引 ，但最初发现 数据 居然还是 反复 了。

到底怎么回事呢？

本文通过一次踩坑经验，聊聊惟一索引，一些有意思的知识点。

1. 还原问题现场

前段时间，为了避免商品组产生反复的数据，我专门加了一张 防重表。

问题就出在商品组的防重表上。

具体表构造如下：

CREATE TABLE `product_group_unique` (
  `id` bigint NOT NULL,
  `category_id` bigint NOT NULL,
  `unit_id` bigint NOT NULL,
  `model_hash` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `in_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

为了保证数据的 唯一性，我给那种商品组防重表，建了惟一索引：

alter table product_group_unique add unique index 
ux_category_unit_model(category_id,unit_id,model_hash);

依据分类编号、单位编号和商品组属性的 hash 值，能够惟一确定一个商品组。

给商品组防重表创立了 惟一索引 之后，第二天查看数据，发现该表中居然产生了反复的数据：

表中第二条数据和第三条数据反复了。

这是为什么呢？

2. 惟一索引字段蕴含 null

如果你认真查看表中的数据，会发现其中一个比拟非凡中央：商品组属性的 hash 值（model_hash 字段）可能为null，即商品组容许不配置任何属性。

在 product_group_unique 表中插入了一条 model_hash 字段等于 100 的反复数据：

执行后果：

从上图中看出，mysql 的唯一性束缚失效了，反复数据被拦挡了。

接下来，咱们再插入两条 model_hash 为 null 的数据，其中第三条数据跟第二条数据中 category_id、unit_id 和 model_hash 字段值都一样。

从图中看出，居然执行胜利了。

换句话说，如果惟一索引的字段中，呈现了 null 值，则唯一性束缚不会失效。

最终插入的数据状况是这样的：

  1. 当 model_hash 字段不为空时，不会产生反复的数据。
  2. 当 model_hash 字段为空时，会生成反复的数据。

咱们须要特地留神：创立惟一索引的字段，都不能允许为 null，否则 mysql 的唯一性束缚可能会生效。

3. 逻辑删除表加惟一索引

咱们都晓得惟一索引非常简单好用，但有时候，在表中它并不好加。

不信，咱们一起往下看。

通常状况下，要删除表的某条记录的话，如果用 delete 语句操作的话。

例如：

delete from product where id=123;

这种 delete 操作是 物理删除，即该记录被删除之后，后续通过 sql 语句根本查不进去。（不过通过其余技术手段能够找回，那是后话了）

还有另外一种是 逻辑删除 ，次要是通过update 语句操作的。

例如：

update product set delete_status=1,edit_time=now(3) 
where id=123;

逻辑删除须要在表中额定减少一个删除状态字段，用于记录数据是否被删除。在所有的业务查问的中央，都须要过滤掉曾经删除的数据。

通过这种形式删除数据之后，数据任然还在表中，只是从逻辑上过滤了删除状态的数据而已。

其实对于这种逻辑删除的表，是没法加惟一索引的。

为什么呢？

假如之前给商品表中的 name 和model加了惟一索引，如果用户把某条记录删除了，delete_status 设置成 1 了。起初，该用户发现不对，又从新增加了截然不同的商品。

因为惟一索引的存在，该用户第二次增加商品会失败，即便该商品曾经被删除了，也没法再增加了。

这个问题显然有点重大。

有人可能会说：把 name、model 和delete_status三个字段同时做成 惟一索引 不就行了？

答：这样做的确能够解决用户逻辑删除了某个商品，起初又从新增加雷同的商品时，增加不了的问题。但如果第二次增加的商品，又被删除了。该用户第三次增加雷同的商品，不也呈现问题了？

由此可见，如果表中有逻辑删除性能，是不不便创立惟一索引的。

但如果真的想给蕴含逻辑删除的表，减少惟一索引，该怎么办呢？

3.1 删除状态 +1

通过后面晓得，如果表中有逻辑删除性能，是不不便创立惟一索引的。

其根本原因是，记录被删除之后，delete_status 会被设置成 1，默认是 0。雷同的记录第二次删除的时候，delete_status 被设置成 1，但因为创立了惟一索引（把 name、model 和 delete_status 三个字段同时做成惟一索引），数据库中已存在 delete_status 为 1 的记录，所以这次会操作失败。

咱们为啥不换一种思考：不要纠结于 delete_status 为 1，示意删除，当 delete_status 为 1、2、3 等等，只有大于 1 都示意删除。

这样的话，每次删除都获取那条雷同记录的最大删除状态，而后加 1。

这样数据操作过程变成：

  1. 增加记录 a，delete_status=0。
  2. 删除记录 a，delete_status=1。
  3. 增加记录 a，delete_status=0。
  4. 删除记录 a，delete_status=2。
  5. 增加记录 a，delete_status=0。
  6. 删除记录 a，delete_status=3。

因为记录 a，每次删除时，delete_status 都不一样，所以能够保障唯一性。

该计划的长处是：不必调整字段，非常简单和间接。

毛病是：可能须要批改 sql 逻辑，特地是有些查问 sql 语句，有些应用 delete_status= 1 判断删除状态的，须要改成 delete_status>=1。

3.2 减少工夫戳字段

导致逻辑删除表，不好加惟一索引最基本的中央在逻辑删除那里。

咱们为什么不加个字段，专门解决逻辑删除的性能呢？

答：能够减少 工夫戳 字段。

把 name、model、delete_status 和 timeStamp，四个字段同时做成惟一索引

在增加数据时，timeStamp 字段写入默认值1。

而后一旦有逻辑删除操作，则主动往该字段写入工夫戳。

这样即便是同一条记录，逻辑删除屡次，每次生成的工夫戳也不一样，也能保证数据的唯一性。

工夫戳个别准确到 秒。

除非在那种极限并发的场景下，对同一条记录，两次不同的逻辑删除操作，产生了雷同的工夫戳。

这时能够将工夫戳准确到 毫秒。

该计划的长处是：能够在不扭转已有代码逻辑的根底上，通过减少新字段实现了数据的唯一性。

毛病是：在极限的状况下，可能还是会产生反复数据。

3.3 减少 id 字段

其实，减少工夫戳字段根本能够解决问题。但在在极限的状况下，可能还是会产生反复数据。

有没有方法解决这个问题呢？

答：减少 主键 字段：delete_id。

该计划的思路跟减少工夫戳字段统一，即在增加数据时给 delete_id 设置默认值 1，而后在逻辑删除时，给 delete_id 赋值成以后记录的主键 id。

把 name、model、delete_status 和 delete_id，四个字段同时做成惟一索引。

这可能是最优计划，无需批改已有删除逻辑，也能保证数据的唯一性。

4. 反复历史数据如何加惟一索引？

后面聊过如果表中有逻辑删除性能，不太好加惟一索引，但通过文中介绍的三种计划，能够顺利的加上惟一索引。

但来自灵魂的一问：如果某张表中，已存在 历史反复数据，该如何加索引呢？

最简略的做法是，减少一张 防重表，而后把数据初始化进去。

能够写一条相似这样的 sql：

insert into product_unqiue(id,name,category_id,unit_id,model) 
select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;

这样做能够是能够，但明天的主题是间接在原表中加惟一索引，不必防重表。

那么，这个惟一索引该怎么加呢？

其实能够借鉴上一节中，减少 id 字段的思路。

减少一个 delete_id 字段。

不过在给 product 表创立惟一索引之前，先要做数据处理。

获取雷同记录的最大 id：

select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;

而后将 delete_id 字段设置成 1。

而后将其余的雷同记录的 delete_id 字段，设置成以后的主键。

这样就能辨别历史的反复数据了。

当所有的 delete_id 字段都设置了值之后，就能给 name、model、delete_status 和 delete_id，四个字段加惟一索引了。

完满。

5. 给大字段加惟一索引

接下来，咱们聊一个乏味的话题：如何给大字段减少惟一索引。

有时候，咱们须要给几个字段同时加一个惟一索引，比方给 name、model、delete_status 和 delete_id 等。

但如果 model 字段很大，这样就会导致该惟一索引，可能会占用较多存储空间。

咱们都晓得惟一索引，也会走索引。

如果在索引的各个节点中存大数据，检索效率会非常低。

由此，有必要对惟一索引长度做限度。

目前 mysql innodb 存储引擎中索引容许的最大长度是 3072 bytes，其中 unqiue key 最大长度是 1000 bytes。

如果字段太大了，超过了 1000 bytes，显然是没法加惟一索引的。

此时，有没有解决办法呢？

5.1 减少 hash 字段

咱们能够减少一个 hash 字段，取大字段的 hash 值，生成一个较短的新值。该值能够通过一些 hash 算法生成，固定长度 16 位或者 32 位等。

咱们只须要给 name、hash、delete_status 和 delete_id 字段，减少惟一索引。

这样就能防止惟一索引太长的问题。

但它也会带来一个新问题：

个别 hash 算法会产生 hash 抵触，即两个不同的值，通过 hash 算法生成值雷同。

当然如果还有其余字段能够辨别，比方：name，并且业务上容许这种反复的数据，不写入数据库，该计划也是可行的。

5.2 不加惟一索引

如果切实不好加惟一索引，就不加惟一索引，通过其余技术手段保障唯一性。

如果新增数据的入口比拟少，比方只有 job，或者数据导入，能够单线程程序执行，这样就能保障表中的数据不反复。

如果新增数据的入口比拟多，最终都发 mq 音讯，在 mq 消费者中单线程解决。

5.3 redis 分布式锁

因为字段太大了，在 mysql 中不好加惟一索引，为什么不必 redis 分布式锁 呢？

但如果间接加给 name、model、delete_status 和 delete_id 字段，加redis 分布式锁，显然没啥意义，效率也不会高。

咱们能够联合 5.1 章节，用 name、model、delete_status 和 delete_id 字段，生成一个 hash 值，而后给这个新值加锁。

即便遇到 hash 抵触也没关系，在并发的状况下，毕竟是小概率事件。

6. 批量插入数据

有些小伙们，可能认为，既然有 redis 分布式锁了，就能够不必惟一索引了。

那是你没遇到，批量插入数据的场景。

如果通过查问操作之后，发现有一个汇合：list 的数据，须要批量插入数据库。

如果应用 redis 分布式锁，须要这样操作：

for(Product product: list) {
   try {String hash = hash(product);
        rLock.lock(hash);
        // 查问数据
        // 插入数据
    } catch (InterruptedException e) {log.error(e);
    } finally {rLock.unlock();
    }
}

须要在一个循环中，给每条数据都加锁。

这样性能必定不会好。

当然有些小伙伴持拥护意见，说应用 redis 的 pipeline 批量操作不就能够了？

也就是一次性给 500 条，或者 1000 条数据上锁，最初应用完一次性开释这些锁？

想想都有点不靠谱，这个锁得有多大呀。

极容易造成锁超时，比方业务代码都没有执行完，锁的过期工夫就曾经到了。

针对这种批量操作，如果此时应用 mysql 的惟一索引，间接批量 insert 即可，一条 sql 语句就能搞定。

数据库会主动判断，如果存在反复的数据，会报错。如果不存在反复数据，才容许插入数据。

退出移动版