MySQL. Триггер для сбора статистики по медленным запросам.

GetFieldType(i).Name;if (dGVSchemaTable.RowCount < dataReader.FieldCount)dGVSchemaTable.RowCount += 1;dGVSchemaTable.Rows[i].Cells[0].Value = dataReader.GetName(i);dGVSchemaTable.Rows[i].Cells[1].Value = dataReader.GetFieldType(i).Name; }// ЗакрываемобъектdataReaderdataReader.Close(); }catch (Exception ex) {MessageBox.Show(ex.Message);}} }При выборе таблицы в списке названий таблиц во вторую таблицу выводятся названия её полей и их типы данных:privatevoiddGVTables_CellClick(object sender, DataGridViewCellEventArgs e) {int k = dGVTables.CurrentCell.RowIndex;//запоминаеминдексстрокиstring s = dGVTables.Rows[k].Cells[0].Value.ToString();MySqlConnection con = newMySqlConnection(connStr);try {using (con) {//подключаемсякбазеcon.Open();MySqlCommandcmd;//загружаемданныеcmd = newMySqlCommand("SELECT * FROM " + s, con);DbDataReaderdataReader = cmd.ExecuteReader();// ОчищаемтаблицудлявыводданныхdGVSchemaTable.Rows.Clear();dGVSchemaTable.RowCount = 1;// FieldCount - количество столбцов в таблице, из которой считываются данныеfor (inti = 0; i < dataReader.FieldCount; i++) // перебратьвсеполя{// Вывести имя поля и тип данныхstringstr = dataReader.GetFieldType(i).Name;if (dGVSchemaTable.RowCount < dataReader.FieldCount)dGVSchemaTable.RowCount += 1;dGVSchemaTable.Rows[i].Cells[0].Value = dataReader.GetName(i); dGVSchemaTable.Rows[i].Cells[1].Value = dataReader.GetFieldType(i).Name; }// ЗакрываемобъектdataReaderdataReader.Close(); } }catch (Exception ex) {MessageBox.Show(ex.Message);}} }Разработанное приложение имеет следующий интерфейс при подключенной базе данных: окно приложения содержит поле для ввода запроса, поле для ввода порога длительности выполнения запроса, справа выводится список таблиц подключенной базы данных, нижеимеется список полей с типами данных, список обновляется в соответствиис выбранной в списке таблицей (рисунок 6).Рисунок 6 – Окно работающей программы, подключенной к базе данных.Кнопка «Выполнить запрос» (событие Clickкнопки Button1) отвечает за выполнение введенного в окне запроса и сохранение данных о нём в файле лога. Поскольку нас не интересует вся статистика, а только те запросы, которые выполняются слишком долго, то можно указать порог ожидания выполнения запроса (в миллисекундах). Тогда в лог-файл будут сохраняться данные только по тем запросам, которые превысили порог хотя бы на одну миллисекунду.Подпрограмма выполняет подсчет времени, затраченного на выполнение запроса, начиная с нажатия кнопки «Выполнить запрос» и до возврата результата с сервера. Если время превышает заданный в поле Порог лимит, то результат уходит в лог-файл, иначе выводится только сообщение о длительности запроса. При вызове подпрограммы включается счётчик времени:Stopwatch stopwatch = newStopwatch();stopwatch.Start();Затем приложение устанавливает связь с сервером по созданному подключению базой данных,считывает запрос, который содержится в поле QueryTxtBox, выполняет его, получает ответ и закрывает соединение:MySqlConnection conn = newMySqlConnection(connStr);try {// устанавливаем соединение с БДconn.Open();// запросstringsql = QueryTxtBox.Text;// объектдлявыполненияSQL-запросаMySqlCommand command = newMySqlCommand(sql, conn);// выполняемзапросиполучаемответstring name = command.ExecuteScalar().ToString();// закрываем соединение с БДconn.Close();После этого счётчик времени останавливается:stopwatch.Stop();На экран выводится сообщение о длительности выполненного запроса:MessageBox.Show("Длительность выполнения запроса: " + stopwatch.ElapsedMilliseconds.ToString() + " миллисекунд");и, если длительность выполнения запроса превышает порог, указанный в поле Slow_long_max_txt, то происходит запись данных о запросе в лог-файл:if (stopwatch.ElapsedMilliseconds > Convert.ToInt32(Slow_long_max_txt.Text)){using (StreamWriter writer = newStreamWriter("SlowQueryLog.txt", true, Encoding.Default)) {writer.Write("Database:\n");writer.Write(dbName);writer.Write("\n");writer.Write("Query Text:\n");writer.Write(sql);writer.Write("\n");writer.Write("Time: \n");writer.Write(stopwatch.ElapsedMilliseconds.ToString());writer.Write("\n");writer.Write("Datetime: ");writer.Write(DateTime.Now.ToString() + "\n");writer.Close();//закрываемлог } }Сохранение вфайл происходит без участия пользователя, данные дописываются в файл лога запросов и хранят название базы данных, текст запроса, его длительность и дату и время выполнения запроса в приложении. Файл лога хранится в папке с исполняемым файлом приложения и может быть просмотрен при помощи любого текстового редактора типа Блокнота. Кроме того, запись данных в отдельных строках позволяет затем этот файл экспортировать в другие приложения, например, Excel, для дальнейшей обработки.2.4 Тестирование приложенияПроверим, какое время занимает выполнение запросов к базе данных при помощи профилирования средствами сервера MySQL. Для этого напишем скрипт, включающий профилирование, добавим в него запросы к тестовой базе данных и запустим (рисунок 7).Рисунок 7 – Профилирование средствами MySQLКолонка Durationотражает время потраченное сервером на выполнение запросов. Можно видеть, что выдается результат для всех выполнявшихся на сервере запросах, даже ошибочных.Командаshowprofileforquery 15;позволяет вывести детальную информацию по последнему выполненному запросу (рисунок 8).Благодаря этой команде можно выделить процесс, на котором теряется больше всего времени при выполнении запроса.Теперь протестируем выполнение этих же запросов в разработанном приложении.Рисунок 8 – Детальная статистика выполнения запроса.Для этого поочередно выполнимзапросы и сравним полученные результаты.Выполнимпервыйзапрос:SELECT * FROM products WHERE product_name LIKE '%кресл%'AND price>=1000;Выполнение запроса заняло 45 миллисекунд (рисунок 9). Этот же запрос при профилировании дал результат в 26 миллисекунд.Рисунок 9 – Выполнение первого тестового запроса.Проверим остальные запросы, выполнив их в разработанном приложении. В результате тестирования разработанного приложения получили следующие результаты:ЗапросВремя в приложенииВремя в MySQLWorkbenchSELECT * FROM products WHERE product_name LIKE '%кресл%'AND price>=1000;4526SELECT customer_name,product_name,product_count*price AS summa FROM orders, customers,products WHERE customer_id=customers.id AND product_id=products.id;179SELECT * FROM customers WHERE customer_name LIKE '%Алекс%';10.5SELECT customer_name, product_name, product_count*price AS summa FROM orders, customers, products WHERE customer_id=customers.id AND product_id=products.id AND product_name LIKE '%Н%' AND customer_name LIKE '%лекс%';51SELECT customer_name,product_name,summa FROM (SELECT customer_name,product_name,product_count*price AS summa FROM orders, customers,products WHERE customer_id=customers.id AND product_id=products.id) AS t WHERE t.customer_name LIKE 'D%';41В результате проведенного тестирования в лог-файл были занесены в соответствии с порогом на длительность выполнения только первые два запроса (рисунок 10).Рисунок 10 – Лог-файл с занесенными в него запросами, превышающими заданный порог на время выполнения запросов.Полученные результаты являются сопоставимыми, поскольку увеличение времени выполнения запроса в приложении может быть связано с затратами на выполнение команд приложения. Тем не менее, приложение верно определяет медленные запросы.ЗаключениеВ результате выполнения курсовой работы были разработаны тестовая база данных с большим количеством записей для тестирования приложения, а также приложение, позволяющее сохранять время выполнения запросов к заданной базе данных во внешнем файле.Были выполнены все поставленные задачи, включая изучение основных принципов работы с базами данных MySQL, создание базы данных с необходимыми таблицами, а также разработку приложения для сбора статистики по медленным запросам. В ходе работы была реализована проверка подключения к базе данных, а также возможность просмотра имеющихся в базе данных таблиц, выполнения запросов и оценка скорости их выполнения. Полученные результаты позволяют заключить, что приложение по сбору статистики медленных запросовдля баз данных MySQL успешно разработано и выполняет все поставленные задачи. Приложение может быть использовано для тестирования баз данных MySQL на различных этапах разработки и обеспечивает корректные результаты.Список использованной литературыMySQL. Workbench. Проектируем БД. Теория и практика. [Электронный ресурс] URL: http://digital-flame.ru/2016/02/22/mysql-workbench-proektiruem-bd-teoriya-i-praktika/NavicatMonitor. [Электронный ресурс] – Режим доступа: https://www.navicat.com/en/products/navicat-monitor4.9.5 Журнал медленных запросов //Справочное руководство по MySQL. [Электронный ресурс] – Режим доступа: http://www.mysql.ru/docs/man/Slow_query_log.htmlГарсиа-Молина, Гектор. Системы баз данных : Полный курс / Гектор Гарсиа-Молина, Джеффри Д. Ульман, Дженнифер Уидом ; [Пер. с англ. и ред. А.С. Варакина]. – М.: Издательство «Вильямс», 2017 г. – 1088 с.Грабер, Мартин. SQL для простых смертных. – М.: Издательство «ЛОРИ», 2020 г. – 389 с.Кузнецов М.В. MySQL 5 / М.В. Кузнецов, И.В Симдянов. – СПб.: БХВ-Петербург, 2010. – 1024 с.Куликов, С. C. Работа с MySQL, MS SQL Server и Oracle в примерах :практ. пособие / С. С. Куликов. – 2-е изд. – Минск : Четыре четверти, 2021. – 600 с.Медленные запросы в базу, как оптимизировать?[Электронный ресурс] – Режим доступа: https://qaa-engineer.ru/medlennye-zaprosy-v-bazu-kak-optimizirovat/Обзор Percona и сопутствующих инструментов.[Электронный ресурс] – Режим доступа: https://xakep.ru/2014/09/09/percona-review/ https://www.itsumma.ru/blog/obzor-perkona-i-soputstvuyuschikh-instrumentovПавловская Т. А. С#. Программирование на языке высокого уровня. Учебник для вузов. – СПб.: Питер, 2014. – 432 с: ил.Профилирование запросов MySQL. [Электронный ресурс] – Режим доступа: https://www.8host.com/blog/profilirovanie-zaprosov-mysql/Ткаченко В. MySQL по максимуму. / В. Ткаченко, Б. Шварц, П. Зайцев. – Санкт-Петербург: Питер, 2018. 864 с. ЧумакДмитрий. Основы работы с Percona. [Электронный ресурс] – Режим доступа: https://xakep.ru/2014/09/09/percona-review/Приложение 1Скрипт создания тестовой базы данныхCREATE DATABASE IF NOT EXISTS `testdb` /*!40100 DEFAULT CHARACTER SET cp1251 */ /*!80016 DEFAULT ENCRYPTION='N' */;USE `testdb`;CREATE TABLE IF NOT EXISTS `customers` ( `id` bigint NOT NULL AUTO_INCREMENT, `customer_name` varchar(50) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=655 DEFAULT CHARSET=cp1251;CREATE TABLE IF NOT EXISTS `orders` ( `id` bigint NOT NULL AUTO_INCREMENT, `product_id` bigint NOT NULL DEFAULT '0', `customer_id` bigint NOT NULL DEFAULT '0', `product_count` int NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `FK_orders_product` (`product_id`), KEY `FK_orders_customer` (`customer_id`), CONSTRAINT `FK_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`), CONSTRAINT `FK_orders_product` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1570 DEFAULT CHARSET=cp1251;CREATE TABLE IF NOT EXISTS `products` ( `id` bigint NOT NULL AUTO_INCREMENT, `product_name` varchar(100) NOT NULL DEFAULT '0', `price` decimal(12,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=61745 DEFAULT CHARSET=cp1251;