Индексы в MySQL.

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

Часто программисты, проектируя базу данных, не задумываются об индексах MySQL, однако, последствия этого начинают ощущаться позднее, когда проект становится популярным и посещаемым. Сайт начинает притормаживать, а его владельцы полагают, что всему виной плохой хостинг или слабый сервер. В итоге затраты на «железо» растут, хотя все, что необходимо сделать – это провести оптимизацию базы данных.

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

Таким образом, индексы в MySQL – это определенная сортировка данных в таблице для ускорения поиска данных. Сортировка происходит на «низком» (машинном) уровне, и нам нет необходимости вникать в ее процессы. Все, что нам требуется – это указать MySQL, какие поля нужно индексировать.

Как определить, для каких полей нужно создавать индексы?

Индексы, прежде всего, нужно создавать по тем полям, которые часто попадают в условие «where» ваших sql-запросов.

Например, допустим, ваша таблица с товарами имеет следующую структуру:

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

1

SELECT id, product_name FROM products WHERE cat_id = '5'

В этом случае для оптимизации запросов целесообразно создать индекс для поля cat_id. Первое поле – id всегда имеет уникальное значение и для него целесообразно создать «первичный ключ» (Primary Key).

Как создать индексы в базе данных MySQL.

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

Выберите, к примеру, «добавить индекс» и он будет добавлен к выбранному полю.

Второй способ – это создать SQL-запрос по типу:

1

ALTER TABLE table_name ADD INDEX [index_name] (index_col_name,...)

Например,

1

ALTER TABLE users ADD INDEX i_name (username);

Виды (типы) индексов в MySQL

Первичный ключ (PRIMARY KEY) – это основной ключ, который в таблице может быть только один. Он позволяет идентифицировать уникальные записи в таблице. Значения, которые находятся в столбце, где поля имеют PRIMARY KEY, не могут повторяться. Нередко первичный ключ назначают для полей с идентификатором id.

Уникальный ключ (UNIQUE) – по сути, это альтернатива первичному ключу: значения, которые содержатся в таких полях также не могут повторяться и иметь значение NULL.

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

1

SELECT username FROM users WHERE city = '5' AND age > '18'

При использовании таких запросов составной индекс по полям city и age поможет ускорить выборку данных.

Недостатки использования индексов:

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

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

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

• Как обновить значения поля с типом ENUM в таблице базы данных (MySQL)
• Получение случайных записей из базы данных.
• MySQL LIKE.
• DISTINCT MySQL.
• MySQL TIMESTAMP.