Mysql InnoDB和MyISAM区别总结

2020-12-16 From 程序之心 By 丁仪

MySQL 支持很多存储引擎,如 MyISAM、InnoDB、memory、archive、example等,一般情况下大家可能直接就用默认引擎了。了解不同引擎的区别有助于在技术方案中使用合适的引擎,达到最好的效果。本文简单总结下,以备查阅。

MyISAM 引擎

MySQL 5.5 版本之前,MyISAM 是默认存储引擎。ISAM 是 Indexed Sequential Access Method(有索引的顺序访问方法)的缩写,MyISAM 是 ISAM 的改进版。

MyISAM 引擎适用于查询比较频繁、 count 计算较多、不使用事务的场景。MyISAM 具有如下特点:

  • 用一个变量保存了整个表的行数,如果 count 语句没有 where 条件只需要读出该变量即可,速度很快;
  • 不支持事务需要事务的场景不能用这个引擎;
  • 不支持外键,无法使用外键做约束;
  • 使用表共享读锁:读操作时,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写操作;
  • 使用表独占写锁:进行写操作时,会阻塞其他用户对同一表的读和写操作;
  • 清空表:会重新建表;
  • 存储文件:在磁盘上存储成三个文件。frm文件存储表定义,MYD 文件存储数据,MYI 文件存储索引
  • 存储格式:支持三种不同的存储格式:静态表(默认)、动态表、压缩表;
  • 全文索引:支持 FULLTEXT 类型的全文索引

MyISAM 是非聚集索引,使用 B+ Tree 作为索引结构,索引数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB 引擎

MySQL 5.5 版本开始使用 Innodb 作为默认引擎。InnoDB 由 Innobase Oy公司开发

InnoDB 引擎适用于可靠性要求比较高、查询更新都比较频繁、需要事务保证的场景。InnoDB 引擎具有如下特点:

  • 支持事务需要事务的场景可以用这个引擎,每一条SQL语句都默认封装成事务自动提交;
  • 支持外键:需要用外键做约束可以用这个引擎;
  • 行级锁定:只锁定要修改的行,不影响其他用户对表进行操作。技术实现是给索引项加锁,只有通过索引条件检索数据,才使用行级锁,否则将使用表级锁;
  • 事务死锁:两个事务发生死锁的时候,会自动计算出每个事务影响的行数,然后自动回滚行数少的那个事务
  • 清空表:清空表时一行一行的删除数据
  • 存储文件:frm文件存储表定义,ibd 文件存储数据
  • 缓存数据:主内存中建立其专用的缓冲池用于高速缓冲数据索引
  • 全文索引:不支持 FULLTEXT 类型的全文索引,但是可以使用sphinx插件支持全文索引

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

MyISAM 和 InnoDB 的区别

MyISAM 不支持事务,InnoDB 支持事务

MyISAM 不支持外键,InnoDB 支持外键。

MyISAM 支持表级锁,InnoDB 支持表级锁和行级锁(默认)。

MyISAM 表可以没有主键,InnoDB 表必须有主键,如果用户不设置就会自动生成主键。

MyISAM 存储文件有 frm(表定义文件)、myd(数据文件)、myi(索引文件),Innodb 存储文件有 frm(表定义文件)、ibd(数据文件)。

MyISAM 可以使用自增字段(AUTO_INCREMENT)和其他字段建立联合索引,InnoDB 必须包含只有该字段索引

清空表时,MyISAM 重新建表,InnoDB 逐行删除。

MyISAM 允许没有任何索引和主键的表存在,索引都是保存行的地址。InnoDB 如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

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

本文来源:程序之心,转载请注明出处!

本文地址:https://chengxuzhixin.com/blog/article/200062.html

发表感想

© 2016 - 2022 chengxuzhixin.com All Rights Reserved.

浙ICP备2021034854号-1    浙公网安备 33011002016107号