Поймите предложения по оптимизации MySQL в принципе

Обзор

С момента изучения MySQL мы слышали или видели много предложений по оптимизации, например, не используйте select * query, выполняйте поиск по любому полю; рекомендуется использовать автоинкрементный первичный ключ в качестве первичного ключа таблицы , и так далее. Многие из этих предложений стали здравым смыслом при разработке MySQL, но задумывались ли мы когда-нибудь над этими предложениями по оптимизации, почему мы это делаем? В этом сообщении блога мы начнем с принципа MySQL, чтобы объяснить, почему существуют эти предложения по оптимизации?

★
Экспериментальная среда этой статьи — MySQL 5.7.25.
"

Предварительные знания

Индекс дерева B+

Механизм хранения MySQL по умолчанию, InnoDB, использует деревья B+ для хранения данных, поэтому необходимо понять основные принципы индексов дерева B+, прежде чем анализировать предложения по оптимизации.

На рисунке выше представлена схема индекса дерева B+ (Определение дерева B+ можно увидеть здесь.), каждый узел представляет собой дисковый блок, который также можно понимать как страницу в базе данных. Давайте проанализируем процесс поиска индекса дерева B+.Если я хочу запросить данные, первичный ключ которых равен 35, как будет работать индекс? Во-первых, он решит, что 35 меньше корневого узла 37, продолжит запрос к левому поддереву, решит, что 35 больше, чем 22 и 33, затем войдет в его правое поддерево, найдет конечный узел 33, продолжит обход, чтобы найти 35. , и, наконец, выньте его данные. В случае с индексом запрос 35 занимает всего 3 операции ввода-вывода, что очень эффективно. В реальном сценарии трехуровневое дерево b+ может представлять миллионы данных. Если для поиска миллионов данных требуются только три операции ввода-вывода, повышение производительности будет огромным. Если нет индекса, каждый элемент данных должен будет встречаться один раз. IO, то в общей сложности требуются миллионы IO, очевидно, что стоимость очень и очень высока. На приведенном выше рисунке также показано, что пока высота дерева остается достаточно низкой, операций ввода-вывода будет достаточно мало, количество операций ввода-вывода будет небольшим, а производительность запросов будет высокой.

Объясните план выполнения

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

id:Номер запроса в плане выполнения, выбранном оптимизатором запросов.

select_type:Используется несколько типов запросов:

имя типа	инструкция
SIMPLE	Запросы, отличные от подзапросов или запросов UNION.
PRIMARY	самый внешний запрос в подзапросе,Обратите внимание, что это не запрос первичного ключа.
UNION	Для всех SELECT после второго SELECT в операторе UNION первый SELECT является PRIMARY.
DEPENDENT UNION	UNION в подзапросе и все последующие SELECT в UNION, начиная со второго SELECT, также зависят от набора результатов внешнего запроса.
UNION RESULT	Объединенный результат в UNION.
SUBQUERY	Первый SELECT внутреннего запроса подзапроса, результат не зависит от набора результатов внешнего запроса.
DEPENDENT SUBQUERY	Первый SELECT внутреннего запроса подзапроса, результат зависит от набора результатов внешнего запроса.
DERIVED	Приводная таблица является основной таблицей
MATERIALIZED	Результат подзапроса сохраняется в виде виртуальной временной таблицы.
UNCACHEABLE SUBQUERY	Подзапросы, наборы результатов которых нельзя кэшировать
UNCACHEABLE UNION	Запросы UNION, наборы результатов которых нельзя кэшировать

table:Отображает имя таблицы в базе данных, доступ к которой осуществляется на этом шаге.
partitions:Запросите секции, соответствующие секционированной таблице, и отобразите NULL для несекционированных таблиц.

type:Метод, используемый таблицей запросов, имеет следующие типы:

имя типа	инструкция
all	Полное сканирование таблицы.
const	При чтении констант соответствует не более одной записи, а поскольку это константа, ее нужно прочитать только один раз.
system	Системная таблица, в таблице только одни данные, это специальный константный тип.
eq_ref	В лучшем случае будет сопоставлен только один результат, доступ к которому обычно осуществляется через первичный ключ или уникальный индекс.
ref	Индексный запрос управляемой таблицы в операторе соединения
full_text	Использовать полнотекстовый индекс
ref_or_null	Единственная разница с ref заключается в добавлении запроса с нулевым значением в дополнение к индексному запросу.
index_merge	Используйте два (или более) индекса одновременно в запросе, а затем прочитайте данные таблицы после слияния результатов индекса.
unique_subquery	Возвращаемая комбинация полей результата в подзапросе является первичным ключом или уникальным индексом.
index_subquery	Возвращаемая комбинация полей результата в подзапросе является индексом, но не первичным ключом или уникальным индексом.
range	Сканирование диапазона индексов, часто появляющееся в условиях сравнения, таких как: , МЕЖДУ и т. д.
Index	полное сканирование индекса

Их производительность, от хорошей к худшей, такова:system > const > eq_ref > ref > full_text > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > all.

