专栏首页Java架构沉思录快速理解为啥这个查询使用索引,那个查询不使用索引,学会了才发现:真tm简单

快速理解为啥这个查询使用索引,那个查询不使用索引,学会了才发现:真tm简单

B+树结构

我们说对于InnoDB存储引擎来说,表中的数据都存储在所谓的B+树中,我们每多建立一个索引,就相当于多建立一棵B+树。

  • 对于聚簇索引对应的B+树来说,叶子节点处存储了完整的用户记录(所谓完整用户记录,就是指一条聚簇索引记录中包含所有用户定义的列已经一些内建的列),并且这些聚簇索引记录按照主键值从小到大排序。
  • 对于二级索引对应的B+树来说,叶子节点处存储了不完整的用户记录(所谓不完整用户记录,就是指一条二级索引记录只包含索引列和主键),并且这些二级索引记录按照索引列的值从小到大排序。

我们向表中存储了多少条记录,每一棵B+树的叶子节点中就包含多少条记录(注意是“每一棵”,包括聚簇索引对应的B+树以及二级索引对应的B+树)。

示例

我们举个例子:

CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    key1 INT,
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

这个表就包含2个索引(也就是2棵B+树):

  • id列为主键对应的聚簇索引。
  • key1列建立的二级索引idx_key1

我们向表中插入一些记录:

INSERT INTO t VALUES
    (1, 30, 'b'),
    (2, 80, 'b'),
    (3, 23, 'b'),
    (4, NULL, 'b'),
    (5, 11, 'b'),
    (6, 53, 'b'),
    (7, 63, 'b'),
    (8, NULL, 'b'),
    (9, 99, 'b'),
    (10, 12, 'b'),
    (11, 66, 'b'),
    (12, NULL, 'b'),
    (13, 66, 'b'),
    (14, 30, 'b'),
    (15, 11, 'b'),
    (16, 90, 'b');

所以现在s1表的聚簇索引示意图就是这样:

s1表的二级索引示意图就是这样:

从图中可以看出,值为NULL的二级索引记录都被放到了B+树的最左边,这是因为设计InnoDB的大叔们有规定:

We define the SQL null to be the smallest possible value of a field.

也就是认为NULL值是最小的。

小贴士:原谅我们把B+树的结构做了一个如此这般的简化,我们省略了页面的结构,省略了所有的内节点(只画了了三角形替代),省略了记录之间的链表,因为这些不是本文的重点,画成如果所示的样子只是为了突出叶子节点处的记录是按照给定索引的键值进行排序的。

比方说我们现在执行下边这个查询语句:

SELECT * FROM t WHERE key1 = 53;

那么语句的执行过程就如下图所示:

用文字描述一下这个过程也就是:

  • 先通过二级索引idx_key1对应的B+树快速定位到key1列值为53的那条二级索引记录。
  • 然后通过二级索引记录上的主键值,也就是6到执行回表操作,也就是到聚簇索引中再找到id列值为6的聚簇索引记录。

小贴士:B+树叶子节点中的记录都是按照键值按照从小到大的顺序排好序的,通过B+树索引定位到叶子节点中的一条记录是非常快速的。不过由于我们并没有唠叨内节点、页目录这些东西,所以通过B+树索引定位到叶子节点中的一条记录的过程就不详细唠叨了,这些东西其实都在《MySQL是怎样运行的:从根儿上理解MySQL》的掘金小册里详细讲述过。

像下边这个查询:

SELECT * FROM t WHERE key1 > 20 AND key1 < 50;

它的执行示意图就是这样:

用文字表述就是这样:

  • 先通过二级索引idx_key1对应的B+树快速定位到满足key1 > 20的第一条记录,也就是我们图中所示的key1值为23的那条记录,然后根据该二级索引中的主键值3执行回表操作,得到完整的用户记录后发送到客户端。
  • 然后根据上一步骤中获取到的key1列值为23的二级索引记录的next_record属性,找到紧邻着的下一条二级索引记录,也就是key1列值为30的记录,然后执行回表操作,得到完整用户记录后发送到客户端。
  • 然后再找上一步骤中获取到的key1列值为30的二级索引记录的下一条记录,该记录的key1列值也为30,继续执行回表操作将完整的用户记录发送到客户端。
  • 然后再找上一步骤中获取到的key1列值为30的二级索引记录的下一条记录,该记录的key1列值为53,不满足key1 < 50的条件,所以查询就此终止。

