stored - sentencias preparadas php pdo

¿Por qué ciertos tipos de consultas preparadas que usan PDO en PHP con MySQL son lentas?

Question

Cuando se utiliza SELECT * FROM table WHERE Id IN ( .. ) consultas con más de 10000 claves usando PDO con prepare () / execute (), el rendimiento se degrada 10 veces más que hacer la misma consulta usando mysqli con declaraciones preparadas o PDO sin usar declaraciones preparadas.

Más detalles extraños:

• Las instrucciones SELECT más típicas que no tienen la cláusula WHERE Id IN( ..) funcionan bien incluso con 100K + filas. SELECT * FROM table WHERE Id por ejemplo, es rápido.

• La degradación del rendimiento se produce después de que prepare () / execute () se complete, está completamente en PDOStatement::fetch() o PDOStatement::fetchAll() . El tiempo de ejecución de la consulta de MySQL es mínimo en todos los casos, no se trata de una optimización de MySQL.

• La división de la consulta 10K en 10 consultas con claves 1K es satisfactoria.

• El uso de mysql, mysqli con declaraciones preparadas, o DOP sin declaraciones preparadas es eficaz.

• La DOP con / preparada toma ~ 6 segundos en el ejemplo a continuación, mientras que las otras toman ~ 0.5 s.

• Empeora de forma no lineal cuantas más teclas tenga. Probar las teclas de 100K.

Código de muestra:

// $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;
}

Answer 1

Asegúrese de que le está diciendo a PDO que el valor es un entero y no una cadena; si PDO lo pone como una cadena, entonces MySQL tendrá que encasillar los valores para compararlos. Dependiendo de cómo se desarrolle esto, podría causar desaceleraciones importantes al hacer que MySQL evite usar un índice.

No estoy completamente seguro del comportamiento aquí, pero he tenido este problema con Postgres hace unos años ...

Answer 2

Hay algunos errores importantes en el código de ejemplo. Para ser más precisos.

// $imageIds is an array with 10K keys
$keyCount = count($imageIds);
$keys = implode('', '', array_fill(0, $keyCount, ''?''));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";

Hasta ahora el código anterior proporcionará algo como esto ...

SELECT * FROM images WHERE ImageID IN (?, ?, ?, ?, ?, ?,...?, ?, ?, ?)

No hay ningún bucle para vincular ... Debe haber un pequeño bucle en el que se vincularían todos los parámetros que se pasan a MySQL. Vas de prepare a execute . Cuando el binding correcto es principalmente lo que quieres.

$stmt = $dbh->prepare($query);
$stmt->execute($imageIds);
// until now, it''s been fast.  fetch() is the slow part
while ($row = $stmt->fetch()) {
    $rows[] = $row;
}

Ahora tengo una pregunta lógica simple en esta parte de la pregunta ...

Cuando se utiliza SELECT * FROM table WHERE Id IN ( .. ) consultas con más de 10000 claves usando PDO con prepare () / execute (), el rendimiento se degrada 10 veces más que hacer la misma consulta usando mysqli con declaraciones preparadas o PDO sin usar declaraciones preparadas.

¿No sería mejor si se volviera a escribir la misma consulta para que no tenga que pasar 10000 claves como parámetros?

PDO y MySQLi no tienen diferencias importantes en los tiempos. Malas consultas escritas hacen. Los procedimientos almacenados muy complejos a veces pueden resultar lentos si no están bien optimizados.

Compruebe si otra consulta puede obtener el resultado deseado. Por ejemplo

Crear una pequeña tabla llamada 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'');

Ejecutar esas consultas simples

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

Las últimas 4 consultas tienen el mismo resultado en este ejemplo. Creo que la mayoría de las veces, si lo verifica en SQLFiddle , obtendría tiempos de consulta que corresponden a la etiqueta que se les ha dado.

Answer 3

No tengo experiencia con PDO, así que no puedo ayudar con eso, pero este método es bastante eficaz, aunque es un poco feo en algunos lugares;)

PHP

<?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();

?>

Resultados

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

MySQL

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 ;