Mysql表分区实现

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_monthKaTeX 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；