Shulou(Shulou.com)05/31 Report--

Editor to share with you whether sql will lead to index failure, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Preface

On the Internet, we can often see some articles summarizing all kinds of situations that can not hit the index in mysql, among which there is a saying that statements using or can not hit the index.

This statement is actually not correct, the correct conclusion should be that after mysql5.0, if there is an independent index on all the fields connected by or, the index can be hit. Here is the index_merge feature.

Before the mysql5.0 version, a sql could only choose to use one index, and if the or keyword was used in sql, the existing index would be invalidated and a full table scan would be performed. Because no matter which index you take, mysql cannot find data that meets the criteria at once, so you have to abandon the index.

Mysql is also constantly upgrading and updating, so after the mysql5.0 version, the index_merge index merge feature has been added, thus supporting the use of multiple indexes in one sql.

The core idea of index_merge is to use a single index to find out the data that meets the requirements, and then merge the data together to return.

We can look at an example.

The table and test data we created in the previous article are still used here, with 10 w test data 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 with or.

Mysql > alter table t add index a_index (a); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > explain select a from t where a100 or bang 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 the table return process can be avoided.

So let's index the b field, too, 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: 0mysql > explain select a from t where a100 or bang 6000 +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | SIMPLE | t | index_merge | a_index B_index | axiom index | 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5 Using where | +-+- -- + 1 row in set (0.00 sec)

This time you can see that mysql uses both indexes an and b, and that the value of the type field is index_merge.

Next, let's take a look at another sql and see what the result is.

Mysql > explain select a from t where a > 100or b > 6000 +-- + | id | select_type | table | type | possible_keys | key | key_len | Ref | rows | Extra | +-+ | 1 | SIMPLE | t | ALL | a_index B_index | NULL | 100332 | Using where | +-+ 1 row in set (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 to scan the full table. However, it is said that this problem has been optimized after the mysql5.7 version, that is, index_merge is also supported in interval queries. My version is 5.6. this optimization has not been verified yet. If you are interested, you can verify it.

In fact, many things in mysql are not absolute, for the same sql different mysql versions of the internal treatment may be different, at the same time, you can also see that mysql has been constantly optimizing and upgrading, some old knowledge points will easily no longer apply.

The above is all the contents of the article "will sql cause index failure?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.