mysql数据库myisam和innodb的区别

最新推荐文章于 2024-08-11 16:40:03 发布

huopusa

最新推荐文章于 2024-08-11 16:40:03 发布

阅读量202

点赞数 2

分类专栏：数据库文章标签： mysql 数据库引擎

本文链接：https://blog.csdn.net/weixin_39076716/article/details/88392943

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

https://blog.csdn.net/crazyboy12138/article/details/80297796
https://www.cnblogs.com/y-rong/p/8110596.html – 比较深刻
一、索引的区别
myisam
非聚簇索引：Node节点有包含所含有的值（数据所在的地址）

innodb
聚簇索引： node 节点所有的值（所有的数据）存储在最底层的node中减少了 tree的层级
主键索引：数据是一体的没有分开，叶子数据底层有指向
辅助索引：底层存储的是主键索引的值，根据主键索引再进行具体数据的查询

MyISAM 是MySQL关系数据库管理系统的默认储存引擎(5.5之前)
MyISAM 不支持事物和外键
MyISAM 表对应硬盘上三个文件1.frm文件保存表的定义2.MYD保存表的数据3.MYI是表的索引文件
MyISAM 查询效率相当可观，占用内存少
MyISAM 支持拷贝备份
MyISAM 锁级别为表锁，表锁优点是开销小，加锁快，
MyISAM 锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。
MyISAM 索引以表名+.MYI文件分别保存。

InnoDB 适用于高并发读写的情况
InnoDB 5.5之后作为默认的存储引擎
InnoDB 是一个事务型的存储引擎，支持回滚
InnoDB 支持事务处理、ACID事务特性
InnoDB 实现了SQL标准的四种隔离级别
InnoDB 支持行级锁和外键约束
InnoDB 可以利用事务日志进行数据恢复
InnoDB 锁级别为行锁，高并发是性能优于 MyISAM。缺点是系统消耗较大
InnoDB 索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存
InnoDB 没有保存表的行数，当使用COUNT统计时会扫描全表
InnoDB 必须包含主键索引，且主键索引唯一
InnoDB 索引和数据一起保存在表空间里

两者都仅支持B＋树索引，不支持hash索引

InnoDB所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件）,相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)

Innodb 的行锁模式有以下几种：共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁。

当语句没有使用索引，innodb不能确定操作的行，这个时候就使用的意向锁，也就是表锁

什么是死锁？当两个事务都需要获得对方持有的排他锁才能完成事务，这样就导致了循环锁等待，也就是常见的死锁类型。

解决死锁的方法：
1、数据库参数
2、应用中尽量约定程序读取表的顺序一样
3、应用中处理一个表时，尽量对处理的顺序排序
4、调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）

四、开发的注意事项
1、可以用 show create table tablename 命令看表的引擎类型。
2、对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交。
3、可以执行以下命令来切换非事务表到事务（数据不会丢失），innodb表比myisam表更安全：alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;
4、默认innodb是开启自动提交的，如果你按照myisam的使用方法来编写代码页不会存在错误，只是性能会很低。如何在编写代码时候提高数据库性能呢？
a、尽量将多个语句绑到一个事务中，进行提交，避免多次提交导致的数据库开销。
b、在一个事务获得排他锁或者意向排他锁以后，如果后面还有需要处理的sql语句，在这两条或者多条sql语句之间程序应尽量少的进行逻辑运算和处理，减少锁的时间。
c、尽量避免死锁
d、sql语句如果有where子句一定要使用索引，尽量避免获取意向排他锁。
f、针对我们自己的数据库环境，日志系统是直插入，不修改的，所以我们使用混合引擎方式，ZION_LOG_DB照旧使用myisam存储引擎，只有ZION_GAME_DB，ZION_LOGIN_DB，DAUM_BILLING使用Innodb引擎。

行级锁可能会导致“死锁”，那到底是怎么导致的呢，分析原因：Mysql行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，那么Mysql就会锁定这个主键索引，如果sql语句操作的是非主键索引，那么Mysql会先锁定这个非主键索引，再去锁定主键索引。
/* 死锁 */
行级锁可能会导致“死锁”，那到底是怎么导致的呢？
分析原因：Mysql行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，那么Mysql就会锁定这个主键索引，如果sql语句操作的是非主键索引，那么Mysql会先锁定这个非主键索引，再去锁定主键索引。
在UPDATE 和 DELETE操作时Mysql不仅会锁定所有WHERE 条件扫描过得索引，还会锁定相邻的键值。
“死锁”举例分析：
表Test：（ID,STATE,TIME）主键索引：ID 非主键索引：STATE
当执行"UPDATE STATE =1011 WHERE STATE=1000" 语句的时候会锁定STATE索引，由于STATE 是非主键索引,所以Mysql还会去请求锁定ID索引
当另一个SQL语句与语句1几乎同时执行时：“UPDATE STATE=1010 WHERE ID=1” 对于语句2 Mysql会先锁定ID索引，由于语句2操作了STATE字段，所以Mysql还会请求锁定STATE索引。这时。彼此锁定着对方需要的索引，又都在等待对方释放锁定。所以出现了"死锁"的情况。

行级锁的优点：
有许多线程访问不同的行时，只存在少量的冲突。
回滚时只有少量的更改
可以长时间锁定单一的行
行级锁缺点：
相对于页级锁和表级锁来说占用了更多的内存
当表的大部分行在使用时，比页级锁和表级锁慢，因为你必须获得更多的锁
当在大部分数据上经常使用GROUP BY操作，肯定会比表级锁和页级锁慢。
页级锁：表级锁速度快，但是冲突多；行级锁速度慢，但冲突少；页级锁就是他俩折中的，一次锁定相邻的一组记录。

网上截取了前辈们测试结论：
测试方法：连续提交10个query，表记录总数：38万，时间单位 s

    引擎类型                    MyISAM                InnoDB              性能相差

    count                      0.0008357            3.0163                3609

    查询主键                  0.005708              0.1574                27.57

    查询非主键                  24.01                  80.37                3.348

    更新主键                  0.008124            0.8183                100.7

    更新非主键                0.004141            0.02625              6.338

    插入                        0.004188            0.3694                88.21

（1）加了索引以后，对于MyISAM查询可以加快：4 206.09733倍，对InnoDB查询加快510.72921倍，同时对MyISAM更新速度减慢为原来的1/2，InnoDB的更
新速度减慢为原来的1/30。要看情况决定是否要加索引，比如不查询的log表，不要做任何的索引。

（2）如果你的数据量是百万级别的，并且没有任何的事务处理，那么用MyISAM是性能最好的选择。

（3）InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。

    在我们测试的这个38w的表中，表占用空间的情况如下：
        引擎类型                    MyISAM              InnoDB
        数据                      53,924 KB          58,976 KB
        索引                      13,640 KB          21,072 KB
        占用总空间              67,564 KB          80,048 KB

    另外一个176W万记录的表， 表占用空间的情况如下：

        引擎类型                MyIsam              InnorDB
        数据                  56,166 KB          90,736 KB
        索引                  67,103 KB          88,848 KB
        占用总空间        123,269 KB        179,584 KB