在掃描操作中，mysql優化器只掃描保護數據的那個分區以減少掃描范圍獲得性能的提高。
分區技術使得數據管理變得簡單，刪除某個分區不會對另外的分區造成影響，分區有系統直接管理不用手工干預。
查詢當前的mysql數據庫版本是否支持分區
show variables like '%partition%';
分區類型
【RANGE 分區】：
基於屬於一個給定連續區間的列值，把多行分配給分區。
【LIST 分區】：
類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。
【HASH分區】：
基於用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL中有效的、產生非負整數值的任何表達式。
【KEY分區】
分區：類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。
【復合分區】：RANGE—HASH, LIST—HASH, RANGE—Key, LIST—Key
[root@node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.28 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
[root@node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.28 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> show variables like '%partition%';
Empty set (0.00 sec)
【RANGE 分區】：
mysql> create table emp_age_range
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> age int
-> )
-> partition by range(age)
-> (
-> partition p1 values less than (10),
-> partition p2 values less than (20),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.80 sec)
mysql> create table emp_birthdate_range
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(year(birthdate))
-> (
-> partition p1 values less than (1980),
-> partition p2 values less than (1990),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.50 sec)
【LIST 分區】
mysql> create table emp_deptno_list
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by list(deptno)
-> (
-> partition p1 values in (10),
-> partition p2 values in (20),
-> partition p3 values in (30)
-> );
Query OK, 0 rows affected (0.66 sec)
【HASH分區】
mysql> create table emp_birthday_hash
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by hash(year(birthdate))
-> partitions 4;
Query OK, 0 rows affected (0.41 sec)
【KEY分區】
mysql> create table emp_birthdate_key
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by key(birthdate)
-> partitions 4;
Query OK, 0 rows affected (1.00 sec)
【復合分區】
mysql> create table emp_birthdate_range_hash
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(salary)
-> subpartition by hash(year(birthdate))
-> subpartitions 3
-> (
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rows affected (0.56 sec)
mysql> create table emp_salary_range_key
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(salary)
-> subpartition by key(birthdate)
-> subpartitions 3
-> (
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rows affected (0.62 sec)
mysql> CREATE TABLE emp_birthdate_list_hash (
-> empno varchar(20) NOT NULL,
-> empname varchar(20) ,
-> deptno int,
-> birthdate date NOT NULL,
-> salary int
->
-> )
-> PARTITION BY list (deptno)
-> subpartition by hash(year(birthdate))
-> subpartitions 3
-> (
-> PARTITION p1 VALUES in (10),
-> PARTITION p2 VALUES in (20)
-> );
Query OK, 0 rows affected (0.55 sec)
mysql> CREATE TABLE emp_list_key (
-> empno varchar(20) NOT NULL,
-> empname varchar(20) ,
-> deptno int,
-> birthdate date NOT NULL,
-> salary int
-> )
-> PARTITION BY list (deptno)
-> subpartition by key(birthdate)
-> subpartitions 3
-> (
-> PARTITION p1 VALUES in (10),
-> PARTITION p2 VALUES in (20)
-> );
Query OK, 0 rows affected (0.88 sec)
mysql> show tables;
+--------------------------+
| Tables_in_test |
+--------------------------+
| emp_age_range |
| emp_birthdate_key |
| emp_birthdate_list_hash |
| emp_birthdate_range |
| emp_birthdate_range_hash |
| emp_birthday_hash |
| emp_deptno_list |
| emp_list_key |
| emp_salary_range_key |
+--------------------------+
9 rows in set (0.00 sec)
1.可以查看創建分區表的create語句 show create table 表名
2.可以查看表是不是分區表 show table status
3.查看information_schema.partitions表 ，可以查看表具有哪幾個分區、分區的方法、分區中數據的記錄數等信息
select
*
from information_schema.partitions where
table_schema = schema()
and table_name='test';
4.查看SQL執行計劃，explain partitions select語句
通過此語句來顯示掃描哪些分區，及他們是如何使用的.
mysql> desc emp_age_range;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| empno | varchar(20) | NO | | NULL | |
| empname | varchar(20) | YES | | NULL | |
| deptno | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show create table emp_age_range;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_age_range | CREATE TABLE `emp_age_range` (
`empno` varchar(20) NOT NULL,
`empname` varchar(20) DEFAULT NULL,
`deptno` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (age)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema() ;
+------+-----------------+----------+------------+
| part | expr | descr | table_rows |
+------+-----------------+----------+------------+
| p1 | age | 10 | 0 |
| p2 | age | 20 | 0 |
| p3 | age | MAXVALUE | 0 |
| p0 | `birthdate` | NULL | 0 |
| p1 | `birthdate` | NULL | 0 |
| p2 | `birthdate` | NULL | 0 |
| p3 | `birthdate` | NULL | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p1 | year(birthdate) | 1980 | 0 |
| p2 | year(birthdate) | 1990 | 0 |
| p3 | year(birthdate) | MAXVALUE | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
| p0 | year(birthdate) | NULL | 0 |
| p1 | year(birthdate) | NULL | 0 |
| p2 | year(birthdate) | NULL | 0 |
| p3 | year(birthdate) | NULL | 0 |
| p1 | deptno | 10 | 0 |
| p2 | deptno | 20 | 0 |
| p3 | deptno | 30 | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
+------+-----------------+----------+------------+
41 rows in set (0.01 sec)
mysql> select
-> *
-> from information_schema.partitions where
-> table_schema = schema() ;
