前言

前段时间我踩过一个坑：在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 productgroup by name,category_id,unit_id,model;

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

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

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

减少一个delete_id字段。

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

获取雷同记录的最大id：

select max(id), select name,category_id,unit_id,model from productgroup 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语句就能搞定。

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