Как профилировать SQL-запросы для повышения производительности

В Servebolt мы живем и дышимпроизводительностью .

Производительность базы данных не является исключением.

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

Время до первого байта (TTFB) — это способ измерения того, сколько времени требуется для получения первого байта данных после того, как пользователь делает запрос на веб-сайт.Это также ключевой показатель, используемый поисковыми системами при оценке сайтов. Когда запускается медленный запрос, это негативно влияет на TTFB. Чем дольше выполняется медленный запрос, тем выше будет TTFB, что приведет к снижению общей производительности веб-сайта и менее удовлетворительному пользовательскому опыту.

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

Понимание профилирования SQL-запросов

Когда вы разрабатываете веб-приложение и оно начинает работать в большем масштабе, SQL-запросы, которые когда-то выполнялись без сбоев, могут вызвать проблемы с производительностью. Вообще говоря, существует тенденция к увеличению количества запросов, выполняемых к растущему объему данных, с увеличением числа запросов в секунду. А когда страдает производительность, страдает и опыт ваших пользователей при взаимодействии с вашим сайтом, программным обеспечением или сервисом.

Профилирование запросов — это способ анализа запросов к базе данных, оценки их производительности и выявления потенциальных проблем.

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

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

Как профилировать SQL-запросы (7 методов)

Давайте начнем с анализа различных инструментов и методов, доступных для выявления медленных и неэффективных запросов, чтобы вы знали, на чем сосредоточить усилия по улучшению:

1 – КомандаEXPLAIN EXTENDED

Одним из инструментов, которые можно использовать для анализа ваших SQL-запросов, является командаEXPLAIN .

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

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

Когда вы запускаете команду EXPLAINдля запроса, она возвращает набор результатов с несколькими столбцами, в том числе:

• id: уникальный идентификатор запроса в плане выполнения
• select_type: Тип запроса, например ПРОСТОЙ или ПОДЗАПРОС.
• table: запрашиваемая таблица
• type: используемый тип соединения, например JOIN или INDEX.
• возможных_ключей: индексы, которые MariaDB или MySQL могли использовать для обработки запроса.
• key: индекс, который MariaDB или MySQL фактически использовали для обработки запроса.
• key_len: длина используемого ключа
• rows: количество строк, которые, по оценкам MariaDB или MySQL, будут проверены для запроса.

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

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

Например, если в столбце типа указано «ВСЕ» вместо «индекс»,то запрос выполняет полное сканирование таблицы, что почти наверняка приведет к снижению производительности. Если ключевой столбец имеет значение NULL, то MySQL не использует никаких индексов, что также будет медленным. Если столбец строк имеет высокое значение, это означает, что проверяется много строк, что приводит к дальнейшему снижению производительности.

Мы предпочитаем использовать вариант EXPLAIN EXTENDED для предоставления дополнительной информации.

Примечание. Хотя это устарело в MySQL, оно все еще доступно в MariaDB.

Используя параметр EXTENDED, вы сможете увидеть полезную информацию, такую ​​как количество проверенных строк, количество возвращенных строк, информацию об используемом типе JOIN, порядке сканирования таблиц, используемых индексах и времени. запрос должен был быть выполнен.

Вот как выглядит использование команды EXPLAIN EXTENDED:

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

В этом примере команда EXPLAIN покажет список шагов, которые база данных предпримет для выполнения запроса, а также список ресурсов, которые она будет использовать.

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

Например, использование команды EXPLAIN EXTENDED может помочь определить необходимость добавления индексов, оптимизировать условия JOIN и ограничить общее количество строк, возвращаемых запросом.

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

SET SESSION query_cache_type=0;

После того, как вы внесли эти изменения в свой запрос, снова проверьте его производительность, чтобы определить, какое улучшение было достигнуто (если таковое имеется). Помните, что, как и в случае с любым профилированием и оптимизацией запроса, этот процесс является итеративным — ожидайте использования команды EXPLAIN EXTENDED, за которой следует тест производительности несколько раз.

2 – КомандаEXPLAIN ANALYZE

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

3 – Журнал медленных запросов

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

В Servebolt журнал медленных запросов регистрирует все запросы, выполнение которых занимает более 1 секунды. Это связано с тем, что большинство запросов должны выполняться за доли секунды. В контексте веб-приложения, такого как сайт под управлением WordPress, для загрузки одной страницы требуется от 10 до 100 запросов к базе данных, все из которых должны выполняться последовательно, прежде чем страница может быть скомпилирована в HTML и возвращена пользователю.

