📫作者简介:小明java问道之路,2022年度博客之星全国TOP3,专注于后端、中间件、计算机底层、架构设计演进与稳定性建设优化,文章内容兼具广度、深度、大厂技术方案,对待技术喜欢推理加验证,就职于知名金融公司后端高级工程师。
📫 热衷分享,喜欢原创~ 关注我会给你带来一些不一样的认知和成长。
🏆 2022博客之星TOP3 | CSDN博客专家 | 后端领域优质创作者 | CSDN内容合伙人
🏆 InfoQ(极客邦)签约作者、阿里云专家 | 签约博主、51CTO专家 | TOP红人、华为云享专家
🔥如果此文还不错的话,还请👍关注、点赞、收藏三连支持👍一下博主~
🍅 文末获取联系 🍅 👇🏻 精彩专栏推荐订阅收藏 👇🏻
专栏系列(点击解锁)
学习路线(点击解锁)
知识定位
🔥Redis从入门到精通与实战🔥
Redis从入门到精通与实战
围绕原理源码讲解Redis面试知识点与实战
🔥MySQL从入门到精通🔥
MySQL从入门到精通
全面讲解MySQL知识与企业级MySQL实战 🔥计算机底层原理🔥
深入理解计算机系统CSAPP
以深入理解计算机系统为基石,构件计算机体系和计算机思维
Linux内核源码解析
围绕Linux内核讲解计算机底层原理与并发
🔥数据结构与企业题库精讲🔥
数据结构与企业题库精讲
结合工作经验深入浅出,适合各层次,笔试面试算法题精讲
🔥互联网架构分析与实战🔥
企业系统架构分析实践与落地
行业最前沿视角,专注于技术架构升级路线、架构实践
互联网企业防资损实践
互联网金融公司的防资损方法论、代码与实践
🔥Java全栈白宝书🔥
精通Java8与函数式编程
本专栏以实战为基础,逐步深入Java8以及未来的编程模式
深入理解JVM
详细介绍内存区域、字节码、方法底层,类加载和GC等知识
深入理解高并发编程
深入Liunx内核、汇编、C++全方位理解并发编程
Spring源码分析
Spring核心七IOC/AOP等源码分析
MyBatis源码分析
MyBatis核心源码分析
Java核心技术
只讲Java核心技术
本文目录
本文导读
一、InnoDB支持事务,MyISAM不支持
二、 存储结构不同
三、存储空间不同
四、InnoDB支持行级锁,MyISAM 仅支持表级锁
五、count()函数不同
六、索引不同
1、MyISAM允许没有主键
2、InnoDB支持外键
3、InnoDB聚簇索引,MyISAM非聚簇索引
4、InnoDB表唯一索引必选
5、InnoDB不支持全文索引
6、索引保存位置不同
7、自增(AUTO_INCREMENT)
七、如何选择存储引擎?
八、InnoDB为什么推荐使用自增ID作为主键?
九、为什么InnoDB没有了count变量?
总结
本文导读
文本适合解决:你们公司用什么存储引擎?InnoDB和MyISAM 区别?为什么选择InnoDB?什么事聚簇索引什么是非聚簇索引?InnoDB如何存储数据?如何选择存储引擎?InnoDB为什么推荐用自增ID做主键?等类似的问题。
一、InnoDB支持事务,MyISAM不支持
InnoDB支持事务,MyISAM不支持。
MyISAM 强调的是性能,每次查询具有原子性。其执行数度比InnoDB类型更快,但是不提供事务支持)。MyISAM 默认表类型,它是基于传统的ISAM类型,它是存储记录和文件的标准方法。不是事务安全的,而且不支持外键。
InnoDB支持事务,支持外键、行锁、事务是他的最大特点,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务。
二、 存储结构不同
MyISAM 数据和索引是分别存储的,数据文件的扩展名为(.MYD)。索引文件的扩展名是(.MYI)。
InnoDB 数据和索引是集中存储的(.ibd),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。
三、存储空间不同
MyISAM 可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。
InnoDB 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
四、InnoDB支持行级锁,MyISAM 仅支持表级锁
MyISAM 只支持表级锁,用户在操作MyISAM 表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。
InnoDB支持事务和行级锁,行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。
MySQL InnoDB引擎默认的修改数据语句,update、delete、insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select …for update(不希望别的事务修改数据,其他的事务职能用不带锁的select来查询),加共享锁可以使用 select … lock in share mode语句(或者select不带任何锁来查询数据)
五、count()函数不同
MyISAM 保存有表的总行数,如果select count(*) from table;会直接取出出该值。
InnoDB 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,MyISAM 和InnoDB处理的方式都一样。
六、索引不同
1、InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
2、InnoDB的主键索引的叶子节点存储的是行数据,因此主键索引非常高效。
3、MyISAM索引的叶子节点存储的是行数据地址,需要一次寻址的操作才能获取到数据。
4、InnoDB非主键索引的叶子节点存储的是主键和其他带索引的数据,因此查询是用索引覆盖会更高效。
1、MyISAM允许没有主键
MyISAM 允许没有任何索引和主键的表存在,索引都是保存行的地址。
InnoDB 如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。
2、InnoDB支持外键
InnoDB支持外键,而MyISAM 不支持。对一个包含外键的InnoDB表转为MYISAM会失败;
3、InnoDB聚簇索引,MyISAM非聚簇索引
InnoDB是聚簇索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。
MyISAM是非聚簇索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
MyISAM 这棵树的叶子结点存储数据是物理地址,InnoDB的叶子结点直接存储数据记录,这也是簇索引与非簇索引的区别。
4、InnoDB表唯一索引必选
InnoDB表唯一索引必选,MyISAM 可以选;InnoDB表没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键,而MyISAM 可以没有
5、InnoDB不支持全文索引
InnoDB不支持fulltext全文索引,MyISAM支持;InnoDB不支持FULLTEXT类型的全文索引,但是InnoDB可以使用sphinx插件支持全文索引,并且效果更好。
6、索引保存位置不同
MyISAM 的索引以表名+.MYI文件分别保存。
InnoDB的索引和数据一起保存在表空间里。
7、自增(AUTO_INCREMENT)
MyISAM 引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。
InnoDB引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。
七、如何选择存储引擎?
1. 是否要支持事务,如果要请选择InnoDB,如果不需要可以考虑MyISAM;
2. 如果表中绝大多数都只是读查询,可以考虑MyISAM,如果既有读也有写,请使用InnoDB。
3. 系统奔溃后,MyISAM恢复起来更困难,能否接受;
4. MySQL5.5版本开始InnoDB已经成为MySQL的默认引擎(之前是MyISAM),说明其优势是有目共睹的,如果你不知道用什么,那就用InnoDB,至少不会差。
八、InnoDB为什么推荐使用自增ID作为主键?
自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。
九、为什么InnoDB没有了count变量?
因为InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的,因此count统计会计算对于当前事务而言可以统计到的行数,而不是将总行数储存起来方便快速查询。
InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在,InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区(Buffer Pool)中,count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样,如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
总结
MyISAM是存储记录和文件的标准方法。不是事务安全的,而且不支持外键。InnoDB支持事务安全的引擎,支持事务、外键、索引、行锁是他的最大特点。