Tytułowa zmiana struktury archiwum ma polegać na utworzeniu nowej tabeli o nazwie accounts, w której trzymane będą nasze wszystkie konta, oraz tabeli contacts, w której trzymane będą dane o kontaktach (na potrzebny archiwum jedynie JID, ale można znacznie więcej). Każde konto i każdy kontakt będzie miał swoje ID. Co nam to daje? A no daje nam to możliwość zastąpienia danych z kolumny msgfrom i account samymi ID z odpowiednich tabel.
Przedstawię wam realne dane wprost z mojego archiwum. Waży ono 279 MB i znajduje się w nim 1948918 wiadomość. Pobawiłem się trochę zapytaniami SQL (dzięki kr2ysiek) i obliczyłem, że kolumna msgfrom zawierająca informację od kogo pochodzi dana wiadomość waży 39265133 bajtów natomiast kolumna account mówiąc z którego konta została wysłana wiadomość waży 25846188 bajtów. Obie te kolumny mogłaby zajmować jedynie po 1948918 bajtów każda (2 ID na każdą wiadomość). Bajty, bajty, ale ile to jest? W normalnych jednostkach mamy ~37,45 MB śmieci w kolumnie msgfrom oraz ~22,79 MB śmieci w kolumnie account. Podsumowując, po proponowanej zmianie zamiast trzymać w bazie 60,24 MB, moglibyśmy trzymać jedynie 3,72 MB. Chyba jest różnica. Zdaję sobie sprawę, że wymusiłoby to ponowną konwersję archiwum, jednak uważam, że należy to zrobić i gdyby od razu dokładnie sprawę przemyśleć, to nie byłoby teraz problemu. Raz już konwertowaliśmy ludziom archiwum, więc można to zrobić ponownie.
PS Darujcie drobne pominięcia w wyliczeniach, np. tego, że dla kontaktów z ID > 9 trzeba zapisać w kolumnie dwa bajty - to i tak są tak małe ilości danych, że nie mają znaczenia. Chodziło o wykazanie, dlaczego kompletnie nie opłaca się trzymać pełnych JIDów nadawcy/odbiorcy przy wiadomości. Jest to kompletnie nieopłacalne, a dodatkowo obecność tych dwóch tabel accounts i contacts być może sprawi, że Oconnel upchnie do nich również dane z wszystkich wizytówek (obecnie trzymane w plikach .ini) czy też dane kont. I tak te dane są przechowywane, a umieszczenie ich w bazie danych wprowadzi porządek, a dzięki odpowiednim relacjom pozwoli zmniejszyć rozmiar samej bazy o ponad 20%
Zmiana struktury archiwum
#1
Napisano 2015.01.09, 03:02
- Shinigami lubi to
#2
Napisano 2015.01.09, 03:57
1. Czy takie rozwiązanie może powodować jakieś problemy/błędy?
2. Czy czas wczytywania archiwum skróci się?
PS. Chyba jeszcze miałem się o coś zapytać. Jeżeli sobie przypomnę to napiszę.
#3
Napisano 2015.01.09, 04:19
Odpowiadając (może i nieprofesjonalnie, ale co tam) na Twoje pytania:
- Nie ma mowy o żadnych problemach/błędach. W tabeli messages ustawiamy prostą relację do tabel accounts oraz contacts i tyle. Przykładowo zamiast "deadmaster@jix.im" przy wiadomości w kolumnie assount wstawimy "1", a w tabeli accounts ustawimy konto deamaster@jix.im na początku, przez co będzie miało ono ID=1 - skompresowaliśmy właśnie ilość danych 16-krotnie. To samo robimy w kolumnie msgfrom odwołując się do tabeli contacts wstawiając ID danego kontaktu. Ot cała filozofia
- Jak to wygląda od strony SQLite, to dokładnie nie wiem, ale tak na chłopski rozum, to mniejszy plik będzie się wczytywał szybciej niż większy.
#4
Napisano 2015.01.09, 12:04
To jest genialny pomysł! Przyspieszyłoby to w pewnym stopniu odczyt archiwum i nie tylko. Jestem za tym, by taką minimalizację bazy danych SQLite wprowadzić w AQQ.
- Deadmaster lubi to
#5
Napisano 2015.01.09, 12:59
Mogę porównać to rozwiązanie do konkurencji. Lata temu, jak tworzyłem bazę danych dla mojego bota (Jabbot Asia, ktoś go jeszcze pamięta/używa?) wzorowałem się na archiwum GG, były tam trzy tabelki.
Jedna trzymała informacje o osobach z którymi rozmiawialiśmy - ich numery, nazwy użytkownika, itp.
Druga była spisem rozmów, kiedy się zaczynała i kto w niej uczestniczył - jako odpowiedni ID z pierwszej tabeli.
Trzecia tabela zawiera już same wiadomości, z ID, ID ze spisu rozmów, treścią wiadomości, czasem otrzymania, kierunkiem wiadomości(wysłana lub otrzymana) i AQQ jeszcze przechowuje informacje czy dostarczono wiadomość.
Natomiast w WTW do tego służą dwie tabele, w jednej jest spis rozmów, a w drugiej wiadomości. Tutaj dla każdej rozmowy powtarza się identyfikator rozmówcy.
I dodatkowo są osobne tabelki do zapisu konferencji GG i XMPP.
A w moim rozwiązaniu użyłem dwóch tabel, podobnie jak teraz jest w WTW, oraz trzeciej do zapisu czatu.
Gdy mamy dane rozdzielone na kilka tabel, to należy je połączyć w jedność przy odczycie. Wymaga trochę większej znajomości SQL, niż pobranie wszystkiego z jednej tabeli. Niektórych klauzula JOIN ... ON przeraża.
SQL pozwala na proste generowanie statystyk, np. ile znaków łącznie mają nicki naszych rozmówców w bazie?
SELECT count(msgfrom) As Ilość, length(msgfrom) As "Długość nazwy", count(msgfrom) * length(msgfrom) AS "Suma znaków", msgfrom FROM messages GROUP BY msgfrom ORDER BY "Suma znaków" DESC
I przykład z mojego archiwum:
Ilość: "88713", Długość nazwy: "18", Suma znaków: "1596834", msgfrom: "blabler@blabler.pl"
- Deadmaster i kadet lubią to
#6
Napisano 2015.01.09, 13:54
Bo po to są właśnie relacyjne bazy danych, aby tworzyć w nich relacje a nie wszystko na chama pakować do jednej tabeli... +1
- Beherit, kr2ysiek, Bartek Sz. i 1 inna osoba lubią to
#7 Gość_Dejvus_*
Napisano 2015.01.15, 23:49
Przecież to rozwiązanie jest tak oczywiste, że nie wierzę, iż trzeba je proponować. To podstawy projektowania i optymalizacji baz danych...
Użytkownik Dejvus edytował ten post 2015.01.15, 23:49
Użytkownicy przeglądający ten temat: 0
0 użytkowników, 0 gości, 0 anonimowych