Как сделать запрос ORDER BY в JOIN быстрее? Ничего я не пробовал, работал

Все столбцы в запросе индексируются, включая table1.col6, используемые в ORDER BY.

Ниже приведены результаты EXPLAIN EXTENDED БЕЗ ORDER BY:

Что странно, что я использую ORDER BY DESC в нескольких других запросах на этом сайте, и он не замедляет его нигде почти так же, как с этим конкретным запросом. Для этого запроса есть что-то конкретное, что приводит к значительному замедлению его с помощью ORDER BY.

Я также сделал ANALYZE TABLE на всех трех таблицах, и все они сообщили OK. Затем я заменил каждый LIKE в запросе на =, и он фактически сделал запрос БЕЗ ORDER BY перейти от 0,2 секунды к 3 секунды. Другими словами, заменяя LIKE на =, исходный запрос занимает столько же времени, сколько добавление ORDER BY! Как это возможно, учитывая, что LIKE работает больше, чем =? Возможно, в этом заключается ключ к тому, почему ORDER BY занимает так много времени?

1) Вместо SELECT table1.*, table2.* я попробовал только SELECT table1.col1, и ему все равно потребовалось 3 секунды.

2) Я попытался добавить составной индекс на col1, col2, col3 и col6 в Table1, но это не улучшило скорость выполнения.

3) Я попробовал это решение сделать запрос в качестве подзапроса и затем обернуть ORDER BY вне его на но это не улучшило скорость выполнения.

4) Я попробовал следующую версию запроса, но ничего не улучшил и фактически сделал запрос более 3 секунды БЕЗ даже добавленного к нему ORDER BY (возможно, это обеспечивает еще один ключ):

Как я могу получить этот упрямый запрос для быстрого запуска с помощью ORDER BY? Или это просто невозможно?

Ниже представлен мой файл конфигурации MySQL. Среди прочего, обратите внимание, как sort-buffer-size установлен на 1M. Согласно this, он не должен быть установлен выше 256K, или он может фактически замедлить работу с помощью "37x". Может ли быть частью проблемы?

По другому вопросу, вот РЕЗУЛЬТАТЫ EXPLAIN EXTENDED ПОСЛЕДНИЕ ВОПРОСЫ ИЗ ИВАНА:

По другому вопросу, вот что-то ОЧЕНЬ странное. Следующая версия запроса WITH ORDER BY завершается всего за 0,2 секунды:

В основном, эта версия запроса содержит таблицы FROM Table3 AS T3 и JOIN таблицы 1 и 2, тогда как исходный запрос имеет FROM Table1 AS T1 и JOIN таблицы 2 и 3.

Обратите внимание, как этот запрос фактически выполняет ОБА filesort и temporary вместо простого filesort в исходных и новых запросах от Ivan. Как это может быть на 10 раз быстрее?

Даже незнакомец, переключая порядок JOIN, похоже, не улучшает ни первоначальный запрос, ни более новые запросы от Ивана. Почему это?

Ответы

Ответ 1

После долгих проб и ошибок я наконец нашел решение своего вопроса.

Если мы поместим все предложение WHERE - , кроме, которое вычисляет радиус - вне исходного запроса, тогда мы получаем очень быстрый запрос, который делает не используйте temporary как изменение порядка JOIN:

SELECT * FROM
{
    SELECT
        col1, col2, col3, col4, col5, col6
    FROM 
        Table1 AS table1 
    LEFT JOIN 
        Table2 AS table2 
    USING 
        (col1)
    LEFT JOIN 
        Table3 as table3 
    USING 
        (col1) 
    WHERE 
        3963.191 * 
        ACOS(
        (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180)) 
        +
        (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
        ) <= 10 
) AS sub
WHERE
    col1 != '1' 
AND 
    col2 LIKE 'A' 
AND 
    (col3 LIKE 'X' OR col3 LIKE 'X-Y') 
AND 
    (col4 = 'Y' OR col5 = 'Y') 
ORDER BY 
    col6 DESC

По существу, этот запрос сначала получает результаты JOIN всех трех таблиц на основе радиуса и только затем применяет остальные фильтры для получения нужных нам результатов. Эта версия запроса возвращает точные те же результаты, что и мой исходный запрос, но выполняется только в 0,2 секунды против 3 секунды для моего оригинала запрос.

