- What is an index
An index is a data structure, usually a b-tree, Hash, that stores the values of a particular column (one or more columns) in a table. Its purpose is to help the database to obtain data efficiently, quickly find the specified key corresponding to the value or values. The essence of this is the fast lookup through mapping, which avoids full table scanning.
- B + Tree What's going on
the B + Tree represents a balance tree rather than a binary tree, and is a common type of index used by MySQL, mainly to solve how to quickly find the corresponding data by key, a balance finding tree designed for disk or other access auxiliary devices. b + trees are evolved from a two-fork search tree, a stationary binary tree, and a second tree. The B + Tree is typically 2 to 4 high, effectively reducing the number of disk lookups. B + Tree operation: https://yq.aliyun.com/articles/9280As you can see from the previous article, creating an index is an overhead. B + tree in order to maintain the balance, as the data changes (increase, delete), the B + tree splits or merges, splitting or merging operations involving disk page pages will have some overhead.
- Hash index
Hash index because of its structure, so in each query time directly in place, not as b-tree as a little forward. So the hash index efficiency is higher than b-tree, but the hash also has the disadvantage, mainly as follows:(1) because the hash value is stored, only <=> and in operation are supported.(2) hash index can not be sorted by Operation Index, this is because the time of the deposit is hashed, but the calculated hash value and storage is not necessarily equal, so cannot be sorted.(3) in combination, the index cannot be used on the part.(4) cannot be sortedThe Adaptive Hash index used in InnoDB is executed by the storage engine and cannot be intervened.
- Clustered index
the clustered index constructs a B + tree according to the primary key of each table, and the leaf nodes contain complete data records, and the leaf nodes can also be called data pages. A clustered index can only be a primary key, and if no primary key is found with a unique index that does not contain NULL, a hidden primary key is automatically created if the database is not found.
- Normal index (secondary index)
The difference from a clustered index is that the leaf node does not contain the data of the row record, except that its own key value also contains a bookmark (bookmark), which is the corresponding primary key of the row record. Therefore, through the common index query, we first find the result set of matching records through the leaf node, and then find a complete row record through the primary key index. Note that this is not a pointer to a row record, because the B + tree splits or merges as the data changes, and the position of the leaf nodes changes.
- What's the left principle?
A Federated index is ordered in the form of tuples, such as (Cityid,companyid) as a federated primary Key (index), the order of its leaf layers may be: (3,157), (3,158), (3,200), (5,34), (5,158), (7,90), You can see that it is sorted by Cityid first, then sorted by CompanyID, incremented for Cityid, and unordered for CompanyID. Leftmost Principle Index application branch:
| Query criteria |
Index condition |
|
| where Cityid = 3 |
Application Index |
|
| where CompanyID = 158 |
Do not apply index |
|
| where Cityid = 3 and CompanyID = 158 |
Application Index |
|
| where CompanyID = 158 and Cityid = 3 |
Application Index |
This is optimized by the execution plan |
- Overwrite Index
A secondary index allows you to get a record of the query without having to query the row records for the primary key. Benefits include:
- The secondary index does not include all the information for the entire row of records, so its size is much smaller than the clustered index, reducing IO operations
- The secondary index contains all the information, no longer querying row records through the primary key, reducing IO operations
The extra option in explain can be used to see if the overlay index is gone, if there is a using index is the overwrite indexfor the above-mentioned federated indexes, the following SQL all goes through the overwrite index
- Select Cityid,companyid from Cc_table
- Select Cityid,companyid from cc_table where companyid=158
- Select COUNT (*) from cc_table where companyid=158
- Executive explain
1190000008131735
About MYSQL Innodb Index
