mysql 分区表查询性能 mysql查询分区数据

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;--不会丢失数据