当前位置: 首页 > news >正文

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;-- tpch.orders definition
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 (`O_ORDERKEY`)
) ENGINE=InnoDB;

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

select * 
from customer 
where c_custkey = (select max(o_custkey) from orders where subdate(o_orderdate, interval '1' DAY) < '2022-12-20');

思路分析

首先,customer 表的 c_custkey 字段是主键,查询条件是等值查找,已经最优了。

其次,子查询的条件字段没有索引,需要全表扫描。看一下执行计划:

EXPLAIN
select * from customer 
where c_custkey = (select max(o_custkey) 
from orders where subdate(o_orderdate, interval '1' DAY) < '2022-12-20');id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra                         |
--+-----------+------+----------+----+-------------+---+-------+---+----+--------+------------------------------+1|PRIMARY    |      |          |    |             |   |       |   |    |        |no matching row in const table|2|SUBQUERY   |orders|          |ALL |             |   |       |   |   1|   100.0|Using where                   |

因此主要优化这个子查询。

第一步,考虑子查询使用 o_orderdate 作为条件,可以基于这个字段创建索引,同时需要把 subdate 函数放到表达式右边,否则会导致索引失效。

CREATE INDEX idx_orders_date ON orders(o_orderdate);EXPLAIN
select * from customer 
where c_custkey = (select max(o_custkey) 
from orders where o_orderdate < adddate('2022-12-20', interval '1' DAY));id|select_type|table |partitions|type |possible_keys  |key            |key_len|ref|rows|filtered|Extra                         |
--+-----------+------+----------+-----+---------------+---------------+-------+---+----+--------+------------------------------+1|PRIMARY    |      |          |     |               |               |       |   |    |        |no matching row in const table|2|SUBQUERY   |orders|          |range|idx_orders_date|idx_orders_date|3      |   |   1|   100.0|Using index condition         |

对于 orders 表的访问类型变成了索引范围扫描(range),但是仍然不够理想,因为扫描完索引之后还需要回表查询 max(o_custkey)。

第二步,考虑利用覆盖索引优化,避免回表。可以基于 o_orderdate 和 o_custkey 创建一个复合索引。

CREATE INDEX idx_orders_date_cust ON orders(o_orderdate, o_custkey);EXPLAIN
select * from customer 
where c_custkey = (select max(o_custkey) 
from orders where o_orderdate < adddate('2022-12-20', interval '1' DAY));id|select_type|table |partitions|type |possible_keys                       |key                 |key_len|ref|rows|filtered|Extra                         |
--+-----------+------+----------+-----+------------------------------------+--------------------+-------+---+----+--------+------------------------------+1|PRIMARY    |      |          |     |                                    |                    |       |   |    |        |no matching row in const table|2|SUBQUERY   |orders|          |index|idx_orders_date,idx_orders_date_cust|idx_orders_date_cust|7      |   |   1|   100.0|Using where; Using index      |

第三步,考虑复合索引的字段顺序是否有更好的选择,也就是说 (o_orderdate, o_custkey) 还是 (o_custkey, o_orderdate)。

如果选择 (o_orderdate, o_custkey),通过覆盖索引获取数据时,需要找出 o_orderdate 小于 2022-12-21 的所有索引节点,然后遍历其中的 o_custkey,找出最大的值。

如果选择 (o_custkey, o_orderdate),通过覆盖索引获取数据时,需要按照 o_custkey 从大到小查找索引,找出其中 o_orderdate 小于 2022-12-21 的第一个索引节点即可。

MySQL 优化器目前不支持第二种选择。

相关文章:

SQL面试题:一个优化案例

问题描述 假如存在以下两个表&#xff1a; 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(…...

链表的总体涵盖以及无哨兵位单链表实现——【数据结构】

