Как использовать EXPLAIN для * прогнозирования производительности MySQL-запроса?

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

Я хотел бы добавить некоторую форму разумного/эвристического предсказания для ожидаемой производительности построенного запроса - иногда пользователи непреднамеренно делают запросы, которые неизбежно будут длиться очень долго (потому что они вернут огромные наборы результатов или потому что они "идут против зерна" того, как индексируется БД), и я хотел бы показать пользователю некоторую "несколько надежную" информацию/угадать, сколько времени займет запрос. Он не должен быть совершенным, если он не становится настолько плохим и часто из-за суеты с реальностью, что вызывает эффект "вопля волка", когда пользователи учатся его игнорировать;-) Основываясь на этой информации, пользователь может решить пойти на кофе (если оценка составляет 5-10 минут), пойдите на обед (если это 30-60 минут), убейте запрос и попробуйте что-нибудь другое (возможно, более жесткие ограничения на информацию, которую они запрашивают ) и т.д. и т.д.

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

Любые указатели...?

Ответ 1

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

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

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

Ответ 2

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

Ответ 3

MySQL EXPLAIN имеет столбец с именем Key. Если в этом столбце есть что-то, это очень хороший признак, это означает, что запрос будет использовать индекс.

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

Однако

Существует другое поле под названием Extra. Это поле иногда содержит текст using_filesort.

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

Заключение

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

Подробнее о результатах инструкции MySQL EXPLAIN