Краткое содержание

MySQL — это система управления базами данных взаимосвязей, разработанная на языках программирования C и C, созданная компанией MySQL AB, основанной Дэвидом Аксмарком, Алланом Ларссоном и Майклом Видениусом. Самая новая версия — MySQL 9.0.

MySQL — это проект с открытым исходным кодом и лицензией GNU.

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

Архитектура

• Схема архитектуры MySQL: источник

Клиентский уровень

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

Серверный уровень

• Следующий уровень MySQL отвечает за обработку операторов запроса и предоставление множества утилит.
• Основные особенности:
  • Парсер: когда клиент запрашивает оператор запроса, сервер анализирует его, чтобы создать дерево запроса, переписывает запрос и определяет порядок и индексы, которые будут использоваться для выполнения оператора запроса.
  • Оптимизатор: оптимизирует оператор запроса перед выполнением. Оптимизатору все равно, какой механизм используется, но механизм хранения может влиять на оптимизацию конкретного запроса.
  • Кэш запросов. В кеше запросов хранятся результаты оператора запроса. Если клиент снова запрашивает тот же запрос, сервер возвращает кэшированный результат, минуя этапы анализа и оптимизации. Эта функция устарела в версии 8.0 или выше из-за проблемы с узким местом.
  • Услуги и утилиты:
    • Резервное копирование и восстановление
    • Безопасность: система пользователей и разрешений поставщика
    • Репликация: это процесс копирования и синхронизации данных с главного сервера на множество дочерних серверов.
    • Кластер
    • Разделение: разделите таблицу на множество частей, используя определенную логику.
    • Workbench: это визуальный инструмент, предназначенный для взаимодействия и использования MySQL.

Уровень хранения

• Это уровень ответственности за то, как мы храним данные в базе данных.
• По умолчанию MySQL использует механизм хранения InnoDB.
• MySQL поддерживает множество различных механизмов хранения:
  • InnoDB (по умолчанию)
  • МойИСАМ
  • ПАМЯТЬ
  • CSV
  • АРХИВ
  • ЧЕРНАЯ ДЫРА
  • ОБЪЕДИНИТЬ
  • ФЕДЕРАЦИЯ

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

Оптимизировать оператор SQL

Объясните заявление

• Прогнозирование запроса без выполнения

explain sql_statememnt

• Объяснение после выполнения запроса

explain analyze sql_statement

Техника индексирования

• Повышает производительность при запросах и поиске.
• При создании индекса для нескольких столбцов первый столбец очень важен. Он определяет производительность оператора запроса. Например, если вы создаете индекс по (имя, день рождения), запрос с использованием столбца «имя» будет использовать этот индекс, а запрос с использованием столбца «день рождения» — нет.
• Синтаксис создания нового индекса:

create index idx_birthday on customers(birthday);

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

alter table customers alter index idx_birthday invisible;

Раздел

• Если размер базы данных превышает 2 ГБ (или более 10 миллионов записей), для этой базы данных следует использовать секционирование.
• При использовании секционирования производительность улучшится.
• План разделов должен основываться на столбце, который часто используется в предложениях WHERE.

Оптимизировать параметры базы данных

Попадание в буферный кэш

• Это процент данных, которые система загружает из буферного кэша, а не из физического хранилища.
• Если процент ниже 90%, то его необходимо оптимизировать.
• Зависит от 2 параметров:
  • Innodb_buffer_pool_read_requests: общее количество запросов, отправленных в буферный пул. Команда: показать глобальный статус, например '%Innodb_buffer_pool_read_requests%
  • Innodb_buffer_pool_reads: все запросы, которые не найдены в буферном пуле в памяти, должны быть прочитаны с диска. Команда: показать глобальный статус, например '%Innodb_buffer_pool_reads%
• Формула расчета:

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

Попадание в кэш таблицы

• MySQL кэширует таблицу для быстрого запроса в памяти.
• Если процент ниже 80%, то его необходимо оптимизировать.
• Зависит от 2 параметров:
  • Open_tables: общее количество таблиц открыто в кеше. Команда: показать глобальный статус, например «Open_tables».
  • Opened_tables: Всего открытых таблиц. Команда: показать глобальный статус, например «Открытые_таблицы».
• Формула расчета: Open_tables/Opened_tables

Попадание в кэш определения таблицы

• При выполнении запроса MySQL запрашивает информацию о таблице, такую ​​как ее имя, количество столбцов и количество строк. Эта информация называется определением таблицы.
• Если процент ниже 80%, то его необходимо оптимизировать.
• Зависит от 2 параметров:
  • Open_table_definitions: Общее количество таблиц определений находится в кеше. Команда: показать глобальный статус, например «Open_table_definitions».
  • Opened_tables: запрошены общие таблицы определений. Команда: показать глобальный статус, например «Открытые_таблицы».
• Формула расчета: Open_table_definitions/Opened_table_definitions

Временная таблица в памяти

• Многие операторы SQL, такие как Order, Group By,... должны использовать временную таблицу.
• Если процент ниже 80%, то его необходимо оптимизировать.
• Зависит от 2 параметров:
  • Created_tmp_disk_tables: общее количество временных таблиц создается на диске. Команда: показать глобальный статус, например «%Created_tmp_disk_tables%».
  • Created_tmp_tables: создано общее количество временных таблиц. Команда: показать глобальный статус, например «%Created_tmp_tables%».
• Формула расчета: (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

Заключение

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

Спасибо, что читаете, и увидимся в моем следующем блоге.

Справочный документ

• Документация MySQL
• Курс MySQL — Чан Куок Хай
• Википедия — MySQL