【myISAM和innoDB】mySql的引擎myisam和innodb的区别/mysiam（mysql插入速度优化）
目录
Myisam引擎(非聚集索引--叶子节点存储数据地址的指针)
Innodb引擎(聚集索引---叶子节点的data直接包含数据)
mysql autocommit对myisam,innodb的性能影响
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的值是非常多的。
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)用主键值去主键索引树去叶子节点到该条数据信息
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的性能对比测试_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)博客
