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

  • Запретить запросам выполнять полное сканирование всего набора данных
  • Доступ к меньшему количеству и, следовательно, блокировка меньшего количества строк во время запросов
  • Значительно ускорить запросы
  • Запретить получение записей сортировки записей
  • Наложите ограничения, например уникальность данных
  • Эффективное объединение наборов данных
  • И так далее…

В целом, хорошее индексирование может значительно сократить время отклика.

Распространенные проблемы, возникающие при применении индексов:

  • Наличие индекса для каждого столбца в таблице
  • Не использовать составные (многоколоночные) индексы
  • Использование составных индексов, но с неэффективным порядком столбцов, который не позволяет полностью использовать индекс
  • Создание индексов на основе эмпирических правил или эвристики, таких как индексация всех столбцов, которые появляются в предложении WHERE.

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

Итак, давайте начнем!

Индексы концептуально

Концептуально основной смысл индекса - позволить вам быстрее находить данные - независимо от его базовой реализации (B-Tree, Hash, R-Tree и т. Д.)

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

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

Без индексов

Без сортировки, чтобы найти номер Цукерберга в вашем дружелюбном районе, может потребоваться просмотр каждой записи в телефонной книге. В мире баз данных это называется сканированием таблицы; пример, когда поиск не использует индексы. Это крайне неэффективно, особенно когда в мире живут миллиарды людей.

Добавление индекса с одним столбцом:

Если бы мы вместо этого отсортировали эту телефонную книгу по фамилии, это, по крайней мере, позволило бы нам легко переходить к группировке интересующих записей:

Это пример индекса с одним столбцом в (last_name).

ALTER TABLE `phonebook` ADD INDEX (` last_name`);

Однако в большой телефонной книге все еще может быть много людей с одинаковым last_name:

Есть 513 человек с фамилией «Цукерберг», но более 4 миллионов человек с фамилией «Смит», что означает, что вам все равно придется сканировать 4 миллиона строк.

* Если, конечно, вам не повезло дружить с мистером Чизхедом, который, кажется, единственный человек, удостоенный этой фамилии *

Очевидно, что одного индекса в одной колонке недостаточно, чтобы эффективно найти друга.

Почему бы нам не добавить индекс в каждый из наших столбцов? Мы могли бы добавить несколько индексов с одним столбцом, например:

ДОБАВИТЬ индекс last_name
ДОБАВИТЬ индекс first_name
ДОБАВИТЬ индекс phone_number

Это можно представить, как теперь у вас есть три телефонных книги, каждая из которых отсортирована по столбцу, который вы проиндексировали. Но это не особо поможет! Вы можете смоделировать слияние индексов (подробнее об этом позже), пересекая результаты, полученные по каждому из трех, но это все равно не так эффективно!

Более того: обычно MySQL будет использовать только один индекс для каждой таблицы, для каждого запроса, поэтому производительность будет намного выше, если мы разработаем единственный индекс, удовлетворяющий нашему поиску!

На помощь приходит многостолбцовый (составной) индекс!

Чтобы ускорить этот процесс, мы можем отсортировать наш список по фамилии, а затем по имени.

ALTER TABLE `phonebook` ADD INDEX (` last_name`, `first_name`);

Это так называемый составной индекс, потому что мы индексируем состав из нескольких столбцов. В данном случае составная часть (last_name, first_name).

В этом указателе записи будут отсортированы так, как вы ожидаете в обычной телефонной книге. По фамилии, а затем по имени. Если вы выполнили поиск, например:

ВЫБРАТЬ номер_телефона ИЗ телефонной книги, где first_name = ‘john’ AND last_name = ‘south’;

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

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

Самое важное, что нужно от этого сделать, - это то, как оно было отсортировано. Нам больше не нужно сканировать таблицы, потому что сортировка позволяет нам очень эффективно сужать наши данные!

Теперь мы легко можем найти нашего приятеля Цука!

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

В качестве примера: только что обсужденный нами индекс (last_name, first_name) не подходит для такого запроса, как:

ВЫБРАТЬ * ИЗ телефонной книги, где first_name = ‘Donald’;

Это потому, что мы не указали никаких критериев для last_name. Созданный нами индекс не сортирует имена в каком-либо логическом распределении, которое позволило бы нам быстро выполнять поиск по ним, поэтому вместо этого мы должны вернуться к полному сканированию таблицы. (вернитесь к приведенному выше примеру).

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

  1. Использование составных индексов жизненно важно, если вы пытаетесь ускорить выполнение определенного запроса
  2. Порядок индекса очень важен

