Резюме 4D: изучите принципы оптимизации MySQL

Говоря об оптимизации запросов MySQL, я полагаю, что все накопили массу хитростей и хитростей: нельзя использовать SELECT *, не использовать поля NULL, разумно создавать индексы, выбирать подходящие типы данных для полей... Вы действительно понимаете эти Советы по оптимизации? Вы понимаете, как он работает за этим? Действительно ли производительность улучшается в реальных сценариях? Я так не думаю. Поэтому особенно важно понимать принципы, лежащие в основе этих предложений по оптимизации.Я надеюсь, что эта статья позволит вам пересмотреть эти предложения по оптимизации и разумно применить их в реальных бизнес-сценариях.

Логическая архитектура MySQL

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

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

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

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

Процесс запроса MySQL

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

Что именно делает MySQL при отправке запроса в MySQL?

Процесс запроса MySQL

Протокол связи клиент/сервер

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

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

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

кэш запросов

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

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

Если запрос содержит какие-либо определяемые пользователем функции, хранимые функции, пользовательские переменные, временные таблицы и системные таблицы в библиотеке MySQL, результаты запроса не будут кэшироваться. Например, функции NOW() или CURRENT_DATE() вернут разные результаты запроса из-за разного времени запроса. Например, оператор запроса, содержащий CURRENT_USER или CONNECION_ID(), вернет разные результаты из-за разных пользователей. Кэшировать такие результаты запроса нет никакого смысла.

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

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

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

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

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

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

Разбор и предварительная обработка

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

Оптимизация запросов

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

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

mysql> select * from t_message limit 10;

... опуская набор результатов

mysql> show status like 'last_query_cost';

+-----------------+-------------+

| Variable_name | Value |

+-----------------+-------------+

| Last_query_cost | 6391.799000 |

+-----------------+-------------+

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

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

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

Переопределить порядок ассоциации таблицы (когда с запросом связано несколько таблиц, он не обязательно следует порядку, указанному в SQL, но есть некоторые приемы для указания порядка ассоциации)
Оптимизируйте функции MIN() и MAX() (чтобы найти минимальное значение столбца, если столбец имеет индекс, вам нужно только найти крайний левый конец индекса B+Tree, в противном случае вы можете найти максимальное значение, см. ниже конкретный принцип)
Завершить запрос досрочно (например: при использовании Limit запрос будет прекращен сразу после нахождения достаточного количества наборов результатов)
Оптимизированная сортировка (в старой версии MySQL используется двухпереходная сортировка, то есть сначала чтение указателя строки и сортируемого поля, сортировка его в памяти, а затем чтение строки данных по результату сортировки, при этом новая версия использует одиночную сортировку Sub-transfer, то есть чтение всех строк данных сразу, а затем сортировку по заданному столбцу.Для приложений с интенсивным вводом-выводом эффективность будет намного выше)

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

механизм выполнения запросов

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

Вернуть результат клиенту

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

Если кеширование запросов включено и запрос может быть закэширован, MySQL также сохранит результат в кеше.

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

Вернитесь назад и суммируйте весь процесс выполнения запросов MySQL, который обычно делится на 6 шагов:

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

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

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

Разработка схемы и оптимизация типов данных

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

Вот несколько советов, которые помогут понять ошибки:

