**摘要:**数据太多,就得建分区,查询速度就快,我就提醒一下,建分区的时候最好建的有规律一点,尽量避免查询的时候跨多个分区查询,这样就降低了效率,还有分区建好之后,你表中那个时间的字段不能超过分区那里的显示的时间
1.新建表
CREATE TABLE `test_partition` (
`name` varchar(20) DEFAULT NULL,
`time_test` datetime NOT NULL
)
注意:我这种方式前提是这张已存在分区,我们先新建2个分区
alter table test_partition partition by range (to_days(time_test)) (
partition p20170902 values less than (to_days("2017-09-02")),
partition p20170909 values less than (to_days("2017-09-09"))
);
2.新建表分区的存储过程
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `create_partition_test`$$
/*这里复制的时候注意自己的登录用户*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_partition_test`(IN tableName VARCHAR(20),IN interval_ INT)
BEGIN
/* 事务回滚,其实放这里没什么作用,ALTER TABLE是隐式提交,回滚不了的 */
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
/* 到系统表查出这个表的最大分区,得到最大分区的日期。在创建分区的时候,名称就以日期格式存放,方便后面维护 */
SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name=tableName ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL interval_ DAY))+0;
/* 修改表,在最大分区的后面增加一个分区,时间范围加 interval_ 天 */
SET @s1=CONCAT('ALTER TABLE ' ,tableName ,' ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
/* 输出查看增加分区语句*/
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 提交 */
COMMIT ;
END$$
DELIMITER ;
3.调用存储过程,第一个参数写表名,第二个是分区之间的天数,我这里是7天
自己写好定时器调用即可
call create_partition_test('test_partition',7);
4.查询某个表的所有分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
FROM_DAYS(partition_description) lessthan_sendtime,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = SCHEMA() AND TABLE_NAME='test_partition';
效果如下图
5.删除某一个分区,包括里面的数据
ALTER TABLE test_partition DROP PARTITION p20170926;
6.查询某个表分区下的数据
SELECT * FROM test_partition PARTITION (p20170909);
说明:存储过程是参考网上自己改的,亲测!!!!!!!发现问题了,望留