mysql - 在选择子句中嵌套选择需要永远完成
问题描述
Table markclr
=============
id int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
name varchar(255) NOT NULL,
color varchar(255) NOT NULL,
Table dvcgrp
=============
id int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
name varchar(255) NOT NULL,
markclrid int(10) UNSIGNED NOT NULL,
Table dvc
=============
id int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
name varchar(255) NOT NULL,
shrtname varchar(255) NOT NULL,
dvceui varchar(255) NOT NULL,
dvcgrpid int(10) UNSIGNED NOT NULL,
Table vhsrdt
=============
id int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
dvcid int(10) UNSIGNED NOT NULL,
dtrcvtm timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dtraw varchar(255) NOT NULL,
dtdvcnm varchar(255) NOT NULL,
dtgtwid varchar(255) NOT NULL,
dtport int NOT NULL,
dtdr int NOT NULL,
dtadr varchar(255) NOT NULL,
dtriptype varchar(255),
dlatitudedeg decimal(10,8),
dlongitudedeg decimal(11,8),
dvextgood int(10) UNSIGNED NOT NULL,
dgpscurrent int(10) UNSIGNED NOT NULL,
dignition int(10) UNSIGNED NOT NULL,
ddigin1 int(10) UNSIGNED NOT NULL,
ddigin2 int(10) UNSIGNED NOT NULL,
ddigout int(10) UNSIGNED NOT NULL,
dheadingdeg varchar(255),
dspeedkmph varchar(255),
dbatv varchar(255),
请帮我解释一下我的查询会发生什么:
select
dvc1.id,
dvc1.name as dvcname,
dvc1.dvceui as dvcdvceui,
dvc1.shrtname as dvcshrtname,
markclr.color as markclrcolor,
(
select
tbl1.dtrcvtm
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dtrcvtm,
(
select
tbl1.dtgtwid
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dtgtwid,
(
select
tbl1.dtriptype
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dtriptype,
(
select
tbl1.dlatitudedeg
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dlatitudedeg,
(
select
tbl1.dlongitudedeg
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dlongitudedeg,
(
select
tbl1.dspeedkmph
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dspeedkmph,
(
select
tbl1.dheadingdeg
from vhsrdt as tbl1
where
tbl1.dvcid = dvc1.id and
tbl1.dtrcvtm = (
select
max(tbl2.dtrcvtm) as dtrcvtm
from vhsrdt as tbl2
where tbl2.dvcid = tbl1.dvcid
)
) as dheadingdeg
from dvc as dvc1
left join dvcgrp on dvcgrp.id = dvc1.dvcgrpid
left join markclr on markclr.id = dvcgrp.markclrid
表 vhsrdt 填充了大约 90K 数据。
如果我模拟数据库并使用 XAMPP 在我的开发环境中运行该查询,它会在不到 1 秒的时间内成功获取。
但是,如果我使用带有 MySQL 5.7 的 Linux 服务器在生产环境中运行该查询,它永远不会完成。
我也尝试过删除数据库并再次构建所有结构和数据,但仍然无法完成查询。
谢谢您的帮助。
结果EXPLAIN
:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | PRIMARY | dvc1 | | ALL | | | | | 50 | 100 |
1 | PRIMARY | dvcgrp | | eq_ref | PRIMARY | PRIMARY | 4 | klksensor.dvc1.dvcgrpid | 1 | 100 |
1 | PRIMARY | markclr | | ALL | PRIMARY | | | | 5 | 100 | Using where; Using join buffer (Block Nested Loop)
14 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where
15 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
12 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where
13 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
10 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where
11 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
8 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where
9 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
6 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where
7 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
4 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where
5 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
2 | DEPENDENT SUBQUERY | tbl1 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.dvc1.id | 1627 | 100 | Using where; Using index
3 | DEPENDENT SUBQUERY | tbl2 | | ref | vhsrdt_dvcid_index,vhsrdt_dvcid_dtrcvtm_dtport_index,vhsrdt_dvcid_dtgtwid_index,vhsrdt_dvcid_dtriptype_index,vhsrdt_dvcid_dlatitudedeg_index,vhsrdt_dvcid_dlongitudedeg_index,vhsrdt_dvcid_dlatitudedeg_dlongitudedeg_index | vhsrdt_dvcid_dtrcvtm_dtport_index | 4 | klksensor.tbl1.dvcid | 1627 | 100 | Using index
解决方案
从您的EXPLAIN
陈述中,您可以看到许多Dependent Subquery正在运行;尽管在您的情况下,它们都是相似的,只是在SELECT
差异字段上有所不同。不幸的是,在子句中使用子查询时,MySQL 不允许返回元组SELECT
。
您可以通过在子句中使用计算MAX()
单个“运行”中的所有值,将所有这些子查询减少为单个子查询。FROM
请尝试以下查询:
select
dvc1.id,
dvc1.name as dvcname,
dvc1.dvceui as dvcdvceui,
dvc1.shrtname as dvcshrtname,
markclr.color as markclrcolor,
vhsrdt.dtrcvtm,
vhsrdt.dtgtwid,
vhsrdt.dtriptype,
vhsrdt.dlatitudedeg,
vhsrdt.dlongitudedeg,
vhsrdt.dspeedkmph,
vhsrdt.dheadingdeg
from dvc as dvc1
left join dvcgrp on dvcgrp.id = dvc1.dvcgrpid
left join markclr on markclr.id = dvcgrp.markclrid
left join vhsrdt on vhsrdt.dvcid = dvc1.id
left join (select max(dtrcvtm) as max_dtrcvtm, dvcid
from vhsrdt
group by dvcid
) as dt on dt.dvcid = vhsrdt.dvcid
and dt.max_dtrcvtm = vhsrdt.dtrcvtm
无论我们是否需要,上述方法对所有 的计算max()
值都有限制。dvcid
此外,它将导致临时表的创建(如果很小,则在内存中,否则在磁盘上)。值得庆幸的是,MySQL 8.0.14 引入了LATERAL
Derived Table;因此,如果您可以升级到最新的 MySQL 版本,则性能更高的查询将是:
select
dvc1.id,
dvc1.name as dvcname,
dvc1.dvceui as dvcdvceui,
dvc1.shrtname as dvcshrtname,
markclr.color as markclrcolor,
v1.dtrcvtm,
v1.dtgtwid,
v1.dtriptype,
v1.dlatitudedeg,
v1.dlongitudedeg,
v1.dspeedkmph,
v1.dheadingdeg
from dvc as dvc1
left join dvcgrp on dvcgrp.id = dvc1.dvcgrpid
left join markclr on markclr.id = dvcgrp.markclrid
left join vhsrdt v1 on v1.dvcid = dvc1.id
left join lateral (select max(v2.dtrcvtm) as max_dtrcvtm
from vhsrdt v2
where v2.dvcid = v1.dvcid
) as dt on dt.max_dtrcvtm = v1.dtrcvtm
推荐阅读
- javascript - Socrata、SODA、REST、JS:如何查询行数?
- vba - 重置/重用电子表格的目标范围
- javascript - 从数据库中删除记录后重新加载页面(表)的问题
- tcl - 在 Tcl 脚本中转换列表中的列
- mbtiles - 为什么 Tippecanoe 将 geojson 文件转换为 pbf 矢量切片会导致纬度/经度放置不正确?
- bash - Centos 7 - 脚本未从 init d 运行 - 手动使用&它在后台启动
- python - 使用次轴线图制作分类或分组条形图
- r - R ggplot2:如何跨子组箱线图绘制 2 组的平均线?
- r - 如何修改函数以提取 R 中的某些回归系数
- azure-devops - Azure DevOps 扩展的条件上下文菜单?