Will in use index in mysql?

There are two cases of whether in will use index in mysql: 1. When the value range of in is small, in will use the index; 2. When the value range of in is large, in will not use the index. , instead a full table scan will be used. Because when there are too many in conditions, a lot of data will be returned, which may cause memory overflow in the application heap and lead to index failure.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

Will in use index in mysql?

Conclusion: IN will definitely use index, but when the value range of IN is large, it will cause index failure and use full table scan

navicat visualization tool uses the explain function to view sql execution information

Scenario 1: When the value in IN has only one primary key

We only need to pay attention One of the most important type information is obviously whether the index is used:

The type result values ​​from best to worst are:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

all: Full table scan

index: Another form of full table scan, It’s just that his scanning method is in the order of the index

range: a ranged index scan. Compared with the full table scan of index, it has range restrictions, so it is better than index

ref : The search condition column uses an index and is not the primary key or unique. In fact, it means that although an index is used, the value of the index column is not unique and there are duplicates. In this way, even if the first piece of data is quickly found using the index, it still cannot stop and needs to scan a small range near the target value. But its advantage is that it does not need to scan the entire table, because the index is ordered, and even if there are duplicate values, it will be scanned in a very small range.

const: Normally, if a primary key is placed after where as a conditional query, the mysql optimizer can optimize the query and convert it into a constant. As for how and when to convert, this depends on the optimizer

Generally speaking, it is necessary to ensure that the query reaches at least the range level, preferably ref. When index and all appear in type, it means that the entire table is used The scan does not use the index and is inefficient. In this case, the SQL needs to be tuned.

When Extra appears Using filesor or Using temproary, it means that the index cannot be used and optimization must be done as soon as possible.

possible_keys: Index used by sql

key: Displays the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL

rows: Displays the number of rows that MySQL thinks it must examine when executing the query.

Scenario 2: Expand the value range in IN

The index is still used at this time, but the efficiency is reduced

Scenario 3 : Continue to expand the value range of IN

Looking at the picture above, we find that there is no index at this time, but a full table scan.

Let’s talk about the conclusion

Conclusion: IN will definitely use the index, but when the value range of IN is large, it will cause the index to fail and use the full table scan.

By the way: If not in is used, the index will not be used.

Recommended learning: mysql video tutorial

The above is the detailed content of Will in use index in mysql?. For more information, please follow other related articles on the PHP Chinese website!