Блог

Главная - Вопросы по программированию - Оптимизация запросов соединения Mysql

Оптимизация запросов соединения Mysql

#mysql #sql #join #query-optimization

#mysql #sql #Присоединиться #оптимизация запросов

Вопрос:

У меня есть две таблицы в mysql:

 Results Table : 1046928 rows.
Nodes Table :  50 rows.

Я соединяю эти две таблицы следующим запросом, и выполнение запроса происходит очень, очень медленно.

 select res.TIndex, res.PNumber, res.Sender, res.Receiver, 
sta.Nickname, rta.Nickname from ((Results res join 
Nodes sta) join Nodes rta) where ((res.sender_h=sta.name) and
(res.receiver_h=rta.name));

Пожалуйста, помогите мне оптимизировать этот запрос. Прямо сейчас, если я хочу извлечь только верхние 5 строк, это занимает около 5-6 МИНУТ. Спасибо.

 CREATE TABLE `nodes1` (
  `NodeID` int(11) NOT NULL,
  `Name` varchar(254) NOT NULL,
  `Nickname` varchar(254) NOT NULL,
  PRIMARY KEY (`NodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Results1` (
  `TIndex` int(11) NOT NULL,
  `PNumber` int(11) NOT NULL,
  `Sender` varchar(254) NOT NULL,
  `Receiver` varchar(254) NOT NULL,
  `PTime` datetime NOT NULL,
  PRIMARY KEY (`TIndex`,`PNumber`),
  KEY `PERIOD_TIME_IDX` (`PTime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Комментарии:

1. опубликуйте вывод SHOW CREATE TABLE для двух таблиц

2. вы просто имеете в виду орфографическую ошибку или есть что-то большее, чего я не понимаю? я не вижу ничего логически неправильного, просто tra vs rta

3. @jon_darkstar: Это. Я не смотрел ближе.

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

5. @M99: Пожалуйста, убедитесь, что проблема все еще существует с вашим урезанным тестовым набором. Хорошо , что вы сделали это, но вы должны убедиться, что вы сделали это правильно. 🙂

Ответ №1:

 SELECT  res.TIndex ,
        res.PNumber ,
        res.Sender ,
        res.Receiver ,
        sta.Nickname ,
        rta.Nickname
FROM    Results AS res
        INNER JOIN Nodes AS sta ON res.sender_h = sta.name
        INNER JOIN Nodes AS rta ON res.receiver_h = rta.NAME
  1. Создайте индекс для результатов
    (отправитель_h)
  2. Создайте индекс для результатов (receiver_h)
  3. Создайте индекс для узлов (имя)

Комментарии:

1. 1 изменение where предложений в условия объединения и добавление этих индексов должно иметь большое значение. я предполагаю, что с полными предложениями joins и where огромное декартово произведение создается промежуточно перед его обрезкой. кто-нибудь может это проверить или это оптимизируется?

2. Насколько я могу судить, нет никакого реального прироста производительности между соединениями WHERE over. Я просто предпочитаю синтаксис соединения, поскольку он делает смысл очень ясным. например, это мои объединения таблиц, а это мои фильтры. Весь прирост производительности достигается за счет добавления индексов. Вы можете повторить свой старый запрос, и он должен выполняться так же быстро.

Ответ №2:

Объединение по name вместо NodeId (первичному ключу) узла выглядит совсем не хорошо.

Возможно, вам следует сохранить NodeId for foreign key sender и receiver в Results таблице вместо name добавления ограничений внешнего ключа — это тоже хорошая идея. Среди прочего, это может привести к автоматической индексации в зависимости от вашей конфигурации

Если это изменение затруднительно, по крайней мере, вы должны обеспечить уникальность node поля name

Если вы измените определение таблиц таким образом, измените свой запрос в соответствии с рекомендацией Джона и добавьте индексы, он должен выполняться намного лучше и быть намного более читаемым / в лучшей форме.