Примеры:

ИНДЕКС (последний, первый)

  • WHERE last =… *** Хорошо
  • WHERE last =… AND first =… *** Хорошо
  • ГДЕ первый =… И последний =… *** Хорошо
  • WHERE first =… *** Индекс бесполезен

ИНДЕКС (a, b) VS ИНДЕКС (b, a)

  • WHERE a = 1 AND b = 3 *** Оба работают хорошо
  • WHERE b = 2 *** Только секунда
  • WHERE a = 4 *** Только первое

ИНДЕКС (а), ИНДЕКС (б) - это не то же самое, что ИНДЕКС (а, б)

Но использование двух индексов (a), (b) может принести пользу:
(SELECT… WHERE A…) UNION (SELECT… WHERE B…)

Теперь, когда вы получили основное представление о том, как сортировка данных определенными способами упрощает поиск, я расскажу, как работает индексирование в MySQL.

Архитектура MySQL

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

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

Индексы InnoDB

Большинство наших таблиц настроено с использованием механизма хранения InnoDB, который использует индексы на основе B-дерева, поэтому мы будем обсуждать их исключительно (есть и другие многие другие типы индексов, такие как: hash индексы, полнотекстовые индексы, пространственные индексы R-tree - все это выходит за рамки).

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

ПОКАЗАТЬ СОСТОЯНИЕ ТАБЛИЦЫ \ G;
ВЫБРАТЬ ИМЯ ТАБЛИЦЫ, ДВИГАТЕЛЬ ИЗ information_schema.TABLES;

Ниже представлено визуальное представление B-дерева:

Подобно бинарному поиску, B-деревья позволяют эффективно искать пары ключ-значение, сортируя данные в структуру данных, по которой можно эффективно перемещаться; избегая полного сканирования, о котором мы говорили ранее.

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

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

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

Важно отметить, чем отличаются индексы первичного ключа и индексы вторичного ключа.

Индексы первичного ключа

Первичные ключи используют кластерный индекс: кластеризованный, потому что фактические данные строки хранятся (кластеризованы) вместе с ключом:

В большинстве случаев это будет FBID или PK, который вы выделили сами, и будет уникальным идентификатором для данной строки в вашей таблице. Как вы можете видеть, на листе этого B-дерева данные строки группируются вместе с ключами.

В этом случае кластерный индекс - это буквально сама таблица! а не отдельную структуру для таблицы.

Поскольку кластеризованный индекс «является» таблицей в InnoDB, важно выбрать подходящий первичный ключ, так как этот ключ будет часто использоваться, а реструктуризация может быть очень дорогостоящей.

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

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

Вторичные индексы:

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

Листовые узлы для вторичных индексов не хранят данные строк, как это было в B-дереве первичного ключа, вместо этого они просто хранят значения первичного ключа, которые служат «указателями» на данные строки, как вы можете видеть ниже:

Обычно это означает, что при использовании вторичного индекса InnoDB сначала будет использовать B-дерево вторичного индекса для извлечения значений первичного ключа соответствующих строк, а затем после использования этих значений вместе с B-деревом первичного ключа для извлечения Данные ряда!

Поскольку первичный ключ добавляется к каждому вторичному индексу в innoDB, не выбирайте огромные PK. В идеале делайте их короткими, чтобы не занимать слишком много памяти, и помните, что все данные будут кластеризованы по этому первичному ключу; Следовательно, громоздкий первичный ключ приведет к громоздким вторичным индексам.

Хм… это медленно?

Должно быть совершенно очевидно, что этот дополнительный поиск для отслеживания «указателя» первичного ключа из вторичного индекса имеет некоторые накладные расходы, он по-прежнему быстрый, потому что первичный ключ индексируется, но именно здесь в игру вступает оптимизация «покрывающего индекса»:

Индексы покрытия

Если ваш вторичный индекс содержит все данные, необходимые для удовлетворения вашего запроса (он «покрывает» его), вам не нужно следовать значениям первичного ключа для получения каких-либо дополнительных данных!

Если мы вернемся к нашему первому примеру телефонной книги, если бы у нас был индекс на (`last_name`,` first_name`), и мы выполнили такой запрос, как:

ВЫБРАТЬ номер телефона ИЗ телефонной книги, ГДЕ last_name = «wiggum» И first_name = «шеф»

Мы могли бы быстро получить значения PK для записей, однако для получения phone_number нам все равно нужно будет следовать значениям PK после, чтобы получить данные строки из кластеризованного индекса первичного ключа.

