CREATE INDEX index_name ON tbl_name (col_name[(length)],...)

Команда CREATE INDEX в версиях MySQL до 3.22 не выполняет никаких действий. В версии 3.22 и более поздних CREATE INDEX соответствует команде ALTER TABLE в части создания индексов. See section 6.5.4 Синтаксис оператора ALTER TABLE .

Обычно все индексы создаются в таблице во время создания самой таблицы командой CREATE TABLE . See section 6.5.3 Синтаксис оператора CREATE TABLE . CREATE INDEX дает возможность добавить индексы к существующим таблицам.

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

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

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

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

Следует учитывать, что в версии MySQL 3.23.2 и более поздних для таблиц типа MyISAM можно добавлять индекс только для столбцов, которые могут принимать величины NULL или для столбцов BLOB/TEXT .

Чтобы получить более подробную информацию о том, как MySQL использует индексы, See section 5.4.3 Использование индексов в MySQL .

С помощью опции FULLTEXT можно индексировать только столбцы VARCHAR и TEXT и только в таблицах MyISAM . Эта возможность доступна только в версии MySQL 3.23.23 и выше. See section

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

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

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

Цикл по принципам работы с MySQL, более ранние материалы:

MySQL index и оптимизация запросов

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

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

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

INDEX можно создать сразу при создании таблицы или сделать это позже.

Сразу при создании таблицы это может быть так (резонно ожидаем самое большое количество запросов на выборку с ограничением по цене, поэтому индекс создаем для колонки PRICE):

CREATE TABLE REAL_ESTATE (type VARCHAR(20), city VARCHAR(20), floorspace INT, district VARCHAR(20), street VARCHAR(20), rentorsale VARCHAR(20), PRICE VARCHAR (20), INDEX (PRICE));

Другой способ создания индексов применим к уже существующим таблицам

CREATE INDEX PRICE on PEOPLE(PRICE);

Созданные для таблицы индексы можно легко посмотреть — PEOPLE в примере — имя таблицы

SHOW INDEXES FROM PEOPLE;

+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
| PEOPLE | 1 | PRICE | 1 | PRICE | A | 7 | NULL | NULL | YES | BTREE | | |
+———+————+———-+—————+————-+————+————-+———-+———+——+————+———+—————+
1 row in set (0.00 sec)

Удалим индекс

DROP INDEX PRICE on PEOPLE;

Records: 0 Duplicates: 0 Warnings: 0

UNIQUE MySQL INDEX

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

CREATE UNIQUE INDEX PRICE on PEOPLE(PRICE);

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Сейчас если просмотреть индексы в поле Non_unique будет другое значение

SHOW INDEXES FROM PEOPLE;

Порядок в проиндексированной колонке по умолчанию возрастающий, можно изменить его добавив DESC после ее имени — для примера команда выглядела бы так:

CREATE UNIQUE INDEX PRICE on PEOPLE(PRICE DESC);

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

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

Вновь удалим наш индекс для таблицы PEOPLE и создадим новый — в этот раз составной.

CREATE INDEX PRICE_AND_CITY on PEOPLE(PRICE, CITY);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Для запроса сразу выполним EXPLAIN

EXPLAIN SELECT PRICE, CITY FROM PEOPLE WHERE CITY = «Yekaterinburg» AND PRICE < 30000;

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

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

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

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

Это лучше понять на примере:

SELECT CITY, COUNT(*) FROM PEOPLE GROUP BY CITY;

SELECT PRICE, COUNT(*) FROM PEOPLE GROUP BY PRICE;

Смотрим получившиеся значения и указываем первым в составном индексе ту колонку, для которой в выводе число больше. За счет MySQL INDEX в этом случае будут сначала отфильтрованы значения по первой части и получено небольшое количество результатов. По ним поиск будет производить значительно легче и быстрее.

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

Создавать индексы только для медленных частых запросов
Использовать их там, где много SELECT-ов и мало UPDATE-ов
Периодически проверять логи медленных запросов
Удалять неиспользуемые индексы и обновлять существующие
Не использовать MySQL INDEX-ы совсем если нет большой нагрузки

Используется ли индекс для запроса и насколько оптимально используется можно вычислить при помощи

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

