Как индексировать MySQL для повышения производительности (перевод)

Если вы когда-либо работали с MySQL, Percona Server или MariaDB, вы, вероятно, задавались вопросом, как можно улучшить производительность экземпляров базы данных. Если вы искали совета по этому поводу, вы, вероятно, слышали об индексах.

Индексы в MySQL можно разделить на несколько типов:

  1. Индексы сбалансированного дерева (B-Tree) являются наиболее часто используемым типом индекса. Этот тип индекса может использоваться вместе с поисковыми запросами , которые используют =>>=<<=, и BETWEENключевые слова (также с LIKEзапросами).
  2. Пространственные (R-Tree) индексы могут использоваться вместе с геометрическими типами данных MySQL для индексации географических объектов.
  3. Хеш-индексы обычно используются только с запросами, в которых используются операторы поиска =или <=>. Они очень быстрые, но могут использоваться только при использовании механизма хранения MEMORY.
  4. Покрывающие индексы охватывают все столбцы, необходимые для выполнения запроса.
  5. Кластерные индексы хранят данные строк (обычно PRIMARY KEYsили, если они не существуют, UNIQUEиндексы).
  6. Многоколоночные (составные) индексы создаются для нескольких столбцов.
  7. Индексы префикса позволяют индексировать только префикс столбца. Таким образом, индексы не индексируют полное значение столбца. Их часто используют для экономии места.

Индексы B-Tree и MySQL

Индекс B-дерева — это наиболее часто используемый индекс в MySQL, MariaDB и Percona Server. Когда такой индекс используется, MySQL может использовать поисковые запросы , которые используют =>>=<<=, или BETWEENключевые слова или LIKEзапросы. Одна из основных причин, по которой такой индекс может быть добавлен в столбец, — это ускорить поисковые запросы. Когда используется индекс b-дерева, механизм хранения не просматривает всю таблицу в поисках соответствующих строк.

Вот несколько примеров запросов, которые могут использовать такой индекс:

SELECT * FROM demo_table WHERE column [ = | > | >= | < | <= ] 'value';
SELECT * FROM demo_table WHERE column_a BETWEEN 100 AND 200;
SELECT * FROM demo_table WHERE column LIKE 'value%';

Пространственные индексы и MySQL

Пространственные индексы (также иногда называемые индексами R-Tree) используются для доступа к пространственным (географическим) объектам. Чтобы использовать такие индексы, вы должны использовать функции ГИС, предоставляемые MySQL. Некоторые из этих функций включают в себя MBRContainsMBRCoversили MBREquals. Такие функции могут указывать, содержат ли минимальные ограничивающие прямоугольники одного параметра, покрывают или равны минимальным ограничивающим прямоугольникам другого параметра.

Чтобы добавить такой индекс в таблицу, используйте такое SPATIALключевое слово:

CREATE SPATIAL INDEX spatial_idx ON demo_table (demo_column);

или

ALTER TABLE demo_table ADD SPATIAL INDEX(spatial_idx);

Вот несколько примеров запросов, которые могут использовать такой индекс:

SELECT MBRContains(@variable_1, @variable_2);
SELECT MBRWithin(@variable_2, @variable_1);

Имейте в виду, что перед использованием таких переменных, как variable_1или variable_2в каком-либо качестве, вы должны сначала определить их ( WKT valueпараметр представляет хорошо известное значение в текстовом формате, представляющее геометрические объекты):

SET @variable_1 = ST_GeomFromText('WKT value');

Хеш-индексы и MySQL

У индексов хеширования в MySQL есть несколько явных преимуществ и недостатков. Основным преимуществом хэш-индексов является то, что они очень быстрые, но недостатком является то, что они используются только для сравнений на равенство и работают только с механизмом хранения MEMORY. Они также не поддерживают поиск по диапазону. Чтобы создать такой индекс для таблицы, воспользуйтесь USING HASHопцией в конце вашего запроса:

CREATE INDEX idx_name ON demo_table (demo_column) USING HASH;

