[[
✎ pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start
]]
aiWiki
Pokaż stronę
Ostatnie zmiany
Indeks
Zaloguj
Ta strona jest tylko do odczytu. Możesz wyświetlić źródła tej strony ale nie możesz ich zmienić.
===== Partycjonowanie dużych zbiorów danych ===== ==== Czym jest partycjonowanie? ==== Partycjonowanie polega na fizycznym podzieleniu tabel w bazie danych na osobne pliki. Mechanizm ten jest zupełnie transparentny dla użytkownika. Oznacza to, że na tabeli można wykonywać dokładnie te same operacje co na zwykłej tabli, uwzględniając transakcje.\\ Dzięki partycjonowaniu można znacznie przyspieszyć operacje na bardzo dużych tabelach. ==== Kiedy używać partycjonowania? ==== * Kiedy chcemy przyspieszyć pojedyncze operacje INSERT i SELECT\\ * Kiedy chcemy przyspieszyć operacje SELECT operujące na zakresach (np. od - do)\\ * Kiedy chcemy fizycznie podzielić tabelę między różne partycje lub dyski twarde\\ * Kiedy chcemy wydajnie przechowywać dane historyczne\\ * Kiedy tabele są bardzo duże\\ * Kiedy indeksy przekraczają rozmiar pamięci RAM\\ ==== Partycjonowanie w MySQL ==== Mechanizm partycjonowania dostępny jest w MySQL od wersji 5.1 === Zagadnienia techniczne === Partycje można tworzyć poleceniem <code sql>CREATE TABLE</code> lub <code sql>ALTER TABLE</code>\\ Składnia: <code sql> CREATE TABLE <tableName> (<columns>) ENGINE=<engineName> PARTITION BY <type> ( <partitionExpression> ); </code> <type> może być: RANGE, LIST, HASH lub KEY == Klucze == Wszystkie kolumny użyte w wyrażeniu PARTITION muszą być częściami wszystkich kuczy PRIMARY i UNIQUE. == MyISAM == * Każda partycja przechowywana jest w pliku .MYD i .MYI\\ * Można ustawić ścieżki DATA DIRECTORY i INDEX DIRECTORY\\ == InnoDB == * Bez ustawionego parametru innodb_file_per_table, tabele są przechowywane w domyślnej przestrzeni\\ * W przeciwnym wypadku, dla każdej tabeli, tworzony jest plik .ibd\\ === Metody partycjonowania === Dostępne są 4 metody partycjonowania: RANGE, LIST, HASH i KEY.\\ RANGE, LIST i HASH mogą używać tylko funkcji, które zwracają typ INTEGER. Dozwolone funkcje: <code sql> ABS() MINUTE() CEILING() MOD() DAY() MONTH() DAYOFMONTH() QUARTER() DAYOFWEEK() SECOND() DAYOFYEAR() TIME_TO_SEC() DATEDIFF() TO_DAYS() EXTRACT() WEEKDAY() FLOOR() YEAR() HOUR() YEARWEEK() MICROSECOND() </code> **Przykład RANGE:** <code sql> CREATE TABLE employee ( employee_id INTEGER AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), store_id TINYINT, PRIMARY KEY (employee_id ) ) ENGINE=MyISAM PARTITION BY RANGE (employee_id) ( PARTITION p0 VALUES LESS THAN (10000), PARTITION p1 VALUES LESS THAN (20000), PARTITION p2 VALUES LESS THAN (30000), PARTITION p3 VALUES LESS THAN (40000), PARTITION p4 VALUES LESS THAN MAXVALUE) </code> Parametr MAXVALUE jest opcjonalny.\\ Zakresy wartości muszą być podane w kolejności rosnącej. **Przykład LIST:** <code sql> CREATE TABLE employee ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), store_id TINYINT ) ENGINE=MyISAM PARTITION BY LIST (store_id) ( PARTITION pNorth VALUES IN (2,8,12), PARTITION pEast VALUES IN (1,4,7), PARTITION pWest VALUES IN (3,5,6,10), PARTITION pSouth VALUES IN (9,11) ) </code> **Przykład HASH:** <code sql> CREATE TABLE employee ( employee_id INT AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), store_id TINYINT, PRIMARY KEY (employee_id) ) ENGINE=MyISAM PARTITION BY HASH (employee_id) PARTITIONS 4; </code> Wyrażenie w funkcji HASH musi zwracać typ INTEGER. **Przykład KEY:** <code sql> CREATE TABLE employee ( employee_id INT, first_name VARCHAR(50), last_lname VARCHAR(50), store_id TINYINT ) ENGINE=MyISAM PARTITION BY KEY (last_name) PARTITIONS 4; </code> Wyrażenie nie musi zwracać typu INTEGER. Użyty algorytm jest taki sam jak w funkcji PASSWORD() === Przykłady poprawnego podejścia do partycjonowania danych === **Przykład 1: Partycjonowanie po dacie** <code sql> CREATE TABLE t1 ( d date ) PARTITION BY RANGE (YEAR(d)) ( PARTITION p01 VALUES LESS THAN (1999), PARTITION p02 VALUES LESS THAN (2000), PARTITION p03 VALUES LESS THAN (2001), PARTITION p04 VALUES LESS THAN (MAXVALUE) ); </code> Partycjonowanie zoptymalizowane jest dla typu DATE, dlatego złym pomysłem jest używanie UNIX_TIMESTAMP do przechowywania dat. **Przykład 2:**\\ Mamy partycjonowaną tabelę: <code sql> PARTITION BY RANGE(year(from_date)) </code> Wykonujemy zapytanie: <code sql> SELECT count(*) FROM salaries WHERE YEAR(from_date) = 1998; </code> Czas wykonania: 2.25 sec Drugie zapytanie: <code sql> SELECT count(*) FROM salaries WHERE from_date BETWEEN '1998-01-01' AND '1998-12-31'; </code> Czas wykonania: 0.46 sec Różnica w czasie wynika z tego, że w pierwszym zapytaniu nie wykorzystujemy informacji, że tabela jest partycjonowana po roku. Przez to zapytanie zostanie wykonane na każdej z partycji. W drugim przypadku baza danych wykona zapytanie tylko na partycjach, które należą do danego zakresu dat. === Testy wydajnościowe na dużych zbiorach danych === == Struktura tabeli z partycjonowaniem - silnik MyISAM == <code sql> CREATE TABLE IF NOT EXISTS `logs_partitioned_myisam` ( `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT , `date` DATETIME NOT NULL , `info` TEXT NOT NULL , PRIMARY KEY (`log_id`, `date`) ) ENGINE = MyISAM PARTITION BY RANGE(TO_DAYS(date))( PARTITION p01 VALUES LESS THAN (TO_DAYS('2010-01-01')), PARTITION p02 VALUES LESS THAN (TO_DAYS('2010-02-01')), PARTITION p03 VALUES LESS THAN (TO_DAYS('2010-03-01')), PARTITION p04 VALUES LESS THAN (TO_DAYS('2010-04-01')), PARTITION p05 VALUES LESS THAN (TO_DAYS('2010-05-01')), PARTITION p06 VALUES LESS THAN (TO_DAYS('2010-06-01')), PARTITION p07 VALUES LESS THAN (TO_DAYS('2010-07-01')), PARTITION p08 VALUES LESS THAN (TO_DAYS('2010-08-01')), PARTITION p09 VALUES LESS THAN (TO_DAYS('2010-09-01')), PARTITION p10 VALUES LESS THAN (TO_DAYS('2010-10-01')), PARTITION p11 VALUES LESS THAN (TO_DAYS('2010-11-01')), PARTITION p12 VALUES LESS THAN (TO_DAYS('2010-12-01')), PARTITION p13 VALUES LESS THAN (TO_DAYS('2011-01-01')), PARTITION p14 VALUES LESS THAN (MAXVALUE) ); </code> == Struktura tabeli bez partycjonowania - silnik MyISAM == <code sql> CREATE TABLE IF NOT EXISTS `logs_nonpartitioned_myisam` ( `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT , `date` DATETIME NOT NULL , `info` TEXT NOT NULL , PRIMARY KEY (`log_id`, `date`) ) ENGINE = MyISAM; </code> == Struktura tabeli z partycjonowaniem - silnik innoDB == <code sql> CREATE TABLE IF NOT EXISTS `logs_partitioned_innodb` ( `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT , `date` DATETIME NOT NULL , `info` TEXT NOT NULL , PRIMARY KEY (`log_id`, `date`) ) ENGINE = innoDB PARTITION BY RANGE(TO_DAYS(date))( PARTITION p01 VALUES LESS THAN (TO_DAYS('2010-01-01')), PARTITION p02 VALUES LESS THAN (TO_DAYS('2010-02-01')), PARTITION p03 VALUES LESS THAN (TO_DAYS('2010-03-01')), PARTITION p04 VALUES LESS THAN (TO_DAYS('2010-04-01')), PARTITION p05 VALUES LESS THAN (TO_DAYS('2010-05-01')), PARTITION p06 VALUES LESS THAN (TO_DAYS('2010-06-01')), PARTITION p07 VALUES LESS THAN (TO_DAYS('2010-07-01')), PARTITION p08 VALUES LESS THAN (TO_DAYS('2010-08-01')), PARTITION p09 VALUES LESS THAN (TO_DAYS('2010-09-01')), PARTITION p10 VALUES LESS THAN (TO_DAYS('2010-10-01')), PARTITION p11 VALUES LESS THAN (TO_DAYS('2010-11-01')), PARTITION p12 VALUES LESS THAN (TO_DAYS('2010-12-01')), PARTITION p13 VALUES LESS THAN (TO_DAYS('2011-01-01')), PARTITION p14 VALUES LESS THAN (MAXVALUE) ); </code> == Struktura tabeli bez partycjonowania - silnik innoDB == <code sql> CREATE TABLE IF NOT EXISTS `logs_nonpartitioned_innodb` ( `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT , `date` DATETIME NOT NULL , `info` TEXT NOT NULL , PRIMARY KEY (`log_id`, `date`) ) ENGINE = innoDB; </code> == Dodawanie danych == ** Skrypt testujący ** <code php> $link = mysql_connect("localhost", "root", "pass"); if (!$link) { die("Could not connect: " . mysql_error()); } mysql_select_db("partitioning", $link); mysql_query("TRUNCATE TABLE `logs_partitioned_myisam`"); mysql_query("TRUNCATE TABLE `logs_partitioned_innodb`"); mysql_query("TRUNCATE TABLE `logs_nonpartitioned_innodb`"); mysql_query("TRUNCATE TABLE `logs_nonpartitioned_myisam`"); $time1 = 0; $time2 = 0; $time3 = 0; $time4 = 0; $n = 100000; for($i=0; $i<$n; $i++){ $date = "2010-".rand(1,12)."-".rand(1,28)." ".rand(0,23).":".rand(0,59).":".rand(0,59); $text = md5(uniqid(rand(1,132456), true)); $start1 = microtime(true); mysql_query("INSERT INTO `logs_partitioned_myisam` (date, info) VALUES ('".$date."', '".$text."')"); $stop1 = microtime(true); $start2 = microtime(true); mysql_query("INSERT INTO `logs_nonpartitioned_myisam` (date, info) VALUES ('".$date."', '".$text."')"); $stop2 = microtime(true); $start3 = microtime(true); mysql_query("INSERT INTO `logs_partitioned_innodb` (date, info) VALUES ('".$date."', '".$text."')"); $stop3 = microtime(true); $start4 = microtime(true); mysql_query("INSERT INTO `logs_nonpartitioned_innodb` (date, info) VALUES ('".$date."', '".$text."')"); $stop4 = microtime(true); $time1 += ($stop1-$start1); $time2 += ($stop2-$start2); $time3 += ($stop3-$start3); $time4 += ($stop4-$start4); } mysql_close($link); </code> ** Wyniki ** <code text> </code> == pobieranie danych == ** Skrypt testujący ** <code php> $link = mysql_connect('localhost', 'user', 'pass'); if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db("partitioning", $link); $time1 = 0; $time2 = 0; $time3 = 0; $time4 = 0; $n = 100000; for($i=0; $i<$n; $i++){ $date = "2010-".rand(1,12)."-".rand(1,28)." ".rand(0,23).":".rand(0,59).":".rand(0,59); $start1 = microtime(true); $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_partitioned_myisam` WHERE `date` = '".$date."'"); while($row = mysql_fetch_row($r)){} $stop1 = microtime(true); $start2 = microtime(true); $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_nonpartitioned_myisam` WHERE `date` = '".$date."'"); while($row = mysql_fetch_row($r)){} $stop2 = microtime(true); $start3 = microtime(true); $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_partitioned_innodb` WHERE `date` = '".$date."'"); while($row = mysql_fetch_row($r)){} $stop3 = microtime(true); $start4 = microtime(true); $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_nonpartitioned_innodb` WHERE `date` = '".$date."'"); while($row = mysql_fetch_row($r)){} $stop4 = microtime(true); $time1 += ($stop1-$start1); $time2 += ($stop2-$start2); $time3 += ($stop3-$start3); $time4 += ($stop4-$start4); } mysql_close($link); </code> ** Wyniki ** <code text> </code> === Partycjonowanie w PostreSQL === == Implementacja partycjonowania == Aby dokonać partycjonowania tabeli należy wykonać następujące czynności: * 1. Utworzyć główną tabelę, po której wszystkie partycje będą dziedziczyły. * 2. Utworzyć kilka tabel potomnych (partycji) dziedziczących po głównej tabeli. Zazwyczaj potomne tabele nie posiadają żadnych dodatkowych kolumn. * 3. Dodać ograniczenia do partycji aby zdefiniować dopuszczalne wartości kluczy <code sql> CHECK ( x = 1 ) CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 ) </code> \\ Należy upewnić się, że nałożone ograniczenia gwarantują unikalność kluczy w poszczególnych partycjach.\\ Częstym błędem jest:\\ <code sql> CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 ) </code> W takim przypadku nie wiadomo gdzie należy klucz o wartości 200.\\ * 4. Dla każdej partycji utworzyć indeks na kolumnie klucza (nie jest to bezwzględnie konieczne, ale w większości przypadków przydaje się) * 5. Opcjonalnie można zdefiniować trigger przekierowujący dane z głównej tabeli do odpowiednich partycji. * 6. Upewnić się, że parametr [[http://http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION|constraint_exclusion]] nie został wyłączony w ''postgresql.conf''. W przeciwnym wypadku zapytania nie będą odpowiednio zoptymalizowane Np. Załóżmy, że w naszej tabeli gromadzone są pomiary temperatury każdego dnia w różnych regionach świata.\\ \\ Potrzebna jest następująca tabela: <code sql> CREATE TABLE measurement ( region_id int not null, logdate date not null, peaktemp int, ); </code> \\ Wiemy, że zdecydowana większość zapytań będzie dotyczyła danych z ostatniego tygodnia/miesiąca/kwartału. Aby zredukować ilość niepotrzebne gromadzonych starych danych decydujemy się przechowywać jedynie informacje z 3 ostatnich lat. Na początku każdego miesiąca usuwane są najstarsze dane:\\ W takiej sytuacji partycjonowanie może pomóc nam w sprostaniu różnym wymaganiom dla tabeli ''measurments''. \\ Zgodnie z krokami wymienionymi powyżej, możemy dokonać partycjonowania w następujący sposób: * 1. Główna tabela ''measurments'' została utworzona. * 2. Tworzymy partycje dla każdego miesiąca: <code sql> CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement); </code> \\ Wszystkie partycje są pełnoprawnymi tabelami, ale dziedziczą definicję po tabeli ''measurments''. To rozwiązuje problem z usuwanie danych - każdego miesiąca musimy wykonać ''DROP TABLE'' na najstarszej tabeli oraz utworzyć nową tabelę potomną dla danych z nowego miesiąca. * 3. Musimy zapewnić unikalność kluczy we wszystkich partycjach. W związku z tym powyższy skrypt powinien w rzeczywistości wyglądać tak: <code sql> CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement); </code> * 4. Potrzebujemy indeksów na kolumnach kluczy: <code sql> CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); ... CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate); </code> \\ * 5. Chcemy, aby w naszej aplikacji działała konstrukcja ''INSERT INTO measurments...'', a wszystkie dane były automatycznie przekierowywane do odpowiednich partycji. Możemy to osiągnąć poprzez podpięcie odpowiedniej funkcji do głównej tabeli.\\ Jeśli dane będą dodaane tylko do ostatniej partycji możemy użyć bardzo prostej funkcji:\\ <code sql> CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN INSERT INTO measurement_y2008m01 VALUES (NEW.*); RETURN NULL; END; $$ LANGUAGE plpgsql; </code> Po utworzeniu funkcji tworzymy trigger uruchamiający funkcję:\\ <code sql> CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); </code> Musimy uaktualniać funkcję każdego miesiąca, aby dodawała dane gdo odpowiedniej partycji. Definicja triggera nie musi być uaktualniana, jednakże możemy chcieć dodawać dane i żądać od serwera automatycznej lokalizacji partycji, do której wiersz powinien zostać wstawiony. Możemy to osiągnąć poprzez użycie bardziej złożonej funkcji triggera: <code sql> CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; </code> \\ == Zarządzanie partycjami == Zazwyczaj zbiór partycji utworzonych podczas definiowania tabeli z czasem ulega zmianom. Często usuwa się stare partycje i dodaje nowe. Jedną z największych zalet partycjonowania jest to, że pozwala na wykonywanie tego nieprzyjemnego zadania niemal natychmiast poprzez manipulowanie strukturą partycji, a nie poprzez fizyczne operowanie na dużej ilości danych.\\ Najprostszym sposobem na usunięcie starych danych jest usunięcie niepotrzebnej partycji: <code sql> DROP TABLE measurement_y2006m02; </code> W ten sposób można bardzo szybko usunąc miliony rekordów, ponieważ nie ma potrzeby aby indywidualnie usuwać każdy wiersz.\\ Innym popularnym sposobem jest usunięcie potomnej tabeli ze zbioru wszystkich partycji ale pozostawienie do niej dostępu: <code sql> ALTER TABLE measurement_y2006m02 NO INHERIT measurement; </code> Pozwala to na wykonywanie pewnych operacji na zbiorze danych, zanim zostaną one definitywnie usunięte. \\ W podobnie prosty sposób możemy dodawać nowe partycje. <code sql> CREATE TABLE measurement_y2008m02 ( CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ) ) INHERITS (measurement); </code> Alternatywnie, czasem wygodnie jest utworzyć nową tabelę poza zbiorem partycji i włączyć ją do owego zbioru później. To pozwala na załadowanie, sprawdzenie i modyfikację danych, zanim pojawią się w strukturze partycji: <code sql> CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- possibly some other data preparation work ALTER TABLE measurement_y2008m02 INHERIT measurement; </code> \\ == Partycjonowanie i Constraint exclusion == //Contsraint exclusion to technika optymalizacji zapytań dla partycjonowanych tabel// Np.\\ <code sql> SET constraint_exclusion = on; SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; </code> Bez ''constraint_exclusion'' powyższe zapytanie przeskanowałoby wszystkie partycje wchodzące w skład tabeli ''measurments''. Z włączonym parametrem planner bada ograniczenia nałożone na wszystkie partycje i stara się udowodnić, że dana partycja nie musi być skanowana, ponieważ nie zawiera żadnych danych spełniających warunki podane w klauzuli ''WHERE'' zapytania.\\ Można użyć polecenia ''EXPLAIN'' aby zobaczyć różnicę w planie zapytania z włąćzonym i wyłączonym parametrem ''constraint_exclusion''.\\ Plan niezoptymalizowanego zapytania wygląda następująco: <code> SET constraint_exclusion = off; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=158.66..158.68 rows=1 width=0) -> Append (cost=0.00..151.88 rows=2715 width=0) -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2006m02 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2006m03 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) ... -> Seq Scan on measurement_y2007m12 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) </code> Aby pobrać wszystkie dane nie trzeba skanować starszych tabel.\\ Po włączeniu ''constraint_exclusion'' otrzymamy następujący plan: <code> SET constraint_exclusion = on; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=63.47..63.48 rows=1 width=0) -> Append (cost=0.00..60.75 rows=1086 width=0) -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2008-01-01'::date) </code> Parametr ''constraint_exclusion'' korzysta wyłącznie z ograniczeń nałożonych przez ''CHECK''.\\ Zaleca się włączanie parametru ''constraint_exclusion'' tylko w wypadku zapytań operujących na spartycjonowanych tabelach. == Alternatywne metody partycjonowania == Innym sposobem wstawianie danych do partycji jest ustanowienie reguł, np: <code sql> CREATE RULE measurement_insert_y2006m02 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) DO INSTEAD INSERT INTO measurement_y2006m02 VALUES (NEW.*); ... CREATE RULE measurement_insert_y2008m01 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) DO INSTEAD INSERT INTO measurement_y2008m01 VALUES (NEW.*); </code> \\ Należy pamiętać, że ''COPY'' ignoruje reguły. Jeśli chcesz korzystać z ''COPY'' do wstawiania danych musisz sam wskazać odpowiednią partycję.\\ Inną wadą takiego podejścia jest fakt, iż trudno wymusić błąd jeśli zbiór reguł nie pokrywa całego zbioru danych. Dane zostaną w takiej sytuacji wstawione do głównej tabeli.\\ Partycjonowanie może być również wykonane przy użyciu ''UNION ALL'', zamiast dziedziczenia. <code sql> CREATE VIEW measurement AS SELECT * FROM measurement_y2006m02 UNION ALL SELECT * FROM measurement_y2006m03 ... UNION ALL SELECT * FROM measurement_y2007m11 UNION ALL SELECT * FROM measurement_y2007m12 UNION ALL SELECT * FROM measurement_y2008m01; </code> ==== Zaawansowane możliwości partycjonowania ==== === Sub-partitioning === ==== Zalety i wady partycjonowania ==== === Zalety === === Wady ===
pl/dydaktyka/ztb/2010/projekty/partycjonowanie/start.1275424347.txt.gz
· ostatnio zmienione: 2019/06/27 15:56 (edycja zewnętrzna)
Pokaż stronę
Poprzednie wersje
Menadżer multimediów
Do góry