【myISAM和innoDB】mySql的引擎myisam和innodb的区别/mysiam（mysql插入速度优化）

bandaoyu

已于 2022-09-17 03:21:10 修改

阅读量1.6k

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 sql

于 2018-03-11 21:06:16 首次发布

本文链接：https://blog.csdn.net/bandaoyu/article/details/83312179

版权

数据库专栏收录该内容

43 篇文章 9 订阅

订阅专栏

Myisam 和Innodb 特点和擅长

Myisam 和Innodb 数据存储区别

Myisam引擎(非聚集索引--叶子节点存储数据地址的指针)

Innodb引擎(聚集索引---叶子节点的data直接包含数据)

mysql autocommit对myisam,innodb的性能影响

引擎在不断的发展(比如InnoDB发展），所以不会去记具体的特性，在选型的时候才去看和对比。

简单点说。。。

没啥特殊的话请使用innodb。 myisam已被放弃。

（mySQL 选择B+树作为索引的数据结构Mysql的实现原理 - 海棠--依旧 - 博客园）

Myisam 和Innodb 特点和擅长

读操作多用 MyISAM
写操作多用 InnoDB

1、myisam查询效率更高，查询效率差myisam6-7倍。

2、innodb支持事务，行锁，外键。myisam不支持。

如果数据表涉及的存储数据多、查询多，用myisam，如文章表。如果数据表涉及业务逻辑多，增删改操作多，就用innodb，如订单表。

区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5、InnoDB支持行锁

6、InnoDB：5.6以后才有全文索引;

　MyISAM：支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.

如何选择：

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

推荐用InnoDB引擎.加了索引之后能够大幅度的提高查询速度，但是索引也不是越多越好，一方面它会占用存储空间，另一方面它会使得写操作变得很慢。通常我们对查询次数比较频繁，值比较多的列才建索引。
　　例如：select * from user where sex = "女"，这个就不需要建立索引，因为性别一共就两个值，查询本身就是比较快的。
　　　　　select * from user where user_id = 1995 ,这个就需要建立索引，因为user_id的值是非常多的。

摘自：https://www.zhihu.com/question/20596402

Myisam 和Innodb 数据存储区别

（2018-03-11 21:06:16）

Myisam引擎(非聚集索引--叶子节点存储数据地址的指针)

　　若以这个引擎创建数据库表Create table user （…..），它实际是生成三个文件：

　　user.myi 索引文件 user.myd数据文件 user.frm数据结构类型。

　　如下图：当我们执行 select * from user where id = 1的时候，它的执行流程。

　　　　(1)查看该表的myi文件有没有以id为索引的索引树。

　　　　(2)根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。

　　　　(3)根据数据地址去myd文件里面找到对应的数据返回出来。

Innodb引擎(聚集索引---叶子节点的data直接包含数据)

　　若以这个引擎创建数据库表Create table user （…..），它实际是生成两个文件：

　　user.ibd 索引文件 user.frm数据结构类型

　　因为innodb引擎创建表默认就是以主键为索引，所以不需要myi文件。

　　下图为innodb表的结构图：很显然它与myisam最大的区别是将整条数据存在叶子节点，而不是地址。(叶子节点存的是主键索引和数据信息)

　　若此时，你在其他列创建索引例如name，它就会另外创建一个以name为索引的索引树，(叶子节点存的是索引和主键索引)。

　　你在执行select * from user where name = ‘吴磊’，他的执行过程如下：

　　　　(1)找到name索引树

　　　　(2)根据name的值找到该树下叶子的name索引和主键值

　　　　(3)用主键值去主键索引树去叶子节点到该条数据信息

mysql索引实现原理 - 吴磊的 - 博客园

B+Tree的特性

　　(1)由图能看出，单节点能存储更多数据，使得磁盘IO次数更少。

　　(2)叶子节点形成有序链表，便于执行范围操作。

　　(3)聚集索引中，叶子节点的data直接包含数据；非聚集索引中，叶子节点存储数据地址的指针。

行锁和表锁的区别

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM表锁

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

常用命令和性能对比

（2015-06-25 03:25:22）

【myISAM和innoDB】mySql的引擎myisam和innodb的区别/mysiam_bandaoyu的博客-CSDN博客

常用命令：

　　（1）查看表的存储类型（三种）：

show create table tablename
show table status from dbname where name=tablename
mysqlshow -u user -p password --status dbname tablename

　　（2）修改表的存储引擎：

alter table tablename type=InnoDB

　　（3）启动mysql数据库的命令行中添加以下参数使新发布的表都默认使用事务：

--default-table-type=InnoDB

　　（4）临时改变默认表类型：

set table_type=InnoDB
show variables like 'table_type'

性能：

Mysql数据库Innodb与MyISAM的性能对比测试

（原文：Mysql数据库Innodb与MyISAM的性能对比测试_ghosc的博客-CSDN博客）

由于近期有个项目对系统性能要求很高，技术选型上由于种种原因已经确定使用Mysql数据库，接下来就是要确定到底使用哪种存储引擎。我们的应用是典型的写多读少，写入内容为也很短，对系统的稳定性要求很高。所以存储引擎肯定就定在广泛使用的Innodb和MyISAM之中了。

至于两者的比较网上也有很多，但是毕竟这个事情也不复杂，决定还是自己来做，去验证一下在我们的场景下谁更优。

测试的版本是mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)，使用的是Innodb plugin 1.0.8（官方称比built-in版本性能更好）和默认的MyISAM。

测试机器是我的笔记本，配置如下：Intel 酷睿2双核 P8600，2G*2 DDR3 1066内存，320G硬盘5400转。

测试一：数据插入性能测试，这里我分别对innodb_flush_log_at_trx_commit参数（innodb_flush_log_at_trx_commit对性能的影响见文章后附录1）打开和关闭都测了了一下，每次测试都是运行40s，表中数字都是实际插入条数。

MyISAM Innodb (打开) Innodb (关闭)

单线程，逐个插入 120000 60000 60000

4线程，逐个插入 40000*4 20000*4 40000*4

单线程，批量100条/次插入 3600*100 800*100 3000*100

单线程，批量200条/次插入 1800*200 400*200 1600*200

可以发现批量插入的性能远高于单条插入，但是一次批量的大小对性能影响不大。每条记录是否都刷新日志的参数对innodb性能的影响巨大。总体上来说，MyISAM性能更优一点。这里有一点需要注意，在插入测试过程中，我对系统资源进行了监控，发现MyISAM对系统资源占用很低，但是Innodb对磁盘占用却很高，应该是对事务控制多了很多需要记录的日志。

测试二：数据读取性能测试。每次随机读取1000条记录，反复进行读取。

MyISAM Innodb

单线程，200次读取 5.7s 16.7s

4线程，200次读取 12s 40.8s

可以看出MyISAM的读取性能非常恐怖，性能差距在3倍的样子。

以上两个测试发现MyISAM在无事务的需求下几乎完胜，但是要知道它是表锁，Innodb是行锁，那么在并发读写同时存在的情况下，那结果会是怎么样呢？！

测试三：两个线程并发写入，2个线程并发读取。

MyISAM Innodb

逐个插入写入40s：10000*2 读取200次*2：14s 写入40s：60000*2 读取200次*2：50s

批量100条/次插入写入40s：1000*100*2 读取200次*2：10s 写入40s：1500*100*2 读取200次*2：50s

这下立刻显示出Innodb在并发情况下强劲的性能，几乎没有什么性能衰减。而MyISAM单条插入速度变得非常慢，批量插入也下降了40%性能。

总结一下，在写多读少的应用中还是Innodb插入性能更稳定（行级锁），在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。

另外提一下，这里需要用到对Innodb的热备份，除了用Master-Slave的方式，还可以选用XtraBackup这个开源软件。

对于支持事务的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。MySQL MyISAM/InnoDB高并发优化经验

附录1：《innodb_flush_log_at_trx_commit 对性能的影响》

（原文：mysql mysql的innodb_flush_log_at_trx_commit参数深有体会：mysql mysql的innodb_flush_log_at_trx_commit参数深有体会_fengbangyue的博客-CSDN博客_innodb_flush_log_at_trx_commit）

今天本来是想测试一下在使用触发器的情况下在一个表中插入1百万条数据和用代码实现有多大差别。但没想到插入语句一直执行不完，而观察安装MySQL的服务器CPU使用才5%左右。

这么慢的速度实在等不下去了，于是把innodb_flush_log_at_trx_commit参数调整为2.哈，好家伙，CPU占用马上跳到了70%左右，果然变快了。而且我的数据一会儿就插入完成了。于是深刻地明白了，以前在innodb_flush_log_at_trx_commit为1的时候，大多数时间CPU都在等待日志的读写。

下面是该参数的三个可选值的说明。

innodb_flush_log_at_trx_commit （这个很管用）

抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

附录2

mysql autocommit对myisam,innodb的性能影响

前段时间把数据库的部分myisam表转变成了innodb了,感觉慢了好多。我知道autocommit对innodb性能有一定的影响，但不知道影响有这么大。如何关闭autocommit，请参考mysql禁用autocommit,以及遇到的问题，为了解决这个问题，我做了一些测试，包括autocommit对myisam,innodb影响。

一，测试autocommit对myisam的影响

1,准备测试表和数据

查看复制打印 ?

