Young people do not speak martial virtue, useless index good, let you walk how many detours?

preface

Since the previous “explain | index to optimize the best sword, you can really use?” After that, this is the second article on indexing. The previous article focused on using the Explain execution plan to view index execution so that you can quickly locate which tables have index usage problems.

This article mainly introduces the common causes of index failure and how to use a good index, following a just link between the preceding and the following, to have a need for a friend reference.

This article will be explained from the following convenience:

1. Common causes of index failure:

2. Index failure common mistakes:

3. Several suggestions for index design:

The preparatory work

Check the current version of mysql:

select VERSION();
Copy the code

The current version is 8.0.21

Create a table test1

CREATETABLE`test1` (
  `id`bigintNOTNULL,
  `code`varchar(30) NOTNULL,
  `age`intNOTNULL,
  `name`varchar(30) NOTNULL,
  `height`intNOTNULL,
  PRIMARY KEY (`id`),
  KEY`idx_code_age_name` (`code`,`age`,`name`) USING BTREE,
  KEY`idx_height` (`height`) USING BTREE
) ENGINE=InnoDBDEFAULTCHARSET=utf8
Copy the code

Insert two pieces of data:

INSERTINTO ` test1 ` (` id ` ` code `, ` name `, ` age `, ` address `) VALUES (1, '001', 'zhang fei, 18,' 7 '); INSERTINTO ` test1 ` (` id ` ` code `, ` name `, ` age `, ` address `) VALUES (2, '002', 'guan yu, 19,' 8 ');Copy the code

Query:

select * from test1;
Copy the code

Results:

In addition, two indexes are established: idx_CODE_AGe_NAME (code, age, name) joint index and idx_height(height) plain index.

Common causes of index failure

1. The left-most prefix rule is not met

Case 1:

The field after the WHERE condition contains all the index fields of the union index, and the order is: code, age, name.

Execute SQL as follows:

Explain select * from test1 where code='001'and age=18 and name=' fai '; explain select * from test1 where code='001'and age=18 and name=' faI ';Copy the code

Results:

It can be seen from the red mark in the figure that the joint index idx_CODE_name_age has been used, and the length of the index is 188, 188 = 30 * 3 + 2 + 30 * 3 + 2 + 4. The index is fully used and the efficiency of the index is the best.

Some friends may ask: why is the index length calculated this way?

Answer: please refer to “explain | index to optimize the best sword, you can really use?” “, which gives a very detailed explanation.

Case 2:

The field after the WHERE condition contains all the index fields in the union index. The order is not code, age, or name.

Execute SQL as follows:

Explain select * from test1 where code='001'and name=' jf 'and age=18;Copy the code

Results:

From the figure above, you can see that the execution result is the same as in the first case.

Note: This is a special case, mysql will automatically optimize the order before the query.

Case 3:

The field after the WHERE condition contains the: code field in the federated index

Execute SQL as follows:

explain select * from test1 
    where code='001';
Copy the code

Results:

As can be seen from the figure above, the index length is changed to 92,92 = 30*3 + 2. Only one index field code is used, so the index is not fully used.

Case 4:

The field after the WHERE condition contains the: age field in the federated index

Execute SQL as follows:

explain select * from test1 
    where age=18;
Copy the code

Results:

This is a full table scan, with all indexes invalidated.

Case 5:

The field after the WHERE condition contains the: name field in the federated index

Execute SQL as follows:

Explain select * from test1 where name=' test1 '; explain select * from test1 where name=' test1 ';Copy the code

Results:

This is a full table scan, with all indexes invalidated.

Case 6:

The fields following the WHERE condition contain the code and age fields in the federated index

Execute SQL as follows:

explain select * from test1 
   where code='001'and age=18;
Copy the code

Results:

The index length is 96,96 = 30*3 + 2 + 4. Only two index fields code and age are used, and the index usage is not sufficient.

Case 7:

The fields following the WHERE condition contain the code and name fields in the federated index

Execute SQL as follows:

Explain select * from test1 where code='001'and name=' jf '; explain select * from test1 where code='001'and name=' jF ';Copy the code

Results:

The index length seen in the figure above is the same as in the first case, which is also 92. That is, only one index field code is used, and the age index is invalid.

Case 8:

The fields following the WHERE condition contain the: age and name fields in the federated index

Execute SQL as follows:

Explain select * from test1 where age=18 and name=' test1 ';Copy the code

Results:

As you can see from the figure above, there is a full table scan, with all indexes invalidated.

Summary:

Scenarios that meet the left-most prefix are code, code, age, code, age, and name.
The full index fields including: code, age, and name are special. These three fields can be sorted as any of the query conditions. Finally, mysql will optimize the order of the left-most prefix.
If a fault occurs in the middle, such as code or name, only the first index code is passed, and all indexes following the fault are invalid.
Other scenarios: age,name, age,name index will be invalid.

