MySQL index b-tree Type an explanation of the effectiveness and expiration of index usage

See: http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt343

When people talk about the index, if there is no particular type, it is mostly the B-tree index, which uses the B-tree data structure to store it. This index is supported by most MySQL engines. Archive engine is an exception: before 5.1 Archive does not support any indexes until 5.1 starts to support the index of a single self-increment column (a uto INCREMENT).

We use the term "b-tree" because MySQL also uses the keyword in CREATE TABLE and other statements.

However, the underlying storage engine may also use a different storage structure, for example, the NDB cluster storage engine actually uses the T-TREE structure to store such an index, even if its name is Btree1innodb, and B+tree is used. The various data structures and the variants of the Banyan tree are not covered by this book. The storage engine uses B-tree indexes in different ways, with different performance and pros and cons. For example, MyISAM uses prefix compression technology to make the index smaller, but InnoDB is stored in the original data format. Again, such as the MyISAM index refers to the row being indexed by the physical location of the data, and InnoDB refers to the row that is indexed based on the primary key. B-tree usually means that all values are stored sequentially, well and each leaf page is the same distance from the root. Figure 5-l shows an abstract representation of the B-tree index, which roughly reflects how the InnoDB index works. The structure used by MyISAM is different, but the basic idea is similar.

The B-tree index speeds up access to data because the storage engine no longer needs to perform a full table scan to get the data it needs, and instead begins the search from the root node of the index (the diagram is not drawn). A pointer to a child node is stored in the slot of the root node, and the storage engine looks down the layer based on those pointers. By comparing the values of the node pages and the values you are looking for, you can find the appropriate pointers to the underlying child nodes, which actually define the upper and lower bounds of the values in the child nodes page. The final storage engine either finds the corresponding value or the record does not exist.

Leaf nodes are particularly specific, and their pointers point to the data being indexed, not to other node pages (different engine "pointer" types). In Figure 5-1, only one node and its corresponding leaf nodes are drawn, but there may be many layer node pages between the root node and the leaf node. The depth of the tree is directly related to the size of the table.

B-tree is the sequential organization of indexed columns, so it is well suited to look up range data. For example, in a text-based index tree, it is appropriate to pass successive values in alphabetical order, so a lookup like "Find all names starting with I to K" is very efficient.

Suppose you have the following data table:

1234567

CREATETABLEPeople (last_name VARCHAR(32) NOTNULL,first_name VARCHAR(32) NOTNULL,dob dateNOTNULL,gender enum (‘m‘, ‘f‘) NOTNULL,KEY(last_name, f irst_name, dob))

For each row of data in the table, the index contains the values for the last_name, first_name, and DOB columns, and figure 5-2 shows how the index organizes the storage of the data.

Note that the index sorts multiple values based on 4th. The order of the columns in the Tetable statement when the index is defined. Take a look at the last two entries, and the names of the two people are the same as the names, and they are sorted according to their date of birth.

A query type that can use the B-tree index. B-tree indexes apply to full-key values, key-value ranges, or key-prefix lookups where the key-prefix lookup applies only to lookups based on the leftmost prefix. The indexes described earlier are valid for queries of the following type.

Full value Matching

Full-value matching refers to matching all the columns in the index, for example, the index mentioned earlier can be used to find people named Cubaallen, born in 1960-01-01.

Match left-most front

The index mentioned earlier can be used to find all people with the surname Allen, that is, using only the first column of the index. The match column pre-level can also match only the beginning of a column's value. For example, the index mentioned earlier can be used to find all people with a surname beginning with J. Only the first column of the index is used here.

Match Range value

For example, the index mentioned earlier can be used to find people whose surname is between Allen and Barrymore. Only the first column of the index is used here.

Match exactly one column and range match another column

The previously mentioned index can also be used to find all people whose surname is Allen and whose name is the beginning of the letter K (such as Kim, Karl, etc.). That is, the first column last_name full match, and the second column first_name the range.

Query that accesses the index only

B-tree can often support "indexed queries only", where queries only need to access the index without having to access the data rows. We will discuss the optimization of this "overlay index" separately later.

Because the nodes in the index tree are ordered, the index can also be used for an order-by operation in the query (in sequential lookup), in addition to lookup by value. In general, if B-tree can find a value in some way, it can also be used in this way for sorting. Therefore, if the 0-yang Erby clause satisfies several of the query types listed earlier, the index can also meet the corresponding ordering requirements.

Here are some limitations on the B-tree index:

? If you do not start the search by the leftmost column of the index, you cannot use the index. For example, the index in the example above is for each person who looks for bill, and is not afraid to look for a particular birthday, because neither column is the leftmost data column. Similarly, there is no war to find the person whose last name ends with a letter.

? columns in the index cannot be skipped. That is, the index described earlier cannot be used to find a person whose surname is Smith and is born on a specific date. If you do not specify a name (first_name), MySQL can use only the first column of the index.

? If a query has a range of columns (like between > < calculate range queries), none of its right columns can use index-optimized lookups. For example there is a query WHERE lastname= ' Smith ' and FirstName like '%j% ' and dob= ' 1976-12-23 ', this query can use only the first two columns of the index, because the like here is a scope condition (but the server can use the rest of the columns for other purposes). If the number of range query column values is limited, you can replace the scope criteria by using multiple equals conditions. In the index case Study section of this chapter, we will demonstrate a detailed case.

Here the reader should be able to understand how important the order of the indexed columns mentioned earlier is: These restrictions are related to the order of the indexed columns. When optimizing performance, it may be necessary to use the same columns but with different sequential indexes to meet different types of query requirements.

There are also limitations that are not caused by b-tree itself, but by the way the MySQL optimizer and the storage engine use indexes, which may no longer be limited in future releases.

MySQL index b-tree Type an explanation of the effectiveness and expiration of index usage