【mysql系列】引擎innodb 、myisam7点对比和如何选择？

目录

Innodb 和 Myisam区别

InnoDB为什么推荐使用自增ID作为主键？

innodb引擎的4大特性

何时使用innodb

InnoDB简介

InnoDB的重要内存结构

Buffer Pool简介

redo log buffer简介

小结

Innodb 和 Myisam区别

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

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

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

       也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

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

那么为什么InnoDB没有了这个变量呢？

    因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
    如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
 

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

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

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

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

例如：
 

    t_user(uid, uname, age, sex) innodb;
 
    uid PK（主键索引）
    无其他索引
    update t_user set age=10 where uid=1;             命中索引，行锁。
 
    update t_user set age=10 where uid != 1;           未命中索引，表锁。
 
    update t_user set age=10 where name='chackca';    无索引，表锁。

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

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

        Innodb：frm是表定义文件，ibd是数据文件

        Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

InnoDB为什么推荐使用自增ID作为主键？

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

innodb引擎的4大特性

       插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
 

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	是	是

何时使用innodb？

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

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

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

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

· INNODB会支持关系数据库的高级功能，如事务功能和行级锁

支持交易(事务给予您支持ACID属性)。

行级锁定。具有更细粒度的锁定机制，与例如MyISAM相比，具有更高的并发性。

InnoDB比MyISAM更耐表损坏。

支持数据和索引的大缓冲池。 MyISAM密钥缓冲区仅用于索引。

·   MYISAM的性能更优，占用的存储空间少。

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

MyISAM强调了快速读取操作。

支持全文索引

更简单的设计和创建，因此更适合初学者。不用担心表之间的外部关系。

Innodb详解

MySQL中执行一条SQL语句，相应表数据的读写都是由存储引擎去做（更新数据、查询数据）。

在这个过程，存储引擎需要决策一些事情

• 数据是从内存查还是从硬盘查

• 数据是更新在内存，还是硬盘

• 内存的数据什么时候同步到硬盘

所以存储引擎会按照内部逻辑与内存、硬盘交互。

我们可以按需选择存储引擎，比如常见的 InnoDB、MyISAM、Memory等等。

众多存储引擎中，InnoDB是最为常用的，从 MySQL5.5.8 版本开始，InnoDB是默认的存储引擎。

InnoDB简介

InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。

特点是行锁设计、支持MVCC、外键，提供一致性非锁定读，同时本身设计能够最有效的利用内存和CPU，是 MySQL 最常用的存储引擎。

InnoDB的重要内存结构

InnoDB存储引擎在内存中有两个非常重要的组件，分别是缓冲池（Buffer Pool）和重做日志缓存（redo log buffer）。

Buffer Pool简介

缓冲池（Buffer Pool）里面会缓存很多的数据，比如数据页、索引页、锁信息等等。

MySQL表数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时，如果 Buffer Pool 里命中数据，就直接在 Buffer Pool 里更新。

总之 Buffer Pool 会缓存很多的数据，以便后续的查询与更新。

小贴士：这里只是用数据页举例帮助大家理解，大家千万不要认为 Buffer Pool 里面只有数据页，它只是占 Buffer Pool 大部分空间，关于 Buffer Pool 更多细节，后续会有专门的文章讲解。

redo log buffer简介

接着思考一个问题，假设我们把 Buffer Pool 中某个数据页的某条数据修改了，但是硬盘的数据还未同步，此时数据是不一致的，如果 MySQL 宕机了，数据就丢失了。

这可怎么办呢。

为了保证数据的持久性，InnoDB存储引擎加入了 redo 日志功能，也叫重做日志。

每当我们对表数据进行更新时，会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里。

当事务提交时，会把 redo log buffer 清空，刷盘到 redo 日志文件。

这样 MySQL 宕机了也没关系，因为重启后会根据 redo 日志去恢复数据。

小结

其实不难发现，缓冲池（Buffer Pool）和重做日志缓存（redo log buffer），它们都是为了减少硬盘 IO 开销。