Shulou(Shulou.com)06/01 Report--

Preface

I have read many similar articles before and mentioned that some improper use of sql statements will lead to the invalidation of MySQL indexes. There are also some MySQL "military regulations" or specifications that state that some sql cannot be written in this way, otherwise the index will become invalid.

The author agrees with most of the content, but in some examples, the author thinks that the use of words is too absolute, and does not explain the reason, many people do not know why. Therefore, the author absolutely reorganizes the common scenarios of index failure in MySQL, and analyzes the reasons for your reference.

Of course, remember, explain is a good habit!

Common scenarios of MySQL index failure

When verifying the following scenario, prepare a sufficient amount of data, because when the amount of data is small, MySQL's optimizer will sometimes decide that a full table scan is harmless and will not hit the index.

1. When or is included in the where statement, the index may fail.

Using or does not necessarily invalidate the index. You need to see if the query columns on the left and right sides of the or hit the same index.

Assume that the user_id column in the USER table has an index and the age column does not have an index.

The following statement actually hits the index (it is said to be a new version of MySQL, and if you are using an old version of MySQL, you can use explain to verify it).

Select * from `user` where user_id = 1 or user_id = 2

But this statement cannot hit the index.

Select * from `user` where user_id = 1 or age = 20

Assuming that the age column also has an index, it still cannot hit the index.

Select * from `user` where user_id = 1 or age = 20

Therefore, it is suggested that you should avoid using the or statement as much as possible, and try to use union all or in instead, and these two statements are more efficient than or.

2. Negative query is used in the index column in the where statement, which may cause the index to fail.

Negative queries include: NOT,! =,!,!, NOT IN, NOT LIKE and so on.

Some "military regulations" said that the use of negative query will certainly lead to index failure, the author checked some articles, some netizens refuted this point and proved it.

In fact, negative queries will not absolutely invalidate the index, which depends on the judgment of the MySQL optimizer, which is cheaper to scan the full table or walk the index.

3. The index field can be null, which may cause the index to fail when using is null or is not null

In fact, a single index field can hit the index when using is null or is not null, but netizens say that when two different index fields are connected with or, the index will fail. The author thinks that the index is indeed invalid, but this pot should be carried by or, which belongs to the first scenario.

Assume that the user_id column in the USER table has an index and allows the null,age column to have an index and allows null.

Select * from `user` where user_id is not null or age is not null

However, some "military regulations" and specifications emphasize that there is a reason to set the field to not null and provide default values.

The columns of null make index / index statistics / value comparisons more complex and more difficult for MySQL to optimize. Null, a type of MySQL, needs special processing internally to increase the complexity of database processing records; under the same conditions, when there are more empty fields in the table, the processing performance of the database will be much lower. The null value requires more storage emptiness, and the columns of the null in each row in the table or index need extra space to identify. When dealing with null, we can only use is null or is not null, but not =, in,

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.