Classification of indexes

primary key 、 unique index 、 General index 、 Composite index 、 And full-text indexing ;

primary key

Non empty unique index , A table has only one primary key index ; stay innodb in , Primary key index B+ The tree contains table data information ;

PRIMARY KEY(key)

unique index

You can't have the same value , There can be NULL value ;

UNIQUE(key)

General index

Allow the same index content ;

INDEX(key)

KEY(key[,...])

Composite index

Index multiple columns on a table

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

Full-text index

The technology of finding out any content information in the whole book and article stored in the database ; key word FULLTEXT;
Use... In short strings LIKE % ; Use... In full-text indexing match and against ;

Primary key selection

innodb The middle table is an index organization table , Each table has only one primary key ;
1. If the display settings PRIMARY KEY , Then the value of this setting key Is the primary key of the table ;
2. If no settings are displayed , Select... From the non empty unique index ;
    2.1、 There is only one non empty unique index , Select the index as the primary key ;
    2.2、 There are multiple non empty unique indexes , Select the first declared as the primary key ;
3. No non empty unique index , Then an 6 Bytes of _rowid A primary key ;

The index to achieve

Storage structure

innodb The storage engine uses the storage mode of segment pages , as follows

  The page is the smallest unit of disk management , Default 16k, The size of the area is 1M

Every page is a B+ Node of tree , as follows , Non leaf nodes store index information , The size of each page 16kb, hypothesis key And pointer occupation 16 byte , Leaf nodes occupy 100 byte , be 3 Layer of B+ Trees can store 16kb/16 * 16kb/16 * 160 = 1.6 Billion bars , It can be seen that ,B+ The height of the tree is generally 2-4 layer

Clustered index

Is a B+ Trees , Built according to the primary key B+ Trees , Leaf nodes store data pages , Data is also part of the index

Pictured above , To get the primary key id from 18-40 Data. B+ The tree is the access traversal order of the disk

Secondary index

Not all indexes created by primary keys are secondary indexes , The secondary index is also a tree B+ Trees , Its leaf node does not contain all the data of row records , Contains only clustered indexes key, Here's the picture , Hypothetical search 33, Then... Of its corresponding primary key key yes 47, That is to find in the clustered index key by 47 Value .

Overlay index

For data that can be found in the secondary index , There is no need to search in the clustered index , Because the height of the secondary index tree is generally lower than that of the clustered index tree , Less IO Number of visits .

such as , surface 1 in age It's the primary key

age,name It's a composite index

When looking for age=10 when , Will use the secondary index to directly find , Instead of using age Indexes .

The leftmost matching principle of composite index

For composite indexes , Match from left to right , encounter > < between like Just stop matching ;
 

Index failure

1、select ... where A and B if A and B One of the does not contain an index , The index becomes invalid ;
2、 The index field participates in the operation , The index becomes invalid ; for example ： from_unixtime(idx) = '2021-04-30';
3、 Implicit conversion of index field , The index becomes invalid ; for example ： '1' Implicitly convert to 1 ;
4、LIKE Fuzzy query , wildcard % start , The index becomes invalid ;

for example ： select * from user where name like'%ark';
5、 Use... On index fields NOT <> != Index failure ; If you judge id <> 0 Then change to idx > 0 or idx < 0 ;
6、 In the composite index , The first column index is not used , Index failure ;