文档：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
扫一扫
813
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
