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

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 -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 строку запроса
• Размер ответа

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

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

• 5/12/2020
• Чтение занимает 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 уникальных запросов на период.

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

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

Примечание

В настоящее время 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

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

mysql.query_store_wait_stats

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

Функции

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

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

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

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

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

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

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

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

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

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

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

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

Для получения данных из таблиц в СУБД используется специальная команда 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

• 12.05.2020
• 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:

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

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

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

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

Примечание

В настоящее время 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

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

MySQL.query_store_wait_stats

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

Функции

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

• Если на сервере 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 собирается структурировать ваш запрос и что каждый из этих потенциальных индексов может или не может покрыть.

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

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

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

 + -------- + -------- + ----------- + ------- - + --------- + ------ + ---------------------- + -------- -------------------------------------------------- -------------------------------------------- +
| 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 сек)
 

 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 сек) 

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

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

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

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

| slow_query_log | ВКЛ |

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

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

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

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

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

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



# 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  

  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 |

...