mysql что такое индекс
Индексы в 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, кластеризация, хранение данных в MyIsam и InnoDb
Как устроены индексы в MySql, чем отличается индексирование в двух наиболее популярных движках MyISAM и InnoDb, чем первичные ключи отличаются от простого индекса, что такое кластерные индексы и покрывающие индексы, как с помощью них можно ускорить запросы. Вот как мне кажется наиболее интересные темы которые раскрою в этой статье. Тут же постараюсь подробно раскрыть тему с позиции того как работает этот механизм внутри. Буквально на пальцах и с позиции абстракций а не конкретики. В общем чтоб было минимум текста и максимум понятно.
Что представляет из себя индекс в MySql
Скорость чтения из индекса
Отличия в индексах MyISAM и InnoDb
Первичные и «вторичные» индексы в чем отличия
Вводная информация
Что представляет из себя индекс в MySql
На рисунке изобразил схематично как устроен индекс. Имеются узловые элементы (квадраты) и листья (круги). Предположим у нас есть таблица с колонками «Val» и «ID» как на рисунке. В этой таблице индекс построен по числовому полю «ID». Тогда получается что в узловых элементах находятся значения индекса и ссылки на другой более нижний узел или лист. В листовых же элементах точно так же лежат значения индекса которые уже ссылаются непосредственно на данные из таблицы.
Процесс поиска происходит примерно следующим образом. Например нужно найти строку с индексом 11.
начинаем просмотр корневого (верхнего) узла
первое значение в нем 10
идем к следующему 19, оно уже больше чем нам нужно
по ссылке слева от 19 переходим к следующему нижнему узлу
там первое значение 13, оно больше чем нам нужно
опять по ссылке слева переходим к более нижнему элементу
это уже будет листовой элемент, в нем уже лежат непосредственно данные
просматриваем данные по порядку
переходим по ссылке непосредственно к строке в таблице.
Скорость чтения из индекса
Такое устройство индекса позволяет обеспечить логарифмическую скорость поиска O(log n). Это очень быстро. Вот таблица где для наглядности посчитал сколько сравнений нужно сделать для поиска записи в таблице с разным количеством данных:
Количество элементов в таблице
Количество сравнений
Отличия в индексах MyISAM и InnoDb
MyIsam это более старый движок чем InnoDb и все описанное выше хорошо описывает устройство индекса именно в MyIsam. Более того для MyIsam можно сказать что первичный индекс и просто обычный индекс ни чем между собой не отличаются. В целом таблицы построенные на движке MyIsam вполне себе могут существовать даже без первичного ключа и без всякого индекса в целом. А вот InnoDb уже более свежий и продвинутый движок, и тут как раз есть отличия первичного ключа и просто индекса. Создать таблицу InnoDb тоже можно не указав первичный ключ, но в этом случае первичный ключ все равно создастся. Это называется суррогатный первичный ключ. InnoDb сам выберет поле по которому нужно этот ключ создать, если ни одно поле не подходит, то создаст новое числовое поле, которое конечно же будет скрыто и в структуре его не увидеть. Для разбора индексов InnoDb первым делом нужно начать с кластеризации.
Кластерный индекс
Кластерный индекс отличается тем, что в отличии от предыдущей картинки, где от листьев шли ссылки непосредственно на строки в таблице, тут все данные строк хранятся непосредственно в самом индексе. Проиллюстрировал это на примере листьев 10, 11, 12. Это хорошо тем что позволяет избежать лишнего чтения диска при переходе по ссылке от листа на данные в строке. Тут непосредственно вся строка лежит в индексе. То есть получается что в InnoDb при создании таблицы и указании первичного ключа будет построено такое дерево, в котором все данные таблицы будут продублированы в листья индекса. Если первичный ключ не задать то колонка для него будет выбрана или создана автоматически и все равно по ней будет построен кластерный индекс.
Более того, если мы говорим о таблицах на основе движка InnoDb, то в целом понятие таблица довольно абстрактное. На картинке она нарисована просто для наглядности. На самом деле ни какой таблицы по сути не существует, а все данные просто хранятся в кластерном индексе.
Первичные и «вторичные» индексы в чем отличия
Выше было оговорено что для MyIsam нет разницы между первичными и «вторичными» ключами.
Первичный и вторичный индекс в MyIsam
На картинке нарисован первичный и вторичный ключ в MyIsam. Первичный ключ построен по полю «ID», вторичный по полю «Val». Видно что их структура одинакова. И в том и в другом в листьях расположены значения индекса и ссылки на строки в таблице.
В InnoDb это устроено немного по другому.
Первичный и вторичный индекс в InnoDb
Как уже говорил, таблица тут просто для наглядности. Все ее данные хранятся в первичном (кластерном ключе). Тут первичный ключ построен по полю «Id», вторичный по полю «Val». Видно что в листьях первичного ключа лежат значения индекса + все данные из строк таблицы. Во вторичном же ключе, в листьях лежат значения ключа + первичный ключ.
Можно резюмировать что для MyIsam нет различий между первичным и вторичными индексами. Для InnoDb первичный ключ содержит в себе все данные таблицы, вторичный же ключ содержит значения ключа плюс значение первичного ключа. Получается что при поиске по вторичному ключу, поиск будет произведен дважды. Первый раз непосредственно по самому индексу, будет найдено значение первичного индекса. И уже второй раз по найденому первичному индексу для поиска данных всей строки.
Покрывающие индексы
Смысл покрывающих индексов в том, что MySql может вытаскивать данные непосредственно из самого индекса, не читая при этом всю строку и вовсе не читая строку. Для такой оптимизации нужно чтобы все поля указанные в SELECT имелись в индексе. То есть например у нас имеется таблица с полями «id», «name», «surname», «age», «address». И мы проиндексировали ее по полю «id». В запросе мы хотим получить например «id» и «name». При таком условии MySql найдет по первичному ключу нужную строку, прочитает ее и отбросит все поля не указанные в SELECT. Если же мы немного оптимизируем этот запрос и построим индкес по двум полям «id» и «name», то в таком случае MySql найдя нужную строку по этому индексу не пойдет читать всю эту строку, а просто возьмет данные, которые нужны непосредственно из индекса. Правда есть обратная сторона такого подхода, а именно размер индекса в этом случае будет больше, по этому нужно грамотно подходить к построению покрывающих индексов.
Более подробно можно почитать в очень хорошей книге «MySQL по максимуму» Бэрон Шварц, Петр Зайцев, Вадим Ткаченко
Основы индексирования и возможности EXPLAIN в MySQL
Темой доклада Василия Лукьянчикова является индексирование в MySQL и расширенные возможности EXPLAIN, т.е. нашей задачей будет ответить на вопросы: что мы можем выяснить с помощью EXPLAIN’а, на что следует обращать внимание?
Многие ограничения EXPLAIN’а связаны с оптимизатором, поэтому мы предварительно посмотрим на архитектуру, чтобы понять, откуда следуют ограничения и что, в принципе, с помощью EXPLAIN’а можно сделать.
По индексам мы пройдемся очень кратко, исключительно в плане того, какие нюансы есть в MySQL, в отличие от общей теории.
Доклад, таким образом, состоит из 3х частей:
Василий Лукьянчиков (Станигост)
Архитектура MySQL
Схематически сервер можно представить так:
Первый блок — клиенты, которые обращаются к серверу через функции соответствующего коннектора или C API по протоколу TCP/IP либо UNIX Socket, как правило. Они попадают на блок управления подключениями, где, собственно, происходит авторизация клиента в этот момент, и запуск процесса авторизации и исполнения. Каждый клиент работает в своем независимом потоке. Лишние потоки могут кэшироваться сервером и потом использоваться.
Про кэш запросов нужно отметить, что он представлен одним общим потоком для всех клиентов и в ряде случаев может оказаться узким местом, если у нас многоядерная архитектура и очень простые запросы к базе. База их быстро выполняет, время обращения к кэшу может стать узким местом, поскольку это единственный поток, и все к нему выстраиваются в очередь.
С управления подключениями запрос попадает на основной конвейер, который состоит их трех частей — парсер, оптимизатор и исполнитель. Собственно, эта часть и превращает полученный SQL-запрос в выборку данных. Эта часть общается с интерфейсом хранилищ, каждая по своим задачам:
Здесь нужно отметить ключевую особенность, связанную с MySQL, — это интерфейс подключаемых хранилищ, т.е. вы можете в своей компании разработать свое собственное хранилище (интерфейс стандартный) и подключить его. Таким образом, вы можете сделать, чтобы данные хранились наиболее удобным вам образом с учетом различных нюансов.
Обратной особенностью является то, что оптимизатор очень слабо связан с хранилищами, он не знает и не учитывает отдельные нюансы исполнения части запроса тем или иным хранилищем. Причем, что странно, это так же плохо происходит для основных хранилищ, которые разрабатывают непосредственно разработчики MySQL, и данная ситуация стала улучшаться только в последних версиях. Этот момент нужно учитывать.
Еще тут стоит отметить то, что индексы в MySQL реализованы именно на уровне хранилищ, они не стандартизированы. Поэтому нужно следить за тем, какой тип индекса — полнотекстовый, B-Tree, пространственный и др. — используется тем или иным хранилищем. И самое главное: один и тот же индекс в разных хранилищах — это может быть совершенно разная структура. Например, B-Tree индекс в MyISAM хранит указатель на сами данные, а в InnoDB он хранит указатель на первичный ключ; в MyISAM происходит сжатие префиксных индексов, а в InnoDB этого не происходит, но зато там есть кэширование и данных, и индексов.
Существует масса нюансов, которые нужно учитывать при работе, т.е. какие-то запросы будут быстрее выполняться в одном хранилище, какие-то — в другом, т.к. они по-разному хранят статистику. Например, запрос count (*) в случае MyISAM может выполняться очень быстро без обращения к самим данных, т.к. там хранится именно статистика в метаданных, это, правда, для частных случаев, но, тем не менее, такие нюансы есть.
Сразу скажем про план запроса. Его делает оптимизатор, и это не какой-то исполняемый код, а набор инструкций, который он передают исполнителю. Это некое предположение о том, как запрос будет выполняться. После того, как исполнитель сделает запрос, могут появиться какие-то отличия и, в отличие от PostgreSQL, MySQL не показывает то, что было сделано, т.е., когда мы смотрим EXPLAIN в MySQL, у нас нет ANALYZE. Точнее, оно появилось совсем недавно в версии 10.1 Maria, которая еще beta, и, естественно, пока не используется. Поэтому нужно учитывать, что когда мы смотрим EXPLAIN в MySQL, это некие предположения.
Часто возникает такая ситуация, что у нас один и тот же план, но разная производительность. Тут надо отметить тот момент, что сам оптимизатор в плане EXPLAIN дает очень мало вещей. Например, у нас запросы select (*) из таблицы и select пары полей из таблицы будут иметь одинаковый план, но в одном случае у нас будет выбрано несколько Кб для каждой записи, а в другом — может быть несколько Мб, если у нас записи огромные. Естественно, производительность этих запросов будет различаться на порядки, но план этого никак не покажет. Или же у нас может быть одинаковый план, один запрос, но выполняться на разных машинах он будет по-разному, потому что в одном случае индекс читается из памяти, в другом, если буфер маленький, индекс берется с диска. Опять при одинаковом плане производительность будет различаться. Поэтому в дополнение к EXPLAIN’у нужно на разные вещи смотреть, в первую очередь — на параметры сервера (show status).
Здесь уместно обратиться к прошедшей конференции РИТ++, на которой был доклад Григория Рубцова по ботаническому определителю MySQL — для тех, кто не умеет гипнотизировать сервер и сразу навскидку определять узкое место, там выстроена целая последовательная схема: посмотрели на такой-то параметр — пошли туда. Как в классическом ботаническом определителе, когда велся поиск определения растений, смотрели на количество листьев, на форму и т.д. и так пришли к ответу. Там именно такая структура — куда последовательно смотреть, что делать, чтобы найти узкие места.
Перейдем к индексам в MySQL
Мы не будем рассматривать и перечислять все типы индексов — они более-менее стандартны для каждой базы. Говоря о MySQL, нам нужно отметить следующее: MySQL никак не управляет дублированными индексами, т.е. если мы создаем таблицу так, как представлено, это не означает, что у нас будет создан primary index уникальный на колонку 1. Это означает, что у нас будет создано три одинаковых индекса на одну колонку. Они все три будут занимать место, будут обновляться, будут учитываться оптимизатором, и MySQL сам не выдаст никакого предупреждения, т.е. это нужно смотреть самостоятельно.
Говоря об индексах (в основном мы будем говорить о b-tree, как о наиболее используемых), чтобы не вдаваться в подробности и дерево не рисовать, индекс очень удобно представлять в виде алфавитного указателя. Например, адресная книга — это таблица, алфавитный указатель к ней — это и есть индекс. Применив такую аналогию, мы можем представить, как происходит работа с индексом, за счет чего там быстрее выбираются данные и пр. Но здесь есть некое отличие в самом MySQL, поскольку MySQL всегда идет по индексу, он использует индекс только слева направо последовательно, без фокусов, это может вызвать вопросы.
Например, если нам нужно в алфавитном указателе найти какие-то имена. «Руками» мы будем искать следующим образом: посмотрим первую фамилию, найдем нужные имена, пролистнем до следующей фамилии. Это не ограничение b-tree дерева, это ограничение реализации b-tree дерева непосредственно в MySQL. Другие базы так умеют делать — использовать не первую колонку, например, в случае WHERE B=3 индекс в MySQL использоваться, вообще, не будет. Только в частном случае, если мы попросим минимум и максимум от этой колонки, но это, скорее, исключение.
На слайде выше представлены различные варианты, и стоит отметить, что индекс обрывается на первом же неравенстве, т.е. он используется последовательно слева направо до первого неравенства. После неравенства дальнейшая часть индекса уже не используется. Т.е. в условии А — константа, В — диапазон, будут использованы первые две части. Во втором случае будет использован целиком индекс. В третьем случае индекс тоже будет использован целиком, т.к. порядок констант в условии не имеет значения, и сервер может их переставить местами. В случае В=3 индекс, вообще, использован не будет. В случае константы и неравенства будет использован индекс только на первые две части, на третью часть индекс уже не сможет использоваться. В случае А — константа и сортировка по последнему, будет только на первую часть использован индекс, потому что пропускается вторая часть, предпоследний индекс будет целиком использован. И в последнем варианте, опять же, индекс будет использован только на первую часть, потому что для сортировки индекс может использоваться только лишь, когда в одном направлении идет.
Это связано с тем, что составной индекс в MySQL — это, по сути, обычный b-tree индекс над конкатенацией входящих столбцов, соответственно, он может двигаться либо по возрастанию всего кортежа, либо по его убыванию. Поэтому сказать: «двигаемся по возрастанию В и по уменьшению С» по индексу мы не сможем. Это ограничение, характерное именно для MySQL.
Здесь есть такой нюанс. Например, A in (0,1) и А between 0 and 1 — это эквивалентные формы, это диапазон и там, и там, но в случае, когда это A in (0,1) — список, он понимает, что это не диапазон, а заменяет на множественное условие равенства. В этом случае он будет использовать индекс. Это еще один нюанс MySQL, т.е. нужно смотреть, как писать — либо списком, либо ставить <>. Он это различает.
Пара слов про избыточный синтаксис. Если у нас есть index(A) и index(A,B), то index(A) будет лишним, потому что в случае index(A,B) часть А может использоваться в нем. Поэтому нам нужно следить, чтобы избыточных индексов не было и самостоятельно их удалять. Понятно, это относится и к случаю, когда оба индекса b-tree, но, если, например, index(A) — это full-text, то, естественно, он может быть необходим.
Вернемся к нюансу на списке. Мы можем делать здесь более широкий индекс уникальным. Например, если мы сделаем index(A,B), то просто условие В использовать не будем, но мы можем в приложении сделать так, чтобы оно самостоятельно подставляло пропущенное условие, если там небольшой возможный вариант списка. Но с этой рекомендацией нужно быть очень осторожными, т.к., несмотря на то, что при наличии списка дальнейшее использование индекса не отбрасывается, он не может быть использован для сортировки, т.е. только на равенство. Поэтому, если у нас есть запросы на сортировку, то нам придется запрос перестраивать через union all, чтобы не было списков, дабы использовать сортировку. Естественно, это не всегда возможно и не всегда удобно. Если расширение индекса нам, например, позволит сделать индекс покрывающим (имеется в виду, что все поля, которые выбираются и используются в запросе, присутствуют в индексе), тогда сервер понимает, что лезть в таблицу за данными ему совсем не обязательно и он целиком обращается к индексу для формирования результата. Т.к. индекс более упорядочен, компактен и хранится чаще всего в памяти кэшированной, это более удобно. Поэтому мы при составлении индекса смотрим всегда, можем ли мы как-то подобрать покрывающий индекс для нашего запроса.
Далее рассмотрим случаи, когда индексы не используются.
Здесь есть общие нюансы с другими базами, например, когда индекс является частью выражения, как и в PostgreSQL, он не сможет его преобразовать, поэтому, если у нас в запросе id + 1 = 3, индекс на id использован не будет. Мы должны сами переносить. Если индекс является частью какого-то выражения, мы должны смотреть, можем ли мы его преобразовать так, чтобы индекс вынести в левую часть в явном виде.
Аналогично, за счет того, что он не производит преобразований (это не только математические, это могут быть несоответствие кодировок, преобразование типов), индекс тоже не будет использован. Индекс не используется, когда пропускается первая часть, когда идет поиск по суффиксу. Ну и, естественно, индекс не будет использован, когда идет сравнение с полями сходной таблицы, потому что в этом случае ему сначала будет нужно прочитать запись из таблицы, чтобы сравнить.
До этого я упоминал покрывающие индексы, и чем они хороши. Вернемся к слайду с архитектурой:
Происходит так: исполнитель запрашивает данные (условие WHERE) у хранилища, соответственно, если у нас есть условие WHERE на несколько позиций, то они все могут быть обработаны внутри самого хранилища. Это оптимальный вариант. Может быть вариант, когда часть условий будет обработано на уровне хранилищ по индексу, строки, переданные на уровень вверх, сервер будет применять, а дальнейшие условия отбрасывать. Понятно, это будет медленнее, т.к. будет идти передача самих записей из хранилища в исполнитель. Поэтому покрывающие индексы, если они у нас применяются только для тех полей, которые в запросе, выгоднее, т.к. мы выбираем уже меньше строк.
Вот, к примеру, подобная оптимизация, называемая index condition pushdown:
Имеется в виду следующее. У нас есть индекс на три поля — А, В, С. В таких условиях мы можем использовать только часть первого. Казалось бы, мы можем в самом хранилище проверить индексы, но раньше (до версии MySQL 5.6, MariaDB 5.3) сервер этого не делал, поэтому нужно внимательно смотреть на конкретные релизы — что умеет делать сервер. В новых версиях сервер производит поиск по первой части индекса, только по колонке А, выбирает данные и, прежде чем передавать записи исполнителю, он проверяет условие на вторую и третью части и смотрит, нужно ли выбирать целиком записи или нет. Это естественно уменьшает количество тех записей, которые нужно считывать с диска.
Особенность ключей в InnoDB — это то, что вторичные ключи ссылаются на первичный ключ, поэтому фактически вторичный ключ в InnoDB представляет собой вторичный ключ + указатель на первичный ключ.
Такой длинный индекс имеет невидимый «хвост». Невидимый в том смысле, что раньше оптимизатор его в своих действиях не учитывал.
Вот, у нас есть primary (A, B) составной, соответственно, вторичный ключ — это будет составной ключ на (C, A, B) и по нему уже можно проводить поиск.
Таким образом, когда вы работаете с таблицами InnoDB и делаете индексы, вы всегда должны учитывать, что длинный первичный ключ — это может быть либо хорошо, либо плохо в зависимости от того, какие у вас запросы, потому что он будет добавляться ко всем индексам.
Здесь следующий нюанс — эта оптимизация будет учитываться только для фильтрации строк. У нас в отсортированном виде хранятся только значения вторичного ключа, а указатели на первичный ключ не отсортированы, поэтому такой длинный невидимый «хвост» сервер сможет использовать только для условий равенства фильтрации строк. Это доступно в MariaDB 5.5, MySQL 5.6.
Ключевые ограничения оптимизатора — это то, что он исторически использует очень мало статистики. Он запрашивает у хранилищ данные. Вот здесь указано подробнее, что учитывает сервер:
Он может учитывать результаты вводимых команд, поля, количество строк, т.е. довольно-таки мало данных. Особенность в том, что изначально статистика вычисляется следующим образом: мы запускаем сервер или делаем какую-то команду типа ALTER, у нас статистика обновляется, потом таблица живет некоторое время, какой-то процесс вдруг меняется, потом статистика вновь обновляется. Т.е. бывает, что сама статистика не соответствует распределению данных.
Опять же, каждое хранилище выбирает статистику по-своему — где-то больше, где-то меньше. В последних версиях реализуется идея независимой статистики, т.е. статистики на уровне сервера — выделяются служебные таблицы, в которых единым образом уже независимо от механизмов хранения собирается статистика для всех таблиц, причем, если в Percona 5.6. это сделано только для индексов в InnoDB, то в Maria 10 пошли дальше и собирают ее даже для неиндексированных столбцов, за счет чего оптимизатор может выбирать более оптимальные планы выполнения, поскольку лучше понимает распределение данных.
Оптимизатор не учитывает особенности хранилищ — когда мы передаем запрос в хранилище, то, понятно, что в InnoDB поиск по вторичному ключу будет идти дольше, потому что мы пройдемся по вторичному ключу, получим указатели на первичный ключ, возьмем эти указатели, пойдем за данными, т.е. у нас будет двойной проход, а в MyISAM, например, будут сразу указатели непосредственно на сами строки. Вот подобных нюансов относительного быстродействия тех или иных частей (у нас запрос может одновременно обращаться к разным хранилищам) оптимизатор и не учитывает. Он также не учитывает очень много вопросов, связанных с оборудованием, т.е. какие данные у нас закэшированы, какие буферы.
Метрика. Понятно, что оптимизатор выбирает самый дешевый план, но дешевый план с точки зрения оптимизатора — это план с наименьшей стоимостью, а вопрос стоимости — это некоторая условность, которая может не совпадать с нашими представлениями о ней. Опять же, сложность выбора — это когда много таблиц и нужно их по-разному перемещать и смотреть.
Еще он использует правила, т.е. если он, например, понимает, что нужно использовать full text index, то он использует его, даже несмотря на то, что у нас может быть условие на первичный ключ, которое однозначно выдаст одну колонку.
Есть еще такой нюанс — эти две записи с нашей точки зрения эквивалентны: