php - 如何更好地优化具有特定搜索条件的MySQL SELECT语句？

Question

我开发了一个图像搜索工具来索引所有图像的目录，存储数据，然后根据搜索检索结果。

这是可行的，但由于数据量很大(150,000 张图像大约有 270 万行元数据)，返回结果需要很长时间(有时 >5 分钟)。我相信这是由于在 foreach 循环中查询第二组数据造成的。我希望将我的 sql 合并到单个语句中以更好地优化(除非有另一种方法!)。

到目前为止，我返回元数据与搜索词匹配的所有 image_id，然后使用返回的 image_id 获取每个图像的元数据表中的所有元数据。这是数据库的基本结构:

image_id       filename         thumb 
=============  ============     ================= 
1              image_XYZ.jpg    image_XYZ_thumb.jpg
2              emoticon.png     emoticon_thumb.jpg
3              runner_bean.jpg  runner_bean_thumb.jpg

meta_id  meta_key     meta_value      image_id
=======  ========     ==========      ========
1        filetype     jpg             1
2        keyword      runner          1
3        height       600             1
4        filetype     png             2
5        filesize     5198413         2
6        description  smiley face     2
7        filetype     jpg             3
8        filesize     12485           3
9        description  runnerbean      3
10       keyword      runner          3
11       keyword      vegetable       3

1.

到目前为止，我的代码首先获取搜索词“runner”匹配的所有图像(因此图像 1 和 3)

SELECT image_id
FROM metadata
WHERE (meta_value LIKE '%runner%')
AND meta_key IN ('keyword', 'filename', 'description')
GROUP BY image_id

2.

然后循环返回的结果集并获取每个结果集的元数据:

$search_results = $imagesearch->search_images(); //object array from above sql

foreach ($search_results as $image) {
    $id = $image->image_id;
    $get_metadata = $imageget->get_metadata($id)
}

3.

SELECT *
FROM metadata 
WHERE image_id = $id

使用元数据，我将提取meta_key等于我想要的数据(例如 height )的行来检索值等。

所以我的最终数组看起来像这样:

Array
(
    [1] => array(
        [image_id] => 1
        [filename] => image_XYZ.jpg
        [thumb] => image_XYZ_thumb.jpg
        [filetype] => jpg
        [keyword] => runner
        [height] => 600
    ),
    [2] => array(
        [image_id] => 3
        [filename] => runner_bean.jpg
        [thumb] => runner_bean_thumb.jpg
        [filetype] => jpg
        [filesize] => 12485
        [description] => runnerbean
        [keyword] => runner
        [keyword] => vegetable
    ),
)

<小时/>

到目前为止，我的努力是使用子查询首先获取与搜索词匹配的图像，然后获取这些图像的元数据:

SELECT *
FROM metadata m
LEFT JOIN image i ON i.image_id = m.image_id
WHERE i.image_id IN (
    SELECT image_id
    FROM metadata
    WHERE $search
    AND meta_key IN ('keyword', 'filename', 'description')
    GROUP BY image_id
)
GROUP BY m.image_id

我的分组依据是按图像 ID 折叠结果，因此我只获得每个图像的第一行(上例中的文件类型)。有没有办法获得与搜索词匹配的最终图像数组，并以更优化的方式返回每个图像的元数据？

如有任何帮助，我们将不胜感激

Answer 1

我看不出查询中的第二个 GROUP BY 有何意义，我猜您需要将其删除。然后，查询将为每个图像元数据生成一行，并按图像排序。这并不完全是您期望的结构(这需要旋转结果集)，但您可以循环它并动态生成数组数组(每次 image_id 更改时，开始提供新的全局数组中的子数组)。

SELECT m.*
FROM metadata m
WHERE m.image_id IN (
    SELECT DISTINCT image_id
    FROM metadata
    WHERE 
        meta_value LIKE '%runner%'
        AND meta_key IN ('keyword', 'filename', 'description')
)
ORDER BY m.image_id

注意:根据数据的外观，内部查询中的 SELECT DISTINCT 可能比 SELECT 更高效。

另一个选项(产生相同的结果集)是使用带有 WHERE EXISTS 子句的相关子查询，如下所示:

SELECT m.*
FROM metadata m
WHERE EXISTS (
    SELECT 1
    FROM metadata
    WHERE 
        image_id = m.image_id
        AND meta_value LIKE '%runner%'
        AND meta_key IN ('keyword', 'filename', 'description')    
)
ORDER BY m.image_id