Индексы в mysql

Сложные фильтры и сортировки

В еще более сложных случаях выборки включают дополнительные фильтры либо сортировки. Допустим, мы хотим выбрать все статьи, созданные не позднее месяца назад. А статистику показать для них только за последний день. Только для тех публикаций, у которых набрано более 15 тыс. уникальных посещений. И результат отсортировать по просмотрам:

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

+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | date          | date    | 4       | NULL              | 26384 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.s.article_id |     1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+

Индекс date позволит отфильтровать таблицу статистики до 26 тыс. записей. Каждую из которых придется проверить на соответствие другим условиям (количество уникальных посетителей более 15 тыс.).

Сортировку по просмотрам Mysql будет в любом случае делать самостоятельно. Индексы тут не помогут, т.к. сортируем динамические значения (результат операции GROUP BY).

Поэтому наша задача — выбрать индекс, который позволит максимально сократить выборку по таблице articles_stats используя фильтр s.date = ‘2017-05-14’ AND s.uniques > 15000.

Создадим индекс на обе колонки из первого пункта:

Тогда Mysql сможет использовать этот индекс для фильтрации таблицы статистики:

+----+-------------+-------+--------+---------------+--------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key          | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+---------------+--------------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | date_uniques  | date_uniques | 9       | NULL              | 1681 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,ts_id | PRIMARY      | 4       | test.s.article_id |    1 | Using where                                  |
+----+-------------+-------+--------+---------------+--------------+---------+-------------------+------+----------------------------------------------+

Документ

Как сделать запись в базу данных mysql, используя php-код Индекс	Условие
Ссылка (Кластерный)	Всегда.В индекс входят поля независимых разделителей, которые разделяют этот документ.
Дата + Ссылка	Всегда.
Номер + Ссылка	Свойство «Длина номера» не равно 0.
Реквизит + Ссылка	Для реквизита «Реквизит» свойство «Индексировать» установлено в значение «Индексировать».
Реквизит + Дата + Ссылка	Для реквизита «Реквизит» свойство «Индексировать» установлено в значение «Индексировать с доп. упорядочиванием».
Реквизит	Документ включен в критерий отбора через реквизит «Реквизит».
ПрефиксНомера + Номер + Ссылка	Свойство «Длина номера» не равно 0.

Вводные

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

+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type         | Null | Key | Default           | Extra                       |
+-------+--------------+------+-----+-------------------+-----------------------------+
| id    | int(11)      | NO   | PRI | 0                 |                             |
| ts    | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| title | varchar(512) | YES  |     | NULL              |                             |
+-------+--------------+------+-----+-------------------+-----------------------------+

Данные со статистикой хранятся в другой таблице с такой структурой:

+------------+---------+------+-----+------------+----------------+
| Field      | Type    | Null | Key | Default    | Extra          |
+------------+---------+------+-----+------------+----------------+
| url_id     | int(11) | NO   | PRI | NULL       | auto_increment |
| article_id | int(11) | NO   |     | 0          |                |
| date       | date    | NO   |     | 0000-00-00 |                |
| pageviews  | int(11) | YES  |     | NULL       |                |
| uniques    | int(11) | YES  |     | NULL       |                |
+------------+---------+------+-----+------------+----------------+

Обратите внимание, что во второй таблице первичный ключ — это url_id. Это идентификатор ссылки на статью

Т.е. у одной статьи может быть несколько разных ссылок, и для каждой из них мы будем собирать статистику. Колонка article_id соответствует колонке id из первой таблицы. Сама статистика очень простая — количество просмотров и уникальных посетителей в день.

Дополнительные фильтры

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

В этом случае, Mysql снова не сможет подобрать индекс для таблицы статистики:

+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
|  1 | SIMPLE      | s     | ALL    | article_id    | NULL    | NULL    | NULL              | 676786 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.s.article_id |      1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+

Логика выбора индекса тут такая же, как и в предыдущем примере. Необходимо подобрать индекс, который позволит быстро отфильтровать таблицу статистики по дате:

Теперь запрос будет использовать индексы на обе таблицы:

+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | ref    | article_id,date | date    | 4       | const             | 2996 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY         | PRIMARY | 4       | test.s.article_id |    1 |                                              |
+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+

ALTER command to add and drop INDEX

There are four types of statements for adding indexes to a table −

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − This statement adds a PRIMARY KEY, which means that the indexed values must be unique and cannot be NULL.

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement creates an index for which the values must be unique (except for the NULL values, which may appear multiple times).

• ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once.

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.

The following code block is an example to add index in an existing table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can drop any INDEX by using the DROP clause along with the ALTER command.

