【MySQL】InnoDB和MyISAM区别详解(MySQL专栏启动)
作者:mmseoamin日期:2023-12-18

【MySQL】InnoDB和MyISAM区别详解(MySQL专栏启动),第1张

📫作者简介:小明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支持事务安全的引擎,支持事务、外键、索引、行锁是他的最大特点。