【产品名称】
【产品版本】3.1.3
【问题描述】
mysql> explain select * from xxx\G
*************************** 1. row ***************************
Query Plan: =================================================================================
|ID|OPERATOR |NAME |EST. ROWS |COST |
---------------------------------------------------------------------------------
|0 |PX COORDINATOR | |8624112924|497374044900|
|1 | EXCHANGE OUT DISTR |:EX10000 |8624112924|385736937339|
|2 | PX PARTITION ITERATOR| |8624112924|385736937339|
|3 | TABLE SCAN |xxx|8624112924|385736937339|
=================================================================================
explain SELECT /*+ query_timeout(100000000000) parallel(16) */ IFNULL(SUM(xx) , 0) AS aa FROM xxx WHERE a1 = 251287325 AND a2 = 30 AND bb IN ( 0 ) AND cc IN ( 40 , 50 , 60 , 70 ) AND dd NOT IN ( 1321 , 1270 , 1013 , 1899 , 1214 , 3551 , 1206 , 3913 ) AND ee < 1653029404748\G
*************************** 1. row ***************************
Query Plan: ========================================================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |51718324752|
|1 | PX COORDINATOR | |1 |51717933493|
|2 | EXCHANGE OUT DISTR |:EX10000 |1 |51717933492|
|3 | MERGE GROUP BY | |1 |51717933492|
|4 | PX BLOCK ITERATOR| |2048346 |51717542233|
|5 | TABLE SCAN |xx(xx_idx)|2048346 |51717542233|
========================================================================================================================
索引xx_idx已经覆盖了需要查询的所有数据,这里explain出来是2048346行,实际执行需要6min
实际上count行数是98693,花了15min
SELECT /*+ query_timeout(100000000000) parallel(16) */ COUNT(xx) FROM xx WHERE aa = 251287325 AND bb = 30 AND cc IN ( 0 ) AND dd IN ( 40 , 50 , 60 , 70 ) AND ee NOT IN ( 1321 , 1270 , 1013 , 1899 , 1214 , 3551 , 1206 , 3913 ) AND ff < 1653029404748\G
*************************** 1. row ***************************
COUNT(xx): 98693
1 row in set (15 min 57.49 sec)