Data Structure and algorithm principle behind MySQL indexes (I), mysql Indexes

Data Structure and algorithm principle behind MySQL indexes (I), mysql Indexes

This document usesMySQLThe database is the object of study. We will discuss some topics related to database indexes. In particular,MySQLSupports many storage engines, and different storage engines support different indexes. ThereforeMySQLThe Database supports multiple index types, suchBTreeIndex, hash index, full-text index, and so on. To avoid confusion, this article only focuses onBTreeIndex, because this is commonly usedMySQLThis article does not discuss hash indexes and full-text indexes.

The main content of this article is divided into three parts.

The first part focuses on the data structure and algorithm theory.MySQLMathematical basis of database indexes.

The second part discusses topics such as clustered index, non-clustered index, and covered index based on the index architecture of MyISAM and InnoDB data storage engines in the MySQL database.

The third part discusses the high-performance index Usage Policy in MySQL based on the above theoretical basis.

The essence of basic data structure and algorithm Indexes

MySQLThe official index definition is:Index (Index) Is helpMySQLEfficient Data Structure acquisition. Extract the sentence trunk to get the essence of the index: Index is the data structure.

We know that database query is one of the most important functions of the database. We all want to query data as quickly as possible, so the designers of the database system willQuery Algorithm Optimization. The most basic query algorithm is sequential query (linear search), This complexity isO(n)The algorithm is obviously bad when the data size is large. Fortunately, the development of computer science provides many better search algorithms, such as binary search (binary search), Binary tree search (binary tree search. If you analyze it a little, you will find that,Each search algorithm can only be applied to a specific data structure.For example, binary search requires that the retrieved data be ordered, while binary search can only be applied to binary search trees,The organization structure of the data itself cannot fully satisfy various data structures(For example, theoretically it is impossible to organize both columns in order at the same time). Therefore,In addition to data, the database system also maintains data structures that meet specific search algorithms.,These data structures reference (point to) data in some wayIn this way, you canImplement advanced search algorithms on these data structures.This data structure is an index..

Let's look at an example:

Figure 1 shows a possible indexing method. On the left is a data table with a total of seven records in two columns, and on the left is the physical address of the data records (note that logically adjacent records are not physically adjacent on the disk ). To speed upCol2To maintain a binary search tree shown on the right. Each node containsIndex key valueAnd a physical address pointing to the corresponding data recordPointerIn this way, you can use the binary query inO(log2n)Obtain the corresponding data within the complexity.

Although this is a genuine index, the actual database system hardly uses a binary search tree or its evolutionary Variety（red-black tree）The reason is described below.

B-Tree and B + Tree

Currently, most database systems and file systems useB-TreeOr its variantsB+TreeAs an index structure, the next section in this article will discuss why Based on the memory principle and computer access principle.B-TreeAndB+TreeThis section describes indexes from the perspective of data structures.

B-Tree

To describeB-TreeFirst, define a data record as a binary group.[key, data],keyIs the key value of the record. For different data records,keyThey are different;dataDivision of data recordskeyExternal data. SoB-TreeIs a data structure that meets the following conditions:

Figure 2 isd=2OfB-Tree:

BecauseB-TreeInB-TreePresskeyThe data retrieval algorithm is intuitive: first, perform a binary search from the root node. If yes, the system returnsdataOtherwise, Recursively search the node pointed by the pointer of the corresponding interval until the node ornullPointer. The former query is successful, and the latter query fails.B-TreeThe pseudocode of the above search algorithm is as follows:

BTree_Search(node, key){    if(node == null) return null;    foreach(node.key)    {        if(node.key[i] == key) return node.data[i];        if(node.key[i] > key) return BTree_Search(point[i]->node);    }    return BTree_Search(point[i+1]->node);}data = BTree_Search(root, my_key);

AboutB-TreeThere are a series of interesting properties, such as a degreedOfB-Tree, Set its indexNItemskey, Then its height ishThe upper limit islogd((N+1)/2), RetrievekeyThe progressive complexity of finding the number of nodes isO(logdN). From this point, we can see that,B-TreeIs a very efficient index data structure.

In addition, insertion and deletion of new data records will damageB-TreeTherefore, when inserting or deleting a tree, you need to split, merge, and transfer the tree to maintainB-TreeNature, this article does not intend to fully discussB-TreeThis is because there are a lot of detailsB-TreeIf you are interested in the mathematical nature and the insertion and deletion algorithm, you can find the relevant materials in the reference column at the end of this Article for reading.

B + Tree

B-TreeThere are many variants, the most common of which isB+TreeFor exampleMySQLIt is widely used.B+TreeImplement its index structure.

AndB-TreeCompared,B+TreeThere are the following differences:

Figure 3 is a simpleB+Tree.

Because not all nodes have the same domainB+TreeThe size of the inner and middle nodes is generally different. This correspondsB-TreeDifferent thoughB-TreeThekeyThe number of pointers may be different, but the fields and upper limit of each node are the same.B-TreeThe same size of space is usually applied for each node.

Generally,B+TreeRatioB-TreeIt is more suitable for implementing the external storage index structure. The specific reason is related to the principle of external storage and the principle of computer access, which will be discussed below.

B + Tree with sequential access pointers

Generally used in a database or File SystemB+TreeThe structure is in the classicB+TreeAnd added sequential access pointers.

4B+TreeAdd a pointer to the adjacent leaf node for each leaf node.With sequential access pointerB+Tree. The purpose of this optimization isImprove the Performance of range accessIn Example 4, if you want to querykeyFor slave18To49All data records,After 18 is found, you only need to traverse the nodes and pointers to access all the data nodes at a time. This greatly mentions the interval query efficiency..

This section describesB-TreeAndB+TreeA brief introduction is provided. The next section introduces why the currentB+TreeIt is the preferred data structure for database systems to implement indexes.

Why use B-Tree (B + Tree)

As mentioned above, data structures such as red and black trees can also be used for indexing. However, file systems and database systems are widely used.B-/+TreeAs an index structure, this section will be discussed based on knowledge about computer composition principles.B-/+TreeAs the theoretical basis of indexes.

In general, the index itself is also very large, it is impossible to store all in the memory, so the index is often stored on the disk in the form of index files. In this way, a disk is generated during the index search process.I/OConsumption, relative to memory access,I/OThe access consumption is several orders of magnitude higher, soThe most important indicator for evaluating a data structure as an index is the diskI/OProgressive complexity of the number of operations. In other words,The structure of indexes should be organized to minimize the number of disks in the search process.I/ONumber of accesses. Next we will first introduce the memory and disk access principles, and then combine these principles for analysis.B-/+TreeEfficiency of indexing.

Principle of primary storage access

Currently, the main memory used by computers is basically random read/write memory (RAM), ModernRAMThe structure and access principle of the object are complicated. The difference in this article is as follows: abstract a very simple access model to describe it.RAM.

From the abstract point of view, the primary storage is a matrix composed of a series of storage units, each of which stores a fixed size of data. Each storage unit has a unique address, and the addressing rules of modern primary storage are complicated. Here, it is simplified into a two-dimensional address: a row address and a column address can be used to uniquely locate a storage unit. Figure 5 shows a 4x4 primary storage model.

The access process of the primary storage is as follows:

When the system needs to read the primary storage, the address signal is put into the address bus and uploaded to the primary storage. After reading the address signal, the primary storage parses the signal and locates the specified storage unit, the stored unit data is then placed on the Data Bus for reading by other components.

The process of writing the primary storage is similar. The system will write the Unit Address and data respectively on the address bus and data bus. The primary storage will read the content of the two bus and write the data accordingly.

It can be seen that the time of primary storage access is only linearly related to the number of accesses, because there is no mechanical operation, the "distance" of the data accessed twice will not affect the time, for example, firstA0Retrieve againA1And first takeA0Retrieve againD3The time consumption is the same.

Disk access principle

As mentioned above, indexes are generally stored on disks as files, and index retrieval requires disks.I/OOperation. Unlike primary storage, DisksI/OThere is a mechanical movement cost, so the diskI/OThe time consumption is huge.

Figure 6 shows the overall structure of the disk.

A disk is composed of circular disks of the same size and coaxial. the disk can be rotated (each disk must be rotated synchronously ). There is a head bracket on one side of the disk, and the head bracket is fixed with a set of heads, each of which is responsible for accessing the content of a disk. The head cannot be rotated, but it can be moved along the radius of the disk (in fact, it is a diagonal movement). Each head must be coaxial at the same time, that is, from top to bottom, all heads are overlapped at any time (however, multiple heads are already available independently, but not limited to this ).

Figure 7 shows the disk structure.

The disc is divided into a series of concentric rings with the center of the disc. Each concentric ring is calledTrackAll the tracks with the same radius constitute oneCylindrical. The track is divided into small segments along the radius line. Each segment is calledSlice,Each sector is the minimum storage unit of the disk.. For simplicity, we assume that the disk has only one disk and one head.

When data needs to be read from the disk, the system will pass the data Logical Address to the disk. the disk control circuit translates the logical address into a physical address according to the addressing logic, determine the track and sector of the data to be read. To read data from this sector, you need to place the head above this sector. To achieve this, the head needs to be moved and aligned to the corresponding track. This process is called track finding and the time it takes is calledSeek timeAnd then the disk rotates the target sector to the bottom of the head. the time consumed in this process is calledRotation time.

Local principle and disk pre-read

Because of the characteristics of the storage medium, the access to the disk itself is much slower than the primary storage, coupled with the cost of mechanical movement, the access speed of the disk is often one of the primary storage, so in order to improve efficiency, minimize disk spaceI/O. To achieve this goal, the disk is usually not read strictly on demand, but preread every time. Even if only one byte is required, the disk starts from this location, read data of a certain length in sequence into the memory. This theory is based on the well-known local principle in Computer Science:

When a data is used, the data nearby it is usually used immediately.

The data required during the program running is usually concentrated.

Because sequential disk reading is highly efficient (with little rotation time required without seeking time), preread can improve local programs.I/OEfficiency.

The pre-read length is generally page (page. Pages are logical blocks for computer memory management. Hardware and operating systems often divide primary and disk storage areas into contiguous blocks of the same size, each block is called a page (in many operating systems, the page size is usually4k), The primary storage and disk exchange data in pages. When the data to be read by the program is not in the primary storage, a page missing exception is triggered, and the system sends a disk reading signal to the disk, the disk finds the starting position of the data and reads one or more pages consecutively into the memory. If an exception is returned, the program continues to run.

B-/+ Tree index Performance Analysis

It can be analyzed at last.B-/+TreeThe index performance is improved.

As mentioned above, disks are generally used.I/ONumber of times to evaluate the index structure. Starting fromB-TreeAnalysis, accordingB-TreeYou can see that you need to accesshNodes. The database system designer cleverly utilizes the disk pre-read principle,Set the size of a node to equal to one page.,In this way, each node only needs oneI/OIt can be fully loaded.. In order to achieve this goalB-TreeYou also need to use the following skills:

Each time you create a node, you can directly apply for a page space. This ensures that a node is physically stored on a page. In addition, the computer storage allocation is page-aligned.nodeOnly onceI/O.

B-TreeA maximumh-1TimesI/O(Root Node resident memory), progressive complexity isO(h)=O(logdN). In practicedIt is a very large number, usually greater100, SohVery small (usually no more than 3 ).

To sum up, useB-TreeAs an index structure, the efficiency is very high.

The structure of the red and black trees,hIt is much deeper. Because logically close nodes (Parent and Child) may be far physically unable to use localityI/OThe progressive complexity is alsoO(h), Obvious efficiency ratioB-TreeA lot worse.

As mentioned above,B+TreeMore suitable for external storage index, cause and internal node outboundd. From the analysis above, we can see that,dThe larger the index, the better the performance, and the maximum degree of output depends on the node.keyAnddataSize:

dmax = floor(pagesize / (keysize + datasize + pointsize))   (pagesize – dmax >= pointsize)

Or

dmax = floor(pagesize / (keysize + datasize + pointsize)) – 1   (pagesize – dmax < pointsize)

floorReturns the rounded down value.BecauseB+TreeInternal node removeddataTherefore, you can have a higher degree of output and better performance..

This chapter discusses the index-related data structures and algorithms theoretically. The next chapter will discussB+TreeHow to ImplementMySQLAndMyISAMAndInnDBThe storage engine introduces two different index implementation modes: Non-clustered index and clustered index.

MySQL index implementation

InMySQLThe index is a concept at the storage engine level. Different storage engines have different implementation methods for the index. This article mainly discussesMyISAMAndInnoDBThe index implementation method of the two storage engines.

MyISAM index implementation

MyISAMEngine usageB+TreeAs the index structure, the leaf nodedataThe domain storesData Record address. YesMyISAMSchematic diagram of the index:

The table has three columns.Col1Is the primary key, then figure 8 isMyISAMPrimary Index of a table (Primary key. We can see thatMyISAMThe index file only stores the address of the data record. InMyISAMMedium, primary index and secondary index (Secondary key) There is no difference in structure, but it is only required by the primary index.keyThe secondary index is unique.keyIt can be repeated. If weCol2If a secondary index is created, the index structure is shown in:

It is alsoB+Tree,dataThe address of the domain to save the data record. Therefore,MyISAMThe index search algorithm inB+TreeIf the specifiedKeyIf yes, retrievedataThe value of the domain, and thendataThe value of the domain is the address, and the corresponding data record is read..

MyISAMThe index method is also called "non-clustering ".InnoDBClustered index differentiation.

InnoDB Index implementation

AlthoughInnoDBAlso useB+TreeAs the index structure, but the specific implementation method isMyISAMDifferent.

The first major difference is thatInnoDBThe data file itself is the index file. From the above,MyISAMIndex files and data files are separated,The index file only stores the address of the data record. WhileInInnoDBThe table data file itself isB+TreeAn index structure of the organization, the leaf node of the treedataDomain stores complete data records. This indexkeyIs the primary key of the data table, soInnoDBThe table data file itself is the primary index.

Figure 10 isInnoDBThe primary index (also a data file) shows that the leaf node contains a complete data record. This index is calledClustered Index. BecauseInnoDBThe data file itself needs to be clustered by the primary key, soInnoDBThe table must have a primary key (MyISAMNo). If it is not explicitly specifiedMySQLThe system automatically selects a column that uniquely identifies a data record as the primary key. If this column does not existMySQLAutomaticallyInnoDBThe table generates an implicit field as the primary key,This field is 6 bytes in length and its type is long integer..

Second andMyISAMThe difference between indexes is thatInnoDBSecondary IndexdataThe value of the primary key of the corresponding record in the domain storage instead of the address.. In other words,InnoDBAll secondary indexesdataDomain. For example, in Figure 11Col3A secondary index on:

It is a string of English characters.ASCIICode as a comparison criterion. Clustered index makes the search by primary key very efficient,Secondary index search requires two indexes: First retrieve the secondary index to obtain the primary key, and then use the primary key to retrieve the record in the primary index.

Understanding the index implementation methods of different storage engines is very helpful for correct use and optimization of indexes, for example, knowingInnoDBAfter the implementation of the index, it is easy to understand whyToo long fields are not recommended as primary keys.Because all secondary indexes reference the primary index,If the primary index is too long, the secondary index will become too large.. For example, a non-monotonous field is used as the primary key inInnoDBIs not a good idea, becauseInnoDBThe data file itself isB+TreeA non-monotonous primary key causes the data file to be maintained when a new record is inserted.B+TreeAnd frequent split adjustment, which is very inefficient, and using the auto-incrementing field as the primary key is a good choice.

The next article will discuss these index-Related Optimization Strategies in detail.