mysql 中eq,MySQL解釋了eq_ref和ref類型的含義

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！