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 set num := num +1;
insert into smth values (num ,num,'我是步','我是谁');
end while ;

end;


create procedure smthTest1()
begin
declare num int default 0;
while num set num := num +1;
insert into smth1 values (num ,num,'我是步','我是谁');
end while ;

end;

多奥淘宝客程序API免费版 F8.0

多奥淘宝客程序免费版拥有淘宝客站点的基本功能，手动更新少，管理简单等优点，适合刚接触网站的淘客们，或者是兼职做淘客们。同样拥有VIP版的模板引擎技 术、强大的文件缓存机制，但没有VIP版的伪原创跟自定义URL等多项创新的搜索引擎优化技术，除此之外也是一款高效的API数据系统实现无人值守全自动 化运行的淘宝客网站程序。4月3日淘宝联盟重新开放淘宝API申请，新用户也可使用了

下载

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