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
Technology