Индексы в 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
, за счет сокращения времени отклика при выполнении запроса в базу данных.
Сегодня мы познакомимся с таким инструментом как "Индексы
".
Использование индексов, позволит сократить время отклика от базы данных MySql
, что дает нам огромные преимущества при выборке данных из крупной БД
и окажет большую пользу при использовании в крупных, высоконагруженных проектах.
Давайте разберем, что такое индексы, какие индексы бывают и какие преимущества можно из них извлечь.
MySql Index
- это важный и полезный инструмент, который позволяет оптимизировать выборку из базы данных. Использование индексов значительно сокращает время выполнения запроса, что позволяет быстрее получать данные.
Ощутимую разницу при использовании MySql индексов
можно увидеть при работе с таблицами крупных размеров.
Индекс
представляет из себя структуру, в которой хранятся значения одного или нескольких столбца
[ов] таблицы и ссылок на строки, где эти значения расположены. Для хранения индексов чаще всего используются "бинарные деревья
".
Рассмотрим пример работы индексов
:
Ощутить преимущества работы индексов можно при выполнении SELECT
запроса с условием WHERE
.
У нас есть таблица, которая хранит информацию о пользователях.
В таблице есть колонки:
- id - autoincrement
- firstname - Имя
- lastname - Фамилия
- age - Возраст
Теперь по данной таблице сделаем выборку всех пользователей, у которых возраст составляет 20 лет.
Данный запрос будет выглядеть следующим образом:
SELECT * FROM users WHERE age = 20;
Данный запрос с виду не сложный и не должен оказывать нагрузку, однако если ваша БД размером более 1000 строк и количество таких запросов более одного, то выполнение данного запроса уже будет оказывать нагрузку на базу данных и затраченное время на выборку будет уже не тем, которое ожидалось.
А теперь выполним данный запрос на базе данных разметом в 600мб и содержащую 15 миллионов строк.
И так, запрос запущен и время выполнения данного запроса составляет - 10,562 сек.
Повторяю выполнение данного запроса еще 2 раза, для того, чтобы было понятно среднее время выполнения запроса:
Второй запуск - 10,359
Третий запуск - 10,715
Согласитесь, что ждать по 10 секунд результат запроса это довольно долго.
Теперь определим "индекс
" колонке age
и повторим запрос.
Время выполнения запроса составило - 0,031 сек, что значительно быстрее, чем было ранее.
Таким образом мы видим значительный прирост скорости при использовании индексов
.
Типы MySql индексов :
- PRIMARY KEY - Первичный ключ: основной ключ, который позволяет хранить свои значения как уникальные записи таблицы. Данный тип ключа должен присутствовать в таблице в единственном экземпляре. Обычно данный тип ключа определяют колонке с наименованием id .
- UNIQUE - Уникальный ключ: Частично похож на первичный ключ, за счет того, что значения колонки таблицы должны быть уникальными (не должны повторяться) и не долны быть равны NULL .
- Составной индекс : данный тип индекса включает в индексирование несколько полей. Данный тип индексирования обычно используется в запросах, в которых необходимо произвести выборку по нескольким полям (там где в условии WHERE встречается более одного параметра).
SELECT * FROM users WHERE age = 20 AND firstname = "Alex";
Как создать индекс в MySql?
Создать индекс можно несколькими способами.
Ниже приведен пример того, как создать индекс с помощью софта для администрирования MySql баз данных(БД ) - PhpMyAdmin и как создать индекс с помощью запроса в базу данных(БД ).
Как создать уникальный индекс в MySql?
Пример создания уникального индекса с помощью PhpMyAdmin и создание уникального индекса с помощью запроса:
Как создать составной индекс в MySql?
Пример создания составного индекса с помощью PhpMyAdmin и создание составного индекса с помощью запроса:
Как удалить MySql индекс?
Ряд недостатков при использовании индексов :
Если же индексы дают такой прирост во время выполнения запросов, так почему бы не определять индексы на все колонки таблицы?
- В процессе индексирования колонок MySql таблицы создаются дополнительные таблицы, в которых хранятся отсортированные данные. И такие данные занимают некий объем физической памяти.
- При выполнении ряда запросов наличие индексов может играть совершенно противоположную роль и увеличивать время выполнения запроса.
- При наличии индекса в таблице, INSERT запрос выполняется дольше чем в таблице, которая не содержит индексов.
- Использование индексов может оказывать и негатив при выполнении запроса с использованием выражения LIKE в условии WHERE . Например использование выражения LIKE по индексированному полю не даст ни какого прироста в скорости выполнения.
Наверняка при создании таблиц, одно из полей вы делали первичным ключом. По сути первичный ключ и есть уникальный идентификатор для каждой записи.
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 может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску.
Все индексы 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 существует многостолбцовый индекс, то соответствующие строки могут выбираться напрямую. В случае, когда по столбцам 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" , а во второй - только строки с "Pat" .
Следующие команды 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 обычно использует тот индекс, который находит наименьшее количество строк. Индекс применяется для столбцов, которые сравниваются с помощью следующих операторов: =, >, >=, и 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 не использует индекс, даже если это возможно. Несколько примеров таких ситуаций приведено ниже:
- Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице (в таких случаях просмотр таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска). Следует учитывать, что если подобный запрос использует LIMIT по отношению только к извлекаемой части строк, то MySQL будет применять индекс в любом случае, так как небольшое количество строк можно найти намного быстрее, чтобы вернуть результат.
- Если диапазон изменения индекса может содержать величины NULL при использовании выражений ORDER BY ... DESC .
| Есть еще вопросы или что-то непонятно - добро пожаловать на наш | |
|
|
Индексирования таблиц применяется для уменьшения времени поиска данных. Без индексов поиск происходит простым перебором всех значений таблицы, что занимает довольно продолжительное время.
В mysql таблицах используются следующие типы индексов:
- Первичный ключ
- Уникальный индекс
- Обычный индекс
- Полнотекстовый индекс
Кроме перечисленных выше индексов также используется полнотекстовый индекс , но в этой статье он не рассматривается.
Первичный ключ
Первичный ключ используется для однозначной идентификации записей в таблице. Из основных моментов касающихся первичного ключа следует отметить следующее:
1. таблица не может иметь только один уникальный ключ;
2. значение ключа должно быть уникально в пределах таблицы;
3. первичный ключ не может иметь тип NULL.
Пример создания первичного ключа:
Создание первичного ключа при создании таблицы в описании полей
Пример 3:
Если в качестве первичного ключа выступает текстовое поле, то нужно указывать в скобках количество символов участвующих в индексации.
Пример 5:
Если таблица уже создана, то добавить первичный индекс можно следующим образом.
|
|
Обычный и уникальный индексы
Кроме первичного индекса таблица также может содержать обычные и уникальные индексы. В отличии от первичного индекса этих индексов может быть несколько. Отличие обычного индекса от уникального состоит в том, что в уникальном индексе не может быть одинаковых значений.
Объявление обычных индексов осуществляется при помощи ключевых слов key или index . Если же нужно объявить уникальный индекс, то перед key или index ставится ключевое слово unique .
Пример 6:
Создаем уникальный индекс
Удаление индексов
Удаление индексов осуществляется при помощи команды drop index, при помощи ее удаляются любые индексы.
Пример 8:
|
$connection = new mysqli("localhost","root","","mybase"); |