Обмен технологиями

[Вопросы на собеседовании] MySQL (Часть 4)

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1. Давайте подробно поговорим об этапах выполнения оператора MySQL.

Шаги для последовательного выполнения SQL на уровне сервера:

Запрос клиента -> Коннектор (проверка личности пользователя и предоставление разрешений) Кэш запросов (возврат напрямую, если кэш существует, выполнение последующих операций, если нет) Анализатор (выполнение лексического анализа и синтаксического анализа SQL) Оптимизатор (в основном выполнение метода оптимизации SQL для выбора оптимальный план выполнения) Исполнитель (при выполнении он сначала проверяет, есть ли у пользователя разрешение на выполнение, а затем использует интерфейс, предоставляемый этим движком) -> Перейти на уровень движка для получения возврата данных (если кеш запросов включен, он будет кэшировать результаты запроса)

2. Буферный пул

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

1. Основные понятия

  • определение: Буферный пул — это область памяти в механизме хранения InnoDB, используемая для кэширования страниц данных и индексных страниц на диске, чтобы уменьшить прямой доступ к диску.

  • эффект: повысить скорость доступа к данным и снизить затраты на дисковый ввод-вывод за счет механизма кэширования.

  • состав : Пул буферов состоит из кэшированных страниц данных (Page) и соответствующих блоков управления. Блок управления хранит информацию метаданных страницы кэша, такую ​​как табличное пространство, которому она принадлежит, номер страницы данных, адрес страницы кэша в буферном пуле и т. д.

2. Размер и конфигурация

  • размер по умолчанию: Размер буферного пула по умолчанию в MySQL обычно составляет 128 МБ (но обратите внимание, что разные версии MySQL или разные конфигурации могут привести к тому, что размер по умолчанию будет отличаться).

  • Параметры конфигурации:проходитьinnodb_buffer_pool_sizeПараметры позволяют настроить размер буферного пула. Обычно рекомендуется устанавливать его на уровне 60–80 % системной памяти.

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

3. Типы страниц данных и управление ими

  • тип

    : Страницы данных в буферном пуле можно разделить на три типа в зависимости от их статуса: Свободная страница, Чистая страница и Грязная страница.

    • Бесплатные страницы: страницы кэша, которые не используются.

    • Чистая страница: страница кэша, которая использовалась, но данные не были изменены.

    • Грязная страница: использованная страница кэша, данные которой были изменены, и ее данные не соответствуют данным на диске.

  • управлять

    : InnoDB управляет этими страницами кэша через три структуры связанных списков:

    • Свободный связанный список: управляет свободными страницами и записывает информацию блока управления свободными страницами кэша.

    • Связанный список LRU: управляет чистыми и грязными страницами, использует улучшенный алгоритм LRU и делится на молодые и старые области для оптимизации скорости попадания в кеш.

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

4. Рабочий механизм

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

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

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

5. Оптимизация и меры предосторожности

  • Установите размер соответствующим образом: Разумные настройки, основанные на условиях загрузки системной памяти и базы данных.innodb_buffer_pool_sizeпараметр.

  • Контролируйте и корректируйте: регулярно отслеживать показатели использования и производительности буферного пула и при необходимости вносить коррективы.

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

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

3. Процесс MySQL

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

1. Подключение и аутентификация

  1. Коннектор (Диспетчер соединений):

    • Когда клиент (например, приложение или инструмент командной строки) запрашивает соединение с сервером MySQL, соединитель MySQL отвечает за обработку этих запросов на соединение.

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

    • Если проверка прошла успешно, соединитель выделит поток (или сеанс) клиенту для последующих операций SQL.

