To jest stara wersja strony!
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
CREATE TABLE
lub
ALTER TABLE
Składnia:
CREATE TABLE <tableName> (<columns>)
ENGINE=<engineName>
PARTITION BY <type> ( <partitionExpression> );
<type> może być: RANGE, LIST, HASH lub KEY
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:
ABS()
MINUTE()
CEILING()
MOD()
DAY()
MONTH()
DAYOFMONTH()
QUARTER()
DAYOFWEEK()
SECOND()
DAYOFYEAR()
TIME_TO_SEC()
DATEDIFF()
TO_DAYS()
EXTRACT()
WEEKDAY()
FLOOR()
YEAR()
HOUR()
YEARWEEK()
MICROSECOND()
Klucze
Wszystkie kolumny użyte w wyrażeniu PARTITION muszą być częściami wszystkich kuczy PRIMARY i UNIQUE.
Przykład RANGE:
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)
Parametr MAXVALUE jest opcjonalny.
Zakresy wartości muszą być podane w kolejności rosnącej.
Przykład LIST:
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)
)
Przykład HASH:
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;
Wyrażenie w funkcji HASH musi zwracać typ INTEGER.
Przykład KEY:
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;
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
Testy wydajnościowe na dużych zbiorach danych
Struktura tabeli z partycjonowaniem
CREATE TABLE IF NOT EXISTS `logs_partitioned` (
`log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`date` DATETIME NOT NULL ,
`info` TEXT NOT NULL ,
PRIMARY KEY (`log_id`, `date`) ,
INDEX `date` (`date` ASC) )
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)
);
Struktura tabeli bez partycjonowania, zgodna z praktykami projektowania tabel
CREATE TABLE IF NOT EXISTS `logs_nonpartitioned` (
`log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`date` DATETIME NOT NULL ,
`info` TEXT NOT NULL ,
PRIMARY KEY (`log_id`) ,
INDEX `date` (`date` ASC) )
ENGINE = InnoDB;
Dodawanie danych
skrypt testujący
NIE DZIALA WKLEJANIE KODU!!!
wyniki
pobieranie danych
skrypt testujący
NIE DZIALA WKLEJANIE KODU!!!
Partycjonowanie w PostreSQL - lekko nieintuicyjnie...
Zaawansowane możliwości partycjonowania
Sub-partitioning
Zalety i wady partycjonowania
Zalety
Wady