Jak zrychlit odezvu webových stránek (PHP, MySQL)

Shrnutí pro ty, kteří hledají rychlé informace :-)

• 1) Bottleneck webových aplikací je téměř vždy v databázi a nepoužívání PHP built-in funkcí, níže jsou typy na zrychlení výběru a řazení dat.
• 2) Indexovat sloupce v tabulce, které se objevují ve WHERE, ORDER BY a GROUP BY
• 3) Datové typy sloupců rychlost TEXT vs. VARCHAR - Proč je VARCHAR rychlejší
• 4) Rychlejší INSERT - používání INSERT INTO table (column_1, column_2) VALUES (1, 1), (1, 4), (4,5) - vkládání více dat v jednom SQL dotazu
• 5) Rychlost řazení - vlastní sort vs. usort() = proč je usort() rychlejší

Struktura webové aplikace

Než se vrhneme na samotnou optimalizaci pomalé aplikace, je nejdříve potřeba se podívat na strukturu samotné webové aplikace. Na obrázku níže je rozkreslená velmi stručná ilustrace = základní kámen úrazu většiny webových aplikací.

Pomineme-li v diagramu chybějící webserver a další mezivrsty přes které požadavek cestuje... Nejdéle trvá vybrání dat z databáze a jejich řazení/zobrazení na webu.

Zrychlení SELECTu - INDEXACE

Naprosto zásadní pro rychlost výběru dat z databáze je indexace sloupců. Taková základní a jednoduchá poučka. Sloupce které jsou ve WHERE, ORDER BY, GROUP BY indexujte!

Testovací tabulka, 41 400 řádků

CREATE TABLE `product_option` (
`product_option_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`option_id` int(11) NOT NULL,
`value` text NOT NULL,
`required` tinyint(1) NOT NULL
);

ALTER TABLE `product_option` ADD PRIMARY KEY (`product_option_id`);

Testování:

1. Bez indexů

SELECT * FROM `product_option` WHERE product_id = 1024

Čas = 43,7 ms

2. Index nad sloupcem product_id

ALTER TABLE `product_option` ADD INDEX(`product_id`);“

SELECT * FROM `product_option` WHERE product_id = 1024

Čas = 0,5 ms !

Datové typy tabulky (TEXT vs. VARCHAR)

Při tvoření tabulky volíte pro každý sloupec jeho datový typ. Použití datového typu TEXT se snažte co nejvíce omezit.

Testovací tabulka, 168 725 řádků

CREATE TABLE `product_description` (
`product_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` text NOT NULL,
`description` text NOT NULL,
`tag` text NOT NULL,
`meta_title` text NOT NULL,
`meta_description` varchar(255) NOT NULL,
`meta_keyword` varchar(255) NOT NULL,
`language_done` tinyint(1) NOT NULL DEFAULT '0',
`no_edit` tinyint(1) NOT NULL
)

1. Datový typ TEXT

SELECT * FROM `product_description` ORDER BY name

Čas = 378 ms

1. Datový typ VARCHAR(255)

SELECT * FROM `product_description` ORDER BY name

Čas = 312 ms

1. Datový typ VARCHAR(255) + index nad sloupcem name

SELECT * FROM `product_description` ORDER BY name

Čas = 1,9 ms !

Narozdíl od VARCHARU, datový typ TEXT nelze indexovat! Přidáním indexu nad sloupec VARCHAR se dostaneme opět na velmi dobré odezvy.

Zrychlení importu dat, INSERT

Import velkého množství dat např. z XML importu produktů do tabulky je běžný případ z praxe. Naprosto běžně se vyskytuje kód uvedený níže.

foreach($product as $item) {
$mysql->query("INSERT INTO products (name, price) VALUES('" . escape($item['name']) . "', '" . (int)$item['price'] . "'");
}

Čas = 530 ms / 1000 řádků

Složení SQL stringu s více položkami najednou

$strBuff = "";
$counter = 0;
$buff = 0;
$buffFlush = 8;

foreach($array as $item) {
$counter++;
if($buffFlush > $buff && count($array) != $counter) {

$strBuff .= "('" . $mysqli->real_escape_string($item['name']) . "', '" . (int)$item['price'] . "'), ";
$buff++;

} else if($buffFlush == $buff || count($array) == $counter) {

$strBuff .= "('" . $mysqli->real_escape_string($item['name']) . "', '" . (int)$item['price'] . "')";
$mysqli->query("INSERT INTO products (name, price) VALUES " . $strBuff);

$strBuff = "";
$buff = 0;
}
}

Čas = 74 ms / 1000 řádků !

Výše uvedené kódy jsou pouze pro demonstraci, v produkčním prostředí doporučujeme PREPARED STATEMENTS (PDO)

Je mnohem efektivnější importovat v jednom query více dat najednou. Výše uvedený kód nedělá nic jiného, než místo několikrát zavolaného

INSERT INTO products (name, price) VALUES ("Košile", 120)
INSERT INTO products (name, price) VALUES ("Mikina", 350)
INSERT INTO products (name, price) VALUES ("Bunda", 750)

Tak dotaz složí do

INSERT INTO products (name, price) VALUES ("Košile", 120), ("Mikina", 350), ("Bunda", 750)....

Řazení dat v PHP

Občas se nevyhnete situaci, kdy je potřeba seřadit data v PHP. Velmi častým jevem je uživatelsky napsaný bubble/selection sort.

for($i = 0; $i < count($array); $i++) {
for($j = 0; $j < count($array); $j++) {
if($array[$i]['price'] > $array[$j]['price']) {
$tmp = $array[$i];
$array[$i] = $array[$j];
$array[$j] = $tmp;
}
}
}

Čas = 2760 ms / 5000 prvků

Když to srovnáme s uSortem v PHP

function sortPrices($a, $b) {
if($a['price'] == $b['price']) return 0;

return ($a['price'] > $b['price']) ? -1 : 1;
}

usort($array, "sortPrices");

Čas = 22 ms / 5000 prvků !

Dostaneme se k obrovské optimalizaci řazení prvků. Bubble sort má složitost n² ovšem funkce uSort využívající Merge sort má složitost log(n) * n, díky tomu je uSort mnohem rychlejší.

Závěrem: Optimalizace odezvy webových stránek se skládá z několika faktorů a tento článek popisuje pouze jeden z nich. Věříme že Vám tyto informace pomůžou při optimalizaci Vašich e-shopů, blogů a dalších webových aplikací.