mysql資料庫分區及測試

標籤：sql   刪除   nbsp   style   表示   ons   sql資料庫   丟失   tab   

1、 測試資料庫是否支援分區

mysql可以通過下面語句判斷是否支援分區：

SHOW VARIABLES LIKE ‘%partition%‘;

如果輸出：have_partitioning   YES 表示支援分區。

或者通過：SHOW PLUGINS;

顯示所有外掛程式，如果有partition ACTIVE STORAGE ENGINE GPL 外掛程式則表明支援分區

2、 建立測試表格

3、 將原表格式資料複製到測試的表格

Insert into table_test select * from table

4、 對測試表格進行分區

alter table ta_monitor _history_test partition by RANGE (to_days(monitor_time))  

(PARTITION p0 VALUES LESS THAN (to_days(‘2018-01-01‘)), 

PARTITION p1 VALUES LESS THAN (to_days(‘2018-03-01‘)) , PARTITION p2 VALUES LESS THAN (to_days(‘2018-05-01‘)) , 

PARTITION p3 VALUES LESS THAN (to_days(‘2018-06-01‘)) , PARTITION p4 VALUES LESS THAN (to_days(‘2018-07-01‘)) , 

PARTITION p5 VALUES LESS THAN (to_days(‘2018-08-01‘)) , PARTITION p6 VALUES LESS THAN (to_days(‘2018-09-01‘)) , 

PARTITION p7 VALUES LESS THAN (to_days(‘2018-10-01‘)) , PARTITION p8 VALUES LESS THAN (to_days(‘2018-11-01‘)) , 

PARTITION p9 VALUES LESS THAN (to_days(‘2018-12-01‘)) , PARTITION p10 VALUES LESS THAN (to_days(‘2019-01-01‘)), 

PARTITION p11 VALUES LESS THAN (to_days(‘2019-02-01‘)), PARTITION P12 VALUES LESS THAN (to_days(‘2019-03-01‘)),

PARTITION P13 VALUES LESS THAN (to_days(‘2019-04-01‘)), PARTITION P14 VALUES LESS THAN (to_days(‘2019-05-01‘)),

PARTITION P15 VALUES LESS THAN (to_days(‘2019-06-01‘)), PARTITION P16 VALUES LESS THAN (to_days(‘2019-07-01‘)),

PARTITION P17 VALUES LESS THAN (to_days(‘2019-08-01‘)), PARTITION P18 VALUES LESS THAN (to_days(‘2019-09-01‘)),

PARTITION P19 VALUES LESS THAN (to_days(‘2019-10-01‘)), PARTITION P20 VALUES LESS THAN (to_days(‘2019-11-01‘)),

PARTITION P21 VALUES LESS THAN (to_days(‘2019-12-01‘)), PARTITION P22 VALUES LESS THAN (to_days(‘2020-01-01‘)),

PARTITION P23 VALUES LESS THAN (to_days(‘2020-02-01‘)), PARTITION P24 VALUES LESS THAN (to_days(‘2020-03-01‘)),

PARTITION P25 VALUES LESS THAN MAXVALUE );

5、 查看分區

SELECT  *  FROM

  INFORMATION_SCHEMA.partitions

WHERE

  TABLE_SCHEMA = SCHEMA()

  AND TABLE_NAME=‘ta_monitor _history_test‘;

6、 測試分區

EXPLAIN SELECT

  *

FROM

  `ta_monitor _history`

WHERE monitor_time > ‘2018-07-01 01:00:00‘

  AND monitor_time< ‘2018-07-19 00:00:00‘;

 

  EXPLAIN SELECT

  *

FROM

  `ta_monitorr_history_test`

WHERE monitor_time > ‘2018-07-01 01:00:00‘

  AND monitor_time< ‘2018-07-19 00:00:00‘;

 

Rows顯示遍曆了多少行。

 

 

7、 重新定義range分區表:

Alter table emp partitionbyrange(salary)

(partition p1 values less than (2000),

partition p2 values less than (4000)); ----不會遺失資料

8、刪除表的所有分區:

Alter table emp removepartitioning;--不會遺失資料

 

 

 

 

 

mysql資料庫分區及測試