Dyskusja: optymalizacja baz danych, Zliczanie rekordów z drugiej tabeli Opcje

[Athlan]

Witam,

Dziś chciałbym podyskutować troszkę na temat optylamizacji zapytań do baz danych.

Ostatnio mam problem z pobraniem ilości komentarzy przypisanych do listy notek na blogu, a że będzie to dość wielki system blogowy oferujący dobudowanie swojej cegiełki w Internecie, zależy mi na maksymalnej optymalizacji, dlatego poświece temu ten oto topick.

Wracając do zliczania komentarzy. Mamy 3 sposoby:

1. Zapisywać ilość komentarzy (notes_comments = notes_comments + 1) przy każdym dodawaniu komentarza do notki, a przy usuwaniu odejmować tą wartość.

2. Zliczać ilość komentarzy zapytaniem, które przelatuje przez tabelą komentarzy licząc je i grupując wg ich ID, ale tylko te, które przynależą do notek wyświetlonych na liście, przykładowo:

[PHP] pobierz, plaintext 
<?php
  public function getUsed(array $aNotes)
  {
	$this->_connect();
	$oDb = $this->getSource();
 
	$sSql = '
	SELECT `comment_blog_note` AS `note`, COUNT(`comment_id`) AS `comments`
	FROM `comments`
	WHERE `comment_blog_note` = ' . implode(' OR `comment_blog_note` = ', $aNotes) . '
	GROUP BY `comment_blog_note`';
	$rResult = $oDb->Execute($sSql);
 
	$aComments = array();
 
	while($aRow = mysql_fetch_array($rResult))
	{
	  if(!is_array($aUsedTags[$aRow['note']]))
				   $aUsedTags[$aRow['note']] = array();
 
	  $aComments[$aRow['note']][] = $aRow['comments'];
	}
 
	return $aComments;
  }
?>
[PHP] pobierz, plaintext 

Otrzymujemy tablicę: NOTKA => ILOŚĆ_KOMENTARZY, później możemy połączyć ją w PHP.

3. Dołączmy JOINA i countujemy komentarze przpisane do danej notki:

[SQL] pobierz, plaintext 
SELECT n.*, COUNT(c.comment_id) AS count_comments FROM notes AS n
LEFT JOIN comments AS c ON (c.comment_blog_note = n.note_id)
GROUP BY note_id
ORDER BY note_time DESC
[SQL] pobierz, plaintext 

-----------------------------------------------------------------------------------------

Wykonując sposób pierwszy, nie mamy doczynienia z żadnym liczeniem. Chciałbym trochę podyskutować na temat sposobu 2 i 3. Doszły mnie słuchy od @cichy'ego i od @hwao, że przy wielkiej ilości rekordów baza może trochę zamulać. Zadaje więc 2 pytania:

Całe sedno sprawy

1. Baza danych zacznie zamulać w przypadku dużej ilości rekordów przypisanych do notki?
2. Baza danych zacznie zamulać w przypadku dużej ilości rekordów w tabeli komentarzy?

Jak narazie jestem skłonny jak najbardziej do sposobu nr 1, ale chciałbym omówić dwa pozostałe.

Zapraszam do dyskusji Athlan

[AcidBurnt]

naqjlepiej bedzie zliczac ilosc konetarzy wpisów, dodawajac danycj, poprzez jakiego trigera ktory bedzie po prostu zwiekszal gdzies ta wartosc

i tak baza bedzie sie mulic jesli bedizesz mial duuuuzo rekordów i bedziesz wykonywał operacje count itp

[Athlan]

Przeprowadziłem testy, miałem do dyspozycji:

• 10 000 notek
• do każdej notki po 10 komentarzy, czyli 100 000
• sumarycznie operowałem na 110 000 rekordach

Zapytanie ze sposobu nr 3, operacja z limitem na 1000 rekordów:

[SQL] pobierz, plaintext 
SELECT n.*, COUNT(c.comment_id) AS count_comments FROM megatest_notes AS n
LEFT JOIN megatest_comments AS c ON (c.comment_note = n.note_id)
GROUP BY note_id
LIMIT 0, 1000
[SQL] pobierz, plaintext 

Pokaż rekordy 0 - 999 (1 000 wszystkich, Wykonanie zapytania trwało 0.0111 sekund(y))

Sposób 3 na 2000 rekordów:

Pokaż rekordy 0 - 1999 (2 000 wszystkich, Wykonanie zapytania trwało 0.0216 sekund(y))

Sposób 3 na 5000 i 10000 rekordów: phpmyadmin zwraca pustą stronę.

Teraz przetestujemy sposób 1, wybierając id rekordów z notek (załóżmy, że jest to pole ilości komentarzy)

Sposób 1, 100 rekordów:

[SQL] pobierz, plaintext 
SELECT note_id FROM `megatest_notes` LIMIT 0, 1000
[SQL] pobierz, plaintext 

Pokaż rekordy 0 - 99 (100 wszystkich, Wykonanie zapytania trwało 0.0015 sekund(y))

