如何优化执行耗时超1分钟的MySQL关联子查询语句

问题描述 #

原有SQL查询执行耗时超过1分钟：

SELECT * FROM kp_landing_page lp
WHERE lp.parent = '7' AND (
    SELECT COUNT(*) FROM kp_landing_page_product lpp
        WHERE lpp.landing_page_id = lp.landing_page_id
          AND lpp.productid = '6176'
) != 0

尝试改写的SQL语法错误，虽然执行速度提升但返回结果异常，且phpmyadmin报错：

The current selection does not contain a unique column. 
  Functions such as raster edits, checkboxes, Edit, 
  Copy and Delete are not available.

补充信息 #

原查询执行计划 #

1 id
PRIMARY select_Type
lp table
ALL type 
NULL possible_keys 
NULL keys
NULL key_len
NULL ref
233 rows
Using where extra
---
2
DEPENDENT SUBQUERY
lpp
ref
landing_page_id
landing_page_id
4
kerstpakketonline.lp.landing_page_id
437
Using where

业务代码上下文 #

慢查询实际出现在循环查询逻辑中，属于O(n)级别的性能问题：

$landingPages = array();
$qGetMainPages = $connection->query("SELECT *
         FROM kp_landing_page WHERE parent = 0");

foreach ($qGetMainPages->rows as $mainPage) {
    $qGetSubPages = $connection->query("
        SELECT  lp.* FROM kp_landing_page lp
        WHERE lp.parent = '" . (int)$mainPage['landing_page_id'] . "' AND (
            SELECT COUNT(*) FROM kp_landing_page_product lpp
        WHERE lpp.landing_page_id = lp.landing_page_id 
           AND lpp.productid = " . (int)$row['productID'] . "
        ) != 0
    ");


    foreach ($qGetSubPages->rows as $subPage) {
        $landingPages[$mainPage['title']][] = $subPage['title'];
    }
}

表结构与数据量 #

• kp_landing_page_product：16万行数据，现有单值索引landing_page_id、productid
• kp_landing_page：233行数据，主键为landing_page_id
• 外层主页面查询仅返回9条数据

具体优化方案 #

1. 单条查询优化 #

首先将COUNT(*) != 0改为EXISTS，EXISTS匹配到第一条符合条件的记录就会终止查询，不需要统计全量匹配数据，性能提升明显：

SELECT lp.* FROM kp_landing_page lp
WHERE lp.parent = '7' 
AND EXISTS (
    SELECT 1 FROM kp_landing_page_product lpp
    WHERE lpp.landing_page_id = lp.landing_page_id
    AND lpp.productid = '6176'
)

再新增联合索引，避免回表过滤，进一步压缩查询耗时：

CREATE INDEX idx_landing_product ON kp_landing_page_product(landing_page_id, productid);

2. 业务循环逻辑优化 #

将循环执行的9次查询合并为1次查询，避免多次数据库交互开销：

$productId = (int)$row['productID'];
// 一次性查询所有符合条件的主、子页面关联关系
$allData = $connection->query("
    SELECT 
        main.title as main_title,
        sub.title as sub_title
    FROM kp_landing_page main
    INNER JOIN kp_landing_page sub ON sub.parent = main.landing_page_id
    WHERE main.parent = 0 
    AND EXISTS (
        SELECT 1 FROM kp_landing_page_product lpp
        WHERE lpp.landing_page_id = sub.landing_page_id
        AND lpp.productid = {$productId}
    )
")->rows;

// 拼装返回格式
$landingPages = array();
foreach ($allData as $item) {
    $landingPages[$item['main_title']][] = $item['sub_title'];
}

改写SQL报错原因说明 #

之前的改写语法错误，JOIN后必须跟表/结果集，不能直接写判断条件，且缺少关联条件，所以返回结果异常，同时查询结果没有返回主键列才触发phpmyadmin的提示。

内容的提问来源于stack exchange，提问作者niels van hoof