Will this SQL writing method really cause the index to fail?

Preface

There are often some articles on the Internet summarizing various situations in which indexes cannot be hit in MySQL. One of them is that statements using or cannot hit the index.

This statement is actually not correct. The correct conclusion should be that since MySQL 5.0, if there are independent indexes on the fields connected by or, the index can be hit. This is where the index_merge feature is used.

Before MySQL 5.0, a SQL statement can only use one index. If the or keyword is used in the SQL statement, the existing index will become invalid and a full table scan will be performed. Because no matter which index is used, MySQL cannot find the data that meets the conditions at one time, so it can only give up the index.

MySQL has also been continuously upgraded and updated, so after MySQL version 5.0, the index_merge index merging feature was added, which also supports the use of multiple indexes in one SQL.

The core idea of ​​index_merge is to first use a single index to find data that meets the requirements, and then merge the data together and return it.
We can look at an example.

Here we still use the table and test data created in the previous article. 10w 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 to see how it works.

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 believes that a full table scan is cheaper because it can avoid the table return process.

Then let's add an index to field b and then execute the SQL statement again.

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 indexes a and b, and the value of the type field is index_merge.

Next, let's look at another SQL statement 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 statement simply changes the equal sign to a greater than sign, which means that the returned result set is an interval set. MySQL gives up the index here and performs a full table scan. However, I have read in some articles that this problem has been optimized after MySQL version 5.7, that is, index_merge is also supported in interval queries. My version is 5.6, and I have not verified this optimization yet. If you are interested, you can verify it.

In fact, many things in MySQL are not absolute. Different MySQL versions may have different internal processing methods for the same SQL. At the same time, we can see that MySQL has been continuously optimized and upgraded, and some old knowledge points may easily no longer apply.

I hope the article is helpful to you. Welcome to follow and give me a like, which is the best support for me. Thank you.

In addition, regarding the underlying data structure of MySQL, you can refer to other articles I wrote before, which may help you understand this article.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.