Mysql optimization: in-depth understanding of storage engines and index optimization

Compare the storage methods of two storage engines in the database:

MyIsam: If you observe carefully, you will find that databases using this engine generally contain at least three files, **.frm, **.myi, (index) **.myd (put data), through the index (**.myi file), locate the row in the data file where the data is stored, which will generate a line return. If there is no row return, that is, index coverage, the speed will be very fast

InnoDb: It is a file, the index and data are put together, that is, a disadvantage of the clustered index is that the file is large, the disk rotates, and the search is relatively slow. . This also generates a paginated block file

Create table: It contains primary key index and joint index, using myisam engine and innodb engine respectively

create table smth (
id int auto_increment ,
ver int(11) default null,
content varchar(1000) not null,
intro varchar(1000) not null,
primary key(id),
key idver(id,ver)

)engine = myisam default charset = utf8;

create table smth1 (
id int auto_increment ,
ver int(11) default null,
content varchar(1000) not null,
intro varchar(1000) not null,
primary key(id),
key idver(id,ver)

)engine = innodb default charset = utf8;
The file directory analysis is as above:

Now create two stored procedures to insert 10,000 pieces of data

create procedure smthTest ()
begin
declare num int default 1050;
while num set num := num +1;
insert into smth values ​​(num,num,'I am step','who am I');
end while ;

end;


create procedure smthTest1()
begin
declare num int default 0;
while num set num := num +1;
insert into smth1 values ​​(num ,num ,'I am step','who am I');
end while ;

end;

set profiling = 1; Show detailed information

Running result analysis:

Statement analysis Result:

mysql> explain 
 select id,ver,content from smth order by id;  
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | smth  | ALL  | NULL          | NULL | NULL    | NULL | 9946 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set
mysql> explain
 select id,ver,content from smth order by id,ver;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | smth  | ALL  | NULL          | NULL | NULL    | NULL | 9946 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set
mysql> explain
 select id,ver,content from smth1 order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | smth1 | index | NULL          | PRIMARY | 4       | NULL | 9932 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set
mysql> explain select id
,ver,content from smth1 order by id,ver;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | smth1 | ALL  | NULL          | NULL | NULL    | NULL | 9932 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

mysql> explain
 select id from smth order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | smth  | index | NULL          | PRIMARY | 4       | NULL | 9946 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set
mysql> explain
 select id  from smth order by id,ver;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | smth  | index | NULL          | idver | 9       | NULL | 9946 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set
mysql> explain
 select id from smth1 order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | smth1 | index | NULL          | PRIMARY | 4       | NULL | 9932 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set
mysql> explain
 select id from smth1 order by id,ver;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | smth1 | index | NULL          | idver | 9       | NULL | 9932 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set