Jak przyspieszyć zapytania na dużej tabeli 220 milionów wierszy (dane 9 gig)?

Problem:

Mamy witrynę społecznościową, w której członkowie mogą się wzajemnie oceniać pod kątem zgodności lub dopasowania. Ta user_match_ratingstabela zawiera ponad 220 milionów wierszy (9 gig danych lub prawie 20 gig indeksów). Zapytania do tej tabeli rutynowo pojawiają się w slow.log (próg> 2 sekundy) i są najczęściej rejestrowanymi wolnymi zapytaniami w systemie:

Query_time: 3  Lock_time: 0  Rows_sent: 3  Rows_examined: 1051
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 395357 group by rating;"

Query_time: 4  Lock_time: 0  Rows_sent: 3  Rows_examined: 1294
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 4182969 group by rating;"

Query_time: 3  Lock_time: 0  Rows_sent: 3  Rows_examined: 446
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 630148 group by rating;"

Query_time: 5  Lock_time: 0  Rows_sent: 3  Rows_examined: 3788
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 1835698 group by rating;"

Query_time: 17  Lock_time: 0  Rows_sent: 3  Rows_examined: 4311
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 1269322 group by rating;"

Wersja MySQL:

• wersja protokołu: 10
• wersja: 5.0.77-log
• wersja bdb: Sleepycat Software: Berkeley DB 4.1.24: (29 stycznia 2009)
• wersja kompiluj maszynę: x86_64 wersja_kompilator_os: redhat-linux-gnu

Informacje o stole:

SHOW COLUMNS FROM user_match_ratings;

Daje:

╔═══════════════╦════════════╦════╦═════╦════════╦════════════════╗
║ id            ║ int(11)    ║ NO ║ PRI ║ NULL   ║ auto_increment ║
║ rater_user_id ║ int(11)    ║ NO ║ MUL ║ NULL   ║                ║
║ rated_user_id ║ int(11)    ║ NO ║ MUL ║ NULL   ║                ║
║ rating        ║ varchar(1) ║ NO ║     ║ NULL   ║                ║
║ created_at    ║ datetime   ║ NO ║     ║ NULL   ║                ║
╚═══════════════╩════════════╩════╩═════╩════════╩════════════════╝

Przykładowe zapytanie:

select * from mutual_match_ratings where id=221673540;

daje:

╔═══════════╦═══════════════╦═══════════════╦════════╦══════════════════════╗
║ id        ║ rater_user_id ║ rated_user_id ║ rating ║ created_at           ║
╠═══════════╬═══════════════╬═══════════════╬════════╬══════════════════════╣
║ 221673540 ║ 5699713       ║ 3890950       ║ N      ║ 2013-04-09 13:00:38  ║
╚═══════════╩═══════════════╩═══════════════╩════════╩══════════════════════╝

Indeksy

Tabela ma skonfigurowane 3 indeksy:

1. pojedynczy indeks włączony rated_user_id
2. indeks złożony na rater_user_idicreated_at
3. indeks złożony na rated_user_idirater_user_id

pokaż indeks z user_match_ratings;

daje:

╔════════════════════╦════════════╦═══════════════════════════╦══════════════╦═══════════════╦═══════════╦═════════════╦══════════╦════════╦═════════════════════════╦════════════╦══════════════════╗
║ Table              ║ Non_unique ║ Key_name                  ║ Seq_in_index ║ Column_name   ║ Collation ║ Cardinality ║ Sub_part ║ Packed ║ Null                    ║ Index_type ║ Comment          ║
╠════════════════════╬════════════╬═══════════════════════════╬══════════════╬═══════════════╬═══════════╬═════════════╬══════════╬════════╬═════════════════════════╬════════════╬══════════════════╣
║ user_match_ratings ║ 0          ║ PRIMARY                   ║ 1            ║ id            ║ A         ║ 220781193   ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index1 ║ 1            ║ rater_user_id ║ A         ║ 11039059    ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index1 ║ 2            ║ created_at    ║ A         ║ 220781193   ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index2 ║ 1            ║ rated_user_id ║ A         ║ 4014203     ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index2 ║ 2            ║ rater_user_id ║ A         ║ 220781193   ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index3 ║ 1            ║ rated_user_id ║ A         ║ 2480687     ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
╚════════════════════╩════════════╩═══════════════════════════╩══════════════╩═══════════════╩═══════════╩═════════════╩══════════╩════════╩═════════════════════════╩════════════╩══════════════════╝

