Partitions table in MySQL

[phanstasmal]

Một số lý do nên sử dụng partitions table

• Dữ liệu lưu trữ trên table lớn
• Thường xuyên query trên partition column
• Dữ liệu được đánh index lớn hơn nhiều so với số lượng RAM hiện có
• Xóa dữ liệu lịch sử một cách nhanh chóng

Lợi ích khi sử dụng

• Singer inserts and selects faster
• Range selects faster
• Hỗ trợ lưu trữ với nhiều path khác nhau
• Lưu trữ lịch sữ dữ liệu có hiệu quả cao
• Check constraint to the table
• Control to ARCHIVE tables
• Fasts insert on the master with HASH partitions, fast statistics in a slave with RANGE partitions

Có 4 loại partition

• Range
• List
• Hash
• Key
  Range, List, Hash phải sử dụng giá trị integer để partitions

Unpartitions table:

Partitions table:

Example

Show create table partitions:

) ENGINE=MyISAM AUTO_INCREMENT=28630442 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
(PARTITION M_XX_2014 VALUES LESS THAN (735964) ENGINE = MyISAM,
 PARTITION M_04_2015 VALUES LESS THAN (736054) ENGINE = MyISAM,
 PARTITION M_07_2015 VALUES LESS THAN (736145) ENGINE = MyISAM,
 PARTITION M_10_2015 VALUES LESS THAN (736237) ENGINE = MyISAM,
 PARTITION M_01_2016 VALUES LESS THAN (736329) ENGINE = MyISAM,
 PARTITION M_04_2016 VALUES LESS THAN (736420) ENGINE = MyISAM,
 PARTITION M_07_2016 VALUES LESS THAN (736511) ENGINE = MyISAM,
 PARTITION M_10_2016 VALUES LESS THAN (736603) ENGINE = MyISAM,
 PARTITION M_01_2017 VALUES LESS THAN (736695) ENGINE = MyISAM,
 PARTITION M_04_2017 VALUES LESS THAN (736785) ENGINE = MyISAM,
 PARTITION M_07_2017 VALUES LESS THAN (736876) ENGINE = MyISAM,
 PARTITION M_10_2017 VALUES LESS THAN (736968) ENGINE = MyISAM,
 PARTITION M_01_2018 VALUES LESS THAN (737060) ENGINE = MyISAM,
 PARTITION M_04_2018 VALUES LESS THAN (737150) ENGINE = MyISAM,
 PARTITION M_07_2018 VALUES LESS THAN (737241) ENGINE = MyISAM,
 PARTITION M_10_2018 VALUES LESS THAN (737333) ENGINE = MyISAM,
 PARTITION M_01_2019 VALUES LESS THAN (737425) ENGINE = MyISAM,
 PARTITION M_04_2019 VALUES LESS THAN (737515) ENGINE = MyISAM,
 PARTITION M_07_2019 VALUES LESS THAN (737606) ENGINE = MyISAM,
 PARTITION M_10_2019 VALUES LESS THAN (737698) ENGINE = MyISAM,
 PARTITION M_01_2020 VALUES LESS THAN (737790) ENGINE = MyISAM,
 PARTITION M_XX_FUTURE VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Query using partitions:

mysql> explain partitions select * from dwh_mca.user_history where created_at between '2016-01-01' and '2016-01-02' ;
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | user_history | M_04_2016  | range | created_at    | created_at | 6       | NULL |  993 | Using where |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+-------------+

• Partitions with MyISAM
  Mỗi một partition sẽ tạo ra file .MYD và file .MYI
• Partitions with InnoDB
  Mỗi một partition sẽ tại ra file .idb. Ví dụ user_history#P#M_01_2016.ibd

Partitions by date options

Phân loại một số lựa chọn khi chuyển đổi column partition về kiểu số nguyên:

Bad ideas 👎

• Convert Date to UNIX_TimeStamp
• Using unformatted date (20170401)
• Using expressions

Good ideas 👍

• YEAR(date_column)
• TO_DAYS(date_column)

Partitions BY FUNCTION, Query BY COLUMN

Delete dữ liệu lịch sử với partitions

ALTER TABLE t1 DROP PARTITION M_XX_2014, M_04_2015;