MySQL中的MyISAM读的效率高，InnoDB写的效率高，原理是什么

一、MySQL中的MyISAM读的效率高，InnoDB写的效率高，原理是什么

1、MyISAM存储引擎采用表格锁

MyISAM 存储引擎采用了一种称为“表格锁”（Table-level Locking）的锁机制，这意味着对于一张表的读操作和写操作是互斥的，也就是说，当一个线程在写入数据时，其他所有线程都不能进行任何读写操作。但是，在读取数据时，MyISAM 存储引擎可以使用“并发读”（Concurrent Reading）的方式，多个线程可以同时读取同一张表中的数据，因此 MyISAM 存储引擎的读取效率非常高。

2、InnoDB存储引擎采用行锁

InnoDB 存储引擎采用了一种更加细粒度的锁机制，即“行锁”（Row-level Locking）。这种锁机制可以保证在并发的情况下，每个线程都只锁定自己需要的行而不是整张表，从而避免了 MyISAM 存储引擎的写冲突问题。因此，InnoDB 存储引擎在写入数据时会比 MyISAM 存储引擎更加高效。

二、MyISAM与InnoDB的区别

1、构成不同

MyISAM：

每个MyISAM在磁盘上存储成三个文件。名列前茅个文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。

数据文件的扩展名为.MYD (MYData)。

索引文件的扩展名是.MYI (MYIndex)。

InnoDB： 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

2、事务处理不同

MyISAM 类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB 提供事务支持事务，外部键（foreign key）等高级数据库功能。

3、SELECT UPDATE,INSERT,DELETE操作不同

MyISAM： 如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：

如果你的数据执行大量的 INSERT 或 UPDATE ，出于性能方面的考虑，应该使用InnoDB表。

DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除。

LOAD TABLE FROM MASTER 操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

4、对AUTO_INCREMENT的操作不同

MyISAM：

每表一个AUTO_INCREMEN列的内部处理。

MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。

AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置。

对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

更好和更快的auto_increment处理。

InnoDB：

如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。

自动增长计数器仅被存储在主内存中，而不是存在磁盘上。

5、表的具体行数不同

MyISAM： select count() from table，MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的。

InnoDB： InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行。

6、锁不同

MyISAM： 表锁。

InnoDB： 提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”。

三、MyISAM与InnoDB如何选择

MYISAM和INNODB是Mysql数据库提供的两种存储引擎。两者的优劣可谓是各有千秋。INNODB会支持一些关系数据库的高级功能，如事务功能和行级锁，MYISAM不支持。MYISAM的性能更优，占用的存储空间少。所以，选择何种存储引擎，视具体应用而定。

如果你的应用程序一定要使用事务，毫无疑问你要选择INNODB引擎 。但要注意，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable这样的删除语句。

如果你的应用程序对查询性能要求较高，就要使用MYISAM了 。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

有人说MYISAM只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。

延伸阅读1：MySQL存储引擎有哪些

MyISAM

InnoDB

MERGE

MEMORY(HEAP)

BDB(BerkeleyDB)

EXAMPLE

FEDERATED