possible_keys:Индексы, которые могут использоваться запросом.
key_len:Длина используемого индекса.
ref:Показывает сравнение этих столбцов или констант с индексом совпадений.
rows:Количество строк, просканированных этим запросом.
filtered:Процент отфильтрованных строк, максимальное значение равно 100, когда отображается 100, это означает, что отфильтрованных строк нет, строки показывают предполагаемое количество проверенных строк, умножение на процент фильтрации покажет количество строк, связанных с таблицей ниже . Например, если количество строк равно 1000, а условие фильтрации равно 50,00 (50%), количество строк, соединенных со следующей таблицей, равно 1000 × 50% = 500.
extra:Выполнить запрос с дополнительными условиями,Подробные условия можно посмотреть здесь.

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

Почему рекомендуется использовать автоматически увеличивающийся первичный ключ?

Когда мы создаем таблицу каждый раз, когда рассматриваем возможность использования самоувеличивающегося первичного ключа таблицы? Или использовать UUID? Но с точки зрения производительности мы все же рекомендуем использовать автоинкрементный идентификатор, почему? В основном из-за характера индекса дерева B+ MySQL новые данные предназначены для обновления индекса, то есть для обновления дерева B+. Другими словами, что быстрее и дешевле обновить дерево B+ с использованием автоинкрементного идентификатора или без автоинкрементного идентификатора, является лучшим выбором. Давайте смоделируем случай вставки идентификатора с автоматическим увеличением и вставки идентификатора без автоматического увеличения.

Вставка самоувеличивающегося идентификатора:Мы вставляем 2 фрагмента данных, 10 и 11, в число B+, в котором уже есть 10 фрагментов данных, и видим, как меняется дерево.

自增id — идентификатор автоматического увеличения

Здесь мы можем найти две особенности:

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

2. За исключением самого правого поддерева, узлы остальных поддеревьев заполнены.

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

Вставка несамостоятельно увеличивающегося идентификатора:

非自增id — не увеличивающийся идентификатор

Характеристики вставки идентификатора без автоматического увеличения и вставки идентификатора с автоматическим увеличением можно легко узнать:

1. Вставка влияет на неуправляемые и непредсказуемые узлы.

2. Каждое поддерево имеет ситуацию, когда листовые узлы не удовлетворены.

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

Почему бы не использовать select * запросы

Мы часто слышим о таблице запросов, если вы запрашиваете нужные поля, не запрашиваете поля, которые вам не нужны, и строго запрещено использоватьselect *, мы можем придумать очень интуитивную причину, по которой база данных должна помочь вам преобразовать имя каждого поля в запрос, а затем запрос избыточных полей будет занимать такие ресурсы, как память и пропускная способность. Это действительно причина, и это важная причина, но я хочу сказать здесь еще одну причину,индекс покрытия. Одна из моих предыдущих статей об индексировании также касалась охвата индексов,Заинтересованные студенты могут нажать здесь. Покрывающий индекс означает, что запрос использует объединенный индекс для покрытия запрашиваемого поля, так что базе данных не нужно возвращаться к таблице, тем самым сокращая количество операций ввода-вывода и повышая производительность.

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

я выбираюemployeesДемонстрируются табличные данные, данные по умолчанию не являются совместным индексом, мы добавляем совместный индекс:

---employee表结构-----------
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   | MUL | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

ALTER TABLE employees.employees add Index `idx_first_name_last_name` (first_name,last_name);

Проверьте индекс этой таблицы:show index from employees.employees;

Таблица успешно созданаfirst_nameиlast_nameЧтобы соответствовать индексу, мы включаем профили для отслеживания выполнения SQL.

SET SESSION profiling = 1;

Затем выполните следующий SQL соответственно

SELECT first_name,last_name  FROM employees.employees WHERE first_name='Eric';
SELECT *  FROM employees.employees WHERE first_name='Eric';

Просмотр профилей;

Здесь мы видим, что использование select * примерно в 4 раза медленнее, чем поле select, почему? Посмотрим на план выполнения.

Мы видим, что планы выполнения двух почти одинаковы, отличается только Дополнительный.Дополнительный поля используется для отображения с использованием индекса, который говорит вам, что вы можете найти нужные данные, используя только индекс, потому что ваш индекс используетfirst_nameиlast_nameустановлен, в то время как select * также требует запросаgenderиhire_dateполе (поле первичного ключа не нужно искать дополнительно, а вторичный индекс указывает на первичный ключ), поэтому он не может запрашивать другие поля, не возвращаясь в таблицу, и здесь тоже есть разница в производительности.

Суммировать

В этой статье анализируются два наших ежедневных предложения в принципе.Почему рекомендуется использовать автоинкрементный первичный ключ? Почему не рекомендуется использовать запросы select *? На самом деле, основная конечная причина связана с индексом.Поскольку мы используем индекс для повышения нашей эффективности, мы должны в полной мере использовать его.Ниже приводится краткое изложение точек знаний:

1. Эффективность запроса дерева B+ зависит от его высоты: чем меньше высота дерева, тем меньше операций ввода-вывода при запросе и выше производительность.

2. Типы планов выполнения в порядке убывания:system > const > eq_ref > ref > full_text > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > all.

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

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

5. Покрывающий индекс — хороший метод оптимизации, который позволяет запросам возвращать данные непосредственно через индекс, не возвращаясь к таблице, сокращая количество операций ввода-вывода и повышая производительность.

Ссылаться на

1. https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

2. https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

3. http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Подпишитесь на официальный аккаунт и наслаждайтесь первым обновлением.