MYSQL [ 优化 --- 分区 ]

唐默
2023-12-01

MySQL分区概述

  在MySQL中,InnoDB存储引擎长期支持表空间的概念,并且MySQL服务器甚至在分区引入之前,就能配置为存储不同的数据库使用不同的 物理路径。分区(partion)更进一步,它允许你通过设置各种规则将一个表的各个分区跨文件系统存储。实际上,不同位置的不同表分区是作为一个单独的表来存储的。用户所选择的、实现数据分割的规则被称为分区函数(partioning function),这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数

  最常见是的水平分区(horizontal partitioning),也就是将表的不同的元组分配到不同的物理分区上。目前,MySQL5.1还不支持垂直分区(vertical partitioning),即将表的不同列分配到不同的物理分区。你可以使用MySQL支持的大多数存储引擎来创建表的分区,在MySQL 5.1中,同一个表的各个分区必须使用相同的存储引擎,比如,你不能对一个分区使用MyISAM,而对另一个分区使用InnoDB。但是,你可以对同一个数据库的不同的表使用不同的存储引擎。

  要为某个分区表配置一个专门的存储引擎,必须且只能使用[STORAGE] ENGINE选项,这如同为非分区表配置存储引擎一样。但是,必须记住[STORAGE] ENGINE(和其他的表选项)必须列在用在CREATE TABLE语句中的其他任何分区选项之前。

下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表:

  CREATE TABLE ti (id INT, amountDECIMAL(7,2), tr_date DATE)

  ENGINE=INNODB

  PARTITION BY HASH( MONTH(tr_date) )

  PARTITIONS 6;           

  注:分区必须对一个表的所有数据和索引;不能只对数据分区而不对索引分区,反之亦然,同时也不能只对表的一部分进行分区。

  分区对数据库管理系统实现并行处理有着重要的影响,如果对数据进行分区,则很容易进行并行处理,但是,MySQL还没有充分利用分区的这种并行优势,而这也是它改进的方向 (这种分治思想深深的影响着并行计算,而且在并行计算方面具有天然优势)。MySQL的分区,会给系统带来以下一些优点:

  1.与单个磁盘或文件系统分区相比,单个表可以存储更多的数据。

  2.对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。

  3.对于带Where的条件查询语句,可以得到更大的优化;只需要查询某些分区,而不用扫描全部分区。

  还有其它一些优点,不过MySQL 5.1还不支持:

  1.一些聚合函数,比如SUM() 和COUNT(),能够很容易的并行执行;

  2.通过并行I/O,可以大大提高查询的吞吐量。

  注:实际上,分区不论是对I/O并行,还是查询内并行,都有着重要的影响。只不过MySQL在这方面做得还不够多(不过,正在改进),而Oracle对于查询内并行,做了很多工作。

2.2、分区类型

  MySQL 5.1中可用的分区类型包括:

  1.RANGE分区(portioning):根据列值所属的范围区间,将元组分配到各个分区。

  2.LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

  3.HASH分区:根据用户定义的函数的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

  4.KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。

2.2.1、范围分区

  范围分区是通过计算表达式的值所属的范围区间,对元组进行分区。这些区间要求连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中,假定你创建了一个如下的一个表,该表保存有20家音像店的职员记录,这20家音像店的编号从1到20。

  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 NOT NULL,

  store_id INT NOT NULL

  );

  你可以根据需要对该表进行各种分区,比如,你可以通过store_id来进行分区:

  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 NOT NULL,

  store_id INT NOT NULL

  )

  PARTITION BY RANGE (store_id) (

  PARTITION p0 VALUES LESS THAN (6),

  PARTITION p1 VALUES LESS THAN(11),

  PARTITION p2 VALUES LESS THAN(16),

  PARTITION p3 VALUES LESS THAN (21)

  );

很容易确定数据(72, 'Michael', 'Widenius','1998-06-25', NULL, 13)被插入分区p2;但是,如果一条数据的store_id = 21,会怎么样呢?由于没有规则处理大于20的情况,所以服务器会报错。你可以通过如下方式来处理这种情况:

  CREATETABLE employees (

  id INT NOTNULL,

  fnameVARCHAR(30),

  lnameVARCHAR(30),

  hired DATENOT NULL DEFAULT '1970-01-01',

  separatedDATE NOT NULL DEFAULT '9999-12-31',

  job_code INTNOT NULL,

  store_id INTNOT NULL

  )

  PARTITION BY RANGE (store_id) (

  PARTITION p0VALUES LESS THAN (6),

  PARTITION p1VALUES LESS THAN (11),

  PARTITION p2VALUES LESS THAN (16),

  PARTITION p3VALUES LESS THAN MAXVALUE

  );

  MAXVALUE 表示最大的可能的整数值。现在,store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区

  RANGE分区在如下场合特别有用:

  (1) 当需要删除“旧的”数据时。 在上面的例子中,你只需简单地使用 “ALTER TABLEemployees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR(separated) <= 1990;”这样的一个DELETE查询要有效得多。

  (2) 经常依赖于分区属性进行查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUPBY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。注:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。

  范围分区的缺点就是容易出现执行偏斜,这会影响系统性能。

