数据库引擎INNODB和MYISAM的区别

PS：5.7以后的InnoDB支持全文索引了

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

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

3.索引的存储结构不同

• MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

• 每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：
  1.在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
  2.如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
  3.如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

• 除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

• InnoDB的数据和索引存储在一个文件.ibd中。InnoDB的数据组织方式，是聚簇索引。

举例：

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。


使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录.
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。.

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

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行级锁(默认行级锁），而MyISAM支持表级锁

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。意思就是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
举例：

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE = InnoDB;

update `user_innodb` set age=10 where id=1;             命中索引，行锁。
 
update `user_innodb` set age=10 where id != 1;           未命中索引，表锁。
 
update `user_innodb`set age=10 where username='chackca';    无索引，表锁。

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列ROWID来充当默认主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

补：InnoDB为什么推荐使用自增ID作为主键？
自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

---

如何选择数据库引擎：

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

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

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

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