MySQL(六)–MySQL两种引擎的索引实现
InnoDB和MyISAM的特点及区别:
InnoDB:
- 支持事务操作(并且实现了SQL标准的四种隔离级别)
- 支持行级锁(查询速度快)和表级索
- 不支持全文索引(没有保存表的行数)
- 是聚簇索引(因为索引文件和数据哎同一个文件下)
- 是数据库默认的存储引擎
MyISAM:
- 不支持事务
- 不支持行级锁和外键
- 若表建立时指定存储引擎是MyISAM,则生成表时会生成3个文件:
- .frm文件存储表定义
- .MYI文件存储索引文件
- .MYD文件存储数据表中的数据
- 是非聚簇索引(因为索引文件和数据表中的数据是分开的)
区别:
- MyISAM是非事务的,InnoDB是事务安全的
- MyISAM是表级的,InnoBD是行级的
- MyISAM支持全文索引,InnoBD不支持全文索引
- MyISAM相对简单,效率优于InnoDB (小型应用可以考虑使用MyISAM)
- MyISAM表保存成文件形式,跨平台使用更加方便
以上为比较普通的区别,下面这几个是面试官比较相同的区别:
1) count运算上的区别: 因为MyISAM缓存有表meta-data(行数等),因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说,则没有这种缓存。
2) 是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。但是InnoDB 提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3)是否支持外键: MyISAM不支持,而InnoDB支持。
总结:
MyISAM更适合读密集的表,而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下,经常选择MyISAM作为主库的存储引擎
。
一般来说,如果需要事务支持,并且有较高的并发读取频率(MyISAM的表锁的粒度太大,所以当该表写并发量较高时,要等待的查询就会很多了),InnoDB是不错的选择。如果你的数据量很大(MyISAM支持压缩特性可以减少磁盘的空间占用),而且不需要支持事务时,MyISAM是最好的选择。
MyISAM的索引实现:
MyISAM引擎使用B+Tree作为索引结构,叶子节点的data域存放的是数据记录的地址。
可以看出,MyISAM的索引文件仅仅保存数据记录的地址。
在MyIASM中,主索引和辅助索引在结构上没有任何区别,只要主索引要求key是唯一的,而辅助索引的key可以重复。
因此,MyISAM中索引检索算法为首先按照B+Tree搜索算法搜索索引,若指定的key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
==>MyISAM的索引方式也叫“非聚集的”
InnoDB的索引实现:
虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
第一个重大区别:
InnoDB的数据本身就是索引文件
。MyISAM索引文件和数据文件是分离的,索引文件仅仅保存数据记录的地址
- 而InnoDB:
表数据本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录,这个索引的key是数据表的主键。所以,InnoDB表数据文件本身就是主索引。
叶子结点包含了完整的数据记录,这种索引叫做聚集索引。因为InnoDB的数据本身要按主键聚集。所以,InnoDB要求表必须有主键(MyISAM可以没有),若没有显示指定,则会自动选择一个可以唯一标识数据记录的列作为主键,若不存再这种列,则会生成一个隐含字段作为主键。(使用自增字段作为主键时一个很好的选择)
第二个不同:InnoDB的辅助索引data域存储相应记录主键的值而不是地址。也就是说,InnoDB的所有辅助索引都引用主键作为data域。
下图为辅助索引:
聚簇索引这种实现方式使得按主键的搜索十分高效。
但是辅助索引搜索需要检查两遍索引:首先检查辅助索引获得主键,然后根据主键到主索引中检索获得的记录。这也就是为什么:为什么不建议使用过长的字段作为主键。因为,所有辅助索引变得过大。
这里涉及到一道面试题:
InnoDB索引种类:
- 主键索引称为聚集索引
- 非主键索引称为非聚集索引或者辅助索引
辅助索引:一个非主键索引对应一个辅助索引,一个表可以有多个辅助索引,辅助索引域聚集索引的区别在于叶节点。
辅助索引的叶节点数据页存的不是真实记录,而是对于记录的主键。这种辅助索引名称的由来,通过这个索引,仅仅能查到某些记录的主键,这些主键去查主键索引,得到相应的记录。
感谢并参考:
https://blog.csdn.net/qfc8930858/article/details/89226320