Mysql запросы: Работа с запросами в MySQL

Содержание

Производительность MySQL. Часть 1. Анализ и оптимизация запросов. Хостинг в деталях

Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.

Как понять, что дело именно в MySQL

Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в WordPress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.

Drupal Devel

Если сайт размещается на VPS или выделенном сервере, аналогичные данные можно получить и непосредственно из MySQL. Например, из журнала медленных запросов.

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

С чего начать оптимизацию

Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:

  1. Есть тяжелые запросы, занимающие сотни миллисекунд.
  2. Запросов много, но все они выполняются достаточно быстро.

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

Во втором случае имеет смысл заняться углубленным анализом логов и тонкой настройкой MySQL. На виртуальном хостинге сделать это не получится, только на VPS и выделенных серверах.

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

Кеш запросов

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

По умолчанию кеширование выключено. Включить его можно, добавив в конфигурационный файл my.cnf строчку вида query_cache_size = 64M . Через переменную query_cache_size задается размер оперативной памяти, выделяемой под кеш, в данном случае — 64 мегабайта.

Теперь нужно перезапустить MySQL. Сделать это можно из некоторых панелей управления (в ISPmanager: Management tools — Services), либо по SSH из командной строки примерно так:
/usr/local/etc/rc.d/mysql-server stop
/usr/local/etc/rc.d/mysql-server start

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

Есть еще несколько переменных для настройки кеша:

  • query_cache_type задает режим работы кеша, когда query_cache_size установлен больше нуля. Допустимые значения query_cache_type: 0 или OFF — кеширование выключено; 1 или ON — кеширование включено для всех выражений, кроме начинающихся с SELECT SQL_NO_CACHE; 2 или DEMAND — кеширование включено только для запросов, начинающихся с SELECT SQL_CACHE.
  • query_cache_limit – максимально допустимый размер, при котором результат выполнения запроса будет сохранен в кеше.
  • query_cache_min_res_unit – минимальный размер блоков памяти, выделяемых под кеш. По умолчанию 4 Кб. Если у вас много результатов значительно меньшего объема, query_cache_min_res_unit можно понизить, чтобы память использовалась эффективнее. Подходящее значение можно рассчитать по формуле (query_cache_size — Qcache_free_memory) / Qcache_queries_in_cache.

Пример my.cnf для небольшого VPS:
query_cache_size = 64M
query_cache_limit = 2M
query_cache_type = 1
query_cache_min_res_unit = 2K

Посмотреть текущее состояние кеша можно в phpMyAdmin на вкладке Status, либо из командной строки:

# mysql -u root -p
Password: ********
mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%’;
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Qcache_free_blocks         | 130        |
| Qcache_free_memory         | 56705448   |
| Qcache_hits                | 57092      |
| Qcache_inserts             | 10412      |
| Qcache_lowmem_prunes       | 0          |
| Qcache_not_cached          | 5036       |
| Qcache_queries_in_cache    | 1023       |
| Qcache_total_blocks        | 2409       |
+----------------------------+------------+
8 rows in set (0.01 sec)
  • Qcache_free_blocks – количество свободных блоков в кеше.
  • Qcache_free_memory – объем свободной ОЗУ, отведенной под кеш.
  • Qcache_hits – количество запросов, результаты которых были взяты из кеша.
  • Qcache_inserts – количество запросов, которые были добавлены в кеш.
  • Qcache_lowmem_prunes – количество запросов, которые были удалены из кеша из-за нехватки памяти.
  • Qcache_not_cached – количество запросов, которые не были записаны в кеш (с SQL_NO_CACHE или некешируемые по другим причинам).
  • Qcache_queries_in_cache – количество запросов, которые находятся в кеше.
  • Qcache_total_blocks – общее количество блоков.

Долю закешированных запросов от их общего числа можно посчитать по формуле Qcache_hits / (Com_select + Qcache_hits). Степень использования кеша — Qcache_hits / Qcache_inserts.

О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)

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

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

Результат работы оператора EXPLAIN

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

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

  • производится поиск в части WHERE
  • соединяются таблицы при JOIN
  • сортируются и группируются записи при ORDER BY и GROUP BY
  • производится поиск MIN() и MAX()

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

Разбирая вывод EXPLAIN, обратите особое внимание на столбцы

  • type (значение ALL — плохо)
  • key (NULL — плохо)
  • ref (NULL — плохо)
  • extra (Using filesort, Using temporary, Using where — плохо)

Описание всех значений и пример оптимизации запроса можно посмотреть в документации.

Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name)

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

Если определить тяжелые запросы «на глаз» не получается, нужно собрать более обширную статистику. В этом поможет журнал медленных запросов (slow query log).

Для включения журнала в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение 1 или ON; для отключения журнала — 0 или OFF. В более старых версиях используется log-slow-queries = /var/db/mysql/slow_queries.log (путь можно задать другой).

Вторая важная настройка — long_query_time — порог времени выполнения, при превышении которого запрос считается медленным и записывается в журнал. Начиная с MySQL 5.1.21 может задаваться в микросекундах и может быть равен нулю.

Пара полезных дополнительных настроек:

  • log-queries-not-using-indexes – запись в журнал запросов, не использующих индексы.
  • slow_query_log_file – имя файла журнала. По умолчанию host_name-slow.log

Пример для записи в журнал всех запросов, выполняющихся дольше 50 миллисекунд:
slow_query_log = 1
slow_query_log_file = /var/db/mysql/slow_queries.log
long_query_time = 0.05
log-queries-not-using-indexes = 1

Пример для старых версий MySQL, все запросы дольше 1 секунды:
log-slow-queries = /var/db/mysql/slow_queries.log
long_query_time = 1

Для анализа журнала используются утилиты mysqldumpslow, mysqlsla и mysql_slow_log_filter. Они парсят журнал и выводят агрегированную информацию о медленных запросах.

mysqldumpslow – утилита из состава MySQL. Вызывается таким образом:

mysqldumpslow [параметры] [файл_журнала ...] . Пример:

mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1

Count – сколько раз был выполнен запрос данного типа. Time – среднее время выполнения запроса, дальше в скобках – суммарное время выполнения всех запросов данного типа.

Некоторые параметры mysqldumpslow:

  • -t N – отображать только первые N запросов.
  • -g pattern — анализировать только запросы, которые соответствуют шаблону (как grep).
  • -s sort_type — как сортировать вывод. Значения sort_type: t или at — сортировать по суммарному или среднему времени выполнения запросов, c — по количеству выполненных запросов данного типа.

mysqlsla – еще одна утилита для анализа логов MySQL с аналогичной функциональностью. Пример использования:

mysqlsla -lt slow /tmp/slow_queries.log

Подробности в документации.

mysql_slow_log_filter
— perl-скрипт с похожей функциональностью. Пример использования:

tail –f mysql-slow.log | mysql_slow_log_filter –T 0.5 –R 1000

Эта команда в реальном времени покажет запросы, выполняющиеся дольше 0,5 секунды или сканирующие больше 1000 строк.

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

Вторая часть статьи будет посвящена тонкой настройке MySQL. Материал находится в разработке.


Евгений Демин, http://unixzen.ru
Дмитрий Сергеев, http://hosting101.ru

Просмотр очереди запросов в MySQL

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

Но чтобы найти медленный запрос, требуется либо обратиться за помощью к CMS (это лучший вариант, если в CMS есть такая функция). Либо включить отладчик на PHP или написать свой измеритель времени выполнения. Но есть и другой способ. Можно посмотреть очередь запросов в MySQL напрямую. Для этого следует лишь выполнить в SQL консоли запрос:

SELECT * FROM information_schema.processlist;
Учитывая что запросы от сайта к базе данных выполняются за доли секунд, придётся много раз выполнять эту команду, в надежде поймать «тормозящую» операцию. Делать это вручную утомительно, но можно воспользоваться утилитой «mysqladmin». Её необходимо запустить с несколькими параметрами из консоли сервера, чтобы она раз в секунду сама показывала список очереди MySQL. Команда будет выглядеть так:
mysqladmin -h ip_адрес -P порт -u польз --verbose -p -i 1 processlist
Рассмотрим ключи в этой команде:
  • -h — ip адрес сервера (хоста) с базой данных. По умолчанию «localhost». В команде нужно заменить «ip_адрес» на ip адрес.
  • -P — порт базы данных. По умолчанию «3306». В команде нужно заменить «порт» на число.
  • -u — имя пользователя базы данных. По умолчанию «root». В команде нужно заменить «польз» на логин.
  • -p — запоминает введённый пароль.
  • -i — интервал в секундах между запросами.
  • —verbose — показать полную информацию о запросе. Иначе SQL строка запроса будет обрезаться, чтобы таблица вошла в терминал по ширине.
При выполнении команды каждую секунду будет распечатываться таблица со всеми запросами в очереди к базе. В этой таблице будут следующие столбцы:
  • id запроса
  • Имя пользователя
  • Имя базы данных
  • Хост (ip адрес)
  • Тип команды
  • Время исполнения
  • SQL строку запроса
  • Размер ответа
Обратите внимание на первый столбец «id запроса». Если необходимо оборвать запрос, то нужно воспользоваться командой kill, которой передаётся этот id запроса (целое число):
mysqladmin -h ip_адрес -P порт -u польз. kill id_запроса
В любом случае, даже при отображении очереди раз в секунду через mysqladmin, существует возможность не поймать с первого раза «тормозящий» запрос. Ведь длятся они меньше одной секунды. Поэтому придётся набраться терпения.

Хранилище запросов: База данных Azure для MySQL

  • Чтение занимает 7 мин

