LIKE 和 REGEXP 示例查看 MySQL EXPLAIN 和 ICP

来衡量 SQL 语句的性能。大家都知道使用了短语EXPLAIN。各个领域大家也应该都了解，但是我觉得通过例子来理解更好。本文不会对每个领域进行过多的讨论。互联网上的许多大神都可以比我更好地总结这一点。本文基于LIKE 的实现以及中的正则表达式示例EXPLAIN中的正则表达式示例。

• 在分析SQL语句的执行情况时，主要用到的列有type，额外由以下官网提供，❝ sakila 数据库，附下载链接。
• 电影表的情况如下。其实只有idx_title索引：
  
• MySQL版本为：8.0+
• 阅读这篇文章：可以组合。

示例及分析

• 场景：根据标题匹配对应的电影，从最简单的开始。
• 注意：由于innodb引擎是效率的衡量标准，因此rows可能不准确。
• filtered：过滤列表示将按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有发生行过滤。值从100增加过滤量开始减少。 rows 显示检查的估计行数，rows ×filtered 显示将连接到下表的行数。例如，如果行数为 1000，筛选为 50.00 (50%)，则连接下表的行数为 1000 × 50% = 500。

翻译：这是对将要连接的行数的百分比估计按表条件过滤，通常与rows列结合使用，filtered × rows为按表条件过滤掉的行数。

title=

explain select title from film where title= 'ACE GOLDFINGER';
复制代码

