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

This article mainly explains "how to use the index correctly". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to use the index correctly.

This article will explain from the following convenience:

1. Common causes of index failure:

two。 Common misunderstandings of index failure:

3. Several suggestions for index design:

Preparatory work

View the current version of mysql:

Select VERSION ()

Find out that the current version is 8.0.21

Create a table test1

CREATE TABLE `test1` (`id` bigint NOT NULL, `code` varchar (30) NOT NULL, `age` int NOT NULL, `name` varchar (30) NOT NULL, `height` int NOT NULL, PRIMARY KEY (`id`), KEY `idx_code_age_ name` (`code`, `age`, `name`) USING BTREE, KEY `idx_ height` (`height`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert two pieces of data:

INSERT INTO `test1` (`id`, `code`, `name`, `age`, `address`) VALUES (1, '001', 'Zhang Fei', 18memery 7'); INSERT INTO `test1` (`id`, `code`, `name`, `age`, `address`) VALUES (2, '002', 'Guan Yu', 1914')

Check it out:

Select * from test1

Results:

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

one。 Common causes of index failure 1. The first case that does not meet the leftmost prefix principle:

The fields after the where condition contain all the index fields of the federated index, and the order is: code, age, name.

Execute the sql as follows:

Explain select * from test1 where code='001' and age=18 and name=' Zhang Fei'

Results:

From the red mark in the figure, we can see that the joint index idx_code_name_age has been taken, and the length of the index is 188,188 = 30 * 3 + 2 + 30 * 3 + 2 + 4. The index is fully used and the index is the most efficient.

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

A: please refer to "explain | this excellent sword of index optimization, can you really use it?" It gives a very detailed explanation.

The second case:

The fields after the where condition contain all the index fields of the federated index, in a different order: code, age, name.

Execute the sql as follows:

Explain select * from test1 where code='001' and name=' Zhang Fei 'and age=18

Results:

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

Note: this is a special case where mysql automatically optimizes the order before querying.

The third case:

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

Execute the sql as follows:

Explain select * from test1 where code='001'

Results:

From the figure above, we can see that the index is also gone, but the index length has changed to 92, 92 = 30. 3 + 2, only one index field, code, is used, and the index is not fully used.

The fourth case:

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

Execute the sql as follows:

Explain select * from test1 where age=18

Results:

From the image above, it becomes a full table scan, and all the indexes are invalid.

Case 5:

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

Execute the sql as follows:

Explain select * from test1 where name=' Zhang Fei'

The result: from the image above, it becomes a full table scan, and all the indexes are invalid.

The sixth case:

The field after the where condition contains the: code and age fields in the federated index

Execute the sql as follows:

Explain select * from test1 where code='001' and age=18

Results:

From the figure above, we can see that the index is still gone, but the index length becomes: 96, 96 = 30 percent 3 + 2 + 4, only two index fields, code and age, are used, and the index is not fully used.

The seventh case:

The field after the where condition contains the: code and name fields in the federated index

Execute the sql as follows:

Explain select * from test1 where code='001' and name=' Zhang Fei'

Results:

From the figure above, we can see that the index length is the same as in the first case, and the length is also 92. That is, only one index field, code, is used, and the index of the age field is invalid.

The eighth case:

The field after the where condition contains the: age and name fields in the federated index

Execute the sql as follows:

Explain select * from test1 where age=18 and name=' Zhang Fei'

Results:

As can be seen from the image above, it becomes a full table scan, and all the indexes are invalid.

Summary:

Code code 、 age code 、 age 、 name

Code age name

If there is a fault in the middle, such as code, name, only the first index code will be taken, and the index after the fault will be invalidated.

Age name age,name

two。 The range index column is not placed at the end

The field age after the where condition is greater than or equal to. The specific sql is as follows:

EXPLAIN select * from test1 where code='001' and age > 18 and name=' Zhang Fei'

Results:

From the figure above, you can see that the index length becomes: 96,96 = 303,2 + 4. Only two index fields, code and age, are used, while the index of the name field is invalid.

If the statement of the scope query is placed at the end:

EXPLAIN select * from test1 where code='001' and name=' Zhang Fei 'and age > 18

Results:

What the hell? What, the index length is still: 96?

This is a very classic mistake.

The range query finally refers to the order of the fields in which the federated index is created, which is now:

Adjust the order of the index fields name and age:

Then execute the above sql, and the result:

From the above figure, we can see that the index length has become: 188, and the index is fully used.

Go back and execute the sql you just started:

EXPLAIN select * from test1 where code='001' and age > 18 and name=' Zhang Fei'

Results:

What?

The index length is also 188.

Note: the last scope query refers to the last range column in the federated index, not the last range column in the where condition. If the range column in the federated index is placed last, even if the range column in the where condition is not placed last, it will go to the index normally.

3. Select is used *

In fact, it is also clearly stated in the "Alibaba Development Manual" that the use of select * is prohibited. Why?

EXPLAIN select * from test1

Results:

As can be seen from the picture above, we have taken a full table scan.

So if you are querying an index column:

EXPLAIN select code,age,name from test1

Results:

It can be seen from the figure that the full index scan is more efficient than the full table scan.

In fact, it is used here: overwrite index.

If all the columns in select are index columns, it is called an override index.

If the columns of select contain more than just 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 * High probability needs to query non-index columns and return to the table, so use less.

Of course, many examples in this article use select *, mainly because there are only two pieces of data in my table, which should be eliminated in normal business code for demonstration purposes.

4. There are calculations on the index column

Execute the sql as follows:

Explain select * from test1 where height+1 = 7

Result: from the figure above, you can see that it has become a full table scan, so it can be seen that there is a calculation on the index column, and the index will fail.

5. Functions are used on index columns

If you add a function to the index column, the specific sql is as follows:

Explain select * from test1 where SUBSTR (height,1,1) = 8

Result: as can be seen from the above figure, it has become a full table scan, thus it can be seen that if a function is added to the index column, the index will fail.

6. Character type without quotation marks

Don't talk nonsense and go directly to sql:

Explain select * from test1 where name = 123

Results:

It can be seen from the picture that the index is invalid after the full table scan.

Why does the index fail?

Some friends here may be a little confused.

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

Therefore, when using the character type field to make a judgment, be sure to add single quotation marks.

The problem of index loss caused by type mismatch is very easy to be ignored in our daily work, which must be paid enough attention to.

7. Using is null and is not null does not notice whether the field is allowed to be blank. The height field in the previously created test1 table is not empty.

The query sql is as follows:

Explain select * from test1 where height is null;explain select * from test1 where height is not null

The result is: from the above picture, we can see that all the tables are scanned and the indexes are invalid.

What if the height field is changed to allow empty?

The first sql execution result above:

From the figure above, you can see that the index of type ref is gone.

The result of the second sql execution above:

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

Summary

If the field is not allowed to be empty, the index will be invalid in both is null and is not null cases.

If the field is allowed to be empty, is null takes the index of type ref and is not null takes the index of type range.

% to the left of the 8.like query

There are three main situations for like queries:

Like'% a'

Like'a%'

Like'% a%'

Let's look at the first situation:

Explain select * from test1 where code like'1'

Results:

As can be seen from the picture above, the index is invalid after the full table scan.

Let's look at the second situation:

Explain select * from test1 where code like '001%'

Results:

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

Finally, take a look at the third situation:

Explain select * from test1 where code like'1%'

Results: as can be seen from the picture above, the index is also invalid after the full table scan.

From these three results, we can see that the like statement can walk the index only if% is on the right.

What if some scenarios are going to use the like statement% on the left?

Answer: use override index

The specific sql is as follows:

Explain select code,age,name from test1 where code like'1%'

Results:

As can be seen from the figure above, the full index scan of the index type has better performance than the full table scan.

Of course, the best practice is to avoid the like statement% on the left in sql, and if there is such a business scenario, you can use es instead of mysql to store data.

Summary:

The like'% a' index is invalid

Like'a% 'takes the range type index

The like'% a% 'index is invalid

9. The inadvertent usage when using the or keyword is as follows: explain select * from test1 where height = 8 or height = 9

Results:

It can be seen from the above picture that there is no problem with the index of range type.

Change the sql again:

Explain select * from test1 where code = '001' or height = 8

Results:

As can be seen from the image above, it has become a full table scan and the index is invalid.

We might as well check separately:

Explain select * from test1 where code = '001'

Results:

And

Explain select * from test1 where height = 8

Results:

Both individual queries take the index of type ref, but the index of sql becomes invalid after using the or keyword.

So what should we do if we are thinking about the scenario of using or and want the index to be valid?

Explain (select * from test1 where code = '001') union (select * from test1 where height = 8)

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

The above sql execution results are as follows:

We see the ref type index gone.

The or keyword invalidates the index and can be replaced by union

two。 Common misunderstandings of index failure 1. Using not in will cause the index to fail

The usage is as follows:

Explain select * from test1 where height not in (7 and 8)

Results:

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

It is important to note that the effect is different between mysql5.7 and 5.8. in this case, the result of sql is a full table scan, while the range type index is used in 5.8.The result is a full table scan.

two。 Using a non-equal sign will cause the index to fail.

The usage is as follows:

Explain select * from test1 where Heights 8

Results:

It can be seen from the figure that the index of type range is taken.

It is important to note that the effect is different between mysql5.7 and 5.8. in this case, the result of sql is a full table scan, while the range type index is used in 5.8.The result is a full table scan. What if you want to use an index in 5.7? Answer: use greater than and less than instead of not equal to.

Here is a warm reminder, not equal to the sign is not only! =, but also includes.

Improper order of 3.order by index fields leads to index invalidation

In sql, in addition to the fields after where, the fields behind order by can also be indexed.

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

Results:

You can see from the figure above that the index of type ref is 92 in length, and there is no additional information.

But if you change the conditions after order by to the following two sorts:

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

Results:

From the figure above, we can see that the index of type ref is still gone, and the index length is 92, but the additional information indicates: Using filesort, that is, reorder by file.

The above two examples show that the use of indexes is related to the conditions behind where, but has nothing to do with the fields after order by.

Whether you need to reorder by file or not is directly related to the fields after order by.

The question is, the extra information suggests: how to optimize Using filesort?

A: this is generally caused by the inconsistency between the order of the index fields in the federated index and the where condition and order by in sql. As long as the order is adjusted, this problem will not occur.

three。 Several suggestions for index design:

Give priority to the use of a unique index, which can quickly locate

Index common query fields

Index sorting, grouping, and federated query fields

The number of indexes in a table does not exceed 5.

The table has a small amount of data, so there is no need to build an index.

Try to use fields that take up less space to build an index

Naming indexes with prefixes such as idx_ or unx_, aspect lookup

Delete a useless index because it takes up a certain amount of space

Thank you for your reading, the above is the content of "how to use the index correctly". After the study of this article, I believe you have a deeper understanding of how to use the index correctly, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.