MySQL中怎么實(shí)現(xiàn)表分區(qū)功能

今天就跟大家聊聊有關(guān)MySQL中怎么實(shí)現(xiàn)表分區(qū)功能，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

　MySQL表分區(qū)功能基礎(chǔ)
　　創(chuàng)建分區(qū)表
　　CREATE TABLE `表名` (
　　`EQUIPMENTID` char(17) NOT NULL,
　　`ATTRIBUTEID` char(4) NOT NULL,
　　`VALUE` varchar(20) NOT NULL,
　　`COLLECTTIME` datetime NOT NULL
　　)
　　ENGINE=InnoDB (適用大部分引擎,可根據(jù)需要調(diào)整)
　　DEFAULT CHARSET=gbk(編碼可根據(jù)需要修改)
　　PARTITION BY RANGE (to_days(COLLECTTIME))
　　(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
　　PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,
　　PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
　　PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,
　　PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
　　PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,
　　PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
　　PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,
　　PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
　　PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,
　　PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
　　PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
　　PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
　　PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
　　PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
　　PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
　　PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
　　PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
　　PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
　　PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
　　PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
　　PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
　　PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
　　PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
　　PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
　　PARTITION pmax VALUES LESS THAN MAXVALUE );
　　為現(xiàn)有表創(chuàng)建分區(qū)
　　alter table 表名
　　PARTITION BY RANGE (to_days(COLLECTTIME))
　　(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
　　PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,
　　PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
　　PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,
　　PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
　　PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,
　　PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
　　PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,
　　PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
　　PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,
　　PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
　　PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
　　PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
　　PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
　　PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
　　PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
　　PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
　　PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
　　PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
　　PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
　　PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
　　PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
　　PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
　　PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
　　PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
　　PARTITION pmax VALUES LESS THAN MAXVALUE );
　　如果表中已有數(shù)據(jù),分區(qū)時(shí)會(huì)自動(dòng)進(jìn)行分區(qū)存儲(chǔ),不必?fù)?dān)心數(shù)據(jù)丟失或者手動(dòng)分類數(shù)據(jù).
　　刪除表中的指定分區(qū)
　　ALTER TABLE 表名 DROP PARTITION 分區(qū)名;
　　追加表分區(qū)
　　ALTER TABLE 表名 DROP PARTITION pmax;
　　ALTER TABLE 表名
　　ADD PARTITION (
　　PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),
　　PARTITION pmax VALUES LESS THAN MAXVALUE);
　　查看標(biāo)分區(qū)信息
　　SELECT
　　partition_name part,
　　partition_expression expr,
　　partition_description descr,
　　table_rows
　　FROM
　　INFORMATION_SCHEMA.partitions
　　WHERE
　　TABLE_SCHEMA = schema()
　　AND TABLE_NAME='表名';
　　查看查詢語句涉及分區(qū)信息
　　explain partitions
　　select … from 表名 where …;
　　該操作只查看查詢語句相關(guān)的分區(qū)信息,不會(huì)返回查詢結(jié)果.

看完上述內(nèi)容，你們對(duì)MySQL中怎么實(shí)現(xiàn)表分區(qū)功能有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。