Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Optymalizator tabel w bazie danych MySQL
Forum PHP.pl > Forum > Gotowe rozwiązania > Algorytmy, klasy, funkcje
Speedy
26.03.2006, 14:56:13
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 winksmiley.jpg

[PHP] pobierz, plaintext 
  1. <?php
  2.  
  3. /**
  4.  * optymalizacja bazy danych MySQL
  5.  * @author `Speedy` <sppeedy@poczta.fm>
  6.  * @copy (c) `Speedy` 2006
  7.  * @version 1.0
  8.  */
  9. class optimize_database {
  10.  
  11. 	/**
  12. 	 * identyfikator połączenia z bazą danych
  13. 	 */
  14. 	var $connection;
  15.  
  16. 	/**
  17. 	 * nawiązuje połączenie z bazą danych
  18. 	 * @param string $host nazwa hosta
  19. 	 * @param string $db nazwa bazy danych
  20. 	 * @param string $user nazwa użytkownika
  21. 	 * @param string $password hasło użytkownika
  22. 	 */
  23. 	function connect($host,$user,$password,$db)
  24. 	{
  25. 	$this->connection = @mysql_connect($host,$user,$password);
  26.  
  27. 		if(!$this->connection)
  28. 		{
  29. 		die('Błąd: Nie można nawiązać połączenia z bazą danych.');
  30. 		}
  31.  
  32. 		if(!@mysql_select_db($db))
  33. 		{
  34. 		die('Błąd: Nie można wybrać bazy danych.');
  35. 		}		
  36. 	}
  37.  
  38. 	/**
  39. 	 * zamyka połączenie z bazą danych
  40. 	 * @param void
  41. 	 * @return void
  42. 	 */
  43. 	function disconnect()
  44. 	{
  45. 	mysql_close($this->connection);
  46. 	}
  47.  
  48. 	/**
  49. 	 * tworzy tablicę wielowymiarową 
  50. 	 * ze wszystkimi wierszami w tabeli określonej zapytaniem
  51. 	 * klucze w tablicy, to nazwy tabel
  52. 	 * @param string $query zapytanie SQL
  53. 	 * @return array $result wynik
  54. 	 */
  55. 	function mysql_fetch_all($query)
  56. 	{
  57. 	$result = array();
  58. 	$r	   = @mysql_query($query);
  59. 	   
  60. 	   if(@mysql_num_rows($r))
  61. 	   {
  62. 		  while($row = mysql_fetch_array($r))
  63. 		  {
  64. 		  $result[$row['Name']] = $row;
  65. 		  }
  66. 	   }
  67.  
  68. 	return $result;
  69. 	}
  70.  
  71. 	/**
  72. 	 * Pobiera informacje o tabelach z bazy danych
  73. 	 * @param string $host nazwa hosta
  74. 	 * @param string $db nazwa bazy danych
  75. 	 * @param string $user nazwa użytkownika
  76. 	 * @param string $password hasło użytkownika
  77. 	 * @return array tabele wraz z informacjami
  78. 	 */
  79. 	function get_tables($host,$user,$password,$db)
  80. 	{
  81. 	$this->connect($host,$user,$password,$db);
  82. 	$result = $this->mysql_fetch_all('SHOW TABLE STATUS');
  83. 	return $result;
  84. 	}
  85.  
  86. 	/**
  87. 	 * pobiera tabele, które powinny zostać zoptymalizowane
  88. 	 * @param string $host nazwa hosta
  89. 	 * @param string $db nazwa bazy danych
  90. 	 * @param string $user nazwa użytkownika
  91. 	 * @param string $password hasło użytkownika
  92. 	 * @return array $result zoptymalizowane tabele
  93. 	 */
  94. 	function get_overloaded_tables($host,$user,$password,$db)
  95. 	{
  96. 	$tables = $this->get_tables($host,$user,$password,$db);
  97.  
  98. 		foreach($tables as $table => $value)
  99. 		{
  100. 			if($value['Data_free'] > 0)
  101. 			{
  102. 			$result[$table] = $value;
  103. 			}
  104. 		}
  105.  
  106. 	return $result;
  107. 	}
  108.  
  109. 	/**
  110. 	 * optymalizuje tabele
  111. 	 * @param string $host nazwa hosta
  112. 	 * @param string $db nazwa bazy danych
  113. 	 * @param string $user nazwa użytkownika
  114. 	 * @param string $password hasło użytkownika
  115. 	 * @return int $data_free ilość zwolnionego miejsca (w bajtach)
  116. 	 */
  117. 	function do_optimization($host,$user,$password,$db)
  118. 	{
  119. 	$array = $this->get_overloaded_tables($host,$user,$password,$db);
  120.  
  121. 		if(count($array) > 0)
  122. 		{
  123. 			foreach($array as $table => $value)
  124. 			{
  125. 			$data_free += $value['Data_free'];
  126. 			mysql_query('OPTIMIZE TABLE `'.$table.'`');
  127. 			}
  128. 		}
  129.  
  130. 		else
  131. 		{
  132. 		$data_free = 0;
  133. 		}
  134.  
  135. 	$this->disconnect();
  136.  
  137. 	return $data_free;
  138. 	}
  139.  
  140. }
  141.  
  142. ?>
