eq_ref和ref类型在MySQL中的解释是什么 [英] What does eq_ref and ref types mean in MySQL explain

问题描述

当我们在SQL查询中添加关键字"explain"作为前缀时，我们将获得一个包含一些列的表.请告诉我什么是类型"列.在这种情况下， eq_ref 和 ref 是什么意思.

When we prefix an SQL query with the keyword "explain" we get a table with some columns. Please tell me what is the "type" column. What does eq_ref and ref mean in that context.

推荐答案

我将尝试解释...

eq_ref –假设您有两个表.具有列(id，文本)的表A，其中id是主键.具有相同列(id，文本)的表B，其中id是主键.表A包含以下数据:

eq_ref – imagine that you have two tables. Table A with columns (id, text) where id is a primary key. Table B with the same columns (id, text) where id is a primary key. Table A has the following data:

1, Hello 
2, How are

表B具有以下数据:

1, world!
2, you?

将eq_ref想象为A和B之间的JOIN:

Imagine eq_ref as JOIN between A and B:

select A.text, B.text where A.ID = B.ID

此JOIN速度非常快，因为对于表A中扫描的每一行，表B中只能有 ONE 行满足JOIN条件.一个，最多不超过一个.那是因为B.id是UNIQUE.
在这里，您是:伪代码，它说明了服务器端的处理过程:

This JOIN is very fast because for each row scanned in table A there can be only ONE row in table B which satisfies the JOIN condition. One and no more than one. That is because B.id is UNIQUE.
Here you are: pseudo code which illustrates the processing at server side:

foreach (rowA in A)
{
    if (existsInBRowWithID(rowA.id)
    {
        addToResult(rowA.text, getRowInBWithID(rowA.id).text);
    }
}

ref -现在想象另一个带有列(id，text)的表C，其中id是一个索引，但不是唯一索引.表C具有以下数据:

ref - Now imagine another table C with columns (id, text) in which id an index but a non UNIQUE one. Table C has the following data:

1, John!
1, Jack!

将ref想象为A和C之间的JOIN:

Imagine ref as JOIN between A and C:

select A.text, C.text where A.ID = C.ID

在这里，您是:说明服务器端处理的伪代码:

Here you are: pseudo code illustrating the server side processing:

foreach (rowA in A)
{
    foreach (rowC in C)
    {
        if (rowA.id == rowC.id)
        {
            addToResult(rowA.text, rowC.text);
        }
    }
}

此JOIN的速度不如前一个，因为表A中扫描的每一行都有几个可能满足JOIN条件(嵌套循环)的行.那是因为C.ID不是唯一的.

This JOIN is NOT as fast as the former one because for each row scanned in table A there are SEVERAL possible rows in table C which may satisfy the JOIN condition (nested loops). That is because C.ID is NOT UNIQUE.

我希望对您有帮助...

I hope that helps...

Cheerz！

这篇关于eq_ref和ref类型在MySQL中的解释是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！