Тем не мение! Если мы также добавим в наш индекс столбец phone_number, например: (last_name, first_name, phone_number). Этот же запрос теперь полностью удовлетворяется (покрывается) индексом, поэтому никаких дополнительных поисков не требуется, что делает этот запрос намного быстрее.

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

К настоящему времени индексы показали свою полезность для ускорения поиска по столбцам, указанным в предложении WHERE, а также при извлечении данных за счет покрытия столбцов, указанных в ваших прогнозах. Но это еще не все!

Как индексы влияют на ORDER и GROUP BY

Еще один фактор, о котором часто забывают, - это то, как можно использовать индексы во время упорядочивания и группировки!

Допустим, у вас есть только указатель на (last_name) в своей телефонной книге, и вы выполняете запрос:

SELECT * FROM phone_book WHERE last_name = ‘Simpson’ ЗАКАЗАТЬ ПО first_name

При этом будет использоваться индекс last_name, поскольку вы надеетесь быстро сузить список записей с этой фамилией, к сожалению, теперь вам нужно выполнить сортировку этих результирующих записей, чтобы отсортировать их по имени. Это связано с тем, что индекс не сортировал результаты по first_name каким-либо значимым образом.

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

Это также применимо, даже если вы хотите прочитать только 5 строк. Допустим, вы выполнили следующее:

ВЫБРАТЬ last_name ИЗ телефонной книги WHERE first_name = ’Homer’ ORDER BY last_name LIMIT 5;

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

Очевидно, что если бы мы проиндексировали оба элемента (`last_name`,` first_name`), нам бы не потребовалось выполнять эту дополнительную сортировку, потому что записи уже были бы отсортированы для нас в том порядке, в котором мы хотели.

И это еще один важный вариант использования индексов - отказ от сортировки файлов.

Это также относится к операторам GROUP BY. если бы мы выполнили следующий запрос с этим составным индексом для last_name и first_name:

SELECT * FROM phonebook WHERE last_name = «Burns» ГРУППА ПО first_name

Записи уже будут отсортированы по last_name, что позволит нам быстро отфильтровать записи с помощью last_name ‘Burns’. После того, как эти результаты возвращены, они также сортируются по first_name из-за второй части индекса, и поэтому они по сути уже сгруппированы для нас! Нам не нужно было бы выполнять дополнительную сортировку в конце, которая добавила бы дополнительные накладные расходы к нашему запросу.

Выводы из этого:

Индексы полезны не только для быстрой навигации по таблицам, но и для ускорения операций ORDER BY и GROUP BY.

Упорядочивание результатов по индексу работает только в том случае, если порядок индекса точно такой же, как в предложении ORDER BY, и все столбцы отсортированы в одном направлении (по возрастанию или по убыванию).

ИНДЕКС (а, б)

  • ЗАКАЗАТЬ ASC, b ASC *** Хорошо
  • ЗАКАЗАТЬ ПО УДАЛЕНИЮ, B УДАЛЕНИЕ *** Хорошо
  • ORDER BY a ASC, b DESC *** Невозможно использовать индекс

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

Как индексы влияют на `JOINS`

Индексы также сильно повлияют на скорость операций JOIN. Допустим, у вас есть две таблицы: user и user_meta_data (в которой есть столбец user_id, который ссылается на пользовательские таблицы PK id ).

Если в обеих таблицах 1000 записей, и вы запустите следующее:

ВЫБРАТЬ * ИЗ пользователей ПРИСОЕДИНЯЙТЕСЬ к user_meta_data ON user_meta_data.user_id = users.id;

Для КАЖДОЙ строки в таблице users MySQL будет выполнять поиск в таблице user_meta_data, чтобы присоединиться к id к user_id .

Без каких-либо индексов по user_meta_data (особенно без индекса по user_id) MySQL должен был бы выполнять сканирование таблицы, просматривая все 1000 строк для каждого поиска.

Для этого потребуется более (1000 * 1000) = 1 миллион сравнений, поэтому это будет очень медленно.

Добавляя индекс к user_meta_data в столбце user_id, мы можем предотвратить эти 1000 сравнений для каждой строки, позволяя использовать B-дерево индексов для каждого поиска соединения!

Оптимизация отложенного соединения

Представьте себе случай, когда у вас есть индекс по (пол, рейтинг), и вы пытаетесь выполнить следующий запрос:

