Will this SQL writing really lead to index invalidation

Time:2021-4-17

preface

On the Internet, we often see some articles summarizing the various cases of failing to hit the index in mysql, one of which is that the statements using or cannot hit the index.

In fact, this statement is not correct enough. The correct conclusion should be,After mysql5.0, if there are independent indexes on the fields connected with or, the index can be hit. Here, index is used_ Merge feature.

Before MySQL 5.0, only one index can be selected for a single SQL, and if the or keyword is used in the SQL, the existing index will be invalid and full table scanning will be performed. Because no matter which index is used, MySQL can not find out the qualified data at one time, so it can only give up the index.

MySQL is also constantly updated, so after MySQL version 5.0, index is added_ Merge index merges this feature, which also supports multiple indexes for a single SQL.

index_ The core idea of merge is to use a single index to find out the data that meets the requirements, and then merge these data together to return.
Let’s take a look at an example.

The table and test data we created in the previous article are still used here. 10 W test data are inserted into the table. The table structure is as follows.


CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Let’s first add an index to the “a” field, and then execute a query statement with or.


mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra    |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE   | t   | ALL | a_index    | NULL | NULL  | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Because there is no index on field B, MySQL thinks that the cost of full table scanning is lower, because the process of returning to the table can be avoided.

Let’s try to index the B field, and then execute the SQL.


mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type    | possible_keys  | key       | key_len | ref | rows | Extra                   |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE   | t   | index_merge | a_index,b_index | a_index,b_index | 5,5   | NULL |  2 | Using union(a_index,b_index); Using where |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
1 row in set (0.00 sec)

This time, you can see that MySQL uses both a and B indexes, and that the value of the type field is index_ merge。

Next, let’s look at another SQL to see what the result is.


mysql> explain select a from t where a>100 or b>6000;
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys  | key | key_len | ref | rows  | Extra    |
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
| 1 | SIMPLE   | t   | ALL | a_index,b_index | NULL | NULL  | NULL | 100332 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

This SQL only changes the equal sign to the greater than sign, that is to say, the returned result set is an interval set. MySQL gives up the index here and scans the whole table. However, it is said that this problem has been optimized after MySQL version 5.7, that is, index is also supported in interval queries_ Merge, my version is 5.6, this optimization has not been verified yet, you can verify it if you are interested.

In fact, many things in MySQL are not absolute. For the same SQL, the internal processing methods of different versions of MySQL may be different. At the same time, we can also see that MySQL has been continuously optimizing and upgrading, and some old knowledge points are easily no longer applicable.

I hope the article is helpful to you, welcome to pay attention, like is the best support for me, thank you.

In addition, about the underlying data structure of MySQL, you can refer to other articles I wrote earlier, which may be helpful for you to understand this article.

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]