mysql按照时间分区

文档：https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

查看mysql版本是否大于5.6

查看mysql版本
mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name | Version | Status |
+--------------------+---------+----------+
| binlog | 1.0 | ACTIVE |
| CSV | 1.0 | ACTIVE |
| MEMORY | 1.0 | ACTIVE |
| InnoDB | 5.7 | ACTIVE |
| MyISAM | 1.0 | ACTIVE |
| MRG_MYISAM | 1.0 | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |
| ARCHIVE | 3.0 | ACTIVE |
| BLACKHOLE | 1.0 | ACTIVE |
| FEDERATED | 1.0 | DISABLED |
| partition | 1.0 | ACTIVE |
+--------------------+---------+----------+
11 rows in set


1、查看是否支持分区表
     在mysql5.6及以后版本检查是否开启分区表的方法一
     SHOW PLUGINS ;

查看分区是否为active
mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+


2、创建新表

sql语句
CREATE TABLE `warning_message3` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
`customer_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '客户' ,
`store_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '门店' ,
`rule_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '规则' ,
`rule_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规则类型' ,
`status` tinyint(4) UNSIGNED NULL DEFAULT 0 COMMENT '是否有效' ,
`is_read` tinyint(1) NULL DEFAULT 0 COMMENT '是否已读' ,
`type` tinyint(4) NULL DEFAULT NULL COMMENT '类型' ,
`lng` decimal(20,15) NULL DEFAULT NULL COMMENT '经度' ,
`lat` decimal(20,15) NULL DEFAULT NULL COMMENT '纬度' ,
`location` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地点' ,
`driver_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '设备' ,
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '设备号' ,
`car_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '车辆' ,
`alarm_time` datetime NOT NULL DEFAULT '0000-00-00' COMMENT '报警时间' ,
`start_time` datetime NULL DEFAULT NULL COMMENT '开始时间' ,
`end_time` datetime NULL DEFAULT NULL COMMENT '结束时间' ,
`time_span` decimal(20,0) NULL DEFAULT NULL COMMENT '持续时间' ,
`points` int(11) NULL DEFAULT NULL COMMENT '周边敏感点' ,
`extend` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '其他信息' ,
`remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注' ,
`deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT '删除' ,
`create_id` bigint(20) NULL DEFAULT NULL COMMENT '创建者' ,
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间' ,
`update_id` bigint(20) NULL DEFAULT NULL COMMENT '编辑者' ,
`update_time` datetime NULL DEFAULT NULL COMMENT '编辑时间' ,
`admin_id` bigint(20) NULL DEFAULT NULL COMMENT '管理者' ,
`admin_time` datetime NULL DEFAULT NULL COMMENT '管理时间' ,
PRIMARY KEY (`id`,`alarm_time`),
INDEX `store_id` (`store_id`) USING BTREE ,
INDEX `rule_id` (`rule_id`) USING BTREE ,
INDEX `driver_id` (`driver_id`) USING BTREE ,
INDEX `car_id` (`car_id`) USING BTREE ,
INDEX `code` (`code`) USING BTREE ,
INDEX `alarm_time` (`alarm_time`) USING BTREE ,
INDEX `status` (`status`) USING BTREE ,
INDEX `customer_id` (`customer_id`) USING BTREE ,
INDEX `deleted` (`deleted`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=DYNAMIC
PARTITION BY RANGE COLUMNS(alarm_time) (
 partition p0 values less than ('2018-01-01'),
 partition p1 values less than ('2018-02-01'),
 partition p2 values less than ('2018-03-01'),
 partition p3 values less than ('2018-04-01'),
 partition p4 values less than ('2018-05-01'),
 partition p5 values less than ('2018-06-01'),
 partition p6 values less than ('2018-07-01'),
 partition p7 values less than ('2018-08-01'),
 partition p8 values less than ('2018-09-01'),
 partition p9 values less than ('2018-10-01'),
 partition p10 values less than ('2018-11-01'),
 partition p11 values less than ('2018-12-01'),
 partition p12 values less than ('2019-01-01'),
 partition p13 values less than ('2019-02-01'),
 partition p14 values less than ('2019-03-01'),
 partition p15 values less than ('2019-04-01'),
 partition p16 values less than ('2019-05-01'),
 partition p17 values less than ('2019-06-01'),
 partition p18 values less than ('2019-07-01'),
 partition p19 values less than ('2019-08-01'),
 partition p20 values less than ('2019-09-01'),
 partition p21 values less than ('2019-10-01'),
 partition p22 values less than ('2019-11-01'),
 partition p23 values less than ('2019-12-01')
)
;


3、把alarm_time为空的数据改为非空，比如 0000-00-00

4、复制数据

sql语句

INSERT INTO `warning_message3` (`id`, `customer_id`,
`store_id`, `rule_id`, `rule_type`, `status`, `is_read`, `type`, `lng`,
`lat`, `location`, `driver_id`, `code`, `car_id`,`car_owner_id`, `alarm_time`,
`start_time`, `end_time`, `time_span`, `points`, `extend`, `remark`,
`deleted`, `create_id`, `create_time`, `update_id`, `update_time`,
`admin_id`, `admin_time`) select `id`, `customer_id`, `store_id`,
`rule_id`, `rule_type`, `status`, `is_read`, `type`, `lng`, `lat`,
`location`, `driver_id`, `code`, `car_id`,`car_owner_id`,`alarm_time`, `start_time`,
`end_time`, `time_span`, `points`, `extend`, `remark`, `deleted`,
`create_id`, `create_time`, `update_id`, `update_time`, `admin_id`,
`admin_time` from warning_message limit 500000;
500000,500000
1000000,500000
1500000,500000
2000000,500000
2500000,500000
3000000,500000
3500000,500000
4000000,500000
4500000,500000
5000000,500000


5、查看相关分区是否正确

查看创建的分区数据是否正确
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'warning_message';
如下显示
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |        261 |
| p1             |          0 |
| p2             |        287 |
| p3             |        686 |
| p4             |        826 |
| p5             |        908 |
| p6             |       1823 |
| p7             |       2665 |
| p8             |       8390 |
| p9             |      14394 |
| p10            |      14990 |
| p11            |      21638 |
| p12            |      21353 |
| p13            |      24624 |
| p14            |      18796 |
| p15            |      34213 |
| p16            |      48024 |
| p17            |      68708 |
| p18            |      49800 |
| p19            |      49716 |
| p20            |      35709 |
| p21            |      21287 |
| p22            |       3242 |
| p23            |      10014 |
| p24            |          0 |
+----------------+------------+
25 rows in set


6、把warning_message改为warning_messagebak，把warning_message3改为warning_message

7、手工添加分区2020-01-01，测试添加是否成功

sql语句
1
ALTER TABLE warning_message add partition ( partition p23 values less than ('2020-01-01') );
8、查看最大的分区时间

查看最大的分区时间
mysql> SELECT max(partition_description) des from INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME= 'warning_message';
+--------------+
| des |
+--------------+
| '2020-01-01' |
+--------------+
1 row in set





================================按月自动建立表分区================================

查看定时是否开启

sql语句
1
show global VARIABLES like 'event_scheduler';

开启事件调度器

sql语句
1
SET GLOBAL event_scheduler = on;

(数据库默认是关闭的，这个需要在my.cnf中设置下，否则数据库重启后会自动关闭)

按月自动建立分区

sql语句

create PROCEDURE auto_set_partitions(IN tableName VARCHAR(20),IN timeColName VARCHAR(20))
COMMENT '每月按时添加表分区的存储过程，由定时任务调用'
BEGIN
DECLARE p_id int;
DECLARE nextDate date;
DECLARE lastDate LONG;
#--获取表中的现有的分区数量数量
SELECT COUNT(partition_name) into p_id FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
if p_id=0 then
#--获取下个月第一天的时间值，根据此值设置时间分区
SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH) INTO nextDate from DUAL;
set @v_add=CONCAT('ALTER table ',tableName,' PARTITION by range(TO_DAYS(',timeColName,'))
(partition ',CONCAT('par',p_id),' values less than (\'',nextDate,'\'))');
ELSE
#--获取表中现有的最大的分区日期
SELECT max(partition_description) des into lastDate from INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
#--除去lastDate中的空格
set lastDate = REPLACE(lastDate, '''', '');
SELECT DATE_ADD(lastDate,INTERVAL 1 MONTH) INTO nextDate from DUAL;
set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),
' values less than (\'',nextDate,'\'))');
END IF;
PREPARE stmt from @v_add;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
测试每两秒执行一次

sql语句

create event if not exists test
on schedule every 1 MONTH
on completion preserve
do call auto_set_partitions('warning_message','alarm_time');
==============================================测试==============================================
1、对比一下两个sql，查看是否使用到了分区，以及本次查询影响行数
不使用分区：

查看分区使用情况
explain partitions
select count(*) from warning_message where create_time < '2018-09-30';

使用分区：

explain partitions
select count(*) from warning_message where alarm_time < '2018-09-30';
组合条件不使用分区：

explain partitions
select count(*) from warning_message where create_time < '2018-09-30' and deleted =0;

组合条件使用分区：

explain partitions
select count(*) from warning_message where alarm_time < '2018-09-30' and deleted = 0;


2、执行时间对比
启用：

set profiling = 1;

同时执行sql：

select count(*) from warning_message where create_time < '2019-01-01';
select count(*) from warning_message where alarm_time < '2019-01-01';

查看：

查看效率

show profiles;
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------+
| 1 | 0.181978 | select count(*) from warning_message where create_time < '2019-01-01' |
| 2 | 0.02846825 | select count(*) from warning_message where alarm_time < '2019-01-01' |
+----------+------------+-----------------------------------------------------------------------+
2 rows in set