mysql Several cases of index failure ：1, Conditions include or, Even if there is conditional indexing, it will not be used ;2, For multi column indexes , Unused first part , The index is not used ;3,like Query to % start , index is not valid ;4, When the field type is string , The data in the condition is not quoted in quotation marks .
The index does not always take effect , For example, the following situations , Will invalidate the index ：
1. If there are conditions or, Even if there is conditional indexing, it will not be used ( That's why try to use as little as possible or Reasons for )
be careful ： To use or, Want the index to work again , Can only or Each column in the condition is indexed
2. For multi column indexes , Not the first part of use , The index is not used
3.like Query is based on % start , index is not valid ; When like Prefix no %, Suffix yes % Time , Index valid .
4. If the column type is string , Be sure to quote the data in quotation marks in the condition , Otherwise, the index is not used
5. If mysql It is estimated that using full table scan is faster than using index , Index is not used
in addition , View index usage
show status like ‘Handler_read%';
You can pay attention ：
handler_read_key: The higher the value, the better , Higher indicates the number of times the index is used to query
handler_read_rnd_next: The higher this value is , Description query inefficient
1) No query criteria , Or the query criteria are not indexed
2) No guide column is used on the query criteria
3) The number of queries is the majority of large tables , Should be 30％ above .
4) The index itself is invalid
5) Query criteria use functions on index columns , Or perform operations on index columns , Operations include (+,-,*,/,! etc. ) Wrong example ：select * from test where
id-1=9; Correct example ：select * from test where id=10;
6) Query small table
7) Prompt not to use index
8) The statistics are not true
9) CBO The calculation of running index is too expensive . In fact, it also includes the above situation , This refers to the possession of the table block Smaller than index .
10) Index invalidation due to implicit conversion . This should be taken seriously . It is also a common mistake in development .
Due to the fields in the table tu_mdn Defined as varchar2(20), However, this field is used as the number Type with where Conditions passed to Oracle, This will invalidate the index .
Wrong example ：select * from test where tu_mdn=13333333333; Correct example ：select * from test
where tu_mdn='13333333333';
12) 1,<> 2, alone >,
13,like "%_" The percent sign comes first .
14, Table not analyzed .
15, The index column that is not the first position in the composite index is referenced separately .
16, When the character field is a number where No quotation marks in conditions .
17, Operation on index columns . Functional index is required .
18,not in ,not exist.
19, When the variable is times variable , The fields of the table are date Variable time . Or vice versa .
20,B-tree Indexes is null Can't go ,is not null Can go , Bitmap index is null,is not null Will go
21, Joint index is not null As long as the index column is created ( In no order ) Will go , in null Time
Must be used with the first column of the index , When indexing, the first position condition is is null Time , Other indexed columns can be is null( But it must be in all columns All satisfied is
null When ), perhaps = A value ; When the first place to index is = When a value , Other index columns can be any case ( include is null
= A value ), In both cases, the index will go . I won't go under other circumstances .
Technology