Warum sind bestimmte Arten von vorbereiteten Abfragen mit PDO in PHP mit MySQL langsam?

Question

Bei der Verwendung von SELECT * FROM table WHERE Id IN ( .. ) Abfragen mit mehr als 10000 Schlüsseln, die PDO mit prepare()/execute() verwenden, verschlechtert sich die Leistung um das 10-fache im Vergleich zur Ausführung derselben Abfrage mit mysqli mit vorbereiteten Anweisungen oder PDO ohne vorbereitete Anweisungen.

Weitere seltsame Details:

• Typischere SELECT-Anweisungen, die nicht die WHERE Id IN( ..) Klausel funktioniert auch bei 100K+ Zeilen einwandfrei. SELECT * FROM table WHERE Id ist zum Beispiel schnell.

• Die Leistungsverschlechterung tritt auf, nachdem prepare()/execute() abgeschlossen ist - sie ist vollständig in PDOStatement::fetch() o PDOStatement::fetchAll() . Die Ausführungszeit der MySQL-Abfrage ist in allen Fällen winzig - es handelt sich nicht um eine Optimierung von MySQL.

• Die Aufteilung der 10K-Abfrage in 10 Abfragen mit 1K Schlüsseln ist leistungsfähig.

• Die Verwendung von mysql, mysqli mit vorbereiteten Anweisungen oder PDO ohne vorbereitete Anweisungen ist performant.

• PDO w/prepared benötigt im folgenden Beispiel ~6 Sekunden, während die anderen ~0,5s benötigen.

• Je mehr Tasten Sie haben, desto schlimmer wird es auf nichtlineare Weise. Versuchen Sie 100K Tasten.

Beispiel-Code:

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

Stellen Sie sicher, dass Sie PDO mitteilen, dass der Wert eine Ganzzahl und keine Zeichenkette ist; wenn PDO ihn als Zeichenkette angibt, muss MySQL die Werte für den Vergleich typisieren. Je nachdem, wie das geschieht, kann dies zu erheblichen Verlangsamungen führen, da MySQL die Verwendung eines Indexes vermeidet.

Ich bin mir nicht ganz sicher über das Verhalten hier, aber ich hatte dieses Problem mit Postgres vor ein paar Jahren...

Answer 2

Der Beispielcode weist einige schwerwiegende Fehler auf. Also, um genauer zu sein.

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

Bisher liefert der obige Code etwa Folgendes...

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

Es gibt keine Schleife für die Bindung... Es sollte eine kleine Schleife geben, in der Sie alle Parameter, die an MySQL übergeben werden, binden. Sie gehen von prepare a execute . Wenn korrekt verbindlich ist in erster Linie das, was Sie wollen.

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

Jetzt habe ich eine einfache logische Frage zu diesem Teil der Frage...

Bei der Verwendung von SELECT * FROM table WHERE Id IN ( .. ) Abfragen mit mehr als 10000 Tasten PDO mit prepare()/execute() verwenden, ist die Leistung verschlechtert sich ~10X mehr als die gleiche Abfrage mit mysqli mit vorbereiteten Anweisungen oder PDO ohne vorbereitete Anweisungen.

Wäre es nicht besser, wenn dieselbe Abfrage so umgeschrieben würde, dass man nicht 10000 Schlüssel als Parameter übergeben müsste?

PDO y MySQLi haben keine großen Unterschiede in der Zeitplanung. Schlecht geschriebene Abfragen schon. Sehr komplexe Stored Procedures können sich manchmal als langsam erweisen, wenn sie nicht gut optimiert sind.

Prüfen Sie, ob eine andere Abfrage das gewünschte Ergebnis liefern könnte. Zum Beispiel

Erstellen Sie eine kleine Tabelle mit dem Namen 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');

Führen Sie diese einfachen Abfragen aus

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

Die letzten 4 Abfragen haben in diesem Beispiel das gleiche Ergebnis. Ich denke, dass die meisten der Zeiten, wenn Sie es überprüfen auf SQLFiddle würden Sie Abfragezeiten erhalten, die der Bezeichnung entsprechen, die sie erhalten haben.

Answer 3

Ich habe keine Erfahrung mit PDO, so kann nicht mit, dass helfen, aber diese Methode ist ziemlich performant, obwohl es ein bisschen hässlich in Orten ist ;)

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

?>

Ergebnisse

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 ;