mysql - "Where IN"子句的 MariaDB 性能问题

Question

我的 SQL 代码有问题。我们开发了一个在 MySQL 上运行的应用程序，并且运行良好。所以我决定尝试一下 MariaDB，并将其安装在开发机器上。在某个查询 Stmt 上，我有一个我不明白的性能问题。查询如下:

 SELECT SAMPLES.*, UNIX_TIMESTAMP(SAMPLES.SAMPLE_DATE) as TIMESTAMP,RAWS.VALUE, DATAKEYS.RAW_ID, DATAKEYS.DATA_KEY_VALUE, DATAKEYS.DATA_KEY_ID, KEYDEF.KEY_NAME, KEYDEF.LDD_ID 
FROM 
PDS.TABLE_SAMPLES SAMPLES 
RIGHT OUTER JOIN PDS.TABLE_RAW_VALUES RAWS ON SAMPLES.SAMPLE_ID = RAWS.SAMPLE_ID 
RIGHT OUTER JOIN PDS.TABLE_SAMPLE_DATA_KEYS DATAKEYS ON(DATAKEYS.RAW_ID = RAWS.RAW_ID AND DATAKEYS.SAMPLE_ID = SAMPLES.SAMPLE_ID) OR 
(DATAKEYS.RAW_ID = 0 AND DATAKEYS.SAMPLE_ID = SAMPLES.SAMPLE_ID) 
RIGHT OUTER JOIN PDS.TABLE_DATA_KEY_DEFINITION KEYDEF ON(DATAKEYS.DATA_KEY_ID = KEYDEF.DATA_KEY_ID) 
WHERE 
SAMPLES.SAMPLE_ID IN(1991331,1991637,1991941,2046105,2046411,2046717,2047023,2047635,2047941,2048247)
AND (SAMPLES.PARAMETER_ID = 9) 
GROUP BY DATAKEYS.DATA_KEY_ID, RAWS.RAW_ID, DATAKEYS.DATA_KEY_ID 
ORDER BY SAMPLES.SAMPLE_ID, DATAKEYS.RAW_ID;

只要我在“WHERE IN”条件下只有一个值，查询就需要大约 10 毫秒的时间来执行。这与 MySQL 5.6 所采用的大致相同。一旦我在那里添加另一个值，查询时间就会增加到几分钟。在 MySQL 中，它的提升非常缓慢，显示的查询在 MySQL 上大约需要 150 毫秒，在使用完全相同的数据集的新 MariaDB 安装上大约需要 140 秒。

我不是 SQL 专家，你能给我一些线索如何优化查询以按预期运行吗？

Answer 1

右外连接被where 子句转换为内连接。所以，只需使用正确的 join 类型(我不确定这是否会影响查询的优化，但它可能):

SELECT SAMPLES.*, UNIX_TIMESTAMP(SAMPLES.SAMPLE_DATE) as TIMESTAMP,RAWS.VALUE, DATAKEYS.RAW_ID, DATAKEYS.DATA_KEY_VALUE, DATAKEYS.DATA_KEY_ID, KEYDEF.KEY_NAME, KEYDEF.LDD_ID 
FROM PDS.TABLE_SAMPLES SAMPLES JOIN
     PDS.TABLE_RAW_VALUES RAWS
     ON SAMPLES.SAMPLE_ID = RAWS.SAMPLE_ID JOIN
     PDS.TABLE_SAMPLE_DATA_KEYS DATAKEYS
     ON (DATAKEYS.RAW_ID = RAWS.RAW_ID AND DATAKEYS.SAMPLE_ID = SAMPLES.SAMPLE_ID) OR 
        (DATAKEYS.RAW_ID = 0 AND DATAKEYS.SAMPLE_ID = SAMPLES.SAMPLE_ID) JOIN
     PDS.TABLE_DATA_KEY_DEFINITION KEYDEF
     ON DATAKEYS.DATA_KEY_ID = KEYDEF.DATA_KEY_ID)
WHERE SAMPLES.SAMPLE_ID IN (1991331, 1991637, 1991941, 2046105, 2046411, 2046717, 2047023, 2047635, 2047941, 2048247) AND
      (SAMPLES.PARAMETER_ID = 9) 
GROUP BY DATAKEYS.DATA_KEY_ID, RAWS.RAW_ID, DATAKEYS.DATA_KEY_ID 
ORDER BY SAMPLES.SAMPLE_ID, DATAKEYS.RAW_ID;

接下来，此查询的最佳索引 -- 无论 IN 中值的数量如何，都是复合索引 PDS.TABLE_SAMPLES(PARAMETER_ID, SAMPLE_ID)。这会处理 WHERE 子句。

因为您的查询在某些情况下运行得很快，我假设其他表具有适合连接的索引。