Summary of some common writing methods that cause MySQL index failure

Preface

Recently, I have been busy dealing with some SQL optimization issues left over from the old project. Due to the problems in the original table design and field design, as the business grew, a large number of slow SQLs appeared, causing MySQL's CPU resources to soar. Based on this, I would like to briefly share with you these relatively practical and easy-to-learn and use experiences.

This time I will briefly talk about how to prevent your index from becoming invalid.

Before I go on, let me first share my views on indexes based on my recent experience. I don’t think all tables need to be indexed. For some business data, the volume may be large, and querying the data may be a bit stressful. In this case, the simplest and fastest way is to create a suitable index. However, for some businesses, there may not be much data in the table, or the table is not used very frequently, so there is no need to create an index. For example, some of our tables may only have about 10 pieces of data in 2 years, and the performance with and without indexes is almost the same.

Indexing is just a way for us to optimize our business. We should never build indexes just for the sake of building indexes.

Below is a table structure and some test data I used in this test

CREATE TABLE `user` (
 `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
 `create_time` datetime NOT NULL,
 `name` varchar(5) NOT NULL,
 `age` tinyint(2) unsigned zerofill NOT NULL,
 `sex` char(1) NOT NULL,
 `mobile` char(12) NOT NULL DEFAULT '',
 `address` char(120) DEFAULT NULL,
 `height` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_createtime` (`create_time`) USING BTREE,
 KEY `idx_name_age_sex` (`name`,`sex`,`age`) USING BTREE,
 KEY `idx_ height` (`height`) USING BTREE,
 KEY `idx_address` (`address`) USING BTREE,
 KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;

: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :

Single Index

1. Using != or <> will cause the index to fail

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

We have created an index for the name field, but if != or <> is used, the index will fail and a full table scan will be performed. Therefore, if the amount of data is large, use it with caution.

By analyzing the SQL, we can see that the type is ALL, 10 rows of data are scanned, and a full table scan is performed. <> also has the same result.

2. Index failure caused by inconsistent types

Before talking about this, I must say that when designing table fields, you must, must, must maintain the consistency of field types. What does that mean? For example, if the id in the user table is int and auto-incrementing, the user_id field in the user's account table must also be of int type. Do not write it as varchar, char, or anything like that.

SELECT * FROM `user` WHERE height= 175;

You must read this SQL carefully. The field type of the height table is varchar, but I used the numeric type when querying. Because there is an implicit type conversion in the middle, the index will become invalid and a full table scan will be performed.

Now you understand why I said that you must maintain type consistency when designing fields. If you do not ensure consistency, an int and a varchar will inevitably not be able to use the index when performing a multi-table joint query (eg: 1 = '1').

You may still feel the pain of encountering a table like this, which contains tens of millions of data and cannot be changed.

Young people, remember, 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 use the index.

4. Index failure caused by operators

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

If you perform (+, -, *, /, !) on the column, the index will not be used.

5. Index failure caused by OR

SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

OR causes indexing in specific circumstances. Not all ORs invalidate indexes. If OR connects the same field, the index will not be invalidated. Otherwise, the index will be invalidated.

6. Index failure caused by fuzzy search

SELECT * FROM `user` WHERE `name` LIKE '%冰';

I believe everyone understands this. If you perform a fuzzy search on the prefix, the index will not be used.

7. NOT IN and NOT EXISTS cause index failure

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

SELECT * FROM `user` WHERE `name` NOT IN ('Bingfeng');

These two uses will also invalidate the index. However, NOT IN still uses the index. Do not misunderstand that IN does not use the index at all. I had misunderstandings before (shame on you...).

8. IS NULL does not use the index, IS NOT NULL uses the index

SELECT * FROM `user` WHERE address IS NULL 

Do not follow the index.

SELECT * FROM `user` WHERE address IS NOT NULL; 

Walk the index.

Based on this situation, it is recommended that when designing a field, if there is no necessary requirement that it must be NULL, then it is best to give a default value of an empty string, which can solve a lot of subsequent troubles (deep experience <experience=lesson>).

Match index

1. Leftmost matching principle

EXPLAIN SELECT * FROM `user` WHERE sex = '男';

EXPLAIN SELECT * FROM `user` WHERE name = '冰峰' AND sex = '男';

Before testing, delete other single-column indexes.

What is the leftmost matching principle? It means that for a matching index, the order of an index is compared from left to right. For example, in the second query statement, name is searched through the index, and then age is searched. If age is not in the result condition, the subsequent sex will not be searched through the index.

Notice:

SELECT * FROM `user` WHERE sex = '男' AND age = 22 AND `name` = '冰峰';

Maybe some bricklayers 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 definitely does not follow the index. If you have not tested it yourself and have such an immature idea, then you are too young like me. It actually has nothing to do with the order, because the underlying layer of MySQL will help us optimize it. It will optimize your SQL to execute it in the most efficient way. So don't have this misunderstanding.

2. If != is used, all subsequent indexes will become invalid

SELECT * FROM `user` WHERE sex = '男' AND `name` != '冰峰' AND age = 22;

We used != in the name field. Since the name field is the leftmost field, according to the leftmost matching principle, if the name field is not indexed, the following fields will also not be indexed.

There are currently only two situations where matching indexes lead to index invalidation. In fact, I think the important thing for matching indexes is how to establish an efficient index. You must not say that if I use a field, I will create a separate index and use it globally. This is possible, but it does not meet the requirements of index efficiency. Therefore, in order to become an advanced bricklayer, we still need to continue learning how to create efficient indexes.

Summarize

This concludes this article on some common writing methods that cause MySQL index failure. For more information on common writing methods that cause MySQL index failure, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!