Does this sql writing really invalidate the index

Preface

On the Internet, we can often see some articles summarizing various situations that can't hit the index in mysql, among which one statement means that the statements using or can't hit the index.

In fact, this statement is not correct enough. The correct conclusion should be that after mysql5.0, if there are independent indexes on the fields connected by or, the index can be hit, and the index_merge feature is used here.

Before mysql 5.0, only one index can be selected for one sql, and if or keyword is used in sql, the existing index will be invalid and will be scanned in full table. Because no matter which index you go, mysql can't find the qualified data once, so you can only abandon the index.

mysql is also constantly upgraded and updated, so after mysql version 5.0, the feature of index_merge index merging is added, which supports one sql to use multiple indexes.

The core idea of index_merge is to use a single index to find out the data that meet the requirements, and then merge these data into one return.
We can look at one 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, and 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 the field b, mysql thinks that it is less expensive to scan the full table because it can avoid the process of returning to the table.

Then let's try indexing the b field, and then execute the sql just now.

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 you can see that the value of 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, and mysql gives up the index here and scans the whole table. However, some articles say that this problem has been optimized after mysql5.7, that is, index_merge is also supported in interval query. My version is 5.6, so this optimization has not been verified yet, and those who are interested can verify it.

In fact, many things in mysql are not absolute, and the internal processing methods of the same sql different mysql versions may not be the same. At the same time, we can also see that mysql 1 is constantly being optimized and upgraded, and some old knowledge points will easily be no longer applicable.

I hope the article is helpful to you. Welcome to pay attention. A compliment 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 earlier, which may be helpful for you to understand this article.

Summarize