MySQL常见面试题之存储引擎

最新推荐文章于 2024-10-30 14:55:37 发布

*吴聪聪*

最新推荐文章于 2024-10-30 14:55:37 发布

阅读量519

点赞数 1

分类专栏： MySQL面试文章标签： mysql 数据库面试

本文链接：https://blog.csdn.net/weixin_46410481/article/details/120398731

版权

MySQL面试专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL常见面试题之存储引擎

mysql默认搜索引擎

mysql5.5以后默认使用InnoDB为搜索引擎

MyISAM是表锁，不支持事务和主外键
在这里插入图片描述
InnoDB默认可以创建16个索引

InnoDB支持事务，MyIsam不支持事务，对于InnoDB每一条sql语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条sql语言放到begin和commit之间，组成一个事务。
InnoDB支持外键，而MyIsam不支持，对一个包含外键的InnoDB表转成MyIsam表会失败、
InnoDB是聚簇索引，数据文件和索引绑定在一起，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据。因此主键不应该过大。主键过大的时候，其他索引也会很大。而MyIsam是非聚簇索引，数据和文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
InnoDB不支持全文检索，而MyIsam支持全文检索，查询效率上MyiIsam要高

索引

MySQL官方对索引的定位为：索引是帮助MySQL高效获取数据的数据结构，可以得到索引的本质就是，索引是数据结构。

可以简单的理解为：排好序的快速查找B+树数据结构，B+树中的B代表平衡(balance)而不是二叉(binary)

检索原理

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据上实现高级查找算法，这种数据结构，就是索引。下图就是一种可能的索引方式示例：
在这里插入图片描述
为了加快Col2的查找，可以维护一个左边所示的二叉树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉树在一定的复杂度内获取相应数据，从而快速的检索出所有复合条件的记录。

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到一起，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开，索引结构的叶子节点指向了数据对应的位置

在InnoDB搜索引擎中，在聚簇索引之上创建的索引被称为辅助索引，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找，这个就被称为回表操作。
在这里插入图片描述
InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，若使用where id=4这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶子节点，之后获得行数据。

若对name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树种检索name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中在执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放在一起，因此一个表中仅仅有一个聚簇索引。

表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这时因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序

聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引来代替，如果没有这样的索引，InnoDB会隐式的定义一个主键（类似Oracle中的rowid）来作为聚簇索引。如果已经创建了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复成主键即可。

MyIsam使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助索引B+树的存储了辅助键。表数据存储再独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
在这里插入图片描述
使用聚簇索引的优势

**每次使用辅助索引检索都要经过两次B+树查找，**看上去聚簇索引的效率明显要低于非聚簇索引，这不是多次一举吗？聚簇索引的优势在哪？

由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录的时候，已经把页加载加载到了Buffer中（缓冲器），再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键id来组织数据，获得数据更快。
辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据发生变化，索引树的节点也需要发生分裂变化，或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作的时候，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就可以了，另一个好处是，因为辅助索引存放的是主键值，减少辅助索引占用的存储空间大小。

注：我们直到一次IO读写，可以获取到16K大小的资源，我们称之为读取到的数据区域为page。而我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO操作中被读取到缓存中，所以在访问同一个页中的不同记录的时候，会在内存中操作，而不用再次进行IO操作了，除非发生了页的分裂，即要查询的行数据不再上次IO操作的缓存里，才会触发新的IO操作。
因为MyIsam的主索引并非聚簇索引，那么它的数据结构的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行IO读取，于是开始不停的寻道不停的旋转。聚簇索引则只需要一次IO。
不过如果涉及到大量的数据的排序、全表扫描、count之类的操作的话，还是MyIsam占优势一些，因为索引所占的空间小，这些操作是需要再内存中完成的。

聚簇索引需要注意的地方

当使用主键为聚簇索引的时候，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入再索引树中间的位置，导致所引述调整复杂度变大，消耗更多的时间和资源。

建议使用int类型的自增，方便排序并且默认会再索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id

占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据地物理地址、分页，当然也有一些措施来减少这些操作，但却无法彻底避免。但如果是自增地，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。