因此，我有一个相对简单的查询，该查询根据WHERE为一系列输入选择存储函数的结果。完整代码如下。
令我感到困扰的是，当我通过mysql cli调用查询时，需要662秒的时间才能返回841行的单列。现在，如果我将完全相同的代码复制/粘贴到phpmyadmin中，则相同的值将在28秒内返回。
我需要做什么，以便mysql像phpmyadmin一样快地执行此查询？
我已经尝试过的内容：
验证phpmyadmin和php cli是否使用相同的EXPLAIN计划。EXPLAIN计划现在包括在下面。
验证phpmyadmin和php cli是否都以同一用户身份执行查询。
测试了mysql cli是否比php cli具有更好的性能。
结果：mysql cli在661sec内返回值，该速度可能与php cli相同。我只是意识到，SHOW PROCESSLIST会在进程进入睡眠状态后继续计时。上面的问题标题和描述已被修改。
当前正在探索：
编辑存储的函数，以便使用WHERE IN代替使用WHERE EXISTS。
由http://dev.mysql.com/doc/refman/5.5/en/subquery-optimization-with-exists.html建议
整个查询：（包括非问题字段）
SELECT
EC_ITM_PULL.SKU_NUM,
EC_ITM_PULL.COLOR_CD,
GM_SKU.COLOR_DES,
GM_ITM.DES1,
GM_ITM.DES2,
CUSTOM.EC_GET_ONHAND(SUBSTRING(GM_SKU.SKU_NUM, 1, 9), GM_SKU.COLOR_CD) OH,
UPPER(EC_ITM_PULL.ITEM_PULLED_INIT) INIT,
CUSTOM.EC_MOST_RECENT_EVENT(SUBSTRING(GM_SKU.SKU_NUM, 1, 9), GM_SKU.COLOR_CD, 'E') EVENT_CD
FROM
EC_ITM_PULL,
GM_MERCH.GM_SKU,
GM_INV.GM_ITM,
EC_ITM
WHERE
EC_ITM.SKU_NUM = EC_ITM_PULL.SKU_NUM
AND EC_ITM_PULL.COLOR_CD = EC_ITM.COLOR_CD
AND EC_ITM_PULL.ITEM_PULLED IS NOT NULL
AND GM_ITM.ITM_CD = SUBSTRING(EC_ITM.SKU_NUM, 1, 9)
AND EC_ITM.SKU_NUM = GM_SKU.SKU_NUM
AND EC_ITM.TO_STUDIO IS NULL
AND GM_ITM.ITM_CD = SUBSTRING(EC_ITM_PULL.SKU_NUM, 1, 9);
查询说明计划：
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------------------------------------+------+-------------+
| 1 | SIMPLE | EC_ITM_PULL | ALL | SKU_NUM | NULL | NULL | NULL | 2100 | Using where |
| 1 | SIMPLE | GM_ITM | eq_ref | PRIMARY | PRIMARY | 38 | func | 1 | Using where |
| 1 | SIMPLE | GM_SKU | ref | SKU_NUM | SKU_NUM | 38 | CUSTOM.EC_ITM_PULL.SKU_NUM | 1 | |
| 1 | SIMPLE | EC_ITM | eq_ref | PRIMARY | PRIMARY | 58 | GM_MERCH.GM_SKU.SKU_NUM,CUSTOM.EC_ITM_PULL.COLOR_CD | 1 | Using where |
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------------------------------------+------+-------------+
问题存储功能：
CREATE DEFINER=`root`@`localhost` FUNCTION `EC_GET_ONHAND`(`N_ITM_CD` VARCHAR(12), `N_COLOR_CD` VARCHAR(6)) RETURNS smallint(5)
BEGIN
DECLARE TOTALOH SMALLINT(5);
IF N_COLOR_CD IS NULL THEN SELECT IFNULL(SUM(IFNULL(RESERVE_QTY,0)+IFNULL(AVAIL_QTY,0)),0)
INTO TOTALOH FROM GM_INV.GM_INV_LOC
WHERE SKU_NUM
IN (
SELECT SKU_NUM
FROM GM_MERCH.GM_SKU
WHERE ITM_CD = N_ITM_CD AND COLOR_CD IS NULL
)
AND (
(
(
STORE_CD='85'
OR STORE_CD='95'
)
AND LOC_CD='STG72'
)
OR (
(
STORE_CD='72'
)
AND LOC_CD='RCV'
)
);
ELSE SELECT IFNULL(SUM(IFNULL(RESERVE_QTY,0)+IFNULL(AVAIL_QTY,0)),0)
INTO TOTALOH
FROM GM_INV.GM_INV_LOC
WHERE SKU_NUM
IN (
SELECT SKU_NUM
FROM GM_MERCH.GM_SKU
WHERE ITM_CD = N_ITM_CD
AND COLOR_CD = N_COLOR_CD)
AND (
(
(
STORE_CD='85'
OR STORE_CD='95'
)
AND LOC_CD='STG72'
)
OR (
(
STORE_CD='72'
)
AND LOC_CD='RCV'
)
);
END IF;
RETURN TOTALOH;
END
功能说明计划：
+----+--------------------+------------+----------------+---------------+---------------+---------+------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+----------------+---------------+---------------+---------+------------------+--------+--------------------------+
| 1 | PRIMARY | GM_INV_LOC | ALL | NULL | NULL | NULL | NULL | 509791 | Using where |
| 2 | DEPENDENT SUBQUERY | GM_SKU | index_subquery | SKU_ITM_COLOR | SKU_ITM_COLOR | 97 | func,const,const | 1 | Using index; Using where |
+----+--------------------+------------+----------------+---------------+---------------+---------+------------------+--------+--------------------------+
最佳答案
我通过重写存储函数解决了该问题。这实际上将查询时间缩短到了1.19sec，因此我实际上使查询时间比phpmyadmin =）还要快。
首先，我接受问题中链接文章的建议，并编辑了这一部分：
WHERE SKU_NUM
IN (
SELECT SKU_NUM
FROM GM_MERCH.GM_SKU
WHERE ITM_CD = N_ITM_CD AND COLOR_CD IS NULL
)
对此：
WHERE EXISTS
(
SELECT 1
FROM GM_MERCH.GM_SKU, GM_INV.GM_INV_LOC
WHERE
ITM_CD = N_ITM_CD
AND COLOR_CD IS NULL
AND GM_INV_LOC.SKU_NUM = GM_MERCH.SKU_NUM
)
这将查询的执行时间减少到不到180秒，但是由于该函数正在为子查询中的每一行重复加法运算，因此导致该函数返回不正确的值。
因此，我想到也许我根本不应该使用子查询。我将函数中的查找重写如下：
SELECT IFNULL(SUM(IFNULL(RESERVE_QTY,0)+IFNULL(AVAIL_QTY,0)),0)
INTO TOTALOH FROM GM_INV.GM_INV_LOC, GM_MERCH.SKU_NUM
WHERE
ITM_CD = N_ITM_CD
AND COLOR_CD IS NULL
AND GM_INV_LOC.SKU_NUM = GM_SKU.SKU_NUM
AND (
(
(
STORE_CD='85'
OR STORE_CD='95'
)
AND LOC_CD='STG72'
)
OR (
(
STORE_CD='72'
)
AND LOC_CD='RCV'
)
);
整个大查询现在在1.19秒内以准确的值返回其841行。
关于mysql - 在phpmyadmin中查询需要28秒，但mysql命令行需要662秒，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27282823/