CREATE TABLE `phone` (`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, `country` DECIMAL(5,0) UNSIGNED NOT NULL, `area` DECIMAL(5,0) UNSIGNED NOT NULL, `number` DECIMAL(8,0) UNSIGNED NOT NULL, `extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

В данном примере, колонка ‘id’ является первичным ключом. Если при выполнении INSERT запроса явно не задать значение для этого поля, то оно будет увеличено автоматом (AUTO_INCREMENT).

id	country	area	number	extension
1	1	234	567890	NULL
2	44	9876	54321	42
3	61	3	90908200	NULL

INSERT INTO `phone` (`id`, `country`, `area`, `number`) VALUES (1, 1, 234, 567890);

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

После выполнения запроса, таблица будет выглядеть так:

id	country	area	number	extension
1	1	234	567890	NULL
2	44	9876	54321	42
3	61	3	90908200	NULL
4	1	234	567890	NULL

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

Прекрасно… однако номер телефона у записей 1 и 4 абсолютно идентичны. Что если мы хотим сделать поле phone тоже уникальным?

Уникальные индексы

Уникальные индексы работаю почти так же, как первичные ключи. Однако первичный ключ может быть только один, а уникальных индексов сколько угодно.

В нашем случае укажем что в таблице не может быть записи с одинаковыми данными в полях country, area, number и extension. Делаем это следующим образом:

ALTER TABLE `phone` ADD UNIQUE INDEX `ix_phone` (`country`, `area`, `number`, `extension`);

Название индекса (‘ix_phone’) указывать не обязательно. С тем же успехом, можем удалить таблицу и создать её заново:

DROP TABLE IF EXISTS `phone`; CREATE TABLE `phone` (`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, `country` DECIMAL(5,0) UNSIGNED NOT NULL, `area` DECIMAL(5,0) UNSIGNED NOT NULL, `number` DECIMAL(8,0) UNSIGNED NOT NULL, `extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `ix_phone` (`country`, `area`, `number`, `extension`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Уникальные индексы существуют и в других СУБД, но SQL синтаксис для их создания может отличаться.

Теперь давайте попробуем вставить запись, подставив уже существующие данные:

INSERT INTO `phone` (`country`, `area`, `number`, `extension`) VALUES (44, 9876, 54321, 42);

В результате, MySQL выдаст следующую ошибку:

Error Code: 1062 Duplicate entry "44-9876-54321-42" for key "ix_phone"

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

MySQL и NULL

Есть в MySQL одна особенность. Каждый отдельный NULL является уникальным значением; именно поэтому сравнение нужно осуществлять не так value = NULL, а так value IS NULL. К тому же, это так же распространяется и для значений в уникальных индексах.

Учитывая эту особенность, следующий INSERT запрос мы можем выполнять сколько угодно раз, и каждый раз в поле extension будет вставлен NULL (он считается уникальным для каждой отдельной записи):

INSERT INTO `phone` (`country`, `area`, `number`) VALUES (1, 234, 567890);

Да, это полностью рушит логику нашего уникального индекса.

Решение: убедитесь, что все поля в индексе не могут содержать NULL.

Несмотря на этот нюанс, уникальные индексы могут быть очень полезны, в том числе для сохранения целостности данных!

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

Индексы используются для ускорения выборки данных из таблиц базы данных. По сути дела, индекс в MySQL - это сортировка определённого поля в таблице. То есть если поле сделать индексом, то вся таблица будет отсортирована по этому полю. Почему это выгодно?

Допустим, в нашей таблице находится 1000000 записей. У каждой записи есть уникальный идентификатор ID . И, допустим, нам надо вытащить записть с ID = 530124 . Если нет индекса, то MySQL будет поочерёдно перебирать все записи в таблице, пока не найдёт нужную. В худшем случае, он будет вынужден перебрать 1000000 записей. Разумеется, это будет очень долго. А если бы был индекс (то есть поле было бы отсортировано), то выборка записи произошла бы в среднем в 100 000 раз быстрее. Как видите, выгода очевидна.

Однако, индексы обладают одним существенным изъяном, который не позволяет делать индексом каждое поле таблицы. Фактически, индекс - это ещё одна таблица, но просто с отсортированным соответствующим полем. То есть, делая индекс одного поля, Вы создаёте ещё одну точно такую же таблицу, которая будет занимать дополнительное место на диске.

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

Как сделать индекс в MySQL ?

Для первичных ключей (PRIMARY KEY ) индекс создаётся автоматически, а вот для других полей последовательность действий в PHPMyAdmin следующая:

И, напоследок, хочется сделать небольшое резюме, чтобы Вы поняли: "Когда надо создавать индексы MySQL ":

Если по полю очень часто идёт выборка, то его надо делать индексом.
Если в таблицу очень часто добавляются записи, и при этом выборка происходит редко (такое иногда бывает), то индексы делать не надо.

И ещё кое-что. Если вдруг Вы видите, что Ваши запросы на выборку очень сильно тормозят, то проанализируйте причину этого. Скорее всего, надо просто добавить индекс. В общем, тестируйте, и всё станет понятно.

Индексы применяются для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то 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 .