Mysql optimization: a deep understanding of the storage engine, index optimization, mysql Index

Mysql optimization: a deep understanding of the storage engine, index optimization, mysql Index

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

MyIsam: If you observe carefully, you will find that the database using this engine generally contains less than three files ,**. frm ,**. myi, (INDEX )**. myd (put data), through the index (**. myi file) to locate the row in which the data file is stored, which generates a response. If no rows are returned, that is, index coverage, the return speed is very fast.

InnoDb: A file where the index and data are put together. The disadvantage of clustered index is that the file is too large, and the disk turns and the search is relatively low. In this way, a block file is generated by page.

Create a table: it contains the primary key index and the joint index, respectively using the myisam engine and the innodb engine.

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 follows:

Create a stored procedure and insert 10 thousand data records.

Create procedure smthTest () begin declare num int default 1050; while num <10000 doset 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 <10000 doset num: = num + 1; insert into smth1 values (num, num, 'I am step ', 'Who Am I '); end while; end;

Set profiling = 1; Show Details

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 | 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 | 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 | 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