1. 云图网首页
  2. 技术专区
  3. 数据库

MySQL：分区表之二：LIST 分区

• 数据库

上篇 blog 介绍了 MySQL 中的 RANGE 分区， MySQL: 分区表之一: RANGE 分区, 接下来简单了解下 LIST 分区，例子如下：

创建表

1
2
3
4
5
6
7
8
9
10
11
 
create table user_info (    
 user_id int8 primary key,    
 user_name varchar(64),    
 create_time timestamp  
)  
PARTITION BY LIST ( mod(user_id,4))  (  
PARTITION user_info_p0 VALUES IN (0),  
PARTITION user_info_p1 VALUES IN (1),  
PARTITION user_info_p2 VALUES IN (2),  
PARTITION user_info_p3 VALUES IN (3)  
);

批量插入数据存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
 
DELIMITER //   
CREATE PROCEDURE pro_ins_user_info()   
BEGIN   
 DECLARE i INT;   
SET i =  1;   
  
WHILE i <=  10000 DO  
insert into user_info(user_id,user_name,create_time ) values(i,CONCAT('user_',i),current_timestamp);   
SET i = i +  1;   
 END WHILE;   
END  
//  
DELIMITER ;

执行函数

1
2
 
root@localhost:francs>call pro_ins_user_info()  ;  
Query OK,  1 row affected (17.19 sec)

查看分区表数据分布情况

1
2
3
4
5
6
7
8
9
10
 
root@localhost:francs>  select table_schema,table_name,partition_name,PARTITION_METHOD from information_schema.partitions where table_name='user_info';  
+--------------+------------+----------------+------------------+  
| table_schema | table_name | partition_name | PARTITION_METHOD |  
+--------------+------------+----------------+------------------+  
| francs | user_info | user_info_p0 | LIST |  
| francs | user_info | user_info_p1 | LIST |  
| francs | user_info | user_info_p2 | LIST |  
| francs | user_info | user_info_p3 | LIST |  
+--------------+------------+----------------+------------------+  
4 rows in  set  (0.07 sec)

查看各分区数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
root@localhost:francs>select count(*)  from user_info PARTITION (user_info_p0);  
+----------+  
| count(*)  |  
+----------+  
| 2500  |  
+----------+  
1 row in  set  (0.01 sec)  
  
root@localhost:francs>select count(*)  from user_info PARTITION (user_info_p1);  
+----------+  
| count(*)  |  
+----------+  
| 2500  |  
+----------+  
1 row in  set  (0.00 sec)  
  
root@localhost:francs>select count(*)  from user_info PARTITION (user_info_p2);  
+----------+  
| count(*)  |  
+----------+  
| 2500  |  
+----------+  
1 row in  set  (0.00 sec)  
  
root@localhost:francs>select count(*)  from user_info PARTITION (user_info_p3);  
+----------+  
| count(*)  |  
+----------+  
| 2500  |  
+----------+  
1 row in  set  (0.01 sec)  
  
root@localhost:francs>select  *  from user_info PARTITION (user_info_p0) limit 3;  
+---------+-----------+---------------------+  
| user_id | user_name | create_time |  
+---------+-----------+---------------------+  
| 4  | user_4 |  2014-12-03  13:38:11  |  
| 8  | user_8 |  2014-12-03  13:38:11  |  
| 12  | user_12 |  2014-12-03  13:38:11  |  
+---------+-----------+---------------------+  
3 rows in  set  (0.00 sec)  
  
root@localhost:francs>select  *  from user_info PARTITION (user_info_p1) limit 3;    
+---------+-----------+---------------------+  
| user_id | user_name | create_time |  
+---------+-----------+---------------------+  
| 1  | user_1 |  2014-12-03  13:38:11  |  
| 5  | user_5 |  2014-12-03  13:38:11  |  
| 9  | user_9 |  2014-12-03  13:38:11  |  
+---------+-----------+---------------------+  
3 rows in  set  (0.00 sec)  
  
root@localhost:francs>select  *  from user_info PARTITION (user_info_p2) limit 3;    
+---------+-----------+---------------------+  
| user_id | user_name | create_time |  
+---------+-----------+---------------------+  
| 2  | user_2 |  2014-12-03  13:38:11  |  
| 6  | user_6 |  2014-12-03  13:38:11  |  
| 10  | user_10 |  2014-12-03  13:38:11  |  
+---------+-----------+---------------------+  
3 rows in  set  (0.00 sec)  
  
root@localhost:francs>select  *  from user_info PARTITION (user_info_p3) limit 3;    
+---------+-----------+---------------------+  
| user_id | user_name | create_time |  
+---------+-----------+---------------------+  
| 3  | user_3 |  2014-12-03  13:38:11  |  
| 7  | user_7 |  2014-12-03  13:38:11  |  
| 11  | user_11 |  2014-12-03  13:38:11  |  
+---------+-----------+---------------------+  
3 rows in  set  (0.00 sec)

查看执行计划

根据分区键 user_idd 查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
francs@localhost:francs>explain partitions select  *  from user_info where user_id=1G  
*  1. row *  
 id:  1  
 select_type: SIMPLE  
 table: user_info  
 partitions: user_info_p1  
 type:  const  
possible_keys: PRIMARY  
 key: PRIMARY  
 key_len:  8  
 ref:  const  
 rows:  1  
 Extra: NULL  
1 row in  set  (0.06 sec)

根据非分区键 user_name 查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
francs@localhost:francs>explain partitions select  *  from user_info where user_name='user_1'G  
*  1. row *  
 id:  1  
 select_type: SIMPLE  
 table: user_info  
 partitions: user_info_p0,user_info_p1,user_info_p2,user_info_p3  
 type: ALL  
possible_keys: NULL  
 key: NULL  
 key_len: NULL  
 ref: NULL  
 rows:  10000  
 Extra:  Using  where  
1 row in  set  (0.00 sec)

备注：根据分区键查询走了分区 user_info_p1，而根据非分区键查询走了所有分区。

参考

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/239605.html

赞 (0)
0
上一篇 2022年2月12日
下一篇 2022年2月12日

相关推荐

发表回复

登录后才能评论