前段时间踩了一个坑：在mysql8中给一张innodb引擎表加了唯一索引，最后发现数据还是重复的。到底发生了什么？本文以踩坑的方式来谈谈唯一索引和一些有趣的知识点。1.还原问题站点前段时间，为了防止产品组出现重复数据，特地加了一个防重表。如果你对反重表比较感兴趣，可以看看我的另一篇文章《高并发下如何防重？》，里面有详细的介绍。问题出在商品组的反重表上。具体表结构如下：CREATETABLE`product_group_unique`(`id`bigintNOTNULL,`category_id`bigintNOTNULL,`unit_id`bigintNOTNULL,`model_hash`varchar(255)COLLATEutf8mb4_binDEFAULTNULL,`in_date`datetimeNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_bin;为了保证数据的唯一性，我为商品组的防重表建了唯一索引：altertableproduct_group_uniqueadduniqueindexux_category_unit_model(category_id,unit_id,model_hash);根据类别号、单元号和商品组属性的哈希值，可以唯一确定一个商品组。为商品组防重表创建唯一索引后，第二天查看数据，发现表中存在重复数据：表中第二条和第三条数据重复。为什么是这样？2、唯一索引字段包含null如果仔细看表中的数据，会发现一个特殊的地方：产品组属性的哈希值（model_hash字段）可能为null，即允许产品组不配置任何属性。向product_group_unique表中插入一条model_hash字段等于100的重复数据：执行结果：从上图可以看出，mysql的唯一性约束生效，重复数据被屏蔽。接下来我们再插入两条model_hash为null的数据，第三条数据与第二条数据中的category_id、unit_id、model_hash字段相同。从图中可以看出执行成功。也就是说，如果唯一索引的字段出现空值，则唯一约束不会生效。最终插入的数据如下：当model_hash字段不为空时，不会产生重复数据。当model_hash字段为空时，会产生重复数据。需要特别注意的是：创建唯一索引的字段不能为null，否则mysql的唯一性约束可能会失效。3.墓碑表加唯一索引我们都知道唯一索引非常简单易用，但有时，在表中添加并不容易。不信我们一起往下看。一般情况下，如果要删除表中的某条记录，如果使用delete语句对其进行操作。例如：从productwhereid=123中删除；这种删除操作是物理删除，即记录被删除后，通过SQL语句基本查不到。（不过可以通过其他技术手段找回，那是后话）还有一种墓碑，主要是通过update语句来操作的。例如：updateproductsetdelete_status=1,edit_time=now(3)whereid=123;逻辑删除需要在表中额外增加一个删除状态字段，记录数据是否被删除。在所有的业务查询中，都需要过滤掉被删除的数据。通过这种方式删除数据后，数据还在表中，只是对删除状态的数据进行了逻辑过滤。事实上，不可能给这个逻辑删除的表加上唯一索引。为什么？假设product表中的name和model已经添加了唯一索引，如果用户删除了一条记录，delete_status设置为1，后来用户发现不对，又添加了完全相同的产品。由于唯一索引的存在，用户第二次添加商品会失败。即使删除了商品，也无法再次添加。这个问题显然有点严重。可能有人会说：把name、model、delete_status这三个字段同时做成唯一索引不就行了吗？答：这样做确实可以解决用户逻辑删除某个商品后重新添加相同商品后无法添加的问题。但是如果第二次添加的item又被删除了。用户第三次添加相同的商品，没有问题吗？可见，如果表中有逻辑删除功能，不方便创建唯一索引。但是，如果您真的想为包含逻辑删除的表添加唯一索引怎么办？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。这样，数据操作流程就变成了：添加记录a，delete_status=0。删除记录a，delete_status=1。添加记录a，delete_status=0。删除记录a，delete_status=2。添加记录a，delete_status=0。删除记录a，delete_status=3。由于记录a，每次删除的delete_status都不一样，所以可以保证唯一性。这种方案的优点是非常简单直接，不需要调整字段。缺点是：可能需要修改sql逻辑，特别是一些查询sql语句，有的使用delete_status=1判断删除状态，需要改成delete_status>=1。3.2添加timestamp字段会导致表的逻辑删除。添加唯一索引并不容易。最基本的地方是墓碑。为什么我们不增加一个字段专门处理逻辑删除功能呢？答：可以添加时间戳字段。同时将name、model、delete_status、timeStamp这四个字段做成唯一索引。添加数据时，将默认值1写入timeStamp字段。然后，一旦有墓碑操作，时间戳就会自动写入这个字段。这样即使多次逻辑删除同一条记录，每次产生的时间戳都不一样，可以保证数据的唯一性。时间戳通常精确到秒。除非在那种极端的并发情况下，对同一条记录的两个不同的逻辑删除操作会产生相同的时间戳。这时候时间戳可以精确到毫秒。这种方案的好处是可以在不改变现有代码逻辑的情况下，通过增加新的字段来实现数据的唯一性。缺点是：在极端情况下，仍然可能产生重复数据。3.3添加id字段其实添加timestamp字段基本可以解决问题。但在极端情况下，仍然可能会产生重复数据。有没有解决的办法？答：添加主键字段：delete_id。这个方案的思路和加timestamp字段是一样的，就是在添加数据的时候给delete_id设置默认值1，然后在逻辑删除的时候把delete_id赋值给当前记录的主键id。同时将name、model、delete_status、delete_id这四个字段做成唯一索引。这可能是最优方案，不需要修改现有的删除逻辑，也能保证数据的唯一性。4、如何对重复的历史数据添加唯一索引？上面说了，如果表有逻辑删除功能，添加唯一索引并不容易，但是通过本文介绍的三种方案，可以顺利添加唯一索引。但是一个发自灵魂的问题：如果某个表中已经存在历史重复数据，如何添加索引呢？最简单的方法就是加一个反重表，然后把数据初始化进去。你可以这样写一个sql：insertintoproduct_unqiue(id,name,category_id,unit_id,model)selectmax(id),selectname,category_id,unit_id,modelfromproductgroupbyname,category_id,unit_id,model;是的，不过今天的话题是不加防重表，直接给原表加上唯一索引。那么，如何添加这个唯一索引呢？其实可以借鉴上一节添加id字段的思路。添加delete_id字段。但是，在为产品表创建唯一索引之前，必须先进行数据处理。获取同一记录的最大id：selectmax(id),selectname,category_id,unit_id,modelfromproductgroupbyname,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。但是如果模型字段很大，这会导致唯一索引，可能会占用更多的存储空间。唯一索引我们都知道，我们也会用到索引。如果索引的每个节点都存储大量数据，检索效率会很低。因此，有必要限制唯一索引的长度。目前mysqlinnodb存储引擎中索引允许的最大长度为3072字节，unqiuekey最大长度为1000字节。如果字段太大，超过1000字节，显然不可能加唯一索引。这个时候，有解决办法吗？5.1添加哈希字段我们可以添加一个哈希字段，取大字段的哈希值，生成一个更短的新值。该值可以通过某种哈希算法生成，固定长度为16位或32位等。我们只需要在name、hash、delete_status和delete_id字段上添加唯一索引即可。这样就可以避免唯一索引过长的问题。但是也会带来一个新的问题：一般的哈希算法会产生哈希冲突，即两个不同的值，哈希算法产生的值是相同的。当然，如果还有其他字段可以区分，比如：name，而且业务允许这种重复的数据，不写入数据库，这种方案也是可行的。5.2不加唯一索引如果实在难以添加唯一索引，则不添加唯一索引，通过其他技术手段保证唯一性。如果新增数据条目较少，比如只有job或者dataimport，可以单线程顺序执行，保证表中数据不重复。如果有很多新的数据条目，最后会发送MQ消息，并在MQ消费者中单线程处理。5.3Redis分布式锁由于字段太大，在MySQL中添加唯一索引并不容易。为什么不使用Redis分布式锁呢？但是如果直接加上name、model、delete_status和delete_id字段，加上redis分布式锁，显然是没有意义的，效率也不会高。我们可以结合5.1章节使用name、model、delete_status和delete_id字段生成hash值，然后锁定新值。即使有哈希冲突也没关系。在并发的情况下，毕竟是小概率事件。6.批量插入数据有些小伙伴可能会想，既然有了redis分布式锁，就不需要唯一索引了。那是你没有遇到过批量插入数据的场景。如果查询操作后，发现有一个集合：listdata，需要批量插入数据库。如果你使用redis分布式锁，你需要这样做：for(Productproduct:list){try{Stringhash=hash(product);rLock.lock（散列）；//查询数据//插入数据}catch(InterruptedExceptione){log.error(e);}最后{rLock.unlock();}}需要在循环中锁定每条数据。这样的表现肯定不会好。当然也有小伙伴有异议，说用redis管道做批处理不行吗？也就是一次性锁定500条或者1000条数据，使用完后一次性释放这些锁？想想就有点不靠谱，这锁有多大。很容易导致锁超时。比如业务代码还没有执行，锁过期时间到了。对于这种批量操作，如果此时使用mysql的唯一索引，直接批量插入即可，一条sql语句即可。数据库会自动判断，如果有重复的数据，就会报错。只有在没有重复数据的情况下才允许插入数据。
