Tabela-macierz, B duża ilość kolumn / b duża ilość tabel Opcje

[wojckr]

[JESLI NIE MASZ CZASU TO GłOWNE PYTANIE ZNAJDUJE SIę POD TEKSTEM]

Witam,
będę potrzebował macierz odległości pomiędzy jakimiś punktami w Polsce. Pewnie każdy zna takie macierze odległości między miastami (można sprawdzić w takiej "tabelce" jaka jest odległość np. między Wrocławiem a Łodzią lub między Gdańskiem a Szczecinem) - tu jednak sprawa jest dosyć prosta, bo obejmuje zazwyczaj około 20 największych miast. Moich punktów będzie znacznie więcej (nawet kilkadziesiąt tysięcy albo może nawet więcej). I tu mam pytanie:
Czy zawarcie takiej macierzy w tabeli w mySql powiedzie się? Czy to się nie rozsypie? Jaka jest ewentualnie największa możliwa ilość kolumn przy takiej samej co ilość kolumn ilości wierszy? A może w ogóle to nie jest dobry pomysł i lepiej byłoby to zrobić inaczej? Bo jeszcze ewentualnie można zrobić dla każdego obiektu (miasta) zrobić osobną tabelę, ale jeśli miałoby ich być już kilkaset czy kilka tysięcy to nie wiem jakby to działało.

GENERALNIE: JAK MYSQL RADZI SOBIE Z BARDZO DUżą ILOśCIą KOLUMN W TABELI (KILKADZIESIąT TYSIęCY) ALBO Z BARDZO DUżą ILOśCIą TABEL (KILKADZIESIąT TYSIęCY)

[Cezar708]

Uważam, że używanie do tego celu MySQL jest całkowicie nie na miejscu. Pamiętaj że te dane są statyczne czyli nigdy się nie zmienią (nie sądzę aby miasta się od siebie oddalały )

Ja proponuję Ci, abyś wrzucił wszystkie takie dane do dwóch tablic w samym PHP.
Pierwsza zawierała by tylko $nazwaMiasta => $numerMiasta, a druga byłaby tablicą dwuwymiarową, których kluczami byłyby $numerMiast, a wartością odległość pomiędzy nimi:

Przykład:

[PHP] pobierz, plaintext 
<?php
$miasto = array(
  'Poznań' => 0,
  'Kraków' => 1,
  'Gdańsk' => 2 // i tak dalej  i tak dalej
);
 
$odleglosc = array(
  0 => array( 
	0 => '-',
	1 => '466',
	2 => '603'
  ),
  1 => array(
	0 => '466',
	1 => '-',
	2 => '313'
  ),
  2 => array(
	0 => '603',
	1 => '313',
	2 => '-'
  ) // i tak dalej i tak dalej 
);
?>
[PHP] pobierz, plaintext 

to powyższe wrzucasz do jakiegoś pliku...

potem odwoływanie do tablicy:

[PHP] pobierz, plaintext 
<?php
include("odleglosci.php");
$odlPomiedzyPoznaniemAGdanskiem = $odleglosc[$miasto['Poznań']][$miasto['Gdańsk']];
?>
[PHP] pobierz, plaintext 

Oczywiście czeka Cię tu kilka kruczków (np: rozwiązać polskie znaki), a to powyższe to taki pseudo-kod mówiący mniej więcej jak ma to działać. Oczywiście, jak Ci się ten pomysł spodoba to tę tablicę sobie trochę zoptymalizować (np wszystkie dane są podwójnie wpisane, poco określać odl., pomiędzy Poznaniem a Gdańskiem, a następnie pomiędzy Gdańskiem i Poznaniem).

Kwestię optymalizacji pozostawiam Tobie.

Pozdrawiam

[nospor]

@Cezar708 nie wyobrażam sobie tak duuuuuzej tabeli w php.

@wojckr macież tabel? Po co? Tu wystarczą dwie tabele

