prerequisite There is a data column that will not have duplicate data. Now you need to choose to create an index , How should we choose ?
1. General index
When searching , Return only when the first one does not meet the conditions .
On update , Find the location and update it directly
2. Unique index
When searching , The first qualified direct return is encountered .
On update , First find out if it is unique , Find the location and update it .
Let's go through the bottom analysis ：
Select：
*
We all know that an index is to create one B+ tree , If it's a normal index, we'll look it up now , When it finds the data that matches, it will do a second search , Because the data in the column is unique , So the second search must have no data , This time the search is done by dichotomy B+ The tree finds the leaf node of the binary tree , Each leaf node corresponds to a data block （ Data page ）, If the data page is in memory, it will not have to read the data on the disk or hard disk , Read directly in memory , We also know that a data block can store thousands of data , So it happens to be at the end of the data block at the time of the first search , In the second search, it should be done in the IO Read it
once IO Reading is inefficient . But if it's a unique index, it won't be read a second time , So in the worst case , The biggest difference between a normal index and a unique index is to do it more than once IO read , The probability is minimal .
update：
2. stay update perhaps insert The common index is to go to the B+ Find a place in the tree and follow it B+ Algorithm structure updating of tree
Or update its structure （ expand ： When creating an index, it will reduce the efficiency of updating and inserting , because B+ In some cases, it is inefficient to add nodes to the tree ）, Here it is innoDB If there is no such data block in memory, then in this case, it will not update it now , The update is stored in the ChangeBuffer upper , When searching for this data for the second time, read the data block and then execute the query ChangeBuffer
Data operations in ,meger
This data .（ stay redlog There will be one in it , It's also stored changeBuffer The data can only be operated by the user ,ES（Elaticsearch） One way to synchronize is through redo log
It's synchronous in real time ）, When the unique index is updated
It will first find out whether it is unique , Then do the same operation as normal index . So in line with the business logic and error free premise ： Unique index has one more lookup operation than normal index .（ In progress insert It will check according to the primary key
The data structure of the primary key index has changed ）
Through the above description ： Under the premise of meeting the business scenario, the only index is to use less and use more common index . And then there is change Buffer Use of Read less in update
Zoom in if necessary change buffer Storage capacity of because chanbuffer The larger the quantity is, the more changes will be made Reduced IO More books , The performance is improved .
Technology