在mysql查询中理解EXPLAIN

I am trying to interpret the explain of mysql in a query(written in two different ways),this is the table:

我试图在一个查询中解释mysql的解释(用两种不同的方式编写),这是表:

    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

This is the first type of query :

这是第一种查询:

    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 ; 

and this is the second:

这是第二个:

    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 ;

the two queries are asking the same thing, the only difference is the order of ref_table (driving_table), in the first case is text_mess, in the second case is a sub query. first result:

这两个查询都是同一个问题,唯一的区别是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

second result:

    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

As you can see the difference is in the order of the first two lines, my question in particular is on the second (the faster query) the second line should be the inner-table, but if so, why the column ref tells me: max.dmess, that should be the column of the ref-table (sub-query).

正如您所看到的差异是前两行的顺序,我的问题特别是在第二行(更快的查询)第二行应该是内部表,但如果是这样,为什么列ref告诉我: max.dmess,应该是ref-table(子查询)的列。

Is the last row referred on how the first is built? In the end, you think there is a more efficient query?

最后一行是关于如何构建第一行的吗?最后,你认为有一个更有效的查询?

1 个解决方案

#1

I think the answer is the table scan vs primary key. If you see, by the first query, MySQL does not use any keys but ready every row from the table "text_mess":

我认为答案是表扫描与主键。如果您看到,通过第一个查询,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

But you use an "ON" statement on the "derived2" table and MySQL will create an auto key:

但是你在“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

This key is not exists, so MySQL should create it.

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

If you take your second example, the full table scan happens on the "derived2" table, and your PRIMARY KEY from the "text_mess" is in use:

如果您采用第二个示例,则在“derived2”表上进行全表扫描,并且正在使用“text_mess”中的PRIMARY KEY:

    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

The answer is, that on such a situation MySQL decided to create and use an index, but generally it makes a full table scan, and it is simply faster.

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

Imagine that the auto_key0 key includes the only one column from this subquery again. This extra process is not necessary. That is why your second query is faster.

想象一下,auto_key0键再次包含该子查询中的唯一一列。这个额外的过程不是必需的。这就是为什么你的第二个查询更快。