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

Xiaobian to share with you what the role of MySQL index is, I hope you have something to gain after reading this article, let's discuss it together!

First, create a database table:

create table single_table( id int not auto_increment, key1 varchar(100), key2 int, key3 varchar(100), key_part1 varchar(100), key_part2 varchar(100), key_part3 varchar(100), common_field varchar(100), primary key(id), #Cluster index key idx_key1(key1), #Secondary index unique key uk_key2(key2), #secondary index, and this index is the only secondary index key idx_key3(key3), #Secondary index key idx_key_part(key_part1,key_part2,key_part3) #Secondary index, also joint index)Engine=InnoDB CHARSET=utf8;1. Index is used to reduce the number of records to be scanned

For a query, the simplest and crudest execution is to scan all the records in the table and determine whether each search record meets the search criteria. If so, send it to the client, otherwise skip the record. This implementation scheme is called full table scanning.

For the InnoDB storage engine, a full table scan means starting with the first record of the first leaf node of the clustered index and scanning backwards along the one-way linked list where the record is located until the last record of the last leaf node. If you can use the B+ tree to find records with index column values equal to a certain value, you can reduce the number of records that need to be scanned.

Because the records in the leaf nodes of the B+ tree are sorted according to the order of the index column values from small to large, only the records in a certain interval or some intervals need to be scanned, which can obviously reduce the number of records to be scanned.

For query statements:

select * from single_table where id>=2 and id、=、

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.