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；