Почему из этих запросов. Первый запрос работает очень долго, а второй мгновенно…
- User
Почему из этих запросов. Первый запрос работает очень долго, а второй мгновенно выполняется? в обоих вариантах результат - 1000 строк. получается время запросов должно быть одинаковым.
select SQL_NO_CACHE id from slc.gz_supplier where id > 344760 and ogrn is null order by id limit 1000
select SQL_NO_CACHE id from slc.gz_supplier where id > 344760 and id < 374760 and ogrn is null order by id limit 1000
Assistant
Отличный вопрос! Это классическая ситуация, которая прекрасно иллюстрирует, как работает оптимизатор запросов в базах данных и почему понимание индексов так важно.
Давайте разберем, что видит база данных в каждом случае.
Краткий ответ
Второй запрос быстрее, потому что вы даете базе данных гораздо более узкие рамки для поиска. Она заранее знает, что ей нужно просканировать не более 30 000 записей (
374760 - 344760), чтобы найти нужные 1000.В первом запросе база данных не знает, где закончится поиск. Ей приходится последовательно сканировать записи, начиная с
id = 344760, проверяя каждую на условиеogrn is null, пока не наберется 1000 строк. Если записи сogrn is nullвстречаются редко, ей может потребоваться просканировать миллионы строк, чтобы найти нужную тысячу.
Подробное объяснение
Предположим, у вас есть индекс по полю
id(что почти наверняка так, если этоPRIMARY KEY).Анализ первого (медленного) запроса
select SQL_NO_CACHE id from slc.gz_supplier where id > 344760 and ogrn is null order by id limit 1000Как рассуждает оптимизатор MySQL:
- Сортировка (
order by id): "Мне нужно отсортировать результат поid. Отлично, у меня есть индекс поid, я могу использовать его, чтобы не делать отдельную операцию сортировки (filesort)." - Фильтрация (
where): "У меня два условия:id > 344760иogrn is null." - План выполнения: "Я буду использовать индекс по
id, потому что это позволит мне сразу получить данные в нужном порядке (order by id) и начать с нужного места (id > 344760)."
Что происходит на самом деле:
- База данных с помощью индекса по
idмгновенно "прыгает" к записи сidчуть больше344760. - Она начинает последовательно читать записи по индексу
id(что уже обеспечивает сортировку). - Для каждой прочитанной записи она должна выполнить проверку:
ogrn is null? - Если у записи
id = 344761полеogrnзаполнено, она отбрасывает эту запись и переходит к следующей (id = 344762). - Она продолжает этот процесс (чтение по индексу -> проверка
ogrn) до тех пор, пока не наберет 1000 строк, удовлетворяющих обоим условиям.
Проблема: Если среди записей с
id > 344760очень мало тех, у которыхogrn is null(например, 1 из 1000), то для нахождения 1000 нужных строк базе придется просканировать и проверить1000 * 1000 = 1 000 000записей. Это и есть причина долгого выполнения.Анализ второго (быстрого) запроса
select SQL_NO_CACHE id from slc.gz_supplier where id > 344760 and id < 374760 -- <-- Ключевое отличие! and ogrn is null order by id limit 1000Как рассуждает оптимизатор MySQL:
- Сортировка и Фильтрация: "Ага, здесь у меня есть четкий, ограниченный диапазон по
id: от 344760 до 374760. Это всего 30 000 записей. Это очень маленькая часть таблицы." - План выполнения: "Я сделаю то же самое, что и в первом случае: буду использовать индекс по
id."
Что происходит на самом деле:
- База данных с помощью индекса по
id"прыгает" к записи344760. - Она начинает последовательно сканировать записи, проверяя условие
ogrn is null. - Но теперь у нее есть условие остановки! Как только она дойдет до
id = 374760, она прекратит поиск, даже если не набрала 1000 строк. - В вашем случае все 1000 нужных строк нашлись внутри этого небольшого диапазона. База данных просканировала, возможно, всего 10-20 тысяч записей, нашла нужную тысячу и мгновенно вернула результат.
Аналогия с телефонным справочником
Представьте, что вам нужно найти в огромном телефонном справочнике первые 10 человек, у которых фамилия начинается на "Иванов", и при этом их номер телефона заканчивается на "5".
- Первый запрос (медленный): Вы открываете справочник на букве "И", находите первого Иванова и смотрите на его номер. Не заканчивается на 5. Смотрите следующего Иванова... и так далее. Если номера на "5" встречаются редко, вы можете пролистать сотни страниц с Ивановыми.
- Второй запрос (быстрый): Вам говорят: "Найди нужных людей, но смотри только на страницах с 200 по 205". Задача становится тривиальной. Вы быстро просматриваете всего 5 страниц, находите нужных людей и заканчиваете работу.
Как можно было бы ускорить первый запрос?
Чтобы первый запрос работал так же быстро, как и второй, вам нужен составной индекс (composite index).
CREATE INDEX idx_supplier_ogrn_id ON slc.gz_supplier(ogrn, id);Как бы этот индекс помог:
- База данных сначала нашла бы в индексе все записи, где
ogrn IS NULL. - Внутри этой группы записи уже отсортированы по
id(т.к.id- вторая часть индекса). - Она бы просто взяла первые 1000 записей из этой отсортированной группы, которые удовлетворяют условию
id > 344760.
С таким индексом первый запрос выполнялся бы мгновенно, так как не было бы необходимости сканировать лишние данные.
- Сортировка (
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.