Farmers in yards world, beautiful application experience, from the programmer for the processing of detail, and self requirements state, agriculture in the yard a member of the young people is busy, every day, every week, can leave some footprints, is the creation of content, there is a persistent, is I don’t know why, if you lost, might as well to Chou Chou code track of farmers.

  • Beautiful musical beats take you through the coding process of this effect
  • Insist on every day, is the pursuit of every ideal youth
  • Follow in the footsteps of young people, and maybe your answer is right here
  • Take a look here if you’re confused

For example, in our user table, each user has an ID number. When the user registers or authenticates his identity information, the business code has verified the uniqueness of this ID number.

When the user information is often queried based on the user id number, the id number (ID_card) is generally indexed.

select username from tb_user where id_card=' '
Copy the code

Creating an index for the ID_card field can be considered a unique index or a normal index.

1 Analyze the fault from a query perspective

For example, run the following query statement:

select username from tb_user where id_card='740'
Copy the code

For a common index, the query procedure is as follows

Search the id_card index tree for the row whose ID_card is 740 and then retrieve the row from the table. Then search down again. If the condition is not met, the query is terminated.

If the index is unique, the following figure shows the query process

For a unique index, the index defines uniqueness, stopping the search after finding the first record that meets the condition.

In this query, the difference between the two options is just a “find and judge the next record” operation, just a pointer search and a calculation, and the performance cost difference is negligible for today’s CPU.

2 Analyze from the perspective of modifying data

InnoDB engine reads and writes data on a page basis. InnoDB engine reads data on a page basis from disk and then reads it into memory on a page basis. In InnoDB, the size of each data page is 16KB by default.

Therefore, when updating data, if data pages are directly updated in memory, otherwise, these update operations will be cached in the Change buffer, so data pages are not read from disk at first to reduce disk reads, and the execution speed of statements will be significantly improved.

The next time a query needs to access the data page, it reads the data page into memory and performs the operations associated with the page in change Buffer.

Merge The operations in the Change Buffer into the original data page to obtain the latest results. Background threads merge periodically. Of course, during a database shutdown, the merge operation is performed.

2.1 Update of unique index

The change buffer cannot be used for unique index updates.

Because of unique index, all update operations must first determine whether the operation violates the uniqueness constraint. For example, if you want to insert user data with ID_card 740, but the database already has the unique ID card 740, the operation cannot be carried out, and this process must be read into memory to determine the data page.

2.2 When inserting a new value

If user data whose ID_card is 740 is inserted, the target page to be inserted is in memory.

  • For unique indexes, find the position between 739 and 741, determine that there is no conflict, insert the value, and execute the statement

The end;

  • For normal indexes, find the position between 739 and 741, insert the value, and the statement completes.

The difference between the two is a conflict judgment and can be ignored in terms of performance.

When the target page to be inserted is not in memory

  • For unique indexes, the data page is read into memory, the value is inserted, and the statement is executed

Beam;

  • For normal indexes, updates are recorded in the Change Buffer, and statement execution ends.

Reading data from disk memory involves random I/O access, which is one of the most expensive operations in the database. Because change Buffer reduces random disk access, the update performance will be significantly improved.

3 Index Selection

How to choose between normal index and unique index. In fact, there is no difference between the two types of indexes in terms of query capability, but the main consideration is the impact on update performance.

In business development, change Buffer should be turned off if all updates are immediately followed by a query for the record, and otherwise.

The completion of

Not limited to thinking, not limited to language restrictions, is the highest realm of programming.

With xiaobian character, must be to record a set of video, and then upload

If you are interested, you can check out the watermelon video – early risers