Kod

miasta
ID | NAZWA

odleglosci
ID_MIASTA1 | ID_MIASTA2 | ODLEGLOSC

I juz. To oczywiscie najprostrza wersja.

Jakby sie pokusic o jakies grafy....

[php programmer]

po części zgadzam się z przedmowcą,
(jak to pisałem to jeszcze nie było postu @nospora, czyli z przed-przedmówcą,
no i przeczytałem tylko ostatnie pyatnie, wiec to jes jedynie odpo nate ostatnie pytanie)


Zapisanie macierzy

[PHP] pobierz, plaintext 
<?php
$array = Array();
.. wypełnienie macierzy danymi
$string = serialize($array);
$file = fopen('plik.serial',w);
fputs($file,$string);
fclose($file);
?>
[PHP] pobierz, plaintext 

Odczytanie macierzy

[PHP] pobierz, plaintext 
<?php
$string = file_get_contents('plik.serial');
$array = unserialize(trim($string));
echo '<pre>';
print_r($array);
?>
[PHP] pobierz, plaintext 

Ten post edytował php programmer 22.01.2008, 10:06:20

[wojckr]

Dane dla miast są faktycznie niezmienne.
Miasta podałem jako przykład. Nie chcę zdradzać mojego cyberhiperpomysłu, ale obiekty będą dopisywane, mogą tez znikać, więc baza danych powinna być łatwozmienialna (nie bardzo to widzę przy tablicy PHP tak jak to zaproponował Cezar708, mimo że podoba mi się łatwe odwołanie do odległości).

Mam wrażenie, że pomysł nospora jest ok, tylko że już przy 1000 obiektów to wychodzi 1000x999=prawie 1 milion wierszy ! To dużo bardzo (?). A przy 10 000 ? ~ 100 000 000...

Co myślicie o 10 000 tabel w 1 bazie? Chyba by zahulało (nigdy na tylu nie tabelach nie pracowałem).
Np.

CODE

obiekty
ID | NAZWA

ID (nazwa tab. to ID z tab. "obiekty", tabel tyle ile obiektów)
ID | ODLEGLOSC

Ten post edytował wojckr 22.01.2008, 14:24:01

[php programmer]

Jeśli chcesz dać 10 000 tabel to znaczy, że masz źle zaprojektowaną baze,
tabel zazwyczaj jest kilka lub najwyżej kilkanaście

[wojckr]

Dlatego właśnie proszę o pomoc, bo wychodzi albo:
1. b. duża tablica PHP
2. albo mnóstwo rekordów w tabeli mySql
3. albo w ogóle wielka macierz

Czy pracowałeś/pracowaliście na tak dużej liczbie rekordów? tak rozbudowanej tablicy PHP?

Najbardziej typowym zapytaniem byłoby:
"pokaż obiekty oddalone o od X1 do X2 km od obiektuA"

Ten post edytował wojckr 22.01.2008, 14:56:42

[nospor]

ad1)Zdecydowanie odpada wrecz to poroniony pomysl
ad3)Odpada juz mniej zdecydowanie ale nadal odpada

ad2) Jest ok Lepiej duzo rekordow niz duzo tabel. Jak ty sobie potem zapytania wyobrazasz pisac na tysiącach tabel?

[wojckr]

Jeszcze słabo to sobie wyobrażam, dlatego Was pytam i zdecydowanie chciałbym dobrze zaprojektować tą bazę.
Nospor piszesz dużo rekordów ok, a jak ich jest 100 000 000 to tez ok?

[nospor]

Ok, tu mnie masz

Mysql radzi sobie dobrze z milionami rekordow. Czy radzi sobie dobrze ze 100mln? nie wiem. Sprawdź. Wygeneruj testowe dane i juz.

Googlając trochę znalazłem luźne notatki o bazach z 60 tys tabel i 5 mld rekordów, ale prócz takich info nic konkretengo nie bylo.

[php programmer]

