首页 > 代码库 > mysql innodb表分区
mysql innodb表分区
分区的一些优点包括:
1)、与单个磁盘或文件系统分区相比,可以存储更多的数据。
2)、对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id,
COUNT (orders) as order_total FROM sales GROUP BY
salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
分区类型:
· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
·
LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
·
HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL
中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL
服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
range类型分区简介——这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。
分区前的表结构:
mysql> show create table ca_tophitsdata;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ca_tophitsdata | CREATE TABLE `ca_tophitsdata` (
`defid` int(10) unsigned NOT NULL,
`urldefid` int(10) unsigned NOT NULL,
`count` int(10) unsigned NOT NULL,
`day` date NOT NULL,
PRIMARY KEY (`defid`,`day`,`urldefid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
为这个表分区:
mysql> alter table ca_tophitsdata
PARTITION BY RANGE (YEAR(day))
(PARTITION P07 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION P08 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION P09 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION P10 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION PMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 1328977 rows affected (10.88 sec)
Records: 1328977 Duplicates: 0 Warnings: 0
分区后的表结构:
mysql> show create table ca_tophitsdata;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ca_tophitsdata | CREATE TABLE `ca_tophitsdata` (
`defid` int(10) unsigned NOT NULL,
`urldefid` int(10) unsigned NOT NULL,
`count` int(10) unsigned NOT NULL,
`day` date NOT NULL,
PRIMARY KEY (`defid`,`day`,`urldefid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (YEAR(day)) (PARTITION P07 VALUES LESS THAN (2008) ENGINE = InnoDB, PARTITION P08 VALUES LESS THAN (2009) ENGINE = InnoDB, PARTITION P09 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION P10 VALUES LESS THAN (2011) ENGINE = InnoDB, PARTITION PMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
分区前后的数据表文件对比:
ca_tophitsdata.frm
ca_tophitsdata.ibd
---------------------
ca_tophitsdata.frm
ca_tophitsdata.par
ca_tophitsdata#P#P07.ibd
ca_tophitsdata#P#P08.ibd
ca_tophitsdata#P#P09.ibd
ca_tophitsdata#P#P10.ibd
ca_tophitsdata#P#PMAX.ibd
list类型分区简介——LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr”
是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN
(value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。
CREATE TABLE employees (
id INT NOT NULL,
fname
VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT
‘1970-01-01‘,
separated DATE NOT NULL DEFAULT ‘9999-12-31‘,
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN
(3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN
(7,8,15,16)
);
HASH分区简介——要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH
(expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL
整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。
CREATE TABLE employees (
id INT NOT NULL,
fname
VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT
‘1970-01-01‘,
separated DATE NOT NULL DEFAULT ‘9999-12-31‘,
job_code INT,
store_id INT
)
PARTITION BY
HASH(store_id)
PARTITIONS 4;
KEY分区简介——类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3
DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。
本文出自 “菩提” 博客,请务必保留此出处http://zhangxingnan.blog.51cto.com/3241217/1435249