Database index (notes)

Time:2019-11-21

Introduction

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.

Why index

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

Mainstream useB+Tree, also used by some database indexesHashIndex,BitMap

Disadvantages of hash index

  1. HashIndex can only satisfy=andINQuery, unable to implement range query.
  2. HashThe size relationship of index values is not necessarily the same asHashBefore the operation, the size relationship is exactly the same. The database cannot use the data of the index to avoid any sort operation.
  3. For composite indexes,HashIndex in calculationHashValue is calculated after combining index keysHashValue, not calculated separatelyHashValue, so partial key queries cannot be used.
  4. Different data may have the sameHashValue, so table scanning cannot be avoided.
  5. Encountered a lot ofHashPerformance 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.

InnoDB

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.

MySQL

Database index (notes)

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

Overall thinking:

  1. Locate slow query based on slow logSQL
  2. UseexplainEtcSQL
  3. modifySQLOr as far as possibleSQLIndex walking

Slow log:MySQLThe slow query log for isMySQLA type of logging provided for recording inMySQLStatement with response time over threshold in.

Set up:

key To configure describe
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:index_area_title

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.

However,SELECT * FROM person_info WHERE title = 'YUNZHI', no index.

  1. MySQLMatches right until a range query is encountered(>/</between/like)Stop matching. such asa = 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.
  2. =andINIt can be disordered, such asa = 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.

Reason

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

  1. Tables with small amount of data do not need to be indexed, which will increase additional overhead.
  2. Data changes require index maintenance, so more indexes mean more maintenance costs.
  3. More indexes also mean more space.