Mysql最佳化之深入瞭解儲存引擎，進行索引最佳化，mysql索引

Mysql最佳化之深入瞭解儲存引擎，進行索引最佳化，mysql索引

比較兩種儲存引擎在資料庫中儲存方式：

MyIsam：仔細觀察的話會發現使用這種引擎的資料庫裡面一般少說包含三個檔案，**.frm  ,**.myi,(放索引) **.myd（放資料），通過索引（**.myi這個檔案），定位元據在資料檔案 在哪一行存放，這便會產生回行。如果沒有回行，也就是索引覆蓋，速度回非常快

InnoDb：也就是一個檔案，索引和資料放在一塊，就是 聚簇索引一個壞處就是檔案大啦，磁碟轉動，尋找也就比較低啦。這樣也就產生了分頁的塊檔案

建立表：裡麵包含主鍵索引和聯合索引,分別使用myisam引擎和innodb引擎

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;

檔案目錄分析如上：

下面再建立連個預存程序，進行插入一萬條資料

create procedure smthTest()begin declare num int default 1050;while num < 10000 doset num := num +1;insert into smth values (num ,num,'我是步','我是誰');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,'我是步','我是誰');end while ;end;

set profiling = 1; 顯示詳細資料

運行結果分析：

語句分析結果：

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