首页 > 代码库 > 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