Normal and unique indexes

Whether to choose a normal index or a unique index needs to be considered from both query and update.

1. Query operations

The query on the index starts at the root node and searches through the layers to the leaf node, known as the data page, then uses binary bitwise records inside the data page.

  • For normal indexes, the first record that meets the condition is found, and then the next record is searched until the condition is not met.
  • For unique indexes, continued retrieval is stopped as soon as the first record that meets the criteria is found.

InnoDB data is read and written from disk in pages. Data pages default to 16K.

For normal indexes, the extra search and judgment of the next record is usually done in memory. The cost of one address and one calculation is small.

It can be considered that the performance difference between normal index and unique index query operations is negligible.

2. Update operations

change buffer

When you update a piece of data, you update it directly if it’s in memory.

If not, the update operation is cached in the Change buffer. When the query accesses the data page, it reads the data page into memory and then merges change Buffer.

happenmergeThe moment:

  • Access data page
  • The background on a regular basismerge
  • When the database is shut down

mergeProcess:

  • Reads data pages from disk
  • Of the data page appliedchange bufferRecord to get a new version of the data page
  • writeredo logThat contains thechange bufferChanges and data changes

Advantages:

  • Reduce disk read
  • Data reading takes up memorybuffer pool, so it can avoid occupationbuffer poolTo improve memory usage

Unique index cannot be usedchange buffer:

Unique indexes need to determine uniqueness constraints, so data pages need to be read into memory. It is faster to update memory directly than using change Buffer.

innodb_change_buffer_max_size

Change buffer uses memory in the buffer pool.

Size is controlled by the innodb_change_buffer_max_size parameter.

For example, if the value is set to 50, change buffer can occupy a maximum of 50% of the buffer pool.

Insert a new row

If it’s in memory

For unique indexes, uniqueness conflicts need to be determined; For normal indexes, you can insert directly. One more judgment has little effect on performance.

If the record is not in memory

For unique indexes, the data pages need to be read into memory to determine uniqueness.

For normal indexes, just record the updates in the Change Buffer.

change bufferUsage scenarios

Normal indexes are appropriate.

For services that write too much and read too little, the probability of reading data immediately after writing is small.

For frequently read services, reading the data immediately after the write is complete does not reduce the number of random I/O accesses and increases the maintenance cost of the Change buffer.

redo logandchange buffer

Redo log Saves I/O consumption for random disk writes.

The change buffer saves I/O consumption for random disk reads.