The simplest way to query data is to scan the whole table to find the data that meets the conditions.
The design of index is inspired by dictionary, which can locate quickly according to key information.
Index can avoid full table scanning and improve retrieval efficiency.
What kind of information can be an index
Primary key, unique key and other fields that can make data have a certain degree of differentiation.
Data structure of index
B+Tree, also used by some database indexes
Disadvantages of hash index
HashIndex can only satisfy
INQuery, unable to implement range query.
HashThe size relationship of index values is not necessarily the same as
HashBefore the operation, the size relationship is exactly the same. The database cannot use the data of the index to avoid any sort operation.
- For composite indexes,
HashIndex in calculation
HashValue is calculated after combining index keys
HashValue, not calculated separately
HashValue, so partial key queries cannot be used.
- Different data may have the same
HashValue, so table scanning cannot be avoided.
- Encountered a lot of
HashPerformance is reduced when values are equal.
The difference between dense index and sparse index
Dense index: the leaf node holds the entire data record. Dense index determines the physical arrangement order of tables. A table has only one physical arrangement order, so a table can only create one dense index.
Sparse index: the leaf node only holds the key value and the address / primary key of the row data record.
If a primary key is defined, it is used as a dense index.
If no primary key is defined, the first unique non empty index of the table is used as a dense index.
If the above conditions are not met,
InnoDBA hidden primary key (dense index) is generated internally.
The non primary key index stores the related key and its corresponding primary key value, including two searches.
InnoDBIn, the primary key uses a dense index, and the secondary key uses a sparse index. The primary key of the data is stored in the sparse index.
MyISAMIn, the primary key and secondary key are both sparse indexes, and the address of the data is stored.
How to locate and optimize slow query SQL
- Locate slow query based on slow log
SQLOr as far as possible
MySQLThe slow query log for is
MySQLA type of logging provided for recording in
MySQLStatement with response time over threshold in.
|long_query_time||1||Over 1s is recorded as slow query|
|slow_query_log||ON||Open slow query log|
|slow_query_log_file||xxx.log||Slow query log file|
The cause of the leftmost matching principle of joint index
To establish a federated index:
SELECT * FROM person_info WHERE area = 'TIANJIN' AND title = 'YUNZHI', go to index. It can be disordered.
SELECT * FROM person_info WHERE area = 'TIANJIN', go to index.
SELECT * FROM person_info WHERE title = 'YUNZHI', no index.
MySQLMatches right until a range query is encountered
(>/</between/like)Stop matching. such as
a = 3 and b = 4 and c > 5 and d = 6, if
(a, b, c, d)Index of the order,
dNo index is available; if
(a, b, d, c)All indexes of can be used.
INIt can be disordered, such as
a = 1 and b = 2 and c = 3establish
(a, b, c)Indexes can be in any order,
MySQLThe query optimizer of will be optimized to a form that the index can recognize.
MySQLThe rule to create a composite index is to sort the first field on the left of the composite index, and then the second field on the index.
The first field is absolutely ordered, and the second field is out of order. Therefore, it is impossible to directly use the second field to judge.
Is it better to build more indexes
- Tables with small amount of data do not need to be indexed, which will increase additional overhead.
- Data changes require index maintenance, so more indexes mean more maintenance costs.
- More indexes also mean more space.