将我的SQL减少了40％，但速度慢了60倍

Question

i具有JPA生成的SQL，在最坏情况下有1250行。

我查询的结构是嵌套在查询的 WHERE语句中的20个子Queries。此查询在0.015秒内运行。

我试图优化我的查询，因为我注意到我已经重复使用了很多连接的子查询（例如，两个子查询只有其WHERE语句有所不同）。这将SQL降低到750行，并减少到12个子征服，但由于某种原因，运行时间为0.9秒。

有什么可以解释的吗？当有更多可用数据可用时，我试图使查询运行更快的速度可能会更快地运行？

谢谢

Answer 1

，在问题中提供的有限信息只能推测为什么执行时间在您的特定情况下会增加，但是长时间和缺点是，较少的代码没有相等的更快查询。

"简化"查询的主要原因之一可能导致更长的执行时间，即简化意味着索引不再使用，因为虽然查询看起来更简单，但实际上您要求优化者做一些更复杂的事情。

想象这个简单的模式：

CREATE TABLE T1 (ID INT AUTO_INCREMENT PRIMARY KEY, A INT);
CREATE TABLE T2 (ID INT AUTO_INCREMENT PRIMARY KEY, A INT, B INT);
CREATE INDEX IX_T2_A ON T2 (A);
CREATE INDEX IX_T2_B ON T2 (B);

现在，假设我有以下查询：

SELECT  COUNT(T1.ID)
FROM    T1
        INNER JOIN
        (   SELECT  ID
            FROM    T2
            WHERE   A IN (1, 10)
            UNION 
            SELECT  ID
            FROM    T2
            WHERE   B IN (1, 10)
        ) T2
            ON t2.ID = t1.ID;

您可能会认为，可以"简化"以下删除UNION的内容：

SELECT  COUNT(T1.ID)
FROM    T1
        INNER JOIN
        (   SELECT  ID
            FROM    T2
            WHERE   A IN (1, 10)
            OR      B IN (1, 10)
        ) T2
            ON t2.ID = t1.ID;

但是，通过组合您的标准，您确保将不使用索引（在T2.A或T2.B上），因为优化器正在尝试一次执行两者。因此，将执行全表扫描，而不是使用您的两个索引，而是根据数据的分散分辨率，这可能会更加昂贵。

运行EXPLAIN时已确认这一点：

ID      SELECT_TYPE     TABLE       TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    FILTERED    EXTRA
1       PRIMARY         <derived2>  system  (null)          (null)  (null)      (null)  1       100     
1       PRIMARY         T1          const   PRIMARY         PRIMARY     4       const   1       100         Using index
2       DERIVED         T2          index   IX_T2_A         IX_T2_A     5       (null)  1       100         Using where; Using index
3       UNION           T2          index   IX_T2_B         IX_T2_B     5       (null)  1       100         Using where; Using index
(null)  UNION RESULT    <union2,3>  ALL         (null)  (null)      (null)  (null)  (null)      (null)  

ID      SELECT_TYPE TABLE       TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    FILTERED    EXTRA
1       PRIMARY     <derived2>  system  (null)          (null)  (null)      (null)  1       100     
1       PRIMARY     T1          const   PRIMARY         PRIMARY 4           const   1       100     Using index
2       DERIVED     T2          ALL     IX_T2_A,IX_T2_B (null)  (null)      (null)  1       100     Using where

SQL小提琴上的示例

Answer 2

如果查询运行很快，则它不会处理太多行。

很有可能是使用索引处理的连接。引擎只能将索引加载到内存中并处理查询 - 甚至可能永远不会触摸原始数据。

当您在没有where条款的情况下实现加入时，发生了一些事情：

1. 您知道有阅读和编写其他数据的开销;
2. 所处理的数据量可能比任何一个子征值大；
3. 其他优化 - 可能会消除大部分处理 - 不会进行。

Answer 3

也许那些额外的行创建了比您拥有ATM的表，因此您可以比较表（数据）更快。但是现在，由于您已经减少了较小表的数量并大概增加了较大的表的大小，因此查询必须通过较大表（更多数据）在运行特定查询时分页花更多的时间。

更多要进行比较的数据=更多处理时间