mysql 5.6 分区与不分区的区别
mysql> CREATE TABLE t1 ( id INT, date DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=Innodb; Query OK, 0 rows affected mysql> insert into t1 values(1, '2013-05-23 12:59:39'); Query OK, 1 row affected mysql> 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 affected Query OK, 1 row affected Query OK, 1 row affected Query OK, 1 row affected Query OK, 1 row affected Query OK, 1 row affected Query OK, 1 row affected Query OK, 1 row affected Query OK, 1 row affected mysql> 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 set mysql> 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 set mysql> 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 set mysql> 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 affected mysql> 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 set mysql> 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 set mysql> 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 set mysql> insert into t2 select * from t1; Query OK, 10 rows affected Records: 10 Duplicates: 0 Warnings: 0 mysql> 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 set mysql> 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 set mysql> 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 set mysql> 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 set mysql>
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) )
;
