mysql 分區 · PHP/Python/前端/Linux 等等學習筆記

[TOC] > [各分區參考](http://haitian299.github.io/2016/05/26/mysql-partitioning/) ## range 分區 ### 根據數值范圍 ``` CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE ); ``` ### 根據TIMESTAMP范圍 ``` CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) ); ``` ### 根據DATE、DATETIME范圍只是支持高版本 ``` CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) PARTITION BY RANGE COLUMNS(joined) ( PARTITION p0 VALUES LESS THAN ('1960-01-01'), PARTITION p1 VALUES LESS THAN ('1970-01-01'), PARTITION p2 VALUES LESS THAN ('1980-01-01'), PARTITION p3 VALUES LESS THAN ('1990-01-01'), PARTITION p4 VALUES LESS THAN MAXVALUE ); ``` ### 根據多列范圍 ``` CREATE TABLE rc3 ( a INT, b INT ) PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (0,10), PARTITION p1 VALUES LESS THAN (10,20), PARTITION p2 VALUES LESS THAN (10,30), PARTITION p3 VALUES LESS THAN (10,35), PARTITION p4 VALUES LESS THAN (20,40), PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE) ); ``` ### demo 1. 創建一個帶分區的表 ``` CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL ) engine= InnoDB PARTITION BY RANGE (year(c3)) ( PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN MAXVALUE ); ``` 2. 創建沒有分區的表 ``` CREATE TABLE `no_part_tab` ( `c1` int(11) DEFAULT NULL, `c2` varchar(30) DEFAULT NULL, `c3` date DEFAULT NULL ) ENGINE=InnoDB ``` 3. 創建一個生成數據的存儲過程分區的表 ``` CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 8000000 do insert into part_tab values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); set v = v + 1; end while; end ``` 為分區的表 ``` CREATE PROCEDURE no_load_part() BEGIN DECLARE i INT; SET i =1; WHILE i<8000001 DO INSERT INTO no_part_tab VALUES(i,'no',ADDDATE('1995-01-01',(RAND(i)*36520) MOD 3652)); SET i=i+1; END WHILE; END ``` 4. 測試執行 ``` SELECT SQL_NO_CACHE COUNT(*) FROM no_part_tab WHERE c3> DATE '1995-01-01' AND c3< DATE '1995-12-31'; SELECT SQL_NO_CACHE COUNT(*) FROM part_tab WHERE c3> DATE '1995-01-01' AND c3< DATE '1995-12-31'; ``` 5. 查看使用sql 查詢訪問的分區查看partitons 字段 `explain partitions SELECT COUNT(*) FROM part_tab WHERE c3> DATE '1995-01-01' AND c3< DATE '1995-12-31'; ` ## Hash分區 ``` CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY HASH(store_id) PARTITIONS 4; ```