mysql> CREATE TABLE `test_test` ( //测试表
-> `id` int(11) NOT NULL auto_increment,
-> `num` int(11) NOT NULL default '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ||
mysql> create procedure p_test(pa int(11))
-> begin
->
-> declare max_num int(11) default 100000;
-> declare i int default 0;
-> declare rand_num int;
->
-> select count(id) into max_num from test_test;
->
-> while i < pa do
-> if max_num < 100000 then
-> select cast(rand()*100 as unsigned) into rand_num;
-> insert into test_test(num)values(rand_num);
-> end if;
-> set i = i +1;
-> end while;
-> end||
Query OK, 0 rows affected (0.03 sec

mysql>  CREATE TABLE `test_test` (     //测试表
 ->   `id` int(11) NOT NULL auto_increment,
 ->   `num` int(11) NOT NULL default '0',
 ->    PRIMARY KEY  (`id`)
 ->  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ||
mysql> create procedure p_test(pa int(11))
 ->  begin
 ->
 ->   declare max_num int(11) default 100000;
 ->   declare i int default 0;
 ->   declare rand_num int;
 ->
 ->   select count(id) into max_num from test_test;
 ->
 ->   while i < pa do
 ->           if max_num < 100000 then
 ->                   select cast(rand()*100 as unsigned) into rand_num;
 ->                   insert into test_test(num)values(rand_num);
 ->           end if;
 ->           set i = i +1;
 ->   end while;
 ->  end||
Query OK, 0 rows affected (0.03 sec

2,测试autocommit开启的情况

查看复制打印 ?

mysql> call p_test(100000)|| //插入10000条数据
Query OK, 1 row affected (0.86 sec)
mysql> truncate table test_test; //清空表
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table test_test; //优化一下表，收回资源，确保测试的公平性

mysql> call p_test(100000)||        //插入10000条数据
Query OK, 1 row affected (0.86 sec)

mysql> truncate table test_test;     //清空表
Query OK, 0 rows affected (0.00 sec)

mysql> optimize table test_test;    //优化一下表，收回资源，确保测试的公平性

这样我连续做了三次测试，平均一下插入10000的数据差不多要0.86秒。关于optimize来优化表，请参考optimize table在优化mysql时很重要

3,autocommit关闭的情况下

查看复制打印 ?

mysql> call p_test(100000)|| //插入10000条数据
Query OK, 1 row affected (0.83 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table test_test; //清空表
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table test_test; //优化一下表，收回资源，确保测试的公平性

mysql> call p_test(100000)||        //插入10000条数据
Query OK, 1 row affected (0.83 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table test_test;     //清空表
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> optimize table test_test;    //优化一下表，收回资源，确保测试的公平性

这样我连续做了三次测试，平均一下插入10000的数据差不多要0.83秒。为了使init_connect='SET autocommit=0'启作用，我是换了个用户测试的。如果在执行储存过程的时候遇到这样的问题，

ERROR 1370 (42000): execute command denied to user 'mysql'@'localhost' for routine 'test.p_test'

解决办法是：grant execute on procedure p_test to 'mysql'@localhost;

由上面的测试数据我们可以看出，autocommit对myisam没有多大的影响。

二，测试autocommit对innodb的影响

1,测试autocommit开启的情况

查看复制打印 ?

mysql> alter table test_test type=innodb; //将表改为innodb
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> call p_test(10000); //插入数据
Query OK, 1 row affected (16.32 sec)
mysql> truncate table test_test; //删除数据
Query OK, 0 rows affected (0.02 sec)

mysql> alter table test_test type=innodb;          //将表改为innodb
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> call p_test(10000);                       //插入数据
Query OK, 1 row affected (16.32 sec)

mysql> truncate table test_test;                //删除数据
Query OK, 0 rows affected (0.02 sec)

我也做了3次测试，都是在16点几秒。myisam插入10000条数据，都不到一秒，而innodb要十几秒，差了20多倍，太杯具了。

2,测试autocommit关闭的情况

查看复制打印 ?

mysql> call p_test(10000); //插入数据
Query OK, 1 row affected (0.61 sec)
mysql> commit; //提交
Query OK, 0 rows affected (0.02 sec)
mysql> truncate table test_test; //删除数据
Query OK, 0 rows affected (0.00 sec)
mysql> commit; //提交
Query OK, 0 rows affected (0.00 sec)

mysql> call p_test(10000);                       //插入数据
Query OK, 1 row affected (0.61 sec)

mysql> commit;                                  //提交
Query OK, 0 rows affected (0.02 sec)

mysql> truncate table test_test;                //删除数据
Query OK, 0 rows affected (0.00 sec)

mysql> commit;                                  //提交
Query OK, 0 rows affected (0.00 sec)

我也测试了3次，第一次测试的时候，我以为我只插入了1000条，不然怎么会差距这么大呢。我又测试了二次，测试用时差不多，都是在0.6秒左右。autocommit对innodb的影响太大了，差了快30倍。我汗

所以我的建议是把mysql的autocommit自动提交功能关闭，这样可以提高mysql的性能，特别是innodb表比较多的情况下，不是提高一点点。如果关闭了autocommit，不要忘了commit。不然mysql服务器挂掉了，或者重起了，数据就丢失了。

转载请注明
作者:海底苍鹰
地址:mysql autocommit对myisam,innodb的性能影响«海底苍鹰(tank)博客

bandaoyu

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
【myISAM和innoDB】mySql的引擎myisam和innodb的区别/mysiam（mysql插入速度优化）

引擎在不断的发展(比如InnoDB发展），所以不会去记具体的特性，在选型的时候才去看和对比。简单点说。。。没啥特殊的话请使用innodb。 myisam已被放弃。简单说读操作多用MyISAM写操作多用InnoDB1、myisam查询效率更高，查询效率差myisam6-7倍。2、innodb支持事务，行锁，外...
复制链接

扫一扫