MySQL optimization for an in-depth understanding of the storage Engine for index optimization

Compare the storage engines stored in the database in two ways:

MyIsam: Careful observation will find that the database using this engine is generally less said to contain three files, **.frm, **.myi, (Put index) **.myd (put data), through the index (**.myi this file), the location of data in the data file in which row stored, this will produce a return line. If there is no return line, that is, index overlay, speed back very quickly

InnoDb: That is, a file, index and data in one piece, is clustered index a disadvantage is that the file is large, disk rotation, find is relatively low. This also results in a paging block file

CREATE TABLE: Contains primary key index and federated index, using MyISAM engine and InnoDB engine respectively

CREATE TABLE smth (id int auto_increment, ver int (one) default null,content varchar () not Null,intro varchar ($) Not Null,primary key (ID), key idver (id,ver)) engine = MyISAM default CharSet = UTF8;

CREATE TABLE smth1 (id int auto_increment, ver int (one) default null,content varchar () not Null,intro varchar ($) Not Null,primary key (ID), key idver (id,ver)) engine = InnoDB default CharSet = UTF8;

File directory analysis as above:

Next, create a single stored procedure to insert 10,000 data

CREATE PROCEDURE smthtest () BEGIN declare num int default 1050;while num < 10000 doset num: = num +1;insert into smth VA Lues (num, num, ' I am a 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 val UES (num, num, ' I am a step ', ' Who am I '); end while; end;

Set profiling = 1; Show more information

Run result Analysis:

Statement Analysis results:

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

MySQL optimization for an in-depth understanding of the storage Engine for index optimization