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

end;


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

end;

卡奥斯智能交互引擎

聚焦工业领域的AI搜索引擎工具

36

查看详情

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