Как оптимизировать sql-запрос?

Question

[SergeyNN]

Добрый день.

Есть таблица на 2,5 млн записей.
Есть запрос:

UPDATE `table`
    SET `flag` = 1 
WHERE 1
    AND `flag` = 0
    AND `datetime_a` IS NULL
    AND `datetime_b` IS NOT NULL 
    AND `datetime_b` < '2020-03-22 16:00:00'

Запрос выполняется за 5 секунд. Что с индексами на полях, что нет.
Можно ли это оптимизировать - сократить время выполнения запроса?

Comments

[d'Ivan]

Что с индексами на полях, что нет.

Так какие индексы все же есть?

[Евгений Самсонов]

EXPLAIN не работает?

[nokimaro]

EXPLAIN SELECT flag FROM `table` WHERE 1
    AND `flag` = 0
    AND `datetime_a` IS NULL
    AND `datetime_b` IS NOT NULL 
    AND `datetime_b` < '2020-03-22 16:00:00'

И результаты сюда для дальнейшего обсуждения

[SergeyNN]

nokimaro,

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table	ref	flag,datetime_a,deadline_b	flag	1	const	1284336	Using where

[SergeyNN]

Роман Мирр, сначала индексов не было, потом добавил. Результат не меняется.

[nokimaro]

SergeyNN, используется индекс по полю flag и в выборке 1.284.336 записи
То есть понимаете, что у этого индекса очень плохая селиктивность (слишком много записей)
Могу посоветовать составной индекс
Например (flag + datetime_b) или на все 3 поля (flag + datetime_a + datetime_b)

Не уверен как сейчас, но раньше в MySQL в случае составных индексов ещё играл роль прядок полей в WHERE
то есть если индекс по (a + b +c) то и в запросе поля должны идти в таком же порядке WHERE a = ... AND b =... AND c = ...

[nokimaro]

SergeyNN, в любом случае время выполнения запроса прямо-пропорционально кол-ву записей с которыми производится манипуляция.
То есть UPDATE который затрагивает 1.2 млн строк, никак не может быть моментальным или супер-быстрым, так как это операции записи и они требуют больше времени и ресурсов чем простые SELECT'ы.

[SergeyNN]

nokimaro, попробую, спасибо

[SergeyNN]

dimonchik2013, простите, не понял вашей мысли

[nokimaro]

SergeyNN, то есть главная мысль в том, что оптимизировать можно запрос который ищет строки для апдейта.
Чтобы нужные строки находились максимально быстро - тут помогут индексы.
Если же по итогу, вам нужно сделать UPDATE на половину таблицы (1.2млн строк из 2.5 млн), то тут всё упирается уже в скорость записи (быстрый проц, быстрые диски ssd, размеры буфферов, кол-во RAM) и индексы могут никак не помочь, а иногда даже будут замедлять скорость записи, так как нужно будет обновить не только данные, но и индексы.

[SergeyNN]

nokimaro, спасибо, пойду экспериментировать

[d'Ivan]

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

[marsdenden]

А для чего этот флаг? Не проще проверять условия там, где используется флаг?

[SergeyNN]

marsdenden, попробую и так, спасибо

[SergeyNN]

Роман Мирр, не очень понимаю, про какое удаление речь. Я ж update делаю. Или я чего-то не знаю?

[d'Ivan]

SergeyNN, перепутал с другим вопросом, видимо.

Answer 1

[Dimonchik]

AND `datetime_b` IS NOT NULL лишнее

Comments

[SergeyNN]

к сожалению, ситуация принципиально не меняется

Answer 2

[Иван Мельников]

... WHERE 1 ...
Для чего эта 1 ?

Попробуйте так:

UPDATE `table`
    SET `flag` = 1 
WHERE
    `datetime_b` < '2020-03-22 16:00:00'
    AND `datetime_a` IS NULL
    AND `flag` = 0;

При этом для более быстрого выполнения данного запроса нужен индекс только на поле datetime_b.

Comments

[SergeyNN]

Не помогает

[Дмитрий]

WHERE 1  и похожее обычно прокидывают движки, чтоб было удобно добавлять новые условия
тупо не нужна проверка, есть ли там уже что-то, можно добавить к строке "AND ..." и запрос будет корректным

Answer 3

[FanatPHP]

Идеальным вариантом будет купить букварь по работе с БД и убрать поле flag из таблицы совсем.

Comments

[d'Ivan]

Какой букварь рекомендуете?

[FanatPHP]

Я по-старинке, Поля Дюбуа

[SergeyNN]

FanatPHP, скажите, пожалуйста, в общих чертах, что не так с практикой устанавливать флаги, признаки, категории для записей?

[FanatPHP]

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

[Minu]

FanatPHP, либо в ваших букварях этого не пишут, либо Вы уроки плохо учили, но параметры запроса могут быть динамическими. Я не думаю что '2020-03-22 16:00:00' у автора вбито константой в коде, скорее всего условие выглядит так ... AND `datetime_b` < $1, где параметр приходит из внешней среды и в общем случае может быть произвольным. Что в этом случае прикажете делать?

SergeyNN, флаги плохи тем, что индексы по бинарным обладают низкой селективностью. В итоге СУБД может предпочитать последовательное сканирование таблицы, вместо сканирования по индексу, а отсюда и низкая производительность. Попробуйте создать индекс на `datetime_b` и убрать из запроса условие "`flag` = 0". В PostgreSQL имеет смысл создавать частичные индексы, например

CREATE INDEX table_idx ON `table` USING btree (`datetime_b`) WHERE `flag` = 0

. В MySQL такого нет, на сколько я знаю. Ну и всё сильно зависит от распределения значений в таблице, 50/50 или 5/95 - две большие разницы. Так что тут важно знать не только количество данных в таблице, но и их структуру.

[FanatPHP]

Minu, отправлять запрос на выполнение

[SergeyNN]

Minu, спасибо, попробую

Answer 4

[edo1h]

Я всегда начинаю с select *. Если отрабатывает быстро — значит медленное именно обновление данных (много индексов, например). Если медленно — его и оптимизируем.

Answer 5

[Александр Филиппенко]

Вы обновляете половину записей в таблице. Индексы тут не помогают, а наоборот вредят, так как их надо перестраивать.
Удалить индексы, выполнить апдейт и создать не быстрее будет?

Comments

[SergeyNN]

Без индексов время такое же

[Александр Филиппенко]

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

Answer 6

[Minu]

Сколько записей обновляется запросом?
Выполните

SELECT count(1)
FROM `table` 
WHERE 1
    AND `flag` = 0
    AND `datetime_a` IS NULL
    AND `datetime_b` IS NOT NULL 
    AND `datetime_b` < '2020-03-22 16:00:00'