Mysql表分区实现
(一)、分区的限制:
1.主键或者唯一索引必须包含分区字段,如primary key (id,username),不过innoDB的大组建性能不好。
2.很多时候,使用分区就不要在使用主键了,否则可能影响性能。
3.只能通过int类型的字段或者返回int类型的表达式来分区,通常使用year或者to_days等函数(mysql 5.6 对限制开始放开了)。
4.每个表最多1024个分区,而且多分区会大量消耗内存。
5.分区的表不支持外键,相关的逻辑约束需要使用程序来实现。
6.分区后,可能会造成索引失效,需要验证分区可行性。
(二)、分区实现:
第一步:添加日期作为主键部分
alter table erp_bill_index drop primary key;-- 删除主键 201s
alter table erp_bill_index add primary key(billid,billdate);-- 添加主键203s
第二步:创建分区
ALTER TABLE erp_bill_index PARTITION by RANGE(to_days(billdate))
(
PARTITION p201801 VALUES LESS THAN (to_days('2018-02-01')),
PARTITION p201802 VALUES LESS THAN (to_days('2018-03-01')),
PARTITION p201803 VALUES LESS THAN (to_days('2018-04-01')),
PARTITION p201804 VALUES LESS THAN (to_days('2018-05-01')),
PARTITION p201805 VALUES LESS THAN (to_days('2018-06-01')),
PARTITION p201806 VALUES LESS THAN (to_days('2018-07-01')),
PARTITION p201807 VALUES LESS THAN (to_days('2018-08-01')),
PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')),
PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')),
PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')),
PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')),
PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')),
PARTITION p201901 VALUES LESS THAN (to_days('2019-02-01')),
PARTITION p201902 VALUES LESS THAN (to_days('2019-03-01')),
PARTITION p201903 VALUES LESS THAN (to_days('2019-04-01')),
PARTITION p201904 VALUES LESS MAXVALUE
);
第三步:添加分区
alter table erp_bill_index add PARTITION
(
PARTITION p201905 VALUES LESS THAN (to_days(‘2019-06-01’)) ENGINE = InnoDB
)
第四部步:自动添加分区
DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲该表所在数据库名称 USE `…
DROP PROCEDURE IF EXISTS create_partition_by_month
KaTeX parse error: Expected 'EOF', got '#' at position 110: …64)) BEGIN #̲当前日期存在的分区的个数 …
DELIMITER ;
第五步:定时任务
DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲该表所在的数据库名称 USE …
CREATE EVENT IF NOT EXISTS month_generate_partition
ON SCHEDULE EVERY 1 MONTH #执行周期,还有天、月等等
STARTS ‘2019-04-01 00:00:00’
ON COMPLETION PRESERVE
ENABLE
COMMENT ‘Creating partitions’
DO BEGIN
#调用刚才创建的存储过程,第一个参数是数据库名称,第二个参数是表名称
CALL testtable.create_partition_by_month(‘testtable’,‘erp_bill_index’);
END$$
DELIMITER ;
(三)、处理定时任务不执行问题:
SHOW VARIABLES LIKE ‘event_scheduler’
SET GLOBAL event_scheduler = 1;
开启事件:
ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE; /event_name 事件名称/
处理问题:
地址:https://zhidao.baidu.com/question/1543863766414881427.html
相应文件
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini skip-grant-tables
(四)、问题整理
1)、错误:Table has no partition for value 738059(没有包含所有数据)
先查询最大值
SELECT billdate from erp_bill_index where billdate>‘2019-05-01’ ORDER BY billdate DESC LIMIT 0,20;
在添加最大分区;
2)、创建分区之后,查询时解析可以看到使用分区情况:
EXPLAIN
SELECT billdate from erp_bill_index_test where billdate>‘2019-05-01’ ORDER BY billdate DESC LIMIT 0,20;
以上步骤执行完成,表分区就实现了,如果出现问题大家可以相互探讨交流,相互学习~