Po kilku latach przerwy w blogowaniu, w końcu “zmusiłem” się aby kontynuować. Co jakiś czas będę publikował informację, które mogą się w mniejszy czy większym stopniu przydać - przynajmniej mam takie zamiary, co z tego wyjdzie, to się okaże.

Udało mi się przywrócić posty ale problem mam z komentarzami, jeśli się uda to będą, jeśli nie to oczywiście nie będzie

Na stronie ORACLE został opublikowany artykuł o połczeniu bazy ORACLE (do przykładu została użyta baza Oracle Database 10g XE) z Ruby on Rails. Artykuł zawiera instalację pod systemem Linux i Windows i sposób użycia.

Na liście znalazły się takie pakiety jak:

• actionmailer - E-mail support
• actionpack - Controller and View Framework
• actionwebservice - Web Service Support
• activerecord - Object Relational Mapping Layer
• activesupport - Utility Classes Used by Rails
• Rails - Ruby-based MVC Web Application Framework
• Rake - Ruby Build Program

Źródło dokumentu: http://www.oracle.com/technology/pub/articles/saternos-ror-faq.html

Kilka dni temu natrafiłem na art dotyczący bugów w bazach danych, wykres mówi sam za siebie. Wielkim zdziwieniem jest to, ze MSSQLa w roku 2005 i 2006 nie ma na liście.

Źródło

Wiele osób zaczynających programowanie wykorzystują w swoich aplikacjach bazy danych. Przy projekcie struktury jak i strojeniu zapytań SQL zawsze stykają się z terminem indeksów i kluczy. Chciałbym w krótki sposób przedstawić i pokazać na przykładach wykorzystanie indeksów i kluczy w bazach danych i wyjaśnić dlaczego mają tak wielki wpływ na wydajność wykonania zapytań.

Klucze w bazie danych - zalążek teorii.

Głównymi zastosowaniami kluczy w bazie danych są:

• poprawienie wydajności działania bazy danych - w operacjach DQL (Data Query Language), takich jak SELECT, gdzie na podstawie kluczy optymalizator bazy danych może dostosować najbardziej optymalny plan wykonania zapytania, co wpływa na szybkość zwracania rekordów jak i obciążenia serwera bazodanowego
• zapewnienie automatycznej kontroli poprawności operacji przetwarzania danych - przy stosowaniu operacji DML (Data Manipulation Language), takich jak INSERT, UPDATE, DELETE, gdzie na poziomie bazy danych dane są weryfikowane czy aby relacja jest poprawna, czy dane się nie powtarzają, czy przedział jest odpowiedni, lub wywoływana jest dowolna metoda weryfikacji danych (trigger)

Indeksy

Indeks jest specjalną strukturą, wprowadzoną w celu przyspieszenia dostępu do danych. Indeks w bazie danych jest odzwierciedleniem spisu treści w książce.

Wykorzystuje się przy zapytaniach DQL (SELECT), które maja na celu wyszukiwanie odpowiednich wartości w bazie danych. Optymalizator najpierw przeszukuje indeks, który jest uporządkowany a następnie na podstawie indeksu odczytuje odpowiednie rekordy.

Definicja indeksu

CREATE INDEX name_index ON user (name);

Lub przez polecenei ALTER

ALTER TABLE user ADD INDEX (name);

Kiedy indeksy są zalecane?

Największy pożytek ze stosowania indeksów zależy w dużej mierze od selektywności kolumny lub kolumn do redukcji zbioru danych, innym przykładem zastosowania indeksów są kolumny które są często wymieniane w warunkach (WHERE) i złączeniach tabel.

Często w bazach danych spotyka się indeksy bazujące na funkcjach, przykładem może być funkcja UPPER(), dzięki której wydajność bazy danych polepsza się.

CREATE INDEX upper_index ON users (UPPER(name));

Indeks w operacjach DML powoduje spadek wydajności, ponieważ po każdej modyfikacji rekordu indeks jest odbudowywany.

Klucz podstawowy (Primary Key)

Zadaniem klucza podstawowego jest zapewnienie unikalności, zapewnienie że kolumny klucza nie zawierają wartości pustych, możliwości identyfikacji rekordu w bazie danych, jak i szybszego wyszukania interesującego nas rekordu.

Definicja klucza podstawowego

Klucz podstawowy można utworzyć podczas tworzenia tabeli lub modyfikacji tabeli.

Tworzenie tabeli (przykład dla MySQLa z AUTO_INCREMENT):

CREATE TABLE `user`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL
)

Modyfikacja tabeli:

ALTER TABLE `user` ADD PRIMARY KEY (`id`);

Klucz obcy (Foreign Key)

