侧边栏壁纸
  • 累计撰写 244 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论
隐藏侧边栏

范围匹配查询时索引的使用和 explain 结果字段解析

kaixindeken
2021-05-03 / 0 评论 / 0 点赞 / 176 阅读 / 3,932 字

范围匹配如何使用索引

顺序匹配

所谓范围查询指的是匹配值在某个范围集合中的所有结果集,比如下面这些 SQL 语句:

SELECT * FROM `demo` WHERE `a` > 1000 AND `a` < 1100;
SELECT * FROM `demo` WHERE `b` BETWEEN 1000 AND 1100;

你可以在头脑中回顾下 B+ 树叶子节点的数据结构,由于所有索引值都是按照升序排列好了,对于这种匹配值连成一片的范围查询,如果查询列设置了索引,只需要确定好开头和结尾两个索引值的位置,然后把中间的数据一起返回接好了(主键索引直接返回索引数据,二级索引还需要做一次回表操作)。

对于这种类型的范围查询,如果查询列设置了索引,性能自然很高,不过,如果条件设置不恰当,则可能导致范围查询近似于全表扫描:

SELECT * FROM `demo` WHERE `a` > 1100;
SELECT * FROM `demo` WHERE `a` <> 1100;

IN 查询(离散匹配)

显然,这种范围查询仅适合数值类型字段的匹配,对于字符串类型就无能为力了,如果想对字符串列进行范围查询,可以使用 IN 查询,IN 查询支持不同类型的字段值,只是这个时候的匹配值就是离散分布的了:

SELECT * FROM `demo` WHERE `a` IN (1000, 1050, 1100);
SELECT * FROM `demo` WHERE `d` IN ('aaa', 'bbb', 'ccc');

其实字符串模糊匹配也是一种范围查询:

SELECT * FROM `demo` WHERE `d` LIKE 'xxx%';

另外,如果你深入思考一下,对于最上面两种通过比较运算符或者 BETWEEN...AND... 语句实现的范围查询,如果涉及到回表操作,从二级索引获取到主键索引值后,需要到聚簇索引去获取完整数据记录,这个时候就存在取出的主键索引值可能并不连续,所以这一步依然是一个离散的范围匹配。

对于这种离散的范围查询,如果数据表数据记录很多,可能需要跨多个数据页去查找对应的索引值(假设对应字段所在列设置了索引),所以性能肯定不如第一种顺序匹配。

两种范围匹配的性能对比

我们可以对比一下两种范围查询的耗时来验证上述结论:

1.jpeg

可以看到,对于同一个字段使用顺序范围匹配和 IN 查询的性能差别还是很大的,同理,对于主键索引和二级索引的顺序范围匹配,也存在性能差距,这是因为后者回表存在离散匹配:

1.jpeg

如果没有回表操作的话,就没有什么区别了:

1.jpeg

如果某个字段没有设置索引,则性能会很差,我们也可以看到范围匹配和全值匹配一样在使用联合索引时遵循最左前缀原则:

1.jpeg

对于字符串类型的范围查询,我们以模糊匹配为例,可以看到应用索引与否,性能的对比达到了68 倍之巨,同样也遵循最左前缀原则:

1.jpeg

我们也可以把顺序范围匹配叫做顺序 IO,把离散范围匹配叫做随机 IO。可以看到顺序 IO 的性能远远胜过随机 IO。

组合使用全值匹配和范围匹配

如果 SQL 查询语句中同时使用了全值匹配和范围匹配,那么系统会怎么选择呢?这就是 MySQL Server 层查询优化器所做的工作了,它会判定一条 SQL 语句是否可以使用索引,如果有多个索引的话,尽可能选择最优的组合去执行(当然,也不一定总是能选择最优的方案,也有「失手」的时候,后面我们会专门介绍),这里的最优组合就是扫描行数最少的。

比如对于包含全值匹配和范围匹配的 SQL 语句,如果全值匹配可以使用索引,则不管范围匹配是否使用了索引,通常先进行全值匹配,然后在其基础上进行范围匹配过滤性能往往最好;如果包含多个范围匹配,则先使用扫描行数更少的范围匹配过滤,如果包含主键索引,又有回表操作,则优先使用主键索引进行范围匹配,避免回表,等等(以上分析不是绝对的,MySQL 优化器会结合具体的场景进行分析得出结论)。