Вот EXPLAIN EXTENDED для него:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    43  100 Using where; Using filesort
2   DERIVED T3  ALL PRIMARY NULL    NULL    NULL    143153  100 Using where
2   DERIVED users   eq_ref  PRIMARY,col1,idx01  PRIMARY 4   T3.col1 1   100 
2   DERIVED userProfile eq_ref  PRIMARY,CompositeIndex1 PRIMARY 4   users.col1  1   100

Я хотел поблагодарить Иван Буттинони за отличную работу над этим. Он нашел несколько умных способов сделать этот запрос еще быстрее.

Мораль истории: это не просто предложение ORDER BY, которое может быть сделано быстрее помещая его вне основного запроса, вы также можете получить более быстрый запрос, поместив часть предложения WHERE вне его также в таких ситуациях, как этот.

Ответ 2

Ну, Я предлагаю вам несколько повторений запроса:

введите, где условия не связаны, см. второй запрос:

И (T1.col3 LIKE 'X' ИЛИ T1.col3 LIKE 'X-Y')
избегать ИЛИ использовать IN
избегать как использовать =

И T1.col3 IN ('X', 'X-Y')
избегать вычислений, в которых

создайте несколько новых столбцов для хранения:

SIN(PI() * T3.latitude / 180)
COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
COS(PI() * T3.latitude / 180)

предварительно оценить

SIN (PI() * $usersLatitude/180) COS (PI() * $usersLatitude/180)
если все эти "трюки" не могут избежать принудительного сортировки файлов, индексы

подсказка индекса запросов mysql

ДАЛЬНЕЙШЕЕ ДОБАВЛЕНИЕ

чтобы удалить:

( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )

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

alter table table2 add static1 bit default 0;
alter table add index idx_static1(static1);
update table2 t2 set static1=1 where ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' );

alter table table3 add static2 float(18,16) default 0;
update table3 set static2=SIN(PI() * T3.latitude / 180) where 1

alter table table3 add static3 float(18,16) default 0;
update table3 set static3 = COS(PI() * T3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)   where 1

Если table1.col2 имеет несколько значений

alter table table1 change col2 col2 enum('A','B','C');

Если table1.col3 имеет несколько значений

alter table table1 change col3 col3 enum('X','Y','X-Y');

Создайте уникальный индекс для всех столбцов, в которых alter table добавить индекс idx01 (col1, col2, col3)

SELECT STRAIGHT_JOIN
      T1.*, 
      T2.*
   FROM 
      Table1 AS T1
         JOIN Table2 AS T2 ON T1.Col1 = T2.Col1
         JOIN Table3 as T3 ON T1.Col1 = T3.Col1

   WHERE static1=1 AND
          T1.Col2 = 'A'
      AND T1.col3 IN ( 'X', 'X-Y') 
      AND T1.Col1 != 1
      AND ACOS(  
                 ( 
                   $usersLatitude_sin_pi_fract180  * t3.static2 
                   + $usersLatitude_cos_pi_fract180  * t3.static3 
                 )   
               ) <= 0,00252321929476 -- this 10/3963.191
      ORDER BY T1.Col6

Ваш комментарий подсказывает мне, что у вас другая сортировка в запросе (col1 - latin1_swedish, а col2 - utf8), или ваше соединение использует другую сортировку (ваше соединение - utf-8, и вы запрашиваете столбец latin1_german), поэтому, когда вы запрос:

t1.col2 = 'A'

Mysql должен преобразовать из utf-8 в latin1 каждое значение.

См. также раздел сортировки документации mysql.

Быстрый способ конвертировать все (столбец, таблица, сервер, соединение, клиент) в один и тот же набор синтаксических байтов будет лучше, если вам не нужен utf-8.

Будьте осторожны с ошибкой моего типа или синтаксической ошибкой, которую я мог бы сделать.

ДАЛЬШЕ ДОБАВИТЬ 2

Я воссоздал таблицы в тестовой БД, и я исправил эти столбцы: t1.col2, t2.col3 не может быть нулевым, t1.col1 является первичным и не может быть null.

Индекс "t1.CompositeIndex1" должен индексироваться только: col2, col3, col1; Индексировать столбец "порядок" не полезен или худший.

Я создаю static1, и я создаю индекс на t2.col1 и t2.static1, но с моими 6 строками в БД не используется (см. объяснение позже). t2.static1 также не должен иметь значение NULL.

Я также адаптирую запрос к сортировке столбцов:

