What is an index if there is no index , The number of records scanned is greater than the number of records indexed

The index stores the value of the index column ( such as id Index column , Then store the value of the index column ), Address of the row corresponding to the index value in memory ( Or directly store the data of this row )

SELECT * FROM user WHERE username= 'jiajun'
,username Indexing , If the data structure of the index is hash surface , So at this time , By calculation jiajun of hash value ,O(1) Complexity can find the location of the record

hash Index under equivalent search , None at this time hash conflict , In this case , Efficiency is very high

But under the scope search , because hash Not orderly , Then search the range ,hash The advantages of table cannot be brought into play .

stay hash Under conflict , Search efficiency will decrease

Disk read disk read steps : Fixed cylinder , Fixed track , Fixed magnetic block

Disk time is mainly consumed on the positioning cylinder , So if you want to improve the speed , With the same amount of data , Put as much data as possible on the disk block , Then this can reduce the number of times the head positioning cylinder moves , reduce IO Times of .

The value of all nodes in the left subtree of the binary search tree is less than the value of its root node

The value of all nodes in the right subtree is greater than that of its root node

The left and right subtrees of any node are binary search trees

No node with equal key value

Analyze the complexity of binary search tree lgn

But is there any way to reduce it IO Times of , That is, can we reduce the height of the tree

B- tree

(m Step tree m/2<=k<=m) At least two child nodes of the root node

All leaf nodes are on the same layer

Intermediate node contains k-1 Elements and k Children

The elements in the node are arranged from small to large

Each node contains the value of the index column , And this data record ( Or the value of this data record )

Analysis relative to binary lookup tree ,B The tree became short and fat , Because each node stores more elements , So with the same element , Reduced the height of the tree , Then you can reduce IO Times of

Each node stores data ( The value of this line record or the address of this line record in memory ), So different query performance is different .

B+ Tree in B- On the basis of trees

Except for leaf nodes , Other nodes do not contain records ( Rows in the database ) Location of

The leaf node contains all index values , And arranged from small to large , And records ( Row in database ) Location of

Analysis if the nodes are the same size , So if we except leaf nodes , Other nodes do not contain data , Then you can put more elements ( Index value ), In this way, the tree will become more short and fat , that IO The number of times can be further reduced

Because the elements of leaf nodes are arranged in order , And a linked list is formed between leaf nodes , Then the efficiency of range query can be improved during orderly search

be relative to B tree , Because all data is stored in leaf nodes , That means that every search must find the leaf node from the root , This means that the query performance is average .

Summary index is a kind of data , Can avoid full table query , It can be compared with catalogues and books .

Indexes need a data structure to store

Use hash table (hash) The query complexity can reach O(1), But when querying the range ,hash Can't improve performance

IO Operation is time-consuming , To improve query performance , Can reduce IO Times of

For the storage structure of the tree , To improve performance , reduce IO Times of , Can lower the height of the tree

Read a node once IO, With the same amount of data , If each node can store more elements , Then the height of the tree can be reduced .

B The tree lowered the height of the tree , When the node size is the same , because B The nodes of the tree store elements and data , and B+ The tree stores all the data in the leaf node , So in that case , Each node can store more elements , Then you can lower the height of the tree again

B+ The query performance of the tree is more stable , And it is more conducive to range search

If it is a clustered index (InnoDB engine ), Then the data of this record stored in the node , The data file itself is an index file

If it is a nonclustered index (MyISAM engine ), Then the node stores the address of the record in this line . Index file and data file are separated

Type of index general index , Allow the same content

unique index , Unique index value , Allow null values

primary key , Automatically create a primary key index when creating a primary key , Unique and cannot be empty

Composite index , Multi column composite index

Use of indexes ALTER TABLE table_name ADD INDEX index_name (column_list) Add general index

ALTER TABLE table_name ADD UNIQUE (column_list) Add unique index

ALTER TABLE table_name ADD PRIMARY KEY (column_list) Add primary key index

Note that if this is username,age,sex Build joint index

Leftmost match means that the leftmost index is matched first ,(username)(username,age)(username,age,sex), As long as the query criteria use the leftmost column , Generally, index will be used . The order can be different , such as (age,username), This is the credit of the query optimizer .

Fuzzy query only % No. is not in the first character , Index can be used , such as username like '%jiajun' So it is not adopted

If or There is a condition in which there is no index ,sql Statement does not use index , such as usernmae ='jiajun' or pwd='666', The index is not adopted at this time

In composite index , If the query condition is not the first column of the index , Index may not be adopted , For example, at this time where age =1

If the column is character type , such as username It is a character type and an index column , If this is a query username=1 , Without quotation marks , Then the index will not be used at this time

Can be used show status like 'Handler_read%' To view index usage .

It is suggested to give priority to practice

Index principle index should be designed in where Column after , instead of select Column after

Indexes should be built on columns with large discrimination , For example, the status is only 1 and 2 There is no need to build an index

When indexing strings , A prefix length should be specified , For example, a column is char(200), If the first few characters need to be distinguished , Then index the first few characters , This reduces the footprint , Also increased speed

Don't create too many indexes , Index takes up space , And the speed will be reduced when updating , And if there are too many indexes ,Mysql When implementing the plan , Each index will be considered , It's also a waste of time

There is no doubt about the advantages and disadvantages of indexing , When used correctly , Index can improve query speed

Indexing can also improve the speed of grouping and sorting

When deleting and adding due to modification , To maintain index files , Adjust the tree , So the performance is reduced

Index files also need space