Оптимизация SQL-запроса с огромным предложением where
Я работаю над системой (с Laravel), где пользователи могут заполнить несколько фильтров, чтобы получить нужные им данные. Данные не подготавливаются в реальном времени, после установки фильтров задание помещается в очередь, а после завершения запроса создается файл CSV. Затем пользователь получает электронное письмо с файлом, который был создан, чтобы он мог его скачать.
Я видел некоторые ошибки в заданиях, когда обработка одного задания занимала более 30 минут, и когда я проверил, я увидел, что некоторые пользователи создали фильтр с более чем 600 значениями.
Значения этого фильтра переводятся так:
SELECT filed1,
field2,
field6
FROM table
INNER JOIN table2
ON table.id = table2.cid
/* this is how we try not to give same data to the users again so we used NOT IN */
WHERE table.id NOT IN(SELECT data_id
FROM data_access
WHERE data_user = 26)
AND ( /* this bit is auto populated with the filter values */
table2.filed_a = 'text a'
OR table2.filed_a = 'text b'
OR table2.filed_a = 'text c' )
Что ж, я не ожидал, что пользователи будут сходить с ума и точно настраиваться с помощью огромного набора фильтров. Для них это нормально, но им нужно решение, чтобы сделать этот запрос быстрее.
Один из способов - создать на лету временную таблицу со значениями фильтра и скрыть запрос для INNER JOIN, но не уверен, что это повысит производительность.
Кроме того, учитывая, что в обычном режиме системе потребуется создать не менее 40 временных таблиц и затем удалить их. Станет ли это еще одной проблемой в долгосрочной перспективе?
Я хотел бы услышать любые другие предложения, которые могут помочь мне решить эту проблему, кроме метода временной таблицы.
Действительно, то, что подзапрос в выражении NOT IN не коррелирован, мне кажется очень подозрительным, особенно в свете связанного комментария.
вы уже пытаетесь изменить WHERE table.id NOT IN (подзапрос) с LEFT JOIN data_access ON data_access.data_id = table.id ГДЕ data_access.data_id IS NULL?
Ответы 1
Я бы посоветовал написать такой запрос:
SELECT ?.filed1, ?.field2, ?.field6 -- qualify column names (but no effect on performance)
FROM table t JOIN
table2 t2
ON t.id = t2.cid
WHERE NOT EXISTS (SELECT 1
FROM data_access da
WHERE t.id = da.data_id AND da.data_user = 26
) AND
t2.filed_a IN ('text a', 'text b', 'text c') ;
Тогда я бы порекомендовал индексы. Вероятно:
table2(filed_a, cid)table1(id)(может не понадобиться, еслиidуже является первичным ключом)data_access(data_id, data_user)
Вы можете проверить это как свой собственный запрос. Я не знаю, как заставить Laravel создать это (при условии, что это соответствует вашим целям производительности).
Вы проверили план
EXPLAIN? Я спрашиваю об этом, потому что подзапрос вWHERE- это нет, коррелированный с внешним запросом, а это означает, что MySQL, вероятно, может запустить его один раз и кэшировать результаты. Так что я не уверен, что это будет вашим самым большим узким местом.