从上边的步骤中也可以看出来:需要扫描的二级索引记录越多,需要执行的回表操作也就越多。如果需要扫描的二级索引记录占全部记录的比例达到某个范围,那优化器就可能选择使用全表扫描的方式执行查询(一个极端的例子就是扫描全部的二级索引记录,那么将对所有的二级索引记录执行回表操作,显然还不如直接全表扫描)。

小贴士:我们这里还是定型的分析成本,而不定量分析。定量分析的过程比较复杂,不过小册里有写,有兴趣的同学可以去看。

所以现在的结论就是:判定某个查询是否可以使用索引的条件就是需要扫描的二级索引记录占全部记录的比例是否比较低,较低的话说明成本较低,那就可以使用二级索引来执行查询,否则要采用全表扫描。

具体的查询条件分析

我们分别看一下WHERE子句中出现IS NULLIS NOT NULL!=这些条件时优化器是怎么做决策的。

IS NULL的情况

比方说这个查询:

SELECT * FROM t WHERE key1 IS NULL;

优化器在真正执行查询前,会首先少量的访问一下索引,调查一下key1[NULL, NULL]这个区间的记录有多少条:

小贴士:[NULL, NULL]这个区间代表区间里只有一个NULL值。

优化器经过调查得知,需要扫描的二级索引记录占总记录条数的比例是3/16,它觉得这个查询使用二级索引来执行比较靠谱,所以在执行计划中就显示使用这个idx_key1来执行查询:

IS NOT NULL的情况

比方说这个查询:

SELECT * FROM t WHERE key1 IS NOT NULL;

优化器在真正执行查询前,会首先少量的访问一下索引,调查一下key1(NULL, +∞)这个区间内记录有多少条:

小贴士:我们这里把NULL当作是最小值对待,你可以认为它比-∞都小。另外注意区间(NULL, +∞)是开区间,也就意味这不包括NULL值。

优化器经过调查得知,需要扫描的二级索引记录占总记录条数的比例是13/16,跟显然这个比例已经非常大了,所以优化器决定使用全表扫描的方式来执行查询:

那怎么才能让使用IS NOT NULL条件的查询使用到二级索引呢?这还不简单,让表中符合IS NOT NULL条件的记录少不就行了,我们可以执行一下:

UPDATE t SET key1 = NULL WHERE key1 < 80;

这样再去执行这个查询:

SELECT * FROM t WHERE key1 IS NOT NULL;

优化器在真正执行查询前,会首先少量的访问一下索引,调查一下key1(NULL, +∞)这个区间内记录有多少条::

优化器经过调查得知,需要扫描的二级索引记录占总记录条数的比例是3/16,它觉得这个查询使用二级索引来执行比较靠谱,所以在执行计划中就显示使用这个idx_key1来执行查询:

!= 的情况

比方说这个查询:

SELECT * FROM t WHERE key1 != 80;

优化器在真正执行查询前,会首先少量的访问一下索引,调查一下key1(NULL, 80)(80, +∞)这两个区间内记录有多少条:

优化器经过调查得知,需要扫描的二级索引记录占总记录条数的比例是2/16,它觉得这个查询使用二级索引来执行比较靠谱,所以在执行计划中就显示使用这个idx_key1来执行查询:

且慢!为啥执行计划的rows列的值为3呢???这是个什么鬼,明明只有2条记录符合条件嘛。哈哈,我们罗列一下每个区间找到的符合条件的记录数量:

  • (NULL, 80)区间中有0条记录满足条件key1 != 80
  • (80, +∞)区间中有2条记录满足条件key1 != 80

