Mysql создать индекс для таблицы. Использование индексов в MySQL

What are Index?

Nobody likes slow systems.

High system performance is of prime importance in almost all database systems .

Most businesses invest heavily in hardware so that data retrievals and manipulations can be faster.

But there is limit to hardware investments a business can make.

Optimizing your database is a cheaper and better solution.

Towards this end we can use INDEXES.

The slowness in the response time is usually due to the records being stored randomly in database tables.
Search queries have to loop through the entire randomly stored records one after the other to locate the desired data.
This results in poor performance databases when it comes to retrieving data from large tables
Indexes come in handy in such situations. Indexes sort data in an organized sequential way.Think of an index as an alphabetically sorted list. It is easier to lookup names that have been sorted in alphabetical order than ones that are not sorted.
INDEX"s are created on the column(s) that will be used to filter the data.
Using indexes on tables that are frequently updated can result in poor performance. This is because MySQL creates a new index block every time that data is added or updated in the table. Generally, indexes should be used on tables whose data does not change frequently but is used a lot in select search queries.

Create index basic syntax

Indexes can be defined in 2 ways

1. At the time of table creation

2. After table has been created

For our myflixdb we expect lots of searches to the database on full name.

We will add the "full_names" column to Index in a new table "members_indexed".

The script shown below helps us to achieve that.

CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT, `full_names` varchar(150) DEFAULT NULL, `gender` varchar(6) DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `physical_address` varchar(255) DEFAULT NULL, `postal_address` varchar(255) DEFAULT NULL, `contact_number` varchar(75) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;

Execute the above SQL script in MySQL workbench against the "myflixdb".

Refreshing the myflixdb shows the newly created table named members_indexed.

" Note " members_indexed table has "full_names" in the indexes node.

As the members base expand and the number of records increases , search queries on the members_indexed table that use the WHERE and ORDER BY clauses will be much faster compared to the ones performed the members table without the index defined.

Add index basic syntax

The above example created the index when defining the database table. Suppose we already have a table defined and search queries on it are very slow. They take too long to return the results. After investigating the problem, we discover that we can greatly improve the system performance by creating INDEX on the most commonly used column in the WHERE clause.

We can use following query to add index

CREATE INDEX id_index ON table_name(column_name);

Let"s suppose that search queries on the movies table are very slow and we want to use an index on the "movie title" to speed up the queries, we can use the following script to achieve that.

CREATE INDEX `title_index` ON `movies`(`title`);

Executing the above query creates an index on the title field in the movies table.

This means all the search queries on the movies table using the "title" will be faster.

Search queries on other fields in the movies table will however still are slower compared to the ones based on the indexed field.

Note you can create indexes on multiple columns if necessary depending on the fields that you intend to use for your database search engine.

If you want to view the indexes defined on a particular table, you can use the following script to do that.

SHOW INDEXES FROM table_name;

Let"s now take a look at all the indexes defined on the movies table in the myflixdb.

SHOW INDEXES FROM `movies`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.

Note the primary and foreign keys on the table have already been indexed by MySQL. Each index has its own unique name and the column on which it is defined is shown as well.

Drop index basic syntax

The drop command is used to remove already defined indexes on a table.

There may be times when you have already defined an index on a table that is frequently updated. You may want to remove the indexes on such a table to improve the UPDATE and INSERT queries performance. The basic syntax used to drop an index on a table is as follows.

DROP INDEX `index_id` ON `table_name`;

Let"s now look at a practical example.

DROP INDEX ` full_names` ON `members_indexed`;

Executing the above command drops the index with id ` full_names ` from the members_indexed table.

Summary

Indexes are very powerful when it comes to greatly improving the performance of MySQL search queries.
Indexes can be defined when creating a table or added later on after the table has already been created.
You can define indexes on more than one column on a table.
The SHOW INDEX FROM table_name is used to display the defined indexes on a table.
The DROP command is used to remove a defined index on a given table.

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-ы совсем если нет большой нагрузки

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

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

В 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);
?>

В работе проекта очень важным фактором является скорость его работы.
Одним из методов повышение скорости работы проекта является: ускорение времени выполнение запроса БД MySql , за счет сокращения времени отклика при выполнении запроса в базу данных.
Сегодня мы познакомимся с таким инструментом как "Индексы ".
Использование индексов, позволит сократить время отклика от базы данных MySql , что дает нам огромные преимущества при выборке данных из крупной БД и окажет большую пользу при использовании в крупных, высоконагруженных проектах.
Давайте разберем, что такое индексы, какие индексы бывают и какие преимущества можно из них извлечь.

MySql Index - это важный и полезный инструмент, который позволяет оптимизировать выборку из базы данных. Использование индексов значительно сокращает время выполнения запроса, что позволяет быстрее получать данные.
Ощутимую разницу при использовании MySql индексов можно увидеть при работе с таблицами крупных размеров.
Индекс представляет из себя структуру, в которой хранятся значения одного или нескольких столбца [ов] таблицы и ссылок на строки, где эти значения расположены. Для хранения индексов чаще всего используются "бинарные деревья ".

Рассмотрим пример работы индексов :
Ощутить преимущества работы индексов можно при выполнении SELECT запроса с условием WHERE .
У нас есть таблица, которая хранит информацию о пользователях.
В таблице есть колонки:

id - autoincrement
firstname - Имя
lastname - Фамилия
age - Возраст

Первое поле id является авто инкрементирующим и всегда уникальным полем, по этому ему по умолчанию присваиваем индекс.
Теперь по данной таблице сделаем выборку всех пользователей, у которых возраст составляет 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 по индексированному полю не даст ни какого прироста в скорости выполнения.

Мы разобрали только часть основных недостатков при использовании индексов. Реальное количество может отличаться.

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

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

Категории

Популярное