Уважаемые, доброго дня!У меня свой самописный "фреймворк" назовём его так, и там соответственно свой класс для работы с БД MySQL, средства отладки и прочие личные хотелки.
Так вот, я хочу иметь возможность все генерируемые SQL запросы при включении дебага - чтобы автоматом проверялись на всякие SQL минусы. Типа того, что там например индексы не используются, или временная таблица на диске создалась... Я на самом деле просто в глубинах SQL не так силён, но хочу так сказать погрузиться.
Что у вас хочу спросить: Посоветуйте пожалуйста, как можно анализировать запрос, ну т.е. к примеру всем проходящим через обёртку работы с БД запросам SELECT перед их исполнением делать EXPLAIN и там анализировать то-то и то-то, и в случае таких-то проблем сигнализировать, что данный запрос потенциально "проблемный". Какие есть способы диагностики, может кроме EXPLAIN'а, с ним я немного знаком. Может там на уровне самой БД что-то можно включить, я смогу это связать со своим "конструктором".
> Уважаемые, доброго дня!
> У меня свой самописный "фреймворк" назовём его так, и там соответственно свой
> класс для работы с БД MySQL, средства отладки и прочие личные
> хотелки.
> Так вот, я хочу иметь возможность все генерируемые SQL запросы при включении
> дебага - чтобы автоматом проверялись на всякие SQL минусы. Типа того,
> что там например индексы не используются, или временная таблица на диске
> создалась... Я на самом деле просто в глубинах SQL не так
> силён, но хочу так сказать погрузиться.Тоесть вы хотите самостоятельно, повторно, анализировать план запроса который сгенерирует вам оптимизатор SQL сервера...
Тут есть два момента - прочитать как работает оптимизаптор.. хотя бы в том же оракле...
в мускуле он тоже примерно так же, по смыслу, но описание ораклового есть, а описание мускульного- незнаю, обычно посылают в исходники.. что как понимаете не одно и то же.. :)
и подумать, сильно ли вы компетентнее программистов написавших оптимизатор... раз беретесь поправлять их...И второе - использование индексов оправдано только в некоторых случаях. Есть много случаев когда использование индекса делает запрос медленнее... сююююрпрааааайзззззз :)))
Временная таблица на диске создается только если она нужна. Просто так она не создается. Проверьте... :)
> Что у вас хочу спросить: Посоветуйте пожалуйста, как можно анализировать запрос, ну
> т.е. к примеру всем проходящим через обёртку работы с БД запросам
> SELECT перед их исполнением делать EXPLAIN и там анализировать то-то и
> то-то, и в случае таких-то проблем сигнализировать, что данный запрос потенциально
> "проблемный". Какие есть способы диагностики, может кроме EXPLAIN'а, с ним я
> немного знаком. Может там на уровне самой БД что-то можно включить,
> я смогу это связать со своим "конструктором".можно. можно включить трейс работы оптимизатора и получить простыню на несколько гигабайт... но без багажа знаний хотябы на уровне DBA в крупной конторе, по-моему он будет не сильно полезен вам...
> можно. можно включить трейс работы оптимизатора и получить простыню на несколько гигабайт...
> но без багажа знаний хотябы на уровне DBA в крупной
> конторе, по-моему он будет не сильно полезен вам...Нет, я не про то, что сервер БД чего-то делает не так, а про то, что мои SQL запросы написаны неоптимально. Чтобы какую-то диагностику своих запросов от сервера получать и автоматом на какие-то критерии чтобы он мне сигнализировал, что типа вот запрос - который по таким-то метрикам выглядит как неоптимально составленный.
>> можно. можно включить трейс работы оптимизатора и получить простыню на несколько гигабайт...
>> но без багажа знаний хотябы на уровне DBA в крупной
>> конторе, по-моему он будет не сильно полезен вам...
> Нет, я не про то, что сервер БД чего-то делает не так,
> а про то, что мои SQL запросы написаны неоптимально. Чтобы какую-то
> диагностику своих запросов от сервера получать и автоматом на какие-то критерии
> чтобы он мне сигнализировал, что типа вот запрос - который по
> таким-то метрикам выглядит как неоптимально составленный.Сервер не знает что вы хотите. И не умеет угадывать ваши желания.
> Сервер не знает что вы хотите. И не умеет угадывать ваши желания.Так я же не прошу и не собираюсь просить его об этом.
Вот к примеру в выводе explain'а - можно отслеживать ситуацию когда filesort и куча строк и от этого момента анализировать что не так с запросом.
А в переменных состояния MySQL - есть те, которые лучше бы не увеличивались и не появлялись. Так вот оперируя такого рода информацией, без необходимости кого-то просить что-то предугадать и т.п. - какие есть пути?
> от этого момента анализировать что не так с запросом.Кто будет это делать?
> Кто будет это делать?Так как кто? Сперва мой анализатор-дебаггер который я встрою в класс через который выполняются в моём приложении все запросы. А дальше уже из этого дебаггера при нахождении каких-то аномалий (запрограммированное мной срабатывание на анализируемые параметры) из этого класса себе буду сигнализировать, что вот тут сработка, и буду глядеть - разбирать.
Т.е.public function _QUERY(string $sql, bool $isDebug)?resource {
//Если нужен дебаг, то запускаем всё что для него надо
if($isDebug){
$this->_PRE_DEBUG($sql);
}//Затем выполняем сам запрос
$result = $this->DBLink->($sql);//Выполнение если нужен дебаг каких-то действий после выполнения запроса
$this->_POST_DEBUG();//Возврат результата запроса
return $result;
}protected function _PRE_DEBUG(string $sql){
//Делаем EXPLAIN если это SELECT и обрабатываем
if(substr(strtolower(trim($sql)), 0, 6) == 'select'){
//Добавляем EXPLAIN, обрабатываем данные из него, всё что требуется сигнализируем в лог для разработчика
$sql='EXPLAIN '.$sql;
.......
.......
}protected function _POST_DEBUG(){
//Тут выполняем какие-то действия для дебага которые требуются уже после выполнения запроса
//типа запрашиваем данные по переменным MySQL и сравниваем их с теми, что были получены
//перед выполнением запроса, получаем их дельту и типа что-то если является триггером -
//то сигнализируем об этом в дебаг для разработчика.
}
}
Т.е. напрограммировать получение требуемых данных и сформировать логику всего этого не проблема, проблема только в том, чтобы узнать - какие данные и метрики и у кого запрашивать.
>[оверквотинг удален]
> их с теми, что были получены
> //перед выполнением запроса, получаем их дельту и типа
> что-то если является триггером -
> //то сигнализируем об этом в дебаг для разработчика.
> }
> }
>
какие данные и метрики и у кого запрашивать станет понятно как только вы накидаете логику всего этого.
Пока вы не накидали логику- нет никакой возможности определить какие метрики и данные вам нужны.
Вы похожи на человека, который требует в железнодорожной кассе билет, наотрез отказываясь говорить куда же вам надо.
Ну... вот вам метрики: https://www.datadoghq.com/blog/collecting-mysql-statistics-a.../
или вот: https://www.opennet.dev/docs/RUS/mysqladm/admin.html
используйте их не стесняясь :)
> Вы похожи на человека, который требует в железнодорожной кассе билет, наотрез отказываясь
> говорить куда же вам надо.
> Ну... вот вам метрики: https://www.datadoghq.com/blog/collecting-mysql-statistics-a.../
> или вот: https://www.opennet.dev/docs/RUS/mysqladm/admin.html
> используйте их не стесняясь :)За performance_schema - спасибо! новенькое, не знал о такой штуке, пощупаю что такое.
А по поводу билетных касс - чтобы проще объяснить, вот к примеру обращаются к спецу для решения проблем с медленной работой БД в приложении, он что анализирует - на основе каких данных, вот я собственно это и спрашиваю. Т.е. что "щупать", чтобы выявлять аномалии в показателях и дальше уже на основе этих данных устранять причины.За ссылки спасибо, если есть ещё полезные заметки - буду благодарен!
Раньше с такими вопросами на sql.ru обращался, но он тут что-то уже месяца полтора как на тех.обслуживании, и ищу ответов тут.
Начиная с какого-то уровня сложности выборок нельзя просто так взять и сказать, что запрос неоптимален. Вы хотите искусственный интеллект изобрести.
> Начиная с какого-то уровня сложности выборок нельзя просто так взять и сказать,
> что запрос неоптимален. Вы хотите искусственный интеллект изобрести.Это понятно что серебряной пули быть не может, но хотя бы имеющиеся инструменты какие имеет смысл использовать?
Делать EXPLAIN на каждый чих - тупая тема, тебе же эти километровые портянки читать придётся.Пиши запросы и времена исполнения, потом отсортируешь и вручную сделаешь EXPLAIN для подозрительных.
В PostgreSQL есть забавная фишка - log_min_duration_statement, чтобы писать в лог только то, что тормозит.
> Делать EXPLAIN на каждый чих - тупая тема, тебе же эти километровые
> портянки читать придётся.
> Пиши запросы и времена исполнения, потом отсортируешь и вручную сделаешь EXPLAIN для
> подозрительных.Это у меня ведётся, хочется не дожидаясь тормозов на более крупных набоах данных - исправлять принципиальные проблемы в запросах.
> В PostgreSQL есть забавная фишка - log_min_duration_statement, чтобы писать в лог только
> то, что тормозит.В MySQL тоже похожий параметр есть, типа вывод в лог всех запросов у которых время исполнения более указанного.
Еще раз, использование индексов и промежуточные таблицы на диске не означают, что запрос автоматически плохой. Коррелированные подзапросы могут работать быстрее кроилова из джойнов.
Какой-то запрос может работать медленно, но это лучше, чем обвешивать индексами таблицу, много работающую на запись.
Анекдот про нюансы, в общем.
> Еще раз, использование индексов и промежуточные таблицы на диске не означают, что
> запрос автоматически плохой. Коррелированные подзапросы могут работать быстрее кроилова
> из джойнов.
> Какой-то запрос может работать медленно, но это лучше, чем обвешивать индексами таблицу,
> много работающую на запись.
> Анекдот про нюансы, в общем.Это я знаю, что хуже отсутствия индексов только их избыток :) А анекдот не знаю, прочитаю с удовольствием :)
> анекдот не знаю, прочитаю с удовольствием :)https://www.anekdot.ru/id/-2062519010/
Короче, оптимизируй то, что нужно. Но не более.
>> анекдот не знаю, прочитаю с удовольствием :)
> https://www.anekdot.ru/id/-2062519010/Нормальный анекдот :)
> Короче, оптимизируй то, что нужно. Но не более.
Ок, учту, спасибо!
:D :D :D