2. Обработка запросов

  1. Кэш запросов (кэш запросов, примечание: этот модуль был удален в MySQL 8.0):

    • Для запросов SELECT MySQL сначала проверяет, существуют ли тот же запрос и его результаты в кеше запросов.

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

    • Однако, поскольку кэширование запросов может вызвать несогласованность данных (например, кэшированные данные могли быть изменены другими транзакциями), функция кэширования запросов была удалена в MySQL 8.0.

  2. Парсер:

    • Оператор SQL, отправленный клиентом, сначала отправляется анализатору.

    • Задача синтаксического анализатора — проанализировать оператор SQL, проверить правильность его синтаксиса и преобразовать его во внутреннюю структуру данных (например, дерево синтаксического анализа или дерево синтаксиса).

    • Если в операторе SQL есть синтаксическая ошибка, синтаксический анализатор вернет клиенту информацию об ошибке.

  3. Препроцессор:

    • В некоторых версиях MySQL или в некоторых конкретных сценариях может присутствовать этап препроцессора.

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

  4. Оптимизатор:

    • Оптимизатор отвечает за оценку различных планов выполнения операторов SQL и выбор оптимального плана выполнения.

    • Оптимизатор учитывает множество факторов, таких как доступные индексы, эффективность метода соединения, стоимость запроса и т. д.

    • Оптимизатор может значительно повысить производительность запросов за счет таких операций, как использование индексов, изменение порядка запросов или объединение запросов.

  5. Исполнитель:

    • Исполнитель выполняет фактические операции запроса на основе плана выполнения, созданного оптимизатором.

    • Исполнитель вызовет интерфейс механизма хранения (например, InnoDB), чтобы прочитать данные в таблице данных и выполнить такие операции, как сортировка, агрегирование и фильтрация.

    • Наконец, исполнитель возвращает результаты запроса клиенту.

3. Хранение и поиск данных

  • Механизм хранения:

    • MySQL поддерживает несколько механизмов хранения, и каждый механизм хранения имеет свои собственные методы хранения и извлечения данных.

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

    • Когда исполнитель вызывает интерфейс механизма хранения, механизм хранения отвечает за чтение данных с диска или запись данных на диск.

  • Буферный пул:

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

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

4. Обработка транзакций

  • Сделка:

    • MySQL поддерживает обработку транзакций, позволяя фиксировать или откатывать несколько операций целиком.

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

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

5. Резюме

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

4. Пул соединений MySQL.

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

1. Концепция

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

2. Зачем использовать пул соединений?

  1. Уменьшить потребление ресурсов : Создание и закрытие соединения с базой данных — относительно трудоемкий процесс, включающий трехэтапное рукопожатие и четырехэтапную волну TCP-соединения, а также процесс аутентификации базы данных. Благодаря объединению пулов существующих соединений можно повторно использовать, чтобы уменьшить эти накладные расходы.

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

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

3. Принцип работы пула соединений

  1. инициализация: При запуске программы пул соединений создаст определенное количество подключений к базе данных в соответствии с конфигурацией и поместит эти подключения в пул соединений для резервного копирования.

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

  3. Использовать соединение: программа использует запрошенное соединение для выполнения операций с базой данных.

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

  5. Закрыть пул соединений: Когда программа завершится, все соединения в пуле соединений будут закрыты, а занятые системные ресурсы будут освобождены.

4. Поставщик пула соединений

На рынке существует множество поставщиков пулов соединений MySQL, среди которых наиболее популярными являются:

  • ДБКП : Это реализация пула соединений с открытым исходным кодом в рамках проекта Apache, и это пул соединений, который поставляется с Tomcat. Он быстрее, чем другие пулы соединений, но может быть недостаточно стабильным.

  • C3P0 : это пул соединений JDBC с открытым исходным кодом, который реализует источник данных и привязку JNDI, а также поддерживает стандарт JDBC3 и расширение стандарта JDBC2. Скорость C3P0 относительно медленная, но очень стабильная.

  • Друид (Druid): Это пул соединений с открытым исходным кодом, предоставляемый Alibaba. Он сочетает в себе преимущества DBCP и C3P0 и обеспечивает мощные функции мониторинга и расширения. Druid в настоящее время является одним из наиболее часто используемых пулов соединений MySQL.

5. Конфигурация пула соединений