В этой статье

Область применения: База данных Azure для MySQL — отдельный сервер

Применимо к: База данных Azure для MySQL 5.7, 8.0.

Компонент «Хранилище запросов» в Базе данных Azure для MySQL позволяет отслеживать производительность запросов с течением времени. Хранилище запросов упрощает устранение неполадок, позволяя быстро выявлять самые медленные и ресурсоемкие запросы. Хранилище запросов автоматически ведет журнал запросов и статистики выполнения и сохраняет их для просмотра. Этот компонент разделяет данные по периодам, давая представление о закономерностях использования баз данных. Данные для всех пользователей, баз данных и запросов хранятся в базе данных схемы mysql в экземпляре Базы данных Azure для MySQL.

Распространенные сценарии использования хранилища запросов

Хранилище запросов можно использовать во многих сценариях, включая следующие:

  • обнаружение регрессивных запросов;
  • определение числа выполнений запроса за данный период времени;
  • сравнение среднего времени выполнения запроса за периоды времени для выявления больших расхождений;

Включение хранилища запросов

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

Включение хранилища запросов с помощью портала Azure

  1. Войдите на портал Azure и выберите сервер Базы данных Azure для MySQL.
  2. В разделе меню Параметры выберите Параметры сервера.
  3. Найдите параметр query_store_capture_mode.
  4. Задайте для него значение «ALL» и щелкните Сохранить.

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

  1. Найдите параметр query_store_wait_sampling_capture_mode.
  2. Задайте для него значение «ALL» и щелкните Сохранить.

Подождите около 20 минут, пока первый набор данных не сохранится в базе данных mysql.

Данные в хранилище запросов

Хранилище запросов включает два хранилища:

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

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

Следующий запрос возвращает сведения о запросах в хранилище запросов:

SELECT * FROM mysql.query_store;

Это запрос статистики ожидания:

SELECT * FROM mysql.query_store_wait_stats;

Поиск запросов ожидания

Примечание

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

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

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

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

Варианты настройки

При включении хранилища запросов оно сохраняет данные с 15-минутным периодом агрегирования: не более 500 уникальных запросов на период.

Ниже приведены настраиваемые параметры для хранилища запросов.

Параметр Описание Default Range
query_store_capture_mode Включение или отключение функции хранилища запросов в зависимости от значения. Примечание. Если параметр performance_schema отключен, то включение параметра query_store_capture_mode включит performance_schema и набор инструментов схемы производительности, необходимых для работы этой функции. ALL NONE, ALL
query_store_capture_interval Интервал записи в хранилище запросов в минутах. Позволяет указать интервал агрегирования метрик запросов. 15 5–60
query_store_capture_utility_queries Включение или отключение сбора статистики всех запросов служебной программы, которые выполняются в системе. NO YES, NO
query_store_retention_period_in_days Период времени в днях для хранения данных в хранилище запросов. 7 1–30

Следующие параметры применяются исключительно к статистике ожидания.

Параметр Описание Default Range
query_store_wait_sampling_capture_mode Позволяет включить и отключить сбор статистики ожидания. None NONE, ALL
query_store_wait_sampling_frequency Изменяет частоту выборки времени ожидания в секундах. 5–300 секунд. 30 5–300

Примечание

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

Используйте портал Azure или Azure CLI, чтобы получить значение для параметра или задать другое значение.

Представления и функции

Просмотр и управление хранилищем запросов осуществляется с помощью следующих представлений и функций. Любой пользователь с ролью select privilege public может использовать эти представления для просмотра данных в хранилище запросов. Эти представления доступны только в базе данных mysql.

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

mysql.query_store

Это представление возвращает все данные в хранилище запросов. Для каждого отдельного идентификатора базы данных, идентификатора пользователя и идентификатора запроса используется отдельная строка.

имя; Тип данных IS_NULLABLE Описание
schema_name varchar(64) NO Имя схемы.
query_id bigint(20) NO Уникальный идентификатор, сформированный для конкретного запроса. Если тот же запрос выполняется в другой схеме, создается новый идентификатор.
timestamp_id TIMESTAMP NO Метка времени выполнения запроса. Она основана на значении query_store_interval.
query_digest_text longtext NO Нормализованный текст запроса после удаления всех литералов.
query_sample_text longtext NO Первое вхождение фактического запроса с литералами.
query_digest_truncated bit YES Указывает, был ли текст запроса усечен. Если длина запроса превышает 1 КБ, будет указано значение YES.
execution_count bigint(20) NO Количество выполнений запроса для этого идентификатора метки времени или в течение заданного периода времени.
warning_count bigint(20) NO Число предупреждений, созданных во время указанного периода.
error_count bigint(20) NO Количество ошибок, созданных этим запросом в течение указанного периода.
sum_timer_wait double YES Общее время выполнения этого запроса в течение указанного периода.
avg_timer_wait double YES Среднее время выполнения этого запроса в течение указанного периода.
min_timer_wait double YES Минимальное время выполнения этого запроса.
max_timer_wait double YES Максимальное время выполнения.
sum_lock_time bigint(20) NO Общее время, затраченное на все блокировки при выполнении этого запроса в течение указанного периода времени.
sum_rows_affected bigint(20) NO количество затронутых строк.
sum_rows_sent bigint(20) NO Количество строк, отправленных в клиент.
sum_rows_examined bigint(20) NO Число проверенных строк.
sum_select_full_join bigint(20) NO Число полных объединений.
sum_select_scan bigint(20) NO Количество поисков с помощью инструкции SELECT.
sum_sort_rows bigint(20) NO Количество отсортированных строк.
sum_no_index_used bigint(20) NO Количество случаев, когда запрос не использовал индексы.
sum_no_good_index_used bigint(20) NO Количество случаев, когда подсистема выполнения запросов не использовала правильные индексы.
sum_created_tmp_tables bigint(20) NO Общее число созданных временных таблиц.
sum_created_tmp_disk_tables bigint(20) NO Общее число временных таблиц, созданных на диске (с помощью операций ввода-вывода).
first_seen TIMESTAMP NO Первое обнаружение (в формате UTC) запроса во время периода агрегирования.
last_seen TIMESTAMP NO Последнее обнаружение (в формате UTC) запроса во время этого периода агрегирования.

mysql.query_store_wait_stats

Это представление возвращает данные событий ожидания в хранилище запросов. Для каждого отдельного идентификатора базы данных, идентификатора пользователя, идентификатора запроса и события используется отдельная строка.

имя; Тип данных IS_NULLABLE Описание
interval_start TIMESTAMP NO Начало интервала (с приращением по 15 минут).
interval_end TIMESTAMP NO Окончание интервала (с приращением по 15 минут).
query_id bigint(20) NO Созданный уникальный идентификатор нормализованного запроса (из хранилища запросов).
query_digest_id varchar(32) NO Нормализованный текст запроса после удаления всех литералов (из хранилища запросов).
query_digest_text longtext NO Первое вхождение фактического запроса с литералами (из хранилища запросов).
event_type varchar(32) NO Категория события ожидания.
event_name varchar(128) NO Имя события ожидания.
count_star bigint(20) NO Число событий ожидания, выбранных в течение интервала для запроса.
sum_timer_wait_ms double NO Общее время ожидания (в миллисекундах) этого запроса в течение интервала.

Функции

имя; Описание
mysql.az_purge_querystore_data(TIMESTAMP) Очистка всех данных хранилища запросов до заданной метки времени.
mysql.az_procedure_purge_querystore_event(TIMESTAMP) Очистка всех данных событий ожидания до заданной метки времени.
mysql.az_procedure_purge_recommendation(TIMESTAMP) Очистка рекомендаций, срок действия которых предшествует заданной метке времени.

Ограничения и известные проблемы

  • Если для сервера MySQL включен параметр read_only, то хранилище запросов не сможет записывать данные.
  • Операции хранилища запросов могут быть прерваны при обнаружении длинных запросов в Юникоде (>= 6000 байт).
  • Период хранения статистики ожидания составляет 24 часа.
  • Для получения статистики ожидания используется выборка части событий. Частоту выборки можно изменить с помощью параметра query_store_wait_sampling_frequency.

Дальнейшие действия

запрос в запросе. MySQL: примеры запросов. Вложенные запросы MySQL

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

Реляционная система управления базами данных MySQL является одной из самых востребованных и часто используемых технологий хранения информации. Ее функциональные возможности превосходят по многим показателям существующие СУБД. В частности, одной из главных особенностей является возможность использовать вложенные запросы MySQL.

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

Как должно быть организовано хранение данных

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

Например, если составляется таблица сотрудников определенной компании, то ее самая простая структура будет иметь следующий вид. За каждым сотрудником закреплен уникальный номер, который, как правило, используется в качестве первичного ключа к таблице. Затем в таблицу заносятся персональные данные сотрудника. Это может быть что угодно: Ф. И. О., номер отдела, за которым он закреплен, телефон, адрес и прочее. Согласно требованиям нормализации (6 нормальных форм баз данных), а также для того, чтобы MySQL-запросы выстраивались структурированно, поля таблицы должны быть атомарными, то есть не иметь перечислений или списков. Поэтому, как правило, в таблице существуют отдельные поля для фамилии, имени и т. д.

Employee_id

Surname

Name

Patronymic

Department_id

Position

Phone

Employer_id

1

Иванов

Иван

Иванович

Администрац.

Директор

49 ***

null

2

Петров

Петр

Петрович

Администрац.

Зам. директора

49 **

1

3

Гришин

Григорий

Григорьевич