2.2.2、HASH分区

  HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存 在哪个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。

  你可以通过要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONSnum”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。比如:

  CREATE TABLEemployees (

  id INT NOTNULL,

  fnameVARCHAR(30),

  lnameVARCHAR(30),

  hired DATENOT NULL DEFAULT '1970-01-01',

  separatedDATE NOT NULL DEFAULT '9999-12-31',

  job_code INT,

  store_id INT

  )

  PARTITION BY HASH(store_id)

  PARTITIONS 4;

  如果没有PARTITIONS语句,默认分区数为1。但是,PARTITIONS后面没有数字,系统会报错。

  相对于范围分区,HASH分区更可能保证数据均衡分布。

2.2.3、子分区(Subpartitioning)

  子分区,也叫做复合分区(composite partitioning),是对分区表的每个分区的进一步分割。例如,

  CREATE TABLEts (id INT, purchased DATE)

  PARTITION BY RANGE( YEAR(purchased) )

  SUBPARTITION BY HASH( TO_DAYS(purchased) )

  SUBPARTITIONS 2 (

  PARTITION p0VALUES LESS THAN (1990),

  PARTITION p1VALUES LESS THAN (2000),

  PARTITION p2VALUES LESS THAN MAXVALUE

  );

  表ts 有3个RANGE分区。这3个分区中的每一个分区——p0,p1, 和 p2 ——又被进一步分成了2个子分区。实际上,整个表被分成了3 * 2 = 6个分区。但是,由于PARTITION BY RANGE子句的作用,这些分区的头2个只保存“purchased”列中值小于1990的那些记录。

  在MySQL 5.1中,对于已经通过RANGE或LIST分区了的表再进行分区。子分区既可以使用HASH希分区,也可以使用KEY分区。

  为了对个别的子分区指定选项,使用SUBPARTITION 子句来明确定义子分区也是可能的。例如,创建在前面例子中给出的同一个表的、一个更加详细的方式如下:

  CREATE TABLEts (id INT, purchased DATE)

  PARTITION BY RANGE( YEAR(purchased) )

  SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

  PARTITION p0 VALUES LESS THAN (1990) (

  SUBPARTITIONs0,

  SUBPARTITIONs1

  ),

  PARTITION p1 VALUES LESS THAN (2000) (

  SUBPARTITIONs2,

  SUBPARTITIONs3

  ),

  PARTITION p2 VALUES LESS THAN MAXVALUE (

  SUBPARTITIONs4,

  SUBPARTITIONs5

  )

  );

一些注意点:

  (1) 每个分区的子分区数必须相同;

  (2) 如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区,那么就必须定义所有的子分区;

  (3) 每个SUBPARTITION子句必须包含一个子分区的名称;

  (4) MySQL 5.1.7及之前的版本,每个分区的子分区的名称必须唯一,但是在整个表中,没有必要唯一。从MySQL 5.1.8开始,子分区的名称在整个表中都必须唯一。

  子分区可以用于特别大的表,在多个磁盘间分配数据和索引。假设有6个磁盘,分别为/disk0,/disk1, /disk2等,对于如下例子:

  CREATE TABLEts (id INT, purchased DATE)

  PARTITION BY RANGE( YEAR(purchased) )

  SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

  PARTITION p0 VALUES LESS THAN (1990) (

  SUBPARTITIONs0

  DATADIRECTORY = '/disk0/data'

  INDEXDIRECTORY = '/disk0/idx',

  SUBPARTITIONs1

  DATADIRECTORY = '/disk1/data'

  INDEXDIRECTORY = '/disk1/idx'

  ),

  PARTITION p1 VALUES LESS THAN (2000) (

  SUBPARTITIONs2

  DATADIRECTORY = '/disk2/data'

  INDEXDIRECTORY = '/disk2/idx',

  SUBPARTITIONs3

  DATADIRECTORY = '/disk3/data'

  INDEXDIRECTORY = '/disk3/idx'

  ),

  PARTITION p2 VALUES LESS THAN MAXVALUE (

  SUBPARTITIONs4

  DATADIRECTORY = '/disk4/data'

  INDEXDIRECTORY = '/disk4/idx',

  SUBPARTITIONs5

  DATADIRECTORY = '/disk5/data'

  INDEXDIRECTORY = '/disk5/idx'

  )

  );

  3、体验分区

  下面通过例子来体验分区:

  (1)创建如下分区表:

  CREATE TABLE part_tab

  ( c1 int default NULL,

  c2 varchar(30) default NULL,

  c3 date default NULL

  ) engine=myisam

  PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN(1995),

  PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESSTHAN (1997) ,

  PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESSTHAN (1999) ,

  PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESSTHAN (2001) ,

  PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESSTHAN (2003) ,

  PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESSTHAN (2010),

  PARTITION p11 VALUES LESS THAN MAXVALUE );

  (2)创建一个不分区的表:

  create table no_part_tab

  (c1 int(11) default NULL,

  c2 varchar(30) default NULL,

  c3 date default NULL

  ) engine=myisam;

  (1) 创建一个生成8000000行数据的存储过程:

  delimiter //

  CREATE PROCEDURE load_part_tab()

  begin

  declare v int default 0;

  while v < 8000000

  do