Причина, по которой хеш-индексы такие быстрые, заключается в том, что хеш-индексы обычно очень компактны, а их первичный ключ обеспечивает доступ только к элементам в хеш-таблице. Однако это также означает, что вы не можете использовать поиск по диапазону. Хеш-индексы следует использовать только с операторами =или <=>.

Покрытие индексов в MySQL

При работе с MySQL иногда вы можете столкнуться с некоторыми угловыми случаями, когда требуется использовать индекс по-другому. Здесь на помощь приходят индексы покрытия.

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

SELECT column_1, column_2 FROM demo_table WHERE column_3 = 'value';

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

Кластерные индексы в MySQL

Кластерные индексы сами по себе не являются отдельным типом индексов MySQL. Это просто другой подход к хранению данных. Такие индексы хранят данные строк. Если в вашей таблице есть PRIMARY KEY, то PRIMARY KEYэто кластерный индекс. Если в вашей таблице нет PRIMARY KEY, кластерный индекс будет первым, UNIQUE INDEXвсе его ключевые столбцы определены как NOT NULL.

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

Кластерные индексы легко отличить. В MySQL такие индексы обычно представляют собой золотые ключи. Напротив, индексы сбалансированного дерева представлены ключами, выделенными серебристым цветом (в этом случае столбец с «золотым ключом» — это столбец с кластеризованным индексом, а столбец с «серебряным ключом» — это столбец со сбалансированным деревом. индекс):

Чтобы определить кластерный индекс при создании таблицы, добавьте AUTO_INCREMENTв свой столбец и определите его как PRIMARY KEY. Чтобы определить индекс сбалансированного дерева при создании таблицы, добавьте INDEXв свой столбец и укажите столбец, который вы хотите проиндексировать (вы также можете указать имя индекса, если хотите):

CREATE TABLE arctype ( clustered_index INT(255) NOT NULL 
AUTO_INCREMENT PRIMARY KEY, demo_index VARCHAR(255) NOT NULL, 
INDEX idx_name(demo_index));

UNIQUE INDEXсо всеми его ключевыми столбцами, определенными как, NOT NULLтакже может быть кластеризованным индексом. Как следует из названия, в этом случае все значения в столбце с символом a UNIQUE INDEXбудут уникальными (т.е. не будет дубликатов).

Составные индексы в MySQL

Составной индекс — это индекс, охватывающий несколько столбцов. Это довольно понятно: если у нас есть индекс для нескольких столбцов, у нас есть составной индекс. Вот что добавление такого индекса к таблице может выглядеть (в данном случае c1c2и c3имена столбцов):

CREATE INDEX composite_idx ON demo_table(c1,c2,c3);

Составные индексы могут быть полезны, если мы имеем дело с запросами, которые могут использовать индексированные поля для операций выбора, объединения или фильтрации. Например, составной индекс по столбцам c1и c2может быть полезно , если вы хотите , чтобы удовлетворить запрос следующим образом:

SELECT * FROM demo_table WHERE c1 = 5 AND c2 = 10;

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

Индексы префиксов в MySQL

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

Чтобы создать префиксный индекс для таблицы в MySQL, используйте такой запрос:

CREATE INDEX prefix_idx ON demo_table(column_name(length));

В данном случае prefix_idxэто имя индекса, demo_tableэто имя таблицы, column_nameэто имя столбца и lengthдлина индекса.

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

Во-первых, мы должны определить избирательность колонки. Мы делим отдельные (уникальные) значения в столбце на все значения в таблице. Идеальная селективность индекса — это значение 1:

SELECT COUNT(DISTINCT column) / COUNT(*) FROM demo_table;

Теперь мы должны попытаться сделать селективность индекса префикса как можно ближе к этому значению. Для этого мы могли бы выдать такой запрос:

SELECT COUNT(DISTINCT LEFT(column, 5)) / COUNT(*) AS selectivity_5;

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

Оригинал

Добавить комментарий