Запрос с неизменным планом заметно быстрее при повторном запуске. Какой механизм объясняет разницу?
Разницу обычно объясняет прогрев буферного кэша: при первом запуске нужные страницы читаются с диска или другого медленного уровня хранения, а при повторном уже находятся в оперативной памяти СУБД. План выполнения при этом может оставаться полностью неизменным; меняется не стратегия доступа, а стоимость физического ввода-вывода.
Дисковые операции значительно медленнее работы с оперативной памятью, поэтому СУБД используют буферный менеджер. Он загружает страницы таблиц и индексов в память и старается повторно использовать их без обращения к хранилищу.
Такой подход появился для уменьшения задержек ввода-вывода и повышения пропускной способности. Благодаря ему часто читаемые данные обслуживаются из памяти, а дисковые операции выполняются только при промахах кэша или необходимости загрузить новые страницы.
Один и тот же запрос может показывать разное время выполнения в зависимости от состояния кэша. После перезапуска СУБД, вытеснения страниц из памяти или конкуренции за буферный пул запрос может стать медленным, хотя индексы, текст запроса и план не изменились.
Опасно оценивать оптимизацию только по одному запуску. Измерение на прогретом кэше может скрыть большое количество физических чтений, а измерение на холодном кэше — преувеличить влияние дисковой подсистемы на обычный рабочий сценарий.
План определяет, какие объекты и каким способом читать: например, выполнить поиск по индексу или сканирование. Затем исполнитель обращается к страницам этих объектов. Если страница уже находится в буферном пуле, возникает логическое чтение из памяти; если её нет, происходит физическое чтение с диска или другого уровня хранения, после чего страница помещается в память.
Поэтому повторный запуск часто быстрее при том же плане: данные и страницы индекса уже загружены. Ускорение не означает, что запрос стал логически эффективнее; оно может лишь показывать, что исчезла значительная часть физического ввода-вывода.
Для диагностики сравнивают время, логические и физические чтения, фактический план, ожидания ввода-вывода и состояние памяти. В разных СУБД названия счётчиков различаются, но принцип один: нужно отделять объём работы плана от стоимости доставки страниц в память.
Кэш не гарантирует постоянное ускорение. Страницы вытесняются из-за нехватки памяти, конкурирующих запросов, больших сканирований и других операций. Кроме того, часть данных может обслуживаться кэшем операционной системы или системой хранения, поэтому физическое чтение на уровне СУБД не всегда означает непосредственное обращение к диску.
Возможные меры зависят от причины проблемы. Увеличение памяти уменьшает промахи кэша, но не исправляет запрос, который читает слишком много страниц. Подходящий индекс или покрывающая структура могут сократить объём чтения, однако добавляют стоимость вставок, обновлений, хранения и обслуживания индексов. Оптимизация должна учитывать как холодный старт, так и устойчивую работу при реальной конкуренции за память.
Отчёт по заказам выполнялся за 18 секунд после ночного перезапуска базы и за 2 секунды при повторном запуске. План не менялся, но первый запуск выполнял существенно больше физических чтений; основная таблица и индекс занимали объём, превышающий доступный рабочий набор буферного пула.
Рассматривались три варианта. Увеличение памяти уменьшило бы промахи кэша, но требовало инфраструктурных затрат и не решало проблему при одновременных отчётах. Создание широкого покрывающего индекса сократило бы чтения таблицы, но увеличило бы размер базы и стоимость изменения заказов. Простое повышение тайм-аутов не устраняло причину.
Выбрали более узкий индекс, содержащий условия отбора и необходимые для отчёта ключевые поля, а редко используемые столбцы оставили для отдельного чтения. Это уменьшило число страниц, которые требовалось загрузить, и стабилизировало холодный запуск без чрезмерного роста стоимости записи. После изменения проверили показатели на холодном и прогретом кэше, а также под конкурентной нагрузкой.
1. Как доказать, что ускорение вызвано кэшем, а не изменением плана?
Нужно сравнить планы обоих запусков и убедиться, что операторы, оценки и выбранные индексы не изменились. Затем сопоставляют логические и физические чтения: рост физических чтений на первом запуске при близком объёме логической работы указывает на холодный кэш. Полезно также проверить ожидания ввода-вывода и повторить измерение несколько раз в контролируемой среде; принудительно очищать кэш в рабочей системе опасно.
2. Может ли прогретый кэш скрыть плохой план?
Да. План, который читает чрезмерное число страниц, может выглядеть приемлемым, пока эти страницы уже находятся в памяти. При вытеснении данных, росте объёма таблицы или конкуренции за буферный пул его стоимость резко проявится. Поэтому прогрев кэша снижает наблюдаемую задержку, но не уменьшает количество работы, заложенной в плане.
3. Почему индекс иногда ускоряет холодный запуск, но почти не влияет на повторный?
Индекс может заменить чтение большого числа страниц таблицы чтением меньшего числа страниц индекса, что особенно заметно при холодном кэше. После первого запуска обе структуры частично находятся в памяти, поэтому выигрыш от сокращения физических чтений уменьшается. Если индексный поиск возвращает много строк и вызывает множество обращений к таблице, он даже может оказаться менее выгодным, чем последовательное чтение; решение зависит от селективности, покрытия индекса и стоимости случайного ввода-вывода.