mysql的表很少，在一个大表上的子查询执行缓慢

Question

我们在mysql数据库上的查询性能很慢，我们不确定查询是错误的，还是mysql或服务器不够好。

带有子查询的查询返回一些项目详细信息(3个字段(和最近拍摄的在线相机照片的文件名。

信息表"projects"包含40条记录。表"摄像头"包含大约40条记录(1个项目，可能有多个摄像头("cameraimages"表包含大约250000(25万(条记录。(一台相机可以有数千张图像(引擎是InnoDb数据库大小约为100Mb尚未添加索引。

版本号mysql 8.0.15

这是查询

SELECT
pj.title,
pj.description,
pj.city,
(SELECT cmi.filename 
FROM cameras cm
LEFT JOIN cameraimages cmi ON cmi.cameraId = cm.id
WHERE cm.projectId = pj.id
ORDER BY cmi.dateRecording DESC 
LIMIT 0,1) as latestfilename
FROM
projects pj

返回此数据需要40-50秒。这是一个很长的网页，但我认为它应该不会花那么长时间。为了进行比较，我们在另一台服务器上测试了相同的查询。相同的数据，相同的查询。这需要25秒。

我的问题是：

1. 这个查询是否为'heavy/bad'？如果是，哪个查询应该执行得更好
2. 有没有办法，或者我应该检查什么，来找出为什么这个查询在旧服务器/其他服务器上运行得更好

希望有人能给你一些建议。Thnx！

附加信息

CREATE TABLE `cameras` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`guid` varchar(50) DEFAULT NULL,
`title` varchar(50) DEFAULT NULL,
`longitude` double DEFAULT NULL,
`latitude` double DEFAULT NULL,
`status` smallint(6) DEFAULT NULL,
`cameraUid` varchar(20) DEFAULT NULL,
`cameraFriendlyName` varchar(50) DEFAULT NULL,
`projectId` int(11) DEFAULT NULL,
`dateCreated` datetime DEFAULT NULL,
`dateModified` datetime DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`createArchive` smallint(6) DEFAULT '0',
`createDaily` smallint(6) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=latin1

列cameraId，dateRecording是唯一的。一台相机一次拍摄一张照片。

Answer 1

您正在使用一个所谓的依赖子查询。太慢了。

我想cameraimages.id是你的cameraimages文件的主要密钥。这只是猜测。你在问题中没有提供足够的信息来确定地回答。

我还猜测cameraimages中的dateRecording值与您的自动递增主键id值的顺序相同。也就是说，我猜您在捕获每个图像时将一条记录插入到该表中。

让我们把它分解一下。

您需要每个项目中最新图像的id。你怎么能得到它？编写一个子查询以检索每个项目的最大、最新的id。

SELECT cm.projectId,
MAX(cmi.id) imageId
FROM cameras cm
JOIN cameraimages cmi ON cmi.cameraId = cm.id
GROUP BY cm.projectId

这个子查询完成了搜索大表的繁重工作。它只做一次，而不是每个项目，所以不会花那么长时间。

然后将该子查询放入查询中，以检索所需的列。

SELECT 
pj.title,
pj.description,
pj.city,
cmi.filename latestfilename
FROM projects pj
JOIN (
SELECT cm.projectId,
MAX(cmi.id) imageId
FROM cameras cm
JOIN cameraimages cmi ON cmi.cameraId = cm.id
GROUP BY cm.projectId
) latest ON pj.id = latest.projectId
JOIN cameraimages cmi ON cmi.imageId = latest.imageId

这有一系列JOIN，形成了从projects到latest子查询以及从那里到cameraimages的链。

这取决于按时间顺序排列的cameraimages.id值。如果他们没有按照这个顺序进行更复杂的查询，仍然可以这样做。

Answer 2

索引：

cm:   INDEX(projectId, id)
cmi:  INDEX(cameraId, dateRecording, filename)
cmi:  INDEX(cameraId, id)

Answer 3

当cameraimages.id值不按时间顺序排列时，我们需要使用最新的dateRecording值。

这将需要一系列的子查询。因此，与其嵌套它们，不如使用MySQL 8+公共表表达式。这是一个很大的疑问。

WITH 
ProjectCameraImage AS (
/* a virtual version of the cameraimages table including projectId */
SELECT cmi.id, cmi.dateRecording, cm.projectId, cm.cameraId 
FROM cameras cm
JOIN cameraimages cmi ON cm.id = cmi.cameraId
),

LatestDate AS (
/* the latest date for each entry in ProjectCameraImage */
/* Notice how this uses MAX rather than ORDER BY ... DESC LIMIT 1 */
SELECT projectId, cameraId, 
MAX(dateRecording) dateRecording
FROM ProjectCameraImage
GROUP BY projectId, cameraId
),

ProjectCameraLatest AS (
/* the cameraimage.id values for the latest images in ProjectCameraImage */
SELECT ProjectCameraImage.id, 
ProjectCameraImage.projectId, 
ProjectCameraImage.cameraId,
ProjectCameraImage.dateRecording
FROM ProjectCameraImage 
JOIN LatestDate
ON ProjectCameraImage.projectId = LatestDate.projectId
AND ProjectCameraImage.cameraId = LatestDate.cameraId
AND ProjectCameraImage.dateRecording = LatestDate.dateRecording
),

LatestProjectDate AS (
/* the latest data for each entry in ProjectCameraLatest */
SELECT projectId, 
MAX(dateRecording) dateRecording
FROM ProjectCameraLatest
GROUP BY projectId
),

ProjectLatest AS (
/* the cameraimage.id values for the latest images in ProjectCameraLatest */
SELECT ProjectCameraLatest.id,
ProjectCameraLatest.projectId
FROM ProjectCameraLatest
JOIN LatestProjectDate 
ON ProjectCameraLatest.projectId = LatestProjectDate.projectId
AND ProjectCameraLatest.dateRecording = LatestProjectDate.dateRecording
)

/* the main query */
SELECT pj.title,
pj.description,
pj.city,
cmi.filename latestfilename
FROM projects pj
JOIN ProjectLatest ON pj.id = ProjectLatest.projectId
JOIN cameraimages cmi ON ProjectLatest.id = cmi.id;

它很大，因为我们必须经历两个不同的周期来找到具有最大dateRecording的cameraimages.id值。

编辑就搜索表而言，繁重的工作发生在第二个通用表表达式(CTE(中，即LatestDate。我建议在cameraimages表中添加一个索引，如下所示。

CREATE INDEX cmi_cameraid_daterec 
ON cameraimages (cameraId, dateRecording DESC);

该复合索引应允许cameraId随机访问，然后快速访问最新日期。请注意，它也应该有助于ProjectCameraLatest CTE。

您可以通过将主查询中的最后一个SELECT更改为仅SELECT * FROM LatestDate;来测试其性能。要查看它是否/如何使用索引，请尝试使用EXPLAIN或EXPLAIN ANALYZE：使用EXPLAIN SELECT * FROM LatestDate;作为主查询。

如果您在运行EXPLAIN时使用和不使用索引，您可能会了解到一些有关索引的有用信息。