LINUX.ORG.RU

MySQL: Странное поведение left join

 


0

1

Имеются две таблицы:

CREATE TABLE `messages` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TOPIC` int(11) NOT NULL DEFAULT '0',
  `MEMBER` int(11) NOT NULL DEFAULT '0',
  `subject` tinytext,
  `poster` tinytext NOT NULL,
  `Email` tinytext,
  `Time` bigint(20) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`ID`),
  KEY `TOPIC` (`TOPIC`),
  KEY `MEMBER` (`MEMBER`),
  KEY `Time` (`Time`)
) ENGINE=InnoDB AUTO_INCREMENT=1351047 DEFAULT CHARSET=cp1251

и

 CREATE TABLE `lenta` (
  `ID` int(11) NOT NULL,
  `sticky` tinyint(4) NOT NULL DEFAULT '0',
  `subject` tinytext NOT NULL,
  `annotation` tinytext,
  `annotatedBy` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `sticky` (`sticky`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251

В этой таблице пока всего одна запись.

Запрос

EXPLAIN SELECT STRAIGHT_JOIN  m.ID, annotatedBy FROM messages m LEFT JOIN lenta l ON m.ID = l.ID ORDER BY m.ID limit 5;

приводит к такому результату

+----+-------------+-------+-------+---------------+----------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                                              |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+----------------------------------------------------+
|  1 | SIMPLE      | m     | index | NULL          | TOPIC | 4       | NULL | 924710 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | l     | ALL   | PRIMARY       | NULL     | NULL    | NULL |      1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+----------------------------------------------------+

Такой запрос выполняется неэффективно и долго.

Однако если убрать поле из запроса из второй таблицы,

EXPLAIN SELECT STRAIGHT_JOIN  m.ID FROM messages m left JOIN lenta l ON m.ID = l.ID ORDER BY m.ID LIMIT 5;

то

+----+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | m     | index  | NULL          | PRIMARY | 4       | NULL                   |    5 | Using index |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY       | PRIMARY | 4       | mydatabase.m.ID |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+

Или

EXPLAIN SELECT STRAIGHT_JOIN  m.ID, l.ID FROM messages m left JOIN lenta l ON m.ID = l.ID ORDER BY m.ID LIMIT 5;

также выполняется нормально.

Т.е. если из второй таблицы запрашивать что угодно кроме полей, имеющими индекс, приводит к неэффективному выполнению запроса.

Кстати, первый вариант запроса, но без ORDER BY выполняется хоть и неэффективно согласно explain, но вроде как быстро без explain:

EXPLAIN SELECT STRAIGHT_JOIN  m.ID, l.annotatedBy FROM messages m LEFT JOIN lenta l ON m.ID = l.ID LIMIT 25;
+----+-------------+-------+-------+---------------+----------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                                              |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+----------------------------------------------------+
|  1 | SIMPLE      | m     | index | NULL          | TOPIC | 4       | NULL | 924710 | Using index                                        |
|  1 | SIMPLE      | l     | ALL   | PRIMARY       | NULL     | NULL    | NULL |      1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+----------------------------------------------------+

Собственно, почему так? Почему запрос столбцов из таблицы справа приводит к неэффективному выполнению запроса?

mysql особо не знаю, но

Кстати, первый вариант запроса, но без ORDER BY выполняется хоть и неэффективно согласно explain, но вроде как быстро без explain:

это бред, limit без order by - это бред.

попробуй выбрать както так

select * from 
(select a,b,c from one order by a limit 5) omg
left join two wtf on omg.a = wtf.a

drsm ★★ ()

Естественно он выполняется быстро, т.к. тебе не надо выбирать поля, для которых не соответствует id. А в первом случае надо выбирать всё, для чего нет id. И зачем тебе вообще left join?

crutch_master ★★★★★ ()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Естественно он выполняется быстро, т.к. тебе не надо выбирать поля, для которых не соответствует id. А в первом случае надо выбирать всё, для чего нет id.

Бред. Ты можешь сослаться на документацию, где такое поведение описывается? Мой запрос говорит выбрать пять последних полей из первой таблицы и присоединить сбоку вторую таблицу в соответствии с их первичными ключами, а там где таких ключей в правой таблице нет поля заполнить значениями NULL. По идее такой запрос должен выполняться почти так же быстро как просто select * from table order by ID desc limit 5, т.к. сортировка и условие для join основано на первичных ключах.

Вот в этой же БД есть такая таблица и join первой таблицы с этой на таких же условиях работает как должно:

CREATE TABLE `topics` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `BOARD` int(11) NOT NULL DEFAULT '0',
  `locked` tinyint(4) NOT NULL DEFAULT '0',
  `isSticky` tinyint(4) NOT NULL DEFAULT '0',
  `notifies` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=43428 DEFAULT CHARSET=cp1251
EXPLAIN SELECT m.ID, t.BOARD, t.notifies FROM messages m LEFT JOIN topics t ON m.TOPIC = t.TOPIC ORDER BY m.ID DESC LIMIT 5;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                      | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+------+-------+
|  1 | SIMPLE      | m     | index  | NULL          | PRIMARY | 4       | NULL                     |    5 | NULL  |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 4       | mydatabase.m.TOPIC       |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+------+-------+
pariyenodu ()
Ответ на: комментарий от drsm

это бред, limit без order by - это бред.

Просто для проверки как оно без.

попробуй выбрать както так

Да, так оно работает быстро, спасибо а подсказку. Смущает только количество rows в последней строке в таблице explain:

EXPLAIN SELECT date, Poster, m.ID, IFNULL(l.subject, m.subject) as Subj from ( select ID, Poster, FROM_UNIXTIME(Time) as date, subject from messages ORDER BY ID DESC LIMIT 25) m LEFT JOIN lenta l ON m.ID = l.ID;
+----+-------------+------------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                              |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |      5 | NULL                                               |
|  1 | PRIMARY     | l          | ALL   | PRIMARY       | NULL    | NULL    | NULL |      1 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | messages   | index | NULL          | PRIMARY | 4       | NULL | 924710 | NULL                                               |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+

Но интересно почему с этой таблицей не работает мой вариант запроса, однако в примере выше JOIN аналогичной таблицы с аналогичными условиями работает нормально, как работало всегда и с другими таблицами.

pariyenodu ()

У тебя, кстати, неправильная схема. Ты джоинишь по lenta.id = messages.id, что абсолютная ахинея. У таблицы messages надо сделать поле lenta_id с foreign key к lenta.id, и тогда джоин будет

LEFT JOIN lenta ON messages.lenta_id = lenta.id

K slovu, lenta po-anglizscki buit «feed».

nikolnik ★★★ ()

БД строит план выполнения запроса не только по наличию/отсутвию индексов - но еще и на основе статистики по таблицам. В данном случае mysql видит что в таблице записей мало (всего одна) и смысла читать индекс а затем сами данные нету, дешевле будет просто загрузить всю таблицу в память (буфер) и уже из этого кеша вытаскивать данные чем каждый раз сканировать индексный файл, а затем по найденым индексам считывать данные из файла данных.

Попробуйте наинсертит больше записей в таблицу и сделать рекалькуляцию статистики, скорее всего mysql построит другой план выполнения этого-же запроса.

zaz ★★★★ ()
Ответ на: комментарий от nikolnik

У таблицы messages надо сделать поле lenta_id с foreign key к lenta.id, и тогда джоин будет

Не обязательно, и без foreign key всегда норм джоинилось по условию вида table1.pk = table2.pk, где pk - primary key. Да и просто по индексам всегда быстро джоинилось.

pariyenodu ()
Ответ на: комментарий от zaz

БД строит план выполнения запроса не только по наличию/отсутвию индексов - но еще и на основе статистики по таблицам. В данном случае mysql видит что в таблице записей мало (всего одна) и смысла читать индекс а затем сами данные нету, дешевле будет просто загрузить всю таблицу в память (буфер) и уже из этого кеша вытаскивать данные чем каждый раз сканировать индексный файл, а затем по найденым индексам считывать данные из файла данных.

Странная конечно логика, почему бы не просто последовательно сперва не сделать выборку из первой таблицы в соответствии с условиями (в данном случае order by ID desc limit 5) и к ней уже приклеить сбоку записи из второй таблицы, какие есть, а остальное заполнить нулями. Ну и STRAIGHT_JOIN казалось бы должен форсировать такую последовательность.

Попробуйте наинсертит больше записей в таблицу и сделать рекалькуляцию статистики, скорее всего mysql построит другой план выполнения этого-же запроса.

А на продакшене как быть? Там актуальные записи не появятся быстро. Это новая фича планируется типа.

pariyenodu ()
Ответ на: комментарий от zaz

Алсо смотри, первый EXPLAIN в первом посте показывает что используется неверный ключ TOPIC, хотя по идее должен быть PRIMARY. Это чем можно объяснить?

pariyenodu ()
Ответ на: комментарий от pariyenodu

Странная конечно логика, почему бы не просто последовательно сперва не сделать выборку из первой таблицы в соответствии с условиями (в данном случае order by ID desc limit 5)

Ну оптимизатор запросов он не всесильный, и явно не умнее человека. Поэтому чаще бывает более эфективно сложные запросы (теже JOIN) разделять на 2 (или болие), например в вашем случае есть смысл первым запросом выбрать нужные id из messages вторым запросом выбрать нужные записи из lenta (WHERE ID IN(...))

А на продакшене как быть? Там актуальные записи не появятся быстро. Это новая фича планируется типа.

MySQL сам пересщитает статистику со временем (в зависимости от настроек), либо явно можно вызвать «ANALYZE TABLE»

zaz ★★★★ ()
Ответ на: комментарий от pariyenodu

Только ты получаешь абсолютную ахинею с точки зрения нормализации. «Lenta» имеет много «messages», «message» принадлежит «lenta». Ты банально не сможешь из-за primary key constraint вставить в базу данных вторую запись в messages. К тому же, из-за денормализации ты получишь ситуацию, когда ты можешь удалить запись из lenta, а все сообщения в ней все еще будут существовать, а все потому, что ты не понимаешь, чем pk от fk отличается.

nikolnik ★★★ ()
Ответ на: комментарий от pariyenodu

После ключевого слово ON, может стоять любое выражение, возвращающее логическое значение. Может хоть написать LEFT JOIN huenta ON TRUE = TRUE

nikolnik ★★★ ()
Ответ на: комментарий от pariyenodu

Алсо смотри, первый EXPLAIN в первом посте показывает что используется неверный ключ TOPIC, хотя по идее должен быть PRIMARY. Это чем можно объяснить?

Это можно объяснить тем что планировщик в MySQL еще тот, и на него особо пологатся не стоит. Также можно попробовать дернуть OPTIMIZE / ANALYZE - иногда помогает ему открыть глаза. Но всеже надежней распелить на два запроса и не думать что вот оно сейчас заработало, а через пол года (или после апдейта базы) опять сломалось ...

zaz ★★★★ ()
Ответ на: комментарий от zaz

Но всеже надежней распелить на два запроса и не думать что вот оно сейчас заработало, а через пол года (или после апдейта базы) опять сломалось

Ок, буду тога использовать такой запрос, спасибо. MySQL: Странное поведение left join (комментарий)

pariyenodu ()
Ответ на: комментарий от nikolnik

Только ты получаешь абсолютную ахинею с точки зрения нормализации. «Lenta» имеет много «messages», «message» принадлежит «lenta».

Наоборот. В «lenta» будет в разы меньше записей. И скорее «lenta» наоборот принадлежит «messages». Хотя там в проекте несколько разных запросов. Где-то lenta присоединяется к messages, где-то наоборот lenta left joim messages.

Ты банально не сможешь из-за primary key constraint вставить в базу данных вторую запись в messages.

К тому же, из-за денормализации ты получишь ситуацию, когда ты можешь удалить запись из lenta, а все сообщения в ней все еще будут существовать, а все потому, что ты не понимаешь, чем pk от fk отличается.

Это я всё знаю, в джанговском ORM эта фигня во всю используется. Но в данном случае это древний проект 15 летней давности, писал не я, просто добавляю у себя новую фичу. Там с десяток разных таблиц и fk нигде не используется.

pariyenodu ()
Ответ на: комментарий от pariyenodu

Бред

А, ну да. Это для первой таблицы всё выбирается что можно.

crutch_master ★★★★★ ()
Ответ на: комментарий от pariyenodu

древний проект 15 летней давности

Блджад, шесть нормальных форм изобрели еще 30 лет того, как твой проект вылупился

Там с десяток разных таблиц и fk нигде не используется.

Так ты бы сразу сказал, что вы все слабоумные. Я бы даже в тред не заходил.

nikolnik ★★★ ()
Ответ на: комментарий от pariyenodu

Но интересно почему с этой таблицей не работает мой вариант запроса, однако в примере выше JOIN аналогичной таблицы с аналогичными условиями работает нормально, как работало всегда и с другими таблицами.

не знаю, возможно мускуль не умеет limit n order by pk desc оптимизировать в принципе, те проталкивать его под джоин условие.

такие запросы, когда нужно получить голову таблицы (а она 90%, что будет в buffer cache) и потом ее сджойнить с чем-то и чем-то, лучше выписывать явно, даже если изначально на тестовых данных план хороший в обычном варианте. ато в продакшене возможны флуктуации потом из-за кривой статистики/распределения данных.

drsm ★★ ()

планировщик мускуля заточен под стандартные схемы join-ов, как только начинают хотеть от него странного, он начинает выдавать чудеса, причём гуляя от версии к версии. решение простое - перестать он него требовать странного и перейти на стандартные схемы. ИМНО - lj по m.ID = l.ID - странное. нормальное в ленту запихнуть message_id, если связь 1 к 1, то проуникалить индекс. план запроса сразу станет нормальным.

vtVitus ★★★★★ ()
Последнее исправление: vtVitus (всего исправлений: 1)
Ответ на: комментарий от pariyenodu

Мой запрос говорит выбрать пять последних полей из первой таблицы и присоединить сбоку вторую таблицу в соответствии с их первичными ключами, а там где таких ключей в правой таблице нет поля заполнить значениями NULL.

Садись, два! Твой запрос говорит: выбери все(!) записи из левой таблицы, прицепи согласно условию из правой(либо null), затем отсортировав в порядке убывания отдай 5.

Minona ★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.