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

This article mainly introduces what is the use of MySQL index, has a certain reference value, interested friends can refer to, I hope you read this article after a great harvest, the following let Xiaobian take you to understand.

Index 1. Advantages of Index

(1) Improve query efficiency (reduce IO usage)

(2) Reduce CPU usage

For example, query order by age desc, because the B+ index tree itself is in good order, so if you query again, if you trigger the index, you don't have to query again.

2. Disadvantages of index

(1) The index itself is large and can be stored in memory or on a hard disk, usually on a hard disk.

(2) Index is not used in all cases, such as ① a small amount of data ② frequently changing fields ③ rarely used fields

(3) Index will reduce the efficiency of addition and deletion

3. Classification of index

(1) Single-valued index

(2) Unique index

(3) Joint index

(4) Primary key index

Note: Unique index and primary key index Unique difference: primary key index cannot be null

4. Create index alter table user add INDEX `user_index_username_password` (`username`,`password`)

MySQL index principle-> B+ tree

The underlying data structure of MySQL index is B+ tree

B+Tree is an optimization based on B-Tree, making it more suitable for implementing external storage index structure. InnoDB storage engine uses B+Tree to implement its index structure.

Each node in the B-Tree structure diagram contains not only the key value of the data, but also the data value. However, the storage space of each page is limited. If the data is large, the number of keys that can be stored in each node (i.e., a page) will be small. If the amount of data stored is large, the depth of the B-Tree will also be large, increasing the number of disk I/O times during query, and thus affecting query efficiency. In B+Tree, all data record nodes are stored on leaf nodes of the same layer according to the order of key value, while non-leaf nodes only store key value information, which can greatly increase the number of key values stored in each node and reduce the height of B+Tree.

B+Tree differs from B-Tree in several ways:

Non-leaf nodes store only key information.

There is a chain pointer between all leaf nodes.

Data records are stored in leaf nodes.

Optimize the B-Tree in the previous section. Since the non-leaf nodes of the B+Tree only store key information, assuming that each disk block can store 4 key values and pointer information, the structure of the B+Tree is as follows:

Usually there are two head pointers on a B+Tree, one pointing to the root node and the other to the leaf node with the smallest key, and all leaf nodes (i.e., data nodes) are in a chained ring structure. Therefore, two lookup operations can be performed on B+Tree: one is range lookup and paging lookup for primary key, and the other is random lookup starting from root node.

Perhaps there are only 22 data records in the above example, and the advantages of B+Tree cannot be seen. Here is an estimation:

The size of a page in the InnoDB storage engine is 16KB, the primary key type of a table is INT (4 bytes) or BIGINT (8 bytes), and the pointer type is generally 4 or 8 bytes. That is to say, a page (a node in the B+Tree) stores about 16KB/(8B+8B)= 1K key values (because it is an estimation, for convenience of calculation, K here is taken as <$10 <$^3). That is, a B+Tree index with depth 3 can maintain 10^3 * 10^3 * 10^3 = 1 billion records.

In practice, each node may not be filled, so in the database, the height of the B+Tree is generally 2~4 layers. MySQL's InnoDB storage engine is designed to have the root node resident in memory, which means that it only needs 1~3 disk I/O operations to find the row record of a certain key value.

B+Tree indexes in databases can be classified into clustered indexes and secondary indexes. The above B+Tree example graph is implemented in the database as a clustered index, and the leaf nodes in the B+Tree of the clustered index store the row record data of the whole table. The difference between an auxiliary index and a clustered index is that the leaf node of the auxiliary index does not contain all the data of the row record, but stores the clustered index key of the corresponding row data, i.e., the primary key. When querying data through a secondary index, the InnoDB storage engine traverses the secondary index to find the primary key, and then finds the complete row record data in the clustered index through the primary key.

Thank you for reading this article carefully. I hope that the article "What is the use of indexes in MySQL" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

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.