|
Задавайте вопросы, мы ответим
Вы не зашли.
Добрый день.
Есть таблица с кол-вом записей ~100 000 000 и постоянно увеличивается (назовем ее table1). Есть еще несколько небольших таблиц (типа справочников) (например table2 table3 ...). У каждой таблицы есть primary_key (id). У первой таблицы есть поля, указывающие на id справочников (типа table1.t2_id и table1.t3_id). Все таблицы в течении суток постоянно изменяются (обновляются).
Как можно оптимизировать следующий запрос?
SELECT * FROM table1 LEFT JOIN table2 ON table1.t2_id=table2.id LEFT JOIN table3 ON table1.t3_id=table3.id WHERE table2.name='russia' AND table3.name='moscow' ORDER BY table1.name
Неактивен
а в чем проблема - какие основания для оптимизации?
по полям id_table2, id_table3 .... должны быть индексы.
Типы полей должны быть одинаковыми у полей и id_table2 и table2.id в справочниках (например tinyint если таблички маленькие)
далее смотрим explain - и убеждаемся, что индексы используются, если нет - думаем почему.
если да - дальнейшая оптимизация - наращиванием железа или денормализацией
P.S. по name тоже должен быть индекс
Отредактированно Shopen (03.07.2008 17:56:35)
Неактивен
Спасибо за ответ.
Индексы по необходимым полям есть, типы полей, по которым соединяем - совпадают. Mysql использует эти индексы.
Оптимизация типов данных сделана.
Еслиб небыло предложения WHERE, то все работало бы максимально быстро. Как только в WHERE появляется условие, которое заставляет отбирать не все данные, сервер начинает напрягаться. Я так понимаю что индексы в данном случае к условию WHERE не применить, т.к. условие накладывается уже на объединенные таблицы.
Железа нарощено достаточно хорошо. Под БД используется RAID0 (stripe). 4ГБ оперативы. Но хотелось бы еще большей производительности. Остается думать в сторону оптимизации структуры БД... Но там вроде тоже уже тупик.
Неактивен
Приведите EXPLAIN Вашего запроса. WHERE во многих случаях можно оптимизировать, используя составные ключи
Неактивен
Какие составные ключи можно использовать, если условия в WHERE затрагивает разные таблицы?
Реальный EXPLAIN привести не могу, т.к. запросы строятся в зависимости от потребности пользователя. Вернее в условии WHERE могут использоваться разные условия из разных таблиц.
Неактивен
Для каждого WHERE потребуются разные ключи, чтобы оптимизировать запросы. Порядок обработки таблиц также может зависеть от WHERE
Неактивен
ivsher написал:
Индексы по необходимым полям есть, типы полей, по которым соединяем - совпадают. Mysql использует эти индексы.
Вы не ответитили на самый главный вопрос - "а в чем проблема - какие основания для оптимизации?"
"сервер напрягается" - это бесмысленные слова - сколько это в граммах? 
т.е. если у вас все работает - то просто так оптимизировать скорее всего не надо, если же есть тормоза - то надо:
- выделить запрос, который "тормозит",
- сделать его explain и привести здесь
- сделать SHOW CREATE TABLE для всех таблиц входящих в запрос и привести результаты сюда.
- еще неплохо бы конфигурацию оборудования, если вы ее знаете (на Pentium Pro может много чего притормаживать )
Только тогда можно расчитывать на продуктивность, в противном случае - это пустая трата времени(и вашего и помогающих) и гадание на кофейной гуще.
Отредактированно Shopen (04.07.2008 13:12:34)
Неактивен
Забыл написать самого главного. Вернее ошибся в написании тестового запроса.
Переписываю запрос + его EXPLAIN
SELECT * FROM A LEFT JOIN B ON A.t1=B.t1 AND B.t2=const WHERE B.t1 is not null ORDER BY A.t2
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE B ref PRIMARY,t2 t2 12 const 361 Using where; Using temporary; Using filesort
1 SIMPLE A ref t1 t1 3 B.t1 49448
Выполняется запрос довольно долго. Можно ли его оптимизировать?
Неактивен
Сам запрос у Вас построен так, что он вытаскивает в среднем 15 миллионов записей (300 * 50к) -
потому и тормозит. Вряд ли Вам нужно все 15 миллионов, LIMIT существенно упростит жизнь запросам.
Индексы для такого запроса нужны на B(t2) и A(t1,t2).
Очень неприятно выражение B.t1 is not null. Возможно, нужна денормализация.
Неактивен
Можно убрать B.t1 IS NOT NULL и заменить LEFT JOIN на INNER JOIN
Неактивен
Логично
Неактивен
Подскажите в какой последовательности выполняется запрос следующего вида и есть ли варианты его оптимизации?
Select table1.*, table2.*, table3.*
from table1
left join table2 on (table2.id = table1.id)
left join table3 on (table3.id = table1.id)
where table1.idx in (1,2,3)
order by table1.chartext
limit 0, 5;
Правильно ли я понимаю что сначала выполняется связка left join, потом идет ограничение по условию where, сортировка и после лимит?
Вообще проблема состоит в filesort'е при выборке, т.к. используется сортировка по текстовому полю (varchar), составные индексы использовать никак т.к. версия 5.0.
В настоящем решение состоит в подзапросе из table1 c лимитом и сортировкой и последующяя связка.
Select t.*, table2.*, table3.*
from
(select * from table1 where idx in (1,2,3) order by table1.chartext limit 0, 5) as t
left join table2 on (table2.id = t.id)
left join table3 on (table3.id = t.id)
есть ли еще какие-то варианты решения?
Неактивен
chg написал:
Select t.*, table2.*, table3.*
from
(select * from table1 where idx in (1,2,3) order by table1.chartext limit 0, 5) as t
left join table2 on (table2.id = t.id)
left join table3 on (table3.id = t.id)
есть ли еще какие-то варианты решения?
На данный момент я не смог найти другого решения подобной задачи. Такая оптимизация срабатывает, только если все три таблицы реально большие. На маленьких и даже средних таблицах "неоптимизированный" запрос работает быстрее. А вот когда размер объединенных трех таблиц сильно превышает размер table1, такая оптимизация неплохо помогает - по скорости у меня выполнение с 2-х секунд упал до 0,07 секунды.
Индекс составной использовать бессмысленно - для операции IN индекс вообще работать не будет. Поэтому делайте table1.chartext NOT NULL и делайте индекс только на него - сортировка будет быстрой.
При такой оптимизации возникает проблема с получением общего количества записей (как, если бы было без LIMIT). Если Вам это актуально, готов рассказать об этой проблеме подробнее.
Неактивен
Magz написал:
При такой оптимизации возникает проблема с получением общего количества записей (как, если бы было без LIMIT). Если Вам это актуально, готов рассказать об этой проблеме подробнее.
Расскажите конечно, интересно.
Проблема с LIMIT'ом существует, т.к. записей ~200000, но мне кажется она не решабельна, так и так будет перебор по всему индексу.
Неактивен
Я имел ввиду другую проблему - при организации постраничной выдачи на сайте. Для того, чтобы получить общее количество страниц (для листалки), нужно получить общее количество данных, которое возвращает запрос с теми же условиями, но без LIMIT. Для этого в MySQL есть замечательный механизм
Еще немного улучшить картину можно, если все три таблицы объединяются left join и в table2 и table3 нет повторяющихся ссылок на table1. Тогда можно спокойно делать только Неактивен
Спасибо за разъяснения, как-то я сильно и не задумывался насчет листалки, а ведь тоже свой кусок "жрёт", будем исправляться
Неактивен
Magz спсасибо помогло.
Неактивен
