19
I'll try an explanation...
我會試着解釋一下......
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:
eq_ref - 想象你有兩張桌子。表A包含列(id,text),其中id是主鍵。表B具有相同的列(id,text),其中id是主鍵。表A包含以下數據:
1, Hello
2, How are
Table B has the following data:
表B有以下數據:
1, world!
2, you?
Imagine eq_ref as JOIN between A and B:
想象一下eq_ref為A和B之間的JOIN:
select A.text, B.text where A.ID = B.ID
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 a pseudo code which illustrates the processing at server side:
這個JOIN非常快,因為對於表A中掃描的每一行,表B中只能有一行滿足JOIN條件。一個,不超過一個。那是因為B.id是獨一無二的。在這里,您是一個偽代碼,它說明了服務器端的處理:
foreach (rowA in A)
{
if (existsInBRowWithID(rowA.id)
{
addToResult(rowA.text, getRowInBWithID(rowA.id).text);
break;
}
}
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:
ref - 現在想象另一個帶有列(id,text)的表C,其中id是索引但非UNIQUE。表C具有以下數據:
1, John!
1, Jack!
Imagine ref as JOIN between A and C:
想象一下ref作為A和C之間的JOIN:
select A.text, C.text where A.ID = C.ID
Here you are a pseudo code which illustrates the processing at server side:
在這里,您是一個偽代碼,它說明了服務器端的處理:
foreach (rowA in A)
{
foreach (rowC in C)
{
if (rowA.id == rowC.id)
{
addToResult(rowA.text, rowC.text);
}
}
}
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 (no break in the loop above). That is because C.ID is NOT UNIQUE.
此JOIN不如前一個快,因為對於表A中掃描的每一行,表C中有幾個可能的行,它們可以滿足JOIN條件(上面的循環中沒有中斷)。那是因為C.ID不是獨一無二的。
I hope that helps...
我希望有幫助......
Cheerz!
Cheerz!