Optymalizator tabel w bazie danych MySQL, klasa, PHP4 i PHP5 Opcje

[Speedy]

Ostatnio znajomy poprosił mnie o napisanie skryptu służącego do optymalizacji baz danych MySQL na serwerze.
Wykorzystuje on w swoim działaniu poniższą klasę. Może komuś jeszcze się przyda

[PHP] pobierz, plaintext 
<?php
 
/**
 * optymalizacja bazy danych MySQL
 * @author `Speedy` <sppeedy@poczta.fm>
 * @copy (c) `Speedy` 2006
 * @version 1.0
 */
class optimize_database {
 
	/**
	 * identyfikator połączenia z bazą danych
	 */
	var $connection;
 
	/**
	 * nawiązuje połączenie z bazą danych
	 * @param string $host nazwa hosta
	 * @param string $db nazwa bazy danych
	 * @param string $user nazwa użytkownika
	 * @param string $password hasło użytkownika
	 */
	function connect($host,$user,$password,$db)
	{
	$this->connection = @mysql_connect($host,$user,$password);
 
		if(!$this->connection)
		{
		die('Błąd: Nie można nawiązać połączenia z bazą danych.');
		}
 
		if(!@mysql_select_db($db))
		{
		die('Błąd: Nie można wybrać bazy danych.');
		}		
	}
 
	/**
	 * zamyka połączenie z bazą danych
	 * @param void
	 * @return void
	 */
	function disconnect()
	{
	mysql_close($this->connection);
	}
 
	/**
	 * tworzy tablicę wielowymiarową 
	 * ze wszystkimi wierszami w tabeli określonej zapytaniem
	 * klucze w tablicy, to nazwy tabel
	 * @param string $query zapytanie SQL
	 * @return array $result wynik
	 */
	function mysql_fetch_all($query)
	{
	$result = array();
	$r	   = @mysql_query($query);
	   
	   if(@mysql_num_rows($r))
	   {
		  while($row = mysql_fetch_array($r))
		  {
		  $result[$row['Name']] = $row;
		  }
	   }
 
	return $result;
	}
 
	/**
	 * Pobiera informacje o tabelach z bazy danych
	 * @param string $host nazwa hosta
	 * @param string $db nazwa bazy danych
	 * @param string $user nazwa użytkownika
	 * @param string $password hasło użytkownika
	 * @return array tabele wraz z informacjami
	 */
	function get_tables($host,$user,$password,$db)
	{
	$this->connect($host,$user,$password,$db);
	$result = $this->mysql_fetch_all('SHOW TABLE STATUS');
	return $result;
	}
 
	/**
	 * pobiera tabele, które powinny zostać zoptymalizowane
	 * @param string $host nazwa hosta
	 * @param string $db nazwa bazy danych
	 * @param string $user nazwa użytkownika
	 * @param string $password hasło użytkownika
	 * @return array $result zoptymalizowane tabele
	 */
	function get_overloaded_tables($host,$user,$password,$db)
	{
	$tables = $this->get_tables($host,$user,$password,$db);
 
		foreach($tables as $table => $value)
		{
			if($value['Data_free'] > 0)
			{
			$result[$table] = $value;
			}
		}
 
	return $result;
	}
 
	/**
	 * optymalizuje tabele
	 * @param string $host nazwa hosta
	 * @param string $db nazwa bazy danych
	 * @param string $user nazwa użytkownika
	 * @param string $password hasło użytkownika
	 * @return int $data_free ilość zwolnionego miejsca (w bajtach)
	 */
	function do_optimization($host,$user,$password,$db)
	{
	$array = $this->get_overloaded_tables($host,$user,$password,$db);
 
		if(count($array) > 0)
		{
			foreach($array as $table => $value)
			{
			$data_free += $value['Data_free'];
			mysql_query('OPTIMIZE TABLE `'.$table.'`');
			}
		}
 
		else
		{
		$data_free = 0;
		}
 
	$this->disconnect();
 
	return $data_free;
	}
 
}
 
?>
[PHP] pobierz, plaintext 

sposób użycia:

[PHP] pobierz, plaintext 
<?php
 
require 'optimize_database.class.php'; // plik z klasą
 
$optimize_database = new optimize_database;
$data_free = $optimize_database -> do_optimization('localhost','admin','password','bazadanych');
 
echo 'Optymalizacja bazy danych zakończona: odzyskano '.$data_free.' bajt(ów)';
 
?>
[PHP] pobierz, plaintext 

Ten post edytował Speedy 4.05.2006, 10:27:56

[Rafiki]

jezeli optymalizator to przydaloby sie cachowanie zapytan.

[Speedy]

Chyba nie przeczytałeś dokładnie kodu .
Cachowanie, to inna bajka .

