Как делать приблизительные оценки SQL-запросов

Представьте, что вы пишете все более сложные требования. Больше, чем просто получение одного объекта по первичному ключу, особенно если вы запускаете его в цикле или в среде, которая крайне нетерпима к длительным запросам. В более сложных случаях вы также не имеете доступа к производственной базе данных и выполняете реальный запрос к оперативным данным и производственному оборудованию.

Как вы обрабатываете приблизительные оценки выполнения SQL-запросов?

Этапы реализации

Каждая база данных выполняет запрос через стандартный процесс (с некоторыми отклонениями в зависимости от реализации dB):

* Разбор синтаксиса * Семантический разбор * Планирование * Выполнение

Самая трудоемкая часть обычно выполняется только потому, что база данных должна перейти на диск, получить некоторые данные и отправить их клиенту. Другие части процесса в основном выполняются процессором и используют некоторые кэшированные метаданные. Планировщик создает схемы, сравнивает их, выбирает лучшую и отправляет на этап исполнения. Этап выполнения запускает план — переходит на диск, загружает данные, сортирует или фильтрует их, снова обращается к диску за другим фрагментом данных, объединяет наборы данных и так далее, пока план не будет завершен и результат не будет готов для отправки в клиент. Эти операции обычно занимают большую часть общего времени запроса.

Планы казней

Давайте посмотрим, что происходит с примером запроса:

SELECT u.id, u.last_login, ud.phone
FROM user u
JOIN user_detail ud ON ud.user_id = u.id
WHERE u.firstname = 'John';

С точки зрения программного обеспечения

Давайте напишем несколько планов выполнения такого запроса.

Например:

• Прямой путь должен быть лучшим в большинстве случаев.

for (User u: Users) {
  if (u.firstname == 'John') {
    for (UserDetail ud: UserDetails) {
      if (ud.user_id = u.id) {
        result.put(u.id, u.last_login, ud.phone);
      }
    }
  }
}
return result;

* Если мы знаем, что почти все пользователи являются «Джонами» и лишь небольшая часть из них имеет расширение user_detail, следующий код должен работать быстрее.

for (UserDetail ud: UserDetails) {
  for (User u: Users) {
    if (ud.user_id = u.id && u.firstname == 'John') {
      result.put(u.id, u.last_login, ud.phone)
    }
  }
}
return result;

Дополнительные данные

Индексы. Они работают как дополнительные структуры и с точки зрения кода. Индексы обычно выглядят как предварительно рассчитанные карты, обычно TreeMaps. Возьмем, к примеру, первый план. Некоторая карта user_id в UserDetails может пересчитываться при каждом изменении таблицы, и эта структура может значительно ускорить наш запрос на выборку.

* Использование индекса поверх user_id в UserDetails

for (User u: Users) {
  if (u.firstname == 'John') {
    result.put(u.id, u.last_login, user_id_to_UserDetails[u.id].phone);
  }
}
return result;

* Использование индекса над firstname в User. Поскольку firstname не является уникальным значением, этот индекс работает как MultiValuedMap.

for (User u: firstname_to_User['John']) {
  for (UserDetail ud: UserDetails) {
    if (ud.user_id = u.id) {
      result.put(u.id, u.last_login, ud.phone);
    }
  }
}
return result;

* Использование обоих индексов

for (User u: firstname_to_User['John']) {
  result.put(u.id, u.last_login, user_id_to_UserDetails[u.id].phone);
}
return result;

Итак, у нас много планов. Как мы выбираем лучший?

Поначалу кажется, что последний план может быть лучшим выбором. Но это верно не во всех ситуациях.

Давайте выясним, почему.

Статистика и данные об оборудовании.

База данных уже выполнила несколько запросов к таблице и знает количество данных и некоторые другие вещи, например, как и где данные хранятся. База данных также знает, как и где хранятся дополнительные структуры (индексы).

