"Reprint" MySQL index optimization

MySQL official definition of index: index is the data structure that helps MySQL to get data efficiently. Indexes are implemented in the storage engine, so the indexes in each of the storage engines are different. such as the MyISAM and InnoDB storage engines support only btree indexes; memory and heap storage engines can support hash and btree indexes.

Only the Btree indexes supported by the commonly used INNODB storage engine are described here:

I. Type of index

First create a new table to demonstrate the index type

CREATE TABLE index_table (    ID BIGINT not NULL auto_increment COMMENT ' primary key ',    name VARCHAR (Ten) COMMENT ' name ',    age INT COMMENT ' age ',    phonenum CHAR (one) COMMENT ' mobile number ',    PRIMARY KEY (id)) ENGINE = INNODB auto_increment = 1 DEFAULT CHARSET = UTF8;

Col2 is the index column, and the corresponding graph of the B-tree structure is recorded, for reference only:

1. General Index

This is the most basic index, without any restrictions.

------CREATE INDEX index_name on index_table (name) directly;

2. Unique index

The value of the indexed column must be unique and can have a null value

---------Create a unique index directly on the Index_phonenum on Index_table (Phonenum);

3. Primary key

is a special unique index that must be specified as PRIMARY key, such as our common auto_increment self-increment primary key

4. Multi-column index

Also known as a composite index, which is the union of one index on multiple fields

-------CREATE index index_union on index_table (name,age,phonenum) directly by creating the combined indexes;

Here is a combined index, equivalent to the following three indexes:

Name

Name,age;

Name,age,phonenum;

There may be a doubt here: why there is no index on the age or Age,phonenum field. This is because the Btree index is subject to the principle of the leftmost prefix, which is expanded in detail later.

Second, index optimization 1, select the index column

Creating an index is simple, but creating an index on which columns requires a good thought. You can consider a column in the WHERE clause or an index on the columns that appear in the join clause

SELECT Age    ----Do not use the index from    index_unionwhere    NAME = ' xiaoming '---Consider using the index and phonenum = ' 18668247687 ';--- Consider using an index

2. The leftmost prefix principle

Union Index (Name,age,phonenum), B + trees are built in order from left to right to build the search tree. such as (' Zhang San ', 18, ' 18668247652 ') to retrieve the data, the B + tree will first match the name to determine the direction of the search, the name match successfully followed by the age, Phonenum, and finally retrieve the final data. In this case there is a three-level index, when the name is the same, find Age,age also, to compare phonenum; but if you retrieve it (18, ' 18668247652 '), the B + tree does not have a first-level index, and the next search direction cannot be determined at all. (' Zhang San ', ' 18668247652 ') This is also the case, when the name match succeeds, there is no age of this level two index, only under the same name, to go through all the phonenum.

The data structure of the B + tree determines that the leftmost prefix principle must be adhered to when using the index, and when creating a federated index, try to place the field that frequently participates in the query on the leftmost side of the federated Index.

3, like the use of

In general, it is not recommended to use the like operation, if the non-use is not possible, you need to note: Like '%abd% ' does not use the index, and like ' aaa% ' can use the index. This is also a usage scenario for the previous leftmost prefix principle.

4. Cannot use index description

MySQL matches the federated index from left to right until a range query is encountered, such as >,<,between,like, a = 1 and b =2 and C > 3 and D = 4, if an index of (A,B,C,D) order is established, D is not will use the index. But if the union index is (A,B,D,C), then a B d C can be used to the index, but eventually C is a range value.

5. ORDER BY

ORDER BY ordering has two sorts: using Filesort uses algorithms to sort in memory and uses MySQL to sort the indexes, and we want to use indexes in some cases.

1	selecttest_index where id = 3 order byid desc;

If the ID is a single-column index, the order by uses the index

1	selecttest_index where id = 3 order by namedesc;

If the ID is a single-column index, name is not an index, or name is a single-column index, the order by does not use the index. Because one query for MySQL only selects an index from many indexes, this time the query uses the ID column index instead of the Name column index. In this scenario, if you want the order by to also use the index, create a federated index (id,name), where you need to pay attention to the leftmost prefix principle, and do not create such a federated index (NAME,ID).

Finally, it is important to note that MySQL has a limit on the size of the sort record: max_length_for_sort_data defaults to 1024, which means that if the amount of data that needs to be sorted is greater than 1024, order by does not use the index, but instead uses the using Filesort.

Category: MySQL Tags: index optimization good text to the top attention to my collection of the text wax gourd Cai
Follow-3
Fans-47 + plus attention00? Previous post: What is b+treeposted @2017-07-29 11:41 Winter melon Tsai Read (80) Comments (0) Edit Collection

"Reprint" MySQL index optimization