2. The range index column is not placed last

SQL > select age from ‘where’ where ‘age’;

EXPLAIN select * from test1 where code='001'and age>18 and name=' jf '; EXPLAIN select * from test1 where code='001'and age>18 and name=' jF ';Copy the code

Results:The index length is 96,96 = 30*3 + 2 + 4, and only two index fields (code and age) are used.

If the range query statement is placed at the end:

EXPLAIN select * from test1 where code='001'and name=' jf 'and age>18; EXPLAIN select * from test1 where code='001'and name=' jF 'and age>18;Copy the code

Results:

What the hell? How to index length or: 96?

This is a classic mistake

The last column in a range query is the order in which the joint index is created.

Change the order of the index fields name and age:

SQL > execute SQL

As you can see from the figure above, the index length becomes: 188, so the index is fully used.

Go back to the original SQL:

EXPLAIN select * from test1 where code='001'and age>18 and name=' jf '; EXPLAIN select * from test1 where code='001'and age>18 and name=' jF ';Copy the code

Results:

What?

Index length: 188.

Note: The range query comes last, referring to the range column in the federated index, not the range column in the WHERE condition. If the range column in the joint index is placed last, the index can be reached normally even if the range column in the WHERE condition is not placed last.

3. Select *

In fact, in the “Alibaba development manual” also explicitly stated that the use of SELECT * is prohibited, why?

EXPLAIN select * from test1
Copy the code

Results:

See the full table scan in the figure above.

So if the query is for an index column:

EXPLAIN select code,age,name from test1
Copy the code

Results:

As you can see from the figure, this case takes a full index scan, which is more efficient than a full table scan.

Overwrite index is used here.

If the columns of the select are all index columns, it is said to overwrite the index.

If the select column contains more than index columns, you need to go back to the table, that is, go back to the table and query for other columns, which is quite inefficient. Select * most likely need to query non-indexed columns, need to return to the table, so use less.

Of course, many of the examples in this article use SELECT *, mainly because I only have two pieces of data in my table. For the sake of demonstration, this method should not be used in normal business code.

4. There are calculations on index columns

Execute SQL as follows:

explain select * from test1 
where  height+1 =7;
Copy the code

Results:

As you can see from the figure above, it is a full table scan, so there is a calculation on the index column and the index will fail.

5. Use function on index column

If you add a function to an index column, the SQL is as follows:

Explain select * from test1 where SUBSTR(height,1,1)=8;Copy the code

Results:

As you can see from the above figure, it is a full table scan, so you can see that if you add a function to the index column, the index will also be invalidated.

6. Character types are not quoted

SQL:

explain select * from test1 
where name = 123;
Copy the code

Results:

Full table scan seen from the figure, index invalid.

Why did the index fail?

Some of you might be a little confused here.

Answer: The name field is a character type, and the one to the right of the equals sign is a number type. The type mismatch results in index loss.

Therefore, when using character type fields for judgment, it is important to use single quotes.

The problem of index loss caused by type mismatch is very easy to be ignored in our daily work, so we must pay enough attention to it

7. Use is null and is not null

The height field in the test1 table created earlier is non-null.

Query SQL as follows:

explain select * from test1 
where  height is null;
Copy the code

explain select * from test1 
where  height is not null;
Copy the code

The results are:From the above figure, we can see that all table scans are full and all indexes are invalid.

What if the height field is allowed to be null?

The first SQL execution result above:

See the ref type index in the figure above.

The second SQL execution result above:

See the range type index in the figure above.

summary

If the field is not allowed to be null, the index is invalid in both is null and IS not NULL cases.
If the field is allowed to be null, is NULL goes to the index of type ref and is not NULL goes to the index of type range.

8. Select * from ‘%’

There are three main types of like queries:

like ‘%a’
like ‘a%’
like ‘%a%’

Let’s look at the first case:

explain select * from test1 
where  code like '%001';
Copy the code

Results:

From the above figure, we can see that the full table scan, index failure.

Consider the second case:

explain select * from test1 
where  code like '001%';
Copy the code

Results:

From the figure above, you can see the index of the range type.

Finally, consider the third case:

explain select * from test1 
where  code like '%001%';
Copy the code

Results:

From the figure above we can see that the full table scan, index also failed.

We can see from these three results that a like statement can only be indexed if % is on the right.

What if there are scenarios where you just want to use the like statement % on the left?

Answer: Use an overwrite index

The specific SQL is as follows:

explain select code,age,name from test1 
where  code like '%001%';
Copy the code

Results:

The figure shows that the index full index scan performs better than the full index scan.

Of course, the best practice is to avoid the left side of the like statement % in SQL, and use ES instead of mysql to store data in such a business scenario.

Summary:

