В предыдущих статьях я часто упоминал про индексы в MySQL . и я обещал, что скоро о них расскажу. Так вот, это время пришло, и сегодня Вы узнаете об индексах MySQL , об их назначении и о том, как их создавать.
Индексы используются для ускорения выборки данных из таблиц базы данных. По сути дела, индекс в MySQL - это сортировка определённого поля в таблице. То есть если поле сделать индексом, то вся таблица будет отсортирована по этому полю. Почему это выгодно?
Допустим, в нашей таблице находится 1000000 записей. У каждой записи есть уникальный идентификатор ID . И, допустим, нам надо вытащить записть с ID = 530124 . Если нет индекса, то MySQL будет поочерёдно перебирать все записи в таблице, пока не найдёт нужную. В худшем случае, он будет вынужден перебрать 1000000 записей. Разумеется, это будет очень долго. А если бы был индекс (то есть поле было бы отсортировано), то выборка записи произошла бы в среднем в 100 000 раз быстрее. Как видите, выгода очевидна.
Однако, индексы обладают одним существенным изъяном, который не позволяет делать индексом каждое поле таблицы. Фактически, индекс - это ещё одна таблица, но просто с отсортированным соответствующим полем. То есть, делая индекс одного поля, Вы создаёте ещё одну точно такую же таблицу, которая будет занимать дополнительное место на диске.
Ещё один небольшой минус индексов в MySQL заключается в том, что запросы на вставку новых записей заставляют проводить сортировку таблицы заново. В результате, вставка новых записей будет происходить несколько дольше обычного. Но не забывайте, что в большиинстве случаев делать это приходится гораздо реже, чем делать выборку, поэтому данный минус не существенен.
Как сделать индекс в MySQL ?
Для первичных ключей (PRIMARY KEY ) индекс создаётся автоматически, а вот для других полей последовательность действий в PHPMyAdmin следующая:
И, напоследок, хочется сделать небольшое резюме, чтобы Вы поняли: "Когда надо создавать индексы MySQL ":
- Если по полю очень часто идёт выборка, то его надо делать индексом.
- Если в таблицу очень часто добавляются записи, и при этом выборка происходит редко (такое иногда бывает), то индексы делать не надо.
И ещё кое-что. Если вдруг Вы видите, что Ваши запросы на выборку очень сильно тормозят, то проанализируйте причину этого. Скорее всего, надо просто добавить индекс. В общем, тестируйте, и всё станет понятно.
Индексы в MySQL – это важный и полезный инструмент, который позволяет оптимизировать выборку из базы данных, значительно сокращая время на получение нужных данных.
При этом заметить разницу можно на очень больших таблицах, содержащих десятки и сотни тысяч строк. Если такие таблицы не имеют индекса полей, то при запросах на выборку будут перебираться все строки подряд, пока не будет найдено искомое значение.
Часто программисты, проектируя базу данных, не задумываются об индексах MySQL, однако, последствия этого начинают ощущаться позднее, когда проект становится популярным и посещаемым. Сайт начинает притормаживать, а его владельцы полагают, что всему виной плохой хостинг или слабый сервер. В итоге затраты на «железо» растут, хотя все, что необходимо сделать – это провести оптимизацию базы данных.
Индексация данных используется не только в MySQL. В повседневной жизни мы тоже находим ей применение для экономии времени. Так, например, каждый, кто хоть раз бывал в больших городских библиотеках, видел и пользовался картотекой, где все названия книг были отсортированы по первым буквам. Представьте себе ситуацию, если бы такой классификации не было. Найти книгу среди сотен тысяч других, пожалуй, нам было бы весьма проблематично.
Таким образом, индексы в MySQL – это определенная сортировка данных в таблице для ускорения поиска данных. Сортировка происходит на «низком» (машинном) уровне, и нам нет необходимости вникать в ее процессы. Все, что нам требуется – это указать MySQL, какие поля нужно индексировать.
Как определить, для каких полей нужно создавать индексы?
Индексы, прежде всего, нужно создавать по тем полям, которые часто попадают в условие «where» ваших sql-запросов.
Например, допустим, ваша таблица с товарами имеет следующую структуру:
Для формирования страниц, к примеру, вам часто приходится делать запросы для поиска товаров одной категории:
SELECT id, product_name FROM products WHERE cat_id = "5"
В этом случае для оптимизации запросов целесообразно создать индекс для поля cat_id. Первое поле – id всегда имеет уникальное значение и для него целесообразно создать «первичный ключ» (Primary Key).
Как создать индексы в базе данных MySQL
Для создания индекса в MySQL можно воспользоваться интерфейсом phpMyAdmin. Напротив нужного поля в режиме редактирования структуры вы можете увидеть различные опции по добавлению индексов для поля.
Выберите, к примеру, «добавить индекс» и он будет добавлен к выбранному полю.
Второй способ – это создать SQL-запрос по типу:
ALTER TABLE table_name ADD INDEX (index_col_name,...)
Например,
ALTER TABLE users ADD INDEX i_name (username);
Виды (типы) индексов в MySQL
- Первичный ключ (PRIMARY KEY) – это основной ключ, который в таблице может быть только один. Он позволяет идентифицировать уникальные записи в таблице. Значения, которые находятся в столбце, где поля имеют PRIMARY KEY, не могут повторяться. Нередко первичный ключ назначают для полей с идентификатором id.
- Уникальный ключ (UNIQUE) – по сути, это альтернатива первичному ключу: значения, которые содержатся в таких полях также не могут повторяться и иметь значение NULL.
- Составной индекс – позволяет включать в индекс несколько полей, по которым часто происходит выборка. Например, если в условиях часто фигурирует два параметра:
SELECT username FROM users WHERE city = "5" AND age > "18"
При использовании таких запросов составной индекс по полям city и age поможет ускорить выборку данных.
Недостатки использования индексов:
- За все приходится платить, не исключение и индексы в MySQL. В процессе индексирования MySQL создает дополнительные таблицы, в которых хранятся отсортированные данные. Однако, в большинстве случаев выгоды от быстродействия более ощутимы, чем издержки физической памяти.
- Запросы на вставку данных в таблицу выполняются чуть дольше, поскольку MySQL требуется создать новые индексы и реорганизовать данные в предназначенных для этого таблицах. Но поскольку запросы на вставку выполняются реже, чем на извлечение данных, то серьезных потерь это обычно не приносит.
В целом, как вы можете видеть, достоинства от использования индексов значительно перевешивают небольшое количество недостатков. На больших и посещаемых проектах без использования индексов не обойтись.
Индексирования таблиц применяется для уменьшения времени поиска данных. Без индексов поиск происходит простым перебором всех значений таблицы, что занимает довольно продолжительное время.
В mysql таблицах используются следующие типы индексов:
- Первичный ключ
- Уникальный индекс
- Обычный индекс
- Полнотекстовый индекс
Кроме перечисленных выше индексов также используется полнотекстовый индекс , но в этой статье он не рассматривается.
Первичный ключ
Первичный ключ используется для однозначной идентификации записей в таблице. Из основных моментов касающихся первичного ключа следует отметить следующее:
1. таблица не может иметь только один уникальный ключ;
2. значение ключа должно быть уникально в пределах таблицы;
3. первичный ключ не может иметь тип NULL.
Пример создания первичного ключа:
Создание первичного ключа при создании таблицы в описании полей
Пример 3:
Если в качестве первичного ключа выступает текстовое поле, то нужно указывать в скобках количество символов участвующих в индексации.
Пример 5:
Если таблица уже создана, то добавить первичный индекс можно следующим образом.
|
|
Обычный и уникальный индексы
Кроме первичного индекса таблица также может содержать обычные и уникальные индексы. В отличии от первичного индекса этих индексов может быть несколько. Отличие обычного индекса от уникального состоит в том, что в уникальном индексе не может быть одинаковых значений.
Объявление обычных индексов осуществляется при помощи ключевых слов key или index . Если же нужно объявить уникальный индекс, то перед key или index ставится ключевое слово unique .
Пример 6:
Создаем уникальный индекс
Удаление индексов
Удаление индексов осуществляется при помощи команды drop index, при помощи ее удаляются любые индексы.
Пример 8:
|
$connection = new mysqli("localhost","root","","mybase"); |
Команда 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
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-е издание.