In the last article, Songo shared two rules for using indexes:
- Do not use function operations on indexes.
- Use overwrite indexes to avoid back tables.
Of course, everything has a degree, which strategy should also be combined with the specific project to determine, not to SQL optimization and abandoned the business.
Today, Songko on the basis of the previous article, and to share with you an index rule, together to learn.
We often say that like in MySQL should be used with caution, because full table scan, which is a terrible thing! However, it also depends on the situation, some like can actually use index: sometimes like with index efficiency is very high, sometimes like although use index efficiency is terrible.
Let’s analyze it.
1. Left-most matching principle
Let me give you an example. Suppose I have the following table:
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`),
KEY `username` (`username`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codeUsername and age form a compound index. The compound index is named username. The username indexes mentioned in the following refer to the compound index.
Based on the previous article (it’s time to check the index posture!) We know that for the following SQL:
select username,age from user2 where username='javaboy' and age=99;
Copy the codeThis SQL in the process of query, will use overwrite index, avoid back table, improve query efficiency.
If I want to query the user by username, do I need to create a separate index for username?
Let’s look at the following SQL:
select username,age from user2 where username='javaboy';
Copy the codeSince my table does not have an index for the USERNAME field, will it use the existing composite index? Let’s look at the execution plan:
Select * from Extra; select * from Extra; select * from Extra;
Why is that? In an index structure like B+Tree, records can be located using the left-most match of the index. The left-most match can either match the first few fields in the composite index or match the first few characters of the first field, in our case, the first field in the composite index.
Of course, we can also match the first few characters of the first field as follows:
select username,age from user2 where username like 'j%';
Copy the codeThe implementation plan is as follows:
The first thing you can confirm from this execution plan is that the query also uses the username compound index.
SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL >
- The previous type is ref, indicating that data is searched through the index. In general, when equivalent matching occurs, the type is ref. The latter type is range, indicating that this is a range scan of the index (because it is a fuzzy match, and fuzzy matches can form a scan range).
- The previous ref is const to indicate that the equivalence match with the indexed column is a constant.
- The front Extra is
Using indexIndicates that an overwrite index is used. The Extra on the back isUsing where; Using index, indicating that indexes are used, but filtering is required.
For the first point in bold font, LET me say a few more words with you. Why is it that fuzzy matching can form scan interval? If username is the same as age, the index is stored in order of username. If username is the same as age, the index is stored in order of age.
| username | age |
|---|---|
| a | 88 |
| b | 89 |
| c | 89 |
| c | 90 |
| c | 99 |
| d | 88 |
| d | 99 |
Therefore, when you want to search for username starting with J, you only need to locate the first username starting with j, and then use the bidirectional linked list between leaf nodes of B+Tree to continue reading backwards, until the first username not starting with j is read, which is the scanning interval.
Like ‘%j’, like ‘%j’, like ‘%j%’ Let’s look at an example:
Yi! Look at the execution plan seems to use the index! Can like use an index as long as the field has an index?
Of course not!
Let’s take a look at songo’s auxiliary case below, and you’ll understand.
2. Auxiliary cases
To give you a better understanding of the left-most matching, Songo will do another example.
The same table and data structure as above, but now if I want to query by age, the SQL is as follows:
select username from user2 where age=99;
select username from user2 where age>99;
Copy the codeThere is no difference between the two SQL execution plans:
How do we explain this query plan? There is no difference between the two query plans, so I’ll explain one.
Select * from B+Tree where username is the same as age. Select * from B+Tree where age is the same as username. Select * from B+Tree where age is the same as username. Username is in order and age is out of order.
| username | age |
|---|---|
| a | 88 |
| b | 89 |
| c | 89 |
| c | 90 |
| c | 99 |
| d | 88 |
| d | 99 |
Username is ordered and age is unordered.
So with that in mind, let’s look at the execution plan and make sense.
If age is unordered in the username index, only the username index can be traversed. If type is index, it means that all index records need to be scanned. For example, in the first SQL query, scan all index records and filter out the rows whose age is 99 (filtering is done at the server layer). Rows represents the estimated number of rows to be scanned, from the last Extra Using WHERE; You can see this with Using index, which uses indexes but also filters the data.
Like ‘%j’ and like ‘%j%’
SQL > select * from ‘username’ where ‘username’; select * from ‘username’ where ‘username’;
- If you don’t understand the overwrite index stamp here: it’s time to check if you’re using the index correctly! .
- What is a “back table” in MySQL? .
But if we query for more than one field in the index, for example:
select * from user2 where age=99;
Copy the codeQuery all fields, so there is no need to use the index, why? Let’s hear from Songo.
Mysql > alter table select * from user where address = 1; mysql > alter table select * from user where address = 1; mysql > alter table select * from user where address = 1; Select * from B+Tree; select * from B+Tree; select * from B+Tree; The primary key index has all the data you want, and the primary key index has all the data you want.
Consider this statement from Songo.
Above is our analysis, and now we look at the execution plan:
You can see, as we thought.
Type All is a familiar full table scan, and rows is the estimated number of rows to be scanned. Extra Using WHERE indicates that MySQL first reads records from the data table (storage engine), returns them to MySQL server layer, and then filters out records that do not meet the criteria at the server layer.
3. Summary
In MySQL, the left-most matching principle of the index is shared by songo, and we hope that you can understand the index storage structure.