本文将深入探讨 MySQL,这是关系型数据库中的核心技术,被广泛应用于数据存储和管理。透过高频面试题解析,我们将深入研究 MySQL 在数据建模、查询优化和事务处理中的作用。无论你是初学者还是渴望加深对关系型数据库技术的了解,本文都将为你提供实用的面试准备。
一、innodb索引和myisam索引的区别
1、myisam的所有方式都是非聚簇的,innodb的索引方式有一个是聚簇的。
2、在innodb存储引擎中,我们只需要根据主键对聚簇索引进行一次查找就能找到对应的记录。而myisam索引文件中确需要进行一次回表操作,意味着myisam中建立的索引相当于全部都是二级索引。
3、innodb的数据文件本身就是索引文件,而myisam索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
4、innodb的非聚簇索引data域记录的是主键值,而myisam索引记录的是地址。
5、myisam的回表操作非常快快速,因为是拿着地址偏移量直接到文件中取数据的,而innodb是通过获取主键之后再去聚簇索引里找记录,虽然也不慢,但比不上直接用地址去访问。
6、innodb要求必须有主键,myisam可以没有。如果没有显示指定,则mysql系统会自动选择一个非空且唯一标识数据记录的列作为主键,如果不存在这种列,mysql自动为innodb表生成一个隐含自动作为主键。
二、B+树索引实现原理
三、什么是自适应哈希索引
自适应哈希索引是innodb引擎的一个特殊功能,当它注意到某些索引值被使用的非常频繁时,会在内存中基于b树再创建一个哈希索引,这就让b树索引也具有hash索引的一些特点。
四、聚簇索引和非聚簇索引b+树实现有什么区别
1、聚簇索引
(1)特点
索引和数据保存在同一个b+树;
页内的记录是按照主键的大小顺序排成一个单向链表;
页与页之间也是根据页中记录主键的大小顺序排成一个双向链表;
非叶子节点存储的是记录的主键+页号;
叶子节点存储的是完整的用户记录。
(2)优点
数据访问更快,因为索引和数据保存在同一个B+树中
聚簇索引对于主键的排序查找和范围查找速度非常快
2、非聚簇索引
聚簇索引只有在搜索条件是主键值时才发挥作用,因为b+树中的数据都是按照主键进行排序的,如果我们想以别的列作为搜索条件,那么需要创建非聚簇索引。例如以c2列作为搜索条件,,那么需要使用c2列创建一个b+树。
特点:
页内的记录是按照从c2列的大小顺序排列成一个单向链表;
页于页之间也是根据页中记录的c2列大小的顺序排成一个双向列表;
非叶子节点存储的是记录的c2列+页号;
叶子节点存储的并不是完整的记录,而是c2列+主键这两个列的值;
一张表可以有多个非聚簇索引。
五、平衡二叉树,红黑树,b树和b+树的区别是什么
1、平衡二叉树
基础数据结构,左右平衡,高度差大于1会自旋,每个节点记录一个数据
特点:它是一棵空树或它的左右两个子树的高度差的绝对值不超过1;并且左右两个子树都是一棵平衡二叉树
2、红黑树
hashmap存储;两次旋转达到平衡;分为红黑节点;红黑树的长子树只要不超过短子树的两倍即可;
节点非黑即红;根节点是黑色,所有null节点成为叶子节点,且认为颜色为黑;所有红节点的子节点都为黑色;
从任一节点到其叶子节点的所有路径上都包含相同数目的黑节点。
3、b+树和b树的差异
(1)b+树中非叶子节点的关键字也会同时存在在子节点中,并且是在子节点中所有关键字的最大值(或最小值)
(2)b+树中非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放在叶子节点中。而b树中,非叶子节点既保存索引,也保存数据记录
(3)b+树中所有关键字都在叶子节点出现,叶子节点构成一个有序链表,而且叶子节点本身按照关键字的大小从小到大顺序链接
六、非聚簇索引为什么不存数据地址值而存储主键
聚簇索引中有时会引发分页操作、重排操作数据有可能会移动
七、复合索引创建时字段顺序不一样使用效果一样吗
1、先把各个记录和页按照c2列进行排序
2、在记录的c2列相同的情况下,采用c3列进行排序
3、b+树叶子节点的记录由c2、c3列和主键c1列组成
4、本质上也是二级索引
八、索引类型
1、主键索引:列设为主键后会自动建立索引,唯一且不能为空
2、唯一索引:索引列值必须唯一,允许由null值
3、普通索引:既不是主键,列值也不需要唯一
4、复合索引:一个索引包含多个列
九、什么时候适合创建索引,什么时候不适合创建索引
1、适合创建索引
频繁作为where条件语句查询字段
关联字段需要建立索引
排序字段可以建立索引
分组字段可以建立索引
统计字段可以建立索引
2、不适合创建索引
频繁更新的字段不适合建立索引
where,分组,排序中用不到的字段不必要建立索引
可以确定表数据非常少不需要建立索引
参与mysql函数计算的列不适合建立索引
十、索引下推
1、未开启索引下推
根据筛选条件在索引树中筛选第一个条件
获得结果集后回表操作
进行其他条件筛选
再次进行回表操作
2、开启索引下推
在条件查询时,当前索引树如果满足全部筛选条件,可以在当前树中完全全部筛选过滤,得到比较小的结果集再进行回表操作
十一、有哪些情况会造成索引失效
1、like以%,_开头索引失效
2、不等于(!= 或者<>)索引失效
3、is not null索引失效
4、类型转换导致索引失效
5、复合索引未用左列字段失效
6、如果mysql觉得全表扫描更快时(数据少)索引失效
十二、多个索引优先级是如何匹配的
主键匹配、全值匹配、最左前缀匹配、范围匹配、索引扫描、全表扫描
若有收获,就点个赞吧