What are the uses of indexes in MySQL

Index

1. Advantages of index

(1) Improve query efficiency (reduce IO usage)

(2) Reduce CPU Usage rate

For example, querying order by age desc, because the B index tree itself is sorted, so if the index is triggered by the query, there is no need to query again.

2. Disadvantages of indexes

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

(2) Indexes are not used in all situations, such as ① a small amount of data ② frequently changing fields ③ rarely used fields

(3) Indexes will reduce the efficiency of additions, deletions and modifications

3. Index classification

(1) Single value index

(2) Unique index

(3) Union index

(4) Primary key index

Note: The only difference between unique index and primary key index: primary key index cannot be null

4. Create index

alter table user add INDEX `user_index_username_password` (`username`,`password`)

5. MySQL index principle-> B tree

The underlying data structure of MySQL index is B tree

B Tree is in B- An optimization based on Tree makes it more suitable for implementing external storage index structures. The 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. The storage space of each page is limited. If the data data is large, the number of keys that can be stored in each node (i.e. one page) will be very small. When the amount of stored data is large, it will also lead to B- The depth of Tree is larger, which increases the number of disk I/Os during query, thereby affecting query efficiency. In B Tree, all data record nodes are stored on leaf nodes of the same layer in order of key value. Only key value information is stored on non-leaf nodes. This can greatly increase the number of key values ​​stored in each node. Reduce the height of B Tree.

B Tree has several differences compared to B-Tree:

Non-leaf nodes only store key value information.
There is a link 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 B Tree only store key value information, assuming that each disk block can store 4 key values ​​and pointer information, it will become the structure of B Tree. As shown in the figure below:

Usually there are two head pointers on the B Tree, one points to the root node, the other points to the leaf node with the smallest keyword, and all leaf nodes ( That is, there is a chain ring structure between data nodes). Therefore, two search operations can be performed on B Tree: one is a range search and paging search for the primary key, and the other is a random search starting from the root node.

Maybe there are only 22 data records in the above example, and the advantages of B Tree cannot be seen. Here is a calculation:

The page size in the InnoDB storage engine is 16KB, and the primary key type of the general table It is INT (occupies 4 bytes) or BIGINT (occupies 8 bytes), and the pointer type is generally 4 or 8 bytes, which means that one page (a node in B Tree) stores approximately 16KB/( 8B 8B) = 1K key values ​​(because it is an estimate, to facilitate calculation, the value of K here is 〖10〗^3). In other words, a B Tree index with a depth of 3 can maintain 10^3 * 10^3 * 10^3 = 1 billion records.

In actual situations, each node may not be fully filled, so in the database, the height of B Tree is generally between 2 and 4 levels. MySQL's InnoDB storage engine is designed so that the root node is resident in memory, which means that only 1 to 3 disk I/O operations are needed to find the row record of a certain key value.

The B Tree index in the database can be divided into clustered index (clustered index) and auxiliary index (secondary index). The above B Tree example diagram is implemented in the database as a clustered index. The leaf nodes in the B Tree of the clustered index store the row record data of the entire table. The difference between an auxiliary index and a clustered index is that the leaf nodes of the auxiliary index do not contain all the data of the row record, but the clustered index key that stores the corresponding row data, that is, 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.

The above is the detailed content of What are the uses of indexes in MySQL. For more information, please follow other related articles on the PHP Chinese website!