Статьи

Бобр в действии: практическая оптимизация MySQL

Октябрь 1, 2018

Эта статья была рецензирована Верн Анчета и Дежи Акала . Спасибо всем рецензентам SitePoint за то, что сделали контент SitePoint как можно лучше!

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

Огненный показатель производительности, сродни измерения скорости в автомобилях

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

MySQL позволяет сохранять все запросы в журнал.

Откройте консоль MySQL: 

mysql --user=USERNAME --password=PASSWORD

Это включает журнал: 

 set global general_log = "on";

Это сохраняет его в таблицу: 

 set global log_output = "table";

Чтобы просмотреть журналы, которые вы сохранили: 

 select * from mysql.general_log;

Чтобы сжать ваши логи: 

 truncate mysql.general_log;

Однако просмотр журналов в консоли MySQL не так удобен, как мог бы быть, потому что:

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

Поскольку я часто работаю с журналами, я разработал инструмент под названием « Beaver MySQL logger », чтобы оптимизировать и упростить их анализ для пользователя. С помощью этого инструмента вы можете:

  • Включить / выключить регистрацию
  • Просматривайте логи в приятном интерфейсе
  • Поиск журналов
  • «Объяснить» запросы
  • Очистить логи

Вы можете увидеть скриншот программы ниже:

Скриншот

Пример проекта по оптимизации клиента

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

сетка

После того, как я включил ведение журнала, я увидел, что около 680 SQL-запросов выполнялось на загрузку страницы. Я обнаружил, что каждый запрос загружался отдельно, приводя к 100 событиям на страницу, и каждое событие дополнительно загружалось так:

Структура

  • пользователь 
 select * from user where id = N
  • Роль 
 select * from role where id = N
  • Страна 
 select * from country where id = N
  • город 
 select * from city where id = N
  • категория 
 select * from category where id = N

Почему?

Почему это случилось? В проекте использовалась инфраструктура Yii, что означало, что код для доступа к данным использовал следующий синтаксис: 

 $event->user->name;
$event->country->title;
$event->city->title;
$event->category->name;
$event->role->name;

Где $event

Это означает, что таблица должна была выполнить 500 дополнительных запросов, что снизило эффективность и повлияло на общую производительность.

Как это исправить

Объединение необходимых значений в один запрос уменьшит объем необходимой нагрузки на сервер: 

 select
    *
from
    event
    left join user on user.id = event.user_id
    left join country on country.id = event.country_id
    left join city on city.id = event.city_id
    left join category on category.id = event.categroy_id
    left join role on role.id = user.role_id

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

 $criteria = new CDbCriteria([
    'width' => ['user.role', 'country.city', 'category']
]);

Ура! Теперь будет выполняться на 500 запросов меньше на запрос!

После изучения журналов я также обнаружил, что для каждого пункта меню в приложении был сделан SQL-запрос для проверки каждого уровня доступа следующим образом: 

 select level from acl where role_id = N and page_id = N

Эта проверка уровня доступа генерирует около 150 добавленных запросов на загрузку страницы! Самый простой способ исправить это — использовать кеш для виджета меню.

Вывод

Перед проектом страница загружалась 1100 мс. После оптимизации 130мс.

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

Более полное руководство по оптимизации MySQL в целом можно найти в следующих трех статьях:

Каковы ваши успехи в оптимизации? Вы можете поделиться чем-нибудь? Дайте нам знать!

72
Share: