MySQL-MySQL的存储引擎MyISAM与InnoDB有什么区别？

Question

应该怎样对两者的使用做出选择呢？

---

根据@毛杭军的答案做一个总结：
1、两者在文件构成上有区别；
2、InnoDB支持事务处理，MyISAM不支持；
3、对无WHERE子句的COUNT(*)操作的不同：MyISAM中保存了该值，直接读取，InnoDB需要作全表扫描；
4、锁的区别：InnoDB支持表级锁和行级锁，MyISAM只支持表级锁；

Answer 1

说点比较直接的感受：
用MyISAM的话，如果表很大，然后维护的时候悲剧的发现索引坏了，或者要修改索引的话，由于只能表锁，只能干等着好几个小时等他修好了改好了，才能重新上线；InnoDB至少可以先上线。

Answer 2

你们还漏了很多。
INNODB的索引会缓存数据，而MYISAM不会
INNODB不区分char和varchar
INNODB支持hash索引，而MYISAM不支持
。。。
还有很多

Answer 3

我一般会看业务是否需要事务支持，查询多还是增删改多。你可以根据你的业务特性结合下面二种存储引擎性的特性进行取舍

以下是网上节选的：
构成上的区别：

MyISAM
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。
.frm文件存储表定义。
数据文件的扩展名为.MYD (MYData)。
索引文件的扩展名是.MYI (MYIndex)。

InnoDB基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB事务处理上方面:

MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持
InnoDB提供事务支持事务，外部键等高级数据库功能
SELECT UPDATE,INSERT，Delete操作如果执行大量的SELECT，MyISAM是更好的选择

1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表
2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用对AUTO_INCREMENT的操作每表一个AUTO_INCREMEN列的内部处理。

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

AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引更好和更快的auto_increment处理如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。
自动增长计数器仅被存储在主内存中，而不是存在磁盘上表的具体行数select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行锁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%”

Answer 4

脏页：
设置了innodb_flush_log_at_trx_commit =1 （0，1，2）参数以后，每一次事务提交后都会日志写入硬盘，也就是写入你说的log_file，log_file是保存在硬盘上。数据要写入到磁盘有以下几种情况：
1、当达到脏页的控制参数innodb_max_dirty_pages_pct值的时候，会flush到磁盘。innodb_max_dirty_pages_pct默认90，网上说一般是75到80。
2、检测到系统空闲的时候，会flush到磁盘。

3、当redo log空间占满的时候会flush脏页到磁盘，然后释放redo log。

show engine innodb statusG
show variables like 'innodb_%io_threads'G;
察看Innodb 引擎 IO线程18个 读写个8个 buffer thread, log thread, master, lock 监控 错误线程 共21个。
可以看到BUFFER POOL AND MEMORY
buffer pool size 缓冲池
Free buffers

Database pages

InnoDB存储引擎的逻辑存储结构和Oracle大致相同,所有数据都被逻辑地存放在一个空间中，我们称之为表空间(tablespace).表空间又由段(segment)、区(extent)、页(page)组成。
1：段(segment) 表空间是由各个段组成的，常见的段有数据段（B+树的页节点）、索引段（B+树的非索引节点）、回滚段等.因为前面已经介绍过了InnoDB存储引擎表的 索引组织(index organized)，因此数据即索引，索引即数据。InnoDB存储引擎对于段的管理是由引擎本身完成的。
2：区(extent) 区是由64个连续的页组成的,每个页(page)大小为16K,即每个区大小为1MB.对于大的数据段，InnoDB存储引擎最多每次可以申请4个区,以此来保证数据的顺序性能。
3：页(page) 同大多数数据库一样,InnoDB有页概念.页是InnoDB磁盘管理的最小单位。
常见的页类型有: 1：数据页(B-tree Node)；2：UnDO页(Undo Log Page)；3：系统页(System Page)；4：事务数据页(Transaction system Page)；5：插入缓冲位图页( Insert Buffer Bitmap)；6：插入组冲空闲列表页(Insert Buffer free Bitmap)；7：二进制大对象页(Uncompressed BLOB Page)；

4：行(row) InnoDB存储引擎是面向行(row-oriented)，也就是说数据的存放按行进行存放的.每个页存放的行记录也是有硬性定义的，最多允许存放16K/2-200行的记录。

Innodb提供了Compact和Redundant (区别：http://dev.mysql.com/doc/refman/5.6/en/data-size.html)
show table status like "invoice"G
可以看到表的Innodb的存储格式：
Name: invoice
Engine: InnoDB
Version: 10
Row_format: Compact

其实Redundant相当于固长，有冗余；Compact更灵活，更紧凑。

show variables like '%partition%'; 是否支持分区。

SELECT * FROM INFORMATION_SCHEMA.partitions WHERE table_schema=database() and table_name='xm_invoice'G。查看分区的相关信息。

InnoDB的索引和算法：索引和开销是需要找一个平衡点，过多或者过少都会影响性能，从而导致负载过高，浪费硬件资源。
InnoDB存储引擎概 述：InnoDB支持常见的两种索引，B+树索引和hash索引。hash索引是自适应的，不能认为干预。B+树是由平衡二叉树演化而来，但是B+树不是 一个二叉树。B+树并不能直接找到具体的行，B+树索引只能找到数据行所在的页，然后数据库通过把页读入内存，再在内存中进行查找。

B+ 树索引：B+树索引在数据库中有一个特点是高扇出性(fan out),B+树的高度一般是2-3层。B+树索引可以分为聚集索引(clustered index)和辅助聚集索引(secondary index)。聚集索引：即表中数据按照主键顺序存放，而聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据。辅助索 引：也称为非聚集索引，叶级别不包含行的全部数据，叶节点除了包行键值以外，每个叶级别中的索引行中还包含了一个书签，该书签就是对应行数据的聚集索引 键。

hash索引：innodb存储引擎中自适应hash索引使用的是散列表(hash table)的数据结构。但是散列表不只存在于自适应hash中，每个数据库中都存在，用来加速内存中数据的查找。hash table又叫散列表，由直接寻址表改进而来。

InnoDB存储引擎中的锁

TRANSACTIONS

Trx id counter 2200
Purge done for trx's n:o < 2014 undo n:o < 0
History list length 120
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 44, OS thread handle 0x7f61c00b3700, query id 191 localhost root

show engine innodb status

InnoDB存储引擎中的锁：

锁是用来管理对共享文件的并 发访问。InnoDB会在行级别上对数据库上锁。不过InnoDB存储引擎会在数据库内部其他多个地方使用锁，从而允许对不同 资源提供并发访问。例如操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入。锁类型：S lock 共享锁 允许事务读一行数据；X lock 排它锁 允许事务删除或者更新一条数据；IS lock 意向共享锁 事务想要获得一个表中某几行的共享锁；IX lock 意向拍他所 事务想要获得一个表中某几行的排它锁。

锁的算法：
InnoDB引擎有三种锁的算法设计：
Record lock：单个记录上的锁。
Gap lock：间隙锁，锁定一个范围，但不包含记录本身。

Next-key lock：Gap lock+Next-key lock锁定一个范围，并锁定记录本身。

锁问题：

本 来锁问题会导致的是更新丢失、幻读、脏读、不可重复读，但是innodb作者却只写出了三种问题,可能是幻读通过innodb Next-key Lock解决了，作者就没有提及。这几个锁问题对应事务隔离的4个安全级别：READ UNCOMMITTED(事务隔离最低的级别，有事务隔离就能解决更新丢失，但是存在脏读的问题)，READ COMMITED(ORACLE和SQL SERVER默认的隔离级别，解决了脏读，但是一个事务多次读取的内容不同，出现了不可重复读的问题)，READ REPEATABLE(可重复读，InnoDB引擎的默认事务隔离级别，解决了不可重复读的问题，但是产生了幻读，innodb通过Next-key lock解决了幻读)。SERIALIZABLE(可串行话，通过强制事务排序解决幻读问题，会降低性能)总的看来innodb默认的 READ REPEATABLE是非常棒的。

阻塞：
InnoDB中需要其他事务的锁释放它锁占用的资源，这个时候就会发生锁等待，这就是阻塞。

InnoDB 引擎有两个相关参数：innodb_lock_wait_timeout 用来设定等待的时间，默认是50秒，这是一个动态参数，可以随时调整，innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作，默认是OFF，代表不回滚，这是一个静态参数。

死锁：

死锁会惨生阻塞，所以可以通过（阻塞）的参数，让超时的阻塞回滚。还有就是开发的时候，每个事务对表，字段，行的操作，都是顺序的，这样可以很大程度上避免死锁。

锁升级：

InnoDB不存在锁升级问题，锁升级一般在SQL SERVER中使用。

InnoDB引擎事务，完全符合事务的ACID特性。
Automicity 原子性，Consistency一致性，Isolation 隔离性，Durability 持久行
事务隔离性通过锁来实现，原子性、一致性、持久性通过数据库的redo和undo来完成。

innodb通过redo日志文件和innodb log buffer来实现redo。
当 事务开始时，innodb会记录该事务的一个LSN(Log Sequence Number,日志序列号)，当执行事务时，会往innodb log buffer写入事务日志，当事务提交时，必须将innodb存储引擎的日志缓存写入磁盘(默认的实现，通过 innodb_flush_log_at_trx_commit=1)
这意味这磁盘上的页和内存缓冲池中的页是不同步的，可以通过 show ENGINE INNODB STATUSG来观察当前磁盘和日志的”差距”，在LOG字段。Log sequence number表示当前的LSN，Log flushed up to表示刷新到重做日志文件的LSN，Last checkpoint at表示刷新到磁盘的LSN。

undo和redo相 反，redo是重做，undo是撤销。使用rollback语句请求回滚就可以利用undo信息将数据回滚到修改之前的样子。redo存放在redo日志 里面，undo存放在数据库内部的一个特殊段(undo segment),undo段位于共享表空间里。undo是逻辑操作，它实际上是做与之前相反的工作，之前是insert，它就delete对于每个 update，undo都会执行一个相反的update。

事务控制语句：
AUTOCOMMIT = 0 关闭自动提交，等于1 开启自动提交。
START TRANSACTION | BEGIN 显示的开启一个事务。
COMMIT 提交你的事务。
ROLLBACK 回滚。
SAVEPOINT identifier SAVEPOINT允许你的事务中创建一个保存点，一个事务中可以有多个保存点。
RELEASE SAVEPOINT identifier 删除一个事务的保存点。
ROLLBACK TO [savepoint]identifier 回滚到之前创建的保存点。
SET TRANSACTION 这个语句用来设置事务的隔离级别，innodb的隔离级别在6.4已经说过了。
completion_type = 0 时COMMIT和COMMIT WORK功能完全相同。
completion_type = 1 时COMMIT WORK等于COMMIT AND CHAIN，表示提交后马上开启一个相同隔离级别的事务。
complation_type = 2 时COMMIT WORK等于COMMIT AND RELEASE，当事务提交后会自动断开与服务器的链接。

隐式提交的SQL语句：
完成这些语句就会自动提交：
DDL：CREATE USER，DROP USER，GRANT、RENAME USER，REVOKE，SET PASSWORD，ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE
注意:TRUNCATE TABLE属于DDL语句，不能rollback。

对于事务的统计：
QPS = Question Per Second = 每秒请求数
TPS = Transaction Per Second = 每秒事务处理能力
计算TPS的方法是com_commit + com_rollback/TIME = TPS

分布式事务：
innodb引擎支持XA分布式事务。
分布式事务指营运需多个独立的事务资源(transactional resources)参与一个全局的事务中。事务资源通常是关系型数据库系统。全局事务要求在其中所有参与的事务要么都提交、要么都回滚，对于原有的ACID有了提高。
注意：在使用分布式事务时，innodb存储引擎的事务隔离级别必须设置成SERIALIABLE。

不好的习惯：
1、在循环中提交事务
2、不要开启自动提交事务 set auto_commit = 0
3、使用自动回滚，存储过程中使用 declare exit handler for sqlexception rollback;
写在我的博客上了：http://www.imop.us/v/MTgwNQ==.html

Answer 5

上边是引擎选择的时候需要看的，
具体到mysql开发的时候，就是InnoDB 需要 commit 一下 sql语句才生效 :)

Answer 6

查了一下资料，发现如下不同。英文不好贴原文了，怕翻译错误 误导大家

Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
Innodb never fragments short rows.
Innodb does not have separate index files so they do not have to be opened.
Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren’t built in optimal order and are fragmented.
There is currently no way to defragment InnoDB indexes, as InnoDB can’t build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
MyISAM requires a file descriptor open for each client for the same table data file. Index file descriptors are shared b/w clients on the other hand. This causes a high usage of open files and causes the database to reach the open files limit allowed for the process (ulimit for Linux external to MySQL or open_files_limit variable inside MySQL).
MyISAM can possibly cause information_schema to respond way too late (not confirmed through Innodb yet).
For MyISAM tables, table header has to be modified each time the table is opened.
In some cases, MyISAM supports building idexes by sorting (after ALTERs).
MyISAM databases can be read from readonly media eg. CD.