EXPLAIN 语句解析

MySQL 专门提供了一个 explain 语句让我们在执行查询语句之前可以先查看优化器使用的执行计划:

1.jpeg

我们来简单分析下 explain 语句返回结果的数据结构都是什么意思:

  • id:查询序列号,标识执行顺序,SQL 语句中出现了几个 SELECT 关键字,就会分配几个查询 id,一般对于单表查询和连接查询,id 值都是 1,对于联合查询和子查询,id 值会有多个;
  • select_type:查询类型,主要是用于区分普通查询、联合查询、子查询等,其中 SIMPLE 表示普通查询(包含连接查询)、UNION 表示联合查询、SUBQUERY 表示子查询;
  • table:要执行查询的数据表;
  • partitions:表分区(通常不会用到,可忽略);
  • type:SQL 查询优化的重要指标,表示执行计划用到的索引类型,性能由高到低依次为:system > const > eq_ref > ref > range > index > ALL(以下是常见的类型值,不是所有):
    • system 表示系统表查询或者 MyISAM、Memory 引擎表中只包含一条记录;
    • const 表示常量查询,通常主键索引或者唯一索引的单表等值匹配查询就是这种类型,比如上述第一个
    • explain 语句得到的执行计划就是;
    • eq_ref 在连接查询中,被驱动表的主键索索引或唯一索引等值匹配会使用这个类型,后面我们介绍连接查询时会演示;
    • ref 通常对应二级索引的等值匹配查询,比如 b = 10000;
    • range 表示范围匹配,比如上述第二个 explain 语句得到的执行计划就是;
    • index 表示索引树扫描,通常 count 查询会应用这个计划;
    • ALL 表示全表扫描,性能最差,通过对于没有设置索引的列进行过滤就会进行全表扫描。
  • possible_keys:查询过程中有可能用到的索引;
  • key:查询时实际使用的索引,如果为 NULL,则表示没有使用索引;
  • key_len:索引长度,越小越好,整型通常是 4,字符串类型根据创建索引时指定的长度设定;
  • ref:表示表的连接匹配条件,即哪些列或常量被用于查找索引列上的值,对于 const 类型单表等值匹配而言,其值是 const,对于连接查询而言,就是被连接的列;
  • rows:返回估算的结果集行数,不一定准确;
  • filtered:表示返回结果的行数占需读取行数的百分比,越大越好;
  • extra:展示一些额外信息(以下只是常见的额外信息,不是所有):
    • Using index 表示使用了覆盖索引;
    • Using where 表示使用了 where 子句来过滤结果集;
    • Using filesort 表示查询过程中使用了文件排序,使用非索引列进行排序时出现,非常消耗性能,排序、分组查询时通常会用到;
    • Using temporary 表示查询过程中使用了临时表,分组、联合查询时通常会用到;
    • Using join buffer 表示连接查询时使用了 join buffer;
    • Start temporary, End temporary 表示 IN 子查询转化为 semijoin 时使用了 DuplicateWeedout 策略;
    • LooseScan 表示 IN 子查询转化为 semijoin 时使用了 LooseScan 策略;
    • FirstMatch 表示 IN 子查询转化为 semijoin 时使用了 FirstMatch 策略。

有了以上介绍,想必你已经可以完全可以独立执行 explain 语句分析业务代码中的 SQL 语句,并进行相应的优化了,你可以以这里的 demo 表为例分析几个 SQL 查询,然后通过返回的字段分析是对应的查询语句是如何使用索引的。

强制使用索引

当然,「智者千虑,必有一失」,MySQL 优化器生成的执行计划并总是最优的,因为执行计划最核心的生成策略就是基于扫描行数,而这个扫描行数在计算过程中存在基于特定策略的估算,既然是估算,就有误差。在极少数情况下,就会存在明明设置了索引,但是没有使用到的情况,在这种情况下,你可以通过强制使用索引的方式来使用这个索引:

select * from `demo` force index(b_c) where b between 1000 and 2000;

注意 index 函数中传入的是索引名称。

0

评论区