可是设计优化器的大叔在这里有个规定:当某个范围区间符合给定条件的记录数量为0时,硬生生的把它掰成1。也就是说实际优化器认为在(NULL, 80)这个范围区间中有1条记录符合条件key1 != 80。所以执行计划的rows列才显示了3

小贴士:下边是设计优化器的大叔自己对当某个范围区间符合给定条件的记录数量为0时硬生生的把它掰成1的解释(能看懂的就看,看不懂赶紧跳过):The MySQL optimizer seems to believe an estimate of 0 rows is always accurate and may return the result 'Empty set' based on that. The accuracy is not guaranteed, and even if it were, for a locking read we should anyway perform the search to set the next-key lock. Add 1 to the value to make sure MySQL does not make the assumption!

总结

至此,我们分别分析了拥有IS NULLIS NOT NULL!=这三个条件的查询是在什么情况下使用二级索引来执行的,核心结论就是:成本决定执行计划,跟使用什么查询条件并没有什么关系。优化器会首先针对可能使用到的二级索引划分几个范围区间,然后分别调查这些区间内有多少条记录,在这些范围区间内的二级索引记录的总和占总共的记录数量的比例达到某个值时,优化器将放弃使用二级索引执行查询,转而采用全表扫描。

小贴士:其实范围区间划分的太多也会影响优化器的决策,比方说IN条件中有太多个参数,将会降低优化器决定使用二级索引执行查询的几率。另外,优化器调查在某个范围区间内的索引记录的条数的方式有两种,一种是所谓的index dive(这种方式在数据少的时候是精确的,在数据多时会有些偏差),一种是依赖index statistics,也就是统计数据来做调查(这种方式的统计是很不精确的,有时候偏差是超级巨大的),反正不论采用哪种方式,优化器都会将各个范围区间中的索引记录数量给计算出来。关于这两种调查方式在小册中都给出了详细的算法,当然都占用了相当大的篇幅,写在公众号文章里就有点杀鸡用牛刀了。

历史热文:

本文分享自微信公众号 - Java架构沉思录(code-thinker)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-11-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!

    这种说法愈演愈烈,甚至被很多同学奉为真理。咱啥话也不说,举个例子。假如我们有个表s1,结构如下:

    黄泽杰
  • 告别垃圾代码,请收下这几个锦囊妙计!

    对软件内部结构的一种调整,目的是在不改变软件可观察行为的前提下,提高其可理解性,降低其修改成本。

    黄泽杰
  • 聊一聊 InnoDB 引擎中的这些索引策略

    这一篇我们学习 InnoDB 的索引,聊一聊索引策略,更好的利用好索引,提升数据库的性能,主要聊一聊覆盖索引、最左前缀原则、索引下推。

    黄泽杰
  • 014.Nginx跨域配置

    同源策略是一个安全策略。同源,指的是协议,域名,端口相同。浏览器处于安全方面的考虑,只允许本域名下的接口交互,不同源的客户端脚本,在没有明确授权的情况下,不能读...

    木二
  • Python---限定函数参数的类型及默

    python作为一门动态语言,在使用变量之前是不需要进行定义,而是通过动态绑定的方法将变量绑定为某种类型。这样做为我们使用变量时提供了方便,但有时也给我们使用变...

    py3study
  • [未解决]报错: pytesseract.pytesseract.TesseractNotFoundError: tesseract is not installed or it's not in y

    hankleo
  • 黄希鸣发公开信正式承认重大经营困难,博郡汽车是否走到尽头?

    黄希鸣代表博郡汽车,向社会各方表达真诚的歉意,也希望公司能获得各方的谅解,为全面履行职责义务留下空间和机会。

    镁客网
  • 在终端打印地图

    宋天伦
  • JavaScript回调函数

    JavaScript的回调函数大概是JavaScript中使用最广泛的函数编程技术了,我们几乎可以在任何脚本中看到它的身影。

    wade
  • 7 Papers & Radios | 微软亚研麻将AI「Suphx」技术细节;港中文、商汤动作识别时序金字塔网络

    论文 1:Background Matting: The World is Your Green Screen

    机器之心

扫码关注云+社区

领取腾讯云代金券