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

171次閱讀

共計 3605 個字符，預計需要花費 10 分鐘才能閱讀完成。

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

MySQL 表分區(qū)功能基礎
創(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ù)需要調整)
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ū)時會自動進行分區(qū)存儲, 不必擔心數(shù)據(jù)丟失或者手動分類數(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);
查看標分區(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 hellip; from 表名 where hellip;;
該操作只查看查詢語句相關的分區(qū)信息, 不會返回查詢結果.