首页 > 其他分享 >使用explain优化慢查询的业务场景分析

使用explain优化慢查询的业务场景分析

时间:2024-07-01 11:10:46浏览次数:22  
标签:场景 查询 EXPLAIN id student date NULL explain

  • 问:你最害怕的事情是什么?
  • 答:搓澡
  • 问:为什么?
  • 答:因为有些人一旦错过,就不在了

Explain 这个词在不同的上下文中有不同的含义。在数据库查询优化的上下文中,"EXPLAIN" 是一个常用的 SQL 命令,用于显示 SQL 查询的执行计划。执行计划是数据库如何执行查询的一个详细描述,包括它将使用哪些索引、表的连接顺序、表的扫描方式等信息。

在 SQL 中,使用 "EXPLAIN" 可以提供以下字段的信息:

  • id: 表示查询中的各个部分的标识符。
  • select_type: 查询类型,比如简单查询、联合查询、子查询等。
  • table: 涉及的表名。
  • partitions: 查询涉及的分区信息。
  • type: 连接类型,如全表扫描、索引扫描等。
  • possible_keys: 可能使用的索引列表。
  • key: 实际使用的索引。
  • key_len: 使用的索引长度。
  • ref: 索引列上使用的列或常量。
  • rows: 估计需要检查的行数。
  • filtered: 行过滤的百分比。
  • Extra: 额外信息,可能包含诸如"Using filesort"、"Using temporary"等信息。

下面,V 哥通过两个案例来详细说明一下如何使用 Explain来优化 SQL。

案例一:

场景设定

假设我们有一个电子商务网站的数据库,其中有一个名为 orders 的表,它记录了用户的订单信息。表结构大致如下:

    id: 订单的唯一标识符
    user_id: 下单用户的ID
    product_id: 购买的产品ID
    order_date: 下单日期
    quantity: 购买数量

问题

我们需要查询2024年1月1日之后所有用户的订单总数。

原始 SQL 查询

SELECT COUNT(*) FROM orders WHERE order_date > '2024-01-01';

步骤 1: 使用 EXPLAIN 分析查询

首先,我们使用 EXPLAIN 来查看当前查询的执行计划:

EXPLAIN SELECT COUNT(*) FROM orders WHERE order_date > '2024-01-01';

步骤 2: 分析 EXPLAIN 输出

假设 EXPLAIN 的输出显示如下:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL range order_date NULL NULL NULL 10000 10.00 Using where; Using index

步骤 3: 识别问题

从 EXPLAIN 输出中,我们可以看到:

  • type 是 range,这意味着数据库将使用索引进行范围扫描,而不是全表扫描。
  • rows 估计为 10000,这可能表示查询需要检查大量行。
  • Extra 显示 Using where; Using index,表示使用了索引。

步骤 4: 优化 SQL

尽管查询已经使用了索引,但我们可能希望进一步优化性能。考虑到我们只需要统计总数,而不是具体的订单数据,我们可以:

  • 使用索引覆盖扫描:如果 order_date 索引包含 id,则可以避免回表查询,直接在索引中完成统计。

优化后的 SQL 可能如下:

SELECT COUNT(*) FROM orders USE INDEX (order_date) WHERE order_date > '2023-01-01';

步骤 5: 再次使用 EXPLAIN

使用优化后的查询再次运行 EXPLAIN:

EXPLAIN SELECT COUNT(*) FROM orders USE INDEX (order_date) WHERE order_date > '2023-01-01';

步骤 6: 分析优化后的输出

假设优化后的 EXPLAIN 输出显示:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL index order_date order_date 4 NULL 10000 10.00 Using index; Backward index scan

步骤 7: 评估优化效果

  • type 现在是 index,表示使用了索引覆盖扫描。
  • Extra 显示 Using index; Backward index scan,表示查询仅使用了索引,没有回表。

