Как посмотреть индексы в mysql
Работа с индексами в MySQL и MariaDB
Индексы позволяют оптимизировать работу с базой — данные сортируются и СУБД не приходится искать значения среди всех записей. На практике, это может сократить время обработки запросов на несколько порядков.
Данная инструкция применима по отношению как к MySQL, так и MariaDB. В примерах будет использоваться таблица с названием table_test и индекс с названием index_test.
Для больших таблиц не создавайте индексы при помощи phpMyAdmin или других веб-инструментов, так как по умолчанию, интерпретатор PHP настроен на ограниченное время обработки скрипта, а процесс создания индекса может затянуться. Это может привести к потери данных.
Прежде чем начать, правильным будет создадать резервную копию базы.
Командная оболочка
Заходим в командную оболочку MySQL или MariaDB:
* под учетной записью root с запросом пароля.
Выбираем базу, с которой будем работать:
* выбрана база с именем base1
Посмотреть индексы
Прежде чем начать, стоит убедиться в отсутствии индексов, чтобы не создать дубли (инструкция if not exists не работает для create index).
Увидеть имеющиеся индексы:
SHOW INDEX FROM table_test;
Простой индекс
Применяется для ускорения выборки данных.
Вводим следующую команду для создания простого индекса:
mysql> CREATE INDEX index_test ON table_test (name);
* в данном примере создан простой индекс, ускоряющий выборку данных, с именем index_test для поля name таблицы table_test
mysql> DROP INDEX index_test ON table_test;
Полнотекстовый поиск
Необходим для поиска данных с помощью функции MATCH. Индекс создается для таблиц типа «MyISAM» и полей с типом «text».
mysql> CREATE FULLTEXT INDEX index_test ON table_test (name);
mysql> DROP INDEX index_test ON table_test;
Уникальный индекс
Обеспечит уникальность данных для проиндексированного поля.
mysql> CREATE UNIQUE INDEX index_test ON table_test (name);
mysql> DROP INDEX index_test ON table_test;
phpMyAdmin
Способ более удобный, но, как сказано выше, его не стоит применять в продуктивной среде.
В правой части кликаем по необходимому индексу для его создания:
Для удаление в том же разделе Структура кликаем по Индексы и в раскрывшемся списке удаляем индекс:
Как показать все индексы в MySQL или схеме
Индекс базы данных MySQL относится к типу структуры данных, используемой в качестве организации данных в базе данных и помогающей повысить скорость различных операций, выполняемых в MySQL.
Индексы очень полезны. Без них MySQL должен сканировать всю таблицу, чтобы найти соответствующие строки и столбцы, что может быть очень неэффективным в больших базах данных.
В этом руководстве основное внимание будет уделено тому, как просматривать информацию индекса с помощью предложения SHOW INDEXES в MySQL.
Показать индексы таблиц
Чтобы показать информацию об индексе в таблице, мы используем предложение SHOW INDEXES, за которым следует имя таблицы, для которой мы хотим получить информацию об индексе.
Общий синтаксис показан как:
Например, рассмотрим одну из таблиц в образце базы данных Sakila. Мы можем получить информацию об индексе, как показано в запросе ниже:
SHOW INDEXES FROM film;
Вышеупомянутый запрос отобразит индексную информацию из таблицы фильмов в базе данных Sakila. Результат:
Понимание информации об индексе
Команда SHOW INDEXES отображает соответствующую информацию об индексах в указанной таблице.
Здесь представлены следующие условия и соответствующая информация:
СОВЕТ: информация об индексах из запроса SHOW INDEXES аналогична информации из SQLStatistics.
Показать индексы схемы
Вы также можете получить индексную информацию о схеме. Общий синтаксис для достижения этого результата следующий:
SELECT table_name, index_name FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = “schema_name”;
Рассмотрим запрос ниже, который показывает информацию о схеме Sakila:
Это отобразит информацию об индексах в схеме Sakila, как показано в выходных данных ниже:
Индексы в MySQL
Индексы в MySQL (Mysql indexes) — отличный инструмент для оптимизации SQL запросов. Чтобы понять, как они работают, посмотрим на работу с данными без них.
1. Чтение данных с диска
На жестком диске нет такого понятия, как файл. Есть понятие блок. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идет после него. Файл делится на куски и каждый кусок сохраняется в пустой блок.
При чтении файла, мы по очереди проходимся по всем блокам и собираем файл из кусков. Блоки одного файла могут быть раскиданы по диску (фрагментация). Тогда чтение файла замедлится, т.к. понадобится прыгать разным участкам диска.
Когда мы ищем что-то внутри файла, нам понадобится пройтись по всем блокам, в которых он сохранен. Если файл очень большой, то и количество блоков будет значительным. Необходимость перепрыгивать с блока на блок, которые могут находиться в разных местах, сильно замедлит поиск данных.
2. Поиск данных в MySQL
Таблицы MySQL — это обычные файлы. Выполним запрос такого вида:
MySQL при этом открывает файл, где хранятся данные из таблицы users. А дальше — начинает перебирать весь файл, чтобы найти нужные записи.
Кроме этого, MySQL будет сравнивать данные в каждой строке таблицы со значением в запросе. Допустим работа ведется с таблицей, в которой есть 10 записей. Тогда MySQL прочитает все 10 записей, сравнит колонку age каждой из них со значением 29 и отберет только подходящие данные:
Итак, есть две проблемы при чтении данных:
3. Сортировка данных
Представим, что мы отсортировали наши 10 записей по убыванию. Тогда используя алгоритм бинарного поиска, мы могли бы максимум за 4 операции отобрать нужные нам значения:
Кроме меньшего количества операций сравнения, мы сэкономили бы на чтении ненужных записей.
Индекс — это и есть отсортированный набор значений. В MySQL индексы всегда строятся для какой-то конкретной колонки. Например, мы могли бы построить индекс для колонки age из примера.
4. Выбор индексов в MySQL
В самом простом случае, индекс необходимо создавать для тех колонок, которые присутствуют в условии WHERE.
Рассмотрим запрос из примера:
Нам необходимо создать индекс на колонку age:
После этой операции MySQL начнет использовать индекс age для выполнения подобных запросов. Индекс будет использоваться и для выборок по диапазонам значений этой колонки:
Сортировка
Для запросов такого вида:
действует такое же правило — создаем индекс на колонку, по которой происходит сортировка:
Внутренности хранения индексов
Представим, что наша таблица выглядит так:
id | name | age
После создания индекса на колонку age, MySQL сохранит все ее значения в отсортированном виде:
age index
Кроме этого, будет сохранена связь между значением в индексе и записью, которой соответствует это значение. Обычно для этого используется первичный ключ:
age index и связь с записями
Уникальные индексы
MySQL поддерживает уникальные индексы. Это удобно для колонок, значения в которых должны быть уникальными по всей таблице. Такие индексы улучшают эффективность выборки для уникальных значений. Например:
На колонку email необходимо создать уникальный индекс:
Тогда при поиске данных, MySQL остановится после обнаружения первого соответствия. В случае обычного индекса будет обязательно проведена еще одна проверка (следующего значения в индексе).
5. Составные индексы
MySQL может использовать только один индекс для запроса (кроме случаев, когда MySQL способен объединить результаты выборок по нескольким индексам). Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.
Рассмотрим такой запрос:
Нам следует создать составной индекс на обе колонки:
Устройство составного индекса
Чтобы правильно использовать составные индексы, необходимо понять структуру их хранения. Все работает точно так же, как и для обычного индекса. Но для значений используются значений всех входящих колонок сразу. Для таблицы с такими данными:
id | name | age | gender
значения составного индекса будут такими:
age_gender
Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.
Поиск по диапазону
Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:
Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:
age_gender
Сортировка
Составные индексы также можно использовать, если выполняется сортировка:
В этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):
Такой порядок колонок в индексе позволит выполнить фильтрацию по первой части индекса, а затем отсортировать результат по второй.
Колонок в индексе может быть больше, если требуется:
В этом случае следует создать такой индекс:
6. Использование EXPLAIN для анализа индексов
Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:
Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).
Как видим, в примере не используется ни один индекс. После создания индекса:
Прочитана всего одна запись, т.к. был использован индекс.
Проверка длинны составных индексов
Explain также поможет определить правильность использования составного индекса. Проверим запрос из примера (с индексом на колонки age и gender):
Значение key_len показывает используемую длину индекса. В нашем случае 24 байта — длинна всего индекса (5 байт age + 19 байт gender).
Если мы выполним изменим точное сравнение на поиск по диапазону, увидим что MySQL использует только часть индекса:
Это сигнал о том, что созданный индекс не подходит для этого запроса. Если же мы создадим правильный индекс:
В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.
7. Селективность индексов
Вернемся к запросу:
Для такого запроса необходимо создать составной индекс. Но как правильно выбрать последовательность колонок в индексе? Варианта два:
Подойдут оба. Но работать они будут с разной эффективностью.
Чтобы понять это, рассмотрим уникальность значений каждой колонки и количество соответствующих записей в таблице:
Эта информация говорит нам вот о чем:
Если колонка age будет идти первой в индексе, тогда MySQL после первой части индекса сократит количество записей до 200. Останется сделать выборку по ним. Если же колонка gender будет идти первой, то количество записей будет сокращено до 6000 после первой части индекса. Т.е. на порядок больше, чем в случае age.
Это значит, что индекс age_gender будет работать лучше, чем gender_age.
Селективность колонки определяется количеством записей в таблице с одинаковыми значениями. Когда записей с одинаковым значением мало — селективность высокая. Такие колонки необходимо использовать первыми в составных индексах.
8. Первичные ключи
Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:
При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.
Кластерные индексы
Обычные индексы являются некластерными. Это означает, что сам индекс хранит только ссылки на записи таблицы. Когда происходит работа с индексом, определяется только список записей (точнее список их первичных ключей), подходящих под запрос. После этого происходит еще один запрос — для получения данных каждой записи из этого списка.
Кластерные индексы сохраняют данные записей целиком, а не ссылки на них. При работе с таким индексом не требуется дополнительной операции чтения данных.
Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.
Overhead
Важно помнить, что индексы предполагают дополнительные операции записи на диск. При каждом обновлении или добавлении данных в таблицу, происходит также запись и обновление данных в индексе.
Создавайте только необходимые индексы, чтобы не расходовать зря ресурсы сервера. Контролируйте размеры индексов для Ваших таблиц:
Когда создавать индексы?
Самое важное
Выделяйте достаточно времени на анализ и организацию индексов в MySQL (и других базах данных). На это может уйти намного больше времени, чем на проектирование структуры базы данных. Удобно будет организовать тестовую среду с копией реальных данных и проверять там разные структуры индексов.
Не создавайте индексы на каждую колонку, которая есть в запросе, MySQL так не работает. Используйте уникальные индексы, где необходимо. Всегда устанавливайте первичные ключи.
Индексы в MySQL: как найти индекс таблицы?
Индекс банка данных — это структура данных, которая увеличивает скорость транзакций таблиц. Имея один или даже несколько столбцов, можно генерировать индексы, закладывая основу для взаимно быстрого случайного поиска и эффективного упорядочивания входов записей. При построении индекса следует иметь в виду, что все столбцы отбрасываются для генерации SQL-запросов, а также для создания одного или даже нескольких индексов для этих столбцов. На практике индексы представляют собой форму таблиц, которые содержат поле столбца первичного ключа или индекса и ссылаются на отдельную таблицу для каждой записи. Пользователи могут не видеть индексы, они отброшены для ускорения запросов, так как поисковая система базы данных будет использовать их для очень быстрой трассировки записей.
Индексы с использованием MySQL WorkBench
Во-первых, запустите MySQL Workbench и подключите его к корневой базе данных.
Мы будем создавать новую таблицу «контакты» в базе данных «данные» с разными столбцами в ней. У нас есть один первичный ключ и один столбец UNIQUE key в этой таблице, например id и адрес электронной почты. Здесь вы должны пояснить, что вам не нужно создавать индексы для столбцов UNIQUE и PRIMARY. База данных автоматически создает индексы для столбцов обоих типов. Таким образом, мы создадим индекс phone для столбца phone и индекс name для столбцов first_name и last_name. Выполните запрос, используя мигающий значок на панели задач.
Из вывода видно, что таблица и индексы созданы.
Теперь перейдите к панели схемы. В списке «Таблицы» вы можете найти только что созданную таблицу.
Давайте попробуем команду SHOW INDEXES, чтобы проверить индексы для этой конкретной таблицы, как показано ниже в области запроса, используя значок flash.
Это окно появится сразу. Вы можете увидеть столбец «Key_name», который показывает, что ключ принадлежит каждому столбцу. Поскольку мы создали индекс «телефон» и «имя», он также появляется. Вы можете увидеть другую важную информацию об индексах, например, последовательность индекса для определенного столбца, тип индекса, видимость и т.д.
Индексы с использованием оболочки командной строки MySQL
Откройте клиентскую оболочку командной строки MySQL на своем компьютере. Введите пароль MySQL, чтобы начать использовать.
Пример 1
Предположим, что у нас есть таблица order1 в схеме order с некоторыми столбцами, имеющими значения, как показано на изображении. Используя команду SELECT, мы должны получить записи «order1».
Поскольку мы еще не определили индексы для таблицы order1, угадать невозможно. Итак, мы попробуем использовать команду SHOW INDEXES или SHOW KEYS для проверки индексов следующим образом:
Вы можете понять, что таблица order1 имеет только 1 столбец первичного ключа из приведенных ниже выходных данных. Это означает, что индексы еще не определены, поэтому для столбца «id» первичного ключа отображаются только записи с одной строкой.
Давайте проверим индексы для любого столбца в таблице order1, где видимость отключена, как показано ниже.
Теперь мы создадим несколько УНИКАЛЬНЫХ индексов для таблицы order1. Мы назвали этот УНИКАЛЬНЫЙ ИНДЕКС как ’rec’ и применили его к 4 столбцам: id, Region, Status и OrderNo. Для этого попробуйте следующую команду.
Теперь посмотрим на результат создания индексов для конкретной таблицы. Результат приведен ниже после использования команды SHOW INDEXES. У нас есть список всех созданных индексов с одинаковыми именами «rec» для каждого столбца.
>> SHOW INDEXES FROM order1 IN data ;
Пример 2
Предположим, что новая таблица «student» в базе данных «data» с полями с четырьмя столбцами имеет несколько записей. Получите данные из этой таблицы с помощью запроса SELECT следующим образом:
Давайте сначала извлечем индексы столбцов первичного ключа, попробовав приведенную ниже команду SHOW INDEXES.
Вы можете видеть, что он выведет индексную запись для единственного столбца, имеющего тип PRIMARY из-за предложения WHERE, используемого в запросе.
Давайте создадим один уникальный и один неуникальный индекс для разных столбцов таблицы «student». Сначала мы создадим УНИКАЛЬНЫЙ индекс ’std’ в столбце ’Имя’ таблицы ’student’, используя команду CREATE INDEX в клиентской оболочке командной строки, как показано ниже.
Давайте создадим или добавим неуникальный индекс в столбец «Тема» таблицы «студент» при использовании команды ALTER. Да, мы использовали команду ALTER, потому что она используется для изменения таблицы. Итак, мы изменили таблицу, добавив индексы к столбцам. Итак, давайте попробуем выполнить приведенный ниже запрос ALTER TABLE в оболочке командной строки, добавив индекс stdSub в столбец Subject.
Теперь настала очередь проверить недавно добавленные индексы в таблице «student» и ее столбцах «Name» и «Subject». Попробуйте выполнить команду ниже, чтобы проверить это.
Из выходных данных вы можете видеть, что запросы присвоили неуникальный индекс столбцу «Тема» и уникальный индекс столбцу «Имя». Вы также можете увидеть названия индексов.
Давайте попробуем команду DROP INDEX, чтобы удалить индекс stdSub из таблицы student.
Давайте посмотрим на оставшиеся индексы, используя ту же инструкцию SHOW INDEX, как показано ниже. Теперь у нас остались только два индекса в таблице «student», как показано ниже.
Заключение
Наконец, мы сделали все необходимые примеры о том, как создавать уникальные и неуникальные индексы, отображать или проверять индексы и отбрасывать индексы для конкретной таблицы.
Индексы в MySQL
Индексы в MySQL (Mysql indexes) — отличный инструмент для оптимизации SQL запросов. Чтобы понять, как они работают, посмотрим на работу с данными без них.
1. Чтение данных с диска
На жестком диске нет такого понятия, как файл. Есть понятие блок. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идет после него. Файл делится на куски и каждый кусок сохраняется в пустой блок.
При чтении файла, мы по очереди проходимся по всем блокам и собираем файл из кусков. Блоки одного файла могут быть раскиданы по диску (фрагментация). Тогда чтение файла замедлится, так как понадобится прыгать по разным участкам диска.
Когда мы ищем что-то внутри файла, нам понадобится пройтись по всем блокам, в которых он сохранен. Если файл очень большой, то и количество блоков будет значительным. Необходимость перепрыгивать с блока на блок, которые могут находиться в разных местах, сильно замедлит поиск данных.
2. Поиск данных в MySQL
Таблицы MySQL – это обычные файлы. Выполним запрос такого вида:
MySQL при этом открывает файл, где хранятся данные из таблицы users. А дальше — начинает перебирать весь файл, чтобы найти нужные записи.
Кроме этого, MySQL будет сравнивать данные в каждой строке таблицы со значением в запросе. Допустим работа ведется с таблицей, в которой есть 10 записей. Тогда MySQL прочитает все 10 записей, сравнит колонку age каждой из них со значением 29 и отберет только подходящие данные:
Итак, есть две проблемы при чтении данных:
3. Сортировка данных
Представим, что мы отсортировали наши 10 записей по убыванию. Тогда используя алгоритм бинарного поиска, мы могли бы максимум за 4 операции отобрать нужные нам значения:
Кроме меньшего количества операций сравнения, мы сэкономили бы на чтении ненужных записей.
Индекс – это и есть отсортированный набор значений. В MySQL индексы всегда строятся для какой-то конкретной колонки. Например, мы могли бы построить индекс для колонки age из примера.
4. Выбор индексов в MySQL
В самом простом случае, индекс необходимо создавать для тех колонок, которые присутствуют в условии WHERE.
Рассмотрим запрос из примера:
Нам необходимо создать индекс на колонку age:
После этой операции MySQL начнет использовать индекс age для выполнения подобных запросов. Индекс будет использоваться и для выборок по диапазонам значений этой колонки:
Сортировка
Для запросов такого вида:
действует такое же правило – создаем индекс на колонку, по которой происходит сортировка:
Внутренности хранения индексов
Представим, что наша таблица выглядит так:
После создания индекса на колонку age, MySQL сохранит все ее значения в отсортированном виде:
Кроме этого, будет сохранена связь между значением в индексе и записью, которой соответствует это значение. Обычно для этого используется первичный ключ:
Уникальные индексы
MySQL поддерживает уникальные индексы. Это удобно для колонок, значения в которых должны быть уникальными по всей таблице. Такие индексы улучшают эффективность выборки для уникальных значений. Например:
На колонку email необходимо создать уникальный индекс:
Тогда при поиске данных, MySQL остановится после обнаружения первого соответствия. В случае обычного индекса будет обязательно проведена еще одна проверка (следующего значения в индексе).
5. Составные индексы
MySQL может использовать только один индекс для запроса (кроме случаев, когда MySQL способен объединить результаты выборок по нескольким индексам). Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.
Рассмотрим такой запрос:
Нам следует создать составной индекс на обе колонки:
Устройство составного индекса
Чтобы правильно использовать составные индексы, необходимо понять структуру их хранения. Все работает точно так же, как и для обычного индекса. Но для значений используются значения всех входящих колонок сразу. Для таблицы с такими данными:
значения составного индекса будут такими:
Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.
Поиск по диапазону
Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:
Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:
Сортировка
Составные индексы также можно использовать, если выполняется сортировка:
В этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):
Такой порядок колонок в индексе позволит выполнить фильтрацию по первой части индекса, а затем отсортировать результат по второй.
Колонок в индексе может быть больше, если требуется:
В этом случае следует создать такой индекс:
6. Использование EXPLAIN для анализа индексов
Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:
Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).
Как видим, в примере не используется ни один индекс. После создания индекса:
Прочитана всего одна запись, так как был использован индекс.
Проверка длины составных индексов
Explain также поможет определить правильность использования составного индекса. Проверим запрос из примера (с индексом на колонки age и gender):
Значение key_len показывает используемую длину индекса. В нашем случае 24 байта – длина всего индекса (5 байт age + 19 байт gender).
Если мы изменим точное сравнение на поиск по диапазону, увидим что MySQL использует только часть индекса:
Это сигнал о том, что созданный индекс не подходит для этого запроса. Если же мы создадим правильный индекс:
В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.
7. Селективность индексов
Вернемся к запросу:
Для такого запроса необходимо создать составной индекс. Но как правильно выбрать последовательность колонок в индексе? Варианта два:
Подойдут оба. Но работать они будут с разной эффективностью.
Чтобы понять это, рассмотрим уникальность значений каждой колонки и количество соответствующих записей в таблице:
68 rows in set (0.00 sec)
Эта информация говорит нам вот о чем:
Если колонка age будет идти первой в индексе, тогда MySQL после первой части индекса сократит количество записей до 200. Останется сделать выборку по ним. Если же колонка gender будет идти первой, то количество записей будет сокращено до 6000 после первой части индекса. Т.е. на порядок больше, чем в случае age.
Это значит, что индекс age_gender будет работать лучше, чем gender_age.
Селективность колонки определяется количеством записей в таблице с одинаковыми значениями. Когда записей с одинаковым значением мало – селективность высокая. Такие колонки необходимо использовать первыми в составных индексах.
8. Первичные ключи
Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:
При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.
Кластерные индексы
Обычные индексы являются некластерными. Это означает, что сам индекс хранит только ссылки на записи таблицы. Когда происходит работа с индексом, определяется только список записей (точнее список их первичных ключей), подходящих под запрос. После этого происходит еще один запрос — для получения данных каждой записи из этого списка.
Кластерные индексы сохраняют данные записей целиком, а не ссылки на них. При работе с таким индексом не требуется дополнительной операции чтения данных.
Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.
Overhead
Важно помнить, что индексы предполагают дополнительные операции записи на диск. При каждом обновлении или добавлении данных в таблицу, происходит также запись и обновление данных в индексе.
Создавайте только необходимые индексы, чтобы не расходовать зря ресурсы сервера. Контролируйте размеры индексов для Ваших таблиц:
Когда создавать индексы?
Самое важное
Выделяйте достаточно времени на анализ и организацию индексов в MySQL (и других базах данных). На это может уйти намного больше времени, чем на проектирование структуры базы данных. Удобно будет организовать тестовую среду с копией реальных данных и проверять там разные структуры индексов.
Не создавайте индексы на каждую колонку, которая есть в запросе, MySQL так не работает. Используйте уникальные индексы, где необходимо. Всегда устанавливайте первичные ключи.
Как исправить ошибку доступа к базе 1045 Access denied for user
Основные понятия о шардинге и репликации
Настройка Master-Master репликации на MySQL за 6 шагов
Примеры ad-hoc запросов и технологии для их исполнения
Анализ медленных PHP скриптов с помощью XHprof
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Типы и способы применения репликации на примере MySQL
Как создать и использовать составной индекс в Mysql
Настройка Master-Slave репликации на MySQL за 6 простых шагов
Синтаксис и оптимизация Mysql LIMIT
Правильная настройка Mysql под нагрузки и не только. Обновлено.
И как правильно работать с длительными соединениями в MySQL
Запрос для определения версии Mysql: SELECT version()
Check-unused-keys для определения неиспользуемых индексов в базе данных
Как работают индексы в Clickhose и как их использовать.
3 примера установки индексов в JOIN запросах
Анализ медленных запросов с помощью EXPLAIN
Что значит и как это починить
Описание, рекомендации и значение параметра query_cache_size
Быстрый подсчет уникальных значений за разные периоды времени
Использование партиций для ускорения сложных удалений
Правила выбора типов данных для максимальной производительности в Mysql
Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL