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

Я исследовал это, но я все еще не могу объяснить, почему:

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

115 мс против 478 мс. Они оба используют InnoDB и определены отношения. "Card_legality" содержит приблизительно 200 тыс. Строк, а таблица "законность" содержит 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, но это не кажется правильным... любые мысли, пожалуйста?

UPDATE: По просьбе я включил результаты объяснения для каждого. Раньше я запускал его, но я не претендую на полное понимание этого.

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

Ответ 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: быстрый скаутский раунд, и я не могу найти ничего конкретного, чтобы поддержать мой взгляд, поэтому.....