Jak działa indeksowanie baz danych?
Biorąc pod uwagę, że indeksowanie jest tak ważne, jak Twój zestaw danych zwiększa rozmiar, czy ktoś może wyjaśnić, jak indeksowanie działa na poziomie bazy danych-agnostic?
Aby uzyskać informacje na temat zapytań do indeksowania pola, sprawdź Jak indeksować kolumnę bazy danych .
10 answers
Dlaczego jest to potrzebne?
Gdy dane są przechowywane na dyskowych urządzeniach pamięci masowej, są one przechowywane jako bloki danych. Bloki te są dostępne w całości, co czyni je operacją atomic disk access. Bloki dyskowe są skonstruowane w taki sam sposób, jak listy połączone; oba zawierają sekcję danych, wskaźnik do lokalizacji następnego węzła (lub bloku) i oba nie muszą być przechowywane obok siebie.
Ze względu na fakt, że szereg rekordów można posortować tylko na jedno pole, możemy stwierdzić, że wyszukiwanie na polu, które nie jest posortowane, wymaga Wyszukiwania liniowego, które wymaga N/2 dostępu do bloków (średnio), gdzie N jest liczbą bloków, które obejmuje tabela. Jeśli to pole nie jest polem kluczowym (tzn. nie zawiera unikalnych wpisów), to cała przestrzeń tablespace musi być przeszukiwana w blokach dostępu N.
Podczas gdy w polu posortowanym można użyć wyszukiwania binarnego, które ma log2 N dostęp do bloków. Również ponieważ dane są sortowane pod warunkiem, że pole nie jest kluczowe, reszta tabeli nie musi być przeszukiwana w poszukiwaniu zduplikowanych wartości po znalezieniu wyższej wartości. Tym samym wzrost wydajności jest znaczny.
Czym jest indeksowanie?
Indeksowanie jest sposobem sortowania wielu rekordów na wielu polach. Utworzenie indeksu na polu w tabeli tworzy inną strukturę danych, która przechowuje wartość pola i wskaźnik do rekordu, do którego się odnosi. Ta struktura indeksu jest następnie sortowana, umożliwiając wyszukiwanie binarne na to.
Minusem indeksowania jest to, że te indeksy wymagają dodatkowego miejsca na dysku, ponieważ indeksy są przechowywane razem w tabeli za pomocą silnika MyISAM, plik ten może szybko osiągnąć limity rozmiaru bazowego systemu plików, jeśli wiele pól w tej samej tabeli jest indeksowanych.
Jak to działa?
Po pierwsze, zarysujmy przykładowy schemat tabeli bazy danych;
Field name Data type Size on disk id (Primary key) Unsigned INT 4 bytes firstName Char(50) 50 bytes lastName Char(50) 50 bytes emailAddress Char(100) 100 bytes
Uwaga : char został użyty zamiast varchar, aby umożliwić dokładny rozmiar na wartości dysku. Ta przykładowa baza danych zawiera pięć milionów wierszy i jest nieindeksowana. Wydajność kilku zapytań będzie teraz analizowana. Są to zapytania przy użyciu id (posortowane pole klucza) i jedno przy użyciu firstName (niesortowane pole bez klucza).
przykład 1 - sortowane vs niesortowane pola
Biorąc pod uwagę naszą przykładową bazę danych r = 5,000,000 rekordów o ustalonym rozmiarze dających długość rekordu R = 204 bajtów i są to przechowywany w tabeli przy użyciu silnika MyISAM, który używa domyślnego rozmiaru bloku B = 1,024 bajtów. Współczynnik blokowania tabeli wynosiłby bfr = (B/R) = 1024/204 = 5 rekordy na blok dysku. Całkowita liczba bloków wymaganych do przechowywania tabeli to N = (r/bfr) = 5000000/5 = 1,000,000 bloki.
Wyszukiwanie liniowe w polu id wymagałoby średnio N/2 = 500,000 dostępu do bloku, aby znaleźć wartość, biorąc pod uwagę, że pole id jest polem kluczowym. Ale ponieważ pole id jest również posortowane, wyszukiwanie binarne może być przeprowadzone wymagając średniej log2 1000000 = 19.93 = 20 bloku dostęp. Od razu widać, że jest to drastyczna poprawa.
Teraz pole firstName nie jest ani sortowane, ani nie jest polem klucza, więc wyszukiwanie binarne nie jest możliwe, ani wartości nie są unikalne, a zatem tabela będzie wymagała wyszukiwania do końca dla dokładnego dostępu do bloku N = 1,000,000. To właśnie ta sytuacja ma na celu skorygowanie indeksowania.
Biorąc pod uwagę, że rekord indeksu zawiera tylko zindeksowane pole i wskaźnik do oryginalnego rekordu, oznacza to, że będzie to mniejszy od rekordu multi-field, na który wskazuje. Tak więc sam indeks wymaga mniej bloków dyskowych niż oryginalna tabela, co wymaga mniej dostępu do bloków do iteracji. Schemat indeksu w polu firstName jest przedstawiony poniżej;
Field name Data type Size on disk firstName Char(50) 50 bytes (record pointer) Special 4 bytes
Uwaga: wskaźniki w MySQL mają długość 2, 3, 4 lub 5 bajtów w zależności od rozmiaru tabeli.
przykład 2 - indeksowanie
Biorąc pod uwagę naszą próbkę baza danych r = 5,000,000 rekordów o długości rekordu indeksu R = 54 bajtów i przy użyciu domyślnego rozmiaru bloku B = 1,024 bajtów. Współczynnik blokowania indeksu wynosiłby bfr = (B/R) = 1024/54 = 18 rekordy na blok dysku. Całkowita liczba bloków wymaganych do przechowywania indeksu to N = (r/bfr) = 5000000/18 = 277,778 bloki.
Teraz wyszukiwanie za pomocą pola firstName może wykorzystać indeks do zwiększenia wydajności. Pozwala to na binarne przeszukiwanie indeksu ze średnią log2 277778 = 18.08 = 19 dostępu do bloku. Aby znaleźć adres faktycznego rekord, który wymaga dalszego dostępu do bloku do odczytu, doprowadzając sumę do 19 + 1 = 20 dostępu do bloku, co jest dalekie od 1 000 000 dostępu do bloku wymaganego do znalezienia dopasowania firstName w tabeli nieindeksowanej.
Kiedy należy go stosować?
Biorąc pod uwagę, że tworzenie indeksu wymaga dodatkowego miejsca na dysku (277 778 bloków dodatkowych z powyższego przykładu, wzrost o ~28%), a zbyt wiele indeksów może powodować problemy wynikające z ograniczeń rozmiaru systemów plików, ostrożnie thought musi być użyty do wybrania właściwych pól do indeksowania.
Ponieważ indeksy są używane tylko w celu przyspieszenia wyszukiwania pasujących pól w rekordach, wynika z tego, że indeksowanie pól używanych tylko do wyjścia byłoby po prostu stratą miejsca na dysku i czasu przetwarzania podczas operacji wstawiania lub usuwania, a zatem powinno być unikane. Również biorąc pod uwagę charakter wyszukiwania binarnego, Kardynalność lub wyjątkowość danych jest ważna. Indeksowanie na polu o wartości cardinality of 2 podzieliłby dane na pół, podczas gdy 1000 cardinality zwróciłoby około 1000 rekordów. Przy tak niskiej cardinality skuteczność jest zredukowana do sortowania liniowego, a optymalizator zapytań uniknie użycia indeksu, jeśli cardinality jest mniejsze niż 30% liczby rekordów, skutecznie czyniąc indeks marnotrawstwem miejsca.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-10 10:40:54
Kiedy pierwszy raz to przeczytałem, bardzo mi to pomogło. Dziękuję.
Od tego czasu uzyskałem pewien wgląd w minusy tworzenia indeksów:
jeśli zapisujesz do tabeli (UPDATE LUB INSERT) z jednym indeksem, masz w systemie plików dwie operacje zapisu. Jeden dla danych tabeli i drugi dla danych indeksu (i uciekanie się do nich (i - jeśli są grupowane - uciekanie się do danych tabeli)). Jeśli tabela i indeks znajdują się na tym samym dysku twardym, to kosztuje więcej czasu. Tak więc tabela bez indeksu (sterty) , pozwala na szybsze operacje zapisu. (gdybyś miał dwa indeksy, skończyłbyś z trzema operacjami zapisu itd.)
Jednak definiowanie dwóch różnych lokalizacji na dwóch różnych dyskach twardych dla danych indeksowych i tabelowych może zmniejszyć / wyeliminować problem zwiększonego kosztu czasu. Wymaga to zdefiniowania dodatkowych grup plików z odpowiednimi plikami na pożądanych dyskach twardych i zdefiniowania lokalizacji tabeli / indeksu zgodnie z życzeniem.
Inny problem z indeksami polega na ich fragmentacji w czasie, gdy dane są wstawiane. REORGANIZE pomaga, musisz napisać procedury, aby to zrobić.
W niektórych scenariuszach sterta jest bardziej pomocna niż tabela z indeksami,
E. g: - Jeśli masz dużo rywalizujących zapisów, ale tylko jeden nocny odczyt poza godzinami pracy w celu raportowania.
Również rozróżnienie między indeksami klastrowymi i nieklastrowymi jest dość ważne.
Pomógł mi: - co robić w klastrze i nie klastrze / align = "left" /
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:47:36
Indeks jest tylko strukturą danych, która przyspiesza wyszukiwanie określonej kolumny w bazie danych. Struktura ta jest zwykle drzewem b lub tabelą haszującą, ale może to być dowolna inna struktura logiczna.
Aby uzyskać więcej informacji, polecam: Jak działają indeksy baz danych? Jak pomagają indeksy?
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-22 12:10:49
Klasyczny przykład "Indeks w księgach"
Rozważmy "książkę" liczącą 1000 stron, podzieloną na 100 sekcji, z których każda zawiera X stron.
Proste, co?
Teraz, bez strony indeksu, aby znaleźć konkretną sekcję zaczynającą się na literę "S", nie masz innej opcji niż skanowanie całej książki. i. e: 1000 stron
Ale z indeksem na początku, jesteś tam. I więcej, aby przeczytać konkretny dział, który ma znaczenie, wystarczy spojrzeć na strona indeksu, raz po raz, za każdym razem. Po znalezieniu pasującego indeksu możesz efektywnie przejść do sekcji, pomijając inne sekcje.
Ale wtedy, oprócz 1000 stron, będziesz potrzebował kolejnych ~10 stron, aby wyświetlić stronę indeksu, więc całkowicie 1010 stron.
Tak więc indeks jest oddzielną sekcją, która przechowuje wartości zindeksowanej kolumny + wskaźnik do zindeksowanego wiersza w posortowanej kolejności dla efektywnego wyszukiwania.
W szkołach rzeczy są proste, prawda? : P
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-10 11:14:17
Powiedzmy, że chcemy uruchomić zapytanie, aby znaleźć wszystkie dane pracowników, którzy nazywają się "Abc"?
SELECT * FROM Employee
WHERE Employee_Name = 'Abc'
Co by się stało bez indeksu?
Oprogramowanie bazy danych musiałoby dosłownie spojrzeć na każdy wiersz w tabeli pracowników, aby sprawdzić, czy nazwa pracownika dla tego wiersza to "Abc". A ponieważ chcemy, aby każdy rząd miał w sobie nazwę "Abc", nie możemy po prostu przestać szukać, gdy znajdziemy tylko jeden rząd o nazwie "Abc", ponieważ mogą być inne wiersze z nazwą Abc . Tak więc każdy wiersz aż do ostatniego wiersza musi być przeszukiwany – co oznacza, że tysiące wierszy w tym scenariuszu będzie musiało zostać zbadanych przez bazę danych, aby znaleźć wiersze o nazwie "Abc". To się nazywa Pełne skanowanie tabeli
Jak indeks bazy danych może pomóc w wydajności
Sens posiadania indeksu polega na przyspieszeniu zapytań poprzez zmniejszenie liczby rekordów / wierszy w tabeli, które należy zbadać. Na indeks jest strukturą danych (najczęściej drzewem B), która przechowuje wartości dla określonej kolumny w tabeli.
Jak działa indeks B-trees?
Powodem, dla którego drzewa B są najpopularniejszą strukturą danych dla indeksów, jest fakt, że są one wydajne w czasie - ponieważ wyszukiwanie, usuwanie i wstawianie mogą być wykonywane w czasie logarytmicznym. Innym ważnym powodem, dla którego drzewa B są częściej używane, jest to, że dane przechowywane w drzewie B mogą być sortowane. RDBMS zazwyczaj określa, która struktura danych jest faktycznie używana dla indeksu. Ale w niektórych scenariuszach z pewnymi systemami RDBMS możesz określić, jakiej struktury danych chcesz użyć w bazie danych podczas tworzenia samego indeksu.
Jak działa indeks tabel hash?
Powodem użycia indeksów hash jest to, że tabele hash są niezwykle wydajne, jeśli chodzi o Wyszukiwanie tylko wartości. Tak więc zapytania, które porównują równość z łańcuchem znaków, mogą pobierać wartości bardzo szybko, jeśli używają indeksu hash.
Na przykład, zapytanie, które omówiliśmy wcześniej, może skorzystać z indeksu hash utworzonego w kolumnie Employee_Name. Sposób działania indeksu hash polega na tym, że wartość kolumny będzie kluczem do tabeli hash, a rzeczywista wartość odwzorowana do tego klucza będzie tylko wskaźnikiem do danych wiersza w tabeli. Ponieważ tabela hash jest w zasadzie tablicą asocjacyjną, typowy wpis wyglądałby jak "Abc = > 0x28939", gdzie 0x28939 jest odniesieniem do wiersz tabeli, w którym Abc jest przechowywane w pamięci. Wyszukanie wartości takiej jak" Abc "w indeksie tabeli hashowej i odzyskanie odniesienia do wiersza w pamięci jest oczywiście o wiele szybsze niż skanowanie tabeli, aby znaleźć wszystkie wiersze o wartości" Abc " w kolumnie Nazwa_pracy.
Wady indeksu hash
Tabele Hash nie są sortowanymi strukturami danych i istnieje wiele typów zapytań, z którymi indeksy hash nie mogą nawet pomóc. Na przykład, załóżmy, że chcesz dowiedzieć się wszyscy pracownicy, którzy mają mniej niż 40 lat. Jak mogłeś to zrobić z indeksem hash table? Cóż, nie jest to możliwe, ponieważ tabela hash jest dobra tylko do wyszukiwania par wartości klucza-co oznacza zapytania, które sprawdzają równość
Co dokładnie znajduje się w indeksie bazy danych? Teraz wiesz, że indeks bazy danych jest tworzony na kolumnie w tabeli i że indeks przechowuje wartości w tej konkretnej kolumnie. Ale ważne jest, aby zrozumieć, że indeks bazy danych ma nie przechowuje wartości w innych kolumnach tej samej tabeli. Na przykład, jeśli utworzymy indeks w kolumnie Employee_Name, oznacza to, że wartości kolumn Employee_Age i Employee_Address nie są również przechowywane w indeksie. Gdybyśmy tylko przechowywali wszystkie pozostałe kolumny w indeksie, to byłoby to tak, jakby tworzyć kolejną kopię całej tabeli – która zajmowałaby zbyt dużo miejsca i byłaby bardzo nieefektywna.
Skąd baza danych wie, kiedy używać indeksu? Kiedy zapytanie takie jak "SELECT * FROM Employee WHERE Employee_Name =' Abc '" jest uruchamiane, baza danych sprawdzi, czy nie ma indeksu w kolumnie(kolumnach), które są pytane. Zakładając, że kolumna Employee_Name ma utworzony indeks, baza danych będzie musiała zdecydować, czy rzeczywiście sensowne jest użycie indeksu do znalezienia przeszukiwanych wartości – ponieważ istnieją pewne scenariusze, w których korzystanie z indeksu bazy danych jest mniej efektywne, a bardziej efektywne skanowanie całego stolik.
Jaki jest koszt posiadania indeksu bazy danych?
Zajmuje miejsce-a im większy stół, tym większy indeks. Kolejnym uderzeniem wydajności indeksów jest fakt, że za każdym razem, gdy dodajesz, usuwasz lub aktualizujesz wiersze w odpowiedniej tabeli, te same operacje będą musiały zostać wykonane na indeksie. Pamiętaj, że indeks musi zawierać takie same dane do minut, jak te, które znajdują się w kolumnach tabeli, które obejmuje indeks.
Ogólnie rzecz biorąc, indeks powinien być tworzony na TABELI tylko wtedy, gdy dane w zindeksowanej kolumnie będą często zadawane pytania.
Zobacz też
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:47:36
Prosty Opis!!!!!!!!!!
Indeks jest niczym innym jak strukturą danych, która przechowuje wartości dla określonej kolumny w tabeli. Indeks jest tworzony na kolumnie tabeli.
Przykład, mamy tabelę bazy danych o nazwie User z trzema kolumnami-Nazwa, wiek i adres. Załóżmy, że tabela użytkownika zawiera tysiące wierszy.
Załóżmy, że chcemy uruchomić zapytanie, aby znaleźć wszystkie szczegóły użytkowników o imieniu "John". Jeśli uruchomimy następujące zapytanie.
SELECT * FROM User
WHERE Name = 'John'
Oprogramowanie bazy danych musiałoby dosłownie spojrzeć na każdy wiersz w tabeli użytkownika, aby sprawdzić ,czy nazwa tego wiersza to 'John'. To zajmie dużo czasu.
W tym miejscu index pomaga nam "indeks jest używany do przyspieszania zapytań poprzez zmniejszenie liczby rekordów / wierszy w tabeli, które muszą zostać zbadane".
Jak utworzyć indeks
CREATE INDEX name_index
ON User (Name)
Indeks składa się z wartości kolumn (np.: John) z jednej tabeli, a wartości te są przechowywane w struktura danych.
więc teraz baza danych użyje indeksu, aby znaleźć pracowników o imieniu John, ponieważ indeks prawdopodobnie będzie posortowany alfabetycznie według nazwy użytkownika. A ponieważ jest posortowane, oznacza to, że wyszukiwanie nazwy jest o wiele szybsze, ponieważ wszystkie Nazwy zaczynające się na" J " będą obok siebie w indeksie!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-04 10:29:34
Tylko szybka sugestia.. Ponieważ indeksowanie kosztuje dodatkowe zapisy i miejsce na dysku, więc jeśli aplikacja wymaga większej liczby operacji wstawiania/aktualizacji, możesz użyć tabel bez indeksów, ale jeśli wymaga większej liczby operacji pobierania danych, powinieneś wybrać tabelę zindeksowaną.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-14 06:44:51
Po prostu pomyśl o indeksie bazy danych jako indeksie książki. Jeśli masz książkę o psach i chcesz znaleźć informacje na temat powiedzmy, owczarki niemieckie, można oczywiście przerzucić przez wszystkie strony książki i znaleźć to, czego szukasz, ale to oczywiście jest czasochłonne i nie bardzo szybko. Inną opcją jest to, że możesz po prostu przejść do sekcji indeks książki, a następnie znaleźć to, czego szukasz, używając nazwy podmiotu, którego szukasz ( w tym przypadku Niemiecki Pasterzy), a także patrząc na numer strony, aby szybko znaleźć to, czego szukasz. W Bazie Danych, numer strony jest określany jako wskaźnik, który kieruje bazę danych do adresu na dysku, na którym znajduje się encja. Używając tej samej analogii owczarka niemieckiego, możemy mieć coś takiego ("Owczarek niemiecki", 0x77129), gdzie 0x77129 jest adresem na dysku, gdzie dane wiersza dla owczarka niemieckiego są przechowywane.
W skrócie, indeks jest strukturą danych, która przechowuje wartości dla konkretną kolumnę w tabeli, aby przyspieszyć wyszukiwanie zapytań.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-21 17:16:02
Indeks SQL jest czymś związanym z przyspieszeniem wyszukiwania w bazie danych SQL. Index pozwala programiście na bardzo szybkie pobieranie danych z bazy danych. Załóżmy, że jesteś studentem lub czytelnikiem książek. Twoja książka zawiera 50 000 stron. Pierwszego dnia czytasz jakiś temat " ABC "następnego dnia chcesz przeczytać jakiś inny temat "xyz". nigdy nie będziesz przechodzić ręcznie strona po stronie. To, co zrobisz w tej sytuacji, to użycie indeksu książki, aby spojrzeć na konkretny temat, a następnie przejść bezpośrednio do tematu. Indeks zapisany masz dużo czasu na szukanie tematu. Podobnie w SQL index, Index pozwala bardzo szybko przeszukiwać miliony rekordów z bazy danych.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-15 10:17:05
Indeks bazy danych jest strukturą danych, która poprawia szybkość operacji wyszukiwania danych w tabeli bazy danych kosztem dodatkowych zapisów i miejsca do przechowywania w celu utrzymania struktury danych indeksu. Indeksy służą do szybkiego lokalizowania danych bez konieczności przeszukiwania każdego wiersza tabeli bazy danych przy każdym dostępie do tabeli bazy danych. Indeksy mogą być tworzone przy użyciu jednej lub kilku kolumn tabeli bazy danych, zapewniając podstawę zarówno do szybkiego losowego wyszukiwania, jak i efektywnego dostępu do uporządkowanych records.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-09 05:33:17