Вообще говоря, изменение столбца, допускающего NULL, на NOT NULL не сильно улучшит производительность, но если вы планируете создать индекс для столбца, вам следует установить для столбца значение NOT NULL.
Указание ширины для целочисленных типов, таких как INT(11), ничего не делает. INT использует 32 бита (4 байта) пространства для хранения, затем был определен диапазон его представления, поэтому INT(1) и INT(20) одинаковы для хранения и вычисления.
UNSIGNED означает, что отрицательные значения не допускаются, что примерно удваивает верхний предел положительных чисел. Например, диапазон хранения TINYINT — от -128 до 127, а диапазон хранения UNSIGNED TINYINT — от 0 до 255.
Вообще говоря, нет особой необходимости использовать тип данных DECIMAL. Даже когда вам нужно хранить финансовые данные, вы все равно можете использовать BIGINT. Например, если вам нужна точность до одной десятитысячной, вы можете умножить данные на один миллион и сохранить их с помощью BIGINT. Это позволяет избежать проблем неточных вычислений с плавающей запятой и дорогостоящих вычислений DECIMAL.
TIMESTAMP использует 4 байта дискового пространства, а DATETIME использует 8 байтов дискового пространства. Таким образом, TIMESTAMP может представлять только годы с 1970 по 2038 год, что намного меньше, чем диапазон, представленный DATETIME, а значение TIMESTAMP зависит от разных часовых поясов.
В большинстве случаев нет необходимости использовать тип перечисления, один из недостатков - список строк перечисления фиксирован, добавление и удаление строк (опций перечисления) необходимо использовать ALTER TABLE (если только дописывание элементов в конце список, не нужно перестраивать таблицу).
В схеме не должно быть слишком много столбцов. Причина в том, что API механизма хранения должен копировать данные между уровнем сервера и уровнем механизма хранения через формат буфера строк, а затем декодировать содержимое буфера в каждый столбец на уровне сервера.Этот процесс преобразования очень дорог. Если столбцов слишком много и фактически используется мало столбцов, это может привести к высокой загрузке ЦП.
ALTER TABLE для больших таблиц очень трудоемок.То, как MySQL выполняет большинство операций, которые изменяют результаты таблицы, состоит в том, чтобы создать пустую таблицу с новой структурой, найти все данные из старой таблицы, вставить их в новую таблицу, а затем удалить старую таблицу. Особенно когда мало памяти и таблица очень большая, а индексы еще большие, это займет больше времени. Конечно, есть некоторые приемы и хитрости для решения этой проблемы, можете сами проверить, если интересно.

Создание высокопроизводительных индексов

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

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

Структуры данных и алгоритмы, связанные с индексами

Индекс, на который мы обычно ссылаемся, относится к индексу B-Tree, который является наиболее часто используемым и эффективным индексом для поиска данных в реляционных базах данных, и большинство механизмов хранения поддерживают этот индекс. Термин B-Tree используется потому, что MySQL использует это ключевое слово в CREATE TABLE или других операторах, но на самом деле разные механизмы хранения могут использовать разные структуры данных.Например, InnoDB использует B+Tree.

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

Прежде чем знакомиться с B+Tree, давайте взглянем на бинарное дерево поиска.Это классическая структура данных.Значение левого поддерева всегда меньше значения корня, а значение правого поддерева всегда больше значение корня, как показано на рисунке ①. Если вы хотите найти запись со значением 5 в дереве этого урока, общий процесс таков: сначала найдите корень, значение которого равно 6, что больше 5, поэтому найдите левое поддерево, найдите 3 и 5 больше, чем 3, затем найти правильный ребенок 3 дерева, в общей сложности 3 раза, чтобы найти. Точно так же, если вы ищете запись со значением 8, вам нужно искать 3 раза. Следовательно, среднее количество поисков в двоичном дереве поиска составляет (3 + 3 + 3 + 2 + 2 + 1)/6 = 2,3 раза, а в последовательном поиске требуется только 1 поиск, чтобы найти запись со значением из 2, но значение поиска равно 8, нужно 6 раз, поэтому среднее время последовательного поиска равно: (1 + 2 + 3 + 4 + 5 + 6) / 6 = 3,3 раза, поэтому средняя скорость поиска бинарного дерева поиска в большинстве случаев выше, чем у последовательного поиска.

Бинарное дерево поиска и сбалансированное бинарное дерево

Поскольку бинарное дерево поиска может быть построено произвольно, одно и то же значение может построить бинарное дерево поиска, как показано на рисунке 2. Очевидно, что эффективность запроса этого бинарного дерева аналогична эффективности последовательного поиска. Если вам нужна самая высокая производительность запросов двоичного числа поиска, вам нужно, чтобы двоичное дерево поиска было сбалансированным, то есть сбалансированным двоичным деревом (дерево AVL).

Сбалансированное бинарное дерево должно, во-первых, соответствовать определению бинарного дерева поиска, а во-вторых, разница высот между двумя поддеревьями любого узла не должна превышать 1. Очевидно, что граф ② не удовлетворяет определению сбалансированного бинарного дерева, а граф 1 является сбалансированным бинарным деревом. Производительность поиска сбалансированного бинарного дерева относительно высока (наилучшая производительность — это оптимальное бинарное дерево).Чем выше производительность запроса, тем выше стоимость обслуживания. Например, в сбалансированном двоичном дереве на рисунке 1, когда пользователю нужно вставить новый узел со значением 9, необходимо внести следующие изменения.

