数据库优化-mysql中INNODB和MYIASM引擎的区别

Innodb引擎

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。 该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。 但是该引擎不支持FULLTEXT类型的索引（全文索引）【5.6.24以后支持】，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。 当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。 但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

 

 MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。 
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

 

 

MyIASM引擎

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。 不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE 时只需要直接读取已经保存好的值而不需要进行全表扫描。 如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

 

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

区别：

1、InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。 

2、叶节点包含了完整的数据记录，这种索引叫做聚集索引。

3、因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

4、InnoDB的所有辅助索引都引用主键作为data域。

5、聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 

总结：

1、InnoDB不建议使用过长的字段作为主键。因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

2、非单调（不是自增或自减的均为非单调）的字段作为主键在InnoDB中不是个好做法，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

 

MyIASM与InnoDB的主要区别：

1、MyIASM是非事务安全的，而InnoDB是事务安全的

2、MyIASM锁的粒度是表级的，而InnoDB支持行级锁

3、MyIASM支持全文类型索引，而InnoDB不支持全文索引（5.6.24以后支持）、

4、MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM

5、MyIASM表保存成文件形式，跨平台使用更加方便

6、MyIASM不支持外键约束，而InnoDB 支持外键约束（可实现表的联动操作，一删多删）

 

应用场景：

1、MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM

2、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB