Использование индексов в MySQL

Индексы применяются для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице, начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то MySQL может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску.

Синтаксис оператора CREATE INDEX

CREATE [UNIQUE|FULLTEXT] INDEX index_name

ON tbl_name (col_name[(length)],... )

Обычно все индексы создаются в таблице во время создания самой таблицы командой CREATE TABLE. Команда CREATE INDEX дает возможность добавить индексы к существующим таблицам. Список столбцов в форме (col1,col2,...) создает индекс для нескольких столбцов. Величины индексов формируются путем конкатенации величин указанных столбцов.

Для столбцов типов CHAR и VARCHAR с помощью параметра col_name(length) могут создаваться индексы, для которых используется только часть столбца (для столбцов BLOB и TEXT нужно указывать длину). Команда, приведенная ниже, создает индекс, используя первые 10 символов столбца name:

CREATE INDEX part_of_name ON customer (name(10));

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

Все индексы MySQL (PRIMARY, UNIQUE, и INDEX) хранятся в виде B- деревьев. Строки автоматически сжимаются с удалением пробелов в префиксах и оконечных пробелов.

Индексы используются для того, чтобы:

● Быстро найти строки, соответствующие выражению WHERE.

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

● Найти величины MAX() или MIN() для заданного индексированного

столбца. Эта операция оптимизируется препроцессором, который проверяет, не используете ли вы WHERE key_part_4 = константа, по всем частям составногоключа < N.

В этом случае MySQL сделает один просмотр ключа и заменит выражение константой MIN(). Если все выражения заменяются константой, запрос моментально вернет результат:

● SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

● Производить сортировку или группирование в таблице, если эти операции делаются на крайнем слева префиксе используемого ключа (например ORDER BY key_part_1,key_part_2). Если за всеми частями ключа следует DESC, то данный ключ читается в обратном порядке.

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

● SELECT key_part3 FROM table_name WHERE key_part1=1

Предположим, что вызывается следующий оператор SELECT:

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Если по столбцам col1 и col2 существует многостолбцовый индекс, то соответствующие строки могут выбираться напрямую. В случае, когда по столбцам col1 и col2 существуют раздельные индексы, оптимизатор пытается найти наиболее ограничивающий индекс путем определения, какой индекс найдет меньше строк, и использует данный индекс для выборки этих строк. Если данная таблица имеет многостолбцовый индекс, то любой крайний слева префикс этого индекса может использоваться оптимизатором для нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3), то существует потенциальная возможность индексированного поиска по (col1), (col1,col2) и (col1,col2,col3).

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

SELECT * FROM tbl_name WHERE col1=val1;

SELECT * FROM tbl_name WHERE col2=val2;

SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Если индекс существует по (col1,col2,col3), то только первый показанный выше запрос использует данный индекс. Второй и третий запросы действительно включают индексированные столбцы, но (col2) и (col2,col3) не являются крайней слева частью префиксов (col1,col2,col3).

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

SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";

SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

В первой команде рассматриваются только строки с "Patrick" <= key_col <

"Patricl", а во второй - только строки с "Pat" <= key_col < "Pau".

Следующие команды SELECT не будут использовать индексы:

SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";

SELECT * FROM tbl_name WHERE key_col LIKE other_col;

В первой команде величина LIKE начинается с шаблонного символа. Во второй команде величина LIKE не является константой. При поиске с использованием column_name IS NULL будут использоваться индексы, если column_name является индексом.

MySQL обычно использует тот индекс, который находит наименьшее количество строк. Индекс применяется для столбцов, которые сравниваются с помощью следующих операторов: =, >, >=, <, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something%. Если индекс не охватывает все уровни AND в выражении WHERE, то он не применяется для оптимизации данного запроса. Другими словами: чтобы индекс можно было использовать, префикс этого индекса должен входить в каждую группу AND.

Следующие выражения WHERE используют индексы:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

... WHERE index=1 OR A=10 AND index=2 /* индекс = 1 ИЛИ индекс = 2 */

... WHERE index_part1='hello' AND index_part_3=5

/* оптимизировано как "index_part1='hello'" */

... WHERE index1=1 and index2=2 or index1=3 and index3=3;

