Почему mySQL-запрос, левое соединение "значительно" быстрее, чем мое внутреннее соединение

Ответ 1

Это из-за varchar на card_id. MySQL не может использовать индекс на card_id как card_id, как описано здесь преобразование типа mysql. Важная часть

Для сравнения столбца строки с номером MySQL не может использовать индекс в столбце, чтобы быстро найти значение. Если str_col - это индексированный строковый столбец, индекс не может использоваться при выполнении поиск в следующем выражении:

SELECT * FROM tbl_name WHERE str_col = 1;

Причина этого в том, что существует много разных строк, которые могут преобразуется в значение 1, например, "1", "1" или "1а".

Если вы измените свои запросы на

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'

Вы должны увидеть огромное улучшение скорости, а также увидеть другое 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; Using index, а второй - Using index. Также ref является либо NULL, либо CONST. Излишне говорить, что второй лучше.

Ответ 2

Я попробую EXPLAIN по обоим этим запросам. Просто префикс каждого SELECT с помощью EXPLAIN и запустите их. Это дает действительно полезную информацию о том, как mySQL оптимизирует и выполняет запросы.

Ответ 3

L2G довольно сильно подвел итог, хотя я подозреваю, что это может быть из-за типа varchar, используемого для card_id.

Я фактически распечатал эту информативную страницу для быстрого тестирования/профилирования быстродействия. Вот быстрый метод профилирования бедных:

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 |
+--------------------------------+----------+

Удачи, о, и, пожалуйста, опубликуйте свои выводы!

Ответ 4

Я уверен, что MySql имеет лучшую оптимизацию для Left Joins - никаких доказательств, подтверждающих это на данный момент.

ETA: быстрый скаутский раунд, и я не могу найти ничего конкретного, чтобы поддержать мой взгляд, поэтому.....