AUTO模式数据库分区选择语法

分区选择语法

AUTO模式数据库的分区表中允许通过MySQL分区选择语法直接读写某个分区的数据。

语法

SELECT ... FROM tbl_name [PARTITION ( part_name[, part_name, ...] )]

选择一级分区

示例1：查询指定分区表的一个或多个一级分区数据

CREATE TABLE tb_k(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(id, bid) 
    -> PARTITIONS 8;
Query OK, 0 rows affected (2.06 sec)

explain SELECT * FROM tb_k PARTITION( p1,p2 );
+-----------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                               |
|   LogicalView(tables="tb_k[p1,p2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k` AS `tb_k`") |
| HitCache:false                                                                                                        |
| Source:PLAN_CACHE                                                                                                     |
| TemplateId: e210fe50                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.05 sec)

示例2： 删除指定分区表的一个或多个一级分区数据

CREATE TABLE tb_k(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(id, bid) 
    -> PARTITIONS 8;
Query OK, 0 rows affected (3.59 sec)

explain DELETE FROM tb_k PARTITION( p1,p2 );
+---------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                           |
+---------------------------------------------------------------------------------+
| LogicalModifyView(tables="tb_k[p1,p2]", shardCount=2, sql="DELETE FROM `tb_k`") |
| HitCache:false                                                                  |
| Source:PLAN_CACHE                                                               |
| TemplateId: 19bd2adf                                                            |
+---------------------------------------------------------------------------------+

选择二级分区

示例3： 查询指定分区表的一个或多个一级分区或二级分区数据

CREATE TABLE tb_k_k_tp(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(bid,name) 
    -> PARTITIONS 2
    -> SUBPARTITION BY KEY(id) 
    -> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.94 sec)

explain SELECT * FROM tb_k_k_tp PARTITION( p1sp1,p1sp2 )/*指定两个二级分区*/;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                                                    |
|   LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false                                                                                                                             |
| Source:PLAN_CACHE                                                                                                                          |
| TemplateId: 38bba74d                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)

explain SELECT * FROM tb_k_k_tp PARTITION( p1,p2sp2 )/*指定一个一级分区，一个二级分区*/;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                                                                      |
|   LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2,p1sp3,p1sp4,p2sp2]", shardCount=5, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false                                                                                                                                               |
| Source:PLAN_CACHE                                                                                                                                            |
| TemplateId: dbc4cb56                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)