SQL-запрос объединяет несколько таблиц — слишком медленный (8 таблиц)

я пытаюсь объединить 8 таблиц в одну, чтобы создать индекс, используемый другим приложением, мой запрос выглядит так: (мой навык mysql очень любительский)

SELECT t1_id, t2_name, t3_name, t4_name, t5_name, 
       t6_name, t7_name, t8_name, t9_name 
FROM t1 
  LEFT JOIN t2 ON (t1_id = t2_id) 
  LEFT JOIN t3 ON (t3_id = t1_id) 
  LEFT JOIN t4 ON (t4_id = t1_id)
  LEFT JOIN t5 ON (t5_id = t1_id)
  LEFT JOIN t6 ON (t6_id = t1_id) 
  LEFT JOIN t7 ON (t7_id = t1_id)
  LEFT JOIN t8 ON (t8_id = t1_id)
  LEFT JOIN t9 ON (t9_id = t1_id)

я даже не вижу результатов запроса при его выполнении, есть ли способы ускорить его? :) приветствуется любая помощь, но лучше всего один запрос (вне правил применения)

заранее спасибо


sql performance join
person Community    schedule 30.04.2009    source источник

Ответы (8)


arrow_upward
57
arrow_downward

У меня была аналогичная проблема с несколькими таблицами поиска, присоединяющимися к большой таблице со всеми проиндексированными полями идентификатора. Чтобы отслеживать влияние соединений на время выполнения запроса, я запускал свой запрос несколько раз (ограничившись первыми 100 строками), каждый раз добавляя соединение в дополнительную таблицу. После объединения 12 таблиц существенного изменения времени выполнения запросов не произошло. К тому времени, когда я присоединился к 13-му столу, время выполнения подскочило до 1 секунды; 14-й стол 4 секунды, 15-й стол 20 секунд, 16-й стол 90 секунд.

Предложение Keijro использовать коррелированные подзапросы вместо соединений, например.

SELECT t1_id, 
        (select t2_name from t2 where t1_id = t2_id), 
        (select t3_name from t3 where t1_id = t3_id), 
        (select t4_name from t4 where t1_id = t4_id), 
        (select t5_name from t5 where t1_id = t5_id), 
        (select t6_name from t6 where t1_id = t6_id), 
        (select t7_name from t7 where t1_id = t7_id), 
        (select t8_name from t8 where t1_id = t8_id), 
        (select t9_name from t9 where t1_id = t9_id)  FROM t1

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

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

person Community    schedule 16.06.2009
comment
Я бы дал вам гораздо больше, чем +1 за это, если бы мог. У меня была точно такая же проблема, и это решение работает отлично. Исходя из фона MSSQL/Oracle, где воспринимается мудрость в том, что коррелированных подзапросов следует избегать везде, где это возможно, это очень нелогично, но это работает. При чтении кажется, что это может быть связано с тем, что MySQL обрабатывает соединения как вложенные циклы. - person Cruachan; 25.05.2012
comment
Я исхожу из 15 столов. Даже с индексами запрос по-прежнему занимает у меня 1 или 2 секунды из предела 50 результатов. Попробую переписать большой метод по вашим рекомендациям. Спасибо ! - person Alucard; 12.05.2014

arrow_upward
29
arrow_downward

В зависимости от того, сколько данных содержится в таблицах, вам может потребоваться разместить индексы на столбцах, которые объединяются. Часто низкая скорость запросов сводится к отсутствию индекса в нужном месте.

Также:

ЛЕВЫЕ СОЕДИНЕНИЯ медленнее, чем ВНУТРЕННИЕ СОЕДИНЕНИЯ (хотя это зависит от того, что именно вы делаете) - можете ли вы выполнить то, что ищете, с помощью внутренних соединений?

person Nathan Ridley    schedule 30.04.2009
comment
Было бы неплохо объяснить разницу между внутренним и левым соединениями: внутренние соединения не включают строки, в которых данные не найдены, в отличие от левых соединений (или внешних соединений). то есть, если, скажем, в t8 отсутствует идентификатор, соответствующий t1_id, то эта строка вообще не будет включена (даже если есть другие таблицы с этим идентификатором) - person Jimmy Stenke; 30.04.2009
comment
Согласен с обоими пунктами. Используйте INNER JOINS и убедитесь, что в полях, к которым вы присоединяетесь, есть индекс. - person Jeff Davis; 16.06.2009
comment
НО см. ответ об использовании коррелированных подзапросов ниже. У меня была таблица соединений из 15 (все внутренние соединения для декодирования первичных индексов), которая работала в 1000 раз быстрее, если бы 10 соединений, которые могли быть помещены в коррелированные подзапросы, а не соединения - person Cruachan; 25.05.2012

arrow_upward
5
arrow_downward

Было бы немного полезно, если бы вы могли опубликовать план объяснения запроса.

