предисловие
Я не буду повторять пункты знаний, упомянутые выше.Друзья, которые не видели его, могут зайти на мою домашнюю страницу, чтобы проверить это (предисловие идет с бесплатным авиабилетом). Эта статья будет кратким изложением всей темы и одним из наиболее часто задаваемых вопросов интервьюеров — «Что вы думаете об оптимизации производительности MySQL?»
Многим друзьям, которые ходят на собеседования, в принципе следует задавать этот вопрос, но может быть немногие могут ответить на него в совершенстве. средний и продвинутый уровень), я надеюсь, что после этой сегодняшней статьи каждый сможет интегрировать и систематизировать разрозненные знания в своем уме. Я надеюсь, что вы сможете вернуться, чтобы сообщить мне хорошие новости (конечно, также можно плевать горечь), и я с нетерпением жду дальнейшего прогресса вместе с вами.
Возвращаясь к теме, о точках знаний по оптимизации производительности MySQL на этот раз я разделю ее на две статьи для вывода.Я напишу оптимизацию производительности операторов SQL в отдельной главе.Оптимизация операторов относится к производительности на практике.Наивысшее -уровень оптимизации точки оптимизации, я надеюсь, что все это хорошо переварить.
Эту тему MySQL я готовил много лет, и я думал о том, как опубликовать эту часть статьи сразу после китайского Нового года, сначала опубликовав карту мозга, а затем опубликовав специальные точки знаний. в соответствии с направлением карты мозга.В таком виде она должна быть после этого.Ведь у меня есть четкое представление о написании статьи, и у каждого есть четкое представление при чтении статьи.Обзор Очки знаний могут также основываться на картах мозга.
Я начал писать пост в прошлом году, раньше делал больше заметок в облачной заметке. Я думаю, что делать заметки и писать резюме очень полезно для самосовершенствования, а делиться ими — это также надежда на то, что каждый может извлечь из этого новые знания, и в то же время может помочь мне вместе совершенствоваться, дайте мне несколько предложений, позвольте мне дать вам Делясь резюме, я также могу проверить и заполнить пробелы (еще раз спасибо моим друзьям, которые поддерживали меня все время ♪(･ω･)ﾉ)
Я еще не решил, что написать в следующей теме.Если у вас есть идеи, вы можете оставить мне сообщение на официальном аккаунте.
Старые правила, садись в самолет первым:
- Связано с MySQL (1) - как выполняется оператор запроса
- Связано с MySQL (2) - как выполняется оператор обновления
- Связано с MySQL (дополнительная статья) - логическая структура хранения innodb;
- Связано с MySQL (3) - вывод модели данных индекса и подробное введение в B + Tree;
- Связано с MySQL (четыре) - индекс ключевых точек оптимизации производительности
- Связано с MySQL (5) — подробное введение в функции транзакций и уровни изоляции
- Связанный с MySQL (6) — реализация уровня изоляции транзакций (MVCC)
- Связано с MySQL (7) - введение и использование блокировки innodb
- Связано с MySQL (8) — углубленный анализ блокировок на уровне строки innodb
- Связанный с MySQL (9) - возникновение и предотвращение взаимоблокировки
Упомянутая выше карта мозга выглядит следующим образом.
текст
Идеи оптимизации
позволь мне болтать
Как архитектор или разработчик, что вы думаете об оптимизации производительности базы данных?
Или, если быть более конкретным, если вы столкнетесь с таким вопросом во время интервью: из каких измерений вы бы оптимизировали базу данных, как бы вы ответили?
Я считаю, что в предыдущих главах все постепенно установили систему знаний о базе данных и правильное представление о настройке.
Когда мы говорим о настройке производительности, в большинстве случаев мы хотим добиться ускорения наших запросов. Действие запроса состоит из множества ссылок, и каждая ссылка требует времени В первой главе уже был проанализирован процесс выполнения оператора SQL.
Если мы хотим сократить время, затрачиваемое на запрос, мы должны начать с каждой ссылки (сначала картинка, которая всем более знакома).
Соединение — оптимизация конфигурации
Первая ссылка - это то, что клиент подключается к серверу, какие проблемы с производительностью могут возникнуть в этом подключении?
Возможно, на стороне сервера недостаточно подключений, поэтому приложение не может получить подключение. Например, сообщая оMysql: error 1040: Too many connectionsошибка.
Мы можем решить проблему недостаточного количества соединений с двух сторон:
- Со стороны сервера мы можем увеличить количество доступных подключений на стороне сервера.
Если к базе одновременно обращаются несколько приложений или много запросов, а количество подключений недостаточно, мы можем: (1) Измените параметры конфигурации, чтобы увеличить количество доступных соединений, и измените размер max_connections:
show variables like 'max_connections';-- Изменить максимальное количество подключений при наличии нескольких подключений приложений
(2) Или, или вовремя отпустите неактивное соединение. Тайм-аут по умолчанию для интерактивных и неинтерактивных клиентов составляет 28800 секунд, 8 часов, мы можем уменьшить это значение.
show global variables like 'wait_timeout';-- Своевременно освобождайте неактивные соединения, будьте осторожны, чтобы не освободить соединения, которые все еще используются пулом соединений.
- Со стороны клиента количество соединений, получаемых со стороны сервера, можно уменьшить, если мы хотим не создавать новое соединение каждый раз при выполнении SQL, что нам делать?
В настоящее время мы можем ввести пул соединений, чтобы реализовать повторное использование соединений.
На каком уровне мы можем использовать пул соединений? уровня ORM (MyBatis поставляется с пулом соединений) или используйте специальный инструмент пула соединений (Ali's Druid, пул соединений по умолчанию Hikari версии Spring Boot 2.x, старые DBCP и C3P0).
Когда клиент переходит на получение соединений из пула соединений, как должен быть установлен размер пула соединений? У вас может возникнуть непонимание, что чем больше максимальное количество подключений в пуле подключений, тем лучше, чтобы в случае высокого параллелизма клиент мог получить больше подключений без очереди.
На практике это не так. Пул соединений не настолько велик, насколько это возможно. Пока поддерживается определенное количество пулов соединений, другие клиенты могут стоять в очереди для получения соединений. Иногда чем больше пул соединений, тем ниже эффективность.
- Максимальный размер пула соединений по умолчанию для Druid равен 8. Максимальный размер пула соединений Hikari по умолчанию равен 10. Почему значения по умолчанию такие маленькие?
В документации Hikari на github приведена предлагаемая формула для установки размера пула соединений для базы данных PostgreSQL:Github.com/brett whert dr ...
Его рекомендация состоит в том, чтобы умножить количество ядер машины на 2 плюс 1. То есть для 4-ядерной машины достаточно, чтобы пул соединений поддерживал 9 соединений. Эта формула в определенной степени применима и к другим базам данных. Существует также случай уменьшения размера пула соединений для улучшения параллелизма и пропускной способности.
- Почему уменьшение количества подключений в некоторых случаях увеличивает пропускную способность? Почему рекомендуемый размер пула соединений связан с количеством ядер ЦП?
Для каждого соединения сервер должен создать поток для его обработки. Чем больше подключений, тем больше потоков создает сервер.
Как ЦП одновременно выполняет задачи, которые намного превышают размер его ядра? временной отрезок. переключение контекста.
Количество ядер ЦП ограничено, и частое переключение контекста приведет к относительно большому снижению производительности.
Здесь мы говорили об оптимизации базы данных с уровня конфигурации базы данных. Будь то конфигурация самой базы данных или конфигурация операционной системы, в которой установлена служба базы данных, конечной целью оптимизации конфигурации является более эффективное использование производительности самого оборудования, включая ЦП, память, диск и сеть. .
В разных аппаратных средах параметры операционной системы и MySQL настраиваются по-разному, стандартной конфигурации нет. В предыдущих главах мы также затронули множество параметров конфигурации MySQL и InnoDB, включая настройку различных переключателей и значений.Большинство параметров имеют значения по умолчанию, такие как размер буфера по умолчанию, размер страницы по умолчанию и количество InnoDB. параллельные потоки и т. д.
Эти конфигурации по умолчанию могут удовлетворить потребности большинства ситуаций, если нет особых потребностей, а затем изменить их, когда значение параметров станет ясным. Работа по изменению конфигурации обычно выполняется профессиональным администратором баз данных.
Что касается выбора самого оборудования, такого как использование твердотельных накопителей, построение дисковых массивов и выбор конкретных моделей процессоров, это не является предметом внимания наших разработчиков, поэтому мы не будем подробно их представлять.
Если вы хотите узнать значение некоторых конкретных параметров, на официальном сайте есть список системных параметров для справки:
- Кроме разумной настройки количества соединений на стороне сервера и размера пула соединений на стороне клиента, что еще можно сделать для уменьшения количества клиентов Как насчет количества соединений между клиентом и сервером базы данных?
Мы можем ввести кэширование.
Кэширование — оптимизация архитектуры
тайник
Когда количество одновременных приложений в системе приложений очень велико, отсутствие кеша вызовет две проблемы: с одной стороны, это создаст большую нагрузку на базу данных. С другой стороны, на уровне приложения также будет затронута скорость манипулирования данными.
Мы можем решить эту проблему с помощью стороннего сервиса кэширования, такого как Redis.
Какие еще меры по оптимизации мы можем предпринять на архитектурном уровне, чтобы уменьшить нагрузку на чтение и запись на одном сервере базы данных?
репликация master-slave
Если одна служба базы данных не может удовлетворить требованиям доступа, мы можем использовать кластерное решение базы данных.
Кластеризация неизбежно столкнется с проблемой, то есть с проблемой непротиворечивости данных между разными узлами. Если несколько узлов базы данных считываются и записываются одновременно, как обеспечить согласованность данных всех узлов?
На данный момент нам нужно использовать технологию репликации, реплицируемый узел называется ведущим, а реплицируемый узел называется подчиненным. Сам ведомый также может использоваться в качестве источника данных для других узлов, что называется каскадной репликацией.
Как реализована репликация master-slave? Операторы обновления регистрируют binlog, который является логическим журналом.
С этим двоичным журналом подчиненный сервер получит файл двоичного журнала главного сервера, затем проанализирует оператор SQL внутри и выполнит его на подчиненном сервере, чтобы сохранить согласованность данных главного и подчиненного.
Задействовано три потока: подключение к мастеру для получения бинлога, анализ бинлога и запись журнала ретрансляции Этот поток называется потоком ввода-вывода.
На главном узле есть поток дампа журнала, который используется для отправки двоичного журнала подчиненному.
Поток SQL подчиненной библиотеки используется для чтения журнала ретрансляции и записи данных в базу данных.
После схемы репликации master-slave мы записываем данные только на главный узел, а запросы на чтение можно использовать совместно с подчиненными узлами. Мы называем эту схему разделением чтения-записи.
Разделение чтения и записи может в определенной степени снизить нагрузку на сервер базы данных, но особое внимание следует уделить проблеме непротиворечивости данных ведущий-ведомый. Что, если мы пропишем в мастере и сразу перейдем к слейву делать запрос, но данные слейва в это время не синхронизировались?
Итак, исходя из принципа master-slave репликации, нам нужно выяснить, где репликация master-slave медленная?
один поток
На заре MySQL поток SQL подчиненного устройства был одним потоком. Мастер может поддерживать параллельное выполнение операторов SQL.Максимальное количество настроенных подключений равно максимальному количеству одновременных выполнений SQL.
Однако SQL ведомого может выполняться только в однопоточной очереди.Когда основная база данных имеет большой объем параллелизма, будут задержки в синхронизации данных.
Почему поток SQL в подчиненной библиотеке не может выполняться параллельно? Например, основная база данных выполняет несколько операторов SQL: сначала пользователь разместил комментарий, затем изменил его содержимое и, наконец, удалил комментарий. Порядок выполнения этих трех операторов в ведомой библиотеке не должен быть обратным.
insert into user_comments (10000009,'nice');
update user_comments set content ='very good' where id =10000009;
delete from user_comments where id =10000009;
Итак, как решить эту проблему? Как уменьшить задержку репликации master-slave?
Асинхронный и полностью синхронный
Прежде всего, нам нужно знать, что в процессе master-slave репликации MySQL по умолчанию реплицируется асинхронно. То есть для мастер-узла, когда бинлог пишется, транзакция завершается, и он возвращается клиенту. Для слейва это делается после получения бинлога, мастеру все равно успешно ли записываются данные слейва.
Хотя этот метод может гарантировать, что данные были успешно синхронизированы перед чтением, побочные эффекты, принесенные вами, должны быть в состоянии представить, что время выполнения транзакции будет больше, что приведет к снижению производительности мастер-узла.
Есть ли способ лучше? Можно ли уменьшить задержку записи ведомого устройства без существенного увеличения времени, необходимого ведущему устройству для возврата к клиенту?
полусинхронная репликация
Между асинхронной репликацией и полной синхронной репликацией существует также метод полусинхронной репликации.
Как выглядит полусинхронная репликация?
Основная библиотека не возвращается к клиенту сразу после выполнения транзакции, отправленной клиентом, а ждет, пока хотя бы одна подчиненная библиотека получит бинарный журнал и запишет его в журнал ретрансляции, прежде чем вернуться к клиенту. Мастер долго ждать не будет, но когда вернется к клиенту, данные будут успешно записаны, потому что ему остался только последний шаг: чтение журнала реле и запись в библиотеку слейва.
cd /usr/lib64/mysql/plugin/
Основная библиотека и подчиненная библиотека — это разные плагины, которые необходимо включить после установки:
--Выполнение основной библиотеки
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1;
show variables like '%semi_sync%';
-- выполнить из библиотеки
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
show global variables like '%semi%';
По сравнению с асинхронной репликацией, полусинхронная репликация повышает безопасность данных, а также вызывает определенную степень задержки: нужно дождаться записи ведомого устройства в журнал реле, и происходит дополнительный процесс сетевого взаимодействия. Поэтому полусинхронную репликацию лучше всего использовать в сетях с малой задержкой.
Это с точки зрения соединения между главной библиотекой и ведомой библиотекой для обеспечения записи ведомых данных.
Еще одна идея: если вы хотите уменьшить задержку синхронизации ведущий-ведомый и сократить время ожидания, вызванное выполнением SQL, есть ли способ разрешить параллельное выполнение нескольких операторов SQL в ведомой библиотеке, а не в очереди на выполнение?
Параллельная репликация с несколькими базами данных
Как добиться параллельной репликации? Представьте, если три оператора выполняются в трех базах данных и работают с соответствующими базами данных, не будет ли проблем с параллелизмом? Порядок исполнения также не требуется. Конечно, если вы работаете с тремя базами данных, потоки SQL подчиненных библиотек этих трех баз данных могут выполняться одновременно. Это параллельная репликация с несколькими базами данных, поддерживаемая в MySQL 5.6.
Но в большинстве случаев мы находимся в одной базе данных с несколькими таблицами.Как реализовать параллельную репликацию в базе данных? Другими словами, мы знаем, что сама БД поддерживает одновременную работу нескольких транзакций, почему эти транзакции могут без проблем выполняться параллельно на основной БД?
Поскольку они не мешают друг другу, например, эти транзакции работают с разными таблицами или работают с разными строками, отсутствует конкуренция за ресурсы и помехи данных. Транзакции, выполняемые параллельно в главной библиотеке, должны выполняться параллельно и в подчиненной библиотеке, верно? Например, на мастере есть три транзакции, которые работают с тремя таблицами одновременно, можно ли эти три транзакции выполнять параллельно на слейве?
Репликация GTID для асинхронной репликации
Следовательно, мы можем разделить транзакции, которые выполняются параллельно на главной библиотеке, на группу и пронумеровать их, а транзакции этой группы также могут выполняться параллельно на подчиненной библиотеке. Этот номер мы называем GTID (глобальные идентификаторы транзакций), этот метод репликации master-slave мы называем репликацией на основе GTID.
Если мы хотим использовать репликацию GTID, мы можем включить ее, изменив параметр конфигурации, который по умолчанию отключен:
show global variables like 'gtid_mode';
Будь то оптимизация соединения между ведущим и подчиненным или разрешение подчиненной библиотеке выполнять SQL параллельно, это должно решить проблему задержки репликации ведущий-ведомый на уровне базы данных.
Помимо уровня самой базы данных, на уровне приложения у нас также есть некоторые методы для уменьшения задержки синхронизации master-slave.
После того как мы выполнили репликацию master-slave, если данные, хранящиеся на одном главном узле или в одной таблице, слишком велики, например, в таблице с сотнями миллионов данных, производительность запросов для одной таблицы все равно будет снижаться. типизация и разделение узлов базы данных называется подбазой данных и подтаблицей.
Подбиблиотека и подтаблица
Вертикальное разделение библиотеки снижает нагрузку на параллелизм. Разделите таблицы по горизонтали, чтобы устранить узкие места в хранилище.
Практика вертикального разделения базы данных делит базу данных на разные базы данных в зависимости от бизнеса:
Практика горизонтальной подбазы данных и подтаблицы распределяет данные одной таблицы по нескольким базам данных в соответствии с определенными правилами.
С помощью master-slave или подбазы данных и подтаблицы давление доступа и давление хранения для одного узла базы данных может быть уменьшено, и цель повышения производительности базы данных может быть достигнута, но что, если главный узел зависнет?
Таким образом, высокая доступность также является основой для высокой производительности.
Решение высокой доступности
Dev.MySQL.com/doc/MySQL — также…
репликация master-slave
Традиционное решение HAProxy + keepalived основано на репликации master-slave.
NDB Cluster
MySQL Cluster на основе механизма хранения NDB Cluster.
Galera
Кластерное решение для синхронной репликации с несколькими мастерами.
MHA/MMM
Специальности.Meituan.com/2017/06/29/…
MMM (диспетчер репликации Master-Master для MySQL), архитектура высокой доступности с несколькими мастерами, была разработана японцем, и такие компании, как Meituan, также много использовали MMM в первые дни.
MHA (доступен MySQL Master High). И BM, и MHA предоставляют виртуальный IP-адрес для внешнего мира и контролируют главный узел и подчиненный узел.Когда главный узел выходит из строя, подчиненный узел должен быть повышен до главного узла, а данные, отсутствующие на подчиненном узле, сравниваются с мастер-узел дополняется Направьте VIP на новый мастер-узел.
MGR
Dev.MySQL.com/doc/Furious/… Dev.MySQL.com/doc/Furious/…
InnoDB Cluster, запущенный MySQL 5.7.17, также называемый MySQL Group Replicatioin (MGR), этот пакет включает оболочку mysql и mysql-route.
Проблема, которая должна быть решена в решении с высокой доступностью, заключается в том, как сделать подчиненный узел с последними данными ведущим, когда главный узел выходит из строя. Если вы запускаете несколько мастеров одновременно, вы должны решить проблему репликации данных между мастерами и маршрутизации соединений для клиентов.
Разные решения имеют разные сложности реализации и разные затраты на управление эксплуатацией и обслуживанием.
Выше приведена оптимизация на уровне архитектуры.Вы можете использовать кеш, master-slave, подбазу данных и подтаблицу.
Третья часть: Синтаксический анализатор, лексический и грамматический анализ в основном обеспечивают правильность утверждения, и нет проблем, если утверждение правильное. Север должен сам с этим справиться, скип.
Шаг 4: Оптимизатор Этот раздел будет рассмотрен в новой главе, посвященной главным образом аспектам оптимизации производительности операторов SQL.
Резюме: Оптимизация системы
Помимо кода, операторов SQL, определений таблиц, схем и оптимизации конфигурации нельзя игнорировать оптимизации бизнес-уровня. Приведу несколько примеров:
1) Почему в Double Eleven определенного года вы выполняете перезарядку Yu'e Bao и бонусную активность для баланса (перезарядите 300, чтобы получить 50)?
Поскольку при оплате балансом или Yu'Ebao записывается локальная или внутренняя база данных, а при оплате банковской картой необходимо вызывать интерфейс, а работать с внутренней базой данных определенно быстрее.
2) Почему в прошлогоднем Double Eleven было запрещено проверять счета, кроме как сегодня, в ранние утренние часы?
Это мера понижения, чтобы обеспечить наиболее основной бизнес в настоящее время.
3) Для Double 11 в последние годы, почему цена в день Double 11 уже доступна более чем за неделю? Предпродажная диверсия.
На уровне приложений также существует множество других решений, которые можно оптимизировать для максимально возможного снижения нагрузки на базу данных, например, ограничение тока или введение пикового сглаживания MQ и т. д.
Зачем использовать MySQL? Некоторые компании могут обрабатывать миллионы уровней параллелизма, в то время как другие не могут справиться с сотнями. Ключ в том, как это использовать. Поэтому медленное использование БД не означает, что сама БД медленная, и иногда приходится оптимизировать верхний слой.
позже
Тема MySQL официально завершена на этой главе. По сути, она соответствует направлению карты мозга, поэтому вы можете сравнить MySQL с картой мозга и моим сообщением в блоге. По сути, участвуют вопросы интервью, и написание темы действительно это. это мозгоемкая, энергозатратная и трудоемкая вещь, мне все еще нужно всеобщее внимание, и я люблю поддерживать ее, хахаха~
Если вы чувствуете, что есть еще что написать, пожалуйста, помогите обратить внимание на мой официальный аккаунт, и оставьте мне сообщение в фоновом режиме, чтобы надеяться, что я напишу что-нибудь о какой теме (если что-то не так с текущим обучением и продажей ,пожалуйста,помогите меня поправить,спасибо большое) ,если много людей,то сразу оформляйте~
Все таки, после периода ремонта, я сначала запущу карту мозга на паблик, а потом на основании карты мозга составлю конспект темы, чтобы не писать в тумане, а всем будет прочитайте мой пост в блоге в более направленном виде, еще раз Спасибо за вашу поддержку ~
By the way
Что-то не так? Вы можете оставить мне сообщение или приватный чат Есть урожай? Тогда поставь мне палец вверх~
Конечно, вы также можете зайти на «6 Xixuan» под моим официальным аккаунтом,
Ответьте «Узнать», чтобы получить копию[Видеоруководство для Java-инженеров и продвинутых архитекторов]~
Ответ на "интервью" вы можете получить:[Вопросы для интервью по Java, с которыми я тщательно разобрался]
Ответ на "карту мозга MySQL", вы можете получить[Очки знаний MySQL, прочесывающие карту мозга в высоком разрешении]
Поскольку я программист с профессиональным образованием, я делал это на php, Android и железе, но в итоге решил сосредоточиться на Java, поэтому, если у вас есть какие-либо вопросы, вы можете задать их и обсудить в публичном аккаунте (вы можно поговорить о технических эмоциях хахаха) см. Если вы приедете, я отвечу как можно скорее. Надеюсь учиться и прогрессировать вместе с вами. Статьи о серверной архитектуре, анализе основных знаний Java, карьере, резюме интервью и т. д. будет продолжать время от времени нажимать вывод.Приглашаем всех обратить внимание~~~