Текущая конфигурация Servebolt Cloud хранит журналы медленных запросов на глобальном сервере журналов. Если возникнет необходимость, вы можете просто связаться с нашей службой поддержки, и мы отфильтруем файл для соответствующих журналов и предоставим вам результат.

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

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – План визуального объяснения

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

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

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

Различные инструменты, такие как MySQL Workbench и EXPLAIN Analyzer, могут генерировать планы визуального объяснения и предлагают интерактивный интерфейс для навигации по плану выполнения и подробного изучения каждой операции.

Например, в MySQL Workbench создать визуальный план объяснения так же просто, как выполнить запрос и нажать кнопку «Объяснить план » на вкладке результатов.Здесь представлено графическое представление плана выполнения запроса, а также подробная информация о каждой операции. Это позволяет выявить любые проблемы с производительностью, а затем при необходимости оптимизировать запрос.

5 — MySQL-тюнер

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

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

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

Примечание. Если ваше веб-приложение (и база данных) уже размещены в облаке Servebolt — это то, на чем наша команда специализируется и может сделать лучше, чем любые рекомендации, которые может предоставить инструмент.

6 – Профилировщики запросов

Существуют сторонние профилировщики запросов, которые можно использовать для профилирования SQL-запросов, например, MariaDB Enterprise Query Analyzer , Dataedo и Percona Toolkit . Сторонние профилировщики запросов могут предоставлять дополнительные функции и возможности по сравнению со встроенными инструментами, доступными в MariaDB (или MySQL).

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

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

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

7 – Профилирование с помощью инструментов мониторинга

Существует также ряд инструментов мониторинга, таких как Prometheus, Grafana и Nagios, которые можно использовать для профилирования запросов и мониторинга производительности ваших баз данных.

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

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

Nagios помогает вам постоянно следить за состоянием вашей базы данных.Его можно настроить для мониторинга ключевых ресурсов, таких как ЦП, ОЗУ и дисковое пространство, а также для отслеживания других служб и сетевых устройств. Поскольку он легко настраивается, это отличный инструмент для упреждающего мониторинга запросов к базе данных.

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

Общие методы оптимизации запросов

Существует несколько распространенных методов оптимизации запросов, которые можно использовать для повышения производительности SQL-запросов:

1 – Индексация

Индексы — это способ ускорить запросы, особенно те, которые используют фильтры(WHERE).Использование индексов приводит к тому, что структуры данных в вашей базе данных (MariaDB или MySQL) находятся за пределами определенных таблиц и указывают на данные, которые вы пытаетесь запросить. В этом посте мы не будем вдаваться в подробности, поскольку использование индексов для улучшения запросов к базе данных требует отдельной статьи, которую мы планируем осветить в будущем.

Например, рассмотрим большую таблицу под названием «заказы», ​​которая содержит миллионы строк данных, включая такую ​​информацию, как идентификатор заказа, идентификатор клиента и дата заказа. Если выполняется запрос для получения всех заказов, размещенных конкретным клиентом без индекса в столбце идентификатора клиента, MariaDB придется сканировать всю таблицу, чтобы найти соответствующие данные. Это может занять много времени и ресурсов, особенно для больших таблиц.

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

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

Чтобы просмотреть любые индексы, существующие в конкретной таблице,

Вы можете просмотреть любые индексы, которые существуют в определенной таблице, используя SHOW INDEX FROM — например, в приведенном ниже примере для таблицы wp_postmeta:

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

В одном сценарии мы недавно создали два индекса для таблицы wp_postmeta:sb_postid_metakey и sb_postid_metakey_metaval.

Эти индексы были добавлены на основе изучения самых медленных запросов и обнаружения того, что все они были относительно схожими по характеристике операторов SELECT, которые фильтруют с использованием WHERE в дополнение к множеству условий сравнения (И/ИЛИ). Увидев это, я просмотрел текущие индексы для используемой таблицы и запустилEXPLAIN EXTENDED для запроса, чтобы дополнительно проверить мой подход.

Запрос в основном работал и использовал таблицу wp_postmeta с использованиемJOIN.В зависимости от порядка, в котором это происходило, добавление этих индексов позволило бы MariaDB (или MySQL) получать ответ от индексов вместо сканирования всей таблицы со всеми ее строками.

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

Это комбинация «выяснения вещей» с использованием имеющихся в вашем распоряжении инструментов (как описано выше), а также знаний о типах данных и содержимом базы данных. Это далеко не всегда работает; даже когда это происходит, это не всегда приводит к повышению производительности на 500%. Наличие огромного индекса может оказаться медленнее, чем сканирование всех строк, поэтому для уверенности необходимо тестировать запросы до и после применения индексов.