Настройка пула соединений обычно включает в себя следующие аспекты:

  • Максимальное количество подключений: максимальное количество соединений, которыми может управлять пул соединений.

  • Минимальное количество подключений: исходное количество соединений, созданных при запуске пула соединений.

  • Получить тайм-аут соединения: максимальное время ожидания при получении соединения из пула соединений.

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

  • Стратегия повторного использования соединений: перезагружать соединения в зависимости от времени их простоя и использования.

6. Связь между пулом соединений и пулом потоков

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

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

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

5. Каковы общие журналы MySQL? Какова их соответствующая функция?

Общие журналы в MySQL включают следующее:

  • Журнал ошибок : Записывайте информацию об ошибках при запуске, работе или остановке сервера MySQL, а также любую информацию о критических ошибках. Это помогает диагностировать проблему.

  • Журнал запросов (Общий журнал) : Записывайте каждый клиентский запрос и ответ, полученный сервером MySQL, включая действия входа пользователя в систему, выполненные операторы SQL и т. д. Обычно используется для аудита или отладки.

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

  • Двоичный журнал (сокращенно Binlog): записывает все операторы, изменяющие данные базы данных (за исключением таких операторов, как SELECT и SHOW), которые в основном используются для репликации и восстановления данных.

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

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

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

6. Как включить и настроить журнал медленных запросов?

Журнал медленных запросов можно открыть и настроить через файл конфигурации MySQL (например, my.cnf или my.ini) или настроить динамически с помощью команд SQL.

  • Метод файла конфигурации:

    • Добавьте или измените следующие параметры в файле конфигурации MySQL:

      [mysqld]  
      slow_query_log = 1  
      slow_query_log_file = /path/to/your/slow-query.log  
      long_query_time = 2

      в,

      slow_query_log

      Используется для включения журналов медленных запросов,

      slow_query_log_file

      Укажите путь к файлу журнала медленных запросов,

      long_query_time

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

    • После изменения файла конфигурации необходимо перезапустить службу MySQL.

  • Командный режим SQL:

    • Журнал медленных запросов можно включить динамически с помощью команд SQL, ноslow_query_log_fileиlong_query_timeВозможно, потребуется задать параметры через файл конфигурации, поскольку динамические настройки могут не поддерживаться или не работать.

    • Включить журнал медленных запросов:

      sql复制代码
      ​
      SET GLOBAL slow_query_log = 'ON';
    • Обратите внимание, что журнал медленных запросов, динамически открытый с помощью команд SQL, может стать недействительным после перезапуска системы, поэтому рекомендуется настроить его через файл конфигурации.

7. Сколько форматов двоичного журнала (Binlog) существует? Какая разница между ними?

Двоичные журналы (Binlog) имеют три формата:

  • ЗАЯВЛЕНИЕ : Репликация на основе операторов SQL (репликация на основе операторов, SBR). В этом формате MySQL будет записывать выполненные операторы SQL в binlog. Его преимущество состоит в том, что объем журнала невелик, но при этом могут возникнуть некоторые проблемы репликации, такие как функции, триггеры, хранимые процедуры и т. д., которые могут вызвать несогласованность данных главного-подчиненного устройства.

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

  • СМЕШАННЫЙ : Репликация на смешанной основе (MBR). MySQL автоматически выберет формат STATEMENT или ROW в зависимости от ситуации. Смешанный режим является режимом по умолчанию и предназначен для объединения лучшего из обоих миров.

8. Как Redo Log обеспечивает надежность транзакций?

Redo Log обеспечивает долговечность транзакций в механизме хранения InnoDB следующими способами:

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

  • Затем в подходящее время запишите журнал повторов из буфера журнала повторов в файл журнала повторов на диске. Этот процесс является асинхронным, но временем и частотой чистки диска можно управлять путем настройки параметров.

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

9. Кратко опишите, как просматривать и удалять файлы журналов в MySQL.

Просмотр файлов журналов

  • журнал ошибок: Обычно это можно сделать, просмотрев файл конфигурации MySQL.log_errorпараметр, чтобы указать путь к файлу, чтобы найти файл журнала ошибок и использовать текстовый редактор или инструмент командной строки, напримерtailcatи т. д.), чтобы просмотреть его содержимое.