&#x1f60a;W…Y&#xff1a;个人主页 在学习之前看一下美丽的夕阳&#xff0c;也是很不错的。 如果觉得博主的美景不错&#xff0c;博客也不错的话&#xff0c;关注一下博主吧&#x1f495; 在上一期中&#xff0c;我们说完了顺序表&#xff0c;并且提出顺序表中的问题 1. 中…...

网页版Java五子棋项目(一)websocket【服务器给用户端发信息】

网页版Java五子棋项目&#xff08;一&#xff09;websocket【服务器给用户端发信息】 一、为什么要用websocket二、websocket介绍原理解析 三、代码演示1. 创建后端api&#xff08;TestAPI&#xff09;新增知识点&#xff1a;extends TextWebSocketHandler重写各种方法 2. 建立…...

企业大数据可视化案例专题分享-入门

一、什么是数据可视化&#xff1f; 基本概念&#xff1a;数据可视化是以图示或图形格式表示的数据。让决策者可以看到以直观方式呈现的分析&#xff0c;以便他们可以掌握困难的概念或识别新的模式。借助交互式可视化&#xff0c;可以使用技术深入挖掘图表和图形以获取更多详细…...

GoogLeNet卷积神经网络-笔记

GoogLeNet卷积神经网络-笔记 GoogLeNet是2014年ImageNet比赛的冠军&#xff0c; 它的主要特点是网络不仅有深度&#xff0c; 还在横向上具有“宽度”。 由于图像信息在空间尺寸上的巨大差异&#xff0c; 如何选择合适的卷积核来提取特征就显得比较困难了。 空间分布范围更广的…...

腾讯云TencentOS Server镜像系统常见问题解答

腾讯云TencentOS Server镜像是腾讯云推出的Linux操作系统&#xff0c;完全兼容CentOS生态和操作方式&#xff0c;TencentOS Server操作系统为云上运行的应用程序提供稳定、安全和高性能的执行环境&#xff0c;TencentOS可以运行在腾讯云CVM全规格实例上&#xff0c;包括黑石物理…...

【项目 进程13】2.28共享内存(1) 2.29共享内存(2)

文章目录 2.28共享内存&#xff08;1&#xff09;共享内存&#xff08;效率最高&#xff0c;比内存映射更高。因为内存映射还需一个文件做载体&#xff09;共享内存使用步骤共享内存操作函数头文件 2.29共享内存&#xff08;2&#xff09;共享内存相关问题共享内存和内存映射的…...

Flask框架-流量控制:flask-limiter的使用

一、flask使用flask-limiter存在版本问题 Flask1.1.4 Flask-Bootstrap3.3.7.1 Flask-Caching1.9.0 Flask-Cors3.0.10 Flask-Limiter1.4 Flask-Migrate2.5.3 Flask-RESTful0.3.8 Flask-Script2.0.6 Flask-SocketIO5.0.1 Flask-Sockets0.2.1 Flask-SQLAlchemy2.4.4 Jinjia22.11.…...

【机器学习】西瓜书习题3.5Python编程实现线性判别分析,并给出西瓜数据集 3.0α上的结果

参考代码 结合自己的理解&#xff0c;添加注释。 代码 导入相关的库 import numpy as np import pandas as pd import matplotlib from matplotlib import pyplot as plt导入数据&#xff0c;进行数据处理和特征工程 得到数据集 D { ( x i , y i ) } i 1 m , y i ∈ { 0 ,…...

Elasticsearch:通过动态修剪实现更快的基数聚合

作者&#xff1a;Adrien Grand Elasticsearch 8.9 通过支持动态修剪&#xff08;dynamic pruning&#xff09;引入了基数聚合加速。 这种优化需要满足特定的条件才能生效&#xff0c;但一旦实现&#xff0c;通常会产生惊人的结果。 我们观察到&#xff0c;通过此更改&#xff0…...

Webpack5 生产模式压缩图片ImageMinimizerPlugin