insert into part_tab

values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));

  set v = v + 1;

  end while;

  end

  //

  (2) 调用存储过程,生成数据:

  mysql> delimiter ;

  mysql> call load_part_tab();

  Query OK, 1 row affected (6 min 35.39 sec)

  (5)

  mysql> insert into no_part_tab select * from part_tab;

  Query OK, 8000000 rows affected (40.98 sec)

  Records: 8000000 Duplicates: 0 Warnings: 0

  数据准备好了,下面开始测试:

  (6)

  mysql>select count(*) from no_part_tab where

  -> c3 > date '1995-01-01' and c3 < date '1995-12-31';

  +----------+

  | count(*) |

  +----------+

  | 795181 |

  +----------+

  1 row in set (4.23 sec)

  mysql> select count(*) from part_tab where

  -> c3 > date '1995-01-01' and c3 < date '1995-12-31';

  +----------+

  | count(*) |

  +----------+

  | 795181 |

  +----------+

  1 row in set (0.55 sec)

速度差异很明显;下面看一下查询计划:

  (8)

  mysql> explain select count(*) fromno_part_tab where

  -> c3 > date '1995-01-01' and c3 <date '1995-12-31'G

  *************************** 1. row***************************

  id: 1

  select_type: SIMPLE

  table: no_part_tab

  type: ALL

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 8000000

  Extra: Using where

  1 row in set (0.00 sec)

  mysql> explain partitions select count(*)from part_tab where c3 > date '1995-01

  -01' and c3 < date '1995-12-31'G

  *************************** 1. row***************************

  id: 1

  select_type: SIMPLE

  table: part_tab

  partitions: p1

  type: ALL

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 8000000 #why??

  Extra: Using where

  1 row in set (0.00 sec)

附SQL语句:

代码  

  1 CREATE TABLE part_tab

  2 ( c1 int default NULL,

  3 c2 varchar(30) default NULL,

  4 c3 date default NULL

  5 ) engine=myisam

  6 PARTITION BY RANGE (year(c3))

  7 (

  8 PARTITION p0 VALUES LESS THAN (1995),

  9 PARTITION p1 VALUES LESS THAN (1996) ,

  10 PARTITION p2 VALUES LESS THAN (1997) ,

  11 PARTITION p3 VALUES LESS THAN (1998) ,

  12 PARTITION p4 VALUES LESS THAN (1999),

  13 PARTITION p5 VALUES LESS THAN (2000) ,

  14 PARTITION p6 VALUES LESS THAN (2001) ,

  15 PARTITION p7 VALUES LESS THAN (2002) ,

  16 PARTITION p8 VALUES LESS THAN (2003) ,

  17 PARTITION p9 VALUES LESS THAN (2004) ,

  18 PARTITION p10 VALUES LESS THAN (2010),

  19 PARTITION p11 VALUES LESS THAN MAXVALUE

  20 );

  21

  22

  23 create table no_part_tab

  24 (c1 int(11) default NULL,

  25 c2 varchar(30) default NULL,

  26 c3 date default NULL

  27 ) engine=myisam;

  28

  29

  30 delimiter //

  31 CREATE PROCEDURE load_part_tab()

  32 begin

  33 declare v int default 0;

  34 while v < 8000000

  35 do

  36 insert into part_tab(c1,c2,c3)

  37 values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));

  38 set v = v + 1;

  39 end while;

  40 end

  41 //

  42

  43 delimiter ;

  44 call load_part_tab();

  45 explain select count(*) from no_part_tabwhere

  46 c3 > date '1995-01-01' and c3 < date'1995-12-31';

  47

  48 explain select count(*) from part_tab where

  49 c3 > date '1995-01-01' and c3 < date'1995-12-31';

  50

  51

  52

  53

  54 CREATE TABLE part_tab2

  55 (

  56 c1 int default NULL

  57 ) engine=myisam

  58 PARTITION BY RANGE (c1)

  59 (

  60 PARTITION p0 VALUES LESS THAN (5),

  61 PARTITION p1 VALUES LESS THAN (10),

  62 PARTITION p2 VALUES LESS THAN MAXVALUE

  63 );

  64

  65 insert into part_tab2 values(2),(3);

 类似资料: