MySQL i NoSQL: Pomóż mi wybrać właściwy

Powinieneś przeczytać poniżej i dowiedzieć się trochę o zaletach dobrze zaprojektowanej tabeli innodb i jak najlepiej korzystać z klastrowych indeksów - dostępnych tylko z innodb !

Http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

Http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

Następnie Zaprojektuj Swój system według następującego uproszczonego przykładu:

Przykład schemat (uproszczony)

Ważne jest to, że tabele używają silnika innodb, a klucz podstawowy tabeli wątków nie jest już pojedynczym kluczem auto_incrementującym, ale złożonym klastrem opartym na kombinacji forum_id i thread_id. np.

threads - primary key (forum_id, thread_id)

forum_id    thread_id
========    =========
1                   1
1                   2
1                   3
1                 ...
1             2058300  
2                   1
2                   2
2                   3
2                  ...
2              2352141
...

Każdy wiersz forum zawiera licznik o nazwie next_thread_id (unsigned int), który jest utrzymywany przez wyzwalacz i zwiększa się za każdym razem, gdy wątek jest dodawany do danego forum. Oznacza to również, że możemy przechowywać 4 miliard wątków na forum, a nie łącznie 4 miliardy wątków, jeśli używa się pojedynczego klucza głównego auto_increment dla thread_id.

forum_id    title   next_thread_id
========    =====   ==============
1          forum 1        2058300
2          forum 2        2352141
3          forum 3        2482805
4          forum 4        3740957
...
64        forum 64       3243097
65        forum 65      15000000 -- ooh a big one
66        forum 66       5038900
67        forum 67       4449764
...
247      forum 247            0 -- still loading data for half the forums !
248      forum 248            0
249      forum 249            0
250      forum 250            0

select * from threads where thread_id = y;

Musisz zrobić:

select * from threads where forum_id = x and thread_id = y;

Jednak Twój kod aplikacji powinien być świadomy, które forum przegląda użytkownik, więc nie jest to do końca trudne do zaimplementowania-Zapisz aktualnie przeglądany identyfikator forum_id w sesji zmienne lub ukryte pole formularza itp...

Oto schemat uproszczony:

drop table if exists forums;
create table forums
(
forum_id smallint unsigned not null auto_increment primary key,
title varchar(255) unique not null,
next_thread_id int unsigned not null default 0 -- count of threads in each forum
)engine=innodb;


drop table if exists threads;
create table threads
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
reply_count int unsigned not null default 0,
hash char(32) not null,
created_date datetime not null,
primary key (forum_id, thread_id, reply_count) -- composite clustered index
)engine=innodb;

delimiter #

create trigger threads_before_ins_trig before insert on threads
for each row
begin
declare v_id int unsigned default 0;

  select next_thread_id + 1 into v_id from forums where forum_id = new.forum_id;
  set new.thread_id = v_id;
  update forums set next_thread_id = v_id where forum_id = new.forum_id;
end#

delimiter ;

Być może zauważyłeś, że dodałem reply_count jako część klucza głównego, co jest nieco dziwne, ponieważ kompozyt (forum_id, thread_id) jest unikalny sam w sobie. Jest to tylko optymalizacja indeksu, która zapisuje niektóre operacje wejścia / wyjścia, gdy wykonywane są zapytania używające reply_count. Więcej informacji na ten temat można znaleźć w 2 linkach powyżej.

Przykładowe zapytania

Wciąż Ładuję dane do mojego przykładu stoły i do tej pory mam załadowany ok. 500 milionów wierszy (o połowę mniej niż Twój system). Po zakończeniu procesu ładowania powinienem spodziewać się około:

250 forums * 5 million threads = 1250 000 000 (1.2 billion rows)

Celowo sprawiłem, że niektóre fora zawierają ponad 5 milionów wątków, na przykład forum 65 mA 15 milionów wątków:

forum_id    title   next_thread_id
========    =====   ==============
65        forum 65      15000000 -- ooh a big one

Query runtimes

select sum(next_thread_id) from forums;

sum(next_thread_id)
===================
539,155,433 (500 million threads so far and still growing...)

Pod innodb sumowanie next_thread_ids, aby dać całkowitą liczbę wątków, jest znacznie szybsze niż zwykle:

select count(*) from threads;

Ile wątków forum 65:

select next_thread_id from forums where forum_id = 65

next_thread_id
==============
15,000,000 (15 million)

Znowu jest to szybsze niż zwykle:

select count(*) from threads where forum_id = 65

Ok teraz wiemy, że mamy do tej pory około 500 milionów wątków, a forum 65 mA 15 milionów wątków-zobaczmy jak będzie wyglądał schemat:)

select forum_id, thread_id from threads where forum_id = 65 and reply_count > 64 order by thread_id desc limit 32;

runtime = 0.022 secs

select forum_id, thread_id from threads where forum_id = 65 and reply_count > 1 order by thread_id desc limit 10000, 100;

runtime = 0.027 secs

Wygląda dość wydajny dla mnie-więc jest to pojedyncza tabela z 500 + milionów wierszy (i rośnie) z zapytaniem, które obejmuje 15 milionów wierszy w 0.02 sekundy (podczas gdy pod obciążeniem !)

Dalsze optymalizacje

Te include:

• Partycjonowanie według zakresu

• Sharding

• Rzucanie w niego pieniędzmi i sprzętem

Itd...

Mam nadzieję, że ta odpowiedź okaże się pomocna:)