При использовании SELECT * FROM table WHERE Id IN ( .. ) запрашивает более 10000 ключей с использованием PDO с помощью команды prepare () / execute (), производительность ухудшает ~ 10X больше, чем выполнение одного и того же запроса с использованием mysqli с подготовленными операторами или PDO без использования подготовленные заявления.
Более странные детали:
Более типичные операторы SELECT, у которых нет WHERE Id IN( ..) , выполняются отлично даже при 100K + строках. SELECT * FROM table WHERE Id например, быстро.
Деградация производительности происходит после завершения подготовки () / execute () – это полностью в PDOStatement::fetch() или PDOStatement::fetchAll() . Время выполнения запросов MySQL является крошечным во всех случаях – это не случай оптимизации MySQL.
Выполнение разбиения 10K-запроса на 10 запросов с помощью клавиш 1K.
Использование mysql, mysqli с подготовленными инструкциями или PDO без подготовленных операторов является перформативным.
PDO с приготовлением занимает примерно 6 секунд в примере ниже, в то время как другие принимают ~ 0,5 с.
Ухудшается нелинейно, чем больше у вас ключей. Попробуйте ключи 100K.
Образец кода:
// $imageIds is an array with 10K keys $keyCount = count($imageIds); $keys = implode(', ', array_fill(0, $keyCount, '?')); $query = "SELECT * FROM images WHERE ImageID IN ({$keys})"; $stmt = $dbh->prepare($query); $stmt->execute($imageIds); // until now, it's been fast. fetch() is the slow part while ($row = $stmt->fetch()) { $rows[] = $row; }
Убедитесь, что вы указываете PDO, что это целое число, а не строка; если PDO ставит его как строку, тогда MySQL должен будет отображать значения для сравнения. В зависимости от того, как это происходит, это может привести к серьезным замедлениям, заставив MySQL избегать использования индекса.
Я не совсем уверен в поведении здесь, но у меня была эта проблема с Postgres несколько лет назад …
На образцовом коде есть некоторые серьезные ошибки. Точнее.
// $imageIds is an array with 10K keys $keyCount = count($imageIds); $keys = implode(', ', array_fill(0, $keyCount, '?')); $query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
пока что вышеприведенный код предоставит что-то вроде этого …
SELECT * FROM images WHERE ImageID IN (?, ?, ?, ?, ?, ?,...?, ?, ?, ?)
Не существует цикла для привязки … Там должен быть небольшой цикл, в котором вы бы привязывали все параметры, передаваемые в MySQL. Вы идете от prepare к execute . Когда правильное связывание – это прежде всего то, что вы хотите.
$stmt = $dbh->prepare($query); $stmt->execute($imageIds); // until now, it's been fast. fetch() is the slow part while ($row = $stmt->fetch()) { $rows[] = $row; }
Теперь у меня есть простой логический вопрос по этой части вопроса …
При использовании
SELECT * FROM table WHERE Id IN ( .. )запрашивает более 10000 ключей с использованием PDO с помощью команды prepare () / execute (), производительность ухудшает ~ 10X больше, чем выполнение одного и того же запроса с использованием mysqli с подготовленными операторами или PDO без использования подготовленные заявления.
Не было бы лучше, если бы тот же запрос был переписан, чтобы вам не нужно было передавать 10000 ключей в качестве параметров?
PDO и MySQLi не имеют существенных различий в таймингах. Плохие письменные запросы. Очень сложные Хранимые процедуры иногда могут оказаться медленными, если они недостаточно оптимизированы.
Проверьте, может ли другой запрос получить желаемый результат. Например
Создайте небольшую таблицу с именем test
create table `test` ( `id` int(10) not null, `desc` varchar(255) ); insert into `test` (`id`,`desc`) values (1,'a'),(10,'a1'),(11,'a2'),(12,'a3'),(13,'a4'),(14,'a5'),(15,'a6'),(2,'ab'),(20,'ab1'),(21,'ab2'),(22,'ab3'),(23,'ab4'),(24,'ab5'),(25,'ab6');
Запуск этих простых запросов
select * from `test` where `id` rlike '^1$'; select * from `test` where `id` rlike '^1+'; select * from `test` where `id`=1; select * from `test` where `id` rlike '^1.$'; select * from `test` where `id` rlike '.2$'; select * from `test` where `id` rlike '^2$'; select * from `test` where `id` rlike '.(2|3)'; // Slower select * from `test` where `id` IN (12,13,22,23); // Faster select * from `test` where `id` IN ('12,13,22,23'); // Wrong result select * from `test` where `id` IN ('12','13','22','23'); // Slower
Последние 4 запроса имеют тот же результат в этом примере. Я думаю, что в большинстве случаев, если вы проверите его на SQLFiddle, вы получите время запроса, соответствующее метке, которую они дали.
Не имейте опыта работы с PDO, поэтому не может с этим справиться, но этот метод довольно эффективен, хотя он немного уродлив в местах;)
<?php $nums = array(); $max = 10000; for($i=0;$i<$max*10;$i++) $nums[] = $i; $conn = new mysqli("127.0.0.1", "vldb_dbo", "pass", "vldb_db", 3306); $sql = sprintf("call list_products_by_id('%s',0)", implode(",",array_rand($nums, $max))); $startTime = microtime(true); $result = $conn->query($sql); echo sprintf("Fetched %d rows in %s secs<br/>", $conn->affected_rows, number_format(microtime(true) - $startTime, 6, ".", "")); $result->close(); $conn->close(); ?>
select count(*) from product; count(*) ======== 1000000 Fetched 1000 rows in 0.014767 secs Fetched 1000 rows in 0.014629 secs Fetched 2000 rows in 0.027938 secs Fetched 2000 rows in 0.027929 secs Fetched 5000 rows in 0.068841 secs Fetched 5000 rows in 0.067844 secs Fetched 7000 rows in 0.095199 secs Fetched 7000 rows in 0.095184 secs Fetched 10000 rows in 0.138205 secs Fetched 10000 rows in 0.134356 secs-select count(*) from product; count(*) ======== 1000000 Fetched 1000 rows in 0.014767 secs Fetched 1000 rows in 0.014629 secs Fetched 2000 rows in 0.027938 secs Fetched 2000 rows in 0.027929 secs Fetched 5000 rows in 0.068841 secs Fetched 5000 rows in 0.067844 secs Fetched 7000 rows in 0.095199 secs Fetched 7000 rows in 0.095184 secs Fetched 10000 rows in 0.138205 secs Fetched 10000 rows in 0.134356 secs
drop procedure if exists list_products_by_id; delimiter # create procedure list_products_by_id ( in p_prod_id_csv text, in p_show_explain tinyint unsigned ) proc_main:begin declare v_id varchar(10); declare v_done tinyint unsigned default 0; declare v_idx int unsigned default 1; create temporary table tmp(prod_id int unsigned not null)engine=memory; -- split the string into tokens and put into a temp table... if p_prod_id_csv is not null then while not v_done do set v_id = trim(substring(p_prod_id_csv, v_idx, if(locate(',', p_prod_id_csv, v_idx) > 0, locate(',', p_prod_id_csv, v_idx) - v_idx, length(p_prod_id_csv)))); if length(v_id) > 0 then set v_idx = v_idx + length(v_id) + 1; insert ignore into tmp values(v_id); else set v_done = 1; end if; end while; end if; if p_show_explain then select count(*) as count_of_tmp from tmp; explain select p.* from product p inner join tmp on tmp.prod_id = p.prod_id order by p.prod_id; end if; select p.* from product p inner join tmp on tmp.prod_id = p.prod_id order by p.prod_id; drop temporary table if exists tmp; end proc_main # delimiter ;