mysql 5.6 分区与不分区的区别-白红宇

mysql 5.6 分区与不分区的区别

阅读量：7128 次

发布时间：2019-06-28

本文共 11834 字，大约阅读时间需要 39 分钟。

mysql> CREATE  TABLE t1  ( id INT, date DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=Innodb;Query OK, 0 rows affectedmysql> insert into t1 values(1, '2013-05-23 12:59:39');Query OK, 1 row affectedmysql> insert into t1 values(2, '2013-05-23 12:59:43');insert into t1 values(3, '2013-05-23 12:59:44');insert into t1 values(4, '2013-07-04 19:35:45');insert into t1 values(5, '2014-04-04 19:35:45' );insert into t1 values(6, '2014-05-04 19:35:45' );insert into t1 values(7,  '2015-05-04 19:35:45');insert into t1 values(8, '2015-05-05 19:35:45');insert into t1 values(9, '2017-05-05 19:35:45');insert into t1 values(10,'2018-05-05 19:35:45' );Query OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedmysql> select * from t1;+----+---------------------+| id | date                |+----+---------------------+|  1 | 2013-05-23 12:59:39 ||  2 | 2013-05-23 12:59:43 ||  3 | 2013-05-23 12:59:44 ||  4 | 2013-07-04 19:35:45 ||  5 | 2014-04-04 19:35:45 ||  6 | 2014-05-04 19:35:45 ||  7 | 2015-05-04 19:35:45 ||  8 | 2015-05-05 19:35:45 ||  9 | 2017-05-05 19:35:45 || 10 | 2018-05-05 19:35:45 |+----+---------------------+10 rows in setmysql> explain select * from t1;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in setmysql> EXPLAIN SELECT * FROM t1 WHERE date >= '2014-03-05 19:00:12' AND date <= '2016-03-05 18:45:12';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in setmysql>  CREATE  TABLE t2  ( id INT, date DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=Innodb        PARTITION BY RANGE (YEAR(date)) (        PARTITION p2013 VALUES LESS THAN(2014),        PARTITION p2014 VALUES LESS THAN(2015),        PARTITION p2015 VALUES LESS THAN(2016),        PARTITION p2016 VALUES LESS THAN(2017),        PARTITION p2017 VALUES LESS THAN(2018),        PARTITION p2099 VALUES LESS THAN MAXVALUE     ) ;Query OK, 0 rows affectedmysql> show create table t2;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t2    | CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL,  `date` datetime DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (YEAR(date))(PARTITION p2013 VALUES LESS THAN (2014) ENGINE = InnoDB, PARTITION p2014 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB, PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB, PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB, PARTITION p2099 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in setmysql>  SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS  WHERE  table_schema=database() AND table_name='t2';+------------+----------------+------------+| table_name | partition_name | table_rows |+------------+----------------+------------+| t2         | p2013          |          0 || t2         | p2014          |          0 || t2         | p2015          |          0 || t2         | p2016          |          0 || t2         | p2017          |          0 || t2         | p2099          |          0 |+------------+----------------+------------+6 rows in setmysql>  EXPLAIN SELECT * FROM t2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    6 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in setmysql> insert into t2 select * from t1;Query OK, 10 rows affectedRecords: 10  Duplicates: 0  Warnings: 0mysql> explain select *  from t2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   11 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in setmysql> select *  from t2;+----+---------------------+| id | date                |+----+---------------------+|  1 | 2013-05-23 12:59:39 ||  2 | 2013-05-23 12:59:43 ||  3 | 2013-05-23 12:59:44 ||  4 | 2013-07-04 19:35:45 ||  5 | 2014-04-04 19:35:45 ||  6 | 2014-05-04 19:35:45 ||  7 | 2015-05-04 19:35:45 ||  8 | 2015-05-05 19:35:45 ||  9 | 2017-05-05 19:35:45 || 10 | 2018-05-05 19:35:45 |+----+---------------------+10 rows in setmysql>  SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS  WHERE  table_schema=database() AND table_name='t2';+------------+----------------+------------+| table_name | partition_name | table_rows |+------------+----------------+------------+| t2         | p2013          |          4 || t2         | p2014          |          2 || t2         | p2015          |          2 || t2         | p2016          |          0 || t2         | p2017          |          1 || t2         | p2099          |          1 |+------------+----------------+------------+6 rows in setmysql>  EXPLAIN PARTITIONS SELECT * FROM t2 WHERE date >= '2014-03-05 19:00:12' AND date <= '2016-03-05 18:45:12';+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | partitions        | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | t2    | p2014,p2015,p2016 | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+1 row in setmysql>

SELECT *

FROM tb_common_biz

PARTITION (p201707);

如果可以通过代码确定使用的PARTITION，可以这样查：

SELECT * FROM B PARTITION(p1)

数据插入完成后，要验证是否对应id的数据保存在了对应的分区，可以使用查询分区的命令，如下：

SELECT partition_name,partition_expression,partition_description,table_rows

FROM information_schema.PARTITIONS

WHERE table_schema = SCHEMA() AND table_name='tb_common_biz'

DROP TABLE IF EXISTS `tb_common_biz`;

CREATE TABLE `tb_common_biz` (

`data_id` bigint(20) NOT NULL,

`data_category` varchar(32) DEFAULT NULL,

`service_type` varchar(32) DEFAULT NULL,

`group_id` varchar(32) DEFAULT NULL,

`group_name` varchar(64) DEFAULT NULL,

`create_person_id` varchar(16) DEFAULT NULL,

`create_person_name` varchar(64) DEFAULT NULL,

`create_person_telno` varchar(32) DEFAULT NULL,

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`update_person` varchar(16) DEFAULT NULL,

`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`result` varchar(256) DEFAULT NULL,

`result_ext` varchar(256) DEFAULT NULL,

`service_owner` varchar(64) DEFAULT NULL,

`process_instance_id` varchar(64) DEFAULT NULL,

`task_id` varchar(64) DEFAULT NULL,

`ext_activiti_info` varchar(256) DEFAULT NULL,

`status` varchar(256) DEFAULT NULL,

`ext_status` varchar(256) DEFAULT NULL,

`data1` varchar(1024) DEFAULT NULL,

`data2` varchar(1024) DEFAULT NULL,

`data3` varchar(1024) DEFAULT NULL,

`data4` varchar(1024) DEFAULT NULL,

`data5` varchar(1024) DEFAULT NULL,

`data6` varchar(1024) DEFAULT NULL,

`data7` varchar(1024) DEFAULT NULL,

`data8` varchar(1024) DEFAULT NULL,

`data9` varchar(1024) DEFAULT NULL,

`data10` varchar(1024) DEFAULT NULL,

PRIMARY KEY (`data_id`)

)

PARTITION BY RANGE (data_id) (

PARTITION p201704 VALUES LESS THAN (2017050100010000001),

PARTITION p201705 VALUES LESS THAN (2017060100010000001),

PARTITION p201706 VALUES LESS THAN (2017070100010000001),

PARTITION p201707 VALUES LESS THAN (2017080100010000001),

PARTITION p201708 VALUES LESS THAN (2017090100010000001),

PARTITION p201709 VALUES LESS THAN (2017100100010000001),

PARTITION p201710 VALUES LESS THAN (2017110100010000001),

PARTITION p201711 VALUES LESS THAN (2017120100010000001),

PARTITION p201712 VALUES LESS THAN (2018010100010000001),

PARTITION p201801 VALUES LESS THAN (2018020100010000001),

PARTITION p201802 VALUES LESS THAN (2018030100010000001),

PARTITION p201803 VALUES LESS THAN (2018040100010000001),

PARTITION p201804 VALUES LESS THAN (2018050100010000001),

PARTITION p201805 VALUES LESS THAN (2018060100010000001),

PARTITION p201806 VALUES LESS THAN (2018070100010000001),

PARTITION p201807 VALUES LESS THAN (2018080100010000001),

PARTITION p201808 VALUES LESS THAN (2018090100010000001),

PARTITION p201809 VALUES LESS THAN (2018100100010000001),

PARTITION p201810 VALUES LESS THAN (2018110100010000001),

PARTITION p201811 VALUES LESS THAN (2018120100010000001),

PARTITION p201812 VALUES LESS THAN (2019010100010000001),

PARTITION p2019 VALUES LESS THAN (MAXVALUE) )

;

转载地址：http://apael.baihongyu.com/

你可能感兴趣的文章

【Transact-SQL】一句SQL删除重复记录

CentOS6.5 NFS服务器的安装与基本参数

查看>>

I/O多路转接之select

查看>>

让有些“-l”链接静态库，而另一些链接共享库？

查看>>

使用Webstorm操作git

查看>>

uboot移植之start_armboot()函数分析