If the citizen system needs to check the name according to the ID number, then we will add an index in ID — card, then we need to consider whether to use a common index or a unique index?

Why not use the ID number as the primary key?

Since each index carries a primary key, the ID card occupies a large amount of resources, so we do not set the ID card as the primary key.

What is the performance difference between a unique index and a plain index?

select id from T where k=100
Copy the code

For normal indexes, after finding the first record that meets the condition (100,200), the next record is searched until the first record that does not meet the condition k=100 is encountered.

For unique indexes, since the index defines uniqueness, the search stops after the first record that meets the condition is found.

But the performance difference is small, because mysql loads a page of data from disk at a time in 16KB, and queries in memory are fast, so it doesn’t make much of a difference.

What does change buffer do?

When a data page needs to be updated, it is directly updated in memory. If the data page is not already in memory, InnoDB caches the update in the Change Buffer without affecting the consistency of the data, so that the data page does not need to be read from disk.

The next time a query needs to access the data page, the data page is read into memory and the change Buffer operations related to the page are performed. In this way, the correctness of the data logic can be guaranteed. It should be noted that despite its name, the change Buffer is actually persistent data. In other words, the change buffer is copied in memory and is also written to disk.

The process of applying the operations in the Change Buffer to the original data page to get the latest results is called merge. In addition to accessing the data page that triggers the merge, the system has background threads that merge periodically. The merge operation is also performed during the database shutdown process.

Why don’t unique indexes use change Buffer? For unique indexes, all update operations must first determine whether the operation violates the uniqueness constraint. For example, to insert (4,40), we need to determine whether the table already has a k=4 record, which must be read into memory.

If it is already read into memory, it is faster to update the memory directly, and there is no need to use change buffer. Therefore, the change Buffer cannot be used for updates to unique indexes, and in fact only normal indexes can be used.

Performance analysis of update statements using unique and plain indexes?

In the first case, the target page for which the record is to be updated is in memory.

InnoDB’s process is as follows: for a unique index, find the position between 3 and 5, determine that there is no conflict, insert the value, and the statement is finished. For normal indexes, find the position between 3 and 5, insert the value, and the statement completes. Thus, the difference between a normal index and a unique index in the performance of an update statement is just a judgment that costs a tiny amount of CPU time.

In the second case, the target page for which the record is to be updated is not in memory.

For unique indexes, the data page needs to be read into memory, and if there is no conflict, insert the value, and the statement is executed. For normal indexes, updates are recorded in the Change Buffer, and statement execution ends.

When is it appropriate to use change Buffer? When is it not appropriate?

For businesses that write too much and read too little, the probability of the page being accessed immediately after writing is relatively small. In this case, the use of change Buffer is the best. This business model is common in billing and logging systems.

On the other hand, if a business update pattern is that a query is made immediately after a write is written, then even if the condition is met, the update is recorded in the Change Buffer first, but then the merge process is triggered immediately because the data page is to be accessed immediately. This will not reduce the number of random ACCESS to IO, but increase the maintenance cost of change buffer. Therefore, change Buffer is a side effect of this business model.

Redo log vs. change buffer

The redo log and change Buffer (including disk persistence) mechanisms differ in that they optimize different phases of the change process. 1. Read the data page of the row to be changed from disk and read it to the memory page. 3. Write the changed data page to the disk. Step 1: Randomly read disk I/OS. Step 3: Randomly write DISK I/OS. Change buffer optimized step 1 to avoid random I/o Redo log. Optimized Step 3 to avoid random I/O write to disk. Optimized to sequential write to disk (write Redo log, Ensure that crash-safe) ——————————– in our mysql InnoDB, the change buffer mechanism is not always applied, only when the data page is not currently in memory, need to read disk load data page first. The change buffer comes in handy. The redo log mechanism is always used to ensure crash-safe. — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — – have used change buffer mechanism, the redo log this difference is that, with the change of buffer mechanism, the changes in the redo log record, The new change buffer item is recorded, not the physical page changes.