文章目录 一、 ImageMinimizerPlugin是什么&#xff1f;二、已经有了asset&#xff0c;为什么需要ImageMinimizerPlugin&#xff1f;三、怎么使用ImageMinimizerPlugin&#xff1f;四、ImageMinimizerPlugin压缩的成果 一、 ImageMinimizerPlugin是什么&#xff1f; 它的实际依…...

时序预测 | Matlab实现基于BP神经网络的电力负荷预测模型

文章目录 效果一览文章概述源码设计参考资料效果一览 文章概述 时序预测 | Matlab实现基于BP神经网络的电力负荷预测模型 BP神经网络是一种多层的前馈神经网络,其主要的特点是:信号是前向传播的,而误差是反向传播的。B...

基于回溯算法实现八皇后问题

八皇后问题是一个经典的计算机科学问题&#xff0c;它的目标是将8个皇后放置在一个大小为88的棋盘上&#xff0c;使得每个皇后都不会攻击到其他的皇后。皇后可以攻击同一行、同一列和同一对角线上的棋子。 一、八皇后问题介绍 八皇后问题最早由国际西洋棋大师马克斯贝瑟尔在18…...

Linux【网络编程】之深入理解TCP协议

Linux【网络编程】之深入理解TCP协议 TCP协议TCP协议段格式4位首部长度---TCP报头长度信息 TCP可靠性&#xff08;确认应答&#xff09;&& 提高传输效率确认应答(ACK)机制32位序号与32为确认序号 16位窗口大小---自己接收缓冲区剩余空间的大小16位紧急指针---紧急数据处…...

如何克服看到别人优于自己而感到的焦虑和迷茫?

文章目录 每日一句正能量前言简述自己的感受怎么做如何调整自己的心态后记 每日一句正能量 行动是至于恐惧的良药&#xff0c;而犹豫、拖延&#xff0c;将不断滋养恐惧。 前言 虽然清楚知识需要靠时间沉淀&#xff0c;但在看到自己做不出来的题别人会做&#xff0c;自己写不出的…...

浅谈React中的ref和useRef

目录 什么是useRef&#xff1f; 使用 ref 访问 DOM 元素 Ref和useRef之间的区别 Ref和useRef的使用案例 善用工具 结论 在各种 JavaScript 库和框架中&#xff0c;React 因其开发人员友好性和支持性而得到认可。 大多数开发人员发现 React 非常舒适且可扩展&#xff0c;…...

Linux C 获取主机网卡名及 IP 的几种方法

在进行 Linux 网络编程时&#xff0c;经常会需要获取本机 IP 地址&#xff0c;除了常规的读取配置文件外&#xff0c;本文罗列几种个人所知的编程常用方法&#xff0c;仅供参考&#xff0c;如有错误请指出。 方法一&#xff1a;使用 ioctl() 获取本地 IP 地址 Linux 下可以使用…...

解密外接显卡:笔记本能否接外置显卡?如何连接外接显卡?

伴随着电脑游戏和图形处理的需求不断增加&#xff0c;很多笔记本电脑使用者开始考虑是否能够通过外接显卡来提升性能。然而&#xff0c;外接显卡对于笔记本电脑是否可行&#xff0c;以及如何连接外接显卡&#xff0c;对于很多人来说仍然是一个迷。本文将为您揭秘外接显卡的奥秘…...

list与erase()

运行代码&#xff1a; //list与erase() #include"std_lib_facilities.h" //声明Item类 struct Item {string name;int iid;double value;Item():name(" "),iid(0),value(0.0){}Item(string ss,int ii,double vv):name(ss),iid(ii),value(vv){}friend istr…...

Arcgis 分区统计majority参数统计问题

利用Arcgis 进行分区统计时&#xff0c;需要统计不同矢量区域中栅格数据的众数&#xff08;majority&#xff09;&#xff0c;出现无法统计majority参数问题解决 解决&#xff1a;利用copy raster工具&#xff0c;将原始栅格数据 64bit转为16bit...

