The effective condition of MySQL joint index and the invalid condition of index

Hello everyone , I meet you again , I'm the king of the whole stack .

1. Conditions for union index failure

Joint index Also called Composite index . An index on two or more columns is called a composite index .

For composite indexes ：Mysql Use the fields in the index from left to right , A query can use only a part of the index , But only the far left part . For example, the index is key index （a,b,c）. Can support a | a,b| a,b,c 3 Combination of search , But does not support b,c Search for . When the leftmost field is a constant reference , The index is very efficient .

Use additional columns in the index , You can narrow your 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 phonebook , A person's first name consists of a last name and a first name , The phonebook is first sorted by last name , Then sort people with the same last name by first name . If you know the 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 the first and last names , The phone book will be useless .

So when you create a composite index , The order of the columns should be carefully considered . When searching all columns in the index or only the first few columns , Composite indexes are very useful ; When a search is only performed on any subsequent column , Composite indexes are useless .

Such as ： establish full name 、 Age 、 Composite index of 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 The order abc All three indexes are in where It's used in the conditions , And it all worked (2) select * from myTest where c=4 and b=6 and a=3; where The order of the conditions inside will be changed before the query mysql Automatic optimization , The effect is the same as the sentence (3) select * from myTest where a=3 and c=7; a Use index ,b of no avail , therefore c There is no index effect (4) select * from myTest where a=3 and b>7 and c=3; —- b Range value , The breakpoint , blocked c The index of a Yes ,b Also used. ,c Not used , This place b It's the range value , It's also a breakpoint , It just uses the index itself (5) select * from myTest where b=3 and c=4; — Federated indexes must be used sequentially , And need to use it all 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 Index is used ,b Index effect is also used in 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 Index is used , But this place c No ranking effect , Because there's a break in the middle , Use explain You can see filesort (9) select * from mytable where b=3 order by a; b No index is used , Sorting a It doesn't have an indexing effect

2. The condition of index invalidation

• Do nothing on the index column （ Calculation 、 function 、（ Automatically or Manual ） Type conversion ）, It will cause index invalidation and turn to full table scan
• The storage engine cannot use indexes Range conditions The column on the right
• Try to use overlay index （ Queries that only access the index （ The index column is consistent with the query column ））, Reduce select *
• mysql In use is not equal to （！= perhaps <>） Unable to use the index will result in a full table scan
• is null,is not null You can't use indexes —- There are questions here , After testing, it can be used ,ref and const Grade , Not at all all
• like Start with a wildcard （’%abc…’）mysql Index invalidation will become a full table scan operation . problem ： solve like‘% character string %’ When index is not used ？

• String index is invalid without single quotation marks

SELECT * from staffs where name=’2000′; — because mysql It will be implicitly type converted at the bottom SELECT * from staffs where name=2000; — Unused index

General advice

1. For single key indexes , Try to choose the current query Better filtering index
2. When choosing a composite index , At present Query The most filterable field in the index field order , The higher the position, the better .
3. When choosing a composite index , Try to include the current query Medium where Index of more fields in clause
4. By analyzing statistics and adjusting as much as possible query To achieve the purpose of selecting the appropriate index

Expand to add :

link ：https://www.nowcoder.com/questionTerminal/efe65435bf4d4eee9d7534cf64951674 source ： Cattle from mysql One in the database user In the table , It contains fields A,B,C, The field types are as follows :A:int,B:int,C:int According to the fields A,B,C according to ABC Build composite index sequentially idx_A_B_C, Indexes are used in the following query statements idx_A_B_C What are the sentences of ？

select *from user where A=1 and B=1
select *from user where 1=1 and A=1 and B=1
select *from user where B=1 and C=1
select *from user where A=1 and C=1

right key ：A B D

analysis :

Composite index can only use a part of composite index , But it must start with the leftmost part , And there can be constants

Because the composite index is idx_A_B_C, So the query condition can only be a,ab,abc,ac In order to be Use to index idx_A_B_C

A The leftmost prefix rule of composite index is for condition A=1 and B=1 Obviously, the index will be used .

B For conditions A = 1 and B = 1 Satisfy the leftmost prefix 1 = 1 This part of the constant expression is not indexed .

C The leftmost prefix condition is not satisfied , Index cannot be used .

D Through conditions A = 1 To use the index to query . Because a,c Combination can also , Because actually only a The index of ,c Not used , But it still shows ABC Joint index , Actually, it just used a A single column index of ; Because it is one of the leftmost prefixes , If it is changed to a separate condition C = 1, Instead of using indexes, you can scan the whole table , This can be done by mysql Of explain Command to verify .

This is for multiple and The leftmost prefix if... Is applied to the condition or single condition of conditional connection or No way. .

Publisher ： Full stack programmer stack length , Reprint please indicate the source ：https://javaforall.cn/111653.html Link to the original text ：https://javaforall.cn