100 000 000 rekordów?

Rozumiem, że "trzeba mieć fantazję dziadku", ale bez przesady z tą fantazją.

Znałem już wielu takich co zakładali że bedą mieli miliony rekordów z danymi,
a w rzeczywistości jak w ogóle wyszło to mielo co najwyżej kilka tysięcy

Ten post edytował php programmer 22.01.2008, 17:36:51

[orson]

Witam.

Jeżeli to dane geograficzne i odległości są ściśle "w linii prostej" to może rozważ obliczanie odległości na podstawie położenia geograficznego danego punktu? Wysokość i szerokość geograficzna po to właśnie są.

Jeżeli odległość nie jest prosto przełożona na odległość punktów to może opracuj model w którym zastosujesz x i y do położenia w przestrzeni albo obliczaj odległość na zasadzie od A do B przez pkt C i D. Ilość rekordów powinna zmaleć.

IMHO proste obliczenie 2 współrzędnych będzie dużo wydajniejsze niż query do bazy ze 100 000 000 000 rekordów ...

Pozdrawiam.

[wojckr]

Tez pogooglalem i wyszlo, ze:
-praktycznie nie ma ograniczenia co do ilosci wierszy, jedynie ograniczenie wielkosci bazy często równe 4GB (ale wiadomo, ze to jest jakostam do przejscia),
-maksymalna ilosc kolumn 3398

A co do problemu to się w pewnym sensie rozwiazal (już zanim napisał o tym orson), bo kazdy obiekt faktycznie bedzie mial wspolrzedne X,Y, a co dalej za tym idzie wystarczy użyć twierdzenia Pitagorasa
SQRT(POW(x2-x1,2)+POW(y2-y1,2))

Dzięki wszystkim za pomoc, ale jeszcze mam jedno pytanie: czy baza danych, w której jest kila(naście) tysięcy tabel, jest faktycznie "źle zaprojektowana"? DLACZEGO? (to głównie pytanie do php programmer)

Pytam, ponieważ jednak nadal raczej uważam, że dobrze by było, gdyby każdy obiekt miał swoją tabelkę, bo zapytanie typu: "select .... from ... WHERE odleglosc>20" jest szybsze niż "select ... from ... where SQRT(POW(x2-x1,2)+POW(y2-y1,2))>20", szczególnie, że tabela każdego obiektu generowna byłaby bardzo żadko, a to drugie bardziej skomplikowane zapytanie byłoby wykonywane na okrągło..
W tym momencie uważam, że lepiej poświęcić porządek w bazie na rzecz szybkości działania. A WY?

Ten post edytował wojckr 22.01.2008, 21:14:20

[php programmer]

Im więcej masz tabel tym więcej masz do pisania zapytań, bo na każdą tabele będziesz musiał ręcznie napisać zapytanie
po za tym masz więcej zależności do zapamiętania, nie mówiąc już o tym, że jak będziesz chciał w przyszłości coś zmienić
np dodać kolumnę, to będziesz musiał to zrobić tyle razy ile masz tabel i jeszcze w kodzie pozmieniać to w tych zapytaniach,
przy 1000 tabelach to już jest masakra i właściwie nie wykonalne, a tym tam chciałeś jeszcze więcej,

jeśli chodzi o ilość wierszy, to ich duża ilość nie wpływa na zwiększenie twojej manualnej pracy
co najwyżej baza sobie nie poradzi i bedzie zamulać ale na to też są sposby, np unikanie joinów itp

[dr_bonzo]

php programmer: przeciez mozesz napisac skrypt ktory ci zrobi 1000x ALTER TABLE itd a pregami zmienisz zapytania.

[wojckr]

php programmer, uważam, że zamulanie bazy i to, że sobie nie radzi, to jest gigantyczny minus.

Nie wiem jak baza jest przeszukiwana w poszukiwaniu rekordu. Dlatego właśnie zastanawiam się