[PHP] pobierz, plaintext


sposób użycia:

[PHP] pobierz, plaintext 
  1. <?php
  2.  
  3. require 'optimize_database.class.php'; // plik z klasą
  4.  
  5. $optimize_database = new optimize_database;
  6. $data_free = $optimize_database -> do_optimization('localhost','admin','password','bazadanych');
  7.  
  8. echo 'Optymalizacja bazy danych zakończona: odzyskano '.$data_free.' bajt(ów)';
  9.  
  10. ?>
[PHP] pobierz, plaintext
Rafiki
29.03.2006, 20:30:46
jezeli optymalizator to przydaloby sie cachowanie zapytan.
Speedy
30.03.2006, 13:52:12
Chyba nie przeczytałeś dokładnie kodu tongue.gif.
Cachowanie, to inna bajka winksmiley.jpg.

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 winksmiley.jpg.
SongoQ
30.03.2006, 16:59:40
Cytat
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
6.04.2006, 17:33:42
Hej,

Postanowiłem upublicznić mały dodatek do mojej klasy winksmiley.jpg

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 
  1. <?php
  2.  
  3. /**
  4.  * optymalizacja baz danych MySQL
  5.  * @author `Speedy` <sppeedy@poczta.fm>
  6.  * @copy (c) `Speedy` 2006
  7.  * @version 1.0
  8.  */
  9. class optimize_databases {
  10.  
  11. 	/**
  12. 	 * przyszła instancja klasy do optymalizacji pojedynczej bazy danych
  13. 	 */
  14. 	var $optimize_database;
  15.  
  16. 	/**
  17. 	 * adres serwera MySQL
  18. 	 */
  19. 	var $host;
  20.  
  21. 	/**
  22. 	 * konstruktor
  23. 	 * @param void
  24. 	 * @return void
  25. 	 */ 
  26. 	function optimize_databases()
  27. 	{
  28. 	$this->optimize_database = new optimize_database;
  29. 	}
  30.  
  31. 	/**
  32. 	 * pobiera dane z wybranego pliku i zwraca je w tablicy
  33. 	 * nie interpretuje pierwszej i ostatniej linii pliku
  34. 	 * @param mixed $what plik tekstowy
  35. 	 * @param mixed $separator separator między komórkami w pliku
  36. 	 * @return array $file tablica z danymi z pliku
  37. 	 */		
  38. 	function get_data($what,$separator = '|') 
  39. 	{
  40. 		if(file_exists($what)) 
  41. 		{
  42. 			$file  = file($what);
  43. 			$lines = count($file);
  44.  
  45. 			for ($i = 0; $i < $lines; $i++) 
  46. 			{
  47. 			$line = explode($separator, trim($file[$i]));
  48. 			$file[$i] = $line;
  49. 			}
  50.  
  51. 		$file = array_slice($file, 1, -1);
  52. 		return $file;		
  53. 		}	
  54. 	}
  55.  
  56. 	/**
  57. 	 * optymalizuje bazy zdefiniowane w pliku
  58. 	 * struktura danych w pliku:
  59. 	 * user|password|database
  60. 	 * @param string $file plik tekstowy
  61. 	 * @param string $log warunkuje tworzenie pliku z logiem - parametr 'y' = stworzenie loga
  62. 	 * @return int $data_free ilość zwolnionej pamięci (w bajtach)
  63. 	 */
  64. 	function do_optimization($file,$log)
  65. 	{
  66. 	$array = @$this->get_data($file);
  67.  
  68. 		for($i = 0, $count = count($array) - 1; $i <= $count; $i++)
  69. 		{
  70. 		$data_free += $this->optimize_database->do_optimization($this->host,$array[$i][0],$array[$i][1],$array[$i][2]);
  71. 		}
  72.  
  73. 		if($log == 'y')
  74. 		{
  75. 		$file = fopen('optimization.log','a+');
  76. 		fwrite($file, date('Y-m-d, H:i:s').' - '.$data_free." byte(s) restored\n");
  77. 		fclose($file);
  78. 		}
  79.  
  80. 	return $data_free;
  81. 	}
  82.  
  83. }
  84.  
  85. ?>
[PHP] pobierz, plaintext


Przykładowy plik dbs.php

[PHP] pobierz, plaintext 
  1. <?php /* plik konfiguracyjny
  2. user1|password1|database1
  3. user2|password2|database2
  4. */ ?>
[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 
  1. <?php
  2.  
  3. require_once('optimize_database.class.php'); // plik z klasą optimize_database
  4. require_once('optimize_databases.class.php'); // plik z klasą optimize_databases
  5.  
  6. $optimize_databases = new optimize_databases();
  7. $optimize_databases -> host = 'localhost';
  8. $data_free = $optimize_databases -> do_optimization('dbs.php','y');
  9.  
  10. echo 'Optymalizacja baz danych zakończona: odzyskano '.$data_free.' bajt(ów)';
  11.  
  12. ?>
[PHP] pobierz, plaintext


Pozdrawiam.
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.