Ведь каждый план имеет свою сложность. Сложность также зависит от дополнительных структур, потому что некоторые полезные вещи можно предварительно вычислить. Окончательное время (или стоимость) запуска зависит от скорости оборудования. Это зависит не только от количества данных или запросов, которые необходимо выполнить для извлечения данных, но и от характера запросов. Они могут генерировать как последовательные, так и случайные чтения. И характер запросов может сильно повлиять на окончательное время.

Выполнение запроса

Чтобы выбрать лучший план, база данных должна иметь:

* сами планы; * знание предварительно рассчитанных вещей, таких как индексы или кэши из более ранних запросов; * знание расположения данных и дополнительных структур (диск или кэш-память); * знание работы диска и памяти (скорость последовательного и произвольного доступа, количество операций ввода-вывода в секунду).

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

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

Поиск лучшего сценария

Каждую таблицу можно получить с диска с некоторыми фильтрами двумя способами:

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

Давайте сравним оба подхода на примере

Чтобы получить строки таблицы по firstname ='John':

Если нет индексов по firstname, база данных:

* загружает всю таблицу (все записи). Операция требует последовательного чтения в дисковой системе, а количество операций полностью зависит от размера таблицы; * фильтрует все записи по полям; * отправляет результат клиенту.

Если есть индекс по firstname, база данных:

* ищет по индексу и получает позиции, в которых есть записи с firstname=’John’. Операция требует нескольких случайных запросов на чтение к диску и зависит от размера индекса и количества строк, но, поскольку это только логарифмическая сложность, запросов не слишком много, обычно всего несколько; * загружает записи по одной прямо на свои позиции; * отправляет результат клиенту.

Чтобы получить доступ к времени этих подходов, мы должны знать спецификации дисковой системы и размер таблиц и индексов. Для примера возьмем некоторые характеристики HDD диска до 500МБ/с при последовательном чтении и до 300 операций в секунду при случайном чтении. Предположим, у нас есть 1 миллион строк, каждая из которых в среднем занимает 50 байт. Это означает, что у нас есть размер таблицы ~ 50 МБ и размер индекса 6-7 МБ (при условии, что у нас есть в среднем 6 символов на имя). Предположим, у нас в таблице примерно 300 разных Джонсов.

Итак, чтобы рассчитать тайминги обоих подходов:

* без индексов, мы должны прочитать 50 Мб с диска и отфильтровать их процессором. Чтение намного медленнее процессора, поэтому оно займет 99%+ времени. Так что такой подход будет стоить нам 50/500=0,1 сек. * с индексом нам нужно прочитать указатели всех записей с firstname=’John’ за несколько чтений.

Затем нам нужно сделать до 3000 случайных чтений с диска, чтобы извлечь построчно все необходимые строки. Некоторые из них могут находиться на одной и той же странице памяти, поэтому фактическое время может быть ниже. Но для высокого предположения это 300 чтений. 300 операций/сек / 300 операций = 1 сек. Это означает, что выполнение второго подхода занимает до одной секунды.

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

Другие оптимизации

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

Например, когда запрос может использовать несколько индексов для одних и тех же данных, база данных может сделать это с помощью операции с растровым изображением. Это операция, при которой база данных одновременно загружает два или более индексов. Затем он выполняет над ними логические операции - union для операции OR и пересечение для операции AND. Затем база данных может загрузить таблицу, используя результирующую операцию над индексами.

Таким образом, в этом случае вся операция требует меньшего и меньшего количества операций чтения с диска, некоторых операций на ЦП и случайных операций чтения с диска в строки выборки таблицы.

Знание типичных внутренних операций базы данных помогает разработчикам не только оценивать запросы, но и выбирать правильный набор индексов и быстрее писать запросы.

Заключение

Одна важная современная вещь о предположениях заключается в том, что сегодня мы используем облака повсеместно, и иногда просто сложно узнать об оборудовании в недрах облачного провайдера, и, в конце концов, может быть сложно сделать предположение. Тем не менее, есть законы физики, которые мы не можем обойти. Каждое чтение требует некоторого времени, и каждая операция имеет задержку. И если у вас нет терабайт памяти в вашей базе данных, знаний достаточно, чтобы делать определенные предположения на основе типичных запросов.