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

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

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

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

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


mysql sql-execution-plan
person Alex Martelli    schedule 25.04.2009    source источник

Ответы (3)


arrow_upward
22
arrow_downward

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

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

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

person Greg    schedule 25.04.2009
comment
В настоящее время мы не генерируем подзапросы, так что это не должно быть проблемой. Но все равно спасибо за подсказку — и за новость о том, что нет хорошего способа оценить стоимость запроса (плохая новость, но лучше узнать, прежде чем тратить неограниченное время на погоню за химерой!). - person Alex Martelli; 26.04.2009
comment
EXPLAIN чрезвычайно полезен. Я не уверен, почему это «ответ». Проверьте кардинальность — чем больше строк, тем больше нужно выполнить поиск. Кроме того, он показывает, какие индексы используются. Это критически важно для производительности SELECT. Что касается подзапросов, то они очень редко действительно нужны — для ясности их следует, по возможности, реорганизовать. - person Adam Nelson; 05.12.2009

arrow_upward
11
arrow_downward

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

person chaos    schedule 25.04.2009

arrow_upward
3
arrow_downward

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

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

Однако

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

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

Заключение

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

Подробнее о наборе результатов оператора MySQL EXPLAIN

person firelynx    schedule 09.11.2015