Database index

zero 、 Use navicat Of “ explain ” Button to view the execution plan , Click again every time , Otherwise, the explanation page is the previous implementation plan .

One 、 The design principle of index
1） Often used as query criteria 、 Fields that are used more frequently , Can be set as index column ;
2） Index , Try to use unique fields in the whole table , Build unique index , This is the most efficient way to query ;
3） When building a composite index , Pay attention to the field order ;
4） The more indexes, the better , Too many indexes will reduce DML（ Database operation language ） The speed of , Need frequent increase 、 Delete 、 The changed table is greatly affected , Because every time you operate the database, you have to maintain the index . For less data 、 Try not to build indexes on tables with few queries .

Two 、 Composite index
1） Set up composite index , Pay attention to the order ; Because the pre index , Will also hit the composite index , So don't waste , Put the fields that are often used as query criteria first

2） As long as the leading index is included, the index will be combined in the name , It has nothing to do with order

3） The combined index columns are all in where in , You can hit the index . It's not about order .

4）where The condition only applies to the non leading columns of the composite index , Unable to hit index .

3、 ... and 、 Several situations that cause index failure
1、 When there are too many data rows in the query result （ More than the total number of rows in the table 30%）, No index , Direct full table scan ;

2、where After the query , Use

>  perhaps  >=
<  perhaps  <=
!=  
not in  
is null

Don't go with the index

3、 There are operations on the column
Here are two sql The result is the same , But if there are operations on the column, do not go to the index

select * from test where object_id + 1 > 20000;   //  Full table  

select * from test where object_id  > 20000 - 1;  //  Indexes 

4、 There is a function on the column

select * from test where to_char(CREATED, 'yyyy-mm-dd hh24:mi:ss') = '2021-04-01 22:24:03';  //  Full table 

select * from test where CREATED = to_date('2021-04-01 22:24:03', 'yyyy-mm-dd hh24:mi:ss');  //  Indexes 

5、 Implicit conversion
When Character type and When comparing numeric types ,Oracle Will convert character type to numeric type , namely TO_NUMBER(“STR_ID”)=3 , Then compare .
Suppose there is a field in the table STR_ID Is a string type , Then it will cause the following situations

a. select * from test where str_id = '3';   //  Indexes 

b. select * from test where str_id = 3;     //  Full table 

b Missed Index , Because the database implicitly converts the fields , namely TO_NUMBER(“STR_ID”), This is equivalent to using functions on fields .

So what if the column is numeric ？ Here are two sql They all go by the index , This is because ’3’ Will become 3, There is no implicit conversion of columns

a. select count(*) from test where data_object_id = 3;  // 1 That's ok 
b. select * from test where data_object_id = 3;  //  Full table 

6、 Plan to use composite indexes , But no pre index columns , No index hits