为什么mySQL查询，左连接“相当多”？比我的内部连接更快

Question

我已经研究过这一点，但我仍然无法解释原因：

SELECT cl.`cl_boolean`, l.`l_name`
FROM `card_legality` cl
INNER JOIN `legality` l ON l.`legality_id` = cl.`legality_id`
WHERE cl.`card_id` = 23155

明显慢于：

SELECT cl.`cl_boolean`, l.`l_name`
FROM `card_legality` cl
LEFT JOIN `legality` l ON l.`legality_id` = cl.`legality_id`
WHERE cl.`card_id` = 23155

115ms Vs 478ms。它们都使用 InnoDB 并且定义了关系。 “card_legality”表包含大约 200k 行，而“legality”表包含 11 行。以下是每个的结构：

CREATE TABLE `card_legality` (
  `card_id` varchar(8) NOT NULL DEFAULT '',
  `legality_id` int(3) NOT NULL,
  `cl_boolean` tinyint(1) NOT NULL,
  PRIMARY KEY (`card_id`,`legality_id`),
  KEY `legality_id` (`legality_id`),
  CONSTRAINT `card_legality_ibfk_2` FOREIGN KEY (`legality_id`) REFERENCES `legality` (`legality_id`),
  CONSTRAINT `card_legality_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `card` (`card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

并且：

CREATE TABLE `legality` (
  `legality_id` int(3) NOT NULL AUTO_INCREMENT,
  `l_name` varchar(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`legality_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

我可以简单地使用 LEFT-JOIN，但它似乎不太正确......有什么想法吗？

更新： 根据要求，我已经包含了每个的解释结果。我以前运行过它，但我并不假装对它有透彻的理解..

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  cl  ALL PRIMARY NULL    NULL    NULL    199747  Using where
1   SIMPLE  l   eq_ref  PRIMARY PRIMARY 4   hexproof.co.uk.cl.legality_id   1   

并且，内部连接：

id  select_type table   type    possible_keys   key key_len         ref                         rows    Extra
1   SIMPLE  l   ALL PRIMARY NULL    NULL    NULL    11  
1   SIMPLE  cl  ref PRIMARY,legality_id legality_id 4   hexproof.co.uk.l.legality_id    33799   Using where

Answer 1

这是因为card_id上的varchar。 MySQL 无法使用 card_id 上的索引作为 card_id，如此处所述 mysql 类型转换。重要的部分是

对于字符串列与数字的比较，MySQL 不能使用
列上的索引可以快速查找值。如果 str_col 是
索引字符串列，执行时不能使用索引
在以下语句中查找：

从 tbl_name WHERE str_col=1 中选择*；

这样做的原因是有许多不同的字符串可能
转换为值 1，例如 '1'、'1' 或 '1a'。

如果您将查询更改为

SELECT cl.`cl_boolean`, l.`l_name`
FROM `card_legality` cl
INNER JOIN `legality` l ON l.`legality_id` = cl.`legality_id`
WHERE cl.`card_id` = '23155'

and

SELECT cl.`cl_boolean`, l.`l_name`
FROM `card_legality` cl
LEFT JOIN `legality` l ON l.`legality_id` = cl.`legality_id`
WHERE cl.`card_id` = '23155'

您应该会看到速度的巨大改进，并且还会看到不同的 EXPLAIN。

这是一个类似（但更简单）的测试来证明这一点：

> desc id_test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | varchar(8) | NO   | PRI | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.17 sec)

> select * from id_test;
+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |
| 8  |
| 9  |
+----+
9 rows in set (0.00 sec)

> explain select * from id_test where id = 1;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | id_test | index | PRIMARY       | PRIMARY | 10      | NULL |    9 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)


> explain select * from id_test where id = '1';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | id_test | const | PRIMARY       | PRIMARY | 10      | const |    1 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

在第一种情况下，有 Using where;使用索引，第二个是使用索引。 ref 也可以是 NULL 或 CONST。不用说，第二个更好。

Answer 2

L2G 对此进行了相当多的总结，尽管我怀疑这可能是因为 card_id 使用了 varchar 类型。

实际上，我打印了此信息页面以进行基准测试/分析快速。这是一个快速的穷人分析技术：

Time a SQL on MySQL
Enable Profiling
mysql> SET PROFILING = 1
...
RUN your SQLs
...
mysql> SHOW PROFILES;

+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00014600 | SELECT DATABASE()     |
|        2 | 0.00024250 | select user from user |
+----------+------------+-----------------------+
mysql> SHOW PROFILE for QUERY 2;

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000034 |
| checking query cache for query | 0.000033 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000011 |
| init                           | 0.000013 |
| optimizing                     | 0.000004 |
| executing                      | 0.000011 |
| end                            | 0.000004 |
| query end                      | 0.000002 |
| freeing items                  | 0.000026 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

祝你好运，哦，请发布你的发现！

Answer 3

我会尝试对这两个查询进行 EXPLAIN 。只需在每个 SELECT 前面添加 EXPLAIN 并运行它们即可。它提供了有关 mySQL 如何优化和执行查询的非常有用的信息。

Answer 4

我非常确定 MySql 对左连接有更好的优化 - 目前没有证据支持这一点。

预计到达时间：经过一轮快速的侦察，我找不到任何具体的东西来支持我的观点，所以......