SQL面试题:第二个优化案例

这篇具有很好参考价值的文章主要介绍了SQL面试题:第二个优化案例。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

今天继续给大家分享一个 SQL 优化案例。

问题描述

已知表结构如下:

CREATE TABLE `customer` (
`C_CUSTKEY` int NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` int NOT NULL,
`C_PHONE` char(15) NOT NULL,
`C_ACCTBAL` decimal(15,2) NOT NULL,
`C_MKTSEGMENT` char(10) NOT NULL,
`C_COMMENT` varchar(117) NOT NULL,
PRIMARY KEY (`C_CUSTKEY`)
) ENGINE=InnoDB;

CREATE TABLE `orders` (
`O_ORDERKEY` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) NOT NULL
PRIMARY KEY (`C_ORDERKEY`)
) ENGINE=InnoDB;

请问下面的查询语句应该如何优化?

SELECT o_custkey, c_name, sum(o.o_totalprice)
FROM customer c, orders o 
WHERE o_custkey = c_custkey 
GROUP BY o_custkey, c_name
ORDER BY c_name;

思路分析

首先,这个查询的作用是统计每个客户的订单金额汇总,数据来自 orders 表;最后按照客户名称排序,名称来自 customer 表,两个表通过 c_custkey 字段关联。看一下执行计划:

EXPLAIN
select c_custkey, c_name, sum(o.o_totalprice)
from customer c, orders o 
where o_custkey = c_custkey 
group by c_name, c_custkey
order by c_name;

id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref           |rows|filtered|Extra                          |
--+-----------+-----+----------+------+-------------+-------+-------+--------------+----+--------+-------------------------------+
 1|SIMPLE     |o    |          |ALL   |             |       |       |              |   1|   100.0|Using temporary; Using filesort|
 1|SIMPLE     |c    |          |eq_ref|PRIMARY      |PRIMARY|4      |hr.o.O_CUSTKEY|   1|   100.0|                               |

orders 表使用了 ALL 全表扫描,由于 group by,所以 Extra 包含了临时表(Using temporary);由于 order by,所以 Extra 包含了排序操作(Using filesort)。这些都会导致性能问题。

第一步,考虑到每个客户的订单金额汇总只需要 orders 表中的数据,可以单独按照 o_custkey 分组,并且基于 o_custkey 和 o_totalprice 创建复合索引,利用索引覆盖扫描优化;然后再和 customer 表关联。另外,c_custkey 是主键,决定了 c_name,延迟关联可以避免 group by 中的 c_name。

-- 创建索引
CREATE INDEX idx_orders_custkey_totalprice ON orders(o_custkey, o_totalprice);

EXPLAIN
select o.o_custkey, c.c_name, o.sumprice
from
(select o_custkey, sum(o_totalprice) sumprice
from orders 
group by o_custkey) o, customer c
where o_custkey = c_custkey 
order by c_name;

id|select_type|table     |partitions|type |possible_keys                |key                          |key_len|ref           |rows|filtered|Extra         |
--+-----------+----------+----------+-----+-----------------------------+-----------------------------+-------+--------------+----+--------+--------------+
 1|PRIMARY    |c         |          |ALL  |PRIMARY                      |                             |       |              |   1|   100.0|Using filesort|
 1|PRIMARY    |<derived2>|          |ref  |<auto_key0>                  |<auto_key0>                  |4      |hr.c.C_CUSTKEY|   2|   100.0|              |
 2|DERIVED    |orders    |          |index|idx_orders_custkey_totalprice|idx_orders_custkey_totalprice|11     |              |   1|   100.0|Using index   |

子查询 o 已经优化完成,Extra 中的 Using index 显示了索引覆盖扫描。剩下的问题就是 customer 表上的排序操作,对应 Extra 中的 Using filesort。

第二步,基于 c_name 字段创建索引,避免排序操作。

-- 创建索引
CREATE INDEX idx_customer_name ON customer(c_name);

EXPLAIN
select c.c_custkey, c.c_name, o.sumprice
from
(select o_custkey, sum(o_totalprice) sumprice
from orders 
group by o_custkey) o, customer c
where o_custkey = c_custkey 
order by c_name;

id|select_type|table     |partitions|type |possible_keys                |key                          |key_len|ref           |rows|filtered|Extra      |
--+-----------+----------+----------+-----+-----------------------------+-----------------------------+-------+--------------+----+--------+-----------+
 1|PRIMARY    |c         |          |index|PRIMARY                      |idx_customer_name            |102    |              |   1|   100.0|Using index|
 1|PRIMARY    |<derived2>|          |ref  |<auto_key0>                  |<auto_key0>                  |4      |hr.c.C_CUSTKEY|   2|   100.0|           |
 2|DERIVED    |orders    |          |index|idx_orders_custkey_totalprice|idx_orders_custkey_totalprice|11     |              |   1|   100.0|Using index|

可以看到,对于 customer 表的访问,也使用了索引覆盖扫描,因为二级索引包含了主键字段(c_custkey)。

额外补充,还有一种写法就是调整 group by 字段的顺序,把 c_name 放在第一位,并且把 c_custkey 改成 o_custkey:

EXPLAIN 
select o_custkey, c_name, sum(o.o_totalprice)
from customer c, orders o 
where o_custkey = c_custkey 
group by c_name, o_custkey
order by c_name;

id|select_type|table|partitions|type |possible_keys                |key                          |key_len|ref           |rows|filtered|Extra      |
--+-----------+-----+----------+-----+-----------------------------+-----------------------------+-------+--------------+----+--------+-----------+
 1|SIMPLE     |c    |          |index|PRIMARY,idx_customer_name    |idx_customer_name            |102    |              |   1|   100.0|Using index|
 1|SIMPLE     |o    |          |ref  |idx_orders_custkey_totalprice|idx_orders_custkey_totalprice|4      |hr.c.C_CUSTKEY|   1|   100.0|Using index|

留个作业,为什么呢?文章来源地址https://www.toymoban.com/news/detail-634249.html

到了这里,关于SQL面试题:第二个优化案例的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • smartsofthelp 5.0 最专业的数据库优化工具,数据库配置优化,数据库高并发优化,SQL 语句优化...

      下载地址:百度网盘 请输入提取码 SQL操作返回历史记录: 2023-08-21 20:42:08:220  输入:select @@version as 版本号 2023-08-21 20:42:08:223  输出:当前数据库实例版本号:Microsoft SQL Server 2012 - 11.0.2100.60 (X64)      Feb 10 2012 19:39:15      Copyright (c) Microsoft Corporation     Developer Edition (

    2024年02月12日
    浏览(59)
  • MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)

    本篇博客深入详细地介绍了数据库索引的概念和重要性。内容包含:索引的概念和目标、索引的优点与缺点。此外,博客还深入解析了三种主要的索引结构:B-Tree、B+Tree和Hash,提供了详细的结构解析和优化方法,并通过插图进一步增强了理解。 博客的部分内容专注于对B-Tr

    2024年02月21日
    浏览(64)
  • 关于数据库SQL优化

      在项目上线初期,业务数据量相对较少,SQL的执行效率对程序运行效率的影响可能不太明显,因此开发和运维人员可能无法判断SQL对程序的运行效率有多大。但随着时间的积累,业务数据量的增多,SQL的执行效率对程序的运行效率的影响逐渐增大,此时对SQL的优化就很有必

    2024年02月08日
    浏览(61)
  • SQL笔记 -- 数据库结构优化

    不常用的数据为冷数据,反之则为热数据。如果一个表中的数据存在明显的使用频率差异,那么可以将冷热数据分离。通过这种分解可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表,可以通过这种分解的方式来优化数据库的性能。 例如: 会员members表存储会

    2024年01月22日
    浏览(59)
  • 数据库优化案例—某市中心医院HIS系统

    记得在自己学习数据库知识的时候特别喜欢看案例,因为优化的手段是容易掌握的,但是整体的优化思想是很难学会的。这也是为什么自己特别喜欢看案例,今天也开始分享自己做的优化案例。 最近一直很忙,博客产出也少的可怜,今天整理了一下自己做过优化或各种方案的

    2024年02月05日
    浏览(48)
  • 【MySQL 数据库】7、SQL 优化

    ① 批量插入数据 ② 手动控制事务 ③ 主键顺序插入,性能要高于乱序插入 主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89 【☆】 】 ① 如果需要一次性插入大批量数据(百万级别),使用 insert 语句插入性能 很低 ② 可使用 MySQL 数据库提供的 loa

    2024年02月08日
    浏览(60)
  • SQL Server 数据库优化分享

           随着数据量和业务复杂性的增加,数据库优化变得越来越重要。通过对 SQL Server 数据库进行优化,您可以提高查询性能、减少资源消耗,从而改善整体系统性能。以下是一些优化技巧,可帮助您实现更高效、更可靠的数据库操作。 使用恰当的索引: 索引是一种提供快

    2024年02月11日
    浏览(53)
  • 数据库优化:探索 SQL 中的索引

    推荐:使用 NSDT场景编辑器 助你快速搭建可编辑的3D应用场景 在一本书中搜索特定主题时,我们将首先访问索引页面(该页面位于该书的开头),并找到包含我们感兴趣的主题的页码。现在,想象一下在没有索引页的书中找到特定主题是多么不方便。为此,我们必须搜索书中

    2024年02月14日
    浏览(70)
  • 【数据库】sql优化有哪些?从query层面和数据库层面分析

    这类型问题可以称为:Query Optimization,从清华AI4DB的paper list中,该类问题大致可以分为: Query Rewriter Cardinality Estimation Cost Estimation Plan Optimization 从中文的角度理解那就是: 查询重写 基数估计 成本估计 执行计划优化 可以发现,这类型的优化问题,大多数从sql本身,或者说从

    2024年01月17日
    浏览(54)
  • 实时Flink的数据库与Kafka集成优化案例

    在现代数据处理系统中,实时数据处理和分析是至关重要的。Apache Flink是一个流处理框架,可以用于实时数据处理和分析。在许多场景下,Flink需要与数据库和Kafka等消息系统进行集成,以实现更高效的数据处理。本文将讨论Flink与数据库和Kafka集成的优化案例,并提供实际示

    2024年02月20日
    浏览(43)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包