Сбалансированное вращение бинарного дерева

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

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

Эффективным решением является уменьшение глубины дерева и превращение бинарного дерева в m-арное дерево (многоходовое дерево поиска), а B+Tree — многоходовое дерево поиска. При понимании B+Tree вам нужно понять только две его наиболее важные особенности: во-первых, все ключевые слова (которые можно понимать как данные) хранятся в конечных узлах (конечная страница), неконечных узлах (индексная страница) и нет реальных данные сохраняются, все узлы записи хранятся в одном слое конечных узлов в порядке размера значения ключа. Во-вторых, все листовые узлы связаны указателями. На следующем рисунке показано упрощенное дерево B+ с высотой 2.

Упростить B+дерево

Как понять эти две характеристики? MySQL устанавливает размер каждого узла в целое число, кратное странице (причина будет описана ниже), то есть, когда размер пространства узла определен, каждый узел может хранить больше внутренних узлов, так что каждый узел может хранить больше внутренних узлов.Диапазон индексов больше и точнее. Преимущество использования указателей для всех конечных узлов заключается в том, что они могут выполнять доступ к диапазону.Например, на приведенном выше рисунке, если вы ищете записи больше 20, но меньше 30, вам нужно найти только узел 20, и вы может перемещаться по указателю, чтобы найти 25 и 30 по очереди. Если нет указателя ссылки, поиск диапазона не может быть выполнен. Это также важная причина, по которой MySQL использует B+Tree в качестве структуры хранения индексов.

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

«Страница — это логический блок памяти, которым управляет компьютер. Аппаратное обеспечение и ОС часто делят основную память и дисковое хранилище на смежные блоки одинакового размера. Каждый блок хранилища называется страницей (во многих ОС размер страницы равен обычно 4K) 。 Основная память и диск обмениваются данными в единицах страниц. Когда данные, которые должны быть прочитаны программой, не находятся в основной памяти, будет вызвано исключение ошибки страницы. В это время система отправит сигнал чтения на диск, и диск найдет начало позиции данных и последовательно прочитает одну или несколько страниц обратно в память, затем вернется вместе, и программа продолжит работу."

MySQL умело использует принцип упреждающего чтения с диска, чтобы установить размер узла равным одной странице, так что каждый узел может быть полностью загружен только одним вводом-выводом. Для достижения этой цели каждый раз, когда создается новый узел, он напрямую обращается к странице пространства, что гарантирует, что узел также физически хранится на странице, а распределение памяти компьютера выравнивается по страницам, что реализует чтение узел.Только один ввод-вывод. Если предположить, что высота B+Tree равна h, для извлечения требуется не более h-1I/O (резидентная память корневого узла), а сложность $O(h) = O(\log_{M}N)$. В сценариях практического применения М обычно велико, часто превышает 100, поэтому высота дерева, как правило, невелика, обычно не более 3.

Наконец, давайте кратко разберемся в работе узла B+Tree и получим общее представление о поддержании индекса в целом.Хотя индекс может значительно повысить эффективность запросов, поддержание индекса все еще стоит дорого, поэтому разумное создание индекса также особенно важно.

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

Ни конечная страница, ни индексная страница не заполнены

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

Листовая страница разделена

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

Конечная страница и индексная страница разделены

После расщепления такое дерево окончательно формируется.

последнее дерево

Для поддержания баланса B+Tree необходимо выполнять множество операций разделения страниц для вновь вставленных значений, а для разделения страниц требуются операции ввода-вывода.Чтобы максимально сократить количество операций разделения страниц, B+Tree также предоставляет аналогичные Функция вращения для сбалансированного бинарного дерева. Когда конечная страница заполнена, но ее левый и правый одноуровневые узлы не заполнены, B+Tree не спешит выполнять операцию разделения, а перемещает запись на одноуровневый узел текущей страницы. Обычно левый соседний элемент сначала проверяется на наличие операций поворота. Например, во втором примере выше при вставке 70 будет выполняться не разбиение страницы, а поворот влево.

