为什么mysql使用innodb替代了myisam的一些思考?

最新推荐文章于 2024-05-27 16:06:40 发布

LUK流

最新推荐文章于 2024-05-27 16:06:40 发布

阅读量1.2k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_35688140/article/details/103263249

版权

mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

关于innodb和mysiam的区别：
https://blog.csdn.net/qq_35688140/article/details/101936797

myisam使用的非聚簇索引如下：（假设只有两个字段 id和name）
在这里插入图片描述
innodb的聚簇索引：叶子页包含行的全部数据（辅助索引查询依赖聚簇索引）

对比一：读写性能–myisam优于innodb
对比过程参考：https://zhuanlan.zhihu.com/p/44630741
总结：

写入速度，MyISAM比InnoDB快，单线程的情况下，两者差异尤为明显
读取速度，InnoDB和MyISAM无明显差异

差异：MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的 ISAM （Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

既然myisam这么优秀的读写速率，为什么官方还推荐使用innodb呢？又是为什么innodb的速度不如myisam呢？

对比二：索引速率
InnoDB 是聚集索引(innodb的聚簇索引只有主键索引是这样的,辅助索引并不是聚簇索引)，数据文件是和索引绑在一起的，必须要有主键(myisam可以没有主键)，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，否则其他索引也会很大(因为所有的普通索引都会存储主键，会导致普通索引过于庞大)。（因此也解释了为什么官方推荐主键自增，这样的主键存储小而且递增，查询一般尽量使用主键）

而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。这也就解释了为什么myisam的写入速率快。因为myisam没有主键id，每次写入数据不需要为主键寻址，写到合适的地方。而且myisam的就算设置了索引是也是索引与数据分离，不像innodb索引的叶子节点上面写入主键数据，所以随机性插入数据（插入的数据不是主键连续的）myisam的效率更高

既然将innodb变成聚簇索引并没有多大的效率提升，反而写性能下降了。那为什么innodb做出这么“优化”，将myisam的非聚簇索引变成聚簇索引？

当然上面说的设计到了一部分场景条件，《MySQL高性能》上面有一句话这样写到:
不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB的速度都可以让MyISAM望尘莫及，尤其是用到了聚簇索引(通过id查询)，或者需要访问的数据都可以放入内存的应用。(myisam直接从磁盘里拿数据，而innodb要分两步，innodb要从内存里首先获取数据，如果没有再到磁盘里拿。如果内存足够大,innodb会将所有的数据缓存在内存)

内存效率对比参考：https://yq.aliyun.com/articles/532600
上文中一句核心的话：myisam直接从磁盘里拿数据，而innodb要分两步，innodb要从内存里首先获取数据，如果没有再到磁盘里拿。而且一开始innodb要有个加热的过程，也就是说，内存里的数据不是一下子就缓存，而是一点一点的缓存那些热数据。如果innodb内存足够大，查询足够多，innodb速率可能会超过myisam。

对比三：范围查找
innodb的聚簇索引，只要主键自增，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。对磁盘更加友好。

innodb范围查找时，我们只需要一次io读写，可以获取到16K大小的资源，我们称之为读取到的数据区域为Page。而我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO操作中被读取到缓存中，所以在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行IO操作了。除非发生了页的分裂，即要查询的行数据不在上次IO操作的换村里，才会触发新的IO操作。因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

对比四：索引形成的行锁
我们都知道，InnoDB 锁粒度是行锁，而 MyISAM 是表锁。但是行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。但是如果innodb通过聚簇索引实现查询，既有行锁，以此来保证数据执行的事务性的同时又可以获得高的查询效率。
但实际上，因为事务性的保证需要开启事务和提交事务，故也需要一定的开销，innodb通过主键查询的效率不一定高于myisam。

总结：
（1）myisam的随机写能力强，读性能和innodb差不多(在innodb事务性消耗性能的情况下)
（2）innodb范围查询快(聚簇索引读入内存的时候，会将数据读入内存，只有一次磁盘io)
（3）innodb通过递增的主键查询速率高
（4）inodb的事务性保证数据的ACID

参考文献：
https://juejin.im/post/5b1685bef265da6e5c3c1c34
https://juejin.im/post/5c43ee36518825254b5a3c3a
https://blog.csdn.net/andong154564667/article/details/82180067
https://juejin.im/post/5b1685bef265da6e5c3c1c34
https://yq.aliyun.com/articles/532600
https://www.jianshu.com/p/54c6d5db4fe6