MySQL 查询慢除了使用索引？

Question

我正在运行一个查询来查找与同一表中其他行相关联的行(特别是那些具有相同 ddef 索引的行，也就是除了 svar 之外的所有 NOT NULL 列是相同的)。查询很慢，我不明白为什么。

表格:

    CREATE TABLE `data_der_testing` (
        `def`       VARCHAR( 30 )     NOT NULL ,
        `cntry`     VARCHAR( 5 )      NOT NULL ,
        `var`       VARCHAR( 10 )     NOT NULL ,
        `type`      VARCHAR( 4 )      NOT NULL ,
        `svar`      VARCHAR( 5 )      NOT NULL ,
        `track`     INT ( 3 )         NOT NULL ,
        `year`      INT ( 5 )         NOT NULL ,
        `v1211`     TEXT,
        `v1212`     TEXT,
        `v1302`     TEXT,
        `v1304`     TEXT,
        `v1305`     TEXT,
        INDEX   ddef ( `cntry`, `var`, `type`, `track`, `year` ),
        UNIQUE  ( `def` ),
        UNIQUE  idb  ( `cntry`, `var`, `type`, `svar`, `track`, `year` )                        
    ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

表中的数据包含约 450k 行:

    `def`: a combination of all other "NOT NULL" columns, which makes it unique
    `cntry`: 34 country codes
    `var`: 86 variable codes
    `type`: 2 type codes
    `svar`: 3 sub-variable codes
    `track`: 6 codes
    `year`: 32 year codes (99, 1980...2010)
    `v...`: the variable value I want to check and update if needed

SHOW INDEX 的输出:

    data_der    1   ddef    1   cntry   A   410                 BTREE   
    data_der    1   ddef    2   var     A   18871               BTREE   
    data_der    1   ddef    3   type    A   33388               BTREE   
    data_der    1   ddef    4   track   A   43404               BTREE   
    data_der    1   ddef    5   year    A   434048              BTREE

查询:

    SELECT *
    FROM `data_der`
    WHERE `type`='str' && `svar` != '99' &&`v1305` = '-90' && ROW(`cntry`,`var`,`type`,`track`,`year`) IN
    (
         SELECT `cntry`,`var`,`type`,`track`,`year`
         FROM `data_der`
         WHERE `type` = 'str' && `svar` != '99' && `v1305` != '-90'
         GROUP BY `cntry`,`var`,`type`,`track`,`year`
    )

它是解释:

    1   PRIMARY             data_der    ALL                        434048   Using where
    2   DEPENDENT SUBQUERY  data_der    index           ddef    71      6   Using where

如果您对如何让这个问题对普通观众更有用有任何建议，请告诉我。我一直从其他问题中获益，很乐意做出贡献。

最佳乔内兹

Answer 1

根据评论(谢谢大家!)，我编写了以下 JOIN，我将使用它来更新:

    UPDATE `data_der`
    SET `v1305` = '-95'
    WHERE `def` IN
    (
        SELECT * FROM
        (
            SELECT
                t1.`def`
            FROM `data_der` AS t1
            JOIN `data_der` AS t2
            ON (t1.`cntry`,t1.`var`,t1.`type`,t1.`track`,t1.`year`) = (t2.`cntry`,t2.`var`,t2.`type`,t2.`track`,t2.`year`)
            WHERE
                t1.`type`      = 'str'
                && t1.`svar`  != '99'
                && t1.`v1305`  = '-90'
                && t2.`v1305` != '-90'
        ) AS sub
    )

以及子查询的EXPLAIN:

    1   SIMPLE  t1  ref idb,sdef,cntry,var,type,track,year,ddef type    14  const   65338   Using where
    1   SIMPLE  t2  ref idb,sdef,cntry,var,type,track,year,ddef ddef    71  impic.t1.cntry,impic.t1.var,const,impic.t1.track,impic.t1.year  1   Using where

所以我的 INDEX ddef ( cntry, var, type, track , year ) 正在被使用!成功! (你可以忽略其他指标，我知道不是所有的都有意义...)

额外福利:有没有更短的方式来编写 ON 子句？