Операция левого вращения

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

Высокопроизводительная стратегия

Я полагаю, что благодаря вышеизложенному у вас есть общее представление о структуре данных B+Tree, но как индекс в MySQL организует хранение данных? Чтобы проиллюстрировать на простом примере, если есть следующая таблица данных:

CREATE TABLE People(

last_name varchar(50) not null,

first_name varchar(50) not null,

dob date not null,

gender enum(`m`,`f`) not null,

key(last_name,first_name,dob)

);

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

Как индексы организуют хранение данных, из: High-Performance MySQL

Видно, что индекс сначала сортируется по первому полю, а при совпадении имен сортируется по третьему полю, то есть по дате рождения. «крайний левый принцип» индекса.

1, MySQL не будет использовать индекс: ненезависимые столбцы

«Отдельный столбец» означает, что индексированный столбец не может быть частью выражения или аргументом функции. Например:

select * from where id + 1 = 5

Легко видеть, что это эквивалентно id = 4, но MySQL не может автоматически анализировать это выражение, и то же самое верно для использования функций.

2, индекс префикса

Если столбец очень длинный, вы обычно можете проиндексировать некоторые символы в начале, что может эффективно сэкономить место в индексе и повысить эффективность индекса.

3, многоколоночные индексы и порядок индексов

В большинстве случаев создание независимых индексов для нескольких столбцов не повышает производительность запросов. Причина очень проста: MySQL не знает, какой индекс выбрать более эффективно для выполнения запросов, поэтому в старой версии, такой как MySQL 5.0, он будет случайным образом выбирать индекс для столбца, а новая версия примет стратегию слияние индексов. Простой пример: в таблице составов фильмов устанавливаются независимые индексы для столбцов act_id и film_id, а затем выполняется следующий запрос:

select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1

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

select film_id,actor_id from film_actor where actor_id = 1

union all

select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1

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

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

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

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

После понимания концепции селективности индекса нетрудно определить, какое поле имеет более высокую селективность, просто проверьте это, например:

SELECT * FROM payment where staff_id = 2 and customer_id = 584

Должен ли быть создан индекс (staff_id, customer_id) или порядок должен быть обратным? Выполните следующий запрос, в зависимости от того, какое поле имеет селективность ближе к 1 и какое поле индексируется первым.

select count(distinct staff_id)/count(*) as staff_id_selectivity,

count(distinct customer_id)/count(*) as customer_id_selectivity,

count(*) from payment

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

select user_id from trade where user_group_id = 1 and trade_amount > 0

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

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

4, избегайте множественных условий диапазона

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

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;

Есть проблема с этим запросом: он имеет два условия диапазона, столбец login_time и столбец age, MySQL может использовать либо индекс столбца login_time, либо индекс столбца age, но не оба.

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

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

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

6, используйте сканирование индекса для сортировки

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

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

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

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

// Крайний левый столбец постоянный, индекс: (date,staff_id,customer_id)

select staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id

7, избыточные и повторяющиеся индексы

Избыточные индексы — это индексы одного типа, созданные в одном и том же порядке для одних и тех же столбцов. Таких индексов следует по возможности избегать и удалять сразу после обнаружения. Например, если есть индекс (A, B), то создание индекса (A) является избыточным индексом. Избыточные индексы часто возникают, когда в таблицу добавляется новый индекс, например, кто-то создает новый индекс (A, B), но этот индекс не является расширением существующего индекса (A).

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

8, удалять индексы, которые давно не использовались

Очень полезно периодически удалять некоторые индексы, которые не использовались в течение длительного времени.

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

Оптимизация запросов для конкретных типов
1. Оптимизируйте запрос COUNT()

Функция COUNT() может быть наиболее неправильно понятой, поскольку у нее есть две разные функции: одна для подсчета количества значений столбца, а другая — для подсчета количества строк. При подсчете значений столбца значение столбца должно быть ненулевым, и оно не будет учитывать NULL. Если вы подтверждаете, что выражение в круглых скобках не может быть пустым, вы фактически подсчитываете количество строк. Самый простой заключается в том, что при использовании COUNT(*) он не расширяется на все столбцы, как мы себе представляли, на самом деле он игнорирует все столбцы и считает все строки напрямую.

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

