Доброта лучше красоты (Г. Гейне).

Выбор лучших индексов для оптимизации запросов MySQL

5 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
1 апреля 2019
Выбор лучших индексов для оптимизации запросов MySQL
Представьте себе мир, в котором все запросы SQL выполняются быстро.

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

Основное внимание уделяется базам данных MySQL, MariaDB и Percona Server. Эта информация может относиться и к другим поставщикам баз данных, но в некоторых случаях может не подойти.

 

Какие индексы я должен создать для запроса SQL?

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

При создании индекса наиболее важными частями являются условия равенства в условиях WHERE и JOIN. В большинстве случаев такие условия, как name = ‘ AndreyEx’, позволят базе данных отфильтровать большинство строк из таблицы и пройти небольшое количество строк, чтобы получить требуемые результаты. Поэтому мы должны начать индексирование, добавив эти столбцы в индекс.

Затем вы должны изучить условия диапазона, но вы должны добавить только одно из них – самое избирательное, поскольку MySQL не может справиться с большим количеством из них. В некоторых случаях, когда нет условий диапазона, имеет смысл добавить столбцы GROUP BY/ORDER BY, предполагая, что упорядочение выполняется только в одном направлении (ASC/DESC).

Представьте себе мир, в котором все запросы SQL выполняются быстро.

В некоторых случаях также имеет смысл создать отдельный индекс, который содержит столбцы предложения ORDER BY, поскольку MySQL иногда выбирает его использование. Обратите внимание, что для этого индекс должен содержать все столбцы из предложения ORDER BY, и все они должны быть указаны в одном и том же порядке (ASC/DESC). Это не гарантирует, что оптимизатор базы данных выберет этот индекс, а не другие составные индексы, но стоит попробовать.

Кроме того, в некоторых случаях имеет смысл также добавлять столбцы из предложения SELECT к индексу, чтобы иметь полный индекс покрытия. Это актуально, только если индекс еще не «слишком большой». Что слишком велико? Ну, здесь нет официального правила, но скажем… 5-7 столбцов? Создание покрывающего индекса позволяет базе данных не только фильтровать с использованием индекса, но также извлекать информацию, требуемую предложением SELECT, непосредственно из индекса, что сохраняет драгоценные операции ввода-вывода.

Давайте посмотрим на пример, чтобы уточнить:

SELECT id, first_name, last_name, age from employees where first_name = ‘AndreyEx’ AND last_name = ‘Brack’ and age > 38 ORDER BY age ASC;

 

Для этого запроса мы начнем с добавления столбцов first_name и last_name , которые сравниваются с оператором равенства. Затем мы добавим столбец age, который сравнивается с условием диапазона. Здесь нет необходимости индексировать предложение ORDER BY, поскольку столбец age уже есть в индексе. И последнее, но не менее важное: мы добавим идентификатор из предложения SELECT в индекс, чтобы иметь индекс покрытия.

Таким образом, чтобы правильно проиндексировать этот запрос, вы должны добавить индекс:

employee (first_name, last_name, age, id)

Выше приведен очень упрощенный псевдоалгоритм, который позволит вам создавать простые индексы для довольно простых запросов SQL.

 

Что не нужно делать при индексации (или написании SQL-запросов)?

Мы собрали некоторые из наиболее распространенных ошибок, которые мы видим у программистов и администраторов баз данных при написании запросов и индексации своих таблиц.

 

Индексирование каждого столбца в таблице отдельно

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

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

Мы рекомендуем использовать составные индексы (описанные далее в этой статье), а не одноколонные индексы.

 

Оператор OR в условиях фильтрации

Рассмотрим этот запрос:

SELECT a, b FROM tbl WHERE a = 3 OR b = 8

 

Во многих случаях MySQL не сможет использовать индекс для применения условия OR, и в результате этот запрос не будет индексирован.

Поэтому мы рекомендуем избегать таких условий OR и рассмотрим разделение запроса на две части в сочетании с UNION DISTINCT (или, что еще лучше, UNION ALL, если вы знаете, что повторяющихся результатов не будет)

 

Порядок столбцов в индексе важен

Допустим, я передаю вам свою телефонную книгу контактов, которая упорядочена по имени контактного лица, и прошу вас подсчитать, сколько людей там по имени «AndreyEx» в книге. Вы возьмете книгу в обе руки и скажете «без проблем». Вы перейдете на страницу, содержащую все имена, начиная с AndreyEx, и начнете отсчитывать оттуда.

Теперь, допустим, я изменил назначение и передал вам телефонную книгу, упорядоченную по фамилии контакта, но попросил вас по-прежнему считать все контакты с именем «AndreyEx». Как бы вы подошли к этому? Ну, база данных почесывает голову и в этой ситуации.

Теперь давайте посмотрим на запрос SQL, чтобы продемонстрировать то же поведение с оптимизатором MySQL:

SELECT first_name, last_name FROM contacts WHERE first_name = ' AndreyEx';

 

Индекс (First_name, last_name) идеально подходит здесь, потому что индекс начинается с нашего состояния фильтрации и заканчивается еще один столбец в SELECT.

Но иметь обратные контакты индекса (last_name, first_name) довольно бесполезно, поскольку база данных не может использовать индекс для фильтрации, поскольку столбец, который нам нужен, является вторым в индексе, а не первым.

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

 

Добавление избыточных индексов

Индексы великолепны при попытке оптимизировать ваши запросы SQL, и они могут значительно повысить производительность.

Но они также имеют свои недостатки. Каждый создаваемый вами индекс должен обновляться и синхронизироваться, когда в ваших базах происходят изменения. Поэтому для каждого INSERT/UPDATE/DELETE в ваших базах данных должны быть обновлены все соответствующие индексы. Это обновление может занять некоторое время, особенно с большими таблицами/индексами.

Поэтому не создавайте индексы, если вы не знаете, что они вам понадобятся.

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 13

Если статья понравилась, то поделитесь ей в социальных сетях:

Читайте также

    Отправить ответ

    Войти с помощью: 
    avatar
      Подписаться  
    Уведомление о

    Сообщить об опечатке

    Текст, который будет отправлен нашим редакторам:

    Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

    badge
    Обратный звонок 1
    Отправить
    galka

    Спасибо! Ваша заявка принята

    close
    galka

    Спасибо! Ваша заявка принята

    close