Но, во-первых, у вас есть индексы по всем полям, используемым в соединении? что-то вроде CREATE INDEX ix_t2_id on t2 (t2_id, t2_name);

Вместо соединений вы можете сделать что-то вроде

SELECT t1_id, 
    (select t2_name from t2 where t1_id = t2_id), 
    (select t3_name from t3 where t1_id = t3_id), 
    (select t4_name from t4 where t1_id = t4_id), 
    (select t5_name from t5 where t1_id = t5_id), 
    (select t6_name from t6 where t1_id = t6_id), 
    (select t7_name from t7 where t1_id = t7_id), 
    (select t8_name from t8 where t1_id = t8_id), 
    (select t9_name from t9 where t1_id = t9_id) 
FROM t1

Но с хорошим планировщиком запросов это не должно отличаться от соединений.

person Jimmy Stenke    schedule 30.04.2009
comment
спасибо за ваш ответ, вот план объяснения: 1 SIMPLE r ALL NULL NULL NULL NULL 4977 1 SIMPLE m ref выпуск выпуска 4 main.r.r_id 2 1 SIMPLE t ref выпуск выпуска 4 main.r.r_id 2 1 SIMPLE s ALL NULL NULL NULL NULL 4889 1 SIMPLE mu ref release release 4 main.r.r_id 2 1 SIMPLE n ALL NULL NULL NULL NULL 6 1 SIMPLE q ALL NULL NULL NULL NULL 13 1 SIMPLE o ref release release 4 main.r.r_id 2 1 SIMPLE g ref выпуск выпуска 4 main.r.r_id 2 - person ; 30.04.2009
comment
Я не уверен, что встроенные выборки - хороший путь к производительности SQL. - person u07ch; 30.04.2009
comment
Некоторые книги называют их коррелированными подзапросами. Они выбираются внутри других операторов select перед From; они оцениваются построчно, поэтому для больших наборов данных они очень неэффективны. - person u07ch; 30.04.2009
comment
Правда, в основном их следует избегать, но у меня было несколько случаев, когда они вносили большой вклад в производительность, поэтому в некоторых случаях их следует учитывать. - person Jimmy Stenke; 30.04.2009

arrow_upward
5
arrow_downward

О каком количестве данных мы говорим? Возможно, у вас много данных, и поскольку предложение where выполняется в конце процесса запроса, вы объединяете огромные объемы данных перед их фильтрацией.

В этом случае лучше отфильтровать данные как можно скорее, поэтому, если вы можете ограничить данные из T1 в первом внутреннем выборе, все остальные соединения будут присоединяться к более ограниченному набору данных.

Select <your fields> from
(
Select * from t1 where t1_id = t1_value
) t1

Inner join t2
on t1.ID = t2.ID
...

если это не масса данных; проверьте правильность ваших индексов, затем проверьте тип сервера; фрагментация индекса; дисковые очереди и т.д.

person u07ch    schedule 30.04.2009
comment
Есть ли вообще значение t1_value для проверки? Он / она не сказал об этом в вопросе. - person Louis; 30.04.2009

arrow_upward
1
arrow_downward

Если вам нужны все строки t1, и вы оставили соединение с первичным ключом (я думаю, это также кластеризованный индекс) других таблиц, нет никакого способа улучшить скорость запроса.

Чтобы улучшить производительность, вам нужно либо уменьшить набор результатов, либо выполнить неприятный трюк (например, сделать денормализованную копию данных).

person Dries Van Hansewijck    schedule 30.04.2009

arrow_upward
1
arrow_downward

Из вашего плана запроса я могу сделать вывод, что таблицы, называемые s, n и q, не имеют индекса в поле, к которому они присоединяются.

Поскольку в этих таблицах много строк (около 400,000 строк в их декартовом произведении) и единственный способ MySQL сделать JOIN с помощью NESTED LOOPS, это действительно займет вечность.

Создайте индекс для этих таблиц или определите объединенное поле как PRIMARY KEY.

person Quassnoi    schedule 03.05.2009

arrow_upward
0
arrow_downward

Как я вижу, таблица t1 — это та, которая объединяется со всеми таблицами, вместо того, чтобы помещать их в один запрос с таким количеством соединений, вы можете попробовать объединение разных запросов, что-то вроде этого.

SELECT  t1_id, t2_name 
FROM    t1 LEFT JOIN t2 ON (t1_id = t2_id)
union 
SELECT  t1_id, t3_name 
FROM    t1 LEFT JOIN t3 ON (t1_id = t3_id)

однако в этом случае результат, который вы получите, будет иметь не 8 столбцов, а только 1 столбец. не уверен, что этот вариант доступен с вами.

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

person Vikram    schedule 30.04.2009

arrow_upward
-1
arrow_downward

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

person Doug L.    schedule 16.06.2009