Mysql分區技術 --建立分區表

分区的作用：数据库性能的提升和简化数据管理

在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。

分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不用手工干预。

查询当前的mysql数据库版本是否支持分区

show variables like '%partition%';

分区类型

【RANGE 分区】：

基于属于一个给定连续区间的列值，把多行分配给分区。

 

【LIST 分区】：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

 

【HASH分区】：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

 

【KEY分区】

分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

 

【复合分区】：RANGE—HASH,    LIST—HASH,   RANGE—Key,     LIST—Key

[[email protected] ~]# 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

[[email protected] ~]# 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

|

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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()  ;

获取【下载地址】 企业框架源码