MySQL 分区表原理及使用详解

分区查询 1）查询某张表一共有多少个分区

mysql> select 
 ->   partition_name,
 ->   partition_expression,
 ->   partition_description,
 ->   table_rows
 -> from 
 ->   INFORMATION_SCHEMA.partitions
 -> where
 ->   table_schema=‘test‘
 ->   and table_name = ‘emp‘;
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | store_id             | 10                    |          0 |
| p1             | store_id             | 20                    |          1 |
+----------------+----------------------+-----------------------+------------+

即，可以从information_schema.partitions表中查询。

2）查看执行计划，判断查询数据是否进行了分区过滤

mysql> explain partitions select * from emp where store_id=10 \G;
*************************** 1. row ***************************
        id: 1
select_type: SIMPLE
     table: emp
partitions: p1
      type: system
possible_keys: NULL
       key: NULL
   key_len: NULL
       ref: NULL
      rows: 1
     Extra: 
1 row in set (0.00 sec)

上面的结果：partitions:p1 表示数据在p1分区进行检索。

MySQL 分区表原理及使用详解

标签：