Продажи

Начальник

1

59

Сергеев

Сергей

Сергеевич

Продажи

Продавец-консульт.

49 **

32

Выше представлен тривиальный пример структуры таблицы базы данных. Однако она ещё не до конца отвечает основным требованиям нормализации. В реальных системах создается дополнительная таблица отделов. Поэтому приведенная таблица вместо слов в колонке «Отдел» должна содержать номера отделов.

Каким образом происходит выборка данных

Для получения данных из таблиц в СУБД используется специальная команда MySQL – запрос Select. Для того чтобы сервер базы данных правильно отреагировал на обращение, запрос должен быть корректно сформирован. Структура запроса формируется следующим образом. Любое обращение к серверу БД начинается с ключевого слова select. Именно с него строятся все в MySQL запросы. Примеры могут иметь различную сложность, но принцип построения очень похож.

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

Для ограничения выборки в MySQL-запросы добавляются специальные операторы, предусмотренные СУБД. Для выборки неповторяющихся (уникальных) данных используется предложение distinct, а для задания условий – оператор where. В качестве примера, применимого к вышеуказанной таблице, можно рассмотреть запрос, требующий информацию о Ф.И.О. сотрудников, работающих в отделе «Продажи». Структура запроса примет вид, как в таблице ниже.

Понятие вложенного запроса

Но главная особенность СУБД, как было указано выше, возможность обрабатывать вложенные запросы MySQL. Как он должен выглядеть? Из названия логически понятно, что это запрос, сформированный в определенной иерархии из двух или более запросов. В теории по изучению особенностей СУБД сказано, что MySQL не накладывает ограничений на количество MySQL-запросов, которые могут быть вложены в главный запрос. Однако можно поэкспериментировать на практике и убедиться, что уже после второго десятка вложенных запросов время отклика серьезно увеличится. В любом случае на практике не встречаются задачи, требующие использовать чрезвычайно сложный MySQL-запрос. В запросе может потребоваться максимально до 3-5 вложенных иерархий.

Построение вложенных запросов

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

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

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

Итоги

Подводя итог, необходимо отметить, что существует ещё много других дополнительных возможностей, которые значительно облегчают построение запросов, так как СУБД MySQL – мощное средство с богатым арсеналом инструментов для хранения и обработки данных.

MySQL запросы для заполнения таблицы данными из других таблиц

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

Если две таблицы одинаковы по структуре, все просто:

INSERT INTO `table_1` SELECT * FROM `table_2`

Для таблиц с разными названиями полей придется делать «подгонку». В запросе данные из `table_2` добавляются `table_1`.

