MySQL index и оптимизация запросов. Использование индексов в MySQL MySQL index и оптимизация запросов

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 может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Для таблицы, содержащей 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 .

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 таблицах используются следующие типы индексов:

• Первичный ключ
• Уникальный индекс
• Обычный индекс
• Полнотекстовый индекс

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

Первичный ключ

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

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 индекс?


Ряд недостатков при использовании индексов :
Если же индексы дают такой прирост во время выполнения запросов, так почему бы не определять индексы на все колонки таблицы?

1. В процессе индексирования колонок MySql таблицы создаются дополнительные таблицы, в которых хранятся отсортированные данные. И такие данные занимают некий объем физической памяти.
2. При выполнении ряда запросов наличие индексов может играть совершенно противоположную роль и увеличивать время выполнения запроса.
3. При наличии индекса в таблице, INSERT запрос выполняется дольше чем в таблице, которая не содержит индексов.
4. Использование индексов может оказывать и негатив при выполнении запроса с использованием выражения LIKE в условии WHERE . Например использование выражения LIKE по индексированному полю не даст ни какого прироста в скорости выполнения.

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