Linux应用开发之网络套接字编程(实例篇)

服务端与客户端单连接 服务端代码 #include <sys/socket.h> #include <sys/types.h> #include <netinet/in.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <arpa/inet.h> #include <pthread.h> …...

HTML 语义化

目录 HTML 语义化HTML5 新特性HTML 语义化的好处语义化标签的使用场景最佳实践 HTML 语义化 HTML5 新特性 标准答案&#xff1a; 语义化标签&#xff1a; <header>&#xff1a;页头<nav>&#xff1a;导航<main>&#xff1a;主要内容<article>&#x…...

树莓派超全系列教程文档--(62)使用rpicam-app通过网络流式传输视频

使用rpicam-app通过网络流式传输视频 使用 rpicam-app 通过网络流式传输视频UDPTCPRTSPlibavGStreamerRTPlibcamerasrc GStreamer 元素 文章来源&#xff1a; http://raspberry.dns8844.cn/documentation 原文网址 使用 rpicam-app 通过网络流式传输视频 本节介绍来自 rpica…...

【网络安全产品大调研系列】2. 体验漏洞扫描

前言 2023 年漏洞扫描服务市场规模预计为 3.06&#xff08;十亿美元&#xff09;。漏洞扫描服务市场行业预计将从 2024 年的 3.48&#xff08;十亿美元&#xff09;增长到 2032 年的 9.54&#xff08;十亿美元&#xff09;。预测期内漏洞扫描服务市场 CAGR&#xff08;增长率&…...

C++ 基础特性深度解析

目录 引言 一、命名空间&#xff08;namespace&#xff09; C 中的命名空间​ 与 C 语言的对比​ 二、缺省参数​ C 中的缺省参数​ 与 C 语言的对比​ 三、引用&#xff08;reference&#xff09;​ C 中的引用​ 与 C 语言的对比​ 四、inline&#xff08;内联函数…...

【AI学习】三、AI算法中的向量

在人工智能&#xff08;AI&#xff09;算法中&#xff0c;向量&#xff08;Vector&#xff09;是一种将现实世界中的数据&#xff08;如图像、文本、音频等&#xff09;转化为计算机可处理的数值型特征表示的工具。它是连接人类认知&#xff08;如语义、视觉特征&#xff09;与…...

汇编常见指令

汇编常见指令 一、数据传送指令 指令功能示例说明MOV数据传送MOV EAX, 10将立即数 10 送入 EAXMOV [EBX], EAX将 EAX 值存入 EBX 指向的内存LEA加载有效地址LEA EAX, [EBX4]将 EBX4 的地址存入 EAX&#xff08;不访问内存&#xff09;XCHG交换数据XCHG EAX, EBX交换 EAX 和 EB…...

vue3+vite项目中使用.env文件环境变量方法

vue3vite项目中使用.env文件环境变量方法 .env文件作用命名规则常用的配置项示例使用方法注意事项在vite.config.js文件中读取环境变量方法 .env文件作用 .env 文件用于定义环境变量&#xff0c;这些变量可以在项目中通过 import.meta.env 进行访问。Vite 会自动加载这些环境变…...

C# 求圆面积的程序(Program to find area of a circle)

给定半径r&#xff0c;求圆的面积。圆的面积应精确到小数点后5位。 例子&#xff1a; 输入&#xff1a;r 5 输出&#xff1a;78.53982 解释&#xff1a;由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982&#xff0c;因为我们只保留小数点后 5 位数字。 输…...

JAVA后端开发——多租户

数据隔离是多租户系统中的核心概念&#xff0c;确保一个租户&#xff08;在这个系统中可能是一个公司或一个独立的客户&#xff09;的数据对其他租户是不可见的。在 RuoYi 框架&#xff08;您当前项目所使用的基础框架&#xff09;中&#xff0c;这通常是通过在数据表中增加一个…...