/* Можно использовать индекс по index1, но не по index2 или index 3 */

Следующие выражения WHERE неиспользуют индексы:

... WHERE index_part2=1 AND index_part3=2

/* index_part_1 не используется */

... WHERE index=1 OR A=10

/* Индекс не используется в обеих частях AND */

... WHERE index_part1=1 OR index_part2=10

/* Нет индекса, покрывающего все строки*/

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

o Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице (в таких случаях просмотр таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска). Следует учитывать, что если подобный запрос использует LIMIT по отношению только к извлекаемой части строк, то MySQL будет применять индекс в любом случае, так как небольшое количество строк можно найти намного быстрее, чтобы вернуть результат.

o Если диапазон изменения индекса может содержать величины NULL

при использовании выражений ORDER BY ... DESC.

Индексы столбцов

В MySQL могут быть проиндексированы столбцы всех типов.

Использование индексов на соответствующих столбцах представляет собой

хороший способ ускорения выполнения операций SELECT.

Максимальное количество ключей и максимальная длина индексов

определяется обработчиком таблиц. Можно иметь по меньшей мере 16 ключей

на всех обработчиках таблиц и общую длину индексов по меньшей мере 256

байтов.

Для столбцов типов CHAR и VARCHAR можно индексировать префикс

столбца. Это намного быстрее и требует меньше дискового пространства, чем

индексация всего столбца. Используемый в команде CREATE TABLE синтаксис

для индексации префикса столбца выглядит примерно так:

KEY index_name (col_name(length))

В следующем примере создается индекс для первых 10 символов в столбце

name:

CREATE TABLE test (

name CHAR(200) NOT NULL,

KEY index_name (name(10)));

Для столбцов типа BLOB и TEXT индексировать необходимо префикс столбца. Нельзя индексировать столбец целиком. Можно также создавать специальные индексы FULLTEXT. Они используются для полнотекстового поиска. Полнотекстовые индексы FULLTEXT поддерживают только таблицы типа MyISAM. Они могут создаваться только по столбцам VARCHAR и TEXT. Индексация всегда производится для целого столбца, а частичная индексация не поддерживается.

Многостолбцовые индексы

MySQL может создавать индексы по нескольким столбцам. Индекс может включать в себя до 15 столбцов (на столбцах CHAR и VARCHAR можно также использовать префикс столбца в качестве части индекса) Многостолбцовый индекс может рассматриваться как упорядоченный массив, содержащий величины, созданные конкатенацией величин проиндексированных столбцов.

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

Предположим, создается следующая таблица:

CREATE TABLE test (

id INT NOT NULL,

last_name CHAR(30) NOT NULL,

first_name CHAR(30) NOT NULL,

PRIMARY KEY (id),

INDEX name (last_name,first_name));

Индекс name является индексом по столбцам last_name и first_name. Этот

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

области для last_name или для обоих столбцов last_name и first_name. Таким

образом, индекс name будет использоваться в следующих запросах:

SELECT * FROM test WHERE last_name="Widenius";

SELECT * FROM test WHERE last_name="Widenius"

AND first_name="Michael";

SELECT * FROM test WHERE last_name="Widenius"

AND (first_name="Michael" OR first_name="Monty");

SELECT * FROM test WHERE last_name="Widenius"

AND first_name >="M" AND first_name < "N";

Порядок выполнения работы

В соответствии с БД, разработанной в ходе выполнения предыдущих лабораторных работ и пользуясь приведенными выше способам оптимизации, поддерживаемыми СУБД MySQL, необходимо выполнить следующие действия:

(1) Используя оператор EXPLAIN, произвести анализ запроса с использованием нескольких таблиц.

(2) Произвести оптимизацию структуры БД, добавив индексы в таблицы.

(3) Добавить многостолбцовые индексы.

Контрольные вопросы

1) Знать возможности оптимизации БД.

2) Уметь анализировать запросы, используя оператор EXPLAIN.

3) Как MySQL оптимизирует LEFT JOIN?

4) Для чего применяются индексы?

5) Как создать индекс по нескольким столбцам?

6) Как подсчитать общее количество записей, возвращаемое запросом?