关于mysql索引:MySQL面试小抄索引考点二面总结

32次阅读

共计 3629 个字符,预计需要花费 10 分钟才能阅读完成。

《MySQL 面试小抄》索引考点二面总结

我是肥哥,一名不业余的面试官!

我是囧囧,一名踊跃找工作的小菜鸟!

囧囧示意:小白面试最怕的就是面试官问的知识点太抽象,本人无奈疾速定位到关键问题点!!!


本期次要面试考点

面试官考点之谈谈索引保护过程?页决裂?页合并?
面试官考点之简述一下查问时 B + 树索引搜寻过程?
面试官考点之什么是回表?
面试官考点之什么是索引笼罩?应用场景?
面试官考点之什么状况下会索引生效?
面试官考点之哪些状况下,可能会面临索引生效的问题?
面试官考点之 or 走索引和索引生效别离是什么场景?
面试官考点之哪些状况下须要创立索引?
面试官考点之联结索引之最左前缀准则?
面试官考点之索引下推场景?


面试官考点之谈谈索引保护过程?页决裂?页合并?

B+ 树为了保护索引有序性,在插入删除的时候须要做必要的保护,必要时候可能波及到 页决裂,页合并 过程!

首先假如每个叶子节点(数据页或磁盘块)只能存储 3 条索引和数据记录,如图

状况 1、新增行记录,ID=3,此时【数据页 1】未满,只须要在 data2 后新增 ID= 3 的行记录,B+ 树整体构造不须要进行调整

状况 2、新增行记录,ID=8,此时【数据页 2】已满,这时候须要申请一个新的数据页,而后移动局部数据过来。这个过程称为 页决裂

页决裂过程耗费性能,同时空间利用率也升高了

有决裂就有合并,当相邻两个页因为删除了数据,利用率很低之后,会将数据页做合并。合并的过程,能够认为是决裂过程的逆过程

当相邻两个页因为删除了数据,利用率很低之后,会将数据页做合并。合并的过程,能够认为是决裂过程的逆过程。

【数据页 2】删除了 ID=7,ID= 8 的行记录,此时【数据页 2】【数据页 3】利用率很低,将进行页合并。

面试官考点之简述一下查问时 B + 树索引搜寻过程?

筹备一张用户表,其中 id 为主键,age 为一般索引

