Database--Index

First, the index introduction

General application system, reading and writing ratio of about 10:1, and the insert operation and general update operation rarely occur performance problems, encountered the most prone to problems or some complex query operations, so the optimization of query statements is obviously the top priority.

The index is equivalent to the book directory, can help users quickly find the content

Also called a key in MySQL, a data structure used by the storage engine to quickly find records. Can greatly improve query efficiency. In particular, when the amount of data is very large and the query involves more than one table, using an index can often speed up the query by tens of thousands of times.

Summarize:

The purpose of the index is to improve the efficiency of the query, which is the same as the directory we use to look up books: First locate the chapter, then navigate to a section under that chapter, and then find the number of pages. The essence is to filter out the final desired results by constantly narrowing down the range of data you want, and to turn random events into sequential events, that is, with this indexing mechanism, we can always use the same search method to lock the data.

Second, the Index method

1.BTREE Index

An index value is stored in a tree-shaped data structure by a certain algorithm.

When the system reads data from disk to memory, it is based on disk block (block), and data in the same disk block is read out at once rather than on demand. The InnoDB storage engine uses pages as the unit of data read, the smallest unit of its disk Management, and the default page size is 16kB.

For example, is a B + tree, the definition of B + tree can be seen in the B + tree, here is only a few points, the light blue block we call a disk block, you can see each disk block contains several data items (dark blue) and pointers (shown in yellow), such as disk Block 1 contains data items 17 and 35, including pointers P1, P3,P1 represents a disk block that is less than 17, P2 represents a disk block between 17 and 35, and P3 represents a disk block greater than 35. Real data exists at leaf nodes 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Non-leaf nodes do not store real data, only data items that guide the direction of the search, such as 17 and 35, do not exist in the data table.

The discovery process of B + trees

, if you want to find the data item 29, then the disk Block 1 is loaded into memory by disk, at this time Io, in memory with a binary lookup to determine 29 between 17 and 35, locking disk Block 1 P2 pointer, memory time because of very short (compared to the disk IO) can be negligible, Disk Block 1 through disk address of the P2 pointer to the disk block 3 is loaded into memory, the second io,29 between 26 and 30, locking disk block 3 of the P2 pointer, loading disk blocks 8 through the pointer to memory, a third Io, while in-memory binary find found 29, the end of the query, a total of three IO. The real situation is, the 3-tier B + tree can represent millions of data, if millions of data to find only three Io, the performance will be huge, if there is no index, each data item will occur once IO, then a total of millions of Io, it is obviously very expensive.

It is strongly noted that the index fields are as small as possible, and the disk blocks can store more indexes.

2.HASH Index

A hash is a (key=>value) Form of a key-value pair that allows multiple keys to correspond to the same value, but does not allow a key to correspond to more than one value, and to establish a hash index for a column or column.   The value of this column or column will be used to calculate a hash value by a certain algorithm, corresponding to one row or several rows of data. The hash index can be positioned once, and does not need to be looked up by layers like a tree index, so it is highly efficient.

Suppose the index uses the hash function f (), as follows:

1234	f(‘Arjen‘) = 2323f(‘Baron‘) = 7437f(‘Peter‘) = 8784f(‘Vadim‘) = 2458

At this point, the structure of the index is probably as follows:

　　

3.HASH comparison with Btree:

Hash Type index: query single fast, range query slow btree type index: b+ tree, the more the number of layers, the larger the amount of data, range query and random query fast (innodb default index type) different storage engine supports the same type of index InnoDB Support transactions, support row-level locking, Support Btree, Hash and other indexes, do not supportfull-text index; MyISAM does not support transactions, support table-level locking, Support Btree,full-text indexes, not support Hash Memory does not support transactions, support table-level locking, Support Btree, Hash and other indexes, do not supportfull-text index, NDB support transactions, support row-level locking, Support Hash index, does not support Btree,full- indexes such as text, Archive does not support transactions, table-level locking support, Btree, Hash,full-text and other indexes;

DG \

Database--Index