Примечание. При попытке протестировать скорость индекса вам нужно отключить кэширование запросов для сеанса, используя:

SET SESSION query_cache_type=0;

В этом случае до использования индексов выполнение запроса занимало 10,437 секунды. И после создания двух индексов один и тот же запрос занял [количество секунд].

2 – Сокращение доступа к данным

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

Например, если запрос к базе данных ищет данные о пользователе для входа в систему, запрос должен быть LIMIT 1, поскольку явно не должно требоваться больше данных одного пользователя.

Примечание. Это больше относится к дизайну базы данных, чем к оптимизации.Хотя это важно для поддержания производительности, эти усилия больше важны для разработчиков плагинов (в контексте WordPress), чем для большинства конечных пользователей.

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

SET SESSION query_cache_type=0;

3 – Использование секционирования данных

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

Чтобы разбить данные в базе данных, вы можете выполнить следующие шаги:

1. При выборе таблицы для секционирования обязательно выберите ту, которая содержит большой объем данных и будет выгодна от разделения. Это поможет оптимизировать вашу систему и повысить производительность запросов.
2. Выбор правильного метода секционирования для вашей базы данных имеет решающее значение. Вы можете выбрать диапазон, список, хэш или разделение по ключу — в зависимости от структуры ваших данных и запросов, которые вы планируете выполнять. Убедитесь, что вы выбрали тот, который лучше всего соответствует вашим потребностям для оптимизации производительности и результатов.
   
   1. Разделение по диапазону — идеальный выбор, когда у вас есть данные, которые можно разделить на определенные диапазоны.Например, если у вас есть таблица с данными за несколько лет, вы можете создать раздел диапазона, чтобы лучше организовать ее. Это может быть основано на дате или числовом значении рассматриваемого столбца.
   2. Разделение списка — это эффективный метод обработки данных, которые можно легко разделить на различные группы в соответствии с определенным параметром.Например, у вас есть таблица с информацией о сотрудниках, классифицированной по отделам; это требует использования разделения списка.
   3. Разбиение по хешу — это эффективная стратегия организации данных в кластеры одинакового размера на основе хеш-значения определенного столбца.Это позволяет равномерно распределить данные по нескольким разделам, что делает его отличным выбором для эффективного распределения данных.
   4. Разбиение по ключу похоже на разбиение по хешу, но основное отличие состоит в том, что оно использует определенное значение столбца в качестве основы для разделения данных на разные группы.Это делает его идеальным выбором для наборов данных, которые можно разбить на отдельные группы на основе уникального идентификатора или естественного ключа.
3. Создав секционированную таблицу, вы можете эффективно разделить исходную таблицу на более мелкие. Это достигается путем добавления предложения разделения в оператор CREATE TABLE, где вы указываете желаемый метод и условия для сегментации. Это может помочь повысить производительность запросов, а также сделать управление данными более эффективным.
4. Вы можете быстро скопировать данные из исходной таблицы во вновь разбитую с помощью оператора INSERT INTO… SELECT. Это позволит легко заполнить вашу секционированную таблицу всей необходимой информацией.
5. Теперь приложения должны быть перенастроены, чтобы использовать преимущества многораздельной таблицы. Это заменит исходную таблицу и сделает ваши приложения более эффективными.
6. Перед запуском любого теста для оценки потенциального улучшения производительности необходимо сначала отключить кэширование запросов, выполнив команду: SET SESSION query_cache_type=0;
7. Чтобы ваша секционированная таблица работала без сбоев, важно внимательно следить за ее производительностью. Если вы заметили какие-либо проблемы, может помочь изменение условий разбиения или переход на другой метод. Регулярный мониторинг ваших разделов поможет вам максимально использовать их потенциал.

Важное примечание об обновлениях сценариев и секционированных таблицах

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

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

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

4 — Редис

Для клиентов Servebolt Redis — это (платное) дополнение, которое может помочь с оптимизацией запросов.

Redis (иногда называемый сервером удаленного словаря) — это решение с открытым исходным кодом, которое хранит данные в памяти и может использоваться для кэширования, базы данных или даже в качестве брокера сообщений. Его можно интегрировать с базой данных для повышения производительности, выступая в качестве эффективного посредника между приложением и базой данных.

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

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

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

Помните, что если вы внедряете Redis в первый раз, вам необходимо отключить кэширование запросов перед запуском каких-либо тестов производительности. Для этого используйте команду:

SET SESSION query_cache_type=0;

Заключение

Экосистема MariaDB и MySQL имеет широкий спектр инструментов и методов, облегчающих обнаружение узких мест в выполнении запросов к базе данных, что позволяет повысить производительность ваших веб-приложений.

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