ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
(1 оценок, среднее: 5,00 из 5)
Загрузка...
Представьте себе мир, в котором все запросы SQL выполняются быстро.
В этой статье не будут детализированы все внутренности алгоритма, а скорее будут изложены основные и важные аспекты индексации в простых терминах.
Кроме того, и самое главное, мы представим практические примеры для правильной индексации ваших таблиц и запросов, опираясь на набор правил, а не на догадки.
Основное внимание уделяется базам данных MySQL, MariaDB и Percona Server. Эта информация может относиться и к другим поставщикам баз данных, но в некоторых случаях может не подойти.
Как правило, MySQL может использовать только один индекс для каждой таблицы в запросе. Поэтому нет смысла создавать более одного индекса для каждого запроса. Предпочтительно, чтобы одни и те же индексы соответствовали как можно большему количеству запросов, так как это уменьшит нагрузку на базу данных при вставке или обновлении данных (что также требует обновления индексов).
При создании индекса наиболее важными частями являются условия равенства в условиях WHERE и JOIN. В большинстве случаев такие условия, как name = ‘ AndreyEx’, позволят базе данных отфильтровать большинство строк из таблицы и пройти небольшое количество строк, чтобы получить требуемые результаты. Поэтому мы должны начать индексирование, добавив эти столбцы в индекс.
Затем вы должны изучить условия диапазона, но вы должны добавить только одно из них – самое избирательное, поскольку MySQL не может справиться с большим количеством из них. В некоторых случаях, когда нет условий диапазона, имеет смысл добавить столбцы GROUP BY/ORDER BY, предполагая, что упорядочение выполняется только в одном направлении (ASC/DESC).
В некоторых случаях также имеет смысл создать отдельный индекс, который содержит столбцы предложения 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.
Мы собрали некоторые из наиболее распространенных ошибок, которые мы видим у программистов и администраторов баз данных при написании запросов и индексации своих таблиц.
В большинстве случаев MySQL не сможет использовать более одного индекса для каждой таблицы в запросе.
Поэтому при создании отдельного индекса для каждого столбца в таблице база данных обязана выполнять только одну из операций поиска с использованием индекса, а остальные из них будут значительно медленнее, поскольку база данных не может использовать индекс для выполнить их.
Мы рекомендуем использовать составные индексы (описанные далее в этой статье), а не одноколонные индексы.
Рассмотрим этот запрос:
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 в ваших базах данных должны быть обновлены все соответствующие индексы. Это обновление может занять некоторое время, особенно с большими таблицами/индексами.
Поэтому не создавайте индексы, если вы не знаете, что они вам понадобятся.
Кроме того, мы настоятельно рекомендуем время от времени анализировать вашу базу данных в поисках любых избыточных индексов, которые можно удалить.
