Shulou(Shulou.com)06/01 Report--

What is the difference between a normal index and a unique index in MySQL? I believe there are many people do not understand, today Xiaobian in order to let you know more about the general index and unique index in MySQL, so to sum up the following content, let's look down.

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

I. the difference between query and update

There is no difference in query ability between these two types of indexes, and the main consideration is the impact on update performance. It is recommended to choose a general index as far as possible.

1.1 query operation of MySQL

■ General Index

After finding the first record that meets the condition, continue traversing back to the first record that does not meet the condition.

■ unique index

Because the index defines uniqueness, after finding the first record that meets the criteria, stop searching directly.

The normal index will be retrieved once more, with little impact. Because the data of InnoDB is read and written according to the data page, when you need to read the data, you don't read the record directly from the disk, but read the data page into memory first, and then retrieve it from the data page.

A data page defaults to 16 KB. For integer fields, a data page can put nearly a thousand key. Unless the data to be read is in the last record of the data page, you need to read another data page. This is rare, and the consumption of CPU can be ignored.

Therefore, in terms of querying data, there is no difference between a normal index and a unique index.

1.2 update operation of MySQL

The update operation is not to update the data on the disk directly, but to read the data page from the disk into memory first, and then update the data page.

■ General Index

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

■ unique index

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

Because there is a change buffer mechanism in MySQL, it will lead to some difference in update between normal index and unique index.

The function of change buffer is to reduce IO operation and avoid excessive system load. The process by which change buffer writes data to a data page is called merge.

If the data page that needs to be updated is in memory, the data page will be updated directly; if the data is not in memory, the update operation will be recorded in change buffer first, and the next time the data page is read, merge will be added to the data page. Change buffer also has a regular merge policy. Merge is also triggered during a normal database shutdown.

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 memory, you can directly judge and update it. If it is not in memory, you need to read it on disk. Determine whether it is unique, and update it if it is. Change buffer is not needed. Read the data page even if it is not in memory.

Change buffer uses memory in buffer pool, so it cannot be increased indefinitely. The size of the change buffer can be dynamically set by 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: unique index can not use change buffer, only ordinary index can be used.

II. The difference between change buffer and redo log

2.1 applicable scenarios for change buffer

The role of change buffer is to reduce the frequency of update operations and cache update operations. This has a disadvantage that it is not updated in time, and change buffer is not recommended for tables with frequent read operations.

Because as soon as the update operation is recorded in change buffer, the table is read, the data page is read into memory, and the data is immediately merge into the data page. This not only does not reduce performance consumption, but increases the cost of maintaining change buffer.

It is suitable for tables that write more and read less.

2.2 differences between change buffer and redo log

Let's 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)

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

The execution process is as follows:

Write directly to Page 1 (id1,k1)

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

Record the above two actions in redo log.

After doing all of the above, the transaction can be completed. The cost of executing this update statement is low: two places of memory are written, and then a disk is written (the two operations are combined to write the disk once), and it is written sequentially.

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

If you want to read the data, what is the process?

Mysql > select * from t where k in (K1, K2)

Assuming that shortly after the update, there is still Page 1 and no Page 2 in memory, then the read operation has nothing to do with redo log and ibdata1.

Get the latest data on Page 1 from memory (id1,k1)

Read the data page Page 2 into memory and perform the merge operation, and the Page 2 in memory also has the latest data (id2,k2)

It is important to note that:

The data in redo log may not have been flush to disk, and there is no up-to-date data in Page 1 and Page 2 on disk, but we can still get the latest data (Page 1 in memory is up-to-date. Page 2 is not up-to-date, but after reading from disk to memory, merge operation is performed and Page 2 in memory is the latest. )

If there is an abnormal downtime of MySQL at this time, such as an abnormal power outage of the server, will the data in change buffer be lost?

The data in change buffer is divided into two parts, one is the data that has been merge to ibdata1, this part of the data has been persisted and will not be lost. Another part of the data, which is still in change buffer, does not have merge to ibdata1. There are three types of data:

(1) change buffer writes data to memory, and redo log has also written (ib-log-filex), but no commit,binlog and no fsync to disk, this part of data will be lost

(2) change buffer writes data to memory, and redo log has also written (ib-log-filex), but not commit,binlog has been written to disk, so this part will not be lost. After abnormal restart, redo log will be recovered from binlog first, and then change buffer will be recovered from redo log.

(3) change buffer writes data to memory, and both redo log and binlog have been fsync. They are recovered directly from redo log and will not be lost.

Redo log mainly saves the IO consumption of random disk writes (converted to sequential writes), while change buffer mainly saves IO consumption of random disk reads.

These are the details of the difference between the ordinary index and the unique index in MySQL, and do you have anything to gain after reading it? If you want to know more about it, welcome to the industry information!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.