[Turn]MYSQL index structure principle, performance analysis and optimization

The first part: basic knowledge

Index

The official introduction index is a data structure that helps MySQL efficiently get data. I understand that the index is equivalent to a book directory, through the directory to know where to go, without a page to find the information needed.

Unique index

Emphasis is unique, that is, the index value must be unique.

To create an index:

Create unique index index name on table name (column name);

ALTER TABLE name add unique index index name (column name);

To delete an index:

Drop index index name on table name;

ALTER TABLE name DROP INDEX name;

Primary key

The primary key is a unique index, the primary key is specified when the table is built, generally with the auto_increment column, the keyword is primary key

Primary Key creation:

creat table test2 (ID int not NULL primary key auto_increment);

Full-Text Indexing

InnoDB not supported, MyISAM support performance is better, typically created on CHAR, VARCHAR, or TEXT columns.

Create Table Table name (

ID int NOT NULL PRIMARY key anto_increment,

Title varchar (+), Fulltext (title)

) Type=myisam;

Single-column and multicolumn indexes

An index can be a single-column index or a multicolumn index (also called a composite index). The index created in the form above is a single-column index, so let's look at creating a multicolumn index:

CREATE TABLE Test3 (

ID int NOT NULL PRIMARY key auto_increment,

Uname char (8) NOT null default ' ',

Password char () NOT NULL,

INDEX (Uname,password)

) Type=myisam;

Note: Index (A, B, c) can be used as an index of a or (a, b), but not as an index of B, C, or (B,C). This is an optimization method with the leftmost prefix, which will be described in detail later, as long as you know there are two concepts.

Clustered index

An index in which the logical order of key values in the index determines the physical order of the corresponding rows in the table. The clustered index determines the physical order of the data in the table. MySQL MyISAM table is not clustered index, InnoDB have (primary key is clustered index), clustered index in the following INNODB structure is described in detail.

To view the index of a table

By command: Show index from table name such as:

Mysql> Show index from TEST3;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+--- -+

| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part |

Packed | Null | Index_type | Comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+--- -+

|          Test3 | 0 |        PRIMARY |    1 |     ID |   A |     0 | NULL |

NULL | |         BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+--- ---+

Table: Tables Name

Key_name: What type index (here is the primary key)

column_name: Field names for indexed columns

Cardinality: Index cardinality, a key parameter, average value group = Index cardinality/table total data row, the closer the average value group is to 1, the more likely it is to take advantage of the index

Index_type: If the index is a full-text index, then it is fulltext, here is the B+tree index, B+tree is also one of the focus of this article

Part II: MyISAM and INNODB index structure

Simple Introduction to B-tree B + Tree

B-tree structure View

The B-tree tree of a M-order has the following properties

Ki represents the keyword value, k1<k2<...<ki<k0<kn (as you can see, the left child node key value of a node < the keyword value < right child node key value)

PI represents a pointer to a child node, the left pointer points to the left Dial hand node, and the right pointer to the right child node. That is: p1[points to the value]<k1<p2[points to the value]<k2 ...

All keywords must have unique values (which is why the MyISAM and InnoDB tables have to be primary keys), each node contains a description of how many keywords of that node, such as I and N for the second row

Node:

Each node can have a maximum of M child nodes.

Root node is not a leaf node, at least 2 child nodes, up to M child nodes

Each non-root, non-leaf node is at least [M/2] child node or called subtree ([] means rounding up), up to M child nodes

Key words:

Root node number of keywords 1~m-1

Number of keywords for non-root non-leaf nodes [m/2]-1~m-1, such as m=3, the number of such node keywords: 2-1~2

The relationship between the key word K and the pointer to the number of child nodes p:

K+1=p, note that depending on the specific needs of the stored data, the left and right pointers are empty when there is a flag indicating that no b+tree structure is as follows:

B + Tree is a variant of B-tree and a multi-path search tree: * The subtree pointer of the non-leaf node is the same as the number of keywords * Add a chain pointer to all leaf nodes (arrows of the red dot flag)

MYISAM Index Structure

MyISAM refers to the B + tree to store the data, the pointer to the MyISAM index points to the address of the key value, and the address stores the data, such as:

Structure Explanation: 3 order tree, the primary key is the Col2,col value is the physical address where the row data is saved, where the red part is the description callout.

1 The callout part may be confusing, the front is not said keyword 15 the right pointer to the key value is greater than 15, how do the following 15 keywords? Because all leaf nodes of b+tree contain all keywords and are sorted in ascending order (the primary key index is unique, the secondary index can be not unique), so the data value equal to the keyword is in the right subtree

The 2 callout is the physical address where the corresponding keyword stores the corresponding data, and note that this is also one of the different places after the InnoDB index

2 The callout is also an index of the said MyISAM table and the data is detached, and the index is saved in the table name. MYI "file, while the data is saved in the" table name. MYD "In the file, the physical address of the 2 callout is the" table name. MYD "The physical address of the corresponding data within the file. (InnoDB table's index file and data file together)

There is no big difference between secondary and primary key indexes, and index values for secondary indexes can be duplicated (but there is a noticeable difference between the InnoDB secondary index and the primary key index, which is noted here first)

Innode Index Structure

(1) First there is a table, the content and the primary key index structure is as follows two graphs:

Col1

Col2

Col3

1

15

Phpben

2

20

Mhycoe

3

23

Phpyu

4

25

Bearpa

5

40

Phpgoo

6

45

Phphao

7

48

Phpxue

......

Structure: It can be seen that the index structure of InnoDB is very myisam distinct

The index and data of the MyISAM table are separate, pointing to the physical address of the data, and the indexes and data in the InnoDB table are stored together. See red Box 1 to see that one row of data is saved.

There is also a more than three rows of hidden data columns (dashed table), this is because MyISAM does not support transactions, InnoDB processing transactions in the performance of concurrency control is better, see the figure in the Red box 2 in the DB_TRX_ID is the transaction ID, automatic growth; DB_ROLL_PTR is a rollback pointer, Data rollback recovery for transaction errors; db_row_id is the record line number, which is actually the primary key value in the primary key index, where the repetition is for easy introduction, or if there is no primary key index (secondary index), db_row_id will find a unique column in the table as the value, If there is no unique column, the system automatically creates one. About InnoDB and multi-transaction MVCC point this: http://www.phpben.com/?post=72

(2) Add the Col1 in the table above is the primary key (the error), and Col2 is the secondary index, then the corresponding secondary index structure diagram:

You can see that the InnoDB Secondary index does not save all the corresponding column data, but instead saves the key values of the primary key (Figure 1, 2, 3 ...) the pros and cons are also obvious:

In the existing primary key index, to avoid data redundancy, while modifying the data only need to modify the secondary index value.

But the secondary index finds data two times, finds the corresponding primary key index value and then finds the corresponding data in the index to retrieve the primary key. This is also a lot of MySQL performance optimization mentioned in the online "primary key as short as possible" reason, the longer the primary key secondary index is larger, of course, the larger the primary key index.

The MyISAM index is more than the InnoDB index

MyISAM supports full-text indexing (fulltext), compressed indexes, INNODB not supported

InnoDB support transaction, MyISAM not supported

MyISAM sequential storage data, index leaf node to save the corresponding data row address, the secondary index is very similar to the primary key index; InnoDB primary key node holds data rows at the same time, other secondary indexes hold the value of the primary key index

MyISAM key values are detached, index loaded into memory (key_buffer_size), data cache depends on operating system, InnoDB key value is saved together, index is loaded with data in InnoDB buffer pool

MyISAM primary KEY (unique) index stores storage in ascending order, Innod

[Turn]MYSQL index structure principle, performance analysis and optimization