Decydując się na przejście z PostgreSQL do MySQL trzeba pamiętać o wielu rzeczach. Nie wystarczy dostosować schematu bazy danych, bo jak już nasza aplikacja zacznie działać, możemy się niemiło zaskoczyć.
Dlaczego decydujemy się przenieść naszą bazę danych?
MySQL posiada jedną ogromna przewagę nad PostgreSQL: replikacja (od wersji 9.0 już tylko master-master). Był to jedyny powód, dla którego podjęta została decyzja o migracji projektu. W poniższej wyszczególniłam jedne z najważniejszych cech bazodanowych, dla których podejmowane były ważne decyzje/kroki:
- Replikacja
- MySQL: master-master, master-slave
- PostgreSQL: master-slave (od v.9.2)
- Typ engine'u:
- MySQL: InnoDB dla transakcyjność, MyISAM i wiele innych
- PostgreSQL: -
- Milisekundy w timestampach:
- MySQL: nie, w timestamp-ach przechowywane są tylko informacje co do sekundy
- PostgreSQL: tak
- Znaki specjalne:
- MySQL: od wersji 5.5 dostępny jest charset utf8mb4, który przechowuje znaki w 4 bajtach na znak.
- PostgreSQL: tak
Zmiany bazodanowe
Migracja schematu jest pracą łatwą ale czasochłonną. Aby pomóc sobie w tej pracy można się posłużyć aplikacjami windowsowymi, które dla wybranych tabel wykonają tą pracę za nas, ale dobrze wiedzieć jak zrobić to samemu.
Definicja tabelki w PostgreSQL:
CREATE TABLE a ( id integer NOT NULL, "timestamp" timestamp without time zone NOT NULL, n character varying(255) NOT NULL, p character varying(255) NOT NULL, pp smallint, s character varying(255) NOT NULL, ss integer, deleted boolean, r character varying(255), e character varying(20), dcs character varying(5) );
ma swój odpowiednik w MySQL-u:
CREATE TABLE `a` ( `id` int(11) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `n` varchar(255) NOT NULL, `p` varchar(255) NOT NULL, `pp` smallint(6) DEFAULT NULL, `s` varchar(255) NOT NULL, `ss` int(11) DEFAULT NULL, `deleted` tinyint(1) DEFAULT NULL, `r` varchar(255) DEFAULT NULL, `e` varchar(20) DEFAULT NULL, `dcs` varchar(5) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Warto pamiętać, że:
- timestamp domyślnie jest NOT NULL (http://dev.mysql.com/doc/refman/5.0/en)/timestamp-initialization.html). Musisz sprecyzować czy kolumna może przyjmować wartości NULL-owe i jaka jest wartość domyślna.
mysql (dbu@test)> create table test (c1 integer primary key, c2 varchar(255), c3 timestamp, c4 timestamp, c5 timestamp, c6 timestamp null default null); Query OK, 0 rows affected (0.22 sec) mysql (dbu@test)> show create table test \G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `c1` int(11) NOT NULL, `c2` varchar(255) DEFAULT NULL, `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c4` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `c5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `c6` timestamp NULL DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
- zakres danych dla timestamp'a jest inny. W MySQL minimalna wartość to '1970-01-19 00:00:01' UTC a maksymalną '2038-01-19 03:14:07' UTC. Dla PostgreSQL minimalną wartością jest '4714-01-01 BC', a maksymalną: '5874897-12-31 00:00:00'
- w MySQL boolean, bool to synonimy do tinyint(1). Wartość 0 jest traktowana jako false, a wszystkie inne jako true
- w MySQL'u, wzory SQL nie uwzględniają wielkości liter
mysql (dbu@test)> select * from test; +----+-------+---------------------+ | c1 | c2 | c3 |+----+-------+---------------------+ | 1 | test | 2013-12-23 14:37:21 | | 2 | test | 2013-12-23 14:37:27 | | 3 | test | 2013-12-23 14:37:33 | | 4 | TEST | 2013-12-23 14:37:39 | +----+-------+---------------------+ 4 rows in set (0.00 sec) mysql (dbu@test)> select *, length(c2) from test where c2 = 'TEST'; +----+-------+---------------------+------------+ | c1 | c2 | c3 | length(c2) | +----+-------+---------------------+------------+ | 1 | test | 2013-12-23 14:37:21 | 4 | | 3 | test | 2013-12-23 14:37:33 | 5 | | 4 | TEST | 2013-12-23 14:37:39 | 4 | +----+-------+---------------------+------------+ 3 rows in set (0.00 sec) mysql (dbu@test)> select *, length(c2) from test where c2 like 'TEST'; +----+------+---------------------+------------+ | c1 | c2 | c3 | length(c2) | +----+------+---------------------+------------+ | 1 | test | 2013-12-23 14:37:21 | 4 | | 4 | TEST | 2013-12-23 14:37:39 | 4 | +----+------+---------------------+------------+ 2 rows in set (0.00 sec)
Zmiany w aplikacji
Przeważnie, migrując aplikację do innego silnika bazodanowego, jesteśmy zmuszeni tylko do zmiany biblioteki klienta odpowiedzialnego za połączenia.
Dla przykładu w projektach Ruby on Rails jest to zamiana konfiguracji połączenia bazodanowego (config/database.yml) z:
development: adapter: postgresql encoding: unicode database: db_name username: db_user_name password: db_user_password host: db_host port: db_port
na:
development: adapter: mysql encoding:utf8 database: db_name username: db_user_name password: db_user_password host: db_host port: db_port
Jeśli jednak zapytania tworzyliśmy sami, wymagane jest sprawdzenie i/lub zmiana ich konstrukcji. Dobrym przykładem jest polecenie UPDATE. W PostgreSQL wykonując update rekordów tabeli, odwołując się do innej tabeli wykonujemy następująco:
UPDATE aggregated_services ags, services s
SET
accumulated_a = (
SELECT count(*)
FROM sn
WHERE s.template_id = sn.template_id
AND s.template_info_id = sn.template_info_id
AND sn.appeared_at < ags.end_time
),
ags.recalculate_failed_optins = false
WHERE s.id = ags.service_id
AND ags.end_time = '2013-01-01'
AND ags.recalculate_failed_optins
;
Natomiast w MySQL to samo zapytanie będzie wyglądało tak:
UPDATE aggregated_services
SET
accumulated_a = (
SELECT count(*)
FROM sn
WHERE s.template_id = sn.template_id
AND s.template_info_id = sn.template_info_id
AND sn.appeared_at < aggregated_services.end_time
),
recalculate_failed_optins = false
FROM services s
WHERE s.id = aggregated_services.service_id
AND end_time = '2013-01-01'
AND recalculate_failed_optins
;
Dobrą praktyką jest przetestowanie naszej aplikacji pod kątem wydajności. Zapytania, które dobrze działały w PostgreSQL, nie koniecznie będą dobrze działać pod MySQL. Na przykład zapytanie, które liczy unikalność wierszy w kilku kolumnach z warunkami:
SELECT count(*) AS count FROM ( SELECT DISTINCT templateid, templateinfoid, msgstate, gw_out_time FROM event ) as event WHERE msgstate NOT IN (-4,-5,-6) AND gw_out_time >= '2013-01-01' AND gw_out_time < '2013-01-02' AND templateid = 1 AND templateinfoid = 10;
Jak się okazuje, PostgreSQL w pierwszej kolejności wybiera dane, a później selekcjonuje unikalność i robi agregatę.
explain SELECT count(*) AS count FROM (SELECT DISTINCT templateid,templateinfoid,msgstate, gw_out_time FROM event) as event WHERE msgstate NOT IN (-4,-5,-6) AND gw_out_time >= '2013-01-01' AND gw_out_time < '2013-01-02' AND templateid = 1 AND templateinfoid = 10;
QUERY PLAN
(cost=31974.24..31974.25 rows=1 width=0)
-> Unique (cost=31744.01..31859.13 rows=9209 width=28)
-> Sort (cost=31744.01..31767.04 rows=9209 width=28)
Sort Key: public.event.templateid, public.event.templateinfoid, public.event.msgstate, public.event.gw_out_time
-> Index Scan using idx_event_1_tid_tinfoid_outtime on event (cost=0.00..31137.65 rows=9209 width=28)
Index Cond: ((templateinfoid = 10) AND (templateid = 1) AND (gw_out_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (gw_out_time < '2013-01-02 00:00:00'::timestamp without time zone))
Filter: (msgstate <> ALL ('{-4,-5,-6}'::integer[]))
(7 rows)
Dla tego samego zapytania w MySQL wygląda to zupełnie inaczej, bo w pierwszej kolejności wykonywany jest podselect (SELECT DISTINCT templateid, templateinfoid, msgstate, gw_out_time FROM event). Jeśli tabela jest bardzo duża, na wynik explain-a możemy się nie doczekać (bo aby zobaczyć wynik explain-a, MySQL musi wykonać zapytanie). Aby jednak otrzymać wynik z MySQL trzeba przenieść warunki wyszukiwania do podzapytania tak aby zapytanie główne wykonywało tylko agregatę:
mysql> explain SELECT count(*) AS count FROM (SELECT DISTINCT templateid,templateinfoid,msgstate, gw_out_time FROM event WHERE msgstate NOT IN (-4,-5,-6) AND gw_out_time >= '2013-01-01' AND gw_out_time < '2013-01-02' AND templateid = 1 AND templateinfoid = 10) event \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away *************************** 2. row *************************** id: 2 select_type: DERIVED table: event type: range possible_keys: idx_event_gw_out_time,idx_event_tid_tinfoid_outtime key: idx_event_tid_tinfoid_outtime key_len: 23 ref: NULL rows: 20970 Extra: Using where; Using temporary 2 rows in set (0.06 sec)
Trudność w realizacji tego zadania jest mocno uzależniona od złożoności naszego projektu. Warto przetestować go na realnych danych aby upewnić się, że wszystkie prace optymalizacyjne zostały zakończone sukcesem.
Powodzenia
Brak komentarzy:
Prześlij komentarz