ВЫБЕРИТЕ пол, рейтинг, возраст, рост, ‹cols› ИЗ профилей, ГДЕ sex = ’M’ ЗАКАЗАТЬ ПО ОГРАНИЧЕНИЮ рейтинга 100000, 10;

(где этот запрос пропускает первые 100000 строк и возвращает только 10)

Это будет работать, как и следовало ожидать, он будет использовать индекс для сканирования 100 010 записей (нет, он недостаточно умен, чтобы просто перейти к смещению), и поскольку индекс не покрывает, он будет тянуть дополнительные данные столбца, например возраст, рост с помощью PK B-Tree, как мы упоминали ранее. После бессмысленного извлечения всех этих данных 100 000 из 100 010 записей выбрасываются, потому что нам нужно всего 10! Очевидно, что получение дополнительных данных для 100000 было ненужной работой.

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

После того, как вы получили эти PK, вы можете просто выполнить обратное соединение с исходной таблицей, чтобы получить все нужные столбцы. Это помогает свести к минимуму объем работы, которую MySQL должен выполнять по сбору данных, которые он только выбрасывает позже. Вернемся к нашему примеру:

ВЫБРАТЬ пол, рейтинг, возраст, рост, ‹cols› ИЗ профилей ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ ‹столбцов первичного ключа› ИЗ профилей, ГДЕ x.sex = 'M' ЗАКАЗАТЬ ПО ОГРАНИЧЕНИЮ рейтинга 100000, 10) КАК x ИСПОЛЬЗОВАНИЕ (‹Столбцов первичного ключа›);

Выделенный подзапрос обращается только к столбцам первичного ключа, поэтому никаких дополнительных накладных расходов не происходит при извлечении данных, не содержащихся в индексе. Это индекс покрытия, и он будет соответственно быстрее. После того, как мы получим 10 значений первичного ключа, которые нас интересуют. Затем мы выполняем объединение этих результатов, извлекая в первую очередь нужные нам дополнительные столбцы. В этом случае мы несем накладные расходы только на выборку дополнительных столбцов для 10 записей, что намного быстрее, чем для всех 100 010 записей !!

Это особенно полезно, если столбец, который вы хотите выбрать, очень большой, например TEXT \ BLOB, потому что накладные расходы будут намного более серьезными.

** Хорошее мышление - думать о доступе к данным. Обычно чем меньше записей вы получаете доступ, тем меньше накладных расходов вы понесете и, следовательно, тем быстрее будет время вашего ответа.

Так как же проверить, действительно ли используются ваши индексы? Обычно это просто: используйте оператор объяснения! (ИНФОРМАЦИОННАЯ СХЕМА также может быть полезна, о чем будет сказано позже.)

Заявление ОБЪЯСНЕНИЯ

Оператор объяснения в том виде, в каком он назван, очень полезен для объяснения того, как выполняется ваш запрос, и, следовательно, выявления, почему он может быть медленным. Если вы добавите EXPLAIN перед запросом, вам будет предоставлена ​​информация от оптимизатора MySQL о плане выполнения оператора.

  • Индексы, которые он планирует использовать.
  • Порядок, в котором планируется объединить столы.
  • Фактически используемые индексы.
  • Сколько строк будет доступно.
  • Использовал ли он файловую сортировку.
  • И так далее…

EXPLAIN работает с операторами SELECT, DELETE, INSERT, REPLACE и UPDATE.

Вы должны использовать данные из объяснения, чтобы провести расследование. Он может указывать на то, какие индексы отсутствуют или не используются, а также на многие другие недостатки в вашем запросе / схеме.

********************** 1. строка **********************
id: 1
select_type: SIMPLE
таблица: категории
тип: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
строк: 4
Дополнительно:
1 строка в наборе (0,00 сек)

Я не буду вдаваться в подробности о каждой из этих колонок - вероятно, это заслуживает отдельной статьи; однако несколько ключевых моментов, на которые следует обратить внимание при оценке индексов:

  • Возможные ключи: показывает ключи, которые могут использоваться MySQL для поиска строк в таблице, если это значение NULL, это означает, что нельзя применить какие-либо полезные индексы.
  • Ключ: указывает фактический индекс, который использовал MySQL.
  • Строки: количество записей, которые были проверены для получения выходных данных. Это особенно актуально при объединениях.
  • Key_len: самая длинная длина использованного ключа (то есть, какие части составного индекса используются). Используйте это, чтобы определить, сколько столбцов было использовано из него.
  • Ссылка: какие столбцы или константы сравниваются с индексом для выбора строк.