通过这些步骤,我们对原始查询进行了分析和优化,提高了查询效率。在实际应用中,可能需要根据具体的数据库结构和数据分布进行更多的调整和优化。

案例二:

我们考虑一个更复杂的场景,涉及到多表查询和联结。

场景设定

假设我们有一个在线教育平台的数据库,其中有两个表:

1. students 表,存储学生信息:

  • student_id: 学生ID
  • name: 学生姓名
  • enrollment_date: 入学日期

2. courses 表,存储课程信息:

  • course_id: 课程ID
  • course_name: 课程名称

3. 还有一个 enrollments 表,存储学生的课程注册信息:

  • enrollment_id: 注册ID
  • student_id: 学生ID
  • course_id: 课程ID
  • enrollment_date: 注册日期

问题

我们需要查询所有在2024年注册了至少一门课程的学生的姓名和他们注册的课程数量。

原始 SQL 查询

SELECT s.name, COUNT(e.course_id) AS course_count
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.name;

步骤 1: 使用 EXPLAIN 分析查询

EXPLAIN SELECT s.name, COUNT(e.course_id) AS course_count
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.name;

步骤 2: 分析 EXPLAIN 输出

假设 EXPLAIN 的输出如下:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE s NULL ALL NULL NULL NULL NULL 1000 NULL NULL
1 SIMPLE e NULL ref student_id student_id 5 students.student_id 5000 NULL Using where

步骤 3: 识别问题

  • students 表使用了全表扫描(type 是 ALL),这意味着查询需要扫描整个 students 表。
  • enrollments 表使用了 ref 类型的联结,它使用了 student_id 索引。

步骤 4: 优化 SQL

我们可以通过以下方式优化查询:

  • 添加索引:如果 enrollments 表上的 enrollment_date 没有索引,考虑添加一个,以便快速过滤2023年的注册记录。
  • 过滤条件:在联结条件中添加过滤条件,减少需要联结的行数。

优化后的 SQL 可能如下:

SELECT s.name, COUNT(e.course_id) AS course_count
FROM students s
JOIN (
  SELECT course_id, student_id
  FROM enrollments
  WHERE enrollment_date >= '2023-01-01'
) e ON s.student_id = e.student_id
GROUP BY s.name;

步骤 5: 再次使用 EXPLAIN

使用优化后的查询再次运行 EXPLAIN。

步骤 6: 分析优化后的输出

假设优化后的 EXPLAIN 输出显示:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY s NULL ALL NULL NULL NULL NULL 1000 NULL NULL
2 DERIVED e NULL range enrollment_date NULL NULL NULL 500 10.00 Using where
1 SIMPLE <subquery2> NULL ref student_id student_id 5 s.student_id 500 NULL Using index

步骤 7: 评估优化效果

  • 子查询 e 现在使用 range 类型扫描,只获取2023年的注册记录,减少了行数。
  • 主查询现在使用 ref 类型联结,因为子查询结果已经通过索引 student_id 进行了优化。

通过这些步骤,我们对原始查询进行了分析和优化,减少了需要处理的数据量,提高了查询效率。在实际应用中,可能需要根据具体的数据库结构和数据分布进行更多的调整和优化。

最后

以上是 V 哥在整理的关于 EXPLAIN 在实际工作中的使用,并结合案例给大家作了分析,用熟 EXPLAIN 将大大改善你的 SQL 查询效率,你在工作中还用到哪些业务场景或案例,可以在评论区讨论,或者说出你遇到的问题,V 哥来帮你定位一下问题,关注威哥爱编程,每天精彩内容不错过。

标签:场景,查询,EXPLAIN,id,student,date,NULL,explain
From: https://www.cnblogs.com/wgjava/p/18277651

相关文章

  • ADI的DSP开发,如何在CCES里查询到你想要的资料和信息
    作者的话ADI的DSP开发,一个很有特色的点就是,他会把所有提供的资料都集中在他的开发软件Help里,你需要找什么资料,直接查,以CCES为例,我举例说明一下。ADIDSP资料全集的链接:https://item.taobao.com/item.htm?spm=a1z10.5-c.w4002-5192690539.20.263c73c8Q6RngW&id=56626235250......
  • MongoDB 查看慢查询级别及慢查询日志
    MongoDB慢查询记录功能简介如何定位MongoDB数据库的慢查询,我想应该是很多刚使用MongoDB数据库的朋友最想知道的问题。通过慢查询的定位,可以辅助对MongoDB中的collection进行优化。MongoDB数据库的慢查询数据其实存放在一个数据库集合(collection)中(system.profile......
  • MySQL 索引之外的相关查询优化总结
    在这之前先说明几个概念:1、驱动表和被驱动表:驱动表是主表,被驱动表是从表、非驱动表。驱动表和被驱动表并非根据from后面表名的先后顺序而确定,而是根据explain语句查询得到的顺序确定;展示在前面的是驱动表,后面的是非驱动表。2、关联查询的类型非为:内连接(innerjoin)、......
  • 数据库查询总结
     select语句SELECT语句用于从数据库中选取数据。结果被存储在一个结果表中,称为结果集。select语句语法格式SELECT[ALL|DISTINCT]输出表达式,...[FROM表名1[,表名2].....]/*FROM子句*/[WHERE条件]/*WHERE子......
  • 【SQL】⼀条 SQL 查询语句的执⾏顺序
    SQL查询语句的执行顺序与编写查询语句的顺序不同,理解SQL查询的执行顺序有助于更好地优化和调试SQL查询。下面是SQL查询语句的基本的执行顺序:FROM:确定数据源,包含表连接(JOIN)操作。ON:定义连接条件(对于JOIN子句)。WHERE:过滤数据,排除不符合条件的行。GROUPBY:对......
  • 详解 ClickHouse 的查询优化
    一、单表查询1.使用prewhere替代whereprewhere和where语句的作用相同,都是用来过滤数据prewhere和where语句的不同在于:prewhere只支持MergeTree族系列引擎的表prewhere首先会读取指定的列数据来判断数据过滤,等待数据过滤之后再读取select声明的列字段......
  • 纯真IP库查询方法(2024-6-19更新qqwry.dat后无法查询,修改代码)
    2024-6-19更新qqwry.dat后使用pthon38那篇文章里的代码无法查询,使用pythom2的代码,修改之后python3可用,将文件放到工程里查询,不用Lib库里的。修改后的qqwry.py如下,python3可用。coding=utf-8forPython2.7为https://pypi.python.org/pypi/qqwry-py3的Python2版版本:2017-10-......
  • 库分表后复杂查询的应对之道:基于DTS实时性ES宽表构建技术实践
    1问题域业务发展的初期,我们的数据库架构往往是单库单表,外加读写分离来快速的支撑业务,随着用户量和订单量的增加,数据库的计算和存储往往会成为我们系统的瓶颈,业界的实践多数采用分而治之的思想:分库分表,通过分库分表应对存系统读写性能瓶颈和存储瓶颈;分库分表帮我们解决问题的同时......
  • httpClient使用GetStringAsync、GetByteArrayAsync、GetStreamAsync三种方法的使用场
     `HttpClient`类中的`GetStringAsync`、`GetByteArrayAsync`和`GetStreamAsync`方法用于从远程服务器获取不同类型的数据。它们的主要使用场景如下:1.`GetStringAsync`方法:-使用场景:当您需要从远程服务器获取文本数据时,可以使用`GetStringAsync`方法。这通常用于......
  • Redis常见的16个使用场景
    1、缓存String类型例如:热点数据缓存(例如报表、明星出轨),对象缓存、全页缓存、可以提升热点数据的访问数据。2、数据共享分布式String类型,因为Redis是分布式的独立服务,可以在多个应用之间共享例如:分布式Session<dependency><groupId>org.springframework.session</gr......