带有LIMIT和UNION的Select查询性能问题

Question

我想从我的mysql数据库中选择某些数据。我正在使用一个带LIMIT和UNION的php循环和sql语句。

问题:我的查询速度太慢了。一条UNION语句需要2-4秒。由于循环的原因，整个查询需要3分钟。有没有机会优化我的查询？

我尝试分离“三个”语句并合并结果。但这并不是真的更快。所以我认为UNION不是我的问题。

PHP/SQL

我的代码正在运行两个foreach循环。代码运行正常。但性能是问题所在。

$sql_country = "SELECT country FROM country_list"; 
foreach ($db->query($sql_country) as $row_country) { //first loop (150 entries)
    $sql_color = "SELECT color FROM color_list";

    foreach ($db->query($sql_color) as $row_color) { //second loop (10 entries)
        $sql_all = "(SELECT ID, price FROM company 
                            WHERE country = '".$row_country['country']."' 
                                AND color = '".$row_color['color']."'  
                                AND price BETWEEN 2.5 AND 4.5  
                                order by price DESC LIMIT 2)

                            UNION

                            (SELECT ID, price FROM company 
                            WHERE country = '".$row_country['country']."' 
                                AND color = '".$row_color['color']."'  
                                AND price BETWEEN 5.5 AND 8.2  
                                order by price DESC LIMIT 2)

                            UNION 

                            (SELECT ID, price FROM company 
                            WHERE country = '".$row_country['country']."' 
                                AND color = '".$row_color['color']."'  
                                AND price BETWEEN 8.5 AND 10.8  
                                order by price DESC LIMIT 2)";
        foreach ($db->query($sql_all) as $row_all) {
                $shopID[] = $row_all['ID']; //I just need these IDs
        }
    }
}

你有什么想法或提示可以更快地完成这项工作吗？

Answer 1

(country, color, price, ID)上的索引应该可以将单个查询的性能从几秒钟提高到几毫秒，甚至更少。但是您仍然存在执行1500个查询的问题。根据您的系统，一次查询执行可能会增加大约10毫秒的开销，在您的情况下加起来是15秒。您需要找到一种方法来最小化查询的数量-在最好的情况下是单个查询。

对于较低的限制(如本例中的2 )，您可以组合多个具有不同偏移量的LIMIT 1子查询。我会动态生成这样的查询。

$priceRanges = [
    ['2.5', '4.5'],
    ['5.5', '8.2'],
    ['8.5', '10.8'],
];

$limit = 2;
$offsets = range(0, $limit - 1);

$queryParts = [];
foreach ($priceRanges as $range) {
    $rangeFrom = $range[0];
    $rangeTo   = $range[1];
    foreach ($offsets as $offset) {
        $queryParts[] = "
            select (
              select ID
              from company cmp
              where cmp.country = cnt.country
                and cmp.color   = clr.color
                and cmp.price between {$rangeFrom} AND {$rangeTo}
              order by cmp.price desc
              limit 1
              offset {$offset}
            ) as ID
            from country_list cnt
            cross join color_list clr
            having ID is not null
        ";
    }
}

$query = implode(' UNION ALL ', $queryParts);

这将生成一个相当长的UNION查询。您可以看到PHP demo on rexester.com和SQL demo on db-fiddle.com。

我不能保证它会更快。但这值得一试。