前言朋友去阿里面试。他说面试官给了他几个查询SQL，问：我需要进行多少次树搜索操作？朋友当时有点懵，冷静一想，原来是为了索引。几个基础知识点~~本篇我们分九个指标知识点，一起来探讨。不对的地方还请指出，一起学习~面试官考点的索引是多少？面试官考点索引类型为什么选择B+树作为面试官考点索引结构覆盖索引面试官考点索引无效Scenario面试官考点最左边前缀面试官考点索引被推送down面试官考点大表添加索引1.面试官考点索引是多少？索引是一种可以提高数据库查询效率的数据结构。好比字典的目录，可以帮助你快速找到对应的记录。索引一般存储在磁盘上的文件中，占用物理空间。俗话说，水能载舟，亦能覆舟。合适的索引可以提高查询效率，过多的索引会影响数据库表的插入和更新功能。2.索引有哪些类型？数据结构维度B+树索引：所有数据都存储在叶子节点中，复杂度为O(logn)，适合范围查询。哈希索引：适用于等值查询，检索效率高，一次性到位。全文索引：MyISAM和InnoDB都支持使用全文索引，一般在文本类型char、text、varchar上创建。R-Tree索引：用于为GIS数据类型创建SPATIAL索引物理存储维度聚簇索引：聚簇索引是以主键创建的索引，表中的数据存储在叶子节点中。非聚集索引：非聚集索引是使用非主键创建的索引，主键和索引列存储在叶子节点中。逻辑维度主键索引：一种特殊的唯一索引，不允许空值。普通索引：MySQL中的基本索引类型，允许空值和重复值。联合索引：由多个字段创建的索引在使用时遵循最左前缀原则。唯一索引：索引列中的值必须是唯一的，但允许空值。空间索引：MySQL5.7支持空间索引，在空间索引方面遵循OpenGIS几何数据模型规则。3、面试官为什么选择B+树作为索引结构？这个问题可以从这几个维度来看，查询速度够不够快，效率是否稳定，存了多少数据，查盘次数等等。为什么不是哈希结构？为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而是B+树？我们在写业务SQL查询的时候，大多数情况下都是范围查询，如下：SQLselect*fromemployeewhereagebetween18and28;为什么不使用散列结构呢？我们知道hash结构类似于k-v结构，即key和value是一对一的关系。可用于“等值查询”，但对范围查询无能为力。为什么不使用二叉树？先回忆一下二叉树的相关知识~所谓“二叉树有如下特点：”每个结点至多有两棵子树，分别称为左子树和右子树。左子节点的值小于当前节点的值，当前节点的值小于右子节点的值。最上面的节点称为根节点，没有子节点的节点值称为叶节点。在我们的脑海中，这种二叉树结构图很容易浮现：但是，有一些特殊的二叉树，可能是这样的：如果将二叉树专门化为链表，就相当于全表扫描.那么为什么需要索引呢？因此，一般的二叉树不适合作为索引结构。为什么不用平衡二叉树呢？平衡二叉树的特点：也是二叉查找树，任意节点的两棵子树的高度最大差为1。所以链表不会特化。但是呢：平衡二叉树在插入或者更新的时候，需要左旋和右旋来保持平衡，维护成本高。如果数量很大，树的高度就会很高。因为数据存在于磁盘上，将其作为索引结构，每从磁盘读取一个节点，IO操作的次数就会增加。为什么不使用B树？如果数据量很大，平衡二叉树的高度会很高，会增加IO。那为什么不选择相同数据量的“更短的B树”呢？与平衡二叉树相比，B树可以存储更多的数据并且具有更低的高度。但是最后为什么选择B+树呢？因为B+树是B树的升级版：B+树非叶子节点不存储数据，只存储键值，而B树节点不仅存储键值，还存储数据。innodb中页面的默认大小是16KB。如果不存储数据，存储的key值越多，对应的树（节点的子节点树）的阶数越大，树越矮越胖。这样一来，我们需要为磁盘查找数据的IO次数就会再次减少，数据查询的效率也会更快。B+树索引的所有数据都存放在叶子节点中，数据按顺序排列，链表相连。那么B+树使得范围搜索、排序搜索、分组搜索和去重搜索变得异常简单。4.面试官考点“面试官：”的一个B+树索引查找过程假设有如下表结构，有这几条数据CREATETABLE`employee`(`id`int(11)NOTNULL,`name`varchar(255)DEFAULTNULL,`age`int(11)DEFAULTNULL,`date`datetimeDEFAULTNULL,`sex`int(1)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_age`(`age`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintoemployeevalues(100,'小伦',43,'2021-01-20','0');insertintoemployeevalues(200,'俊杰',48,'2021-01-21','0');insertintoemployeevalues(300,'子奇',36,'2020-01-21','1');insertintoemployeevalues(400,'Lihong',32,'2020-01-21','0');insertintoemployeevalues(500,'奕迅',37,'2020-01-21','1');insertintoemployeevalues(600,'小军',49,'2021-01-21','0');insertintoemployeevalues(700,'小燕',28,'2021-01-21','1');「面试官：」如果执行如下查询SQL，需要执行多少次树查找操作？可以画出对应的索引结构图~select*fromTemployeewhereage=32;”解析：“其实面试官就是考察应聘者是否熟悉B+树索引结构图。姜子之类的可以回答~先画出idx_age索引的索引结构图，大致如下：然后画出id主键索引，我们先画出簇索引结构图，如下：所以，大体流程执行这条SQL查询语句的是蒋子：查找idx_age索引树，将磁盘块1载入内存，由于32<37，查找左分支，在磁盘上寻址磁盘块2。将磁盘块2载入内存，继续在内存中遍历，找到age=32的记录，得到id=400。得到id=400后，返回id主键索引树。查找id主键索引树，将磁盘块1加载到内存中，遍历内存，找到400，但是B+树索引的非叶子节点不保存数据。索引会继续寻找400的右分支，到磁盘中去寻找磁盘块3。将磁盘块3加载到内存中，遍历内存，找到id=400的记录，得到该数据行R4。好的，你完成了。所以，这个SQL查询进行了几次树查找操作，是不是一步就搞清楚了？“特”，在idx_age二级索引树中找到主键id后，返回id主键索引查找的过程称为回表。什么是返回表？获取主键再返回主键索引查询的过程称为“返回表”5.面试官考点覆盖索引“面试官：”如果不用select*，而是用selectid，age,above主题进行了多少次树搜索操作？【解析】本题主要考查考生覆盖指数的知识点。回到idx_age索引树，可以发现查询选项id和age都在叶子节点上。所以可以直接提供查询结果，根本不需要回表~覆盖索引：在查询数据列中，不需要回表查看，就可以直接从索引列中得到想要的结果。也就是说，你的SQL使用的索引列数据覆盖了查询结果的列，就被认为是覆盖索引。因此，相对于上一题，省略了返回表的树搜索操作。6、面试官考点索引无效“面试官：”如果我在name字段上加一个普通的索引，然后用like模糊搜索，会执行多少查询？SQL如下：select*fromemployeewherenamelike'%杰伦%';》分析：》这里考察的知识点是like会不会导致不使用索引，我们先看SQL的explain执行计划。其实像模糊搜索会导致没有使用到索引，如下：所以，这条SQL最后会扫描整张表~在日常开发中，这几种操作可能会导致索引失效，如下：查询condition包含or，可能如果字段类型是字符串，where字段必须用引号括起来，否则索引可能会像通配符一样失败。对于联合索引，查询的条件列不是联合索引的第一列，索引失效。在索引列上使用mysql的内置函数，索引变得无效。对于索引列操作（例如，+、-、*、/），索引无效。在索引字段上使用(!=or<>,notin)时，可能会导致索引失败。在索引字段上使用isnull和isnotnull可能会导致索引失败。左连接查询或右连接查询关联的字段编码格式不同，可能会导致索引失败。mysql估计使用全表扫描比使用索引快，所以没有使用索引。7.面试官考点联合索引最左前缀原则面试官：如果我现在在name和age字段上加一个联合索引索引，下面的SQL会进行多少次树查找？先画索引树？选择*fromemployeewherenamelike'Small%'orderbyagedesc;《解析：》这里考察联合索引的最左前缀原则和like是否包含在索引中的知识点。组合索引树的示意图大致如下：联合索引项先按名字从小到大排序，如果名字相同则按年龄从小到大排序。面试官要求勾选第一个字是“小”的所有人。SQL'slike'small%'可以用在idx_name_age联合索引中。本次查询会沿着idx_name_age索引树寻找第一个词小的索引值，所以分别找到Xiaojun、Xiaolun、Xiaoyan，分别得到Id=600、100、700，然后回表三次，去查找相应的记录。这里最左边的前缀是small，就是字符串索引最左边的M个字符。实际上，这个最左边的前缀可以是联合索引的最左边的N个字段。比如复合索引(a,b,c)可以相当于构建三个索引(a),(a,b),(a,b,c)，大大提高了索引复用能力。最左边的前缀也可以是字符串索引的最左边的M个字符。八、面试官考点的索引下推“面试官：”我们还是住在复合索引idx_name_age中，下面的SQL进行了多少次树搜索？选择*fromemployeewherenamelike'small%'andage=28andsex='0';《解析：》这里考察索引下推的知识点。如果是“Mysql5.6之前”，在idx_name_age索引树中找到所有首字符为“small”的人，得到他们的主键id，然后回表找数据行，再对比其他年龄和性别等领域。如图：有的朋友可能会觉得奇怪，(name,age)不是联合索引吗？为什么在选择“小”字后检查年龄并返回表不是更有效率？于是，MySQL在5.6中引入了“索引下推优化”。在索引遍历过程中，可以先判断索引包含的字段，不满足条件的记录可以直接过滤掉，减少回表的次数。所以MySQL5.6版本以后，在选中包含“small”的词后，沿表过滤age=28，这样只需要回表一次即可。9、给面试官考点的大表加索引《面试官：》如果一张表的数据量级在千万级以上，那么，要给这张表加索引怎么办？”分析：“我们需要知道的一件事是，当给表加索引时，表会被锁住。如果不小心操作，可能会发生生产事故。可以参考以下方法：1.先新建一张与原表A数据结构相同的新表B2.在新表B中添加需要添加的新索引3.从中导入数据原表A到新表B4。将新表B重命名为原表的表名A，将原表A改为其他表名；总结与实践本文主要讲解索引的9个重点面试考点。帮助大家。接下来跟大家分享一下我最近在业务开发中遇到的索引SQL。让我们看看你是如何回答的。有兴趣的可以联系我一起讨论~题目如下：select*fromAwheretype='1'andstatus='s'orderbycreate_timedesc;假设type有9种，区分度还不错，但是status的区分度不高（有3种），那怎么加索引呢？是给type或者(type,status,create_time)联合索引还是(type,create_time)联合索引加单索引？看看谢谢MySQL有哪些索引类型？[1]大表加索引方案[2]MySQL实战45讲[3][1]什么是MySQL？索引类型?：https://segmentfault.com/q/1010000003832312[2]大表加索引方案：https://zhuanlan.zhihu.com/p/151460679[3]MySQL实战45讲：https://时间.geekbang.org/column/article/69636本文转载自微信公众号《捡到蜗牛的小男孩》，可通过以下二维码关注。转载请联系捡蜗牛的小男孩公众号。
