Оператор PDO занимает в 400 раз больше времени, чем MySQL CLI
Я запускаю подготовленный оператор PDO для выбора из таблицы примерно из 6 тыс. Строк. Этот конкретный запрос возвращает все строки из-за оператора WHERE, который имеет ~ 5k pId. Таблица также имеет индекс по столбцу pId.
SELECT * FROM table_a WHERE pId in (?, ? ,? ....)
Для выполнения этого запроса на php требуется 4,5 секунды, а при выполнении в интерфейсе командной строки MySQL - 0,01 секунды. Оператор EXPLAIN для PHP и MySQL одинаков, оба НЕТ используют индекс на pId. Я думаю, это связано с тем, что MySQL знает, что возвращает всю таблицу и не нуждается в индексе.
Я знаю, что с подготовленными операторами есть некоторые накладные расходы, но я запускаю очень похожий запрос (другое имя таблицы) в другом месте, и он не занимает столько времени (~ 0,9 секунды). Любые идеи?
Версия PHP: 5.5
Версия MySql: 5.6
PDO должен установить соединение для выполнения операций. Установите для пула значение true и попробуйте выполнить этот оператор несколько раз.
Это быстрее с EMULATE_PREPARES?
Вы используете подготовленные операторы в интерфейсе командной строки?
Я сомневаюсь, что входит в 4,5 секунды. Это измеряется только у execute. Включает ли это время для звонков bindValue? Звонок prepare? Включает ли это время для вызовов bindResult и время для выборки и обработки строк? Использует ли PDO подготовку на стороне сервера или эмулирует подготовку на стороне клиента? Для сравнения, сколько времени, когда значения включаются в текст SQL без вызовов bindValue? Кроме того, каково время выполнения CLI, когда кеш запроса не задействован SELECT SQL_NO_CACHE * FROM?
@bassxzero Я не знаю, я не знал, что могу использовать подготовленные операторы в CLI, но даже никогда не смотрел. Я знаю, что выполнение команды SQL со значениями, уже содержащимися в операторе «where», будет быстрее, но опять же в 400 раз быстрее кажется безумием. Также я запускаю идентичный запрос только к другой таблице, и это занимает четверть времени в PHP.
@shreddish ~ Подготовка 6k идентификаторов - нетривиальная задача, и я готов поспорить, что именно поэтому ваш запрос такой медленный. Однако вы не узнаете наверняка, пока не протестируете его. Прямо сейчас кажется, что вы сравниваете яблоки с апельсинами. dev.mysql.com/doc/refman/8.0/en/…
@ spencer7593 Я попробую получить более детальное профилирование PDO. Прямо сейчас я вызываю функцию-оболочку, чтобы все мои операторы PDO были сгруппированы вместе. Что касается подготовки к эмуляции, для него установлено значение false.
@bassxzero имеет смысл, что подготовка этого оператора будет иметь некоторые накладные расходы, но почему бы мне не увидеть эти накладные расходы в идентичном подготовленном запросе только в другой таблице?
@shreddish Это снова, вероятно, проблема яблок с апельсинами. Обе таблицы имеют одинаковое количество строк, запрашиваете ли вы обе таблицы по индексу, обе таблицы разделены одинаково и т. д.?
@bassxzero да, другая таблица проиндексирована по pId и имеет около 5,5 тыс. строк и разделена одинаково
Возможно, вам что-то не хватает. Вы никогда не узнаете, пока не испытаете его, приятель. Я бы попробовал тот же подготовленный запрос в CLI и посмотрел, изменится ли время.
@ spencer7593, вы что-то поняли, я могу объединить эти 4,5 секунды со временем выполнения запроса. Кажется, что PDO fetchAll занимает ~ 90% времени для этого запроса, а также для остальных моих запросов. Почему fetchAll будет намного длиннее в этом запросе по сравнению с «идентичным» запросом и с очень похожей длиной результата?
@bassxzero, похоже, ты тоже кое-что понял, ха-ха, мой подход к созданию этих подготовленных операторов был несовершенным с точки зрения отдела эффективности. Вместо того, чтобы создавать ~ 6k значений в операторе «where», я изменил его на одно значение и развернул свой массив pIds для подготовки. Это ЗНАЧИТЕЛЬНО сократило время, спасибо!
@shreddish "свернул мой массив pIds" - это приведет к чему-то вроде WHERE pid IN ('3,6,8,...'), который затем будет преобразован в INT как 3. Конечно, он будет работать быстрее. Но получаете ли вы такой же результат? :-)
@PaulSpiegel, ха-ха, как раз собирался исправить себя, был слишком взволнован результатом и даже не потрудился проверить результат ... Я не получаю того же результата. Есть ли способ сделать что-то подобное, чтобы избежать подготовки значений ~ 6k?
Возникла проблема с PDO. Вот связанный с этим нерешенный вопрос: stackoverflow.com/questions/4350718/… .. Понятно - вы это уже нашли.
@PaulSpiegel Ох уж совсем забыл об этом ... Это продолжающийся процесс оптимизации, который уже несколько месяцев откладывается на задний план, и, погрузившись в него, я явно забыл о своих предыдущих исследованиях. Отметить как дубликат или удалить это?
Не удаляйте это. Дубликат - это нормально, если вы думаете, что это та же проблема. Другой вопрос хорошо описывает проблему. Также рассмотрите возможность публикации отчета об ошибке.
@PaulSpiegel, вы довольно активно ответили на другой вопрос. Есть ли у вас какие-либо рекомендации по решению этой проблемы? Я попытался удалить предложение IN и использовал WHERE pId =? ИЛИ pId =? ... ', но время не улучшилось. Кажется, что единственные другие варианты, если я хочу сохранить подготовленные операторы, - это переключиться на mysqli или разбить мои запросы.
@shreddish Вот связанный отчет об ошибке: bugs.php.net/bug.php?id=53458
@shreddish Обходной путь: если идентификаторы поступают из БД - используйте JOIN или IN (<subquery>). Если нет - сначала сохраните их во временной таблице.
Ответы 1
Я подозреваю, что медлительность связана с получением строк, количеством возвращаемых строк, а не с 5000+ заполнителями привязки в инструкции. pId IN ( ? , ? , ... , ? )
Мое предложение состояло в том, чтобы протестировать возврат только одной строки, предоставить одно значение, которое, как известно, существует / вернуть строку, а затем 4999+ значений, которые, как известно, не существуют / не возвращают строку.
Например, если мы знаем наивысшее значение pId в таблице, используйте значения выше этого, укажите значения привязки для такого оператора
... pId IN ( ? , ? , ? , ... , ? )
поэтому результат будет эквивалентен запуску
... pId IN ( 99999999 , 99999998 , 99999997 , ... , 42 )
что будет тем же результатом, что и мы
... pId IN ( 42 )
Мы ожидаем вернуть только одну строку (pId = 42).
Затем сравните время этого (5000+ значений привязки, возвращающих 1 строку) с двумя значениями привязки, возвращающими одну строку
... pId IN ( 99999999 , 42 )
И посмотрите, есть ли существенная разница в производительности.
(С более чем 5000 значений привязки предстоит еще поработать, но я бы не ожидал разницы в огромный, но ее следует протестировать.
Немного подумав, может быть проще настроить тест, используя все существующие значения привязки и просто добавив LIMIT 2 в конец запроса. (Я не уверен, есть ли у MySQL улучшения производительности для LIMIT 2.
Может быть, лучше добавить условие вроде AND pId * 10 = 420
Цель состоит в том, чтобы предоставить множество значений привязки, но вернуть только одну или две строки.
Другой тест - вернуть множество строк, но с использованием только пары значений привязки. Может быть, условие диапазона, которое возвращает 5000+ строк.
Запрос может быть таким:
... pId >= ? AND pId <= ?
с достаточно большим диапазоном между предоставленными значениями, которые мы получаем в районе 5000 строк.
И сравните производительность.
Мой прогноз (предположение?) Заключается в том, что производительность будет больше коррелировать с количеством возвращаемых строк, а не с количеством значений привязки.
Я не уверен, что это ответ на ваш вопрос, но это подход, который я бы выбрал, чтобы ответить на вопрос ... «что вызывает медленную работу, количество значений привязки или количество возвращаемых строк? "
"что заставляет это быть медленным, количество значений привязки или количество возвращаемых строк?" - Это и то, и другое - таким образом, O (n²)
@PaulSpiegel: будет подтверждено некоторым тестированием ... я подозреваю, что количество значений привязки не так сильно влияет на общее прошедшее время. Тесты, которые я рекомендую в этом ответе, должны указывать, какой фактор (количество значений привязки, количество или возвращаемых строк) действительно влияет на прошедшее время. (Я предполагаю, что значения привязки предоставляются с bindValue, а не с bindParam, или, может быть, это не имеет значения.)
Мой комментарий основан на тестах. Выполните глупый тестовый запрос вроде select * from t where id in (?, .. , ?) or 1=1 limit $numRows. Начните с 1000 параметров и ограничьте 1000. Затем увеличьте количество параметров. Вы увидите увеличение времени выборки с тем же набором результатов.
@PaulSpiegel: а тесты проводятся с использованием bindValue, а не bindParam, верно?
Они выполняются с использованием exec($params). Но как вы думаете, почему это имеет значение? Большую часть времени проводят с fetch() или fetchAll().
pIds, используемый в предложенииin, был получен из другого запроса? Если да, то можно переписать с помощьюexists