This question examines the effective and invalid conditions of the index. Such questions are actually very meaningful. I suggest you pay more attention to these questions when interviewing other partners in the future, which is much better than investigating general conceptual questions.

Can you roughly check whether the candidate pays attention to optimizing the program, improving code quality and program performance, or just doing it simply.

Conditions for Federation index invalidation

Joint index is also called composite index. Indexes on two or more columns are called composite indexes.

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

Using additional columns in the index can narrow the search, but using an index with two columns is different from using two separate indexes.

The structure of the composite index is similar to that of the telephone book. The person's name is composed of last name and first name. The telephone book first sorts people with the same last name by last name, and then sorts people with the same last name by first name. If you know your last name, the phone book will be very useful; If you know your last name and first name, the phone book is more useful, but if you only know your first name but not your last name, the phone book will not be useful.

Therefore, when creating a composite index, you should carefully consider the order of columns. Composite indexes are useful when searching all columns in an index or only the first few columns; Composite indexes are not useful when searching only for any subsequent columns

For example, establish 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
abc All three indexes are in where Conditions are used, and they all play a role


(2) select * from myTest  where  c=4 and b=6 and a=3;
where The order of conditions will be changed before query mysql Automatic optimization, the effect is the same as the previous sentence


(3) select * from myTest  where a=3 and c=7;
a Using the index, b It's no use, so c The index effect is not used

(4) select * from myTest  where a=3 and b>7 and c=3;     ---- b Range value, breakpoint, blocked c Index of
a Yes, b Also used, c No, this place b It 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;   --- Federated indexes must be used sequentially and need to be used in full
 because a The index is not used, so here bc No indexing effect


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

(7) select * from myTest  where a=3 order by b;
a The index is used, b The index effect is also used in the result sorting, a In any of the following paragraphs b It's in order


(8) select * from myTest  where a=3 order by c;
a The index is used, but this place c No sorting effect, because the intermediate breakpoint is, use explain Can see filesort

(9) select * from mytable where b=3 order by a;
b The index is not used. It is in sorting a It doesn't have an indexing effect

Finally, we talk about the conditions of index invalidation

• Failure to do any operation (calculation, function, (automatic or manual) type conversion) on the index column will lead to index failure and turn to full table scanning
• The storage engine cannot use the column to the right of the index range condition
• Try to use the overlay index (only the query accessing the index (the index column is consistent with the query column)) to reduce the select ion*
• mysql is not equal to (= Or < >)
• is null,is not null, and indexes cannot be used
• like starts with a wildcard ('% abc...') if the mysql index fails, it will become a full table scan operation.

Question: how to solve the problem that the index is not used when like '% string%'?

Invalid string index without single quotation marks

SELECT * from staffs where name='2000';  
-- because mysql It is implicitly typed at the bottom

SELECT * from staffs where name=2000;  
--- Index not used

ordinary suggestion

• For single key indexes, try to select indexes with better filtering performance for the current query
• When selecting a combined index, the fields with the best filtering performance in the current Query are in the index field order. The higher the position, the better.
• When selecting composite indexes, try to select indexes that can contain more fields in the where clause of the current query
• Try to select the appropriate index by analyzing the statistical information and adjusting the writing method of query

Source: https://smiletimli.blog.csdn.net/article/details/87888809

Recent hot article recommendations:

1.1000 + Java interview questions and answers (2021 latest version)

2.Stop playing if/ else on the full screen. Try the strategy mode. It's really fragrant!!

3.what the fuck! What is the new syntax of xx ≠ null in Java?

4.Spring Boot 2.5 heavy release, dark mode is too explosive!

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!