Dywagacje nt. indeksów

Zahaczyłem o ten blog http://blog.mwojcik.pl/2009/07/06/mysql-indeksy-i-klucze-obce/

1. Czy to prawda ? Korzystam z MySQL 5.5

CREATE INDEX name_surname ON players (name, surname)

Indeks wykorzystany będzie, gdy będziemy przeszukiwać dane po polu name, bądź jednocześnie po polu name jak i surname – nie będzie tak, gdy wyszukujemy dane tylko przez sprawdzenie nazwiska – to zapytanie nie wykorzysta indeksu:

2. Druga sprawa: mam tabelę przechowującą wartości liczbowe pewnych pomiarów w pewnych lokalizacjach, w niej 1mln rekordów. Składa się z 4 kluczy głównych (ID_Lokalizacji, ID_Pomiaru, Id_Dnia, Godzina) oraz kolumna Wartosc typu Decimal.

Musiałem dołożyć do tej tabeli dwa dodatkowe pola typu Decimal(15,4) oraz jedno TinyInt i DateTime. I co się okazało ? Otóż pobierając z kolumny Wartosc, czas zapytań się zwiększył kilkukrotnie !. Dla mnie jest to dziwne - fakt że szersza krotka, ale selektuje wyłącznie po kluczach głównych, więc czas wykonania powinien być tylko minimalnie większy. Gdy usunę te dodatkowe pola, zapytania się wykonują szybko (więc to jest na 100% przyczyna).
Nałożyłem Indeks na pole Wartosc, ale i tak wolniej to działa, niż przed dołożeniem tych 4 kolumn o ktorych pisalem. Nie rozumiem, czemu spowodowało to takie zwolnienie (około 4-krotne).

1. tak
2. pewnie trzeba przeliczyć statystyki. Najprościej dropnij indeks i załóż go jeszcze raz (pojęcia nie mam czy mysl ma coś do przeliczenia/odbudowania statystyk). BTW zobacz sobie co pokazuje query plan (http://dev.mysql.com/doc/refman/5.0/en/explain.html)

Genialne przyspieszenie dostał gdy nałożyłem klucz główny na kolumnę Wartosc.
Tylko, że to tak brzydko wg mnie...

LOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOL

Byłbyś łaskaw rozwinąć swoją wypowiedź ? Nie widzę w tym co pisałem, nic do śmiechu.

Klucz główny jest jednocześnie indeksem unikalnym.

Wiem.
Ale jakoś wydajnościowo jest zdecydowana różnica pomiędzy gdy ustawię index a primary.
Nie wiem czy to wina 5.5, muszę potestować na wersji 5.1

Ciężko mi to sprawdzać - nakładanie indeksu trwa parę minut. Zdejmowanie, zakładanie - wyniki za każdym razem wychodzą inne, pomimo że testuje z SQL_NO_CACHE.

Więc oprę się na teorii - czy indeks na agregowane funkcje ma sens ? Czyli SUM(Wartosc) AVG(Wartosc). To czy indeks coś da na kolumnę wartość (wyszukuję po atrybutach klucza w wyrażeniu WHERE) ?

Nie, indeksu nie zakłada się na pola agregowane, a jedynie na pola po których WHERE-ujemy oraz często JOIN-uejmy i sortujemy.

hmm, czym jest w ogóle index.
Indeksy mają tą swoją miłą cechę, ze środowisko bazy danych (na pewno mssql) dba o to, aby leżało na dysku nie za bardzo rozpieprzone i dające się łatwo i szybko zebrać. Więc stąd ten narzut po każdej modyfikacji danych w indeksowanych polach.
Za to jak zrobię
"select id, wartość from tabela"
i obie kolumny leżą na indeksach, albo wręcz nawet należą do tego samego indeksu, wtedy myk myk, parę machnięć głowicą i dane zassany. Problem powstaje wtedy, kiedy do listy select dodam pole z poza indeksu. Wszystko pięknie, przefiltrowanie, wyselectowanie właściwych rekordów potrwa w mig, ale dobranie pozostałych danych, może się wiązać z pozbieraniem ich z całego dysku - nie są indeksowane - leżą gdzie głowica poniesie. O ile siedząc lokalnie nad bazą i molestując ją nawet intensywnie, to i tak szczęśliwie zwykle udaje się zapisać dane tak, że robiąc selecta bez żadnych sortowań, dostajemy kolejność zgodną z insertowaniem, lub akurat przypadkowo użytym indeksem. Tak jest w mssql.

Zawsze wydawało mi się że w indeksach zapisywane są pary (klucz -> adres w tabeli). A tu nagle dowiaduję się, że SELECT WYPISUJE dane bezpośrednio z indeksów...

Chyba normalne są sytuacje, gdzie nie ma potrzeby dobierania się do tabeli, i wystarczy przeczytać sam index?
Mnie natomiast zawsze się wydawało że można zmusić bazę danych, aby dbała o swoje indeksy, i zapisywała dane wskazywane przez indeksy w taki sposób, aby ich pobranie z pliku wiązało się z jak najmniejszą ilością odczytanych stron. Mylę się gdzieś?

Dane w tabeli też mogą leżeć fizycznie na dysku obok siebie, jeżeli masz na tabeli tzw. indeks klastrowany, w takim wypadku rekordy na dysku są uporządkowane wg pól tego indeksu. Z tego też powodu powinno się zakładać jeden porządny unikalny indeks klastrowany na zestawie pól jednoznacznie identyfikujących krotkę, zamiast bezsensownych indeksów na polu 'wartosc'. W takim wypadku jeden indeks klastrowany przyspiesza nam wszystkie zapytania typu SELECT pole_nieindeksowane, pole_nieindeksowane FROM tabela_z_kluczem_klastrowanym.

Indeks to lista odnośników gdzie dane się znajdują a nie układanie samych danych fizycznie na dysku. To coś jak spis treści.

MiL napisał(a)

Indeks to lista odnośników gdzie dane się znajdują a nie układanie samych danych fizycznie na dysku. To coś jak spis treści.

Tak zwykły indeks to osobna struktura w bazie, przypominająca indeks w książce. Ja pisałem o indeksie klastrowanym, który odpowiada za fizyczne ułożenie obok siebie rekordów na dysku.

Co do indexow to w MSSQL standardem jest stosowanie techniki zwanej index covering.
Polega ona na wyciąganiu danych bezposrednio z indexow.
Najlepiej gdy na polach zlaczen mamy indexy (unikamy zlaczen typu left), a sytaucja jest idealna wrecz gdy mamy index klastrowy unikalny :)