Mysql - длительный запрос без правильных индексов
этот запрос работает около 15 часов в производстве, я ищу альтернативы его улучшениям,
некоторые улучшения, которые, я думаю, могут помочь, прокомментированы здесь:
SELECT table1.*
FROM table1
WHERE UPPER(LEFT(table1.cloumn1, 1)) IN ('A', 'B')
AND table1.cloumn2 = 'N' /* add composite index for cloumn2,
column3 */
AND table1.cloumn3 != 'Y'
AND table1.id IN (
SELECT MAX(id)
FROM table1
GROUP BY column5,column6
) /* move this clause to 2nd after
where */
AND table1.column4 IN (
SELECT column1
FROM table2
WHERE column2 IN ('VALUE1', 'VALUE2')
AND (SUBSTRING(column3,6,1) = 'Y'
OR SUBSTRING(column3,25,1) = 'Y')
) /* move this clause to 1st after
where */
AND (table1.column5,table1.column6) NOT IN (
SELECT column1, column2
FROM table3
WHERE table3.column3 IN ('A', 'B')/* add index for this column*/
)
AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE,
INTERVAL - 28 DAY)) /* need index ON this col? */ ;
Любые комментарии / предложения приветствуются.
Обновлять: при обновлении только порядка фильтрации производительность запроса была улучшена до ~ 28 секунд, здесь обновится после добавления некоторых индексов и замены некоторых подзапросов в соединениях
«Я ищу альтернативы для улучшения этого», подумайте об использовании PostgreSQL вместо MySQL .. PostgreSQL поддерживает индексы по выражениям, что означает, что вы можете индексировать функции. postgresql.org/docs/9.1/static/indexes-expressional.html
Или используйте сгенерированные столбцы MySQL 5.7.6+ для индексации функций ... что-то вроде column AS (UPPER(LEFT(table1.cloumn1, 1))) stored, тогда вы можете индексировать.
@RaymondNijland - UPPER(LEFT(table1.cloumn1, 1)) для этого я думаю создать индекс как create index name_first on my_table (cloumn1(1));, чтобы исключить использование LEFT.
У вас есть лишняя скобка в запросе? Кажется, есть, когда я провожу быструю проверку синтаксиса. Но, возможно, лучше всего выполнить соединение, а не использовать подзапросы. Специально для основной проверки, которая получает максимальные идентификаторы для каждого столбца 5 / столбца 6. Таким образом, MySQL может исключить максимальное количество записей на самом раннем этапе.
Обновление: с обновлением только порядка фильтрации производительность запроса была улучшена до ~ 28 секунд, обновится здесь после добавления некоторых индексов и замены некоторых подзапросов в соединениях
Ответы 2
Предполагая, что вы можете добавить полезные индексы (которые помогут в некоторых из ваших проверок), тогда, возможно, попытайтесь исключить строки как можно раньше.
Я подозреваю, что у вас довольно много строк на table1 для каждой комбинации column5 / column6. Если вы можете получить только самую последнюю из них (т. Е. Используя подзапрос, к которому вы присоединяетесь) как можно раньше, вы можете исключить большинство строк из table1, прежде чем вам нужно будет проверить любое из неиндексированных предложений WHERE. Вы также можете исключить некоторые из них, выполнив дополнительное соединение с подзапросом в table3.
Не проверено, но если мои предположения о структуре вашей базы данных верны, это может быть улучшением: -
SELECT table1.*
FROM
(
SELECT MAX(table1.id) AS max_id
FROM table1
INNER JOIN
(
SELECT DISTINCT column1, column2
FROM table3
WHERE table3.column3 IN ('A', 'B')
AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE, INTERVAL - 28 DAY)
) sub0_0
ON table1.column5 = sub0_0.column1
AND table1.column6 = sub0_0.column2
WHERE (table1.cloumn1 LIKE 'A%' OR table1.cloumn1 LIKE 'B%')
AND table1.cloumn2 = 'N'
AND table1.cloumn3 != 'Y'
GROUP BY table1.column5,
table1.column6
) sub0
INNER JOIN table1
ON table1.id = sub0.max_id
INNER JOIN
(
SELECT DISTINCT column1
FROM table2
WHERE column2 IN ('VALUE1', 'VALUE2')
AND (SUBSTRING(column3,6,1) = 'Y'
OR SUBSTRING(column3,25,1) = 'Y')
) sub1
ON table1.column4 = sub1.column1
Спасибо, я протестирую их в разных средах и обновлю то, что найду.
даже без добавления индексов - я вижу, что производительность улучшилась в тестовом env с ~ 15 секунд до ~ 1,5 секунд - Большое спасибо :)
Индексы должны это улучшить. Точно так же, вероятно, тривиально удалить часть форматирования даты одного из подзапросов, что позволяет использовать индекс в поле timstampColumn (при необходимости вы можете добавить время к вычислению 28 дней назад, что позволяет запросу напрямую используйте индекс на timstampColumn)
(Это может помочь увидеть SHOW CREATE TABLE.)
AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE,
INTERVAL - 28 DAY))
не может использовать индекс; это может быть эквивалентно:
AND timstampColumn > CURRENT_DATE - INTERVAL 28 DAY
Пожалуйста, предоставьте EXPLAIN.
Какую версию ты используешь?
Это может (в зависимости от версии) помочь превратить предложения IN ( SELECT ... ) в `` производные '' таблицы:
JOIN ( SELECT ... ) ON ...
WHERE (x,y) IN ... плохо оптимизирован. Какие это ценности?
С сопоставлением * _ci,
UPPER(LEFT(table1.cloumn1, 1)) IN ('A', 'B')
можно сделать:
LEFT(table1.cloumn1, 1) IN ('A', 'B')
Это не сильно повлияет на производительность. Лучше не разбивать колонки для тестирования.
Здесь может использоваться индекс с участием cloumn1:
table1.cloumn1 >= 'A'
AND table1.cloumn1 < 'C'
Порядок вещей, сложенных вместе AND, редко имеет значение. Порядок в INDEX может иметь большое значение.
Множественный выражения nonSARGable:
UPPER(LEFT(table1.cloumn1, 1)),DATE_FORMAT(timstampColumn, '%Y/%m/%d'),SUBSTRING(column3,6,1)