Btree Index and Hash Index (index is a data structure that improves query, sorting, grouping speed)
- Btree index data structure is balanced binary tree, time complexity of LGN
- Hash index data structure is a hash function, time complexity of O (1), data input hash function, output a data line address
- Disadvantage: The memory space will be redundant is not necessarily tightly arranged, not conducive to the scope of the query; Hash address conflict problem (zipper algorithm)
Rules for common Federated indexes
- The creation of a federated index is not a thin air, but a research need.
- Assuming index (a, B, c), the order is important
- where a = 1 and B = 2 and c = 4 (A,b,c can be used to index)
- where a = 3 and B = 4 (b can be used to index)
- where B = 4 and a = 3 (b can be used to index)
- where B = 5 and c = 4 (not indexed)
- where a = 1 and b like ' hello% ' and C = 4 (A, B can be used without the index C)
- where a = 1 and b like '%hello ' (a can use the index, B is not indexed)
- Group by B, C (b, C used to index)
- Group BY C, B (not indexed)
- Assuming that the A,b,c three fields correspond to three planks, from A to B, ' hello% ' went to the left half of the plank, so used the index, '%hello ' walked the right half of the plank, so do not walk the index, this is also called the index of the left prefix principle
- If there are multiple independent indexes in the where condition, can only use the previous one?
Non-clustered index and clustered index
- Non-clustered index corresponding to MyISAM, data and index binary tree is a separate file, check the data first to the index binary tree, then to the data, the index points to the location of the row on the disk, also known as the return line
- The clustered index corresponds to the InnoDB, the data and the index binary tree have the correlation, the leaf node of the primary key index directly puts the data itself, the leaf node of the subordinate index points to the index of the primary key
Page splitting due to clustered index
- When the data primary key of the insert is not regular, because the leaf node is stored by itself, it causes the primary key index tree node to change greatly, called the page splitting
- Considering two cases, the MyISAM engine is inserted into the sequence, and the InnoDB engine is inserted in the random order, and when the select * from T1 is used, the data table query results of the INNODB engine are output sequentially, although inserting the data is not necessary, but the index tree itself is ordered MyISAM engine corresponding to the data table, because there is no use where condition, so how to insert the output, is still disorderly
Index overrides in non-clustered indexes
- When the column to be queried is itself part of the index, it is not necessary to go through this step, called an index overlay, for example, to define a federated index, index (ID, name), and execute select name from t1 where id = 1, the Using index
MySQL Index optimization 1
