SQL Optimizer – save as much as you can

Time:2021-6-8

Please consider a question, how does SQL optimizer work?

#Create table SQL
create table why_index(
    id int unsigned primary key auto_ Increment comment 'Auto increment primary key ID',
    K1 int unsigned not null default "0" comment 'an index will be added to this field',
    C1 varchar (32) not null default "" comment 'cross reference field' otherwise ',
    index idx_k1(k1)
)Engine = InnoDB, character set utf8mb4, comment "explore optimizer select index";

#Insert data SQL
insert into why_index(k1, c1) values
(5, 'a5'),
(3, 'a3'),
(4, 'a4'),
(2, 'a2'),
(1, 'a1');

The following three SQL, are you and I think the same

select * from why_index limit 3;
#The result is [{1, 5, A5}, {2, 3, A3}, {3, 4, A4}] 
select c1 from why_index limit 3;
#The result is [A5, A3, A4] 
select k1 from why_index limit 3;
#The result is [1, 2, 3]??
#??  Why ` select K1 from why_ Is the result set of index limit 3 not [5, 3, 4]?
select id from why_index limit 3;
#Do you think the result is [1, 2, 3]?? I think so

The underlying data is represented by legend and secondary indexk1And primary keyidThe structure of leaf node chain list is as follows

SQL Optimizer - save as much as you can

The answers are as follows:
Using explain to view four SQL execution plans, we can get the following results:
Sql-1 scan primary keyidLeaf node list (take out all the fields in the tuple);
Sql-2 scan primary keyidLeaf node list (take out the fields in the tuple)c1);
Sql-3 scan indexk1Leaf node list andk1The leaf node index value is returned as the result (overlay index);
Sql-4 scan indexk1Leaf node list andk1The leaf node data value isPrimary key IDReturn as result (overlay index);

Conclusion: SQL (storage engine) will read data according to the execution plan,Note: Although the where clause is not used in the above SQL, it is easy to understand and basicSimple as it is, I hope it’s fun and rewarding

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

JQuery source code series (VII) callbacks function

WelcomeMy columnView a series of articles. To be honest, the source code of sizzle is so depressing that it was very painful during the period of writing sizzle articles. At first, I thought it was very interesting. The later I felt that the code was difficult to read and annoying. After the winter vacation, I […]