Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Zakladanie indeksow
Forum PHP.pl > Forum > Bazy danych > MySQL
gazelek
13.04.2010, 20:13:12
Po raz pierwszy mam do czynienia z dosc spora baza danych (500 tys rekordow). Mam skrypt, ktory w petli wykonuje zapytanie (okolo 150-200 razy na jedno wywolanie skryptu)

[SQL] pobierz, plaintext 
  1. SELECT minimum,maksimum,kurs_zamkniecia FROM notowanie WHERE indeks_id = '$idIndeksu' AND data<='$data' LIMIT 2
[SQL] pobierz, plaintext


Zalozylem indeks w tabeli notowanie, typu BTREE na kolumny indeks_id i data, jednak nie zaobserwowalem przyspieszenia wykonania skryptu

Cytat
Czas wykonania skryptu: 88.978815078735s


Jest to czas porownywalny z poprzednim czasem wykonania skryptu. Czy cos jeszcze da sie zrobic w celu przyspieszenia wykonania skryptu?

Jesli podalem zbyt malo szczegolow, to prosze o informacje smile.gif
blooregard
13.04.2010, 20:32:08
Zerknij tutaj:
http://di.com.pl/porady/25700,0,Porada_MyS..._aplikacji.html
gazelek
13.04.2010, 21:09:46
Przykladowe zapytanie

[SQL] pobierz, plaintext 
  1. SELECT * FROM notowaniee WHERE indeks_id=1 AND DATA<=1189302400 LIMIT 2
[SQL] pobierz, plaintext


trwa 0.9271 sekundy. Przeczytalem artykul z linka, sprawdzilem describe'm jak wyglada sytuacja z kluczami. Dla powyzszego zapytania sprawdzanych jest 2292 rekordy.

