Detailed explanation of the difference between ordinary indexes and unique indexes in MySQL

This article introduces the difference between ordinary indexes and unique indexes in MySQL. The explanation is very detailed. I hope it will be helpful to friends who are learning MySQL!

Detailed explanation of the difference between ordinary index and unique index in MySQL

1. The difference between query and update

There is no difference in query capabilities between these two types of indexes. The main consideration is the impact on update performance. It is recommended to choose ordinary indexes as much as possible.

(Free learning video tutorial recommendation: mysql video tutorial)

1.1 MySQL query operation

■ Ordinary index

After finding the first record that meets the condition, continue to traverse backward until the first record that does not meet the condition.

■ Unique Index

Since the index defines uniqueness, after finding the first record that meets the conditions, the search will be stopped directly.

Ordinary indexes will be retrieved one more time, with almost no impact. Because InnoDB data is read and written in units of data pages, when data needs to be read, the records are not read directly from the disk. Instead, the data pages are read into the memory first, and then retrieved from the data pages.

A data page defaults to 16 KB. For integer fields, a data page can hold nearly a thousand keys. Unless the data to be read is in the last record of the data page, another data page needs to be read. This situation is rare, and the CPU consumption is basically negligible.

Therefore, in terms of querying data, there is no difference between ordinary indexes and unique indexes.

1.2 MySQL update operation

The update operation does not directly update the data in the disk. It first reads the data page from the disk into the memory and then updates the data. Page.

■ Ordinary index

Read the data page from the disk into the memory and update the data page.

■ Unique Index

Read the data page from disk into memory, determine whether it is unique, and then update the data page.

Because there is a change buffer mechanism in MySQL, there will be a certain difference in the update of ordinary indexes and unique indexes.

The function of change buffer is to reduce IO operations and avoid excessive system load. The process of writing data to the data page in the change buffer is called merge.

If the data page that needs to be updated is in the memory, the data page will be updated directly; if the data is not in the memory, the update operation will be recorded in the change buffer first. When the data page is read next time, it will be merged into In the data page, the change buffer also has a regular merge strategy. Merge will also be triggered during the normal shutdown of the database.

For a unique index, you need to determine whether the data is unique before updating (it cannot be repeated with the data in the table). If the data page is in the memory, you can directly determine and update it. If it is not in the memory, you need to go to the disk. Read it out and check if it is unique. If so, update it. The change buffer is not used. Even if the data page is not in memory, it still needs to be read.

The change buffer uses the memory in the buffer pool, so it cannot be increased infinitely. The size of the change buffer can be dynamically set through the parameter innodb_change_buffer_max_size. When this parameter is set to 50, it means that the size of the change buffer can only occupy up to 50% of the buffer pool.

Conclusion: The change buffer cannot be used for unique indexes, only ordinary indexes can be used.

2. The difference between change buffer and redo log

2.1 Applicable scenarios of change buffer

change buffer The function is to reduce the frequency of update operations and cache update operations. This has a disadvantage, that is, updates are not timely. For tables with frequent read operations, it is not recommended to use change buffer.

Because the update operation was just recorded in the change buffer, the table was read, the data page was read into the memory, and the data was immediately merged into the data page. Not only will this not reduce performance consumption, but it will increase the cost of maintaining the change buffer.

Suitable for tables with more writes and less reads.

2.2 The difference between change buffer and redo log

We give an example to understand redo log and change buffer. We execute the following SQL statement:

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

Assume that (id1,k1) is in data page Page 1, (id2,k2) is in data page Page 2. And Page 1 is in memory, Page 2 is not.

The execution process is as follows:

Write (id1,k1) directly to Page 1;

Write down "Write (id2) to Page 2" in the change buffer ,k2)"This message;

Record the above two actions in the redo log.

After completing the above, the transaction can be completed. The cost of executing this update statement is very low, that is, writing to two memories and then writing to a disk (the two operations combined write one disk), and they are written sequentially.

This update statement involves four parts: memory, redo log (ib_log_fileX), data table space (t.ibd), and system table space (ibdata1).

如果要读数据的话，过程是怎样的？

mysql> select * from t where k in (k1, k2);

假设读操作在更新后不久，此时内存中还有 Page 1，没有 Page 2，那么读操作就和 redo log 以及 ibdata1 无关了。

从内存中获取到 Page 1 上的最新数据 (id1,k1)；

将数据页 Page 2 读入内存，执行merge 操作，此时内存中的 Page 2 也有最新数据(id2,k2)；

需要注意的是：

redo log中的数据，可能还没有 flush 到磁盘，磁盘中的 Page 1 和 Page 2 中并没有最新数据，但我们依然可以拿到最新数据（内存中的 Page 1 就是最新的，Page 2 虽然不是最新的，但是从磁盘读到内存中后，执行了merge操作，内存中的 Page 2 就是最新的了。）

如果此时 MySQL 异常宕机了，比如服务器异常掉电，change buffer 中的数据会不会丢？

change buffer 中的数据分为两部分，一部分是已经merge到ibdata1中的数据，这部分数据已经持久化，不会丢失。另一部分数据，还在 change buffer 中，没有merge 到ibdata1，分 3 种情况：

（1）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog中也没有fsync到磁盘，这部分数据会丢失；

（2）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog 已写入到磁盘，这部分不会多丢失，异常重启后会先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer；

（3）change buffer 写入数据到内存，redo log 和 binlog 都已经fsync，直接从redo log 恢复，不会丢失。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

更多MySQL相关教程，请关注PHP中文网！

The above is the detailed content of Detailed explanation of the difference between ordinary indexes and unique indexes in MySQL. For more information, please follow other related articles on the PHP Chinese website!