Shulou(Shulou.com)06/01 Report--

I believe that most people do not understand the difference between MyISAM and InnoDB index, today Xiaobian in order to let you know more about the difference between MyISAM and InnoDB index, to sum up the following content, follow the editor to have a look.

The B+ tree is a data structure that is very suitable for database indexing:

(1) it is very suitable for disk storage and can make full use of the locality principle and disk pre-reading.

(2) very low tree height, able to store a large amount of data

(3) the memory occupied by the index itself is very small.

(4) it can well support single point query, range query and ordered query.

The index of the database is divided into primary key index (Primary Inkex) and normal index (Secondary Index).

One, the index of MyISAM

MyISAM indexes are stored separately from row records, called nonclustered indexes (UnClustered Index).

There is no essential difference between its primary key index and a normal index:

Areas with continuous aggregations store row records separately

The leaf node of the primary key index, which stores the primary key and the pointer to the corresponding row record

The leaf node of a general index that stores index columns and pointers to corresponding row records

Voiceover: MyISAM tables can have no primary key.

The primary key index and the ordinary index are two independent index B + trees. When searching through the index column, we first locate the leaf node of the B + tree, and then locate the row record through the pointer.

For example, MyISAM:

T (id Competition, name KEY, sex, flag)

There are four records in the table:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

The B + tree index is constructed as shown in the figure above:

Row records are stored separately

Id for competition, there is an index tree of id, and the leaf points to the row record.

Name is KEY, there is an index tree of name, and the leaves also point to the row record.

II. Index of InnoDB

The primary key index and row records of InnoDB are stored together, so it is called clustered index (Clustered Index):

There is no separate area to store row records

The leaf node of the primary key index, which stores the primary key and records with the corresponding rows (rather than pointers)

Voiceover: therefore, InnoDB's competitive query is very fast.

Because of this feature, InnoDB tables must have clustered indexes:

(1) if the table defines competition, then the competition is a clustered index.

(2) if the table does not define contention, the first non-empty unique column is a clustered index

(3) otherwise, InnoDB will create a hidden row-id as a clustered index

There can be only one clustered index, because data rows can only have one copy of clustered storage on the physical disk.

InnoDB can have multiple normal indexes, which are different from clustered indexes:

The leaf node of a normal index that stores the primary key (not a pointer)

For the InnoDB table, the revelation here is:

(1) it is not recommended to use longer columns as primary keys, such as char (64), because all ordinary indexes store primary keys, which can cause ordinary indexes to be too large.

(2) it is recommended to use the key with increasing trend as the primary key, because the data row is integrated with the index, so that when the record is inserted, a large number of indexes are split and the row records move.

It is still the example above, except that the storage engine is replaced with InnoDB:

T (id Competition, name KEY, sex, flag)

There are still four records in the table:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

The B + tree index is constructed as shown in the figure above:

Id is competitive, row records and id index trees are stored together

Name is KEY, has an index tree of name, and leaves store id

When:

Select * from t where name='lisi'

The name secondary index is used to locate the leaf node of the B+ tree to get the id=5, and then the clustered index is used to locate the row record.

Voiceover: so, actually swept the index tree twice.

After reading the above, do you have a general idea of the difference between the indexes of MyISAM and InnoDB? If you want to know more about the content of the article, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.