mysql rządzi? indeksy pokrywające

przeczytałem właśnie o pewnej funkcjonalności mysql'a o której wcześniej nie wiedziałem. w dodatku – której postgresql nie ma!
chodzi o indeksy pokrywające.
co to jest?
ogólna idea polega na tym, że silnik bazodanowy może wykorzystać do zwracania wartości wartości pobrane z indeksu a nie z tabeli.
kumacie coś z tego? pewnie nie. ja też nie. więc przykład.
mamy tabelkę:

# create table zakupy (id serial primary key, user_id int4, kwota int4);

piszę po postgresowemu, ale chodzi o pokazanie idei.
teraz.
często potrzebujemy zrobić zestawienie nt. łącznej sumy kwot zakupów użytkownika. czyli wynik zapytania:

select sum(kwota) from zakupy where user_id = <costam>

aby to przyspieszyć robimy indeks na pole user_id:

create index x on zakupy (user_id).

i jest lepiej.
system działa tak, że wyszukuje które rekordy w tabeli powinien wziąść pod uwagę (przy pomocy indeksu), potem je znajduje w tabeli, odczytuje, sumuje i zwraca.
proste.
ale wbrew pozorom mało wydajne.
w mysql'u jest coś takiego jak rzeczone indeksy pokrywające.
oznacza to, że jeśli zrobimy indeks:

create index x on zakupy (user_id, kwota).

to mysql użyje tego indeksu w dwóch celach:

  1. do znalezienia odpowiednich rekordów
  2. do pobrania kwot do zsumowania

na czym polega rewolucja? nie trzeba sięgać do tabeli by znaleźć dane!
szybkie. wydajne. zajebiste. tyle, że zżera trochę więcej miejsca na dysku. ale to jest tani zasób.
covering indices nie są domeną mysql'a. mają je też inne bazy. szybki searchmash pokazał, że na pewno są one obecne też w mssql'u (więc pewnie w sybase też). zgaduję, że oracle i db2 też je mają.
a czemu postgres nie? no cóż. temat był kilkukrotnie poruszany na liście pgsql-hackers, ale okazało się, że ze względu na mvcc sprawa jest mocno skomplikowana. i (na razie) nie ma. muszę przyznać, że jest to pierwsza rzecz jakiej (jako postgresowiec) zazdroszczę mysql'owi.

8 thoughts on “mysql rządzi? indeksy pokrywające”

  1. CREATE TABLE ZAKUPY ( ID NUMBER NOT NULL, USER_ID NUMBER NOT NULL, KWOTA NUMBER NOT NULL, CONSTRAINT ZAKUPY_PK PRIMARY KEY(ID));

    explain plan query: select sum(kwota) from zakupy where user_id=10;
    SELECT STATEMENT
    SORT(AGGREGATE)
    TABLE ACCESS(FULL)

    create index x on zakupy (user_id, kwota);

    explain plan query: select sum(kwota) from zakupy where user_id=10;
    SELECT STATEMENT
    SORT(AGGREGATE) INDEX(RANGE SCAN) VNULL.X ANALYZED
    (troche skrocone te plany).. ale jak widac Oracle tez nie robil TABLE ACCESS z indeksem X

  2. A co z zapytaniem.:
    select avg(kwota) from zakupy where user_id =

    Jakie agregaty pokrywa ten “indeks pokrywający”?

  3. sorry, może to nie było jasne. indeks pokrywający nie pokrywa agregatów.
    to działa trochę inaczej.
    załóżmy, że mamy zapytanie:
    select a from t where b = ?;
    i mamy indeks na t (b);
    system korzysta z indeksu by znaleźć odpowiednią krotkę w tabeli, potem wyszukuje ją w pliku tabeli i zwraca wartość a.
    jeśli mamy inkdesy pokrywające i index on t (b,a).
    to system pomija szukanie w danych tabeli – tylko od razu zwraca wartość “a” z krotki indeksu.
    dzięki temu szybsze jest wszystko. agregaty, proste selecty, wszystko.

  5. czyli pokrycie oznacza nałożenie indexów na przynajmniej 2 kolumny ?
    sory jesli nie zrozumiałem i zadałem banalne pytanie
    Łukasz

  6. @Łukasz:
    nie. indeks na wielu kolumnach to standarowa sprawa dostępna wszędzie.
    indeks pokrywający polega na tym, że silnik bazodanowy nie musi już sięgać do danych z tabeli by wykonać zapytanie i zwrócić dane, bo dane jakie ma zwrócić są gotowe w indeksie.

Comments are closed.