Единственный другой столбец объяснения, который я упомяну, - это столбец «Дополнительно», который содержит дополнительную информацию:

Что вы хотели бы видеть в Extra:

  • Использование индекса - MySQL смог использовать покрывающий индекс
  • Distinct - MySQL прекращает поиск после того, как обнаружит первую совпадающую строку
  • Использование условия индекса

Чего вы не хотите видеть в Extra:

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

* К настоящему времени должно быть ясно, что индексы должны быть разработаны для всего запроса, а не только для предложения WHERE.

Подробнее об индексном порядке

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

Как выбрать хороший заказ?

Это зависит от запроса, который будет использовать индекс - вам нужно выбрать индекс, который позволяет:

  • Ваши условия WHERE для эффективного поиска данных
  • Ваши строки должны быть отсортированы и сгруппированы таким образом, чтобы это было удобно для запроса.
  • Ваши СОЕДИНЕНИЯ, чтобы быть эффективными и т. Д.

Еще одна вещь, которая может повлиять на выбор порядка, - это избирательность.

Селективность индекса - это отношение количества отдельных индексированных значений (количество элементов) к общему количеству строк в таблице (#T).

  • Он варьируется от 1 / # T до 1. Селективность уникального индекса равна 1, что является наилучшим результатом.
  • Используя пример телефонной книги: индекс (first_name, last_name) может быть менее эффективным, чем (last_name, first_name), потому что имена гораздо менее различимы по сравнению с last_names, что означает это сужает меньше результатов.

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

Запросы диапазона

Подобно тому, что мы только что сказали с правилом префикса, как только вы используете запрос диапазона для столбца в своем индексе, вы достигнете того, насколько сможете использовать этот индекс. Это должно иметь смысл, если вы отправили запрос диапазона, например:

ВЫБРАТЬ * ИЗ телефонной книги, ГДЕ last_name LIKE ‘f%’ AND first_name = ’Ned’; ADD INDEX (last_name, first_name, phone_number)

При этом будет использоваться первая часть (last_name) нашего индекса, что позволит нам быстро удовлетворить условному диапазону и найти все строки с last_name, начинающимся с «f»; однако после этого невозможно будет использовать наше B-дерево для быстрой фильтрации по first_name.

* Если вы используете индекс для запросов диапазона, убедитесь, что столбец, в котором выполняется диапазон, упорядочен последним в индексе.

Точно так же вы не можете полностью использовать индекс для выполнения запросов диапазона в двух столбцах для уже упомянутых точек.

Раскрывающийся список условий индекса

При этом есть метод, называемый "выталкивание условия индекса", который может помочь в этом, я не буду вдаваться в подробности об этом, поскольку этот документ предназначен только для начинающих.

Смещение индекса вниз по существу позволяет нам передавать условия индекса в ядро ​​базы данных, чтобы ему не приходилось возвращать нерелевантные строки, которые позже будут отфильтрованы MySQL.

Это означает, что в некоторых случаях мы все еще можем использовать индекс после условия диапазона. Лучше всего протестировать и увидеть результаты самостоятельно, но независимо от ситуации вы будете наиболее эффективны, если сможете оставить столбцы диапазона ближе к концу индекса. ICP будет наиболее полезен, когда у вас нет выбора.

Выводы: как правило, мы можем использовать только крайний левый префикс индекса!

  • Вам может потребоваться иметь индексы для одних и тех же столбцов в разном порядке в зависимости от ваших запросов.
  • Попробуйте использовать как можно больше столбцов до первого диапазона запроса - после диапазона нельзя использовать другие столбцы индекса. Поэтому поместите индекс, который может быть ранжирован, в самом конце.

А как насчет диапазонов префиксов?

ВЫБРАТЬ… ГДЕ last_name КАК «% mithers»

Это не диапазон, очевидно, что мы не можем использовать B-Tree для его обхода. Представьте, что вы находитесь в корне дерева с таким вопросом: вы идете влево или вправо? Вы не знаете! Именно поэтому мы не можем использовать индекс для поддержки такого запроса.

Примеры:

ИНДЕКС (a, b) VS ИНДЕКС (b, a)

  • WHERE a = 1 AND b ›3 *** Первое лучше
  • WHERE b = 5 AND b ›7 *** Вторая лучше
  • ГДЕ a ›1 И b› 3 *** Каждая остановка после 1-го столбца
  • WHERE b = 2 *** Только секунда
  • WHERE b ›2 *** Только секунда
  • WHERE a = 4 *** Только первое
  • ГДЕ a ›4 *** Только первое

ИНДЕКС (a, b, c)

  • WHERE a ›1 AND b = 3 AND c = 4 *** Использует только первую часть индекса
  • WHERE a = 1 AND b ›3 AND c = 4 *** Использует первые 2 части индекса
  • WHERE a = 1 AND b = 3 AND c = 4 *** Использует весь индекс

Полезный трюк: пропустить сканирование

Допустим, у вас есть таблица пользователей со столбцами: пол, фамилия, имя, возраст и т. Д.

Если у вас есть индекс по (пол, фамилия, имя), и вы хотите выполнить запрос:

ВЫБЕРИТЕ first_name, last_name, WHERE last_name = ‘Nahasapeemapetilon’ AND first_name = ‘Apu’;

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

Один из способов изменить свой запрос для использования нашего индекса - это переписать его следующим образом:

ВЫБЕРИТЕ first_name, last_name, WHERE sex IN («мужской», «женский») И last_name = «Nahasapeemapetilon» И first_name = «Apu»;

Вы можете думать об этом как о разрешении MySQL перечислять каждое значение (в данном случае «sex») по отдельности, что позволяет нам погрузиться в остальную часть индекса оттуда. После завершения перечисления каждого значения мы можем просто ОБЪЕДИНИТЬ результаты.

В этом случае: сначала мы используем "M" как пол, чтобы мы могли использовать весь оставшийся индекс для запроса; после того, как мы будем использовать «F» в качестве отправной точки, и снова мы сможем использовать весь наш индекс для удовлетворения запроса. Наконец, мы можем просто объединить полученные наборы «M» и «F», чтобы получить все интересующие нас строки.

Это хорошо работает, когда столбец, используемый в IN, имеет относительно небольшой диапазон значений; однако его эффективность может отличаться. В случае сомнений измерьте время отклика на изменения вашего индекса и при необходимости добавьте новые индексы. Определенно не заставляйте себя использовать единый индекс, который пытается удовлетворить все ради него!

Неоднозначные запросы

Неоднозначные запросы будут медленными, потому что MySQL не может использовать индексы для предотвращения сканирования таблиц:

ИНДЕКС ПО телефонной книге (город, имя, фамилия)

ВЫБРАТЬ * ИЗ телефонной книги ГДЕ город ‹› ‘MPK’ и first_name = ‘Bart’

ВЫБРАТЬ * ИЗ телефонной книги, ГДЕ НЕТ города (‘MPK’, ‘SEA’) и first_name = ‘Bart’

MySQL не сможет использовать индекс для этих запросов; Однако оптимизатор может легко работать с этим:

ВЫБРАТЬ * ИЗ телефонной книги ГДЕ город В ("SPRINGFIELD", "SEA", "NYC") и first_name = "Bart"

В общем, все перечисленное ниже может сделать индекс непригодным для использования:

  • !=
  • <>
  • НЕ НРАВИТСЯ, НЕ В…
  • НЕ СУЩЕСТВУЕТ (SELECT *…) - по сути, ЛЕВОЕ СОЕДИНЕНИЕ, часто эффективно
  • НЕ (выражение)

Не используйте функции в своих запросах

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

Выражение слева должно быть столбцом, например ‹column› ‹оператор› ‹value›.

Когда вы выполните func (column) ‹operator› ‹value›, вы не сможете использовать индекс, и произойдет полное сканирование таблицы.

Примеры:

  • ГДЕ id + 3 = 4; *** ПЛОХО
  • Плохо: WHERE start_date + INTERVAL 1 YEAR ›NOW () *** BAD
  • ГДЕ ГОД (начальная_дата) = 2015 И МЕСЯЦ (начальная_дата) = 1 *** ПЛОХО
  • Где число +0 = 5; *** ПЛОХО
  • ГДЕ func (число) = n; *** ПЛОХО
  • ГДЕ число = 5 + 4; *** ХОРОШО
  • WHERE number = func (n); *** ХОРОШО
  • ГДЕ start_date ›СЕЙЧАС () - ИНТЕРВАЛ 1 ГОД *** ХОРОШО
  • ГДЕ start_date МЕЖДУ «2015–01–01» И «2015–01–31» *** ХОРОШО

Резервные индексы

Чрезмерное индексирование может снизить производительность из-за накладных расходов

Недостатком слишком большого количества индексов является стоимость обслуживания.

Добавление новых индексов может повлиять на производительность операций INSERT, UPDATE и DELETE, особенно если новый индекс приводит к превышению пределов памяти.

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

  • Если есть индекс на (A, B), добавление другого индекса (A) будет излишним, потому что это префикс первого индекса. То есть индекс на (A, B) уже можно использовать в качестве индекса только для столбца A.
  • Если есть индекс на (A, PK_ID). Столбец PK_ID, как вы уже знаете, уже включен, если вы используете InnoDB, поэтому он избыточен, к счастью, он не добавит его дважды, поэтому вы можете сделать это безопасно, вам просто не нужно.

Единственный раз, когда нам нужны избыточные индексы, - это когда расширение существующего индекса делает его намного больше и, следовательно, снижает производительность!

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

При этом не бойтесь добавлять индексы, которые действительно будут использоваться! В читаемом тяжелом приложении затраты будут незначительными. Лучше всего проверить, полезен ли индекс для себя!

Слияние индексов

Когда я сказал, что MySQL использует только один индекс для каждого запроса, для каждой таблицы, в большинстве случаев это правда! Однако иногда MySQL действительно может использовать несколько индексов с одним столбцом.

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

Однако они редко используются на самом деле, и если вы можете сформировать подходящий многоколоночный индекс, вам следует это сделать, потому что он обычно превосходит индекс слияния:

Https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/

Другие варианты использования: индексы ограничений

НЕ ИСПОЛЬЗУЙТЕ УНИКАЛЬНОСТЬ НА УРОВНЕ ПРИЛОЖЕНИЯ

Используйте уникальные индексы, когда вам нужно, чтобы что-то было уникальным, выполнение этого в коде приложения всегда вызовет проблемы в дальнейшем!

  • Скрытые пути кода в обход проверок, особенно с FBcode, WWW и т. Д., Разработчики должны знать, что нужно запускать эти проверки перед изменением базы данных.
  • Люди, управляющие БД напрямую.
  • Код приложения, выполняющийся параллельно в разных транзакциях, может иметь несовместимые представления базы данных, что очень затрудняет соблюдение ограничений.
  • И так далее..

Разрешение мягкого удаления

Если вам нужно поддерживать мягкое удаление, вы можете добиться этого одним из следующих способов:

  • Добавление префикса NULLABLE для столбца tinyint (1) (по умолчанию 1) к вашему уникальному индексу.
  • Добавление отметки удаленного времени (по умолчанию 0) в уникальный индекс.
  • Добавление токена удаления UUID (по умолчанию некоторая константа) в уникальный индекс.
  • Создайте отдельную таблицу «deleted_» и перемещайте туда записи, когда они удаляются.

Скидки

  • Преимущество столбца «active» в том, что его очень просто настроить. Примечание: из-за того, как работает InnoDB, он не обрабатывает NULL как дубликаты, именно поэтому этот метод работает. Неудаленные записи будут иметь значение, например «1», что предотвращает дублирование. Однако удаленные записи будут иметь значение NULL, что позволяет использовать несколько «удаленных» дубликатов.
  • Преимущество токена delete_time_stamp / UUID в том, что они не обязательно должны быть префиксами, мы можем поместить их в конец индексации. Это может обеспечить лучшую производительность при поиске, потому что наши столбцы индекса будут изначально более избирательными, чем столбец `active`, что позволит потенциально более быстрые вставки (каждый раз, когда мы пытаемся вставить, этот индекс нужно будет пройти, чтобы проверить наличие дубликата).
  • Обратной стороной токена UUID является необходимость его генерации, а из-за его случайного распределения в дальнейшем это может вызвать накладные расходы фрагментации; кроме того, его размер может вызвать накладные расходы.
  • Удаленный_время_стамп обеспечивает хорошую производительность без особых усилий.
  • Создание отдельной таблицы обеспечивает высокую производительность "чтения": уменьшение размеров таблицы, возможность использования обычного уникального индекса и возможность повседневного поиска для сканирования меньшего количества записей. Обратной стороной является то, что вам нужно фактически перемещать эти записи между таблицами.
  • Я рекомендую использовать подход deleted_time_stamp, а затем, если позже вы обнаружите потребность в производительности, вы можете перейти к использованию отдельной удаленной таблицы.

Итак, какие столбцы мне следует индексировать?

Из всего того, что мы обсуждали, вы должны понять, что на самом деле это зависит ...

  • Какие столбцы вы собираетесь запрашивать
  • Какие присоединения вы будете выполнять
  • Какие ORDER / GROUP BY и т. Д.
  • Найдите свои медленные запросы (из журнала медленных запросов), если вы не задумываетесь о них, и посмотрите, какие индексы могут их ускорить! Используйте EXPLAIN, чтобы узнать, какие индексы используются в настоящее время. Затем используйте извлеченные вами уроки, чтобы улучшить индексацию. Снова используйте EXPLAIN, чтобы убедиться, что ваш новый индекс эффективен.
  • Не знаете, в каком порядке столбцов? Затем используйте наиболее избирательный подход, если вам не нужен диапазон или порядок и т. Д., Они обычно всегда должны идти до конца.
  • Не индексируйте столбец с низкой избирательностью, например «Секс» сам по себе. Если WHERE пол = ‘F’ встречается ›20% результатов, индекс может быть не таким эффективным (вместо этого оптимизатор может предпочесть сканирование таблицы). В этих случаях просто запустите тест на время отклика, чтобы принять решение.
  • Запросите базу данных INFORMATION SCHEMA, чтобы увидеть статистику по вашим индексам и таблицам, это действительно может помочь вам в настройке производительности!
  • Используйте связанный ранее неиспользуемый скрипт индекса, чтобы найти ошибки индекса
  • Дизайн указателя неотделим от дизайна таблицы. Они идут рука об руку, и вы должны спроектировать все, чтобы удовлетворить ваши запросы.

Звездная система

Хороший способ измерить качество индекса - использовать звездную систему, цель которой - получить все три звезды:

  1. Индекс получает одну звезду, если он размещает соответствующие строки рядом друг с другом или, по крайней мере, близко друг к другу, насколько это возможно. Это минимизирует толщину индексного среза, который необходимо сканировать. Цель состоит в том, чтобы максимально сократить количество строк, которые необходимо просканировать. Чтобы получить эту звезду, вы можете выбрать столбцы из всех одинаковых предикатов в предложении WHERE. Многие думают, что цель индекса - найти отдельные строки, но поиск отдельных строк приводит к случайным операциям с диском (что является медленным). Гораздо лучше найти группы строк, все или большинство из которых интересны, чем искать их по одной. Следовательно, хороший индекс будет группировать ключи вместе таким образом, чтобы это было эффективно.
  2. Вторая звезда присуждается, если индекс сортирует строки в порядке, необходимом для запроса, избегая дополнительных сортировок файлов.
  3. И последняя звезда засчитывается, если индекс покрывает все столбцы, необходимые для запроса, иначе говоря, это индекс покрытия. Все столбцы в SELECT появятся в индексе.

Конечно, в зависимости от вашего запроса, возможно, не удастся выполнить все три!

Подробнее здесь: Дизайн индекса реляционной базы данных и оптимизаторы - Lahdenmaki, Leach

Прочие соображения

  • Когда количество строк исчисляется миллионами, вы должны гораздо внимательнее рассматривать типы данных и индексы. Более легкий тип данных может сэкономить много накладных расходов для большой таблицы! Особенно, если это позволяет хранить в памяти больше информации.
  • Когда количество строк исчисляется миллиардами, сводные таблицы (например, для хранения счетчиков и т. Д.) Могут быть очень эффективными.
  • Удаленные флаги и т. Д. С низкой мощностью могут работать плохо или просто не использоваться. Возможным обходным решением может быть перемещение удаленных строк в другую таблицу.
  • Если вам нужно проиндексировать большой строковый столбец и т. Д., Учитывая только индексирование по префиксу или хешу этого столбца, чтобы избежать чрезмерно больших индексов.

Если вы используете ORM

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

Как всегда, обратная сторона ORM - это абстракция от MySQL, которая может вызвать множество проблем, связанных с производительностью, особенно когда нужно масштабировать.

Как MySQL выберет индекс?

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

Для запросов с одной таблицей набор оцениваемых индексов может быть любым индексом, включая столбцы, указанные в предложении WHERE. Для объединений в нескольких таблицах оптимизатор MySQL попытается выяснить, какая таблица может быть сужена в наибольшей степени с помощью переданных предикатов (как и в запросе одной таблицы), а затем он вычислит, сколько строк должно быть все объединение. сканирование на основе статистики таблицы (например, размера таблицы / индексов). Когда очевидных планов запросов не существует, изменение этой статистики может привести к неоднозначному выбору плана.

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

Тестирование индекса

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

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

Есть также много других факторов, которые влияют на производительность: например, как вы пишете свои запросы, разрабатываете свои схемы и т. д .; Однако эти темы лучше оставить для отдельной статьи.

Спасибо за прочтение!