英文:
The Difference Between inner-Join and left-Join When Joining Two Tables with Different Charsets
问题
我有两个MySQL表，字符集不同：utf8mb4和utf8。我尝试使用以下SQL代码执行连接语句：
select count(*)
from n_order as o
left join t_vehicle as v on o.vehicle_license = v.vehicle_license
where o.order_type in (1, 2, 5)
and o.order_status = 2;
连接键vehicle_license在两个表中都具有相同的列类型，即varchar。然而，当执行左连接语句时，性能非常差。执行计划表明，它需要一个Block Nested-Loop而不是利用索引键。
另一方面，当执行内连接语句时，MySQL表现正常。
因此，我想知道在处理具有不同字符集的表时，内连接和左连接之间是否存在任何差异。
英文:
I have two tables in MySQL with different charsets: utf8mb4 and utf8. I tried executing join statements using the following SQL code:
select count(*)
from n_order as o
left join t_vehicle as v on o.vehicle_license = v.vehicle_license
where o.order_type in (1, 2, 5)
and o.order_status = 2;
The joining key, vehicle_license, has the same column type, which is varchar, in both tables. However, when executing the left-join statement, the performance is very poor. The execution plan indicates that it requires a Block Nested-Loop instead of utilizing the index key.
On the other hand, when executing the inner join statement, MySQL performs normally.
Hence, I wonder if there are any differences between inner-join and left-join when dealing with tables having different charsets.
答案1
得分: 1
我听到了由字符集和校对规则引起的速度问题。我也听说了一个COUNT问题，但这是基于其他问题的，尤其是LEFT。
-
如果
vehicle_license在表格之间具有不同的字符集和/或校对规则，那么JOIN（无论是左连接还是内连接）将非常慢。解决这个问题。 -
任何
JOIN中的COUNT（无论哪种类型）首先执行JOIN，然后进行计数。根据目标的不同，这可能是“正确”的或“不正确”的。 -
JOIN构建（至少在逻辑上）一个中间表格，其中包含所有行的有效组合（“有效”基于WHERE和ON条件）。这有助于解释前一项中的“计数膨胀”。 -
INNER JOIN（也称为JOIN）会过滤掉行。LEFT JOIN会包括在“右”表格（在您的示例中为v）中缺失的任何行。这总结了为什么LEFT可以导致更大计数的解释。 -
最后一个提示是，如果
n_order具有INDEX(order_status, order_type)，查询可能会运行得更快。
英文:
I hear a speed problem due to charset and collation. And I hear a COUNT problem, but this is based on other issues, especially LEFT.
-
If
vehicle_licensehas a different character set and/or collation between the tables, theJOIN(whether Left or Inner) will be very slow. Fix that. -
Any
COUNTin aJOIN(either kind) first does theJOIN, then counts. Depending on the goal, this could be 'correct' or 'incorrect'. -
A
JOINbuilds (logically, at least) an intermediate table with all valid combinations of the rows. ("Valid, based onWHEREandON). This helps explain the "count inflation" in the previous item. -
INNER JOIN(akaJOIN) filters out rows.LEFT JOINincludes any rows missing from the 'right' table (vin your example). This wraps up the explanation of whyLEFTcan lead to a bigger count. -
As a final tip, the queries may run faster if
n_orderhasINDEX(order_status, order_type).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论