500万行MySQL表：查询日期差值遇内存溢出与1317错误求解决方案

问题描述 #

有一张包含500万行数据的product表，结构如下：

+-------------+------------+-----------+
|  product    | date       |  count    | 
+-------------+------------+-----------+
|  платье     | 04-01-2024 | 186574    | 
|  штаны      | 04-01-2024 | 20564     |
|  кепка      | 07-01-2024 | 104443    | 
|  штаны      | 06-01-2024 | 10574     | 
|  платье     | 06-01-2024 | 223001    |
+-------------+------------+-----------+

需求：筛选两个自定义日期的记录，匹配相同product名称后计算count的差值，最终取差值排名前100的结果。

当前采用两次查询+PHP双重循环的方式实现，代码如下：

$get_ex = $connection->prepare("SELECT * FROM product WHERE date = :date");
$get_ex->execute(array(':date' => $first_date));
$ex_list_2 = $get_ex->fetchAll(PDO::FETCH_ASSOC);

$get_ex_3 = $connection->prepare("SELECT * FROM product WHERE date = :date");
$get_ex_3->execute(array(':date' => $second_date));
$ex_list_3 = $get_ex_3->fetchAll(PDO::FETCH_ASSOC);

$result = Array();
$i=0;
foreach($ex_list_2 as $key => $value)
{
    foreach($ex_list_3 as $key2 => $value2)
    {
        if($value['product'] == $value2['product'])
        {
            $result[$i]['product'] = $value['product'];
            $result[$i]['count_1'] = $value['count'];
            $result[$i]['count_2'] = $value2['count'];
            $result[$i]['res'] = $value['count'] - $value2['count'];
            $i++;
        }
    }
}

但该方法触发内存溢出错误：

Fatal error: Allowed memory size of 1073741824 bytes exhausted

即使调大memory_limit到2048MB仍无法解决。尝试编写MySQL单查询时出现1317超时错误，且排序无效，需要正确的单查询语句。

正确的MySQL查询语句 #

方法1：INNER JOIN 关联查询 #

适合需要明确关联两个日期记录的场景，确保只返回两个日期都存在的产品：

SELECT 
    p1.product,
    p1.count AS `{$first_date}`, -- 替换为实际传入的第一个日期参数
    p2.count AS `{$second_date}`, -- 替换为实际传入的第二个日期参数
    ABS(p1.count - p2.count) AS diff -- 用ABS取绝对值，若需要正负差值可去掉ABS
FROM 
    product p1
INNER JOIN 
    product p2 ON p1.product = p2.product
WHERE 
    p1.date = '{$first_date}'
    AND p2.date = '{$second_date}'
ORDER BY 
    diff DESC -- 按差值降序排序，若要升序改为ASC
LIMIT 100;

方法2：条件聚合查询（更高效） #

仅扫描一次表，性能优于JOIN，适合大数据量场景：

SELECT 
    product,
    MAX(CASE WHEN date = '{$first_date}' THEN count ELSE 0 END) AS `{$first_date}`,
    MAX(CASE WHEN date = '{$second_date}' THEN count ELSE 0 END) AS `{$second_date}`,
    ABS(
        MAX(CASE WHEN date = '{$first_date}' THEN count ELSE 0 END) - 
        MAX(CASE WHEN date = '{$second_date}' THEN count ELSE 0 END)
    ) AS diff
FROM 
    product
WHERE 
    date IN ('{$first_date}', '{$second_date}')
GROUP BY 
    product
HAVING 
    `{$first_date}` > 0 AND `{$second_date}` > 0 -- 过滤仅单个日期有数据的产品
ORDER BY 
    diff DESC
LIMIT 100;

关键优化建议 #

1. 添加联合索引：
   为product表创建联合索引idx_date_product_count(date, product, count)，这样WHERE条件、JOIN关联、GROUP BY都能命中索引，避免全表扫描导致的1317超时错误，大幅提升查询速度。
2. 优化日期字段类型：
   如果date字段是字符串类型，建议修改为DATE类型，既节省存储空间，又能更高效地进行日期比较和索引操作。

为什么PHP方法不可行？ #

• 双重循环的时间复杂度为O(n*m)，当两个日期的记录数较多时（比如各几十万条），循环次数会达到数十亿次，执行效率极低。
• 一次性将几十万甚至几百万条数据加载到PHP内存中，远超PHP的内存上限，即使调大memory_limit也只是治标不治本，且会占用大量服务器资源。

内容的提问来源于stack exchange，提问作者Ростислав