mysql中如何查找表分区信息

郑全

CREATE TABLE orders_range (
id int(11)  AUTO_INCREMENT primary key,
last_name varchar(30) ,
store_id int(11) ,
sales_id int(11) ,
order_date date ,
note varchar(50))
engine=innodb
partition by range(id) (
(PARTITION p0 VALUES LESS THAN (10000),
 PARTITION p1 VALUES LESS THAN (20000),
 PARTITION p2 VALUES LESS THAN (30000),
 PARTITION p3 VALUES LESS THAN (40000),
 PARTITION p4 VALUES LESS THAN (50000)
);

--往里面插入值

delimiter //          

create procedure p_insert(p_upper int)
begin
   declare i int default 1;
     while i < p_upper do
     insert into orders_range(id) values(i);
     set i=i+1;
     end while;
end;
//

call p_insert(50000);
//

--查看分区表的定义:show create table orders_range

mysql> show create table orders_range\G
*************************** 1. row ***************************
       Table: orders_range
Create Table: CREATE TABLE `orders_range` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `sales_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50000 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (40000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (50000) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql>

--查看哪些表建立了分区

mysql> show table status\G
*************************** 1. row ***************************
           Name: arc_test
         Engine: ARCHIVE
        Version: 10
     Row_format: Compressed
           Rows: 2
 Avg_row_length: 4354
    Data_length: 8709
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-12 09:21:19
    Update_time: 2015-04-12 09:21:19
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: dept
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-11 14:59:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: emp
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-11 12:13:12
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 4. row ***************************
           Name: emp_bak
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-07 18:54:15
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 5. row ***************************
           Name: emp_bak1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-07 18:54:42
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 6. row ***************************
           Name: home
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 66
    Data_length: 0
Max_data_length: 15379056
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-12 10:14:34
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 7. row ***************************
           Name: job
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 22
    Data_length: 68
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-04-11 15:03:20
    Update_time: 2015-04-11 15:03:20
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 8. row ***************************
           Name: location
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 67108864
 Auto_increment: NULL
    Create_time: 2015-04-11 15:18:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 9. row ***************************
           Name: orders_hash
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 49919
 Avg_row_length: 27
    Data_length: 1376256
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 50000
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:
*************************** 10. row ***************************
           Name: orders_list
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6
 Avg_row_length: 16384
    Data_length: 98304
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 1
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:
*************************** 11. row ***************************
           Name: orders_range
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 49999
 Avg_row_length: 27
    Data_length: 1392640
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 50000
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:
11 rows in set (0.00 sec)

 

 

郑全

--通过数据字典表去获取分区定义信息

mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME, PARTITION_METHOD,SUBPARTITION_METHOD, PARTITION_EXPRESSION,TABLE_ROWS
    ->   from information_schema.partitions
    ->   where PARTITION_METHOD is not null;
+--------------+--------------+----------------+-------------------+------------------+---------------------+----------------------+------------+
| TABLE_SCHEMA | TABLE_NAME   | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | TABLE_ROWS |
+--------------+--------------+----------------+-------------------+------------------+---------------------+----------------------+------------+
| sztech       | orders_hash  | p0             | NULL              | HASH             | NULL                | id                   |      12479 |
| sztech       | orders_hash  | p1             | NULL              | HASH             | NULL                | id                   |      12480 |
| sztech       | orders_hash  | p2             | NULL              | HASH             | NULL                | id                   |      12480 |
| sztech       | orders_hash  | p3             | NULL              | HASH             | NULL                | id                   |      12480 |
| sztech       | orders_list  | p0             | NULL              | LIST             | NULL                | store_id             |          0 |
| sztech       | orders_list  | p1             | NULL              | LIST             | NULL                | store_id             |          0 |
| sztech       | orders_list  | p3             | NULL              | LIST             | NULL                | store_id             |          0 |
| sztech       | orders_list  | p4             | NULL              | LIST             | NULL                | store_id             |          0 |
| sztech       | orders_list  | p2             | NULL              | LIST             | NULL                | store_id             |          0 |
| sztech       | orders_list  | p5             | NULL              | LIST             | NULL                | store_id             |          0 |
| sztech       | orders_range | p0             | NULL              | RANGE            | NULL                | id                   |       9999 |
| sztech       | orders_range | p1             | NULL              | RANGE            | NULL                | id                   |      10000 |
| sztech       | orders_range | p2             | NULL              | RANGE            | NULL                | id                   |      10000 |
| sztech       | orders_range | p3             | NULL              | RANGE            | NULL                | id                   |      10000 |
| sztech       | orders_range | p4             | NULL              | RANGE            | NULL                | id                   |      10000 |
+--------------+--------------+----------------+-------------------+------------------+---------------------+----------------------+------------+
15 rows in set (0.01 sec)

--有哪些分区

mysql> select table_name,group_concat(partition_name)  
        from information_schema.partitions  
       where table_schema='sztech'  
       group by table_name;

+--------------+------------------------------+
| table_name   | group_concat(partition_name) |
+--------------+------------------------------+
| arc_test     | NULL                         |
| dept         | NULL                         |
| emp          | NULL                         |
| emp_bak      | NULL                         |
| emp_bak1     | NULL                         |
| home         | NULL                         |
| job          | NULL                         |
| location     | NULL                         |
| orders_hash  | p3,p2,p1,p0                  |
| orders_list  | p5,p2,p4,p3,p1,p0            |
| orders_range | p0,p1,p2,p3,p4               |
+--------------+------------------------------+
11 rows in set (0.00 sec)

 

郑全

--查看具体分区条件

mysql> select table_name,partition_name,PARTITION_METHOD ,PARTITION_EXPRESSION, partition_DESCRIPTION
    ->   from information_schema.partitions
    ->    where table_schema='sztech'
    ->     and PARTITION_METHOD is not null
    -> ;
+--------------+----------------+------------------+----------------------+-----------------------+
| table_name   | partition_name | PARTITION_METHOD | PARTITION_EXPRESSION | partition_DESCRIPTION |
+--------------+----------------+------------------+----------------------+-----------------------+
| orders_hash  | p0             | HASH             | id                   | NULL                  |
| orders_hash  | p1             | HASH             | id                   | NULL                  |
| orders_hash  | p2             | HASH             | id                   | NULL                  |
| orders_hash  | p3             | HASH             | id                   | NULL                  |
| orders_list  | p0             | LIST             | store_id             | 1,3,4,17              |
| orders_list  | p1             | LIST             | store_id             | 2,12,14               |
| orders_list  | p3             | LIST             | store_id             | 6,8,20                |
| orders_list  | p4             | LIST             | store_id             | 5,7,9                 |
| orders_list  | p2             | LIST             | store_id             | 10,13,15,18           |
| orders_list  | p5             | LIST             | store_id             | 19                    |
| orders_range | p0             | RANGE            | id                   | 10000                 |
| orders_range | p1             | RANGE            | id                   | 20000                 |
| orders_range | p2             | RANGE            | id                   | 30000                 |
| orders_range | p3             | RANGE            | id                   | 40000                 |
| orders_range | p4             | RANGE            | id                   | 50000                 |
+--------------+----------------+------------------+----------------------+-----------------------+
15 rows in set (0.00 sec)

--查看sql语句使用了哪些分区：可以使用 explain partitions

20000以内，有两个分区
mysql> explain partitions select count(*) from orders_range where id<20000\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_range
   partitions: p0,p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 9999
        Extra: Using where; Using index
1 row in set (0.00 sec)

10000 到 20000，只有一个分区
mysql> explain partitions select count(*) from orders_range where id<20000 and id>10000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_range
   partitions: p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5000
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql>

bikong123

已试，赞！