MySQL joint index effective conditions and index invalid conditions

1. Conditions for joint index failure

A joint index is also called a composite index. An index on two or more columns is called a composite index.

For composite indexes: MySQL uses the fields in the index from left to right. A query can use only part of the index, but only the leftmost part. For example, the index is key index (a,b,c). It can support the search of three combinations: a | a,b | a,b,c, but does not support the search of b,c. When the leftmost field is a constant reference, the index is very effective.

The additional columns in the index allow you to narrow the scope of your search, but using one index with two columns is different from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name consists of a last name and a first name. The phone book is sorted first by last name pairs, and then by first name pairs for people with the same last name. A phone book is very useful if you know the last name, even more useful if you know both the first and last name, but useless if you know only the first name.

So when creating a composite index, you should carefully consider the order of the columns. Composite indexes are useful when you perform searches on all columns in the index or on just the first few columns; they are not useful when you perform searches on just any of the later columns.

For example: create a composite index of name, age, and gender.

    create table myTest（
         a int,
         b int,
         c int,
         KEY a（a,b,c）
    );

(1) select * from myTest where a=3 and b=5 and c=4; ---- abc order
The three indexes abc are all used in the where condition, and they all play a role.

(2) select * from myTest where c=4 and b=6 and a=3;
The order of conditions in where will be automatically optimized by MySQL before querying, and the effect is the same as the previous sentence.

(3) select * from myTest where a=3 and c=7;
A uses the index, B does not, so C does not use the index effect.

(4) select * from myTest where a=3 and b>7 and c=3; ---- b range value, breakpoint, blocks the index of c
a is used, b is also used, but c is not used. In this case, b is a range value, which is also a breakpoint, but it uses the index itself.

(5) select * from myTest where b=3 and c=4; --- Joint indexes must be used in order and all of them must be used. Because index a is not used, indexes b and c are not used here.

(6) select * from myTest where a>4 and b=7 and c=9;
a is used, b is not used, c is not used

(7) select * from myTest where a=3 order by b;
a uses the index, and b also uses the effect of the index in the result sorting. Any segment of b below a is sorted.

(8) select * from myTest where a=3 order by c;
a uses the index, but c does not have a sorting effect here because there is a breakpoint in the middle. Use explain to see filesort

(9) select * from mytable where b=3 order by a;
b does not use the index, and a does not play the role of the index in the sorting process

2. Conditions for index failure

• Do not perform any operations (calculations, functions, (automatic or manual) type conversions) on the index column, which will cause the index to fail and switch to a full table scan.
• The storage engine cannot use the columns on the right side of the index range condition
• Try to use covering indexes (queries that only access the index (index columns and query columns are consistent)), and reduce select *
• MySQL cannot use indexes when using inequality (!= or <>), which results in a full table scan.
• is null, is not null, and the index cannot be used either ---- There is a question here. After testing, it can indeed be used. The ref and const levels are not all
• If like starts with a wildcard character ('%abc...'), the MySQL index will fail and the operation will be a full table scan. Question: How to solve the problem of index not being used when like '% string %'?

String indexing fails without single quotes

SELECT * from staffs where name='2000'; -- Because MySQL will perform implicit type conversion at the bottom level SELECT * from staffs where name=2000; --- No index is used

General advice

• For single-key indexes, try to choose an index that has better filtering performance for the current query.
• When selecting a composite index, the field with the best filtering properties in the current query is ranked as early as possible in the index field order.
• When choosing a composite index, try to choose an index that can include more fields in the where clause of the current query.
• Try to select the appropriate index by analyzing statistics and adjusting the query writing method as much as possible.

This is the end of this article about the conditions for the effectiveness of MySQL joint indexes and the conditions for index invalidation. For more relevant content about the effectiveness of MySQL joint indexes, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!