Try out the following example to drop the above-created index.

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

You can drop any INDEX by using the DROP clause along with the ALTER command.

Детство

Агрегационные запросы

Предыдущий пример носит более лабораторный характер. Более приближенный к практике запрос — это выборка статистики сразу по нескольким статьям:

Однако в этом случае Mysql будет вести себя точно также. Он оценит какие индексы можно использовать из каждой таблицы. EXPLAIN покажет:

+----+-------------+-------+--------+---------------+------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+---------------+------------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | article_id    | article_id | 4       | NULL              | 2030 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY    | 4       | test.s.article_id |    1 | Using index                                  |
+----+-------------+-------+--------+---------------+------------+---------+-------------------+------+----------------------------------------------+

Таблицы будут обработаны в другом порядке. Сначала будет сделана выборка всех подходящих значений из таблицы статистики. А затем из таблицы с названиями.

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

Составные индексы

MySQL может использовать только один индекс для запроса (кроме случаев, когда MySQL способен объединить результаты выборок по нескольким индексам). Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.

Рассмотрим такой запрос:

Нам следует создать составной индекс на обе колонки:

Устройство составного индекса

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

id | name   | age | gender
1  | Den    | 29 | male
2  | Alyona | 15 | female
3  | Putin  | 89 | tsar
4  | Petro  | 12 | male

значения составного индекса будут такими:

age_gender
12male
15female
29male
89tsar

Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

Поиск по диапазону

Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:

Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:

age_gender
12male
15female
29male
89tsar

Плагин защиты комментариев от спама

Для тех, кто любит простые решения, я написал плагин для защиты комментариев WordPress от спама при помощи Google reCAPTCHA. Для его использования вам необходимо:

1. Скачать плагин.
2. Установить плагин.
3. Ввести ключи в настройках WordPress.

Ключи необходимо вводить на главной странице настроек WordPress (Меню -> Настройки). Поля станут доступны внизу страницы настроек сразу после активации плагина.

Поля ввода ключей Google reCAPTHCA

Если ключи не будут введены, то защита отключится без каких-либо ошибок или последствий для работы сайта.

Для придания отступов и других стилей, добавьте в файл style.css правило для класса g-recaptcha.

Simple and Unique Index

You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table.

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

You can use one or more columns to create an index.

For example, we can create an index on tutorials_tbl using tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

You can create a simple index on a table. Just omit the UNIQUE keyword from the query to create a simple index. A Simple index allows duplicate values in a table.

If you want to index the values in a column in a descending order, you can add the reserved word DESC after the column name.

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

Чтение данных с диска

На жестком диске нет такого понятия, как файл. Есть понятие блок. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идет после него. Файл делится на куски и каждый кусок сохраняется в пустой блок.

При чтении файла, мы по очереди проходимся по всем блокам и собираем файл из кусков. Блоки одного файла могут быть раскиданы по диску (фрагментация). Тогда чтение файла замедлится, т.к. понадобится прыгать разным участкам диска.

Когда мы ищем что-то внутри файла, нам понадобится пройтись по всем блокам, в которых он сохранен. Если файл очень большой, то и количество блоков будет значительным. Необходимость перепрыгивать с блока на блок, которые могут находиться в разных местах, сильно замедлит поиск данных.

Дополнительные фильтры

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

В этом случае, Mysql снова не сможет подобрать индекс для таблицы статистики:

+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
|  1 | SIMPLE      | s     | ALL    | article_id    | NULL    | NULL    | NULL              | 676786 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.s.article_id |      1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+

Логика выбора индекса тут такая же, как и в предыдущем примере. Необходимо подобрать индекс, который позволит быстро отфильтровать таблицу статистики по дате:

Теперь запрос будет использовать индексы на обе таблицы:

+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | ref    | article_id,date | date    | 4       | const             | 2996 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY         | PRIMARY | 4       | test.s.article_id |    1 |                                              |
+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+

Удалить индекс

Вы можете удалять индексы в SQL, используя оператор DROP INDEX.

Синтаксис

Синтаксис удаления индекса в SQL.

Для Oracle и PostgreSQL.

DROP INDEX index_name;

Для MySQL и MariaDB.

DROP INDEX index_name ON table_name;

Для SQL Server.

DROP INDEX table_name.index_name;

index_name

Имя индекса для удаления

table_name

Имя таблицы, к которой принадлежит индекс

Пример

Давайте рассмотрим пример того, как удалить индекс с именем sites_idx из таблицы sites. Для Oracle.

PgSQL

DROP INDEX websites_idx;

1	DROPINDEXwebsites_idx;

Поскольку каждое имя индекса должно быть уникальным в базе данных, нам не нужно указывать таблицу websites в операторе DROP INDEX в Oracle.

Для MySQL и MariaDB.

PgSQL

DROP INDEX websites_idx
ON websites;

1 2	DROPINDEXwebsites_idx ONwebsites;

Для SQL Server.

PgSQL

DROP INDEX websites.websites_idx;

1	DROPINDEXwebsites.websites_idx;

Как видите, каждая база данных имеет уникальные различия в своем синтаксисе. Убедитесь, что вы используете правильную команду DROP INDEX.

Introduction to MySQL composite index

A composite index is an index on multiple columns. MySQL allows you to create a composite index that consists of up to 16 columns.

A composite index is also known as a multiple-column index.

The query optimizer uses the composite indexes for queries that test all columns in the index, or queries that test the first columns, the first two columns, and so on.

If you specify the columns in the right order in the index definition, a single composite index can speed up these kinds of queries on the same table.

To create a composite index at the time of table creation, you use the following statement:

In this syntax, the composite index consists of three columns c2, c3, and c4.

Or you can add a composite index to an existing table by using the statement:

Notice that if you have a composite index on (c1,c2,c3), you will have indexed search capabilities on one the following column combinations:

For example:

The query optimizer cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. For example, the following queries cannot use the composite for lookups:

Чтение данных с диска

На жестком диске нет такого понятия, как файл. Есть понятие блок. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идет после него. Файл делится на куски и каждый кусок сохраняется в пустой блок.

При чтении файла, мы по очереди проходимся по всем блокам и собираем файл из кусков. Блоки одного файла могут быть раскиданы по диску (фрагментация). Тогда чтение файла замедлится, т.к. понадобится прыгать разным участкам диска.

Когда мы ищем что-то внутри файла, нам понадобится пройтись по всем блокам, в которых он сохранен. Если файл очень большой, то и количество блоков будет значительным. Необходимость перепрыгивать с блока на блок, которые могут находиться в разных местах, сильно замедлит поиск данных.

5 последних уроков рубрики «Разное»

• Выбрать хороший хостинг для своего сайта достаточно сложная задача. Особенно сейчас, когда на рынке услуг хостинга действует несколько сотен игроков с очень привлекательными предложениями. Хорошим вариантом является лидер рейтинга Хостинг Ниндзя — Макхост.

• Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

  Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

• Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.

• Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

Описание

CREATE INDEX создаёт индексы по указанной колонке(ам) заданного отношения, которым может быть таблица или материализованное представление. Индексы применяются в первую очередь для оптимизации производительности базы данных (хотя при неправильном использовании возможен и противоположный эффект).

Ключевое поле для индекса задаётся как имя колонки или выражение, заключённое в скобки. Если метод индекса поддерживает многоколоночные индексы, допускается указание нескольких полей.

Поле индекса может быть выражением, вычисляемым из значений одной или нескольких колонок в строке таблицы. Это может быть полезно для получения быстрого доступа к данным по некоторому преобразованию исходных значений. Например, индекс, построенный по выражению upper(col), позволит использовать поиск по индексу в предложении WHERE upper(col) = 'JIM'.

PostgreSQL предоставляет следующие методы индексов: B-дерево, хеш, GiST, SP-GiST и GIN. Пользователи могут определить и собственные методы индексов, но это довольно сложная задача.

Если в команде присутствует предложение WHERE, она создаёт частичный индекс. Такой индекс содержит записи только для части таблицы, обычно более полезной для индексации, чем остальная таблица. Например, если таблица содержит информацию об оплаченных и неоплаченных счетах, при этом последних сравнительно немного, но именно эта часть таблицы наиболее востребована, то увеличить быстродействие можно, создав индекс только по этой части. Ещё одно возможное применение WHERE — добавив UNIQUE, обеспечить уникальность в подмножестве таблицы. Подробнее это рассматривается в Разделе 11.8.

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

Составные индексы

MySQL может использовать только один индекс для запроса (кроме случаев, когда MySQL способен объединить результаты выборок по нескольким индексам). Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.

Рассмотрим такой запрос:

Нам следует создать составной индекс на обе колонки:

Устройство составного индекса

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

id | name   | age | gender
1  | Den    | 29 | male
2  | Alyona | 15 | female
3  | Putin  | 89 | tsar
4  | Petro  | 12 | male

значения составного индекса будут такими:

age_gender
12male
15female
29male
89tsar

Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

Поиск по диапазону

Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:

Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:

age_gender
12male
15female
29male
89tsar

Замечания

