php - 提高 PHP 文件中的 SQL 查询性能

Question

这是查询，我用它来计算总行数(我需要它来进行分页)

$sql = "SELECT count(tmdb_id),group_concat(genres.genres_name) AS genres_name

 FROM `tmdb_movies` 

JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres1' )
    AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres2' )

GROUP BY tmdb_movies.tmdb_id";


$result = $conn->prepare($sql); 
$result->execute(); 
$totalrows = $result->rowCount();

带有EXPLAIN语句的查询截图

这是 SQL 查询，我用来回显结果

$stmt = $conn->prepare("SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies

LEFT JOIN videos ON videos.videos_tmdb_id=tmdb_movies.tmdb_id
JOIN genres USING (tmdb_id)

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres1' )
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres2' )

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY $sortby $order LIMIT 10 OFFSET $start"); 

     // Then fire it up
     $stmt->execute();

     // Pick up the result as an array
     $result = $stmt->fetchAll();




    // Now you run through this array in many ways, for example
     for($x=0, $n=count($result); $x < $n; $x++){
   echo $result[$x]["movie_title"];

}

带有此查询的 EXPLAIN 语句的屏幕截图

在这里，第一个查询执行大约需要 0.6 秒，第二个查询也需要大约 0.6 秒来执行。

有什么方法可以合并两个 SQL 查询以节省执行时间吗？

还有其他提高 SQL 查询性能的方法吗？

这是 SQL Fiddle第二个 SQL 查询。

我的索引:

create index idxm on tmdb_movies(tmdb_id);
create index idxv on videos(videos_tmdb_id, videos_name, videos_key);
create index idxv on genres(genres_name, tmdb_id );

Answer 1

这是什么意思

我不会说这是一个答案，因为查询优化从来都不是那么容易，这是更多的查询简化...

WHERE 1=1
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres1' )
AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = '$genres2' )

这不是等价的吗(都是ands)

WHERE
EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name IN( '$genres1','$genres2'))

我的猜测是排序会杀了你...尝试在没有排序的情况下运行它。

如果这很快，那么有时在仅提取 ID 的内部查询中进行排序会有所帮助，因为排序可能会生成一个临时表，因此提取最少量的信息对其进行排序，然后使用连接进行外部查询会有所帮助.

类似这样的事情(注意我不能真正测试这个所以这只是一个猜测，虽然我过去曾成功使用过这种方法)

SELECT
    tmdb_movies1.movie_title
    ,tmdb_movies1.tmdb_id
    ,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
    ,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
    ,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name
FROM (
    SELECT 
        tmdb_movies.tmdb_id
    FROM
        tmdb_movies
    WHERE
        EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name IN('$genres1','$genres2' ))
    GROUP BY tmdb_movies.movie_title, tmdb_movies.tmdb_id
    ORDER BY $sortby $order LIMIT 10 OFFSET $start
) AS tmdb_movies0
JOIN
    tmdb_movies AS tmdb_movies1 
ON
    tmdb_movies0.tmdb_id = tmdb_movies1.tmdb_id
LEFT JOIN
    videos
ON
    videos.videos_tmdb_id=tmdb_movies1.tmdb_id
JOIN
    genres ON genres.tmdb_id = tmdb_movies1.tmdb_id

基本上是为了加快排序，你提取出最少量的数据，只是一个 ID，对它们进行排序，然后使用它来加入并提取其余数据(批量)，从而让你创建最小的 Temp排序可能的表格，它使用更少的内存等...

它可以工作......(真的有 15% 的机会......哈哈)

* 作为旁注 *Or 也可以是一个 killer ，(虽然我没有看到任何)你可以通过对使用 UNION 而不是 OR 的子查询执行类似的技巧，从使用 or 的查询中获得一些性能，并仅提取 Pk ID。然后再join back在表上拉取数据，2个Union查询可以等同于一个OR。

注意 如果我没有提到 SQL 注入(inject)，我就不会为你提供好的服务，所以对这些东西使用准备好的语句 '.$var.'