SELECT  T1.*, T2.* 
FROM Table1 AS T1
         JOIN Table2 AS T2   ON ( T1.Col1 = T2.Col1   )
         JOIN Table3 as T3 ON T1.Col1 = T3.Col1
   WHERE  
         (  T1.Col2 =    'A'   collate utf8_bin  AND T1.col3 IN  ( 'X' collate utf8_bin , 'X-Y'  collate utf8_bin )   AND T1.Col1 != 1 )
and T2.static1=1
      AND ACOS(  (   2.3  * T3.static2  + 1.2 * T3.static3  ) ) <= 0.00252321929476 
      ORDER BY T1.Col6

Здесь следует объяснение, расширенное

+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
| id | select_type | table | type   | possible_keys                     | key             | key_len | ref            | rows | filtered | Extra                       |
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
|  1 | SIMPLE      | T1    | ref    | PRIMARY,col2,col3,CompositeIndex1 | CompositeIndex1 | 1       | const          |    1 |   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | T2    | eq_ref | PRIMARY,CompositeIndex1           | PRIMARY         | 4       | testdb.T1.col1 |    1 |   100.00 | Using where                 |
|  1 | SIMPLE      | T3    | eq_ref | PRIMARY                           | PRIMARY         | 4       | testdb.T1.col1 |    1 |   100.00 | Using where                 |
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+

Является ли это тем же для colums: select_type, table, type, key, ref, filter, Extra?

Мои цели оптимизации: - соответствовать условиям, в которых есть несколько индексов - избегать расчетов - избежать преобразования сортировки - избегать ИЛИ - избегать NULL, где условие

Теперь плохая новость Похоже, что в таблицах, которые вы используете ~ 140K записей, а запрос с использованием порядка может подразумевать использование подхода fileort, если запрос включает в себя много строк, поэтому окончательный ответ может увеличить буфер memsort, как подсказывает @mavroprovato.

ДАЛЬШЕ ДОБАВИТЬ 3

Чтобы оценить адекватность key_buffer_size, см. http://dba.stackexchange.com

ДАЛЬШЕ ДОБАВИТЬ 4

Я думаю, что только кто-то из Oracle может точно сказать, что происходит, но у меня есть идея.

Я думаю, что этот запрос свойственен:

все таблицы (t1, t2, t3) объединяются с помощью первичного ключа
другие условия зависят только от calcs (t3.colX)
некоторые условия зависят только от индекса (t1.colX)

Из-за 1 from_table_rows >= join1_table_rows >= join2_table_rows, так что меньше строк вернет из таблицы быстрее, будут 2 других JOINs

Оптимизатор для оценки усилия рассчитает аналогичное уравнение:

effort = num_rows*key_size/index_cardinality

(index_cardinality отображается по phpmyadmin рядом с каждым индексом)

Из-за 2 усилий >= num_rows

Мой запрос из-за 3 таблица1 (из таблицы) возвращает 92333 строк, таблица3 (join1_table) уменьшает до 1 (!) строки, таблица2 сохраняет 1 строку (усилие ~ 3).

Ваш запрос из-за 2 у вас должно получиться усилие = 140000, но, к счастью, для вас результат calc возвращает только 1 результат, чтобы ваш запрос был экстремально быстрым.

Demostration

В вашем запросе, изменяющемся от "< = 10" (в состоянии соединения) до "< = 1000" или более, вы увидите экспоненциальное снижение производительности.

В моем запросе, изменяющемся от "< = 10" (в состоянии соединения) до "< = 1000" или более, вы увидите линейное/логарифмическое снижение производительности.

ДАЛЬШЕ ДОБАВИТЬ 5

ответ на вопрос: слишком большой размер буфера сортировки?

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

ответ на вопрос: невозможно выполнить быстрый запрос?

IMHO нет, это возможно (даже если размер сортировочного буфера не устраняется).

Моя идея довольно проста, и она может возобновиться в этом mot: "cirlce хорош, но квадрат лучше".

В настоящий момент наибольшая мощность определяется координатами в таблице 3, но из-за формулы не применяется индекс. Таким образом, вместо поиска всех точек внутри радиуса вы можете искать все точки внутри "квадрата"

FROM table3
...
WHERE (t3.latitude-0.15) < $usersLatitude AND  $usersLatitude < t3.latitude+0.15  
AND t3.longitue - 0.15 < $usersLongitude AND   $usersLongitude < t3.longitue + 0.15

