Главная » Весенняя одежда » Пример создания индекса mysql. Справочное руководство по MySQL

Пример создания индекса mysql. Справочное руководство по MySQL

Задача
Просмотр таблицы выполняется слишком медленно. Или вставка и обновление записей требуют слишком много времени.

Решение
Предложение ALTER TABLE умеет удалять и добавлять не только столбцы, но и индексы для этих столбцов. Подобные операции часто улучшают производительность базы данных. Обычно индексирование часто используемого столбца ускоряет выполнение предложений SELECT за счет отсутствия необходимости полного просмотра таблиц. В некоторых случаях пользу может принести и удаление индекса. При любом обновлении строки MySQL приходится обновлять все индексы, содержащие измененные столбцы. Если вы редкоиспользуете какой-то индекс, это может свидетельствовать о том, что таблица перегружена индексами, и удаление какого-то из них может повысить эффективность обработки таблицы.

Обсуждение
Для удобства работы начнем с создания нового экземпляра тестовой таблицы mytbl. Используем предложения DROP TABLE и CREATE TABLE для удаления существующей версии и воссоздания таблицы в ее первоначальной форме:

DROP TABLE mytbl;
CREATE TABLE mytbl
i INT,
c CHAR(1)
);

В начале главы мы применяли SHOW COLUMNS для наблюдения за результатами изменения таблицы.

Теперь будем исследовать изменения индекса и выводить результаты при помощи SHOW INDEX, а не SHOW COLUMNS. В настоящий момент в таблице нет индексов, так как они не были указаны в предложении CREATE TABLE:

Mysql> SHOW INDEX FROM mytbl;
Empty set (0.00 sec)

Добавление индексов
Существует четыре типа предложений, добавляющих индексы в таблицу:

ALTER TABLE имя_таблицы ADD PRIMARY KEY (список_столбцов);
ALTER TABLE имя_таблицы ADD UNIQUE имя_индекса (список_столбцов);
ALTER TABLE имя_таблицы ADD INDEX имя_индекса (список_столбцов);
ALTER TABLE имя_таблицы ADD FULLTEXT имя_индекса (список_столбцов);

Первое предложение добавляет первичный ключ (PRIMARY KEY), то есть индексированные значения должны быть уникальными и не содержать NULL. Второе предложение создает индекс, для которого значения должны быть уникальными (за исключением значений NULL, которые могут встречаться многократно). Третье предложение добавляет обычный индекс, в котором любое значение может появляться несколько раз. Последнее же создает специальный индекс FULLTEXT, который используется для просмотра текста.

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

Если не указать его, MySQL автоматически присвоит индексу имя.

Столбцы для индексирования указываются в параметре список_столбцов – списке из одного или нескольких имен столбцов, разделенных запятыми.

Рассмотрим два простых примера: первый создает одностолбцовый индекс для c, а второй – многостолбцовый индекс, включающий c и i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

Во многих случаях индексируемые столбцы должны быть объявлены как не-NULL. Например, если вы создадите mytbl как таблицу типа ISAM, то приведенные выше предложения ADD INDEX не выполнятся, так как таблицы ISAM не допускают NULL ни в каких типах индексов. Кроме того, индексы типа PRIMARY KEY не могут содержать значения NULL вне зависимости от типа таблицы. Если вы пытаетесь добавить индекс, а MySQL жалуется на проблемы, связанные с NULL, используйте предложение ALTER TABLE для изменения соответствующего столбца (столбцов) на не-NULL и повторите попытку создания индекса. Например, если попробовать сделать первичным ключом столбец i, возникнет ошибка:

Mysql>
ERROR 1171 at line 5: All parts of a PRIMARY KEY must be NOT NULL;
If you need NULL in a key, use UNIQUE instead

Необходимо предварительно переопределить столбец i так, чтобы он не допускал использования NULL:

Mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);

Все получилось.

А в первом случае, как видно из сообщения об ошибке, вместо первичного ключа можно было бы создать индекс UNIQUE в случае необходимости присутствия в индексе значений NULL.Удаление индексов Чтобы удалить индекс, используйте одно из предложений:

ALTER TABLE имя_таблицы DROP PRIMARY KEY;
ALTER TABLE имя_таблицы DROP INDEX имя_индекса;

Проще всего удалить индекс PRIMARY KEY, так как не нужно знать имя индекса:

ALTER TABLE mytbl DROP PRIMARY KEY;

Чтобы удалить индекс, не являющийся первичным ключом, необходимо указать его имя. Если вы не знаете, как называется индекс, используйте SHOW INDEX. Во избежание вывода чересчур длинных строк используем вертикальный вывод (\G):

Mysql> SHOW INDEX FROM mytbl\G
*************************** 1. row ***************************
Table: mytbl
Non_unique: 1
Key_name: c
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:
*************************** 2. row ***************************
Table: mytblNon_unique: 1
Key_name: c_2
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:
*************************** 3. row ***************************
Table: mytbl
Non_unique: 1
Key_name: c_2
Seq_in_index: 2
Column_name: i
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:

Значения Key_name и Seq_in_index соответствуют именам индексов и позициям столбцов в индексе. Теперь вы знаете, что в таблице mytbl есть одностолбцовый индекс с именем c и многостолбцовый индекс с именем c_2 (эти имена выбраны MySQL для двух созданных нами ранее индексов). Предложение, удаляющее индексы, будет таким:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

Как видите, в одном предложении ALTER TABLE можно выполнить несколько операций, которые необходимо разделять запятыми.

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

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

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

