Optimalizace databáze v RS - Builder.cz - Informacni server o programovani

redakcni system

redakcni system

15. července 2002, 00.00 | Přestože jsou freeweby které poskytují neomezené místo v databázi, nikdy není na škodu trochu místa ušetřit. Jak na to, si řekneme v tomto článku.

Asi si říkáte, jak toho dosáhneme. Způsob je jednoduchý. Říká se tomu optimalizace. V RS jsme používali MySQL databázový server, a tak se budeme zabývat jen jím.
Co to vlastně optimalizace je a proč se používá? Optimalizace je proces, kdy se z tabulky odstraní zbytečné bajty, které jen zabírají prostor. Tyto bajty vznikají, když updatujeme nebo mažeme nějaký záznam z tabulky. Nejsou to žádné obrovské prostory na disku. Ušetřené místo se pohybuje v závislosti na používání tabulkek a jejich počtu v databázi tak okolo desítek bajtů u málo používaných, až po kilobajty u těch více zatěžovaných. Jak ale říkám, záleží na tom, jak je databáze používaná. V zásadě by se dalo říci, že je to defragmentece databáze.
Způsobů ušetření místa je několik. My se však budeme zabývat je tím, který je použit v našem scriptu. Ostatní najdete v originální dokumentaci.
Nejprve Vám ukáži script a poté si ho popíšeme.

include "../conn.php";

$dbname="rs"; // název databáze

echo "<center><b>Optimalizace databáze: $dbname</b><br><br>";
echo "<table border><tr><td>Tabulka</td><td>Velikost</td>
		<td>Správa</td><td>Ušetřené místo</td></tr>";
 $data = 0;
 $index = 0;
 $row = array();
 $sql = mysql_query("SHOW TABLE STATUS FROM $dbname");   // dotaz na databázi
    if (mysql_num_rows($sql)) {
	while ($row = mysql_fetch_array($sql)) {

/* celá problematika je přímo pod tímto komentářem */

	$data = $row['Data_length'];
            $index  = $row['Index_length'];
	$save = $row['Data_free'];

/* konec problematiky, ostatní jsou již známé věci :-) */

	$total = round(($data + $index)/1024,3); 
                 //spočítáme, vydělíme, zaokrouhlíme
	$save /= 1024; // uvolněné místo v kb
            $celkem += $save;
            $save = round($save,3); // zaokruhlíme  
	    $optim = mysql_query("OPTIMIZE TABLE $row[0]"); 
                                  // dotaz na databázi
       	    if ($save == 0) {
       		echo "<tr><td>"."$row[0]"."</td>"."<td>"."
                                         $total"." Kb"."</td>";
		echo "<td>Bez opravy</td><td>0 Kb</td></tr>";
       	    } else {
       	   	echo "<tr><td><b>"."$row[0]"."</b></td>"."<td>
                                         <b>"."$total"." Kb"."</b></td>";
		echo "<td><b>Optimalizovaná</b></td><td>
                                         <b>"."$save"." Kb</b></td></tr>";
       	    }		
	} 
    }
    echo "</table></center><br>";

    $celkem = round ($celkem,3); // zaokrouhlíme na 3 deset. místa
    echo "<center><b>Výsledky</b><br><br>
             Celkově ušetřené místo ".$celkem." Kb<br>"; 
             //výsledné ušetřené místo zobrazíme
  mysql_close($conn); //uzavřeme spojení s databází
?>

V současnosti by tento způsob pracoval pouze na MyISAM a u DBD tabulek (pokud se nemýlím), ale to je, myslím, nepodstatné. Příkaz OPTIMIZE TABLE používá následující postupy:

• Pokud v tabulce bylo mazáno nebo záznamy byly rozděleny, opraví tabulku
• Pokud indexy jednotlivých položek nejsou seřazené, seřadí je
• Pokud statistiky nejsou aktuální (a oprava nemůže být provedena seřazením indexů), aktualizuje statistiku.

Začneme načtením scriptu conn.php, abychom se mohli k databázi vůbec připojit. Inicilizujme proměnné na nulové hodnoty.
Následně použijem SQL dotaz SHOW TABLE STATUS jméno databáze

• Name - jméno tabulky
• Type - typ tabulky (ISAM,HEAP,MyISAM novější verze MySQL i BDB nebo InnoDB)
• Row_Format - formát uchovávání záznamu (Fixed, Dynamic nebo Compressed)
• Rows - počet záznamů
• Avg_row_length - průměrná délka záznamu
• Data_length - velikost datového souboru databáze
• Max_data_length - maximální velikost datového souboru
• Index_length - velikost indexového souboru
• Data_free - počet zabraných, ale nevyužívaných bajtů
• Auto_increment - další hodnata u pole auto_increment (většinou primární klíč)
• Create_time - čas vytvoření tabulky
• Update_time - čas poslední modifikace tabulky
• Check_time - čas poslední kontroly
• Create_options - sepeciální možnosti či práva, které byli uděleny při vytváření tabulky (příkaz CREATE TABLE)
• Comment - komentáře

Velikost datového a indexového souboru dané tabulky převedeme na KB a zaokrouhlíme na 3 desetinná místa. Ušetřené místo převedeme též na KB (o řádek níže ho zaokrouhlíme). Ušetřené místo připočítáme k celkově ušetřenému místu. A následuje to nejdůležitější, to jest optimalizace tabulky provedená příkazem OPTIMIZE TABLE. Tady proběhnou výše popsané postupy ušetření místa. Pokud jsme něco ušetřili, vypíšeme řádku s ušetřeným místem. V opačném případě zobrazíme hlášků, že tabulku není potřeba opravovat. Tento proces se opakuje pro všechny tabulky ve vybrané databázi. Nakonec ještě zobrazíme celkově ušetřené místo, které před tím zaokrouhlíme na 3 desetinná místa. Následuje již jen uzavření spojení s databází.

Jen pro úplnost. Možná si říkáte, že kilo, je předpona pro 1000 a my používáme 1024. Je to způsobeno tím, že počítače pracují ve dvojkové soustavě a předpona kilo je tu definována jako 2¹⁰, což je 1024. Ale to snad všichni víme.

Na závěr bych chtěl poděkovat Vladimíru Štofaníkovi. Optimalizace i vytvoření tohoto scriptu byla jeho práce. Já jsem doplnil jen svůj komentář.

Je tu další otázka. Jak často script spouštět? To je závislé na používání databáze, množství článků v ní, či pohybu dat v databázi. Script by se mohl spouštět automaticky (pomocí služby Shedule na Win NT a démonu Crond na Unixech), nebo manuálně. Nejspíše tedy šéfredakotrem. Z toho vyplívá, že do scriptu menu.php přidáme řádku a tak bude vypadat následovně:

.
.
.
// volby pro administrátora
if ($INFO[6] == 3)
{
echo "<A HREF=users.php target=main>Editace uživatelů</A><BR>\n";
echo "<A HREF=schval.php target=main>Schválené články</A><BR>\n";
echo "<A HREF=prehled.php target=main>Přehled článků</A><BR>\n";
echo "<A HREF=optimize.php target=main>Optimalizace databáze</A><BR>\n";
echo "<BR>\n";
}
.
.
.

Z toho jste asi poznali, že script optimize.php bude umístěn v adresáři ./admin.

A to je tak myslím vše. Ještě download originální verze. Znovu vyzývám všechny, kteří by měli nějaké připomínky či nápady na zlepšení, nechť mi je zašlou a já se je pokusím uvést do praxe a podělit se o výsledky s návštěvníky serveru Builder.cz.