mysql - 理解 mysql 查询中的 EXPLAIN

Question

我试图在查询中解释 mysql 的 explain(以两种不同的方式编写)，这是表格:

    create table text_mess(
    datamess timestamp(3) DEFAULT 0,
    sender bigint ,
    recipient bigint ,
    roger boolean,
    msg char(255),
    foreign key(recipient) 
            references users (tel) 
                        on delete cascade
                        on update cascade,
primary key(datamess,sender)
)
engine = InnoDB

这是第一种查询:

    EXPLAIN
    select /*!STRAIGHT_JOIN*/datamess, sender,recipient,roger,msg
    from text_mess join (select max(datamess)as dmess
                    from text_mess 
                    where roger = true
                    group by sender,recipient) as max
                    on text_mess.datamess=max.dmess ; 

这是第二个:

    EXPLAIN
    select /*!STRAIGHT_JOIN*/datamess, sender,recipient,roger,msg
    from  (select max(datamess)as dmess
                    from text_mess 
                    where roger = true
                    group by sender,recipient) as max
      join
    text_mess
    on max.dmess = text_mess.datamess ;

这两个查询问的是同一件事，唯一的区别是ref_table(driving_table)的顺序，第一种情况是text_mess，第二种情况是一个子查询。第一个结果:

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows        Extra
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY     text_mess       ALL     PRIMARY         null        null    null                            294225      null
    1   PRIMARY     derived2        ref     auto_key0       auto_key0   7       inextremis.text_mess.datamess   10          Using index
    2   DERIVED     text_mess       ALL     recipient       null        null    null                            294225      Using where; Using temporary; Using filesort

第二个结果:

    id  select_type     table       type    possible_keys   key          key_len    ref     rows    Extra
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY      <derived2>     ALL     null            null         null      null     294225  Using where
    1   PRIMARY      text_mess      ref     PRIMARY         PRIMARY         6      max.dmess    1   null
    2   DERIVED      text_mess      ALL     recipient       null         null      null     294225  Using where; Using temporary; Using filesort

如您所见，区别在于前两行的顺序，我的问题特别是第二行(更快的查询)第二行应该是内表，但如果是这样，为什么列 ref 告诉我:max.dmess，那应该是引用表(子查询)的列。

最后一行是否提到了第一行是如何构建的？最后，您认为还有更高效的查询方式吗？

Answer 1

我认为答案是表扫描与主键。如果您看到，在第一个查询中，MySQL 不使用任何键，而是准备好表“text_mess”中的每一行:

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows        Extra
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     1   PRIMARY     text_mess       ALL     PRIMARY         null        null    null                            294225      null

但是您在“derived2”表上使用“ON”语句，MySQL 将创建一个自动键:

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows        Extra
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY     derived2        ref     auto_key0       auto_key0   7       inextremis.text_mess.datamess   10          Using index

这个键不存在，所以 MySQL 应该创建它。

如果以第二个示例为例，全表扫描发生在“derived2”表上，并且正在使用“text_mess”中的主键:

    id  select_type     table       type    possible_keys   key          key_len    ref     rows    Extra
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY      <derived2>     ALL     null            null         null      null     294225  Using where
    1   PRIMARY      text_mess      ref     PRIMARY         PRIMARY         6      max.dmess    1   null

答案是，在这种情况下，MySQL 决定创建和使用索引，但通常它会进行全表扫描，而且速度更快。

假设 auto_key0 键再次包含此子查询中的唯一一列。这个额外的过程不是必需的。这就是为什么您的第二个查询更快。