Understanding Why Unique Indexes Fail to Prevent Duplicate Data in MySQL

1. The Problem Scenario

Recently, while implementing a duplicate prevention mechanism for product groups, I created a dedicated table called product_group_unique.

The issue arose specifically with this product group uniqueness table. The table structure was defined as follows:

CREATE TABLE `item_group_uniqueness` (
  `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;

To ensure data uniqueness, I created a unique index on this table:

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

The combination of category ID, unit ID, and the hash value of product group attributes should uniquely identify a product group.

After creating the unique index, I checked the data the next day and discovered that duplicate records had been inserted into the table. The second and third records were duplicates.

Why did this happen?

2. NULL Values in Unique Indexed Fields

If you examine the data carefully, you'll notice a particular characteristic: the hash value of product group attributes (model_hash field) can be NULL, meaning product groups can exist without any configured attributes.

When inserting a duplicate record into the item_group_uniqueness table with model_hash equal to 100, MySQL's uniqueness constraint worked correctly and prevented the duplicate:

[Insert execution result showing duplicate rejection]

Next, we inserted two records with model_hash as NULL. The third record had the same category_id, unit_id, and model_hash values as the second record.

Surprisingly, this operation succeeded.

In other words, when NULL values appear in fields included in a unique index, the uniqueness constraint may not be enforced.

The final data insertion pattern was as follows:

1. When the model_hash field is not NULL, duplicate data is prevented.
2. When the model_hash field is NULL, duplicate data can be created.

Important note: Fields included in unique indexes should not allow NULL values, otherwise MySQL's uniqueness constraints may fail.

3. Adding Unique Indexes to Tables with Logical Deletion

While unique indexes are simple and effective, they can be challenging to implement in certain table structures, particularly those with logical deletion.

Typically, to delete a record from a table, you can use a DELETE statement:

delete from product where id=123;

This is a physical deletion, meaning the record is removed and cannot be retrieved through standard SQL queries (though recovery might be possible through other means).

Alternatively, there's logical deletion, which typically uses an UPDATE statement:

update product set is_deleted=1,last_modified=now(3) 
where id=123;

Logical deletion requires an additional status field in the table to indicate whether data has been deleted. All business queries must filter out records marked as deleted.

After such a deletion, the data remains in the table but is logically filtered out.

For tables with logical deletion, adding unique indexes becomes problematic.

Why? Suppose we've added a unique index on the name and model fields of a product table. If a user deletes a record (setting is_deleted to 1), then later tries to add a product with the same name and model, the unique index would prevent the insertion, even though the previous product was logically deleted.

This is clearly a significant issue.

One might suggest creating a unique index that includes name, model, and is_deleted fields together. While this would allow adding a product with the same name and model after one has been deleted, it creates a new problem: if the newly added product is then deleted, and the user tries to add the same product again, it would still fail.

This demonstrates that tables with logical deletion functionality make it difficult to create effective unique indexes.

However, if you must add a unique index to a table with logical deletion, several approaches are available:

3.1 Incremental Deletion Status

The fundamental issue with logical deletion and unique indexes is that when a record is deleted, the is_deleted field is set to 1 (assuming 0 is the default). When attempting to insert a record with the same values, the unique index prevents it because a record with is_deleted=1 already exists.

A different approach is to treat any value greater than 1 in the is_deleted field as a deleted record. Each deletion would increment the maximum is_deleted value for that record:

1. Add record A with is_deleted=0.
2. Delete record A with is_deleted=1.
3. Add record A with is_deleted=0.
4. Delete record A with is_deleted=2.
5. Add record A with is_deleted=0.
6. Delete record A with is_deleted=3.

Since each deletion uses a different is_deleted value, uniqueness is maintained.

Advantages: Simple and direct, requiring no field modifications.

Disadvantages: May require modifying existing SQL logic, especially queries that use is_deleted=1 to check deletion status, which would need to be changed to is_deleted>=1.

3.2 Adding a Timestamp Field

The core challenge with logical deletion and unique indexes lies in the deletion mechanism itself. We can address this by adding a dedicated field for handling logical deletion.

Create a unique index that includes name, model, is_deleted, and a new timestamp field.

When adding data, set the timestamp field to a default value of 1. For each logical deletion operation, automatically update this field with the current timestamp.

Even if the same record is logically deleted multiple times, each operation will generate a different timestamp, ensuring uniqueness.

Timestamps are typically precise to the second. In high-concurrency scenarios where multiple logical deletions of the same record might occur simultaneously, increasing precision to milliseconds may be necessary.

Advantages: Enables data uniqueness without modifying existing code logic by simply adding a new field.

Disadvantages: In extreme cases, concurrent operations might still produce duplicate timestamps, potentially leading to duplicate data.

3.3 Adding an ID Field

While adding a timestamp field generally solves the problem, extreme scenarios might still result in duplicate timestamps. A more robust solution is to add a primary key field specifically for deletion tracking: delete_id.

The approach is similar to adding a timestamp field. When adding data, set delete_id to a default value of 1. During logical deletion, set delete_id to the primary key ID of the current record.

Create a unique index that includes name, model, is_deleted, and delete_id.

This is likely the optimal solution, as it maintains data uniqueness without modifying existing deletion logic.

4. Adding Unique Indexes to Tables with Historical Duplicate Data

As discussed, tables with logical deletion functionality make it challenging to add unique indexes, but the three solutions presented in the previous section can successfully address this issue.

However, a critical question arises: if a table already contains historical duplicate data, how can we add a unique index?

The simplest approach is to create a separate and initialize it with the data:

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

While this works, our goal here is to add the unique index directly to the original table without using a separate uniqueness table.

The solution can leverage the approach from the previous section of adding an id field:

1. Add a delete_id field to the table.
2. Before creating the unique index, process the data to identify duplicates:

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

3. Set the delete_id field to 1 for one instance of each duplicate set.
4. For all other duplicate records, set delete_id to their respective primary key IDs.

Once all delete_id fields are properly set, you can create a unique index on name, model, is_deleted, and delete_id.

This approach effectively distinguishes historical duplicate records and allows for the creation of a unique index.

5. Adding Unique Indexes to Large Fields

Next, let's explore an interesting topic: how to add unique indexes to large fields.

Sometimes, we need to create a unique index across several fields, such as name, model, is_deleted, and delete_id. However, if the model field is very large, this unique index could consume significant storage space.

Unique indexes are used for data retrieval, and if index nodes contain large amounts of data, retrieval efficiency becomes poor.

Therefore, it's necessary to limit the length of unique indexes. Currently, MySQL's InnoDB storage engine allows a maximum index length of 3072 bytes, with a maximum of 1000 bytes for unique keys.

If fields are too large, exceeding the 1000-byte limit, adding a unique index becomes impossible. Are there any solutions?

5.1 Adding a Hash Field

We can add a hash field that generates a shorter value from the large field using a hash algorithm. This value can have a fixed length, such as 16 or 32 bits.

Instead of indexing the original large field, we create a unique index on name, hash, is_deleted, and delete_id.

This approach prevents the unique index from becoming too long.

However, this introduces a new problem: hash collisions, where different values produce the same hash result.

If other fields (like name) can distinguish between records and business requirements allow for such duplicates (which wouldn't be written to the database), this solution is viable.

5.2 Forgoing Unique Indexes

If adding a unique index proves too difficult, alternative methods can ensure uniqueness:

• If data entry points are limited (e.g., only through jobs or data imports), execute operations sequentially in a single thread to prevent duplicates.
• If multiple data entry points exist, route all operations through a message queue (MQ) and process them in a single thread in the MQ consumer.

5.3 Redis Distributed Locks

Since large fields make it difficult to add unique indexes in MySQL, why not use Redis distributed locks?

However, directly applying Redis locks to fields like name, model, is_deleted, and delete_id would be inefficient.

We can combine the approach from section 5.1: generate a hash value from name, model, is_deleted, and delete_id, and apply locks to this new value.

Even hash collisions become less problematic in concurrent scenarios, as they represent a low-probability event.

6. Batch Data Insertion

Some might argue that with Redis distributed locks, unique indexes become unnecessary. However, this perspective doesn't account for batch data insertion scenarios.

Consider a situation where a collection of data (list) needs to be inserted into the database after a query operation. Using Redis distributed locks would require the following approach:

for(Item item: list) {
   try {
        String hash = generateHash(item);
        rLock.lock(hash);
        // Query data
        // Insert data
    } catch (InterruptedException e) {
       log.error(e);
    } finally {
        rLock.unlock();
    }
}

This approach requires adding locks for each data item within a loop, resulting in poor performance.

Some might suggest using Redis' pipeline for batch operations—applying locks to 500 or 1000 items at once and releasing them together after use. However, this approach is impractical, as the required lock size would be enormous and极易 prone to timeout issues (e.g., the lock expires before business operations complete).

For batch operations like this, MySQL's unique indexes provide a more efficient solution. A single INSERT statement can handle the entire batch, with the database automatically detecting and rejecting duplicates while allowing unique records to be inserted.