Sposób 1, 500 rekordów:
phpmyadmin zwraca pustą stronę.

Wniosek? Chyba będę używał sposobu nr 3, bo nie przewiduję aż tak wielkiej bazy na jakiej testowałem moje przemyślenia.

Jak się do tego ustosunkujecie? Jakieś rady?

Dzięki za odpowiedzi, Athlan

[NuLL]

A moze tak cache zamiast robic dziwne rzeczy ?

[Athlan]

A moze tak cache zamiast robic dziwne rzeczy ?

Oprócz tego co piszę w powyższych postach będzie cache uaktualniane co 5minut lub przy akcji np edycji notki albo usunieciu komenta.

Zaznaczam, że nie mam jednego bloga tylko serwis blogowy

[SongoQ]

To moze ja sie wypowiem troche w tej kwestii. Podales 3 przyklady wykorzystania, jesli chodzi o optymalne rozwiazanie i ilosc komentarzy do posta to nie wiem jak bys kombinowal to 1 przykad jest najbardziej optymalny - dlaczego? Wyciagasz 1 rekord jako PK z 1 tabeli gdzie masz juz 1 wartosc, opleracje zewnetrznych zlaczen jak i agregacji sa calkowicie pomijane.

Co do przykladu nr 3 warto podkreslic ze na optymalizacje wchodzi kilka czynnikow. 1 to index na pola zlaczeniowe, 2 index na pole uzywane w sortowaniu.

Co do stwierdzenia ze przy duzych danych baza bedzie zamulac to pomysl ma szybciej dzialac? Przeciez LEFT JOIN to odczytanie krotek z jednej tabeli a nastepnie w petli dolanczanie do 2 tabeli a nastepnie agregacja sortowania i inne cuda, wiec co szybciej jest wykonac 10x petle czy powiedzmy 10 mil? Wazne w bazie sa indeksy - to tak jak w ksiazce otwierasz spis tresci i wiesz jaki temat Cie interesuje i jaka strone chesz otworzyc bez czytania kazdej strony.

Co do Twoich testow to wsumie wykonywales na malej ilosci danych.

Jesli chcesz dobrze operowac cache w bazie danych to takie narzedzie jak bind jest Ci pomocne. Mowie tutaj o cache stringa zapytan w bazie. Kiedys o tym juz pisalem, po co sie stosuje prepare i bind.

Co do cache juz w samym php to tez ma sens jesli nawet cache mial by sie oswierzac 1 na 10 zadan.

[Athlan]

@SongoQ, dzięki za wypowiedź

Dobrze, że zwróciłeś uwagę na indeksy, to akurat robie już przy samym projektowaniu bazy obmyślając każdy przypadek użycia jakiegokolwiek z pól w ewentualnych featurach. Indeksy mają duży wpływ na szybkość działania baz danych.

Wracając już do mojego problemu. Razem z Bastion'em i Envp rozwiązałem ten problem w bardzo prosty sposób. Połączmy sposoby 1 i 3. Co nam to da? Nie liczymy rekordów za każdym razem, a zapisujemy je w polu. Dlaczego akurat tak?

Załóżmy że zapytanie dodania komentarza nie wypaliło bo cośtam. Nawet jeśli sprawdzałbym, czy zapytanie się powiodło tutaj również mogło coś niewyaplić. Tak czy siak dodaje notes_comments_count = notes_comments_count + 1. Żle! Przy takiej akcji mogę wywołać metodę w modelu która podliczy ile faktycznie mam komentarzy przypisanych do tej notki i (jeżeli wartość się różni) zapisać wynik w pole notes_comments_count.

Myślę, że jest to najlepsze wyjście, podkreślam kiedy warto go użyć:
- przy dodawaniu newsa
- edycji
- usuwaniu
- wszelkim zmianom statusu (aktywny/nieaktywny na stronie)

Pozdrawiam i dzięki za dyskusję, Athlan

[dr_bonzo]

Załóżmy że zapytanie dodania komentarza nie wypaliło bo cośtam. Nawet jeśli sprawdzałbym, czy zapytanie się powiodło tutaj również mogło coś niewyaplić. Tak czy siak dodaje notes_comments_count = notes_comments_count + 1. Żle! Przy takiej akcji mogę wywołać metodę w modelu która podliczy ile faktycznie mam komentarzy przypisanych do tej notki i (jeżeli wartość się różni) zapisać wynik w pole notes_comments_count.

eee? A transakcje?

[SongoQ]

Dokladnie od tego sa transakcje, nie ma takiej mozliwosci ze sie cos wywali tutaj doda a tutaj nie. Baza danych baz transakcji to zwykly zbior danych a tak nie jest. Odnosnie Twojego problemu padla post ze najlepiej triggera zrobic. Przy swoich projektach staram sie wszystkie takie operacje wlasnie na triggerach robic 1 zapytanie do bazy i masz juz mase operacji zalatwionych.