Выполняя любую команду SQL, каждая СУБД старается выполнить её максимально эффективно. В каждой СУБД есть «оптимизатор», то есть механизм, принимающий решение как команда будет выполнена максимально эффективным образом. Строится план. Мы можем влиять на план выполнения запроса с помощью подсказок (хинтов), оставляя их прямо в SQL-коде.
Если на одной из таблиц, участвующих в SQL-запросе есть индекс для какого-то столбца, значения которого используются во WHERE или в ON, то не обязательно, что он будет задействован при выполнении запроса: при наличии, например, в таблице продаж в общей сложности ста строк, и если в этих строках даты продаж только вчерашние и сегодняшние, то при выборке данных из этой таблицы за сегодняшнюю дату, индекс может не использоваться. Дело в том, что оптимизатор оценил, что с помощью индекса ему не удастся выиграть в скорости: оптимизатор понял, что ему проще перебрать все сто строки таблицы, проверяя каждую на выполнение условия отбора, чем обратиться к индексу, получив указатели на целую половину строк из таблицы и уж потом их начать выбирать. Проще читать данные сразу из таблицы. Из-за низкой селективности индекса, то есть показателя того, что данных будет отобрано много по отношению к общему количеству данных, оптимизатор может принять решение индекс не использовать.
Иногда для таблицы может быть создано несколько индексов и при выборке данных из таблицы по нескольких условиям оптимизатор может принять решение об использовании не правильного индекса, а может и вовсе ни один из индексов не использовать. Но в подавляющем большинстве случаев, оптимизатор хорошо разбирает правильно написанный запрос и использует наиболее подходящие индексы для максимально быстрого доступа к нужным строкам.
Наш сегодняшний урок - это продолжение предыдущего поста. Рассмотрим использование хинтов на примере СУБД ORACLE и работать будем опять в программе SQL Developer.
Для управления тем, какие индексы использовать для доступа к таблицам, можно прямо в запросах вставлять хинты (hint – англ. «подсказка»).
С помощью хинтов можно не только подсказывать какие индексы каких таблиц использовать, но управлять запросом в целом, порядком выполнения его частей и т.д. Можно, например, указать выполнить ли сначала подзапрос, подготавливающий таблицу данных для последующего присоединения, или выполнять для каждой строки основной таблицы.
Хинты в запрос вставляются, начиная с символов /*+, и заканчивая символами */, то есть вписываются в SQL почти как многострочный комментарий:
Если хинт написан с ошибкой, то при выполнении запроса, ошибки не будет, хинт просто будет проигнорирован.
Рассмотрим пример использования хинта индекса. Сделаем выборку:
Получаем данные:
Прочитаем план запроса как мы делали это на прошлом уроке:
Согласно плану, запрос вызовет полный перебор строк таблицы PersonCanteenOrder (мы это поняли, так как в плане указано что к ней будет применён TABLE ACCESS FULL, с англ. - доступ к таблице полный) и найдёт 1035 записей и если учесть, что всего то в нашей таблице примерно 5,6 тысяч записей, то получается, что селективность небольшая. Но, все же, если планируется, что столовую будут посещать тысячи покупателей и любой из них должен иметь возможность быстро просматривать свои заказы, то нам хорошо бы иметь индекс для столбца PersonID. Создадим его. Если ты ещё не читал мой предыдущий пост, то вначале прочти его. Там мы научились создавать индексы. Итак, создадим индекс в таблице PERSONCANTEENORDER для столбца PersonID:
Пробуем ещё раз посмотреть план запроса:
Как видим, из-за низкой селективности ORACLE решает не использовать индекс, так как данных всего не много и с использованием индекса их отберется не существенно меньше.
При росте количества строк в этой таблице и количестве разных значений PersonID, в плане появится пункт, указывающий на использование индекса. ORACLE сам примет решение когда его нужно будет уже использовать. Но, все же, нам для примера нужно протестировать нашу возможность влияния на план запроса. Впишем в запрос хинт, указывающий на необходимость воспользоваться нашим индексом «PERSONCANTEENORDER_INDEX2» (не очень говорящее название, но таким мы создали индекс, см. скриншот выше).
Хинт, указывающий на необходимость воспользоваться индексом, называется index.
Получаем те же данные, но план запроса изменился:
Теперь мы видим, что доступ к данным в таблице будет осуществлен с помощью индекса, но для ORACLE сейчас это будет труднее, поэтому он его сначала и не брал.
Дорогой ученик, больше об индексах и других типов хинтов ты можешь найти на моем канале в Ютубе. Также, на моем канале ты найдёшь ещё много полезных видео-роликов про оптимизацию запросов. Я собрал большую базу знаний для тебя! В своих видео я даю доступ к тестовым базам данных, где ты можешь попробовать свои навыки не только в оптимизации, но и просто в написании запросов. Ты можешь поработать с другими базами данных! Я буду рад, если ты найдёшь эти видео полезными для себя!
Если есть Telegram, подписывайся на наш канал в Телеграмме. Несколько раз в неделю решаем вместе разные задачи по SQL, и ещё помогаю не выпадать из темы айти. Делюсь полезной информацией и техническим-юмором:)
А здесь ты можешь пройти наш курс обучения языку SQL и получить мощные знания и умения! Всего за 27 уроков до уровня специалиста, имеющего трёхлетний стаж. Много практики в реальной базе данных! С нашей поддержкой и проверкой твоих самостоятельный задач! Записывайся, буду тебя ждать!
