Make writing a habit together! This is the 12th day of my participation in the “Gold Digging Day New Plan · April More text Challenge”. Click here for more details.
Last article talked about InnoDB’s main B+ tree index, hash index and full text index, this article mainly introduces the B+ tree index in the index of the joint index and overwrite index
B+ tree index
Joint index
A joint index is an index of multiple columns in a table, as shown in figure
Mysql will continue matching to the right until it encounters a range query (>, <, between, like). A =x and b = y and c = z can be in any order (because of optimization)
Because the union index also sorts the second or NTH key value, it sometimes saves a sort.
Cover index
Records that can be queried from the secondary index without the need to query records in the clustered index. For example the count (1).
This benefit is due to the fact that the leaf nodes of the secondary index contain much less information than the clustered index, thus reducing IO operations.
So when not to select an index
Select * from table where key > 1000 select * from table where key > 1000
Although we can find the bookmarks by secondary index, in fact, the reading is not sequential, but discrete reading, so the cost becomes uncontrollable, so when a large proportion of the total access data (more than 20%), the clustering index is used as the key
Of course, there may be cases where the difference between discrete reads and sequential reads is not very large, such as solid-state drives. In this case, we can use the keyword FORCE INDEX to FORCE the use of an INDEX.