Vergleich von PDO und MySQLi-Geschwindigkeit

Meine Schlussfolgerung

Aufgrund meiner Tests und der Vorteile von PDO (>Info ) empfehle ich Folgendes: Verwenden Sie PDO mit PDO::ATTR_EMULATE_PREPARESset to TRUEund verwenden Sie das charset=utf8mb4in dsn (um genauso sicher zu sein, als ob die Emulation deaktiviert wäre). Die Geschwindigkeitssteigerung liegt bei ca. 80% und dies ist beispielsweise bei Laravel nicht die Standardeinstellung. Der Unterschied zu MySQLi bei der Verwendung von real_escape_string ist wirklich so gering, dass es sich nicht lohnt (etwa 1,5%) und die Verwendung vorbereiteter Anweisungen viel sicherer ist. Ich habe MySQL auf einem separaten Server (was sehr häufig vorkommt) und ich muss in der Lage sein, Tausende von Abfragen pro Sekunde auszuführen (vielleicht nicht so häufig), daher ist dies eine große Sache für mich. Ich teste hier eine allgemeine Auswahlabfrage in meiner Produktionsumgebung.

Meine Testergebnisse

Testen Sie mit 5000 einfachen Abfragen mit kurzer Latenz (mithilfe des Webservers, der eine Verbindung zu einem externen MySQL-Server im selben Rechenzentrum mit PHP Version 5.6 herstellt): Test 1 (PDO mit Abfrage + Zitat) beendet in 4,282 Sekunden Test 2 (PDO mit vorbereiteter Anweisung) beendet in 4,465 Sekunden Test 3 (PDO mit Emulation mit Abfrage + Zitat) beendet in 2,578 Sekunden Test 4 (PDO mit Emulation mit vorbereiteter Anweisung) beendet in 2,518 Sekunden Test 5 (MySQLi mit real_escape_string) beendet in 2,482 Sekunden Test 6 (MySQLi mit vorbereiteter Anweisung) beendet in 4,396 Sekunden Testen Sie mit langer Latenz (mit localhost, das mit PHP Version 7.1 eine Verbindung zu einem externen MySQL-Server herstellt) mit 100 einfachen Abfragen: Test 1 (PDO mit Abfrage + Anführungszeichen) beendet in 6,231 Sekunden Test 2 (PDO mit vorbereiteter Anweisung) beendet in 6,489 Sekunden Test 3 (PDO mit Emulation mit Abfrage + Anführungszeichen) beendet in 3,126 Sekunden Test 4 (PDO mit Emulation mit vorbereiteter Anweisung) beendet in 3,127 Sekunden Test 5 (MySQLi mit real_escape_string) beendet in 3,123 Sekunden Test 6 (MySQLi mit vorbereiteter Anweisung) beendet in 6,189 Sekunden

Führen Sie Ihren eigenen Test durch

   set_charset('utf8');

// PDO connection
$dsn = "mysql:host=".MYSQL_HOST.";dbname=".MYSQL_DATABASE.";charset=utf8mb4";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE, // 80% speed increase if set to TRUE. Changed to TRUE after test 2
];
$conn_pdo = new PDO($dsn, MYSQL_USERNAME, MYSQL_PASSWORD, $opt);


// Test 1 using PDO with query + quote
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {    
	// Random string to bypass the query cache
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$conn_pdo->quote($domain);
	$stmt = $conn_pdo->query("SELECT domain FROM domains WHERE domain = '{$domain}' LIMIT 1");
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 1 (PDO with query + quote) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 2 using PDO with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_pdo->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1");
	$stmt->execute([$domain]);
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 2 (PDO with prepared statement) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Enable the emulation of prepares
$conn_pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, TRUE);

// Test 3 using PDO with emulation with query + quote
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	// Random string to bypass the query cache
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$conn_pdo->quote($domain);
	$stmt = $conn_pdo->query("SELECT domain FROM domains WHERE domain = '{$domain}' LIMIT 1");
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 3 (PDO with emulation with query + quote) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 4 using PDO with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_pdo->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1");
	$stmt->execute([$domain]);
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 4 (PDO with emulation with prepared statement) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);


// Test 5 using MySQLi with real_escape_string
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$domain = $conn_mysqli->real_escape_string($domain);
	$row = mysqli_fetch_assoc(mysqli_query($conn_mysqli, "SELECT domain FROM domains WHERE domain = '".$domain."' LIMIT 1;"));
}	
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 5 (MySQLi with real_escape_string) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 6 using MySQLi with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_mysqli->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1;");
	$stmt->bind_param('s', $domain);
	$stmt->execute();
	$row = $stmt->get_result()->fetch_array(MYSQLI_ASSOC);
	$stmt->free_result();
}	
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 6 (MySQLi with prepared statement) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2); 
    

      
      set_charset('utf8');

// PDO connection
$dsn = "mysql:host=".MYSQL_HOST.";dbname=".MYSQL_DATABASE.";charset=utf8mb4";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE, // 80% speed increase if set to TRUE. Changed to TRUE after test 2
];
$conn_pdo = new PDO($dsn, MYSQL_USERNAME, MYSQL_PASSWORD, $opt);


// Test 1 using PDO with query + quote
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {    
	// Random string to bypass the query cache
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$conn_pdo->quote($domain);
	$stmt = $conn_pdo->query("SELECT domain FROM domains WHERE domain = '{$domain}' LIMIT 1");
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 1 (PDO with query + quote) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 2 using PDO with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_pdo->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1");
	$stmt->execute([$domain]);
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 2 (PDO with prepared statement) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Enable the emulation of prepares
$conn_pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, TRUE);

// Test 3 using PDO with emulation with query + quote
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	// Random string to bypass the query cache
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$conn_pdo->quote($domain);
	$stmt = $conn_pdo->query("SELECT domain FROM domains WHERE domain = '{$domain}' LIMIT 1");
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 3 (PDO with emulation with query + quote) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 4 using PDO with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_pdo->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1");
	$stmt->execute([$domain]);
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 4 (PDO with emulation with prepared statement) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);


// Test 5 using MySQLi with real_escape_string
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$domain = $conn_mysqli->real_escape_string($domain);
	$row = mysqli_fetch_assoc(mysqli_query($conn_mysqli, "SELECT domain FROM domains WHERE domain = '".$domain."' LIMIT 1;"));
}	
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 5 (MySQLi with real_escape_string) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 6 using MySQLi with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_mysqli->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1;");
	$stmt->bind_param('s', $domain);
	$stmt->execute();
	$row = $stmt->get_result()->fetch_array(MYSQLI_ASSOC);
	$stmt->free_result();
}	
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 6 (MySQLi with prepared statement) finished in ".$time_it_took." seconds
\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);