Mysql优化之深入了解存储引擎，进行索引优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql优化之深入了解存储引擎，进行索引优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5858字，纯文字阅读大概需要9分钟。

内容图文

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;

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

内容总结

以上是互联网集市为您收集整理的Mysql优化之深入了解存储引擎，进行索引优化全部内容，希望文章能够帮你解决Mysql优化之深入了解存储引擎，进行索引优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端