list分區跟range分區類似,差別在于list分區的值是一個離散的整數值集合,每個分區的整數值集合不能有交集。
list分區是以“PARTITION BY LIST(expr)”實作的,expr可以是一個傳回值是整數的表達式,或者是表的某個整數列。然後通過values in(逗号分隔的整數清單)。
1、對已有表進行list分區
CREATE TABLE `test_user_list` (
`user_id` bigint(19) NOT NULL,
`user_name` varchar(50) NOT NULL,
`ext_int` int(2) NOT NULL,
`ts` bigint(19) NOT NULL,
PRIMARY KEY (`user_id`,`ext_int`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
ALTER TABLE test_user_list PARTITION BY list(ext_int)(
PARTITION p0 VALUES IN (5,8,11,15),
PARTITION p1 VALUES IN (1,4),
PARTITION p2 VALUES IN (2,7,10),
PARTITION p3 VALUES IN (3,12),
PARTITION p4 VALUES IN (9)
);
2、直接建立分區表
CREATE TABLE `test_user_list` (
`user_id` bigint(19) NOT NULL,
`user_name` varchar(50) NOT NULL,
`ext_int` int(2) NOT NULL,
`ts` bigint(19) NOT NULL,
PRIMARY KEY (`user_id`,`ext_int`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY list(ext_int)(
PARTITION p0 VALUES IN (5,8,11,15),
PARTITION p1 VALUES IN (1,4),
PARTITION p2 VALUES IN (2,7,10),
PARTITION p3 VALUES IN (3,12),
PARTITION p4 VALUES IN (9)
);
3、插入測試資料
INSERT INTO test_user_list(user_id,user_name,ext_int,ts)VALUES
(1,'user_name1',5,UNIX_TIMESTAMP()),
(2,'user_name2',5,UNIX_TIMESTAMP()),
(3,'user_name3',8,UNIX_TIMESTAMP()),
(4,'user_name4',4,UNIX_TIMESTAMP()),
(5,'user_name5',10,UNIX_TIMESTAMP()),
(6,'user_name6',3,UNIX_TIMESTAMP()),
(7,'user_name4',12,UNIX_TIMESTAMP()),
(8,'user_name4',12,UNIX_TIMESTAMP())
;
預期每個分區記錄條數:
p0:3
p1:1
p2:1
p3:3
p4:0
4、查詢資料庫分區資料情況
5、查詢資料庫詳細資料量