MySQL index (I)

Indexes

1、 Index Overview

Indexes (index） Help MySL Data structure for efficient data acquisition ( Orderly ). Out of data , The database system also maintains a data structure that satisfies a specific search algorithm , These data structures are referenced in some way （ Point to ） data , In this way, advanced search algorithms can be implemented on these data structures , This data structure is the index .

remarks ： The above binary tree index structure is only a schematic diagram , Not a real index structure .

Advantages and disadvantages ：

2、 Index structure

Mysql Indexing is implemented at the storage engine level , Different storage engines have different structures , It mainly includes the following ：

What we usually call index , If not specified , Generally speaking, it means B+ Index of tree structure organization .

Disadvantages of binary tree ： On sequential insertion , It will form a linked list , Query performance is greatly reduced . In case of large amount of data , Deeper levels , The retrieval speed is slow .

Red and black trees ： In case of large amount of data , Deeper levels , The retrieval speed is slow .

be relative to B-Tree difference ：

​ 1、 All data will appear in the leaf node

​ 2、 Leaf nodes form a one-way linked list

Hash Indexes ：

Hash Index characteristics ：

1、Hash Indexes can only be used for peer-to-peer comparisons （=,in）, Range query is not supported （between,>,<,…）

2、 Cannot complete sort operation with index

3、 High query efficiency , Usually only one search is needed , Efficiency is usually higher than B+Tree Indexes

Storage engine support ：

stay MySQL in , Support hash The index is Memory engine , and InnoDB It has adaptive function hash function ,hash The index is the storage engine B+Tree The index is automatically built under specified conditions .

Why? InnoDB The storage engine chooses to use B+Tree Index structure ？

• Relative to a binary tree , Fewer levels , High search efficiency ;
• about B-Tree, Whether leaf nodes or non leaf nodes , Data will be saved , This results in fewer key values stored in a page , The pointer decreases , Save a lot of data as well , Can only increase the height of the tree , Resulting in reduced performance
• relative hash Indexes ,B+Tree Support range matching and sorting operations

3、 Index classification

stay InnoDB In the storage engine , According to the storage form of the index , It can be divided into the following two types ：

Clustered index selection rules ：

• If there is a primary key , A primary key index is a clustered index .
• If there is no primary key , The first unique index will be used （UNIQUE） As a clustered index .
• If the table does not have a primary key , Or a suitable unique index , be InnoDB It will automatically generate a rowid As a hidden clustered index .

hypothesis Query name Arm Of ： select * from user where name = ‘Arm’;

He will first go to the secondary index to query the primary key id, Then query the clustered index and find id = 10 Row data , This is also called a table query .

reflection ：

1、 once SQL sentence , Which is more efficient ？ Why? ？

select * from user where id = 10;

select * from user where name = 'arm';
#  remarks ：id Primary key ,name Fields are indexed 

This is very simple ,id Primary key , explain id Is clustered index , You only need to query once to get row data

And in accordance with name To query, you need to query twice , Return to the table for query