Several common writing methods leading to MySQL index invalidation

Time:2021-3-16

Recently, I have been busy dealing with some SQL optimization problems left over from old projects. Due to the problems in the original table design and field design, with the growth of business, there are a lot of slow SQL, which leads to the soaring CPU resources of MySQL. Based on this, I’d like to share with you some experiences that are easier to learn and use than before.

This time, let’s talk about how to prevent the invalidation of your index.

In my opinion, the most appropriate way to build an index is not to build an index according to my business experience There is no need to index when the frequency is very low. Just like some of our tables, there may be only about 10 pieces of data in 2 years, and the performance with index or without index is almost the same.

Indexing is just a way to optimize our business. We must build an index for the sake of building an index.

Here 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;
Copy code

`INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressHeight ‘) values (1,’ 2019-09-02 10:17:47 ‘,’bingfeng’,’22 ‘,’male’,’1 ‘,’binxian County, Xianyang City, Shaanxi Province’,’175 ‘);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (2, ‘2020-09-02 10:17:47’,’songzi ‘, 13,’ female ‘,’1’, null, ‘180’);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (3, ‘2020-09-02 10:17:48’,’broad bean ‘, 20,’ female ‘,’ 1 ‘, null,’ 180 ‘);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (4, ‘2020-09-02 10:17:47’,’bingfeng ‘, 20,’ male ‘,’17765010977’,’xi’an City, Shaanxi Province ‘,’155’);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (255, ‘2020-09-02 10:17:47’,’bamboo shoot ‘, 22,’ male ‘,’I can save several Chinese characters under test’, null, ‘180’);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (256, ‘2020-09-03’ 10:17:47 ‘,’bingfeng’, 21, ‘female’, ‘, null,’ 167 ‘);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (257, ‘2020-09-02’ 10:17:47 ‘,’xiaohong’, 20 ‘,’, ‘, null,’ 180 ‘);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (258, ‘2020-09-02’ 10:17:47 ‘,’xiaopeng’, 20 ‘,’, ‘, null,’ 188 ‘);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (259, ‘2020-09-02’ 10:17:47 ‘,’zhang San’, 20 ‘,’, ‘, null,’ 180 ‘);
INSERT INTO bingfeng.user(idcreate_timenameagesexmobileaddressheight)Values (260, ‘2020-09-02’ 10:17:47 ‘,’lisi’, 22 ‘,’, ‘, null,’ 165 ‘);
Copy code“

Single index

1. Using! = or < > causes index invalidation

`SELECT * FROM user WHERE Name ‘! =’ice peak’;
Copy code“

We set up an index for the name field, but if! = or < > this will lead to index failure and full table scanning, so we should use it carefully if there is a large amount of data

Through the analysis of SQL, we can see that the type is all, 10 rows of data are scanned, and the whole table is scanned. < > is the same result.

2. Index invalidation due to type inconsistency

Before talking about this, we must say that when designing table fields, we must, must and must maintain the consistency of field types. What do you mean? For example, the ID of the user table is int auto incremented to the user’s account table user_ The ID field must also be of type int. never write it as varchar or char.

`SELECT * FROM user` WHERE height= 175;
Copy code“

You must see clearly that the field type of the height table is varchar, but when I query, I use the number type, because there is an implicit type conversion in the middle, which will lead to index invalidation and full table scanning.

Now I understand why I must keep the consistency of type when designing fields. No, if you don’t guarantee the consistency, an int and a varchar can’t be indexed in the process of multi table joint query (eg: 1 =’1 ‘).

There are tens of millions of data in such a table, but you can’t change it. You may still feel the pain for a while.

Boys, remember, remember.

3. Index invalidation caused by function

`SELECT * FROM user` WHERE DATE(create_time) = ‘2020-09-03’;
Copy code“

If your index field uses an index, I’m sorry, it doesn’t use the index.

4. Index invalidation caused by operator

`SELECT * FROM user` WHERE age – 1 = 20;
Copy code“

If you add (+, *, /,!) to the columns, they will not be indexed.

Several common writing methods leading to MySQL index invalidation

5. Index invalidation caused by or

`SELECT * FROM user WHERE Name ‘=’zhang San’ or height =’175 ‘;
Copy code“

The index caused by or is in a specific situation. Not all or make the index invalid. If the or is connected to the same field, the index will not be invalid, otherwise, the index will be invalid.

Several common writing methods leading to MySQL index invalidation

6. Index invalidation caused by fuzzy search

`SELECT * FROM user WHERE Name ` like ‘% ice’;
Copy code“

I believe we all know that fuzzy search, if you also carry out fuzzy search prefix, then you will not go index.

7. Index invalidation caused by not in and not exists

`SELECT s.* FROM user s WHERE NOT EXISTS (SELECT * FROM user u WHERE u.name = s.name AND u.Name ‘=’ice peak’)
Copy code“

`SELECT * FROM user WHERE Name ` not in ‘;
Copy code“

These two usages will also invalidate the index. But not in is still indexed. Don’t misunderstand it as that all in are not indexed. I had misunderstandings before.

Compliance index

1. Leftmost matching principle

`EXPLAIN SELECT * FROM User ` where sex =’male ‘;
Copy code“

`EXPLAIN SELECT * FROM User ` Where ‘name =’bingfeng’ and ‘sex =’male’;
Copy code“

Before testing, delete other single column indexes.

What is called the leftmost matching principle? For a conforming index, the order of one index is compared from left to right. For example, in the second query, name goes through the index, and then goes back to find age. If there is no age in the result condition, then the following sex will not go through the index.

Several common writing methods leading to MySQL index invalidation

be careful:

`SELECT * FROM userWhere sex ='male 'and age = 22 andName ‘=’ice peak’;
Copy code“

Some brick movers may have a misunderstanding with me at the beginning. Our index order is clearly name, sex and age, and your current query order is sex, age and name, which definitely does not follow the index. If you have not tested it yourself and have this immature idea, you are still too young like me. In fact, it has nothing to do with the order, because the bottom layer of MySQL will help you Let’s do an optimization. It will optimize your SQL to the most efficient way. So don’t have this misunderstanding.

2. If! = is used, it will cause the following indexes to be invalid

`SELECT * FROM userWhere sex ='male 'andName ‘! =’ice peak’ and age = 22;
Copy code“

We use! =, because the name field is the leftmost field. According to the leftmost matching principle, if name does not follow the index, the following fields will not follow the index.

Several common writing methods leading to MySQL index invalidation

In fact, I think it is important for a conforming index to build an efficient index. I can’t say that if I use that field, I will create a separate index, which can be used globally. This is OK, but it is not in line with the index efficiency, so in order to become an advanced brick Porter, we still need to continue to learn how to create an efficient index.

Author: the growth of a programmer
Link:https://juejin.im/post/686927…
Source: Nuggets
The copyright belongs to the author. For commercial reprint, please contact the author for authorization. For non-commercial reprint, please indicate the source.

Recommended Today

[data analysis tool] basic SQL syntax

01. Write in front SQL is one of the necessary skills for data analysts. Whether it is a junior analyst or a senior analyst, SQL has become a necessary option in the recruitment conditions of major companies. Why is SQL so important for data analysts? Before answering this question, let’s understand the following questions. First […]