Nazwałem to optymalizatorem, gdyż opiera się na SQL-owym poleceniu OPTIMIZE TABLE, co powoduje otymalizację tabeli w bazie danych, czyli zwolnienie niewykorzystywanego miejsca. Przy pobieraniu informacji o tabelach jest takie pole `Data_free`, które informuje o nadmiarze danych. W phpmyadminie jest nawet taka kolumna o nazwie `Nadmiar` i przy przeglądaniu bazy i można sobie zoptymalizować pojedynczą tabelę. Jednak uciążliwe będzie optymalizowanie wielu tabel osobno w wielu bazach na serwerze w sposób manualny - ten skrypt robi tę robotę automatycznie .

[SongoQ]

Nazwałem to optymalizatorem, gdyż opiera się na SQL-owym poleceniu OPTIMIZE TABLE, co powoduje otymalizację tabeli w bazie danych, czyli zwolnienie niewykorzystywanego miejsca.

Rowniez mnie to zmylilo, bylem pewnien ze jakos chcesz zoptymalizowac schemat i wydajnosc bazy. Zmien temat bardziej na optymalizacje tabeli. Takie cos mozna podpiac pod crona i wykorzystac.

[Speedy]

Hej,

Postanowiłem upublicznić mały dodatek do mojej klasy

Umożliwia on masową optymalizację wielu baz.

klasa optimize_databases (do jej działania jest potrzebna klasa optimize_database, której źródło jest podane w pierwszym poście tego wątku oraz plik konfiguracyjny dbs.php - jego przykładowe źródło jest niżej).

[PHP] pobierz, plaintext 
<?php
 
/**
 * optymalizacja baz danych MySQL
 * @author `Speedy` <sppeedy@poczta.fm>
 * @copy (c) `Speedy` 2006
 * @version 1.0
 */
class optimize_databases {
 
	/**
	 * przyszła instancja klasy do optymalizacji pojedynczej bazy danych
	 */
	var $optimize_database;
 
	/**
	 * adres serwera MySQL
	 */
	var $host;
 
	/**
	 * konstruktor
	 * @param void
	 * @return void
	 */ 
	function optimize_databases()
	{
	$this->optimize_database = new optimize_database;
	}
 
	/**
	 * pobiera dane z wybranego pliku i zwraca je w tablicy
	 * nie interpretuje pierwszej i ostatniej linii pliku
	 * @param mixed $what plik tekstowy
	 * @param mixed $separator separator między komórkami w pliku
	 * @return array $file tablica z danymi z pliku
	 */		
	function get_data($what,$separator = '|') 
	{
		if(file_exists($what)) 
		{
			$file  = file($what);
			$lines = count($file);
 
			for ($i = 0; $i < $lines; $i++) 
			{
			$line = explode($separator, trim($file[$i]));
			$file[$i] = $line;
			}
 
		$file = array_slice($file, 1, -1);
		return $file;		
		}	
	}
 
	/**
	 * optymalizuje bazy zdefiniowane w pliku
	 * struktura danych w pliku:
	 * user|password|database
	 * @param string $file plik tekstowy
	 * @param string $log warunkuje tworzenie pliku z logiem - parametr 'y' = stworzenie loga
	 * @return int $data_free ilość zwolnionej pamięci (w bajtach)
	 */
	function do_optimization($file,$log)
	{
	$array = @$this->get_data($file);
 
		for($i = 0, $count = count($array) - 1; $i <= $count; $i++)
		{
		$data_free += $this->optimize_database->do_optimization($this->host,$array[$i][0],$array[$i][1],$array[$i][2]);
		}
 
		if($log == 'y')
		{
		$file = fopen('optimization.log','a+');
		fwrite($file, date('Y-m-d, H:i:s').' - '.$data_free." byte(s) restored\n");
		fclose($file);
		}
 
	return $data_free;
	}
 
}
 
?>
[PHP] pobierz, plaintext 

Przykładowy plik dbs.php

[PHP] pobierz, plaintext 
<?php /* plik konfiguracyjny
user1|password1|database1
user2|password2|database2
*/ ?>
[PHP] pobierz, plaintext 

btw. Jeśli ktoś jest administratorem serwera i chce sobie optymalizować wszystkie bazy, które się na nim znajdują, to może zmodyfikować klasę optimize_databases i zastosować funkcję mysql_list_dbs" title="Zobacz w manualu php" target="_manual.

Przykład użycia:

[PHP] pobierz, plaintext 
<?php
 
require_once('optimize_database.class.php'); // plik z klasą optimize_database
require_once('optimize_databases.class.php'); // plik z klasą optimize_databases
 
$optimize_databases = new optimize_databases();
$optimize_databases -> host = 'localhost';
$data_free = $optimize_databases -> do_optimization('dbs.php','y');
 
echo 'Optymalizacja baz danych zakończona: odzyskano '.$data_free.' bajt(ów)';
 
?>
[PHP] pobierz, plaintext 

Pozdrawiam.

Ten post edytował Speedy 6.04.2006, 17:37:17