Hello, I’m Xiao Lin.

Yesterday posted an article about index failures: Who hasn’t touched index failures

I left an interesting question at the end of the article:

This thought question actually came from a reader in the comments section of my previous post “Thoughts caused by an SQL statement”.

Many readers shared their thoughts in the comments section, as well as a few private comments about my answer.

So, I’m going to talk to you tonight about that question.

Subject to a

Topic one is very simple, I believe everyone can analyze the answer, I shared yesterday index failure article also mentioned.

** select * from table 1 where id = primary key, name = secondary key, and all other fields are non-index.

Index_name = index_name; index_name = index_name; index_name = index_name;

Index failures occur in the third and fourth indexes, and the result of the execution plan type= ALL represents a full table scan.

Topic 2

The database table in question 2 is unique in that it has only two fields, one is the primary key index ID and the other is the secondary index name.

For the table in question 2, the first and second fuzzy query statements can also be scanned by index. The execution plan of the second query statement is as follows. The Using index statement in Extra uses an overwrite index:

Let’s look at the execution plan for the third query (same result for the fourth query) :

Key =index_name (Extra); key=index_name (Extra)

Why is that?

Select * from B+ tree (select id,name from B+ tree); select * from B+ tree (select name from B+ tree); So if you look at the B+ tree of the * secondary index, you can find all the results.

However, the type in the execution plan is index, which means that the data was queried by scanning the B+ tree of the secondary index, i.e. traversing the whole index tree.

In the execution plan of the first and second query statements, type is range, indicating the range query of the index column. That is to say, by taking advantage of the orderality of the index tree, data rows are quickly located through the way of query comparison.

Therefore, the query efficiency of type=range is higher than that of index.

Why scan a full secondary index tree instead of a full table (clustered index)?

Because the secondary index tree records very little, only “index column + primary key value”, whereas the clustered index records much more. For example, the leaf node in the clustered index records the primary key value, transaction ID, backflow pointer for transaction and MVCC, and all the remaining columns.

Plus, this SELECT * does not perform a table back operation.

Therefore, the MySQL optimizer believes that traversing the secondary index tree directly is much cheaper than traversing the clustered index tree, so MySQL chooses “full scan secondary index tree” to query data.

Why does the table add a non-index field to the table, and then run the same query, and then run a full table scan?

Select * from t_user where name like “%xx”; select * from t_user where name like “%xx”; To query the data cannot be only found in the secondary index tree, have to work back to the table operation is needed to complete the query, plus is the left fuzzy matching, unable to use the index tree order to quickly locate the data, so in the secondary index tree traversal, one by one for the primary key, and then to clustering index tree search to the corresponding data line, this is really too tired.

Therefore, the optimizer considers the above query process to be too expensive, so it chooses the full table scan mode to query the data.

As we know from this question, using left fuzzy matching (like “%xx”) does not necessarily lead to a full table scan. The key is to look at the fields in the table.

If the fields in the database table have only primary key + secondary index, then the full table scan (type= all) is not performed, but the full secondary index tree (type=index) is performed, even if left fuzzy matching is used.

SQL > alter table select index (‘ index ‘); SQL > alter table select index (‘ index ‘); SQL > alter table select index (‘ index ‘);

And with that, I’ll see you next time