MySQL Performance optimized coverage index

Because most of us use InnoDB, so this blog is mainly based on InnoDB

B + Tree (image from Network)

B + Tree graph from Network

1. The difference between a clustered index and a nonclustered index

Clustered index: Leaf node contains complete data (physical address continuous), called Clustered index

Nonclustered index (also known as secondary index): its leaf node does not contain all the data of the row Records, the leaf nodes in addition to the key values, the index row in each leaf node also contains a bookmark, which is used to tell the storage engine where the corresponding data rows can be found. It is necessary to refer to the primary index as the data domain, but the principle is that it cannot be found directly through the secondary index, it needs to find the primary key through the secondary index, and then finds its corresponding leaf node data according to the primary index. The process is (secondary index + primary key +columns value).

2. Paging needs optimization reasons

Example: Select a from table where b=1

① if the B field does not have an index, the database is scanned for full tables and all databases are scanned

② If the B field is indexed, the index needs to scan 3 blocks of data

⑴ get all b=1 primary keys and their rowid

⑵ then finds the data based on the ROWID.

⑶ If the data is not in the data block back to the table, if a is in the index then the table is not.

The reason for its slowness is that the secondary index needs to go back to the table to query according to the primary key.

3. Pagination Specific implementation

Example: Select Book_name,book_info from Libary limit 20000,10 (table primary key is its ID)

Overwrite index: Contains all indexes that satisfy the needs of the query becomes the overwrite index

That is (id,book_name,book_info) as a composite index, is an embodiment of the overlay index

　　

MySQL Performance optimized coverage index