Should You Use Unique Indexes in MySQL? Performance Impact of Change Buffer

Concept Distinctions

Normal (non‑unique) indexes can contain duplicate values, while unique indexes enforce uniqueness just like primary keys. A table can have only one primary key, which cannot be null, but it may have multiple unique indexes, each allowing a single null value.

Typical usage: a student table may use student_id as the primary key and identity_number as a unique index; some organizations reverse this choice depending on business needs.

Example Query

For a citizen system where each person has a unique identity number, the lookup query is:

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

Creating an index on the id_card field is essential, but because the field is large, it is usually not made the primary key.

Choosing Between Unique and Normal Indexes

Two options exist for the id_card column:

Create a unique index.

Create a normal (non‑unique) index.

If the application logic already guarantees no duplicate identity numbers, both choices are logically correct. From a performance perspective, the difference is minimal for point queries because InnoDB reads data pages (default 16 KB) into memory and then searches within the page.

For a query such as: select id from T where k = 5; With a normal index, the engine finds the first matching record and continues scanning until it encounters a non‑matching key.

With a unique index, the engine stops after locating the first matching record.

The extra work for a normal index is negligible; the CPU cost is almost zero.

Update Process and Change Buffer

When an update requires modifying a data page, InnoDB behaves as follows:

If the page is already in the buffer pool, it is updated directly.

If the page is not in memory, the modification is recorded in the change buffer instead of reading the page from disk first. The change buffer is persisted to disk and later applied during a merge operation.

The change buffer is not merely an in‑memory cache; its contents are also written to disk.

During a merge, buffered changes are applied to the original data page, producing the latest version of the row.

When Change Buffer Is Used

Only non‑unique indexes can use the change buffer because updates to unique indexes must first verify the uniqueness constraint, which requires reading the target page into memory.

The size of the change buffer is configurable via the innodb_change_buffer_max_size parameter (e.g., a value of 50 means the buffer may occupy up to 50 % of the buffer pool).

Update Scenarios

Consider inserting a new record (4, 400) into a table:

If the target page is in memory, both unique and normal indexes insert the row directly; the performance difference is a tiny CPU check.

If the target page is not in memory, a normal index records the operation in the change buffer, while a unique index must first load the page to verify uniqueness.

Reading a page from disk is a costly random I/O operation; the change buffer reduces such reads, improving write performance, especially on mechanical drives.

Change Buffer vs. Redo Log

Both mechanisms aim to minimize random disk I/O:

The redo log turns random writes into sequential writes.

The change buffer reduces random reads by deferring page loads until they are actually needed.

Example insert statement: insert into t(id,k) values (id1,k1),(id2,k2); During this operation, InnoDB may:

Update an in‑memory page directly.

Record a change for a page not in memory in the change buffer.

Write both actions to the redo log.

Later reads will fetch pages from memory if available; otherwise, they load the page from disk and apply any pending change‑buffer entries before returning results.

Practical Index Selection

Because unique indexes cannot benefit from the change buffer, when the application already guarantees uniqueness, a normal index is generally preferred for better write performance.

Typical recommendations:

If every update is immediately followed by a query on the same row, consider disabling the change buffer.

For write‑heavy, read‑light workloads (e.g., billing or logging systems), enable the change buffer to gain noticeable speedups.

On tables stored on HDDs, enlarging the change buffer and using normal indexes can dramatically improve insert throughput for large historical datasets.

Conclusion

When business logic ensures no duplicate values, opting for a non‑unique index allows InnoDB to use the change‑buffer optimization, yielding better update performance without sacrificing query speed.