Integracja referencyjna jest jedną z podstawowych cech klucza obcego, w której każda wartość klucza obcego (niepusta) musi odpowiadać jednej z wartości klucza podstawowego (PRIMARY KEY) lub klucza unikalnego (UNIQUE) innej lub tej samej tabeli. Przykładem takim może być użytkownik, który należy do jakiegoś systemu.

Więzy integralności

Klucze obce wymuszają więzy integralności:

• REFERENCES - źródło klucza obcego (tabela i klucz podstawowy)
• ON DELETE, ON UPDATE - określenie akcji jaka należy podjąć w chwili usunięcia lub modyfikacji klucza podstawowego
• SET NULL zastąpienie wartości klucza obcego przez wartość NULL
• SET DEFAULT zastąpienie wartości klucza obcego przez wartość domyślnie ustawioną podczas definiowania struktury tabeli
• CASCADE modyfikacja pola klucza obcego na nową wartość pola klucza podstawowego lub usunięcie wszystkich rekordów zawierających odwołanie klucza obcego
• NO ACTION tylko w przypadku modyfikacji niezmienianie wartości klucza obcego
• RESTRICT nie dopuszczenie do zmiany

Do największych zalet więzów integralności zalicza się:

• łatwość deklaracji i modyfikacji
• jednakowe reguły dla wszystkich użytkowników i aplikacji
• natychmiastowa informacja zwrotna dla użytkownika (w przypadku błędu)
• elastyczność (włączane / wyłączane)
• bardzo dobra wydajność

Przykłady zastosowań więzów integralności.

Dla przykładu użyje 2 tabelek (”user” i “system”) - przykład dla bazy MySQL (tabele musza zastać utworzone jako InnoDB)

CREATE TABLE `user`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 100 ) NOT NULL
) ENGINE = innodb;

CREATE TABLE `system`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = innodb;

Użytkownik musi zostać przypisany do systemu, więc wystarczy dodać dodatkowe pole do tabeli “user” w którym będzie wskazanie na tabele “system”

ALTER TABLE `user` ADD `system_id` INT NOT NULL;
ALTER TABLE `user` ADD INDEX (`system_id`);

Dodajemy kilka rekordów aby zobrazować przykłady wykorzystania relacji.

INSERT INTO `system` ( `id`, `name` ) VALUES ( 1, 'linux' );
INSERT INTO `system` ( `id`, `name` ) VALUES ( 2, 'windows' );

INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (1, 'SongoQ', 1);
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (2, 'Marcin', 1);
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (3, 'Kasia', 2);
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (4, 'Pawel', 2);

Złączenie tabel da rezultat:

SELECT * FROM `user`, `system` WHERE `user`.`system_id` = `system`.`id`;

+----+--------+-----------+----+---------+
| id | name   | system_id | id | name    |
+----+--------+-----------+----+---------+
|  1 | SongoQ |         1 |  1 | linux   |
|  2 | Marcin |         1 |  1 | linux   |
|  3 | Monika |         2 |  2 | windows |
|  4 | Pawel  |         2 |  2 | windows |
+----+--------+-----------+----+---------+

Następnie przechodzimy do tworzenia relacji między tabelami.

Najczęściej wykorzystywane relacje w bazach danych

RESTRICT

ALTER TABLE `user` ADD CONSTRAINT `user_system_id_fk` FOREIGN KEY (`system_id`)
REFERENCES `system` (`id`);

Usunięcie rekordu z tabeli “system” do której odwołuje się rekord z tabeli “user” spowoduje błąd.

DELETE FROM `system` WHERE `id` = 1;

CASCADE - aktualizacja

ALTER TABLE `user` ADD CONSTRAINT `user_system_id_fk` FOREIGN KEY (`system_id`)
REFERENCES `system` (`id`) ON UPDATE CASCADE;

Zmiana warto??ci klucza podstawowego w tabeli “system” powoduje zmianę klucza obcego w tabeli “user”

UPDATE `system` SET `id` = 3 WHERE `id` = 1;

SELECT * FROM `user`, `system` WHERE `user`.`system_id` = `system`.`id`;

+----+--------+-----------+----+---------+
| id | name   | system_id | id | name    |
+----+--------+-----------+----+---------+
|  1 | SongoQ |         3 |  3 | linux   |
|  2 | Marcin |         3 |  3 | linux   |
|  3 | Monika |         2 |  2 | windows |
|  4 | Pawel  |         2 |  2 | windows |
+----+--------+-----------+----+---------+

CASCADE - usuwanie

ALTER TABLE `user` ADD CONSTRAINT `user_system_id_fk` FOREIGN KEY (`system_id`)
REFERENCES `system` (`id`) ON DELETE CASCADE;

Usunięcie rekordu z tabeli “system” powoduje usunięcie rekorduw z tabeli “user”w której klucz obcy odpowiada kluczowi podstawowemu.

