Use of B + tree indexes in MySQL

1) Use of B + Tree index in different applications

For OLTP applications, since data volume acquisition may be a small fraction of the time, building a B + Tree index is an objection

For OLAP applications, the situation is complicated because the addition of the index should be macro rather than microscopic.

2) Federated Index

Indexes multiple columns on a table. Federated indexes are created in the same way that multiple indexes create them. The difference is that there are multiple index pages

CREATE TABLE  int int, PRIMARY key (a), key Idx_a_b (A, B)) ENGINE=INNODB

In essence, a federated index is a B + tree, and the number of key values for different federated indexes is not 1, but is greater than or equal to 2.

Discuss a joint index consisting of 2 integer columns, a, B, respectively

You can see that the key values are sequential, and all the data can be read logically sequentially by the leaf node (2,1) (2,4) (3,1) (3,2). Data is stored in (b) Order

Therefore, for query select * FROM TABLE WHERE a = 1 and b=2, it is clear that the (a, b) union index can be used. For a single row a query select * FROM table where a = 1, this (a, b) index can also be used, but for column B select * from TABLE WHERE B = 2 You cannot use the B + Tree index. You can find that the value of column B on the leaf node is 1 2 1 4 1 2 is not sequential. Therefore, the query for column B is an index that is not used (A, B)

The second benefit of a federated index is that the second key value is already sorted. For example, in many cases, you need to query a user's shopping situation, and sort the last three purchases by time, using a federated index to avoid a multiple-order operation. Because the index itself is already sorted on the leaf node.

CREATE TABLEBuy_log (useridINTUNSIGNED not NULL, Buy_date date) ENGINE=INNODB;INSERT  intoBuy_logVALUES(1,'2009-01-01');INSERT  intoBuy_logVALUES(2,'2009-01-01');INSERT  intoBuy_logVALUES(3,'2009-01-01');INSERT  intoBuy_logVALUES(1,'2009-02-01');INSERT  intoBuy_logVALUES(3,'2009-02-01');INSERT  intoBuy_logVALUES(1,'2009-03-01');INSERT  intoBuy_logVALUES(1,'2009-04-01');ALTER TABLEBuy_logADD KEY(userid);ALTER TABLEBuy_logADD KEY(userid,buy_date);

Two indexes were established for comparison. All contain the UserID.

SELECT *  from WHERE UserID=2;

View the selection of the optimizer

You can see that two indexes are available. The optimizer eventually uses the UserID, however, because the leaf node of the index contains a single key value. So theoretically a page can hold more records.

Assume that you want to remove the last 3 records of userid=1

SELECT *  from WHERE UserID=1ORDERbyDESC3;

This time the optimizer uses the (userid,buy_date) federated index Userid_2 because Buy_date is already sorted in this federated index. The data is extracted from the federated index without having to do an additional sort operation on the buy_date. If you force the UserID index to be used

SELECT *  from INDEX WHERE UserID=1ORDERbyDESC3;

You can see the using Filesort, which requires an additional sort operation to complete the query. is obviously sort of buy_date. Because the buy_date in the index userid is unsorted

The Federated Index (A, a, b) is actually sorted according to column A, so the following statement can be used to get results directly using a federated index

SELECT *  from WHERE a=1ORDER by B;

For federated Indexes (A,B,C) You can also get results directly from a federated index

 EXPLAIN select  *  from  table  where  a=  1  and  b Span style= "color: #808080;" >=  1  order  Span style= "color: #0000ff;" >by   C; EXPLAIN  select  *  from  table  where  a=  1  order  Span style= "color: #0000ff;" >by  B; 

However, for the following statement, you cannot get the result, you need to perform a filesort sort operation because (A,C) is not sorted

SELECT *  from TABLE WHERE a=1ORDER by C;

3) Overlay Index

The InnoDB storage engine supports overwriting indexes, which can be queried from a secondary index to records without querying the records in the clustered index. One benefit of using an overlay index is that the secondary index does not contain all the information for the entire row of records, so its size is much smaller than the clustered index. So you can reduce the number of IO operations

For the secondary index of the InnoDB storage engine, because it contains primary key information, its leaf node holds data (parimary key1,parimary key2,... key1,key2,...) For example, the following statement can use only one secondary federated index to complete a query

SELECTKey2 from Table WHEREKey1=xxx;SELECT PrimaryKey2,key2 from TableKey1=xxx;SELECT PrimaryKey1,key2 from TableKey1=xxx;SELECT PrimaryKey1,PrimaryKey2,key2 from TableKey1=xxx

Another benefit of overwriting an index is that for some statistical problems, such as the buy_log above, the following query

SELECT COUNT (* from Buy_log;

The InnoDB storage engine does not choose to count by querying a clustered index. Because Buy_log also has secondary indexes, the secondary index is much smaller than the clustered index. Selecting secondary indexes can reduce IO operations.

Show. Possible_keys is null, but the actual execution optimizer chooses the UserID, and the using index of the column extra is the action that represents the optimizer's selection of the overwrite index

In addition, in general, (A, b) of the federated Index, half is not able to select the so-called query condition in column B, but if it is a statistical operation, and is overwritten by the index. The optimizer makes a selection

SELECT COUNT (*from WHERE buy_date>='2011-01-01'and buy_date<'2011-02-01';

Table Buy_log has a (userid,buy_date) federated Index, where only the conditional query is based on B, which is normally not possible with the federated index. But this SQL statement query is a statistical operation. And you can take advantage of the information that overrides the index. Therefore, the optimizer chooses the federated index.

Use of B + tree indexes in MySQL