чтобы вы могли создать индекс в (t3.latitude, t3.longitue).

0,15 градуса должно быть 10 миль. Конечно, вы должны исправить вычеты вблизи меридиана смены дня и рядом с полюсами

Если вам нужен строго радиус, вы можете повторно подключиться к таблице 3 с помощью формулы радиуса (см. пример ниже) или , если возможно выполнить (/разработать) формулу, пока вы не сможете сравнить непосредственно значения с столбцами.

FROM table3 t3
JOIN table3 t3bis ON t3.id=t3bis.id
...
WHERE (t3.latitude-0.15) < $usersLatitude AND  $usersLatitude < t3.latitude+0.15  
AND t3.longitue - 0.15 < $usersLongitude AND   $usersLongitude < t3.longitue + 0.15
AND 
3963.191 
* ACOS(  (SIN(PI() * $usersLatitude / 180) * SIN(PI() * t3bis.latitude / 180)) 
+ (  COS(PI() * $usersLatitude / 180) * COS(PI() * t3bis.latitude / 180) 
* COS(PI() * t3bis.longitude / 180 - PI() * 37.1092162 / 180)
)   
) <= 10

ДАЛЕЕ ДОБАВИТЬ 6

тема: скомпилированные функции делают это лучше

использование функции RADIANS()

degree * PI / 180 == radians(degree)

Использование расширения GIS для mysql

Смотрите статью о расширении GIS MySql

Ответ 3

Попробуйте выполнить первый запрос:

...
FROM 
    Table1 AS table1 USE INDEX (col6)
LEFT JOIN 
    Table2 AS table2   
...

Ответ 4

Каков тип table1.col6? Каков его диаметр поля (максимальная длина)?

Предварительно вычислить значения BTW, которые не зависят от значений полей, как предположил Иван Буттинони. Это не поможет при заказе, но быстрее сделает запрос

Ответ 5

Есть три (3) вещи, которые я вижу, вы можете сделать:

1) Рефакторинг запроса

2) Примените ORDER BY в таблице 1 ранее в запросе

3) Таблица индексов1 для поддержки рефакторинга

Возможно, это...

ALTER TABLE Table1 ADD INDEX col2_col6_ndx (col2,col6);
SELECT
    table1.*, 
    table2.*
FROM 
    (
        SELECT * FROM Table1
        WHERE col2='A' AND 
        ORDER BY col6 DESC
    ) AS table1
LEFT JOIN 
    (
        SELECT * FROM Table2
        WHERE (col4='Y' OR col5='Y')
    ) AS table2 
USING 
    (col1)
LEFT JOIN 
    Table3 as table3 
USING 
    (col1) 
WHERE 
    table1.col1 != '1' AND
    table1.col3 IN ('X','X-Y') AND
    3963.191 * 
    ACOS(
    (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180)) 
    +
    (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
    ) <= 10 
;

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

ALTER TABLE Table1 ADD INDEX col2613_ndx (col2,col6,col1,col3);
ALTER TABLE Table2 ADD INDEX col4_col1_ndx (col4,col1);
ALTER TABLE Table2 ADD INDEX col5_col1_ndx (col5,col1);
SELECT 
    table1.*, 
    table2.*
FROM
(
    SELECT table1.col1,table3.latitude,table3.longitude 
    FROM 
        (
            SELECT col1 FROM Table1 WHERE col2='A' AND
            AND col3 IN ('X','X-Y') ORDER BY col6 DESC
        ) AS table1
    LEFT JOIN 
        (
            SELECT col1 FROM Table2 WHERE col4='Y' UNION
            SELECT col1 FROM Table2 WHERE col5='Y'
        ) AS table2 
    USING (col1)
    LEFT JOIN Table3 as table3 USING (col1)
) col1_keys
LEFT JOIN Table1 table1 USING (col1)
LEFT JOIN Table2 table2 USING (col1)
WHERE 
    3963.191 * 
    ACOS(
    (SIN(PI() * $usersLatitude / 180) * SIN(PI() * col1_keys.latitude / 180)) 
    +
    (COS(PI() * $usersLatitude / 180) * COS(PI() * col1_keys.latitude / 180)
    * COS(PI() * col1_keys.longitude / 180 - PI() * 37.1092162 / 180))
    ) <= 10 
;

Дайте ему попробовать!!!