Принцип оптимизации MySQL

база данных MySQL

Прочитать содержимое (Контент)

предисловие

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

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

перейти к началу

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

 

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

 

​

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

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

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

перейти к началу

Во-вторых, процесс запроса MySQL

 

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

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

​

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

перейти к началу

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

 

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

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

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

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

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

перейти к началу

В-четвертых, кэш запросов

 

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

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

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

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

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

Другой пример: оператор запроса, содержащий CURRENT_USER или CONNECION_ID(), будет возвращать разные результаты для разных пользователей, кэшировать такие результаты запроса бессмысленно.

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

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

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

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

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

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

Заменяя одну большую таблицу несколькими маленькими таблицами, будьте осторожны, чтобы не переусердствовать

Объемная вставка вместо круглой одинарной вставки

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

Вы можете контролировать необходимость кэширования оператора запроса с помощью SQL_CACHE и SQL_NO_CACHE.

 

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

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

перейти к началу

Пять, разбор грамматики и предварительная обработка

 

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

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

перейти к началу

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

 

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

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 стратегии оптимизации, используемые оптимизатором, также постоянно развиваются.Вот лишь несколько очень распространенных и простых для понимания стратегий оптимизации.Для других стратегий оптимизации вы можете проверить их самостоятельно.

перейти к началу

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

 

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

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

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

перейти к началу

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

 

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

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

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

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

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

  • Клиент отправляет запрос на сервер MySQL

  • Сервер сначала проверяет кеш запроса, и если он попадает в кеш, он немедленно возвращает результат, хранящийся в кеше. В противном случае перейти к следующему этапу

  • Сервер выполняет синтаксический анализ SQL, предварительную обработку, а оптимизатор генерирует соответствующий план выполнения.

  • В соответствии с планом выполнения MySQL вызывает API механизма хранения для выполнения запроса.

  • Возврат результата клиенту при кэшировании результата запроса

перейти к началу

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

 

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

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

9.1.Дизайн схемы и оптимизация типов данных

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

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

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

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

2) Указание ширины для целочисленного типа, такого как INT(11), бесполезно. INT использует 32 бита (4 байта) пространства для хранения, затем был определен диапазон его представления, поэтому INT(1) и INT(20) одинаковы для хранения и вычисления.

3) UNSIGNED означает, что отрицательные значения не допускаются, что примерно удваивает верхний предел положительных чисел. Например, диапазон хранения TINYINT — от -128 до 127, а диапазон хранения UNSIGNED TINYINT — от 0 до 255.

4) Вообще говоря, нет необходимости использовать тип данных DECIMAL. Даже когда вам нужно хранить финансовые данные, вы все равно можете использовать BIGINT. Например, он должен иметь точность до одной десятитысячной,

Затем данные можно умножить на миллион и сохранить с помощью BIGINT. Это позволяет избежать проблем неточных вычислений с плавающей запятой и дорогостоящих вычислений DECIMAL.

5) TIMESTAMP использует 4 байта дискового пространства, а DATETIME использует 8 байтов дискового пространства. Таким образом, TIMESTAMP может представлять только годы с 1970 по 2038 год, что намного меньше, чем диапазон, представленный DATETIME, а значение TIMESTAMP зависит от разных часовых поясов.

6) Нет необходимости использовать enum типы в большинстве случаев, один из минусов - список строк enum фиксированный, добавление и удаление строк (опций enum) надо использовать ALTER TABLE (если только дописывать в конце список), нет необходимости перестраивать таблицу).

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

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

8) ALTER TABLE для больших таблиц очень трудоемок.То, как MySQL выполняет большинство операций по изменению результатов таблицы, состоит в том, чтобы создать пустую таблицу с новой структурой, узнать все данные из старой таблицы, вставить ее в таблицу. новую таблицу, а затем удалить старую таблицу. Особенно когда не хватает памяти и таблица очень большая,

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

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

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

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

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

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

Обычно индекс, который мы называем, относится к индексу 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 будет выполняться не разбиение страницы, а поворот влево.

 

​

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

 

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

перейти к началу

10. Высокоэффективная стратегия

 

По вышесказанному я полагаю, что ваша структура данных 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 не будет использовать индексы: ненезависимые столбцы

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

выберите *, где 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), индекс, который включает все связанные столбцы, обычно предпочтительнее, чем несколько независимых индексов.

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

 

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

 

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

 

<section style="margin: 10px 0px; padding: 15px 20px 15px 45px; max-width: 100%; box-sizing: border-box; font-size: 14px; line-height: 22.39px; outline: 0px; border-width: 0px; border-style: initial; border-color: currentcolor; vertical-align: baseline; background-image: url(" http:="" mmbiz.qpic.cn="" mmbiz_jpg="" tibrg3aoijttt5wd7pstdp8xn9fcaqn0hzm4ung7awpvy0vhxe5stzfr97tfcd3orepfelzkiawqpkjmvgnbnenq="" 0?wx_fmt="jpeg&quot;);" ="" background-position:="" 1%="" 5px;="" background-repeat:="" no-repeat;="" word-wrap:="" break-word="" !important;"="">

 

Селективность индекса относится к отношению уникальных значений индекса к общему количеству записей в таблице данных.Чем выше селективность, тем выше эффективность запроса, поскольку индекс с более высокой селективностью может позволить 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 есть два способа создания упорядоченного набора результатов: один — это операция сортировки набора результатов, а другой — естественное упорядочение результатов, полученных путем сканирования в соответствии с порядком индекса. Если значением столбца типа в результате объяснения является индекс, это означает, что для сортировки используется сканирование индекса.

 

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

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

 

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

 

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

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

// 最左列为常数，索引：(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) Отбросьте длинные неиспользуемые индексы

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

 

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

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

перейти к началу

11. Определенный тип оптимизации запроса

 

11.1 Оптимизация запроса COUNT()

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

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

 

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

 

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

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

11.2. Оптимизация связанных запросов

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

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

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

2) Убедитесь, что любые выражения в 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上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

​

11.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; 

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

11.4 Оптимизация UNION

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

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

 

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

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

Вывод

 

 

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

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

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

2) JOIN сам по себе очень удобен, просто запрашивайте напрямую, зачем вам представление?

Категории