Nawet przy indeksach zapytania te są wolne.

Moje pytanie:

Czy rozdzielenie tej tabeli / danych do innej bazy danych na serwerze, która ma wystarczającą ilość pamięci RAM do przechowywania tych danych w pamięci, przyspieszyłoby te zapytania? Czy w ogóle istnieje coś, co można skonfigurować w tabelach / indeksach, aby usprawnić te zapytania?

Obecnie mamy 16 GB pamięci; zastanawiamy się jednak nad uaktualnieniem istniejącej maszyny do 32 GB lub dodaniem nowej maszyny z przynajmniej taką ilością, być może także dysków półprzewodnikowych.

Myśli na ten temat, rzucane w losowej kolejności:

• Oczywistym indeks dla tego zapytania jest: (rated_user_id, rating). Kwerenda, która pobiera dane tylko dla jednego z miliona użytkowników i potrzebuje 17 sekund, robi coś złego: odczytywanie z (rated_user_id, rater_user_id)indeksu, a następnie odczytywanie z tabeli wartości (setek do tysięcy) dla ratingkolumny, jak ratingto nie ma w żadnym indeksie. Tak więc zapytanie musi odczytać wiele wierszy tabeli, które znajdują się w wielu różnych lokalizacjach dysków.

• Zanim zaczniesz dodawać liczne indeksy w tabelach, spróbuj przeanalizować wydajność całej bazy danych, całego zestawu wolnych zapytań, ponownie sprawdź wybór typów danych, używanego silnika i ustawień konfiguracji.

• Zastanów się nad przejściem na nowszą wersję MySQL, 5.1, 5.5 lub nawet 5.6 (także: wersje Percona i MariaDB.) Kilka korzyści, ponieważ błędy zostały poprawione, optymalizator ulepszony i możesz ustawić niski próg dla wolnych zapytań na mniej niż 1 sekundę (jak 10 milisekund). Dzięki temu uzyskasz znacznie lepsze informacje o wolnych zapytaniach.

• Wybór typu danych ratingjest dziwny. VARCHAR(1)? Dlaczego nie CHAR(1)? Dlaczego nie TINYINT? Pozwoli ci to zaoszczędzić trochę miejsca, zarówno w tabeli, jak i w indeksach, które (będą) zawierać tę kolumnę. Kolumna varchar (1) wymaga jeszcze jednego bajta ponad char (1), a jeśli są one utf8, kolumny (var) char będą wymagały 3 (lub 4) bajtów zamiast 1 (tinyint).

Obsługiwałem stoły dla rządu niemieckiego z czasami 60 milionami rekordów.

Mieliśmy dużo takich stołów.

Musieliśmy znać wiele razy całkowitą liczbę wierszy z tabeli.

Po rozmowie z programistami Oracle i Microsoft nie byliśmy tak szczęśliwi ...

Tak więc my, grupa programistów baz danych, zdecydowaliśmy, że w każdej tabeli jest rekord, zawsze jeden rekord, w którym przechowywane są łączne liczby rekordów. Zaktualizowaliśmy ten numer, w zależności od wierszy INSERT lub DELETE.

Próbowaliśmy na wszystkie inne sposoby. To zdecydowanie najszybszy sposób.

Używamy tej metody od 1998 roku i nigdy nie mieliśmy niewłaściwej liczby wierszy we wszystkich naszych wielu milionach tabel rekordów.

Spróbuję podzielić na typy ocen, takie jak:

Wzajemne dopasowanie_ratings_N, Wzajemne dopasowanie_ratings_S itp.

Powinieneś wykonać zapytanie dla każdego typu, ale być może jest to szybsze niż w drugą stronę. Spróbuj.

Zakłada się, że masz ustaloną liczbę typów ocen i że nie potrzebujesz tej tabeli do innych zapytań, które byłyby najgorsze w przypadku tej nowej struktury.

W takim przypadku powinieneś poszukać innego podejścia lub zachować dwie kopie tabeli (początkowej i podzielonej na partycje), jeśli jest to przystępne cenowo pod względem miejsca i łatwości konserwacji (lub logiki aplikacji).