Информацию о том, когда могут применяться, и когда не применяются индексы, и в каких конкретных ситуациях они могут быть полезны, можно найти в Главе 11.

В настоящее время составные индексы поддерживаются только методами B-дерево, GiST, GIN и BRIN. По умолчанию такой индекс может включать до 32 полей. (Этот предел можно изменить, пересобрав Postgres Pro.) Уникальные индексы поддерживает только B-дерево.

Для каждого столбца индекса можно задать класс операторов. Этот класс определяет, какие операторы будут использоваться индексом для этого столбца. Например, индекс-B-дерево по четырёхбайтовым целым будет использовать класс ; этот класс операторов включает функции сравнения для таких значений. На практике обычно достаточно использовать класс операторов по умолчанию для типа данных столбца. Существование классов операторов объясняется в первую очередь тем, что для некоторых типов данных можно предложить более одного осмысленного порядка сортировки. Например, может возникнуть желание отсортировать комплексные числа как по абсолютному значению, так и по вещественной части. Это можно сделать, определив два класса операторов для типа данных и выбрав подходящий класс при создании индекса. За дополнительными сведениями о классах операторов обратитесь к Разделу 11.9 и Разделу 36.14.

Для методов индекса, поддерживающих сканирование по порядку (в настоящее время это поддерживает только B-дерево), можно изменить порядок сортировки индекса, добавив необязательные предложения , , или . Так как упорядоченный индекс можно сканировать вперёд или назад, обычно не имеет смысла создавать индекс по убыванию () для одного столбца — этот порядок сортировки можно получить и с обычным индексом. Эти параметры имеют смысл при создании составных индексов так, что они будут соответствовать порядку сортировки, указанному в запросе со смешанным порядком, например . Параметры полезны, когда требуется реализовать поведение «NULL внизу», изменив стандартное «NULL вверху», в запросах, зависящих от индексов, чтобы избежать дополнительной сортировки.

Для большинства методов индексов скорость создания индекса зависит от значения . Чем больше это значение, тем меньше времени требуется для создания индекса (если только заданное значение не превышает объём действительно доступной памяти, что влечёт за собой использование подкачки).

Для удаления индекса применяется DROP INDEX.

Использование EXPLAIN для анализа индексов

Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:

mysql> EXPLAIN SELECT * FROM users WHERE email = 'golotyuk@gmail.com';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |  336 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).

Как видим, в примере не используется ни один индекс. После создания индекса:

mysql> EXPLAIN SELECT * FROM users WHERE email = 'golotyuk@gmail.com';
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | email         | email | 386     | const |    1 |       |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+

Прочитана всего одна запись, т.к. был использован индекс.

Проверка длинны составных индексов

Explain также поможет определить правильность использования составного индекса. Проверим запрос из примера (с индексом на колонки age и gender):

mysql> EXPLAIN SELECT * FROM users WHERE age = 29 AND gender = 'male';
+----+-------------+--------+------+---------------+------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys | key        | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+---------------+------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | users  | ref  | age_gender    | age_gender | 24      | const,const |    1 | Using where |
+----+-------------+--------+------+---------------+------------+---------+-------------+------+-------------+

Значение key_len показывает используемую длину индекса. В нашем случае 24 байта — длинна всего индекса (5 байт age + 19 байт gender).

Если мы выполним изменим точное сравнение на поиск по диапазону, увидим что MySQL использует только часть индекса:

mysql> EXPLAIN SELECT * FROM users WHERE age <= 29 AND gender = 'male';
+----+-------------+--------+------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | users  | ref  | age_gender    | age_gender | 5       |      |   82 | Using where |
+----+-------------+--------+------+---------------+------------+---------+------+------+-------------+

Это сигнал о том, что созданный индекс не подходит для этого запроса. Если же мы создадим правильный индекс:

mysql> Create index gender_age on users(gender, age);
mysql> EXPLAIN SELECT * FROM users WHERE age < 29 and gender = 'male';
+----+-------------+--------+-------+-----------------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys         | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+-----------------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | users  | range | age_gender,gender_age | gender_age | 24      | NULL |   47 | Using where |
+----+-------------+--------+-------+-----------------------+------------+---------+------+------+-------------+

В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.

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

Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:

При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.

Кластерные индексы

Обычные индексы являются некластерными. Это означает, что сам индекс хранит только ссылки на записи таблицы. Когда происходит работа с индексом, определяется только список записей (точнее список их первичных ключей), подходящих под запрос. После этого происходит еще один запрос — для получения данных каждой записи из этого списка.

Кластерные индексы сохраняют данные записей целиком, а не ссылки на них. При работе с таким индексом не требуется дополнительной операции чтения данных.

Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.