【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查询走索引的原理

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查询走索引的原理
一、背景
今天,交流群有一位同学提出了一个问题。看下图:

之后,这位同学确实也发了一个全模糊查询走索引的例子:

到这我们可以发现,这两个sql最大的区别是:一个是查询全字段(select *),而一个只查询主键(select id)。

此时,又有其他同学讲了其他方案:

全文索引这个不用说,那是能让全模糊查询走索引的。但是索引覆盖这个方案,我觉得才是符合背景的:

1、因为提问的背景就是模糊查询字段是普通索引,而普通索引只查询主键就能用上覆盖索引。

2、并且背景中,就是只查询主键(ID)就显示用上索引了。

二、数据准备和场景重现
1、准备表和数据:
创建 user 表,给 phone 字段加了个普通索引:

CREATE TABLE user (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
age int(11) DEFAULT NULL,
phone varchar(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY index_phone (phone) USING BTREE COMMENT 'phone索引'
) ENGINE=InnoDB AUTO_INCREMENT=200007 DEFAULT CHARSET=utf8;
准备10万条数据意思意思:

delimiter ;
CREATE DEFINER=root@localhost PROCEDURE iniData()
begin
declare i int;
set i=1;
while(i<=100000)do

insert into user(name,age,phone) values('测试', i, 15627230000+i);
set i=i+1;

end while;
end;;
delimiter ;

call iniData();
2、执行 SQL ,查看执行计划:
explain select * from user where phone like '%156%';
explain select id from user where phone like '%156%';
3、执行结果:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user
ALL

99927 11.11 Using where
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user
index
index_phone 36
99927 11.11 Using where; Using index
我们可以发现,第二条 SQL 确实是显示用上了 index_phone 索引。

但是细心的同学可能会发现:possible_keys 竟然为空!有猫腻。。。

我这里先说一下 prossible_keys 和 key 的关系:

1、possible_keys 为可能使用的索引,而 key 是实际使用的索引;

2、正常是: key 的索引,必然会包含在 possible_keys 中。

还有猫腻一点就是:使用索引和不使用索引读取的行数(rows)竟然是一样的!

三、验证和阶段性猜想
上面讲到,possible_keys 和 key 的关系,那么我们利用正常的走索引来验证一下。

下面的 SQL, 不是全模糊查询,而是右模糊查询,保证是一定走索引的,我们分别看看此时 possible_keys 和 key 的值:

explain select id from user where phone like '156%';
执行结果:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user
range index_phone index_phone 36
49963 100 Using where; Using index
这里太明显了:

1、possible_keys 里确实包含了 key 里的索引。

2、 并且rows 瞬间降到 49963,整整降了一倍,并且 filtered 也达到了 100。

阶段猜想:
1、首先,select id from user where phone like '%156%'; 因为覆盖索引而用上了索引 index_phone。

2、possible_keys 为 null,证明用不上索引的树形查找。很明显,select id from user where phone like '%156%'; 即使显示走了索引,但是读取行数 rows 和 select * from user where phone like '%156%'; 没有走索引的 rows 是一样的。

3、那么,我们可以猜测到,select id from user where phone like '%156%'; 即使因为覆盖索引而用上了 index_phone 索引,但是却没用上树形查找,只是正常顺序遍历了索引树。所以说,其实这两条 SQL 在表字段不多的情况下,查询性能应该差不了多少。

四、通过 Trace 分析来验证
我们分别利用 Trace 分析对于这两个 SQL 优化器是如何选择的。
1、查询全字段:
-- 开启优化器跟踪
set session optimizer_trace='enabled=on';
select * from user where phone like '%156%';
-- 查看优化器追踪
select * from information_schema.optimizer_trace;
下面我们只看 TRACE 就行了:

{
"steps": [

{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`phone` AS `phone` from `user` where (`user`.`phone` like '%156%')"
      }
    ]
  }
},
{
  "join_optimization": {
    "select#": 1,
    "steps": [
      {
        "condition_processing": {
          "condition": "WHERE",
          "original_condition": "(`user`.`phone` like '%156%')",
          "steps": [
            {
              "transformation": "equality_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "constant_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "trivial_condition_removal",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "substitute_generated_columns": {
        }
      },
      {
        "table_dependencies": [
          {
            "table": "`user`",
            "row_may_be_null": false,
            "map_bit": 0,
            "depends_on_map_bits": [
            ]
          }
        ]
      },
      {
        "ref_optimizer_key_uses": [
        ]
      },
      {
        "rows_estimation": [
          {
            "table": "`user`",
            "table_scan": {
              "rows": 99927,
              "cost": 289
            }
          }
        ]
      },
      {
        "considered_execution_plans": [
          {
            "plan_prefix": [
            ],
            "table": "`user`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "rows_to_scan": 99927,
                  "access_type": "scan", // 顺序扫描
                  "resulting_rows": 99927,
                  "cost": 20274,
                  "chosen": true
                }
              ]
            },
            "condition_filtering_pct": 100,
            "rows_for_plan": 99927,
            "cost_for_plan": 20274,
            "chosen": true
          }
        ]
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "(`user`.`phone` like '%156%')",
          "attached_conditions_computation": [
          ],
          "attached_conditions_summary": [
            {
              "table": "`user`",
              "attached": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "refine_plan": [
          {
            "table": "`user`"
          }
        ]
      }
    ]
  }
},
{
  "join_execution": {
    "select#": 1,
    "steps": [
    ]
  }
}

]
}
2、只查询主键
set session optimizer_trace='enabled=on';
select id from user where phone like '%156%';
-- 查看优化器追踪
select * from information_schema.optimizer_trace;
下面我们继续只看 TRACE 就行了:

{
"steps": [

{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `user`.`id` AS `id` from `user` where (`user`.`phone` like '%156%')"
      }
    ]
  }
},
{
  "join_optimization": {
    "select#": 1,
    "steps": [
      {
        "condition_processing": {
          "condition": "WHERE",
          "original_condition": "(`user`.`phone` like '%156%')",
          "steps": [
            {
              "transformation": "equality_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "constant_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "trivial_condition_removal",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "substitute_generated_columns": {
        }
      },
      {
        "table_dependencies": [
          {
            "table": "`user`",
            "row_may_be_null": false,
            "map_bit": 0,
            "depends_on_map_bits": [
            ]
          }
        ]
      },
      {
        "ref_optimizer_key_uses": [
        ]
      },
      {
        "rows_estimation": [
          {
            "table": "`user`",
            "table_scan": {
              "rows": 99927,
              "cost": 289
            }
          }
        ]
      },
      {
        "considered_execution_plans": [
          {
            "plan_prefix": [
            ],
            "table": "`user`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "rows_to_scan": 99927,
                  "access_type": "scan", // 顺序扫描
                  "resulting_rows": 99927,
                  "cost": 20274,
                  "chosen": true
                }
              ]
            },
            "condition_filtering_pct": 100,
            "rows_for_plan": 99927,
            "cost_for_plan": 20274,
            "chosen": true
          }
        ]
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "(`user`.`phone` like '%156%')",
          "attached_conditions_computation": [
          ],
          "attached_conditions_summary": [
            {
              "table": "`user`",
              "attached": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "refine_plan": [
          {
            "table": "`user`"
          }
        ]
      }
    ]
  }
},
{
  "join_execution": {
    "select#": 1,
    "steps": [
    ]
  }
}

]
}
好了,到这里我们可以发现,在 Trace 分析里面,都没显示优化器为这两个 SQL 实际选择了什么索引,而只是显示了都是用了 顺序扫描 的方式去查找数据。

可能唯一不同点就是:一个使用了主键索引的全表扫描,而另外一个是使用了普通索引的全表扫描;但是两个都没用上树形查找,也就是没用上 B+Tree 的特性来提升查询性能。

六、最后总结
1、当全模糊查询的 SQL 只查询主键作为结果集时,因为覆盖索引,会用上查询字段对应的索引。

2、即使用上了索引,但是却没用上树形查找的特性,只是正常的顺序遍历。

3、而正常的全表扫描也是主键索引的顺序遍历,所以说,其实这两者的性能其实是差不多的。

原文地址https://www.cnblogs.com/Howinfun/p/12449975.html

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
16天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
29天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
166 1
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
2月前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
72 1
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
73 0
|
2月前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
47 0
|
20天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
22天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
36 4
|
2月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
66 3
Mysql(4)—数据库索引