W skrypcie wykonywany jest tez update (badalem czas wykonania, jest na poziomie 0,14 s - czyli dla 200 rekordow same update'y trwaja 28 sekund).

W przypadku update'a rowniez w warunku znajduja sie tylko pola indeks_id i data, wiec do tej sytuacji odnosi sie jeden i ten sam indeks.

Tak wyglada zapytanie tworzace tabele:
[SQL] pobierz, plaintext 
  1. CREATE TABLE IF NOT EXISTS `notowaniee` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `indeks_id` int(11) NOT NULL,
  4.   `kurs_otwarcia` float(8,2) NOT NULL,
  5.   `minimum` float(8,2) NOT NULL,
  6.   `maksimum` float(8,2) NOT NULL,
  7.   `kurs_zamkniecia` float(8,2) NOT NULL,
  8.   `wolumen` int(7) NOT NULL,
  9.   `data` int(11) NOT NULL,
  10.   `trend` enum('nd','+','-') collate utf8_polish_ci NOT NULL,
  11.   `srednia1` float(8,2) NOT NULL,
  12.   `srednia2` float(8,2) NOT NULL,
  13.   `srednia3` float(8,2) NOT NULL,
  14.   `adxDecyzja` enum('kupno','sprzedaz','czekaj') collate utf8_polish_ci NOT NULL,
  15.   `adx` float(6,2) NOT NULL,
  16.   PRIMARY KEY  (`id`),
  17.   KEY `indeksid_index` USING BTREE (`indeks_id`),
  18.   KEY `Index_4` USING BTREE (`data`,`indeks_id`)
  19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=491694 ;
[SQL] pobierz, plaintext


Zapytania wykonuja sie szybciej jesli w warunku podam id (pierwsze zapytanie wykonuje sie okolo 10 razy szybciej).


Poki co nie mam pomyslu jak rozwiazac problem, w tej chwili skrypt dziala a ja zaobserwowalem, ze po zalozeniu indeksow dziala on gorzej niz przed (wczesniej wykonywal sie w okolo 70s, teraz jest to juz 100s - a ilosc danych sie nie zmienila).
blooregard
13.04.2010, 21:24:23
Usuń te indeksy BTREE i załóż normalne indeksy na kolumny, z których pobierasz dane ( minimum,maksimum,kurs_zamkniecia).
Zobaczymy, co to da.
gazelek
13.04.2010, 21:51:24
Zalozylem trzy indeksy na kolumny maksimum, minimum, kurs_zamkniecia (czy moze mialem zalozyc jeden potrojny na wszystkie trzy)

Cytat
Czas wykonania skryptu: 102.5153901577s
Czas wykonania skryptu: 97.040512084961s


Czyli raczej niewiele to dalo. Ponizej struktura tabeli dotyczaca kluczy

[SQL] pobierz, plaintext 
  1. PRIMARY KEY  (`id`),
  2.   KEY `Index_4` (`kurs_zamkniecia`),
  3.   KEY `Index_2` (`minimum`),
  4.   KEY `Index_3` (`maksimum`)
[SQL] pobierz, plaintext


@blooregard: dzieki za pomoc, troche sie pogubilem przy tych indeksach i potrzebuje jakiegos wsparcia smile.gif

Edit: dalem sobie describe dla zapytania z powyzszego postu, ilosc przeszukiwanych rekordow wzrosla do 493tys.... ;/

Edit edit: Eureka! Zalozylem zwykly index na indeks_id i data i oto efekt:
Cytat
Czas wykonania skryptu: 2.2445020675659s


Dzieki za pomoc!
blooregard
13.04.2010, 22:13:43
A ja się zakręciłem smile.gif

Chodziło o indeks na kolumnę, której używasz w warunku WHERE, czyli właśnie indeks_id, ale zdążyłes sam już do tego dojść smile.gif

gazelek
14.04.2010, 16:17:31
Dalej mam problem smile.gif skrypt przyspieszyl, bo puscilem zapytania na danych, na ktorych wykonywal sie wczesniej. Symulacja przeszla jednak dalej i operuje na danych, ktorych wczesniej jeszcze nie brala. Mam wiec zapytanie:

[SQL] pobierz, plaintext 
  1. SELECT id,minimum,maksimum,kurs_zamkniecia FROM `notowaniee` WHERE indeks_id = '53' AND data<='1221436800' ORDER BY id ASC LIMIT 2
[SQL] pobierz, plaintext


ktore trwa 1.9007 sekund(y). W tabeli notowaniee zalozony jest jeden indeks na dwie kolumny (data i indeks_id). I co tu jeszcze zrobic, zeby uzyskac przyspieszenie?smile.gif
Mchl
14.04.2010, 16:29:13
Która to wersja MySQL?

Możesz podać wynik EXPLAIN?

[edit]

Wiesz co... zrobiłęm sobie tą tabelę, wpakowałem do niej ~600000 losowych wierszy i u mnie to zapytanie nie zajmuje nawet 0.001s...
Może serwer nie daje rady?
gazelek
14.04.2010, 17:25:39
Oto wynik dzialania EXPLAIN dla powyzszego zapytania
Cytat
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE notowaniee range Index_2 Index_2 8 NULL 2824 Using where; Using filesort


MySQL w wersji 5.0.37. Tabele uzywaja jako silnika InnoDB, czy to moze miec jakis wplyw?
Mchl
14.04.2010, 18:49:43
U siebie też dałem InnoDB

U ciebie jest Using filesort u mnie nie. Zrzuca tabelę na dysk, stąd takie opóźnienie.

Jak jest zdefiniowany Index_2 w końcu?

Pokaż co Ci się wyświetli jak puścisz
Kod
SHOW VARIABLES LIKE 'inno%';


gazelek
14.04.2010, 19:24:16
Wynik dzialania

Cytat
Variable_name Value
innodb_additional_mem_pool_size 2097152
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 15728640
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 10485760
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000


Zapytanie tworzace tabele:
[SQL] pobierz, plaintext 
  1. CREATE TABLE IF NOT EXISTS `notowaniee` (
  2.  /* tu kolumny, takie jak kilka postow wyzej */
  3.   PRIMARY KEY  (`id`),
  4.   KEY `Index_2` (`indeks_id`,`data`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=496647 ;
[SQL] pobierz, plaintext


Co do serwera - skrypt wykonuje sie u mnie na komputerze, mam 2x2.0 GHz i 4 Gb Ramu....

Obecnie czas wykonania dla 160 iteracji petli wynosi 180 sekund.
Mchl
14.04.2010, 19:37:35
innodb_buffer_pool_size masz ustawione na 15MB a ta tabela przy ~600000 rekordów zajmuje ponad 60MB. Nie mieści się po prostu w pamięci. Jak masz 4GB, to możesz spokojnie podbić ten paramter do 1GB.
gazelek
14.04.2010, 19:48:57
Dzieki, teraz dziala jak zloto smile.gif ~3s smile.gif potestuje jeszcze, ale mam nadzieje ze juz powinno byc dobrze. Pozdro!
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.