MySQL 当前默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎,也就是说只有 InnoDB 支持事务。
在5.5之前,MyISAM是MySQL的默认数据库引擎。因为它性能好,还包含全文索引、压缩、空间函数等,但不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。因此引入了InnoDB(事务性数据库引擎),之后默认的存储引擎都是InnoDB。
大多数时候我们使用的都是 InnoDB 存储引擎,但是如果读密集的时候使用 MyISAM 也可以
区别:
-
是否支持行级锁 : MyISAM 只有表级锁,而InnoDB 支持行级锁和表级锁,默认为行级锁。只有通过索引查询数据时才使用行级锁,否则,InnoDB将使用表级锁
-
是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行速度比InnoDB类型更快,但是不提供事务支持。但是InnoDB 提供事务支持事务,外部键等高级数据库功能。 具有事务、回滚和崩溃修复能力的事务安全型表。
-
是否支持外键: MyISAM不支持,而InnoDB支持。
-
是否支持MVCC :仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在
READ COMMITTED
和REPEATABLE READ
两个隔离级别下工作;MVCC可以使用 乐观锁 和 悲观锁来实现; -
索引:
MyISAM 与行记录是分开存储的,叫做非聚集索引。B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录
InnoDB 主键索引与行记录是存储在一起的,故叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列,则mysql自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。
数据文件本身就是按B+Tree组织的一个索引结构 ,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址 在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。
闲谈行级锁与表就锁级级:
- 行级锁:分为共享锁和排它锁,一般是排它锁,只对当前行进行加锁,可以减少数据库操作的冲突,被锁定的行不能被修改删除,只能被查询;锁定颗粒度小,所以锁定资源争用的概率也小,并发度高,加锁慢,消耗也大,也容易造成死锁。
有三种分类:
Record Lock: 对索引项加锁,单个行记录上的锁
Gap Lock: 间隙锁,锁定一个范围,不包括记录本身,防止插入,可以解决幻读的问题
Next-key Lock: record+gap 锁定一个范围,包含记录本身
-
innodb对于行的查询使用next-key lock
-
Next-locking keying为了解决Phantom Problem幻读问题
-
当查询的索引含有唯一属性时,将next-key lock降级为record key
-
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内,而这会导致幻读问题的产生
-
有两种方式显式关闭gap锁:(除了外键约束和唯一性检查外,其余情况仅使用record lock) A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1
- 表级锁:锁定最大颗粒度,最大的特点是逻辑简单,资源消耗少,所以获取锁和释放锁的速度很快。并且它一次会锁定整个表,加锁快,所以可以避免死锁。
使用表级锁情况:
- 更新大表中的大部分数据;
- 事务比较复杂,使用行级索很可能引起死锁。