Иногда некоторые бизнес-сценарии не требуют абсолютно точного значения COUNT, и вместо него можно использовать приблизительное значение.Количество строк из EXPLAIN является хорошим приближением, и выполнение EXPLAIN не требует фактического выполнения запроса, поэтому стоимость очень высока. низкий. Вообще говоря, выполнение COUNT() требует сканирования большого количества строк для получения точных данных, поэтому его сложно оптимизировать, и единственное, что можно сделать на уровне MySQL, — это покрывающий индекс. Если проблема не может быть решена, ее можно решить только на архитектурном уровне, например, добавив сводную таблицу или используя внешнюю систему кэширования, такую как Redis.

2. Оптимизируйте связанный запрос

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

Убедитесь, что в предложениях ON и USING есть индексы для столбцов. При создании индекса следует учитывать порядок ассоциаций. Когда таблица A и таблица B связаны со столбцом c, если оптимизатор связывает их в порядке A, B, то нет необходимости создавать индекс для соответствующего столбца таблицы A. Неиспользуемые индексы несут дополнительную нагрузку, вообще говоря, если нет других причин, необходимо создавать индексы только на соответствующие столбцы второй таблицы в порядке ассоциации (конкретные причины будут проанализированы ниже).
Убедитесь, что любые выражения GROUP BY и ORDER BY включают только один столбец таблицы, чтобы MySQL потенциально мог использовать индексы для оптимизации.

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

Слишком абстрактно? Возьмем приведенный выше пример для иллюстрации, например, есть такой запрос:

SELECT A.xx,B.yy

FROM A INNER JOIN B USING(c)

WHERE A.xx IN (5,6)

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

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);

outer_row = outer_iterator.next;

while(outer_row) {

inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;

inner_row = inner_iterator.next;

while(inner_row) {

output[inner_row.yy,outer_row.xx];

inner_row = inner_iterator.next;

}

outer_row = outer_iterator.next;

}

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

3. Оптимизируйте пейджинг LIMIT

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

Распространенная проблема заключается в том, что при очень большом смещении, таком как запрос типа LIMIT 10000 20, MySQL необходимо запросить 10020 записей, а затем вернуть только 20. Первые 10000 записей будут отброшены, что очень дорого.

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

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

Если таблица очень большая, то запрос лучше изменить на следующий:

SELECT film.film_id,film.description

FROM film INNER JOIN (

SELECT film_id FROM film ORDER BY title LIMIT 50,5

) AS tmp USING(film_id);

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

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

SELECT id FROM t LIMIT 10000, 10;

Измените его на:

SELECT id FROM t WHERE id > 10000 LIMIT 10;

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

4. Оптимизируйте СОЮЗ

Стратегия MySQL по работе с UNION состоит в том, чтобы сначала создать временную таблицу, затем вставить каждый результат запроса во временную таблицу и, наконец, выполнить запрос. Поэтому многие стратегии оптимизации плохо работают в запросах UNION. Часто бывает необходимо вручную «протолкнуть» предложения, такие как WHERE, LIMIT, ORDER BY, в каждый подзапрос, чтобы оптимизатор мог в полной мере воспользоваться этими условиями для оптимизации в первую очередь.

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

Эпилог

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

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

Есть много программистов, которые бросят такую точку зрения при обмене: старайтесь по возможности не использовать хранимые процедуры.Хранимые процедуры очень сложны в обслуживании и увеличат стоимость использования.Бизнес-логику следует размещать на стороне клиента . Поскольку клиенты могут делать это, зачем хранить процедуры?
JOIN сам по себе очень удобен, просто запросите его напрямую, зачем вам представление?

использованная литература

Цзян Чэнъяо, MySQL Technology Insider-InnoDB Storage Engine, Machinery Industry Press, 2013 г.
Baron Scbwartz et al., перевод Ning Haiyuan, Zhou Zhenxing et al., High Performance MySQL (третье издание), Electronic Industry Press, 2013 г.
Глядя на структуру индекса MySQL из дерева B-/B+

https://segmentfault.com/a/1190000004690721

Перепечатано с разрешения автора

Источник: Цзяньшу

Автор: Чен Чуань