• 结果与分析：索引等于常数值，类型为♿referen，使用reference键 是 idx_title ，索引的实际使用与'ACE GOLDFINGER'进行比较（即CONST，由于使用索引查找与常量匹配（读取的行数） 行为1），因为使用了覆盖索引，没有返回表获取数据行额外为用户索引。‍电影NULLrefidx_titleidx_title767。 使用索引

  explain select * from film where title= 'ACE GOLDFINGER';
  复制代码

  • 结果与分析：唯一的区别是查询必须返回表其他字段不覆盖索引，额外是NULL。

  id	select_type	表	分区	类型	possible_keyskeys	ref	行	过滤	额外
  1	简单	胶片	NULL	ref	idx_title	idx_title	767	。 NULL

  title LIKE

  • 每个人都知道有一个前缀索引假设LIKE语句的键之一位于位置%。我们来分析以下四种情况。

  %不在前面

  explain select title from film where title LIKE 'A%';
  复制代码

  • 结果与分析：这里需要注意的是类型和额外：1， type：在此处设置LIKE 被视为有限索引扫描，无需遍历所有索引。例如，索引 BETWEEN 1 AND 100 的效果。2.读取46行数据 3.额外：使用where；使用Index这两个值同时在一起意味着什么？我们先尝试一下，然后再详细介绍~

    id	select_type	table	partitions	type♼taste
    1	key_len	ref	行	已过滤	额外
    1	SIMPLE	电影	NULL	idx_title	767	NULL	46	100.00	使用位置;使用索引

    explain select * from film where title LIKE 'A%';
    复制代码

    • 结果与分析： 新增额外：使用索引条件。如下所述，其他字段与前面的语句没有区别

    id	select_type	table	partitions❙typekey可能key_len	参考	行	已过滤	额外
    1	SIMPLE	电影	NULLx-标题idx_title	767	NULL	46	100.00	使用索引条件

    %领先

    explain select title from film where title LIKE '%A';
    复制代码

    结果和分析： 1.首先，它不是有限索引搜索。相反，使用索引来查询整个表，因此type是index。 2、读取1000行，过滤掉1000*11.11的行数。

    id	select_type	表	分区	类型	possible_keyskeys	ref	行	过滤	额外
    1	简单	胶片	NULL	索引	null	idx_title	767	NULL111U。正在哪里；使用索引

    explain select * from film where title LIKE '%A';
    复制代码

    • 结果与分析： 1、不使用索引，全表扫描，按条件过滤。 2. 使用where：存储引擎检索到该行后，MySQL服务器对其进行过滤并返回（来自高性能MySQL，附录D解释）。 ‍电影NULLALLnullnullnull11111. 使用wheretitle REGEXP

      explain select title from film where title REGEXP '^A';
      复制代码

      • 结果与分析：正则表达式查询采用索引全表扫描，没有通过表条件过滤的行。 ‍ 电影
      NULLINDEXnullidx_title767NULL 其中；使用索引

      explain select * from film where title REGEXP '^A';
      复制代码

      • 结果与分析：没有使用索引，全表扫描。‍ movieNULLALLnullnullnull❀使用位置；使用索引 和 使用索引条件
        • 这里参考了官网，然后补充了一些自己的分析。欢迎大家指正错误。
        • 使用地点；使用索引：简单来说，我们可以单独来看，使用where意味着MySQL Server在存储引擎获取行之后对行进行过滤。 使用索引是综合索引查询，不需要返回表获取行数据。这总计为：使用覆盖索引检索行后，在 MySQL 服务器中完成过滤。

        奖励：如果额外值不是Using where并且表的连接类型是ALL或index，那么您的查询可能有问题。翻译：如果类型是ALL或者index，但是Extra没有使用where，那么查询语句可能有问题。 ？可以使用InnoDB和MyISAM。这里我截取InnoDB的关键信息。？ , ref_or_null 2.背景：没有ICP，存储引擎遍历索引来查找基表中的行并将其返回给MySQL服务器，MySQL服务器评估行的WHERE条件，没有ICP，存储引擎遍历索引来检索rows ，返回MySQL Server层进行WHERE条件过滤（看起来很眼熟，用的是where）。 3.原理：在启用ICP的情况下，如果可以仅使用索引中的列来评估WHERE条件的部分内容，则MySQL服务器将WHERE条件的这部分内容推送到存储引擎（如果WHERE条件或其部分内容仅通过以下方式）要使用索引中的列，MySQL服务器可以将WHERE子句的该部分交给存储引擎进行处理。 （由于它被移交给库存引擎，所以它是下推的......) 4.特性：对于InnoDB表，ICP仅用于二级索引。 ICP的目标是减少全行读取的次数，从而减少I/O操作。对于InnoDB聚集索引，整个记录已经读入InnoDB缓冲区。在这种情况下使用 ICP 不会减少 I/O。只能用在二级索引中，以减少从表中检索行数据的次数，减少MySQL服务器和存储引擎之间的I/O操作。聚集索引由于所有数据都已加载到InnoDB缓冲区中，因此I/O操作并没有减少。

        • 其他信息：

        1。 ICP 用于 range、ref、eq_ref 和 ref_or_null 访问方法，当需要访问整个表行时，仅用于 range、ref、eq_ref、ref_or_null 类型且需要返回表来获取行数据。2. 其他信息请参考官网

        • 这或许可以解释上面的结果：

        1。解释从标题为“A%”的电影中选择*；范围并需要返回表 2。解释 select title from movie where title LIKE 'A%';不需要返回表，ICP未启用 3. 解释 select title from movie where title LIKE '%A';索引，ICP无法使用

        • 如何开启和关闭ICP：

        SET optimizationr_switch = 'index_condition_pushdown=off'; SET optimizer_switch = 'index_condition_pushdown=on';

        • 关闭ICP后，运行explain select * from movie where title LIKE 'A%':

        id	select_type		sible_keys	key	key_len	ref	行	过滤	额外
        1	SIMPLE电影NU 范围	idx_title	idx_title	767	NULL	46	100.00	使用时

        结论 (在 InnoDB): sIC MySQL 定向到 区域中的二级索引、、eq_ref 、ullnn 数据access type and not an override 索引的优化机制（Using Index），将MySQL服务器中的数据过滤（可以通过二级索引处理）下推到数据引擎处理，减少查询次数将行数据读回到表中并减少 MySQL 查询的数量。服务器和存储引擎之间的I/O操作。

        总结

        本文以EXPLAIN为出发点，简单介绍 IC、 IC LIKE

        以及正则表达式的结果进行了介绍。

        作者：ZacPark
        链接：https://juejin.im/post/5e9412a9518825738115332e来源：掘金归作者所有。商业转载请联系作者获取授权。非商业转载请注明出处。