CREATE TABLE `user` (`id` int(11) PRIMARY KEY,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from user where age=22 简述一下 B + 树索引搜寻过程?

假如要查问的记录

 id=5,name="张三",age=22

MySQL 为每个索引别离保护了一棵 B +Tree 索引树,

主键索引非叶子节点保护了索引键,叶子节点存储行数据;

非主键索引也称为二级索引,非叶子节点存储主键;

B+ 树索引搜寻过程

搜寻条件 age=22,可走 idx_age 索引,首先加载 idx_age 索引树,找到 age=22 的记录,获得 id=5

回表搜寻,加载主键索引树,找到 id=22 的记录,获得整行数据

面试官考点之什么是回表?

idx_age 二级索引树找到主键 id 后,回到 id 主键索引搜寻的过程, 就称为回表。

并非所有非主键索引搜寻,都须要进行回表搜寻,也就是上面要说的索引笼罩。

面试官考点之什么是索引笼罩?应用场景?

在下面提到的例子中,因为查问后果所须要的数据只在主键索引上有,所以不得不回表。

如果在查问的数据列外面,间接从索引列就能取到想要的后果,就不须要再回表去查,也称之为索引笼罩!

索引笼罩的长处

  1. 能够防止对 Innodb 主键索引的二次查问
  2. 能够防止 MyISAM 表进行零碎调用
  3. 能够优化缓存, 缩小磁盘 IO 操作

批改一下上述栗子,满足索引笼罩条件?

select id, age from user where age=22

查问的信息,id,age 都能够间接在 idx_age 索引树中获取,不须要回表搜寻。

因为笼罩索引能够缩小树的搜寻次数,显著晋升查问性能,所以应用笼罩索引是一个罕用
的性能优化伎俩。

索引是一把双刃剑,提供疾速排序搜寻的同时,索引字段的保护也是要付出相应的代价的。

因而,在建设冗余索引来反对笼罩索引时就须要衡量思考了

面试官考点之索引生效?

创立的索引,到底有没有失效,或者说 SQL 语句有没有应用索引查问?

一个最常见的查问场景,建设 idx_name 索引

select * from t_user where user_name like '%mayun100%';

这条查问是否走索引?

select * from t_user where user_name like 'mayun100%';

这条查问是否走索引?

面试官考点之有哪些状况下,可能会面临索引生效的问题?

  1. like 通配符,左侧凋谢状况下,全表扫描
  2. or 条件筛选,可能会导致索引生效
  3. where 中对索引列应用 mysql 的内置函数,肯定生效
  4. where 中对索引列进行运算(如,+、-、*、/),肯定生效
  5. 类型不统一,隐式的类型转换,导致的索引生效
  6. where 语句中索引列应用了负向查问,可能会导致索引生效 负向查问包含:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE 等。
  7. 索引字段能够为 null,应用 is null 或 is not null 时,可能会导致索引生效
  8. 隐式字符编码转换导致的索引生效
  9. 联结索引中,where 中索引列违反最左匹配准则,肯定会导致索引生效
  10. MySQL 优化器的最终抉择,不走索引

面试官考点之 or 走索引和索引生效别离是什么场景?

or 走索引和索引生效别离是什么场景?

OR 连贯的是同一个字段,雷同走索引

explain select * from t_user where user_name = 'mayun10' or user_name = 'mayun1000'

OR 连贯的是两个不同的字段,不走索引

给 address 列减少索引

alter table t_user add index idx_address(address);
explain select * from t_user where user_name = 'mayun10' or address = '浙江杭州 12';

OR 连贯的是两个不同字段,如果两个字段皆有索引,走索引

(插播,下一期:《MySQL 面试小抄》几种索引生效场景验证)

尽请关注:囧么肥事

面试小抄系列。

面试官考点之哪些状况下须要创立索引?

1. 主键主动建设惟一索引

2. 频繁查问的字段

3.JOIN 关联查问,作为外键关系的列建设索引

4. 单键 / 组合索引的抉择问题,高并发下偏向创立组合索引,创立时遵循最左前缀匹配准则

5.ORDER BY 查问中排序的字段,排序字段通过索引拜访大幅提高排序速度

6.GROUP BY 须要分组字段或查问中统计字段

面试官考点之联结索引之最左前缀准则

MySQL 建设多列索引(联结索引)有最左前缀的准则,即最左优先

当 MySQL 建设的是联结索引,假如以(a,b,c) 列作为联结索引,那么 MySQL 建树规定是什么?

咱们晓得 MySQL 会为每一个索引保护一颗 B +Tree,非叶子节点存储索引 key,叶子节点存储行数据 data。

联结索引(a,b,c) 相当于建设了 (a), (a,b), (a,b,c) 三个索引,MySQL 组装索引树时,是依照从左到右的程序来建设 B +Tree 的联结索引树的。

匹配索引状况一

假如(a,b,c)索引要搜寻的值为(‘ 张三 ’, 21, 100),检索数据时,匹配的程序就是 a,b,c。

B+Tree 会优先比拟 a 来确定下一步的所搜方向,如果 a 雷同再顺次比拟 b 和 c,最初失去检索的数据;

匹配索引状况二

假如(a,c)索引要搜寻的值为(‘ 张三 ’, 100),检索数据时,匹配的程序就是 a,b,c。

B+Tree 应用 a 来指定搜寻方向,但下一个字段 b 缺失,所以只能把 a 等于张三的数据都找到,而后再匹配 c 是 100 的数据。

匹配索引状况三

假如(b,c)索引要搜寻的值为(‘ 张三 ’, 21),检索数据时,无匹配程序

B+Tree 不晓得下一步该查哪个节点,因为建设搜寻树的时候 a 是第一个比拟因子,必须要先依据 a 来搜寻能力晓得下一步去哪里查问。此时索引生效!

索引项是依照索引定义外面呈现的字段程序排序的,最左前缀能够是联结索引的最左 N 个字段,也能够是字符串索引的最左 M 个字符。

面试官考点之索引下推场景?

索引下推,即缩小二级索引回表搜寻次数!!!

艰深说,缩小查问主键索引树次数,缩小磁盘 IO

建设联结索引 idx_age_weight

select * from user where age = 11 and weight = 98

5.6 之前搜寻过程是

在 idx_age_weight 索引树中匹配出所有的 age = 11 索引,拿到主键 id,回表去一条条再比对 weight 字段

如下图,须要进行 3 次回表搜寻操作

5.6 后的搜寻过程是
在 idx_age_weight 索引树中匹配出所有的 age = 11 索引,顺便对 weight 字段进行判断,过滤掉 weight = 100 的记录,而后再进行回表搜寻。

如下图,只须要进行 2 次回表搜寻操作

浏览原文:

《MySQL 面试小抄》索引考点二面总结

《MySQL 面试小抄》索引考点一面总结

随缘更新,整顿不易,欢送分割小白探讨,大神巴巴请绕路!

更多精彩内容,欢送关注微信公众号:囧么肥事 (或搜寻:jiongmefeishi)

正文完
 0