CZY lepiej 10 000 tabel i pytanie typu:
"wyszukaj 1 tabelę wśród 10 000 tabel i w niej 5 rekordów"

CODE

baza: OBIEKTY
10 000 tabel o unikalnych nazwach: OBIEKT1_ID
IDOBIEKTU_2 | ODLEGLOSC_DO_OBIEKTU_2

CZY 1 tabela z 10000000 rekordów i
"wyszukaj w 1 tabeli ze 10000000 rekordów 5 rekordów"

CODE

baza: OBIEKTY
tabela: ODLEGLOSCI
OBIEKT1_ID | OBIEKT2_ID | ODLEGLOSC
unikalna para OBIEKT1_ID-OBIEKT2_ID

Czy wiecie które zapytanie byłoby BARDZIEJ POPRAWNE z punktu widzenia "mechanizmu" przeszukiwania bazy? Pytanie nie jest łatwe raczej.

[orson]

Witam.

Klucze każdej tabeli są, w miarę możliwości, przechowywane w pamięci. Przy takiej ilości tabel nie ma na to szans. Silnik bazy musiałby wiele razy ładować klucze z dysku do pamięci i z powrotem.

Nie znam dokładnie specyfiki projektu oraz sprzętu ale jednak obliczanie będzie szybsze. Pitagoras na małych liczbach wykona się bardzo szybko. Możesz przyspieszyć to różnymi sztuczkami - przechowując część wyników (np. liczby podniesione do kwadratu itp), stosując cache popularnych operacji itp.

Jeżeli mogę zasugerować zmianę bazy na postgresql to ma on natywne, na poziomie wiersza oraz funkcji matematycznych, wsparcie dla danych zawierających współrzędne.

Pozdrawiam.

[mhyjek]

witam
"odziedziczylem" MySQL'a z ponad 1600 tabelek, w kazdej po kilkaset tysiecy rekordow.
baza przechowuje dane floty samochodowej, nowe auto nowych kilka tabelek....
pomijajac maszyne wydajnosc nie byla zbyt zadawalajaca... postanowilem to zmienic
i wrzucic te wszystkie rekordy do jednej tabeli. poindeksowalem i zrobilem test....
na tej samej maszynie zdechlo to totalnie.... postanowilem wszystko przepisac na nowo
z wykorzystaniem postgresql'a.... niestety stanalem przed tym samym problemem:
tabela dla pojazdu czy dane w jednej tabeli...
po wielu testach okazuje sie ze 1000 tabelek, osobna dla kazdego pojazdu
jest wydajniejsze (znacznie!) niz dane z 1000 pojazdow w jednej tabeli.
proste wyszukanie rekordu po dacie trwalo nawet kilka sekund (2xXeon 2,8Ghz/4GBRAM/SCSI 15k RAID1)
okazalo sie tez ze w tym przypadku administracja danymi (poprawki "z palca", przegladanie itd.) jest
o wiele latwiejsza.

Pozdrawiam
Marcin

[orson]

Witam.

@mhyjek: rozważałeś partycjonowanie tabeli? Możesz ustawić je na id pojazdu ... masz wtedy jedną tabelę no i możesz w razie potrzeby pobierać dane dotyczące większej ilości pojazdów.

Pozdrawiam

[mhyjek]

orson dzieki za podpowiedz, niedawno na to trafilem i w postgresie pewnie tak zrobie. wojcikr pytal o mysql'a. nie wiem czy mysql ma taka mozliwosc, natomiast wniosek z moich testow jest taki ze mysql (postgresql w sumie takze) radzi sobie w pewnym momencie lepiej z duza iloscia tabel niz z duza iloscia danych w jednej tabeli.
nie wiem czy bedzie to mialo zastosowanie w tym przypadku ale warto poczytac o RRD... do trzymania odleglosci wydaje sie byc jak najbardziej na miejscu.

Ten post edytował mhyjek 29.01.2008, 11:11:49