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

What are the reasons for the failure of MySQL index? I believe many inexperienced people do not know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Single index

1. Use! = or cause the index to fail

SELECT * FROM `user`WHERE `name`! = 'Bingfeng'

We have indexed the name field, but if! = or this will cause the index to fail and perform a full table scan, so if the amount of data is large, use it with caution

By analyzing the SQL, you can see that the type type is ALL, with 10 rows of data scanned and a full table scan. It's the same result.

2. Index invalidation caused by inconsistent types

Before you talk about this, be sure to say that when designing table fields, you must maintain the consistency of field types. What do you mean? For example, the id of the user table is self-increasing in int. When it comes to the user_id field of the user's account table, it must and must also be of the int type, and must not be written as a coquettish operation such as varchar or char.

SELECT * FROM `user` WHERE height= 175

You must have a clear look at this SQL. The height table field type is varchar, but I used the numeric type when I queried, because there is an implicit type conversion in the middle, so it will cause the index to fail and do a full table scan.

Now I can see why I said that when designing fields, we must maintain type consistency. No, if you don't guarantee consistency, an int and a varchar will not be able to index in a multi-table federated query (eg: 1 ='1').

When you encounter such a watch, there are tens of millions of data in it, but you can't change it. You may still experience that kind of pain for the time being.

Boys, remember.

3. Index failure caused by function

SELECT * FROM `user`WHERE DATE (create_time) = '2020-09-03'

If your index field uses an index, I'm sorry, it really doesn't index.

4. Index invalidation caused by operator

SELECT * FROM `user` WHERE age-1 = 20

If you do (+, -, *, /,!) on the column, then none of them will be indexed.

5. Index failure caused by OR

SELECT * FROM `user`WHERE `name` = 'Zhang San' OR height = '175'

OR causes the index to be in certain circumstances, and not all OR invalidates the index. If the OR joins the same field, then the index will not fail, otherwise the index will fail.

6. Index failure caused by fuzzy search

SELECT * FROM `user`WHERE `name`LIKE'% Ice'

I believe we all understand that fuzzy search if you also prefix fuzzy search, then will not go to the index.

7. NOT IN and NOT EXISTS cause index invalidation

SELECT s.* FROM `user`s WHERE NOT EXISTS (SELECT * FROM `user`u WHERE u.name = s.`name`AND u.`name` = 'Bingfeng') SELECT * FROM `user`WHERE `name`NOT IN ('Bingfeng')

These two uses will also invalidate the index. But NOT IN still takes the index, do not misunderstand that all IN does not go to the index. I had a misunderstanding before.

8. IS NULL does not go to the index, IS NOT NULL does not go to the index

SELECT * FROM `user` WHERE address IS NULL

Do not take the index.

SELECT * FROM `user` WHERE address IS NOT NULL

Take the index.

According to this situation, it is recommended that when designing the field, if there is no necessary requirement to be NULL, then it is best to give a default empty string, which can solve a lot of subsequent troubles (have a profound experience).

Conform to the index

1. Leftmost matching principle

EXPLAIN SELECT * FROM `user`WHERE sex = 'male'; EXPLAIN SELECT * FROM `user`WHERE name = 'Bingfeng' AND sex = 'male'

Delete other single-column indexes before testing.

What is the leftmost matching principle, that is, for compliance with the index, the order of one of its indexes is compared from left to right, such as the second query statement, name walks the index, and then goes back to find age, and then the sex that does not have age in the result condition will not leave the index.

Note:

SELECT * FROM `user`WHERE sex = 'male' AND age = 22 AND `name` = 'Bingfeng'

Maybe some brick movers may have a misunderstanding with me at the beginning. Our index order is obviously name, sex, age, and your current query order is sex, age, name. This certainly does not go through the index. If you have not tested it yourself, you also have this kind of immature idea, then, like me, it is still too young, it actually has nothing to do with the order, because the bottom of mysql will help us do an optimization. It optimizes your SQL to perform as efficiently as it thinks possible. Therefore, there must be no such misunderstanding.

2. If! = is used, it will cause all subsequent indexes to fail.

SELECT * FROM `user` WHERE sex = 'male' AND `name`! = 'Bingfeng' AND age = 22

We used! = in the name field, because the name field is the leftmost field, according to the leftmost matching principle, if name does not index, the following fields will not index.

There are only two things that can be said about the failure of the index caused by conforming to the index. in fact, I think the important thing for the compliant index is how to build an efficient index. I must not say that if I use that field, I will set up a separate index. It can be used globally. This is OK, but it does not meet the efficiency of the index, so in order to become a senior bricklayer, we have to continue to learn how to create an efficient index.

After reading the above, do you know the reasons for the failure of the MySQL index? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.