База данных всегда является самой важной частью приложения. В то же время база данных часто становится узким местом, когда она достигает стадии высокого параллелизма. Если таблицы и индексы базы данных не были хорошо спроектированы в начале, горизонтальное расширение база данных, подбаза данных и подтаблицы столкнутся с трудностями на более позднем этапе.
Для интернет-компаний обычно используются базы данных Mysql.
Во-первых, общая структура базы данных
Давайте сначала посмотрим на общую структуру данных Mysql следующим образом:
Это очень классическая схема архитектуры системы Mysql, с помощью которой мы можем увидеть функции различных частей Mysql.
Когда клиент подключается к базе данных, первое, с чем он сталкивается, этопул соединений, используется для управления подключениями пользователей и выполняет определенную аутентификацию и проверку подлинности.
После подключения к базе данных клиент будет отправлять операторы SQL иSQL-интерфейсЭтот модуль должен принимать оператор SQL пользователя.
Операторы SQL часто должны соответствовать строгим грамматическим правилам, поэтомупарсерРазберите оператор, и принцип разбора грамматики, изученный в принципе компиляции, изменится с оператора на синтаксическое дерево.
Запрос, к которому принадлежит пользователь, может быть оптимизирован таким образом, чтобы можно было выбрать самый быстрый путь запроса, т. е.оптимизаторэффект.
Для ускорения запроса будеткэш запросовмодуля, если в кеше запросов есть результат запроса попадания, оператор запроса может напрямую перейти в кеш запросов, чтобы получить данные.
Все вышеперечисленные компонентысервисный уровень базы данных, с последующимслой ядра базы данных, текущим основным механизмом базы данных является InnoDB.
При любых изменениях в базе данных уровень службы базы данных будет записывать их в двоичный журнал, который является основой для первичной и вторичной репликации.
Для уровня ядра базы данных хорошо известная диаграмма выглядит следующим образом:
На уровне механизма хранения также есть кэши и логи, а конечные данные попадают на диск.
Кэш уровня механизма хранения также используется для повышения производительности, но в отличие от кеша уровня службы базы данных, кеш слоя службы базы данных является кешем запросов, в то время как кеш слоя механизма базы данных кэшируется как для чтения, так и для чтения. пишет. Кэш уровня службы базы данных основан на логике запросов, в то время как кеш механизма ядра базы данных основан на страницах данных, которые можно назвать физическими.
Даже если данные записываются только в кеш на уровне механизма базы данных, для уровня службы базы данных, даже если они являются постоянными, конечно, на этот раз это вызовет несогласованность данных между страницей кеша и страницей на жестком диске. целостность гарантируется журналами уровня ядра базы данных.
Таким образом, журнал уровня ядра базы данных также отличается от журнала уровня обслуживания базы данных.Журнал уровня обслуживания записывает логику модификации одну за другой, в то время как журнал уровня механизма записывает физическую разницу между страницей кэша и страница данных.
Рабочий процесс базы данных
Когда запрос получен, различные компоненты архитектуры Mysql работают следующим образом:
Клиент устанавливает TCP-соединение с сервисным уровнем базы данных, а модуль управления соединениями устанавливает соединение и запрашивает поток соединения. Если в пуле соединений есть свободный поток подключения, он будет назначен этому соединению, если нет, будет создан новый поток подключения, который будет отвечать за этого клиента, не превышая максимальное количество подключений.
Перед реальной операцией также необходимо вызвать пользовательский модуль, чтобы выполнить проверку авторизации, чтобы проверить, есть ли у пользователя разрешение. После прохождения служба предоставляется, и поток соединения начинает получать и обрабатывать оператор SQL от клиента.
После получения инструкции SQL поток соединения передает инструкцию модулю разбора инструкции SQL для анализа синтаксиса и семантического анализа.
Если это оператор запроса, вы можете сначала проверить, есть ли результат в кеше запроса, и если есть результат, вы можете напрямую вернуть его клиенту.
Если в кэше запросов нет результата, вам нужно фактически запросить уровень ядра базы данных, поэтому он отправляется оптимизатору SQL для оптимизации запроса. Если это изменение таблицы, оно будет передано модулям обработки вставки, обновления, удаления, создания и изменения для обработки.
Следующим шагом является запрос уровня механизма базы данных, открытие таблицы и при необходимости получение соответствующей блокировки.
Следующий процесс касается уровня ядра базы данных, такого как InnoDB.
На уровне ядра базы данных необходимо сначала запросить, есть ли соответствующие данные в странице кеша, если есть, то их можно вернуть напрямую, если нет, то их нужно прочитать с диска.
Когда соответствующие данные будут найдены на диске, они будут загружены в кеш, что сделает последующие запросы более эффективными.Из-за ограниченного объема памяти гибкие таблицы LRU часто используются для управления страницами кеша, чтобы обеспечить наличие в кеше всех часто используемых данных. .
После получения данных возвращаем их клиенту, закрываем соединение, освобождаем поток соединения, и процесс завершается.
В-третьих, принцип индексации базы данных
Во всем процессе наиболее часто называемой узким местом является чтение и запись данных, что часто означает чтение и запись на диск последовательно или случайным образом, а скорость чтения и записи на диск часто относительно низкая.
А если ускорить процесс? Думаю, все уже догадались, что это для создания индекса.
Почему индекс ускоряет этот процесс?
Я считаю, что все побывали в фуд-сити.В нем много ресторанов.Если вы никуда не торопитесь, вы не голодны, и у вас нет требований к выполнению обыска, то можете не спеша прогуляться по торговому центру. , посетите один из магазинов и знайте, что вы найдете то, что хотите поесть. ресторан. Но когда вы голодны или у вас назначена встреча в ресторане, вы должны захотеть пойти прямо в этот ресторан.В это время вы часто смотрите на карту этажей, чтобы быстро найти местоположение вашего целевого ресторана.Найдя его , переходите сразу к теме.Это очень сильно сэкономит время, которое играет роль индекса.
Таким образом, индекс должен быстро найти свою позицию через значение, чтобы к нему можно было быстро получить доступ.
Другая функция индекса состоит в том, чтобы делать некоторые суждения, не глядя на данные. Например, если в торговом центре есть ресторан, вы можете узнать об этом, взглянув на индекс. Для ресторана в Сычуани вам нужно только посмотреть в индексе вам не нужно запускать один за другим сычуаньские рестораны.
Итак, как же работают индексы в Mysql?
Структура индекса Mysql часто представляет собой дерево B+.
Дерево B+ порядка m обладает следующими свойствами:
-
Узлы делятся на индексные узлы и узлы данных. Узел индекса эквивалентен внутреннему узлу B-дерева, а все узлы индекса образуют B-дерево, обладающее всеми характеристиками B-дерева. В индексном узле хранятся ключи и указатели, но не сохраняются никакие конкретные элементы. Узел данных эквивалентен внешнему узлу B-дерева.Внешний узел B-дерева пуст и используется в дереве B+ для хранения реального элемента данных, который содержит ключ и другую информацию об элементе, но указателя нет.
-
B-дерево, состоящее из всего узла индекса, используется только для того, чтобы найти, на каком внешнем узле находится элемент данных с определенным ключом. Ключ найден в узле индекса, и дело не в этом, продолжайте поиск узла данных, а затем считывайте элементы в узле данных, либо бинарный поиск, либо последовательное сканирование, чтобы найти настоящий элемент данных.
-
Порядок M используется только для управления степенью части узла индекса Что касается того, сколько элементов содержит каждый узел данных, это не имеет ничего общего с m.
-
Кроме того, существует связанный список, который соединяет все узлы данных и может быть доступен последовательно.
Это определение относительно абстрактно, давайте рассмотрим конкретный пример.
Из рисунка видно, что это дерево B+ 3-го порядка, а внешний узел данных содержит до 5 элементов. Если вставленные данные находятся в узле данных, если разделение и слияние не были вызваны, B-дерево, состоящее из узлов индекса, не изменится.
Если элемент 76 вставить во внешний узел с 71 по 75, это вызовет разделение, 71, 72, 73 станут узлом данных, 74, 75, 76 станут узлом данных, а для индексного узла это эквивалентно вставке ключа 74 процесса.
Если удалить 43 из внешних узлов с 41 по 43, это приведет к слиянию, и в один узел будут объединены 41, 42, 61, 62 и 63. Для индексного узла это эквивалентно процессу удаления ключ 60.
При поиске, поскольку высота слоя дерева B+ очень мала, его можно найти относительно быстро.Например, если мы хотим найти значение 62, если корневой узел обнаруживает, что оно больше 40, доступ к правому , если он меньше 70, доступ к левому, если он больше 60, доступ к правому, а в листе Второй узел нашел 62 и успешно его локализовал.
В InnoDB Mysql существует два типа индексов дерева B+, один из которых называется кластерным индексом, а другой называется вторичным индексом.
Листовым узлом кластерного индекса является узел данных, часто первичный ключ используется в качестве кластерного индекса, а конечный узел вторичного индекса хранит поле KEY плюс значение первичного ключа. Следовательно, чтобы получить доступ к данным через вторичный индекс, необходимо дважды получить доступ к индексу.
Существует также форма индекса, называемая составным индексом или составным индексом, который можно индексировать по нескольким столбцам.
Сопоставление такого индекса заключается в том, что первый столбец сравнивается первым, и если первый столбец равен, сравнивается второй столбец и так далее.
В-четвертых, преимущества и недостатки индексов баз данных.
Наиболее очевидным преимуществом индексов базы данных является сокращение операций ввода-вывода. Ниже анализируются несколько сценариев.
Для поля с условием = вы можете напрямую найти дерево B+, с небольшим количеством операций чтения с жесткого диска (эквивалентно высоте слоя дерева B+) вы можете добраться до конечного узла, а затем напрямую найти местоположение данных.
Для полей диапазона, поскольку дерево B+ полностью отсортировано, диапазон можно быстро найти по дереву.
Аналогично, для orderby/group by/distinct/max/min, поскольку дерево B+ отсортировано, результат может быть получен быстро.
Существует также распространенный сценарий, называемый индексом, покрывающим данные. Например, в качестве полей условий используются два поля A и B. Часто появляются A=a И B=b.При одновременном выборе C и D часто строится совместный индекс (A, B), который является вторичным Таким образом, при поиске по B+-дереву вторичного индекса можно быстро найти соответствующие листовые узлы и записи, но часть записей является идентификатором кластеризованного индекса, поэтому необходимо искать B+-дерево вторичного индекса. кластеризованный индекс один раз, чтобы найти записи в реальной таблице, а затем в записи считываются C и D. Если совместный индекс установлен как (A, B, C, D), то все данные доступны в дереве B+ вторичного индекса, который можно вернуть напрямую, сократив процесс поиска по дереву один раз.
Конечно, у индекса должна быть цена, бесплатных обедов в мире не бывает.
Преимущества индексов в основном заключаются в повышении эффективности чтения, в то время как стоимость индексов заключается в снижении эффективности записи.
Вставка и изменение данных может означать изменение индекса.
При вставке кластерный индекс часто строится по первичному ключу, поэтому первичный ключ лучше всего использовать самовозрастающий, чтобы вставляемые данные всегда были в конце, и он был последовательным, и эффективность была относительно высокой. Не используйте UUID для первичного ключа, потому что порядок является случайным, что приведет к случайным записям, а эффективность относительно низкая. Не используйте первичный ключ, связанный с бизнесом, потому что связанный с бизнесом означает, что он будет обновляться, и его будут удалять и повторно вставлять, а эффективность будет относительно низкой.
Из приведенного выше введения в принцип дерева B+ мы можем видеть, что стоимость разделения дерева B+ относительно велика, и разделение часто происходит в процессе вставки.
Для модификации данных это в основном эквивалентно удалению, а затем вставке, и стоимость относительно высока.
Для вторичных индексов некоторых строковых столбцов часто возникают случайные записи и чтения, а нагрузка на ввод-вывод относительно велика.
5. Интерпретировать принципы базы данных военных уставов
Зная принципы работы этих двух индексов, мы можем объяснить, почему так выглядят многие так называемые БД воинских уставов. Давайте объясним их один за другим.
Когда следует использовать составной индекс вместо отдельного индекса?
Предполагая условное утверждение A=a AND B=b, если A и B являются двумя отдельными индексами, только один индекс будет работать при условии AND, а B будет оцениваться один за другим, и если объединенный индекс (A, B) используется , просто проходите по дереву, что значительно повышает эффективность. Однако для A=a ИЛИ B=b комбинированный индекс не работает из-за отношения да-или, поэтому можно использовать отдельный индекс.В это время два индекса могут работать одновременно.
Почему индекс должен быть дискриминационным? Должен ли дискриминационный индекс располагаться перед комбинированным индексом?
Если нет различия, такого как пол, это эквивалентно разделению всей большой таблицы на две части, и поиск данных по-прежнему требует обхода половины таблицы, чтобы найти их, что делает индекс бессмысленным.
Нужен ли мне по-прежнему одностолбцовый индекс, если у меня составной индекс?
Если комбинированный индекс равен (A, B), то этот комбинированный индекс можно использовать для условия A=a, поскольку комбинированный индекс сортируется по первому столбцу, поэтому нет необходимости создавать отдельный индекс для A, но Он не используется для B=b, потому что второй столбец сравнивается только тогда, когда первый столбец совпадает, поэтому второй столбец такой же и может быть распределен по разным узлам, и нет возможности быстро его найти.
Чем больше индексов, тем лучше?
Конечно нет, добавляйте индексы только там, где это необходимо.Индексы не только снизят эффективность вставки и модификации, но и при запросе, есть оптимизатор запросов.Слишком много индексов запутает оптимизатор, и может не быть возможности найти правильный путь запроса, таким образом выбирая медленный индекс.
Зачем использовать автоинкрементный первичный ключ
Поскольку строковые первичные ключи и случайные первичные ключи вызывают случайную вставку данных, эффективность относительно низкая, и первичные ключи следует обновлять реже, чтобы избежать B+-деревьев и частого слияния и разделения.
Почему старайтесь не использовать NULL
NULL труднее иметь дело с деревом B +, часто требующим специальной логики обработки, но снижающим эффективность.
Почему бы не индексировать часто обновляемые поля
Обновление поля означает, что соответствующий индекс также должен быть обновлен.Обновление часто означает удаление, а затем вставку.Индекс изначально представляет собой определенную структуру данных, сформированную на этапе записи заранее, что делает его более эффективным на этапе чтения, но если поле пишется больше и читается меньше, индексация не рекомендуется.
Почему бы не использовать функции в условиях запроса
Например, для условия ID+1=10 индекс генерируется при предварительной записи.Операция ID+1 находится в стадии запроса, и индекс неэффективен.Нет возможности сделать расчет для всех сначала индексы, а затем Для сравнения стоимость слишком высока, поэтому следует использовать ID=10-1.
Почему бы не использовать отрицательные условия запроса, такие как НЕ
Вы можете представить, что для дерева B+ следующий узел равен 40. Если ваше условие равно 20, идите налево, чтобы проверить, ваше условие равно 50, затем идите направо, чтобы проверить, но ваше условие не равен 66, индекс Что делать? Только когда вы пройдете его снова, чтобы узнать.
Почему нечеткие запросы не начинаются с подстановочных знаков
Для дерева B+, если корнем является определение символа, если подстановочный знак находится сзади, например, abc%, он должен искать слева, например, efg%, затем он должен искать справа, если подстановочный знак находится в конце. передний %abc, я не знаю, куда идти С одной стороны, давайте просканируем их все.
Почему OR следует заменить на IN или использовать Union
Оптимизация условий запроса ИЛИ часто затрудняет поиск наилучшего пути, особенно когда условий ИЛИ много.Для одного и того же поля лучше использовать IN.БД сортирует условия в IN и объединяет их.Обрабатывается двоичным кодом метод поиска. Для разных полей с помощью Union можно использовать индекс для каждого подзапроса.
Почему тип данных должен быть как можно меньше, а вместо символьных типов часто используются целые числа, а для длинных символьных типов можно использовать префиксные индексы?
Поскольку база данных хранится в страницах, размер каждой страницы одинаков, если тип данных больше, количество страниц будет больше, данные на каждой странице будут меньше, а высота дерева будет выше. , поэтому данные поиска. Количество считываемых операций ввода-вывода будет относительно большим, а узлы будут легко разделены во время вставки, и эффективность будет снижена. В основном это касается использования целых чисел вместо символов, поскольку целые числа более эффективны для индексации, например IP-адресов. Если есть длинные типы символов, которые необходимо запрашивать с использованием индекса, чтобы не делать индекс слишком большим, вы можете рассмотреть возможность индексации префикса поля, а не всего поля.
Шесть, методология оптимизации запросов
Чтобы найти операторы SQL, которые необходимо оптимизировать, сначала соберите проблемные операторы SQL.
В базе данных MySQL предусмотрена функция журнала медленного SQL, с помощью параметра slow_query_log можно получить список котировок SQL, время выполнения которых превышает определенный порог.
Операторы SQL, не использующие индексы, можно включить с помощью параметра long_queries_not_using_indexes.
min_examined_row_limit, операторы SQL с записями сканирования, превышающими это значение, будут записаны в журнал медленного SQL.
После обнаружения проблемного оператора следующим шагом будет использование объяснения SQL для получения плана выполнения SQL.Независимо от того, сканируются ли записи через индекс, эффективность выполнения может быть оптимизирована путем создания индекса. Не слишком ли много записей сканирования. Удерживается ли блокировка слишком долго и есть ли конфликт блокировок. Увеличено ли количество возвращаемых записей.
Затем вы можете настроить оптимизацию. Для полей, участвующих в условиях фильтрации, которые не охватываются индексом, создайте индекс для поля с большей дискриминацией.Если задействовано несколько полей, попробуйте создать совместный индекс.
Количество отсканированных записей очень велико, количество возвращенных записей мало, а различение плохое.Повторно оцените поля, участвующие в операторе SQL, и выберите несколько полей с высокой степенью различения для создания индекса.
Количество отсканированных записей очень велико, и количество возвращенных записей также очень велико. Условия фильтрации не являются строгими. Увеличьте условия фильтрации SQL.
schema_redundant_indexes, чтобы узнать, какие избыточные индексы доступны.
Если несколько индексов включают поля в одном и том же порядке, вы можете сформировать объединенный индекс schema_unused_indexes, чтобы увидеть, какие индексы никогда не используются.
Семь, принцип разделения чтения и письма
Базы данных имеют тенденцию меньше писать и больше читать, поэтому первым шагом в оптимизации производительности является разделение операций чтения и записи.
Репликация master-slave реализуется на основе журнала сервисного уровня на главном узле, а поток ввода-вывода на подчиненном узле читает журнал и записывает его локально. Другой поток повторно выполняется на подчиненном узле после чтения из локального журнала.
На рисунке показана блок-схема асинхронной репликации ведущий-ведомый. После того, как главный экземпляр записывает в механизм, он возвращает успех, а затем отправляет событие подчиненному экземпляру и выполняет его на подчиненном экземпляре. Этот способ синхронизации более быстрый, но при зависании мастера, если нет репликации, может быть проблема с потерей данных.
Синхронная репликация базы данных также отличается. Она возвращается к клиенту, когда подчиненный узел помещается на диск. Конечно, это снижает производительность. Команда базы данных NetEase улучшает производительность с помощью таких технологий, как групповая отправка и параллельная репликация.
При репликации master-slave стратегия разделения чтения-записи может быть установлена на уровне DAO базы данных, а некоторые могут сделать это с помощью промежуточного программного обеспечения базы данных.
На самом деле существует множество других применений журналов базы данных, например, использование канала (проект с открытым исходным кодом Alibaba: инкрементная подписка и потребление на основе бинлога базы данных mysql) для подписки на бинлог базы данных, который можно использовать для обновления кеша, и т.п.
Добро пожаловать в публичный аккаунт