AIwiki

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 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
  

Mechanizm partycjonowania dostępny jest w MySQL od wersji 5.1

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

Wszystkie kolumny użyte w wyrażeniu PARTITION muszą być częściami wszystkich kuczy PRIMARY i UNIQUE.

• Każda partycja przechowywana jest w pliku .MYD i .MYI
  
• Można ustawić ścieżki DATA DIRECTORY i INDEX DIRECTORY
  

• 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
  

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()

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()

Partycjonowanie po dacie:

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)
);

Partycjonowanie zoptymalizowane jest dla typu DATE, dlatego złym pomysłem jest używanie UNIX_TIMESTAMP do przechowywania dat.

TODO: benchmark myisam?

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)
);

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;

skrypt testujący NIE DZIALA WKLEJANIE KODU!!! wyniki

skrypt testujący NIE DZIALA WKLEJANIE KODU!!!