1 InnoDB和MyISAM的区别
(1)InnoDB 支持事务,MyISAM 不支持事务。
(2)InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
(3)InnoDB 是聚集索引(叶子节点存数据),MyISAM 是非聚集索引(叶子节点存指针,数据和索引分离)。聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
(4)InnoDB 不保存表的具体行数,执行 select count(*) from table 时
需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
(5) InnoDB 最小的锁粒度是行锁(同时也支持表级锁),MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。
注:
当索引查找失败的时候,InnoDB会自动将行级锁升级为表级锁。
2 如何选择
(1)是否要支持事务,如果需要就选择 InnoDB,如果不需要可以考虑 MyISAM;
(2)如果表中绝大多数都只是读查询,可以考虑 MyISAM,如果既有读写也挺频繁,就使用InnoDB。
(3)系统奔溃后,MyISAM恢复起来更困难,如果不能接受就选 InnoDB;
3 索引
(1)myisam
索引由B+树构成,执行查询操作的时候会先搜索B+树,如果找到对应叶子结点,会根据叶子节点的值(地址),拿出整行数据。(索引和数据分开查找)
.frm
存储表结构文件.MYD
文件存储索引文件.MYI
存储数据文件
(2)Innodb
主索引搜索时
上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶子节点包含了完整的数据记录,这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。(InnoDB的叶子节点存储了整个数据行的所有数据)
辅助索引搜索时
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
.frm
存储表结构文件.ibd
存储数据及索引
参考文章:https://www.zhihu.com/question/20596402/answer/211492971
参考博客:https://blog.csdn.net/qq_41706670/article/details/92836395
4 Mysql中常见的索引类型
参考博客:常见的索引类型
5 InnoDB行级锁锁的实现
innodb存储引擎是通过给索引上的索引项加锁来实现行锁,只要通过索引条件检索数据,innodb才会使用行级锁,否则会使用表锁。
innodb实现标准行级锁,其中有两种类型的锁,共享锁(S)和独占锁(X)。
- 共享锁,允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
- 排它锁,允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集的共享读锁和排他锁。
如果事务T1持有一行记录的共享锁,那么另一个不同的事务T2对该行记录的锁定如下:
- 如果事务T2对该行的请求是一个共享锁,那么事务T1和事务T2可以共同对该行记录持有同一把共享锁。
- 如果事务T2对该行的请求是一个排它锁,那么事务T2不可能马上获得对该行记录的排它锁,必须要等到事务T1将该记录的共享锁释放,才可以对该行记录持有排它锁。
- 如果事务T1持有第 r 行的独占(X)锁,那么对于事务T2对该行记录的任何一种请求的锁都不能立即授予。相反,事务T2必须要等到事务T1释放在r 行上的锁。