Like ‘%a’ index invalid
Like ‘a%’ to range index
Like ‘%a%’ index invalid

9. Careless use of the or keyword

Usage:

explain select * from test1 
where  height = 8 or height = 9;
Copy the code

Results:

From the figure above, you can see the index of the range type, isn’t that ok?

Alter SQL > alter SQL

explain select * from test1 
where  code = '001'or height = 8;
Copy the code

Results:

As you can see from the figure above, it became a full table scan and the index failed.

Let’s look it up separately:

explain select * from test1 
where  code = '001';
Copy the code

Results:

and

explain select * from test1 
where  height = 8;
Copy the code

Results:

In both cases, the ref type index is used, but the SQL index is invalidated with the OR keyword.

So what do we do if we want to use an OR scenario, but we want the index to be valid?

explain (select * from test1 where   code = '001') 
union (select * from test1 where  height = 8);
Copy the code

Yes, the union keyword is used, but the syntax is slightly different from the OR keyword, but the result of the query is the same.

The SQL execution result is as follows:

We see that the ref type index is gone.

The or keyword invalidates the index. Use union instead

Common errors of index failure

1. If not in is used, the index becomes invalid

Usage:

Explain select * from test1 where height not in (7,8);Copy the code

Results:

From the figure above, we can see that the index of range type is crossed, and it is not invalid.

Note that mysql5.7 and 5.8 have different effects. In 5.7, the result is a full table scan, while in 5.8, the range index is used.

2. The use of an unequal sign will invalidate the index

Usage:

explain select * from test1 where height! = 8;Copy the code

Results:See the index of the range type in the figure.

Note that mysql5.7 and 5.8 have different effects. In 5.7, the result is a full table scan, while in 5.8, the range index is used. What if you want to use indexes in 5.7? Answer: Use greater than and less than instead of not equal.

Here is a warm reminder, not equal to not just! =, and also <>.

3. The index is invalid because the order of index fields is not correct

SQL > select * from table where (select * from table where (select * from table where (select * from table where (select * from table where (select * from table where))));

EXPLAIN select * from test1 
where  code='001'order by age,name;
Copy the code

Results:

The figure above shows an index of type REF with a length of 92 and no additional information.

Order by order by

EXPLAIN select * from test1 
	where  code='001'order by name;
Copy the code

EXPLAIN select * from test1 
	where  code='001'order by name,age;
Copy the code

Results:

The index length is 92, but the additional information is: Using filesort, reordering by file.

The above two examples show that the use of indexes is related to the condition after WHERE, but not to the field after ORDER by.

Reorder by file (order by);

How to optimize Using filesort?

SQL > alter table select * from table where order by; select * from table where order by;

Iii. Suggestions for index design:

Unique indexes are preferred for quick location
Index commonly used query fields
Index sorted, grouped, and federated query fields
The number of indexes in a table cannot exceed 5
Table data volume is small, can not build index
Use small fields to build indexes
Index names with prefixes such as IDX_ or unx_, aspect lookups
Delete indexes that are not used, because they take up space

Iv. Eggs:

Note: Index failure in addition to the above common problems, the number of rows scanned by the mysql index exceeds 10% to 30% of the total table, the optimizer may not run the index, and automatically change to the full table scan.

Here’s a tip for avoiding pits:

Full time match my favorite, most left prefix to follow
The first brother cannot die, the middle brother cannot be broken
Less calculation on index column, all invalidation after range column
Write like to the right, overwrite index does not write *
Unequal null values and OR, index influence should be paid attention to;
Character field quotes cannot be lost, SQL optimization tricks.

One last word (attention, don’t fuck me for nothing)

If this article is of any help or inspiration to you, please scan the QR code and pay attention to it. Your support is the biggest motivation for me to keep writing.

Ask for a key three even: like, forward, look.

In the public account reply: interview, code artifact, development manual, time management have excellent fan welfare, in addition reply: add group, can communicate and learn with many BAT big factory seniors.

Reference: blog.csdn.net/sggtgfs/art…

Young people do not speak martial virtue, useless index good, let you walk how many detours?

preface

The preparatory work

Common causes of index failure

1. The left-most prefix rule is not met

2. The range index column is not placed last

3. Select *

4. There are calculations on index columns

5. Use function on index column

6. Character types are not quoted

7. Use is null and is not null

8. Select * from ‘%’

9. Careless use of the or keyword

Common errors of index failure

1. If not in is used, the index becomes invalid

2. The use of an unequal sign will invalidate the index

3. The index is invalid because the order of index fields is not correct

Iii. Suggestions for index design:

Iv. Eggs:

One last word (attention, don’t fuck me for nothing)

Related Posts

Python Scrapy crawler agent configuration and debugging

Talk about the OpenJDK JHSDB tool

Understanding the definition of JAVA generics and upper limits on type parameters