DELETE FROM `system` WHERE `id` = 3;

SELECT * FROM `user`, `system` WHERE `user`.`system_id` = `system`.`id`;

+----+--------+-----------+----+---------+
| id | name   | system_id | id | name    |
+----+--------+-----------+----+---------+
|  3 | Monika |         2 |  2 | windows |
|  4 | Pawel  |         2 |  2 | windows |
+----+--------+-----------+----+---------+

Oczywiście ON DELETE i ON UPDATE można stosować razem.

Indeks unikalny (UNIQUE)

Indeks unikalny służy do wymuszenia na polu wartości unikalnych, czyli żadna wartość w nim wystąpująca nie może sie powtarzać. Indeks unikalny charakteryzuje się dobrą selektywnośca.

ALTER TABLE `users` ADD UNIQUE (`name`)

Indeks złożony

Indeksy złożone składają sie z wielu kolumn, gdzie kolumny mogą być zdefiniowane w dowolnej kolejności. Indeksy złożone są użyteczne, wtedy kiedy w zapytaniu SELECT w warunku WHERE występuje wiecej niż jedno odwołanie do pol tabeli.

ALTER TABLE `user` ADD UNIQUE `name` (`name`, `surname`)

Mam nadzieje, że ten tekst niektórym przybliżył pojęcia związane z kluczami i indeksami. Odnośnie szczegłów i większej liczby przykładów polecam ksiżeczkę z podstawami relacyjnych baz danych. Wydawnictwo Helion.pl ma kilka dobrych pozycji na ten temat.

Przeprowadziłem kilka testów szybkości odczytu z pliku i tabel typu: MEMORY, MyISAM.

Jak był przeprowadzany test:

Test przeprowadzałem na Ubuntu Dapper (apache2, php5.1.x, MySQL 5.0.22) na laptopie, więc wiele rzeczy mogło wpłynąć na na nieprawidłowość testów.

Test podzieliłem na kilka etapów:

• z włączonym cache zapytań
• z wyłączonym cache zapytań
• z pomiarem połączenia z bazą danych

Odnośnie ilości wywołań testu, to był wykonywany 1000 razy, liczone były pojedyńcze czasy testu, a następnie sumowane i liczona z tego średnia testu. Jako danych testowych użyłem wygenerowanego ciągu znaków o długości około 65 KB.

Wyniki:

Odczyt z pliku:

Do odczytu z pliku użyłem funkcji: file_get_contents()
Średni czas odczytu jaki uzyskałem to: 0.000357507705688 (szczegółowe informacje)

Odczyt z bazy danych:

Do odczytu z bazy użyłem funkcji mysql_* (połączenie, wybór bazy, wykonanie zapytanie i zwrócenie rekordu). Tabela zawierała 1 rekord z id i polem w którym był zapisany taki sam ciąg znaków jak w przypadku pliku. Dlaczego nie więcej? Chciałem wyeliminować czas jaki będzie potrzebny na przeliczenie jaki rekord ma zostać zwrócony.

Wyłączony zapis cache w MySQL i czas połączenia z bazą pominięty:

Tabela MyISAM - średni czas to: 0.00516110825539 (szczegółowe informacje)
Tabela MEMORY - średni czas to: 0.00368802952766 (szczegółowe informacje)

Widać, że odczyt z pliku ma przewagę nad odczytem z bazy danych, a czasy odczytu z tabel typu MyISAM i MEMORY są bardzo do siebie zbliżone.

Włączony zapis cache w MySQL i czas połączenia z bazą pominięty:

Tabela MyISAM - średni czas to: 0.00179107260704 (szczegółowe informacje)

Tabela MEMORY - średni czas to: 0.000719551563263 (szczegółowe informacje)

Z włączonym cache zapytań widać, że czasy są zbliżone do odczytu z pliku.

Włączony zapis cache w MySQL + czas połączenia z bazą danych:

Tabela MyISAM - średni czas to: 0.00202487516403 (szczegółowe informacje)
Tabela MEMORY - średni czas to: 0.00205611944199 (szczegół?owe informacje)

Kluczowym znaczeniem jest czas połączenia z bazą danych a porównanie tych 2 testów zapytań do bazy danych można powiedzieć, że są prawie identyczne.

Różnice w wydajności pomiędzy MyISAM i MEMORY?

Typ tabeli MEMORY będzie lepszy zawsze, kiedy operujemy na pewnym zestawie rekordþw i nie musimy jak w przypadku MyISAM odczytywac z dysku, który jak wiadomo ma fizyczne ograniczenia, które są nie do przeskoczenia.

Polecam zapoznanie się z art. http://dev.mysql.com/tech-resources/articles/mysql_5.0_psea2.html, w którym autor porównał różne typy tabel.