Быстро найти строки, соответствующие выражению WHERE .
Извлечь строки из других таблиц при выполнении объединений.
Найти величины MAX() или MIN() для заданного индексированного столбца. Эта операция оптимизируется препроцессором, который проверяет, не используете ли вы WHERE key_part_4 = константа, по всем частям составного ключа SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
Производить сортировку или группирование в таблице, если эти операции делаются на крайнем слева префиксе используемого ключа (например ORDER BY key_part_1,key_part_2). Если за всеми частями ключа следует DESC , то данный ключ читается в обратном порядке (see section 5.2.7 Как MySQL оптимизирует ORDER BY).
В некоторых случаях запрос можно оптимизировать для извлечения величин без обращения к файлу данных. Если все используемые столбцы в некоторой таблице являются числовыми и образуют крайний слева префикс для некоторого ключа, то чтобы обеспечить большую скорость, искомые величины могут быть извлечены непосредственно из индексного дерева: SELECT key_part3 FROM table_name WHERE key_part1=1

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

Mysql> 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 , показанные ниже:

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

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

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

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

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

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

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

В первой команде величина LIKE начинается с шаблонного символа. Во второй команде величина LIKE не является константой.

В версии MySQL 4.0 производится другая оптимизация на выражении LIKE . Если используется выражение... LIKE "%string%" и длина строки (string) больше, чем 3 символа, то MySQL будет применять алгоритм Турбо Бойера-Мура для инициализации шаблона для строки и затем использовать этот шаблон, чтобы выполнить поиск быстрее.

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

MySQL обычно использует тот индекс, который находит наименьшее количество строк. Индекс применяется для столбцов, которые сравниваются с помощью следующих операторов: =, >, >=,

Если индекс не охватывает все уровни 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 не использует индекс, даже если это возможно. Несколько примеров таких ситуаций приведено ниже:

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

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

В mysql таблицах используются следующие типы индексов:

Первичный ключ
Уникальный индекс
Обычный индекс
Полнотекстовый индекс

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

Первичный ключ

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

1. таблица не может иметь только один уникальный ключ;

2. значение ключа должно быть уникально в пределах таблицы;

3. первичный ключ не может иметь тип NULL.

Пример создания первичного ключа:

Создание первичного ключа при создании таблицы в описании полей

Пример 3:

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

Пример 5:

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

$query = "ALTER table auto ADD CONSTRAINT PRIMARY KEY (id_number)";
$result = $connection->query($query);
?>

Обычный и уникальный индексы

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

Объявление обычных индексов осуществляется при помощи ключевых слов key или index . Если же нужно объявить уникальный индекс, то перед key или index ставится ключевое слово unique .

Пример 6:

Создаем уникальный индекс

Удаление индексов

Удаление индексов осуществляется при помощи команды drop index, при помощи ее удаляются любые индексы.

Пример 8:

$connection = new mysqli("localhost","root","","mybase");
$query = "drop index model on auto";
$result = $connection->query($query);
?>

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name [ USING = index_type ] ON table_name (index_columns)

Ключевые слова UNIQUE , FULLTEXT и SPATIAL могут добавляться для отображения специфический свойств индекса. Если ни одно из низ не задано, создается не уникальный индекс. Оператор CREATE INDEX не может быть использован для создания индекса PRIMARY KEY , для этого необходимо использовать оператор ALTER TABLE .

ALTER TABLE table_name ADD PRIMARY KEY (index_columns) ALTER TABLE table_name ADD INDEX (index_columns) ALTER TABLE table_name ADD FULLTEXT (index_columns) ALTER TABLE table_name ADD UNIQUE (index_name) (index_columns) ALTER TABLE table_name ADD SPATIAL (index_columns)

Если указано несколько столбцов, то из имена следует разделять запятыми. Если имя индекса index_name не определено, оно создается автоматически на основе первого индексируемого столбца. Кроме того, оператор ALTER TABLE позволяет удалять индексы:

ALTER TABLE table_name DROP index_name ALTER TABLE table_name DROP PRIMARY KEY

Индексы можно удалять с помощью оператора DROP INDEX:

DROP INDEX index_name ON table_name DROP INDEX `PRIMARY` ON table_name

Для определения алгоритма индексирования можно использовать оператор USING . TYPE является синонимом USING , для таблиц типа MyISAM и InnoDB это может быть BTREE . Для таблиц типа MEMORY это может быть HASH или BTREE .

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

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

Список использованных источников:

Поль Дюбуа, MySQL, 3-е издание.

Материал взят с сайта:

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

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

Быстро найти строки, соответствующие выражению WHERE .
Извлечь строки из других таблиц при выполнении объединений.
Найти величины MAX() или MIN() для заданного индексированного столбца. Эта операция оптимизируется препроцессором, который проверяет, не используете ли вы WHERE key_part_4 = константа , по всем частям составного ключа . В этом случае 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 , то данный ключ читается в обратном порядке (see section 5.2.7 Как MySQL оптимизирует ORDER BY ).
В некоторых случаях запрос можно оптимизировать для извлечения величин без обращения к файлу данных. Если все используемые столбцы в некоторой таблице являются числовыми и образуют крайний слева префикс для некоторого ключа, то чтобы обеспечить большую скорость, искомые величины могут быть извлечены непосредственно из индексного дерева: SELECT key_part3 FROM table_name WHERE key_part1=1

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

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

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

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

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

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

В первой команде рассматриваются только строки с "Patrick" , а во второй - только строки с "Pat" .

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

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

В версии MySQL 4.0 производится другая оптимизация на выражении LIKE . Если используется выражение ... LIKE "%string%" и длина строки (string) больше, чем 3 символа, то MySQL будет применять алгоритм Турбо Бойера-Мура для инициализации шаблона для строки и затем использовать этот шаблон, чтобы выполнить поиск быстрее.

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

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

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

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

Есть еще вопросы или что-то непонятно - добро пожаловать на наш

Просмотров