INSERT INTO `table_1` (`id`, `name`, `keywords`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	`words` AS `keywords`,
	`short_text` AS `text`
FROM
	`table_2`

Соединение полей

INSERT INTO `table_1` (`id`, `name`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	CONCAT(`words`, `short_text`) AS `text`
FROM
	`table_2`

Соединение полей через разделитель.

INSERT INTO `table_1` (`id`, `name`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	CONCAT_WS(' ', `words`, `short_text`) AS `text`
FROM
	`table_2`

Подзапросы из других таблиц

INSERT INTO `table_1` (`id`, `name`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	(SELECT `full_text` FROM `table_3` WHERE `id` = `table_2`.`item_id`) AS `text`
FROM
	`table_2`

Объединение таблиц

LEFT JOIN

INSERT INTO `table_1` (`id`, `name`, `keywords`, `text`)
SELECT
	NULL AS `id`,
	`table_2`.`title` AS `name`,
	`table_2`.`words` AS `keywords`,
	`table_3`.`full_text` AS `text
FROM
	`table_2`
LEFT JOIN
	`table_3`
ON
	`table_2`.`id` = `table_3`.`item_id`

UNION

INSERT INTO `table_1` (`id`, `name`, `text`)
(
	SELECT
		NULL AS `id`,
		`title` AS `name`,
		`short_text` AS `text`
	FROM
		`table_2`
)
UNION
(
	SELECT
		NULL AS `id`,
		`title` AS `name`,
		`short_text` AS `text`
	FROM
		`table_3`
)

MySQL. Просмотр запросов в реальном времени

Дата 30.05.2021 Автор Alex Рубрика Сервер

В Linux существует команда «watch», которая позволяет запускать команды, стоящие после неё с определённым интервалом. Так можно почти в реальном времени отследить значения в выводе. К сожалению, иногда её неудобно или невозможно использовать для просмотра запросов MySQL. Но это ещё можно делать через mysqladmin.

Для просмотра всех исполняемых запросов из очереди в MySQL можно выполнить команду:

SELECT * FROM information_schema.processlist;

Но запросы часто выполняются намного быстрее секунды. Поэтому придётся запускать команду раз десять, чтобы поймать хотя бы один запрос, который находится в процессе выполнения. И чтобы не молотить пальцами по клавиатуре, можно воспользоваться утилитой «mysqladmin», запустив её с параметром «processlist» и некоторыми другими:

mysqladmin -h ip_адрес -P порт -u польз. —verbose -p -i 1 processlist

Разберём все ключи mysqladmin в этой команде:

  • -h — задаёт адрес сервера (хоста) с базой данных. По умолчанию «localhost».
  • -P — указывает порт сервера, который прослушивается базой данных. По умолчанию «3306».
  • -u — содержит имя пользователя базы данных, от имени которого надо выполнять запросы. По умолчанию «root».
  • -p — заставляет mysqladmin запоминать введённый пароль, чтобы не переспрашивал. Можно не использовать, если пароль указан в файле «.my.conf», в домашней папке пользователя.
  • -i — задаёт интервал в секундах между запросами.
  • —verbose — выводит полную информацию о запросе. Иначе SQL строка запроса будет обрезаться, чтобы таблица вошла в терминал по ширине.

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

  • id запроса
  • Имя пользователя
  • Имя базы данных
  • Хост (ip адрес)
  • Тип команды
  • Время исполнения
  • SQL строку запроса
  • Размер ответа

Чтобы завершить повисший запрос можно воспользоваться командой kill, которой передаётся id запроса (который узнаётся из первого столбца таблицы «processlist»):

mysqladmin -h ip_адрес -P порт -u польз. kill id_запроса

Метки: MySQL

sql — Как оптимизировать таблицы/запрос в MySQL?

Таблица генов человеческого генома невелика, всего 60434 записей:

CREATE TABLE `genes-g38-201505` (
  `chr` varchar(2) NOT NULL,
  `left` bigint(20) NOT NULL,
  `right` int(11) NOT NULL,
  `Complement` int(11) NOT NULL,
  `Name` tinytext NOT NULL,
  `source` tinytext NOT NULL,
  `ENSEMBL` tinytext NOT NULL,
  `gene_version` tinytext NOT NULL,
  `gene_name` tinytext NOT NULL,
  `gene_source` tinytext NOT NULL,
  `gene_biotypeid` tinytext NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=MyISAM;

Таблица повторов человеческого генома, уже хуже — более 5 с половиной миллионов записей:

CREATE TABLE `repeats-g38-201505` (
  `id` int(11) NOT NULL,
  `chr` varchar(2) DEFAULT NULL,
  `left` int(11) DEFAULT NULL,
  `right` int(11) DEFAULT NULL,
  `name` tinytext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Это — две служебных таблицы. Из них нам, по большому счёту, важны лишь chr — название хромосомы, left и right — левая и правая координата целого гена/повтора или его части (частей может быть несколько, в этом случае одному name соответствует несколько наборов {chr, left, right}) и name — название гена/повтора.

Все данные для таблиц.

Теперь данные экспериментов на тканях онкологических больных. Формат таблицы таков:

CREATE TABLE `51k-80-80-ignore-random-noreverse` (
  `chr` varchar(2) NOT NULL,
  `left` bigint(20) NOT NULL,
  `right` bigint(20) NOT NULL,
  `count` int(11) NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `chr_left_right` (`chr`,`left`,`right`),
  `id` bigint(20) NOT NULL AUTO_INCREMENT
) ENGINE=MyISAM;

он одинаков для каждого эксперимента. Каждая запись описывает паттерн ДНК, принадлежащий хромосоме chr, с координатами left и right, количеством штук count. Количество записей разное, от 4 до 7 с половиной миллиона на эксперимент. Каждая запись — уникальный набор по координатам {chr, left, right}

И финальная таблица, в которую нужно собрать данные 4х экспериментов:

CREATE TABLE `pk47-pk51-gene-repeat` (
  `chr` varchar(2) NOT NULL,
  `left` bigint(20) NOT NULL,
  `right` bigint(20) NOT NULL,
  `count_k51` int(11) DEFAULT '0',
  `count_p51` int(11) DEFAULT '0',
  `count_p47` int(11) DEFAULT '0',
  `count_k47` int(11) DEFAULT '0',
  `name_left` varchar(29) NOT NULL,
  `name_right` varchar(17) NOT NULL,
  UNIQUE KEY `pos_name` (`chr`,`left`,`right`,`name_left`,`name_right`)
) ENGINE=MyISAM;

Фактически, всё просто: нужно найти только те паттерны, которые левым краем попадают на ген, а правым — на повтор, посчитать их количество и вывести в сводную таблицу. С запросом вроде бы проблем не возникло, 4 раза повторяю такой запрос, меняя лишь count_k51 на count_p51 и саму таблицу-источник :

INSERT INTO `pk47-pk51-gene-repeat` (
      `chr`,`left`, `right`,`count_k51`,`name_left`, `name_right`
)
SELECT 
     a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
     g.`name` as `name_left`, 
     r.`name` as `name_right` 
FROM `
     51k-80-80-ignore-random-noreverse` a, 
     `genes-g38-201505` g,
     `repeats-g38-201505` r 
WHERE
     a.`chr`=g.`chr` and a.`chr`=r.`chr` and 
     a.`left` < g.`right` and a.`left` > g.`left` and 
     a.`right` < r.`right` and a.`right` > r.`left` 
     on duplicate key 
     update 
       `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;

В первый запуск on duplicate key можно не использовать. Наверное, можно составить и единственный запрос вместо 4х, но он будет чрезвычайно громоздким, и я пока решил пробовать так.

Разумеется, запрос не выполнился, отвалился по таймауту, который я и так сильно увеличил. limit 0,1000; limit 1001,2000 и так далее, как я понимаю, использовать бесполезно, поскольку каждый следующий этап сервер всё равно будет проходить предыдущие.
Решил итерировать запросы по id, добавляя ограничение 20000*i< a.id <20000*(i+1) в запрос, но ситуация не улучшилась, видимо, id надо переопределить, либо заставить сервер проводить данную проверку первой.

Как итог, нужны идеи, как можно оптимизировать запрос, перестроить таблицы или поменять подход, чтобы решить эту задачу (не обязательно чистым SQL-запросом, работать с базой из языков программирования я умею). Скажу спасибо и за советы по физическому ускорению сервера: памяти на машине 32Гб, сервер использует мало, может, какие-то переменные подкрутить?

Update 1. Привожу результаты EXPLAIN для запроса:

Изначальное состояние:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'g', 'ALL', NULL, NULL, NULL, NULL, '60433', NULL
'1', 'SIMPLE', 'a', 'ref', 'chr_left_right', 'chr_left_right', '4', 'dna_homo_pairs.g.chr', '47216', 'Using index condition'
'1', 'SIMPLE', 'r', 'ALL', NULL, NULL, NULL, NULL, '5317291', 'Using where; Using join buffer (Block Nested Loop)'

Добавлены индексы (chr, left, right) по совету @Mike:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'a', 'ALL', 'chr_left_right', NULL, NULL, NULL, '4721638', NULL
'1', 'SIMPLE', 'g', 'ref', 'chr_left_right', 'chr_left_right', '4', 'methyl_base.a.chr', '604', 'Using index condition'
'1', 'SIMPLE', 'r', 'ref', 'chr_left_right', 'chr_left_right', '5', 'methyl_base.a.chr', '53172', 'Using index condition'

Update 2. Заставить mysqld работать в несколько потоков.

Взглянул на загрузку CPU во время запроса. Поскольку сейчас я работаю в монопольном режиме, я один обращаюсь в локальному серверу. Можно ли как-то заставить mysqld обрабатывать один запрос в несколько потоков? А то 8 ядер/16 потоков в его распоряжении, а он пользует только один.

Кстати, раскидывание разных таблиц в папки на разных физических жестких дисках даёт, пусть небольшое, но ускорение работы.

Update 3 На данный момент я программно разбил все исходные таблицы в зависимости от того, какая хромосома и скрипт (точнее, серия скриптов, тоже вызываются программно) сейчас выглядит так ( допустим, обрабатывается 7я хромосома):

INSERT INTO `pk47-pk51-gene-repeat` (
      `chr`,`left`, `right`,`count_k51`,`name_left`, `name_right`
)
SELECT 
     "7", a.`left`, a.`right`, a.`count` as `count_k51`,
     g.`name` as `name_left`, 
     r.`name` as `name_right` 
FROM `
     51k-80-80-ignore-random-noreverse-chr7` a, 
     `genes-g38-201505-chr7` g,
     `repeats-g38-201505-chr7` r 
WHERE
     a.`left` < g.`right` and a.`left` > g.`left` and 
     a.`right` < r.`right` and a.`right` > r.`left` 
     on duplicate key 
     update 
       `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;

Но особого прогресса не отмечаю.

phpMyAdmin Run MySQL Query Tutorial

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

Выполнение запросов MySQL с вкладкой SQL

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

Страница обновится, и вы увидите результаты введенного вами запроса.

Создание и выполнение запросов MySQL на вкладке «Запрос»

Другой способ запросить базу данных MySQL с помощью инструмента phpMyAdmin — использовать вкладку Query . Здесь вы можете определить различные условия поиска, отсортировать результаты и запросить несколько таблиц.

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

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

В текстовом поле Criteria введите критерии, в соответствии с которыми будет завершен поиск.

Кроме того, вы можете использовать флажки Ins и Del для добавления или удаления текстовых строк для условий поиска.То же самое можно сделать в раскрывающемся меню Добавить / удалить строку критериев.

В разделе Modify определите отношения между полями (независимо от того, связаны ли они с помощью логических операторов AND или OR ).

Вам нужно нажать на Update Query , чтобы завершить изменения.

Затем следует выбрать таблицы, используемые в запросе, из списка Использовать таблицы .

Чтобы запустить запрос, нажмите Отправить запрос .

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

Query Store — База данных Azure для MySQL

  • 7 минут на чтение

В этой статье

ОТНОСИТСЯ К: База данных Azure для MySQL — один сервер

Применимо к: База данных Azure для MySQL 5.7, 8,0

Функция хранилища запросов в базе данных Azure для MySQL позволяет отслеживать производительность запросов с течением времени. Хранилище запросов упрощает устранение неполадок с производительностью, помогая быстро находить самые длительные и наиболее ресурсоемкие запросы. Query Store автоматически сохраняет историю запросов и статистику времени выполнения и сохраняет их для вашего просмотра. Он разделяет данные по временным окнам, чтобы вы могли видеть шаблоны использования базы данных. Данные для всех пользователей, баз данных и запросов хранятся в базе данных схемы mysql в экземпляре База данных Azure для MySQL.

Общие сценарии использования хранилища запросов

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

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

Включение хранилища запросов

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

Включение хранилища запросов с помощью портала Azure

  1. Войдите на портал Azure и выберите свою базу данных Azure для сервера MySQL.
  2. Выберите Server Parameters в разделе меню Settings .
  3. Найдите параметр query_store_capture_mode.
  4. Установите значение ВСЕ и Сохраните .

Чтобы включить статистику ожидания в хранилище запросов:

  1. Найдите параметр query_store_wait_sampling_capture_mode.
  2. Установите значение ВСЕ и Сохраните .

Подождите до 20 минут, чтобы первый пакет данных сохранялся в базе данных mysql.

Информация в хранилище запросов

Query Store имеет два магазина:

  • Хранилище статистики времени выполнения для сохранения информации статистики выполнения запроса.
  • Хранилище статистики ожидания для постоянной информации статистики ожидания.

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

Следующий запрос возвращает информацию о запросах в хранилище запросов:

  ВЫБРАТЬ * ИЗ mysql.query_store;
  

Или этот запрос для статистики ожидания:

  ВЫБРАТЬ * ИЗ mysql.query_store_wait_stats;
  

Поиск запросов на ожидание

Примечание

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

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

Вот несколько примеров того, как вы можете получить больше информации о своей рабочей нагрузке, используя статистику ожидания в Query Store:

Наблюдение Действие
Ожидание High Lock Проверьте тексты запросов на наличие затронутых запросов и определите целевые объекты.Поищите в хранилище запросов другие запросы, изменяющие ту же сущность, которые часто выполняются и / или имеют большую продолжительность. После определения этих запросов рассмотрите возможность изменения логики приложения для улучшения параллелизма или использования менее строгого уровня изоляции.
Ожидание ввода-вывода высокого буфера Найдите запросы с большим количеством физических чтений в хранилище запросов. Если они соответствуют запросам с высоким ожиданием ввода-вывода, рассмотрите возможность введения индекса для базового объекта, чтобы выполнять поиск вместо сканирования.Это минимизирует накладные расходы ввода-вывода запросов. Проверьте рекомендации по производительности для вашего сервера на портале, чтобы узнать, есть ли рекомендации по индексам для этого сервера, которые могли бы оптимизировать запросы.
Ожидание высокой памяти Найдите запросы с наибольшим потреблением памяти в хранилище запросов. Эти запросы, вероятно, задерживают дальнейшее выполнение затронутых запросов. Ознакомьтесь с рекомендациями по производительности для вашего сервера на портале, чтобы узнать, есть ли рекомендации по индексам, которые могли бы оптимизировать эти запросы.

Варианты конфигурации

Когда включено хранилище запросов, данные сохраняются в 15-минутных окнах агрегации, до 500 отдельных запросов на окно.

Для настройки параметров хранилища запросов доступны следующие параметры.

Параметр Описание По умолчанию Диапазон
query_store_capture_mode Включите / выключите функцию хранилища запросов в зависимости от значения.Примечание. Если performance_schema выключена, включение query_store_capture_mode приведет к включению performance_schema и подмножества инструментов схемы производительности, необходимых для этой функции. ВСЕ НЕТ, ВСЕ
query_store_capture_interval Интервал захвата хранилища запросов в минутах. Позволяет указать интервал агрегирования показателей запроса 15 5–60
query_store_capture_utility_queries Включение или выключение для захвата всех служебных запросов, выполняемых в системе. НЕТ ДА, НЕТ
query_store_retention_period_in_days Временное окно в днях для хранения данных в хранилище запросов. 7 1–30

Следующие параметры применяются специально для статистики ожидания.

Параметр Описание По умолчанию Диапазон
query_store_wait_sampling_capture_mode Позволяет включить / выключить статистику ожидания. НЕТ НЕТ, ВСЕ
query_store_wait_sampling_frequency Изменяет частоту ожидания выборки в секундах. От 5 до 300 секунд. 30 5-300

Примечание

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

Используйте портал Azure или Azure CLI, чтобы получить или установить другое значение для параметра.

Виды и функции

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

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

mysql.query_store

Это представление возвращает все данные в хранилище запросов. Есть одна строка для каждого отдельного идентификатора базы данных, идентификатора пользователя и идентификатора запроса.

Имя Тип данных IS_NULLABLE Описание
имя_схемы varchar (64) НЕТ Имя схемы
query_id бигинт (20) НЕТ Уникальный идентификатор, сгенерированный для конкретного запроса, если тот же запрос выполняется в другой схеме, будет сгенерирован новый идентификатор
timestamp_id метка времени НЕТ Отметка времени, в которой выполняется запрос.Это основано на конфигурации query_store_interval
query_digest_text длинный текст НЕТ Нормализованный текст запроса после удаления всех литералов
query_sample_text длинный текст НЕТ Первое появление фактического запроса с литералами
query_digest_truncated бит ДА Обрезан ли текст запроса.Значение будет Да, если запрос длиннее 1 КБ
Execution_count бигинт (20) НЕТ Количество раз, когда запрос был выполнен для этого идентификатора временной метки / в течение заданного периода интервала
warning_count бигинт (20) НЕТ Количество предупреждений, сгенерированных этим запросом во время внутреннего
error_count бигинт (20) НЕТ Количество ошибок, сгенерированных этим запросом за интервал
sum_timer_wait двойной ДА Общее время выполнения этого запроса за интервал
avg_timer_wait двойной ДА Среднее время выполнения этого запроса за интервал
min_timer_wait двойной ДА Минимальное время выполнения этого запроса
max_timer_wait двойной ДА Максимальное время выполнения
sum_lock_time бигинт (20) НЕТ Общее время, затраченное на все блокировки для выполнения этого запроса в течение этого временного окна
sum_rows_affected бигинт (20) НЕТ Количество затронутых строк
sum_rows_sent бигинт (20) НЕТ Количество строк, отправленных клиенту
sum_rows_examined бигинт (20) НЕТ Количество проверенных строк
sum_select_full_join бигинт (20) НЕТ Количество полных соединений
sum_select_scan бигинт (20) НЕТ Количество выбранных сканирований
sum_sort_rows бигинт (20) НЕТ Количество отсортированных строк
sum_no_index_used бигинт (20) НЕТ Количество раз, когда запрос не использовал индексы
sum_no_good_index_used бигинт (20) НЕТ Количество раз, когда механизм выполнения запросов не использовал хорошие индексы
sum_created_tmp_tables бигинт (20) НЕТ Общее количество созданных временных таблиц
sum_created_tmp_disk_tables бигинт (20) НЕТ Общее количество временных таблиц, созданных на диске (генерирует ввод-вывод)
first_seen метка времени НЕТ Первое вхождение (UTC) запроса во время окна агрегирования
last_seen метка времени НЕТ Последнее вхождение (UTC) запроса во время этого окна агрегирования

MySQL.query_store_wait_stats

Это представление возвращает данные событий ожидания в хранилище запросов. Есть одна строка для каждого отдельного идентификатора базы данных, идентификатора пользователя, идентификатора запроса и события.

Имя Тип данных IS_NULLABLE Описание
interval_start метка времени НЕТ Начало интервала (с шагом 15 минут)
interval_end метка времени НЕТ Конец интервала (с шагом 15 минут)
query_id бигинт (20) НЕТ Сгенерированный уникальный идентификатор для нормализованного запроса (из хранилища запросов)
query_digest_id varchar (32) НЕТ Нормализованный текст запроса после удаления всех литералов (из хранилища запросов)
query_digest_text длинный текст НЕТ Первое появление фактического запроса с литералами (из хранилища запросов)
event_type varchar (32) НЕТ Категория события ожидания
имя_события varchar (128) НЕТ Имя события ожидания
count_star бигинт (20) НЕТ Число событий ожидания, выбранных в течение интервала для запроса
sum_timer_wait_ms двойной НЕТ Общее время ожидания (в миллисекундах) этого запроса в интервале

Функции

Имя Описание
MySQL.az_purge_querystore_data (TIMESTAMP) Удаляет все данные хранилища запросов до указанной отметки времени
mysql.az_procedure_purge_querystore_event (TIMESTAMP) Удаляет все данные событий ожидания до заданной отметки времени
mysql.az_procedure_purge_recommendation (TIMESTAMP) Удаляет рекомендации, срок действия которых предшествует заданной отметке времени

Ограничения и известные проблемы

  • Если на сервере MySQL включен параметр read_only , хранилище запросов не может захватывать данные.
  • Функциональность хранилища запросов
  • может быть прервана при обнаружении длинных запросов Unicode (> = 6000 байт).
  • Срок хранения статистики ожидания составляет 24 часа.
  • Статистика ожидания использует выборку для захвата части событий. Частоту можно изменить с помощью параметра query_store_wait_sampling_frequency .

Следующие шаги

Как писать сложные запросы MySQL?

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

Что такое сложный запрос MySQL?

Сложные запросы MySQL для поиска данных с использованием более чем одного параметра и могут содержать комбинацию нескольких объединений в нескольких таблицах и довольно большого количества вложенных подзапросов (запросов, вложенных в другой запрос). Сложные запросы также часто связаны с частым использованием предложений AND и OR.

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

Сложный запрос MySQL с несколькими операторами SELECT

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

Пример построения сложных MySQL-запросов в dbForge Studio для MySQL

Написание сложных запросов может пугать и несколько сбивать с толку новичка в MySQL. Здесь на помощь приходит dbForge Studio for MySQL. Его передовая функциональность Query Builder включает простой в освоении и использовании визуальный конструктор запросов, который позволяет создавать запросы MySQL без ручного кодирования.

Шаг 1. Включение построителя запросов

Чтобы начать работу с Query Builder, выберите его в списке инструментов разработки SQL на начальной странице Studio.

Шаг 2. Добавление таблиц в запрос

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

Шаг 3. Создание подзапросов

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

Чтобы создать подзапрос, щелкните правой кнопкой мыши тело диаграммы и выберите команду Создать подзапрос из контекстного меню.

Шаг 4. Создание JOIN-ов между таблицами

dbForge Studio for MySQL позволяет визуально создавать следующие СОЕДИНЕНИЯ: INNER, LEFT OUTER, RIGHT OUTER, NATURAL и CROSS JOIN. Вы также можете легко установить довольно сложные условия для соединения любого типа.

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

Ste p 5. Построение пунктов WHERE или HAVING, если необходимо

При извлечении данных может потребоваться отфильтровать или исключить записи. И лучший способ добиться этого — использовать предложения WHERE и HAVING.

Чтобы создать предложения WHERE и \ или HAVING, перейдите на соответствующие вкладки и внесите необходимые настройки с помощью интуитивно понятного интерфейса.

Шаг 6. Создание предложений GROUP BY или ORDER BY

Предложения GROUP BY и ORDER BY используются для организации выходных данных. Чтобы создать эти предложения, перейдите на соответствующую вкладку и выберите столбцы для сортировки.

Шаг 7. Просмотр и выполнение запроса

После того, как вы внесете все необходимые настройки, переключитесь в текстовое представление, чтобы проверить автоматически сгенерированный запрос.Если результат вас устраивает, нажмите кнопку «Выполнить» на главной панели инструментов, чтобы запустить скрипт.

Шаг 8. Анализ результата

Результат запроса будет немедленно отображен для анализа. Функциональность редактора данных, встроенная в dbForge Studio for MySQL, позволяет управлять данными наиболее удобным способом.

Заключение

Когда дело доходит до построения сложных запросов MySQL, работа с подзапросами, предложениями и условиями может показаться утомительной, особенно для новичков.Функциональность Query Builder, поставляемая с dbForge Studio for MySQL, обязательно устранит эту боль. Интуитивно понятный и хорошо продуманный графический интерфейс значительно упрощает создание сложных запросов и управление условиями JOIN.

Последние сообщения от команды dbForge (посмотреть все)

сложные запросы mysql, инструменты mysql, построитель запросов

Расширенное профилирование и отслеживание запросов MySQL | by Aman Garg

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

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

  • Зависимость таблицы
  • Индексы потенциального диапазона (важно)
  • Как он оценил количество строк и стоимость
  • Как он собирается подготовить соединение.
  • Как он будет выполнять соединение.
Использование: Включение трассировки запросов. Ответ довольно подробный.

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

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

Например, рассмотрим запрос ниже

Запрос с цифрой «3», где части, последний не является тривиальным

Наш предложения в частях where:

  • creative_template_id
  • status
  • concat (content_id, content_version)

Если мы исследуем вывод трассировщика всех индексов, MySQL сообщил нам, что последний , idx_vcg_creative_template_status_idversion будет охватывать большинство ключей.

Последний используемый охватывает большинство ключей даже в соответствии с MySQL

H Как это помогает?

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

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

Примечание. В приведенном выше случае конкатенация (a, b)! = Index (a, b) .Здесь могут помочь такие вещи, как виртуальные столбцы.

MySQL Query (T-SQL) Учебное пособие для SQL Server

Microsoft SQL Server и MySQL, оба являются довольно хорошо зарекомендовавшими себя механизмами баз данных. В нашей роли администратора баз данных в какой-то момент времени нам требуется объединение T-SQL между обоими механизмами базы данных, объединяет Select, Insert, Update, Delete и некоторые другие …

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

Теперь, прежде чем работать с T-SQL для MySQL внутри SQL Server, необходимо установить соединение на взаимосвязанном сервере в качестве начального шага для начала.Кроме того, на машине должен быть предварительно установлен драйвер MySQL ODBC.

Создание связанного сервера MySQL

Начнем с создания источника данных ODBC для сервера MySQL на машине, на которой установлен SQL Server.

Открытые источники данных ODBC

Чтобы добавить новый источник, нажмите кнопку Добавить , выберите MySQL ODBC 8.0 ANSI Driver для интеграции подключения MySQL и нажмите кнопку Finish .

Затем настройте необходимую информацию в окне MySQL Connector , чтобы настроить MySQL Data Source Name . В этом примере мы использовали localhost в качестве имени сервера MySQL. Теперь мы можем проверить соединение, нажав на кнопку Test . после успешного тестирования источник данных будет доступен для включения другого связанного сервера MySQL в иллюстрацию SQL Server.

Мы не выбрали какую-либо базу данных для соединения ODBC, но она работает с базой данных по умолчанию для выполнения запроса T-SQL через соединение.Если вы не указали базу данных, используйте имя базы данных . tablename в запросе T-SQL, чтобы перенаправить ваш оператор T-SQL в эту конкретную базу данных.

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

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

Связанный сервер успешно настроен для получения доступа к серверу MySQL_Local в SQL Server. Но не забудьте разрешить доступ к данным и параметры RPC для него в конфигурации. Теперь мы можем начать использовать присвоение T-SQL. Почему MySQL важен для выполнения запросов в Microsoft SQL Server? Как его можно использовать и при каких обстоятельствах?

Использование запроса T-SQL для получения данных таблицы MySQL в SQL Server

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

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

Как использовать запросы T-SQL с MySQL внутри SQL Server

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

OPENQUERY (связанный сервер, ‘Запрос’)

SELECT *

FROM OPENQUERY ([MYSQL_LOCAL],

‘SELECT fname, lname, email_, mobile_, city_, pincode, password_, user_type_id, is 9_active, created,

ОТ d_portal.users

ГДЕ user_id = 1 ‘

)

Как видно, запрос MySQL T-SQL находится внутри OPENQUERY, а [MYSQL_LOCAL] — это связанный сервер MySQL, который существует в SQL Server.Данные могут быть отфильтрованы как на стороне запроса, что означает, что мы можем применить предложение WHERE на уровне OPENQUERY внешней стороны, а также оператор внутреннего запроса.

Вставка данных в MySQL с помощью SQL Server

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

Мы столкнемся с подобными сценариями в реальном времени, когда будем работать на платформе на основе микросервисов, гибридной платформе микросервисов и монолитной платформы проектирования конструкций.Последние рабочие тенденции заключаются в создании базы данных на основе микросервисов, следовательно, было бы довольно утомительно или нелегко управлять согласованностью данных в нескольких базах данных, если это только Microsoft SQL Server или объединение технологий баз данных, таких как SQL Server, MySQL и т. Д. .

Смотрите здесь, у нас есть различные примеры для вставки данных в MySQL из SQL Server с помощью оператора T-SQL.

INSERT INTO OPENQUERY ([MYSQL_LOCAL],

‘SELECT fname, lname, email_, mobile_, city_, pincode, password_, user_type_id, is_active, created_at

FROM d_portal.users ‘

)

SELECT’ Jerry ‘AS fname,

‘ Ren ‘AS lname,

[email protected] ‘AS email_,

‘ +00 000 000 00 ‘как mobile_,

1 AS city_,

‘1011 AA’ PIN-код AS,

‘4a69676e6573682052616979616e69’ Пароль AS_,

1 как user_type_id,

1 AS is_active,

GETDATE () AS created_at

Данные будут вставлены в таблицу с помощью вышеуказанного запроса, который упоминается внутри OPENQUERY.Если в таблице есть столбец с автоматическим увеличением, то этим статусом будет управлять только MySQL. В приведенном выше примере у нас есть столбец Auto Incremental user_id в таблице пользователей базы данных d_portal, а остальные столбцы будут частью оператора INSERT.

По сути, данные могут быть вставлены в таблицу с помощью пары методов оператора T-SQL: INSERT… VALUES и INSERT… SELECT. Любой из этих вариантов также может быть применен в этом случае. Таким образом, это лучший способ администрирования согласованности транзакций, а не вставка отдельных данных приложением для любых таких случаев.

Обновление данных в MySQL с помощью SQL Server (T-SQL)

В таких случаях можно поддерживать запись в обеих базах данных SQL Server и MySQL, и, следовательно, информация обновляется в строке, строка должна обновляться в обеих базах данных серверной частью или с использованием любого такого механизма или процессов. Смотрите здесь, мы использовали оператор MySQL UPDATE в запросе T-SQL внутри самого SQL Server.

ОТКРЫТЬ ЗАПРОС ОБНОВЛЕНИЯ ([MYSQL_LOCAL],

‘ВЫБРАТЬ user_id ИЗ d_portal.пользователи, у которых user_id = 1 ‘

)

SET is_active = 0

, где user_id = 1;

Вышеупомянутый запрос T-SQL обновит столбец is_active с 0, где user_id = 1, в таблице пользователей базы данных d_portal. Этот метод следует использовать во всех таких сценариях только для того, чтобы иметь в виду производительность запроса к базе данных. Необязательно использовать этот механизм, когда нам нужно поддерживать таблицы поиска или главные таблицы как в базах данных SQL Server, так и в MySQL.В случае таблиц метаданных я бы посоветовал использовать внутренние приложения обновления только с отдельным запросом, но убедившись, что он должен быть постоянным, и поддерживать согласованность данных.

Удаление данных в MySQL с помощью SQL Server

Точно так же, как и в случае с оператором обновления, если строки необходимо удалить из обеих баз данных SQL Server и MySQL, тогда строки должны быть удалены из SQL Server с использованием этого подхода, так же, как операторы SELECT, INSERT и UPDATE, мы также можем использовать DELETE в операторе OPENQUERY.Структура запроса будет такой же, как у обычного оператора DELETE для SQL Server, а инструкция OPENQUERY будет такой же, как упомянуто в приведенном выше случае в запросе T-SQL.

УДАЛИТЬ ИЗ ОТКРЫТОГО ЗАПРОСА ([MYSQL_LOCAL],

‘SELECT user_id FROM d_portal.users’

)

где user_id = 1;

Обратите внимание, что в приведенном выше заявлении запрос T-SQL удалит строку в базе данных MySQL, где user_id = 1.Для операторов удаления и обновления оператору SELECT внутри OPENQUERY требуются только те столбцы, которые упомянуты в предложении WHERE для выполнения DELETE или UPDATE.

Это базовые руководства по запросам MySQL в SQL Server, которые помогут нам управлять некоторым уровнем согласованности данных. В противном случае могут возникнуть проблемы с несогласованностью производительности, которые даже не легко выявить в перекрестных базах данных, например — какая часть зависает и почему?

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

Посмотреть все сообщения от Jignesh Raiyani

Последние сообщения от Jignesh Raiyani (посмотреть все)

Отображение запущенных запросов в MySQL

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

Синтаксис прост:

 показать список процессов;
 

, который выведет что-то вроде этих строк:

 + -------- + -------- + ----------- + ------- - + --------- + ------ + ---------------------- + -------- -------------------------------------------------- -------------------------------------------- +
| Id | Пользователь | Хост | db | Команда | Время | Государство | Информация |
+ -------- + -------- + ----------- + -------- + --------- + ------ + ---------------------- + -------------------- -------------------------------------------------- -------------------------------- +
| 708163 | корень | localhost | NULL | Запрос | 0 | NULL | показать список процессов |
| 708174 | тест | localhost | тест | Запрос | 2 | Копирование в таблицу tmp | выберите расст.имя, dist.filename, count (*)
from orders_header h
внутреннее соединение orders_detail d на h.ord |
+ -------- + -------- + ----------- + -------- + --------- + ------ + ---------------------- + -------------------- -------------------------------------------------- -------------------------------- +
2 ряда в наборе (0,00 сек)
 

Столбец «информация» показывает выполняемый запрос m или NULL, если в данный момент ничего не происходит. При запуске «show processlist» будут показаны только первые 100 символов запроса. Чтобы показать полный запрос, вместо этого запустите «показать полный список процессов».

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

 mysql> показать список процессовG

*************************** 6.ряд ***************************
Id: 708163
Пользователь: root
Хост: localhost
db: NULL
Команда: Запрос
Время: 0
Состояние: NULL
Информация: показать список процессов
************************** 7. ряд ******************** *******
Id: 708174
Пользователь: test
Хост: localhost
db: test
Команда: Запрос
Время: 3
Состояние: копирование в таблицу tmp
Информация: выберите dist.name, dist.filename, count (*)
from orders_header h
внутреннее соединение orders_detail d на h.ord
2 строки в наборе (0,00 сек) 

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

Как определить проблемы производительности MySQL с помощью медленных запросов

Проблемы производительности — распространенные проблемы при администрировании баз данных MySQL. Иногда эти проблемы возникают из-за медленных запросов. В этом блоге мы поговорим о медленных запросах и о том, как их идентифицировать.

Проверка журналов медленных запросов

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

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

  MariaDB [(none)]> показать глобальные переменные, такие как 'slow% log%';

+ --------------------- + --------------------------- ---- +

| Имя_переменной | Значение |

+ --------------------- + --------------------------- ---- +

| slow_query_log | ВКЛ |

| slow_query_log_file | /var/log/mysql/mysql-slow.log |

+ --------------------- + --------------------------- ---- +

2 ряда в наборе (0.001 сек)  

Вы должны убедиться, что для переменной slow_query_log установлено значение ON, в то время как slow_query_log_file определяет путь, по которому вам нужно разместить журналы медленных запросов. Если эта переменная не установлена, она будет использовать DATA_DIR вашего каталога данных MySQL.

Переменная slow_query_log сопровождается переменными long_query_time и min_examined_row_limit, которые влияют на то, как работает медленное ведение журнала запросов. По сути, журналы медленных запросов работают как операторы SQL, выполнение которых занимает более long_query_time секунд, а также требует проверки не менее min_examined_row_limit строк.Его можно использовать для поиска запросов, выполнение которых занимает много времени и поэтому является кандидатом на оптимизацию, а затем вы можете использовать внешние инструменты, чтобы предоставить вам отчет, о котором поговорим позже.

По умолчанию административные операторы (ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE и REPAIR TABLE) не попадают в журналы медленных запросов. Для этого вам необходимо включить переменную log_slow_admin_statements.

Список процессов и монитор состояния InnoDB

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

  ПОКАЗАТЬ [ПОЛНЫЙ] СПИСОК ПРОЦЕССОВ;  

или

  ПОКАЗАТЬ СОСТОЯНИЕ INNODB ДВИГАТЕЛЯ \ G  

Если вы используете ClusterControl, вы можете найти его, используя <выберите свой кластер MySQL> → Производительность → Статус InnoDB, как показано ниже,

или с помощью <выберите свой кластер MySQL> → Монитор запросов → Выполнение запросов (которые будут обсуждаться позже) для просмотра активных процессов, точно так же, как SHOW PROCESSLIST работает, но с лучшим контролем запросов.

Анализ запросов MySQL

Журналы медленных запросов покажут вам список запросов, которые были определены как медленные на основе заданных значений в системных переменных, как упоминалось ранее. Определение медленных запросов может отличаться в разных случаях, поскольку в некоторых случаях даже 10-секундный запрос является приемлемым и все же не медленным. Однако, если ваше приложение является OLTP, очень часто 10-секундный или даже 5-секундный запрос является проблемой или вызывает снижение производительности вашей базы данных.Журнал запросов MySQL действительно помогает вам в этом, но этого недостаточно, чтобы открыть файл журнала, поскольку он не дает вам обзора того, что это за запросы, как они выполняются и какова частота их появления. Следовательно, сторонние инструменты могут помочь вам в этом.

pt-запрос-дайджест

Использование Percona Toolkit, который, я могу сказать, наиболее распространенный инструмент администраторов баз данных, заключается в использовании pt-query-digest. pt-query-digest предоставляет вам четкий обзор конкретного отчета, полученного из вашего журнала медленных запросов. Например, этот конкретный отчет показывает чистую перспективу понимания отчетов о медленных запросах в определенном узле:

  # Доступно обновление программного обеспечения:



# 100 мс время пользователя, 100 мс системное время, 29.12M RSS, 242,41M VSZ

# Текущая дата: 3 фев, пн, 20:26:11 2020

# Имя хоста: testnode7

# Файлы: /var/log/mysql/mysql-slow.log

# Всего: 24 всего, 14 уникальных, 0,00 QPS, 0,02x параллелизм ______________

# Диапазон времени: 2019-12-12T10: 01: 16 до 2019-12-12T15: 31: 46

# Атрибут total min max avg 95% stddev median

# ============ ======= ======= ======= ======= ======= == ===== =======

# Exec time 345s 1s 98s 14s 30s 19s 7s

# Время блокировки 1 с 0 1 с 58 мс 24 мс 252 мс 786 мкс

# Отправлено строк 5.72 млн 0 1,91 млн 244,14 тыс. 1,86 млн 629,44 тыс 0

# Проверка строк 15,26 млн 0 1,91 млн 651,23 тыс. 1,86 млн 710,58 тыс. 961,27 тыс.

Количество строк затрагивает 9,54M 0 1,91M 406,90 тыс. 961,27 тыс. 546,96 тыс. 0

# Отправлено байт 305,81M 11 124,83M 12,74M 87,73M 33,48M 56,92

# Размер запроса 1,20k 25 244 51,17 59,77 40,60 38,53



# Профиль

# Рейтинг Запрос ID Время ответа Вызовы R / Call V / M

# ==== ================================ ============= ===== ======= =====

# 1 0x00C8412332B2795DADF0E55C163... 98.0337 28.4% 1 98.0337 0.00 ОБНОВИТЬ sbtest?

# 2 0xDEF289292EA9B2602DC12F70C7A ... 74.1314 21.5% 3 24.7105 6.34 ALTER TABLE sbtest? sbtest3

# 3 0x148D575F62575A20AB9E67E41C3 ... 37.3039 10.8% 6 6.2173 0.23 ВСТАВИТЬ ВЫБОР sbtest? sbtest

# 4 0xD76A930681F1B4CC9F748B4398B ... 32.8019 9.5% 3 10.9340 4.24 ВЫБРАТЬ sbtest?

# 5 0x7B9A47FF6967FD9042DD3B ... 20.6685 6.0% 1 20.6685 0.00 ALTER TABLE sbtest? sbtest3

# 6 0xD1834E96EEFF8AC871D51192D8F ... 19.0787 5.5% 1 19.0787 0.00 СОЗДАТЬ

# 7 0x2112E77F825903ED18028C7EA76 ... 18.7133 5.4% 1 18.7133 0.00 ИЗМЕНИТЬ ТАБЛИЦУ sbtest? sbtest3

# 8 0xC37F2569578627487D948026820 ... 15.0177 4.3% 2 7.5088 0.00 ВСТАВИТЬ ВЫБОР sbtest? sbtest

# 9 0xDE43B2066A66AFA881D6D45C188 ... 13.7180 4.0% 1 13.7180 0.00 ALTER TABLE sbtest? sbtest3

# MISC 0xMISC 15.8605 4,6% 5 3,1721 0,0 <5 ЭЛЕМЕНТОВ>



# Запрос 1: 0 QPS, 0x параллелизм, ID 0x00C8412332B2795DADF0E55C1631626D в байте 5319

# Оценка: V / M = 0.00

# Временной диапазон: все события произошли в 2019-12-12T13: 23: 15

# Атрибут% всего мин. Макс. Средн.95% стандартное отклонение медиана

# ============ === ======= ======= ======= ======= ====== = ======= =======

# Count 4 1

# Exec time 28 98s 98s 98s 98s 98s 0 98s

# Время блокировки 1 25 мс 25 мс 25 мс 25 мс 25 мс 0 25 мс

Количество отправленных строк 0 0 0 0 0 0 0 0

# Ряды исследуемого 12 1.91 млн 1,91 млн 1,91 млн 1,91 млн 1,91 млн 0 1,91 млн

Число затронутых строк 20 1.91M 1.91M 1.91M 1.91M 1.91M 0 1.91M

# Отправлено байт 0 67 67 67 67 67 0 67

# Размер запроса 7 89 89 89 89 89 0 89

# Нить:

# Тест баз данных

# Хосты localhost

# Последняя ошибка 0

# Пользователи root

# Query_time distribution

# 1us

# 10us

# 100us

# 1мс

# 10 мс

# 100 мс

# 1s

# 10s + ################################################################################# ################

# Таблицы

# ПОКАЗАТЬ СТАТУС ТАБЛИЦЫ ИЗ `test` LIKE 'sbtest3' \ G

# ПОКАЗАТЬ СОЗДАТЬ ТАБЛИЦУ `test`.`sbtest3` \ G

обновить sbtest3 set c = substring (MD5 (RAND ()), -16), pad = substring (MD5 (RAND ()), -16) где 1 \ G

# Преобразовано для EXPLAIN

# EXPLAIN / *! 50100 РАЗДЕЛОВ * /

выберите c = substring (MD5 (RAND ()), -16), pad = substring (MD5 (RAND ()), -16) из sbtest3, где 1 \ G



# Запрос 2: 0,00 QPS, 0,01x параллелизм, ID 0xDEF289292EA9B2602DC12F70C7A041A9 в байте 3775

# Оценка: V / M = 6,34

# Временной диапазон: 2019-12-12T12: 41: 47 до 2019-12-12T15: 25: 14

# Атрибут% всего мин. Макс. Средн.95% стандартное отклонение медиана

# ============ === ======= ======= ======= ======= ====== = ======= =======

# Count 12 3

# Exec time 21 74s 6s 36s 25s 35s 13s 30s

# Время блокировки 0 13 мс 1 мс 8 мс 4 мс 8 мс 3 мс 3 мс

Количество отправленных строк 0 0 0 0 0 0 0 0

# Проверка строк 0 0 0 0 0 0 0 0

# Строки влияют 0 0 0 0 0 0 0 0

# Отправлено байт 0 144 44 50 48 49.17 3 49,17

# Размер запроса 8 99 33 33 33 33 0 33

# Нить:

# Тест баз данных

# Хосты localhost

# Последняя ошибка 0 (2/66%), 1317 (1/33%)

# Пользователи root

# Query_time distribution

# 1us

# 10us

# 100us

# 1мс

# 10 мс

# 100 мс

# 1s ###############################

# 10s + ################################################################################# ################

# Таблицы

# ПОКАЗАТЬ СТАТУС ТАБЛИЦЫ ИЗ `test` LIKE 'sbtest3' \ G

# ПОКАЗАТЬ СОЗДАТЬ ТАБЛИЦУ `test`.`sbtest3` \ G

ИЗМЕНИТЬ ТАБЛИЦУ sbtest3 ENGINE = INNODB \ G  

Использование performance_schema

Медленные журналы запросов могут быть проблемой, если у вас нет прямого доступа к файлу, например, с помощью RDS или с помощью полностью управляемых служб баз данных, таких как Google Cloud SQL или Azure SQL.Хотя для включения этих функций могут потребоваться некоторые переменные, они пригодятся при запросе запросов, зарегистрированных в вашей системе. Вы можете заказать его, используя стандартный оператор SQL, чтобы получить частичный результат. Например,

  MySQL> SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000000 SUM_TIMER_WAIT_SEC, MIN_TIMER_WAIT / 1000000000000 MIN_TIMER_WAIT_SEC, AVG_TIMER_WAIT / 1000000000000 AVG_TIMER_WAIT_SEC, MAX_TIMER_WAIT / 1000000000000 MAX_TIMER_WAIT_SEC, SUM_LOCK_TIME / 1000000000000 SUM_LOCK_TIME_SEC, FIRST_SEEN, LAST_SEEN ОТ events_statements_summary_by_digest;

+ -------------------- + ---------------------------- ------ + ------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------- + ------------ + -------------------- + ----- --------------- + -------------------- + ------------- ------- + ------------------- + --------------------- + --------------------- +

| SCHEMA_NAME | ДАЙДЖЕСТ | DIGEST_TEXT | COUNT_STAR | SUM_TIMER_WAIT_SEC | MIN_TIMER_WAIT_SEC | AVG_TIMER_WAIT_SEC | MAX_TIMER_WAIT_SEC | SUM_LOCK_TIME_SEC | FIRST_SEEN | LAST_SEEN |

+ -------------------- + ---------------------------- ------ + ------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------- + ------------ + -------------------- + ----- --------------- + -------------------- + ------------- ------- + ------------------- + --------------------- + --------------------- +

| NULL | 3f3d1f72317dab6deb40322d119 | ВЫБЕРИТЕ @@ `skip_networking`, @@` skip_name_resolve`, @@ `have_ssl` =? , @@ `ssl_key`, @@` ssl_ca`, @@ `ssl_capath`, @@` ssl_cert`, @@ `ssl_cipher`, @@` ssl_crl`, @@ `ssl_crlpath`, @@` tls_version` | 1 | 0.0373 | 0,0373 | 0,0373 | 0,0373 | 0,0000 | 2020-02-03 20:22:54 | 2020-02-03 20:22:54 |

| NULL | fba95d44e3d0a9802dd534c782314352 | ВЫБЕРИТЕ `UNIX_TIMESTAMP` () | 2 | 0.0002 | 0,0001 | 0,0001 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | 18c649da485456d6cdf12e4e6b0350e9 | ВЫБЕРИТЕ @@ GLOBAL. `SERVER_ID` | 2 | 0.0001 | 0,0001 | 0,0001 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | dd356b8a5a6ed0d7aee2abd939cdb6c9 | УСТАНОВЛЕННЫЙ @? знак равно | 6 | 0.0003 | 0,0000 | 0,0001 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | 1c5ae643e930af6d069845d74729760d | УСТАНОВЛЕННЫЙ @? = @@ ГЛОБАЛЬНЫЙ. `binlog_checksum` | 2 | 0.0001 | 0,0001 | 0,0001 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | ad5208ffa004a6ad7e26011b73cbfb4c | ВЫБРАТЬ @? | 2 | 0.0001 | 0,0000 | 0,0000 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | ed0d1eb982c106d4231b816539652907 | ВЫБЕРИТЕ @@ GLOBAL. `GTID_MODE` | 2 | 0.0001 | 0,0000 | 0,0000 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | cb47e22372fdd4441486b02c133fb94f | ВЫБЕРИТЕ @@ GLOBAL. `SERVER_UUID` | 2 | 0.0001 | 0,0000 | 0,0000 | 0,0001 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | 73301368c301db5d2e3db5626a21b647 | ВЫБЕРИТЕ @@ GLOBAL. `rpl_semi_sync_master_enabled` | 2 | 0.0001 | 0,0000 | 0,0000 | 0,0000 | 0,0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL | 0ff7375c5f076ba5c040e78a9250a659 | ВЫБРАТЬ @@ `version_comment` LIMIT? | 1 | 0.0001 | 0,0001 | 0,0001 | 0,0001 | 0,0000 | 2020-02-03 20:45:59 | 2020-02-03 20:45:59 |

| NULL | 5820f411e67a393f987c6be5d81a011d | ПОКАЗАТЬ ТАБЛИЦЫ ИЗ `performance_schema` | 1 | 0.0008 | 0,0008 | 0,0008 | 0,0008 | 0,0002 | 2020-02-03 20:46:11 | 2020-02-03 20:46:11 |

| NULL | a022a0ab966c51eb820da1521349c7ef | ВЫБРАТЬ СХЕМУ () | 1 | 0.0005 | 0,0005 | 0,0005 | 0,0005 | 0,0000 | 2020-02-03 20:46:29 | 2020-02-03 20:46:29 |

| performance_schema | e4833a7c1365b0b4492e9a514f7b3bd4 | ПОКАЗАТЬ СХЕМЫ | 1 | 0.1167 | 0.1167 | 0.1167 | 0.1167 | 0,0001 | 2020-02-03 20:46:29 | 2020-02-03 20:46:29 |

| performance_schema | 1107f048fe6d970cb6a553bd4727a1b4 | ПОКАЗАТЬ ТАБЛИЦЫ | 1 | 0.0002 | 0,0002 | 0,0002 | 0,0002 | 0,0000 | 2020-02-03 20:46:29 | 2020-02-03 20:46:29 |

...  

Вы можете использовать таблицу performance_schema.events_statements_summary_by_digest. Хотя есть вероятность, что записи в таблицах из performance_schema будут сброшены, вы можете решить сохранить это в конкретной таблице. Взгляните на этот внешний пост из дайджеста запросов Percona MySQL с Performance Schema.

Если вам интересно, почему нам нужно разделить столбцы времени ожидания (SUM_TIMER_WAIT, MIN_TIMER_WAIT_SEC, AVG_TIMER_WAIT_SEC), эти столбцы используют пикосекунды, поэтому вам может потребоваться выполнить некоторые вычисления или округлить, чтобы сделать его более читаемым.

Анализ медленных запросов с помощью ClusterControl

Если вы используете ClusterControl, есть разные способы справиться с этим. Например, в кластере MariaDB, который я привожу ниже, он показывает вам следующую вкладку (Монитор запросов) и ее раскрывающиеся элементы (Самые популярные запросы, Выполняющиеся запросы, Выбросы запросов):

  • Самые популярные запросы — сводный список всех ваших самых популярных запросов, выполняемых на всех узлах кластера базы данных.
  • Выполняемые запросы — просмотр текущих запущенных запросов в кластере базы данных аналогично команде SHOW FULL PROCESSLIST в MySQL
  • Выбросы запроса — показывает запросы, которые являются выбросами.Выброс — это запрос, который занимает больше времени, чем обычный запрос этого типа.

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

Подождите, это еще не конец. ClusterControl также предлагает метрики с высоким разрешением с помощью Prometheus и демонстрирует очень подробные метрики и собирает статистику в реальном времени с сервера. Мы обсуждали это в наших предыдущих блогах, которые разделены на две части.Просмотрите блоги части 1, а затем части 2. Он предлагает вам, как эффективно отслеживать не только медленные запросы, но и общую производительность серверов баз данных MySQL, MariaDB или Percona.

В ClusterControl есть также другие инструменты, которые предоставляют указатели и подсказки, которые могут вызвать снижение производительности запросов, даже если это еще не произошло или не зафиксировано журналом медленных запросов. Проверьте вкладку «Производительность», как показано ниже,

.

этих предметов дает вам следующее:

  • Обзор — Вы можете просмотреть графики различных счетчиков базы данных на этой странице
  • Advisors — списки запланированных результатов советников, созданные в ClusterControl> Manage> Developer Studio с использованием ClusterControl DSL.
  • Статус БД — Статус БД обеспечивает быстрый обзор статуса MySQL на всех узлах вашей базы данных, аналогично оператору SHOW STATUS
  • Переменные БД — переменные БД предоставляют быстрый обзор переменных MySQL, которые установлены на всех узлах вашей базы данных, аналогично оператору SHOW GLOBAL VARIABLES
  • DB Growth — Предоставляет сводную информацию о ежедневном росте вашей базы данных и таблиц за последние 30 дней.
  • InnoDB Status — Выбирает текущий вывод монитора InnoDB для выбранного хоста, аналогично команде SHOW ENGINE INNODB STATUS.
  • Schema Analyzer — анализирует схемы вашей базы данных на отсутствие первичных ключей, избыточных индексов и таблиц с помощью механизма хранения MyISAM.
  • Журнал транзакций
  • — перечисляет длительные транзакции и взаимоблокировки в кластере базы данных, где вы можете легко просмотреть, какие транзакции вызывают взаимные блокировки. Пороговое значение времени запроса по умолчанию составляет 30 секунд.

Заключение

Отслеживание проблемы с производительностью MySQL не так уж и сложно с MySQL.

Оставить комментарий

Добавить комментарий

Ваш адрес email не будет опубликован.

© 2019 Штирлиц Сеть печатных салонов в Перми

Цифровая печать, цветное и черно-белое копирование документов, сканирование документов, ризография в Перми.