MySQL partition分区I

一、        分区的概念
二、        为什么使用分区?(优点)
三、        分区类型
四、        子分区
五、        对分区进行修改(增加、删除、分解、合并)
六、        不同引擎的分区特性
七、        分区的限制性



分区概念
分区针对不同的数据库,具有不同的特性。在这里专门针对MySQL数据库而言。在MySQL数据库里,分区这个概念是从mysql 5.1才开始提供的。不过目前只有在mysql advanced版本里才提供。

分区是把数据库、或它的组成部分(比如表)分成几个小部分。而且专门介绍的都是’水平分区’,即对表的行进行划分。


分区的优点
1.        可以提高数据库的性能;
2.        对大表(行较多)的维护更快、更容易,因为数据分布在不同的逻辑文件上;
3.        删除分区或它的数据是容易的,因为它不影响其他表。
    
注意:pruning,即截断。意思是说当你查询时,只扫描所需要查询的分区。。其他部分不会扫描。。这就大大地提高了性能。


分区类型
分区具有如下4种类型:
Range分区:是对一个连续性的行值,按范围进行分区;比如:id小于100;id大于100小于200;
List分区:跟range分区类似,不过它存放的是一个离散值的集合。
Hash分区:对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目),或直接使用分区语句,比如partition p0 values in…..。
Key分区:与hash分区类似,只不过分区支持一列或多列,并且MySQL服务器自身提供hash函数。

具体描述:
分区语法:
create table t(id int,name varchar(20)) engine=myisam partition by range(id);

按range范围进行分区:
create table orders_range
(
id int auto_increment primary key,
customer_surname varchar (30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500)
)  engine=myisam 
partition by range(id) 
(
partition p0 values less than(5),
partition p1 values less than(10),
partition p3 values less than(15)
);
其实上面的分区创建,我们可知道,它的表类型为myisam,而每个分区的引擎也是myisam,这个可以通过show create table tablename查看。当我们插入数据到表里时,如果要查看小于8的信息,它之后检索p0和p12个分区。这样就非常快速了。

按list进行分区:
create table orders_list
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500),
index idx(id)
) engine=myisam  partition by list(store_id) 
(
partition p0 values in(1,3),
partition p1 values in2,4,6),
partition p3 values in(10)
);

list 分区只能把你插入的值放在某个已定的分区里,若没有那个值,,就显示不能插入。

按hash进行分区:
create table orders_hash
(
id int auto_increment primary key,
cutomer_surname varchar(30),
store_id int,
salesperon_id int,
order_date date,
note varcahr(500)
) engine=myisam  partition by hash(id) partitions 4;

如果分为4个分区,那当我插入数据时,哪些数据是放在哪些分区里呢? 当我对某个id值进行检索时,它明确说放到哪个分区里?或者说是有什么内部机制?
使用hash分区,最主要就是确保数据的分配,它是基于create table时提供的表达式。不必定义单独的分区,只要使用partitions关键字和所需要分多少个区的数字。语句如上所述。

按key进行分区:
create table orders_key
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
alesperson_id int,
order_Date date,
note varcahr(500),
index_idx(id)
) engine=myisam  partition by key(order_date) partitions 4;
这个分区类似于hash分区,除了MySQL服务器使用它本身的hash表达式,不像其他类型的分区,不必要求使用一个int或null的表达式。

按子分区进行分区:
create table orders_range
(
id int auto_increment primary key,
customer_surname varchar(30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500)
) engine=myisam  partition by range(id) 
subpartition by hash(store_id) subpartitions 2
(
partition p0 values less than(5),
partition p1 values less than(10),
partition p3 values less than(15)
);
当把数据插入到表中时,那什么数据是放在子分区里呢?

================================================
MySQL partition分区II( 续)
获得分区信息
MySQL可以通过如下方式来获取分区表的信息:
Show create tabe table;      //表详细结构
show table status;     //表的各种参数状态
select * from information_schema.partitions;//通过数据字典来查看表的分区信息
explain partitions select * from table;   // 通过此语句来显示扫描哪些分区,及他们是如何使用的.


对分区进行修改 (修改、合并、重定义分区)
修改分区
修改部分分区:
由于我们平常使用的数据库大都是动态运行的,所以只对某个表分区进行修改就OK了。
可以对range或list表分区进行add或drop,也可以对hash或key分区表进行合并或分解。这些动作都在alter table语句里进行。
使用add partition 关键字来对已有分区表进行添加。
Alter 
table 
orders_range
add 
partition 
(
Partition p5 values less than(maxvalue)
)

Reorganize partition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。
Splitting即分解一个已有分区:
Alter table orders_range
reorganize partition p0 into

(
partition n0 values less than(5000),
partition n1 values less than(10000)
);

Merge分区:像上面把p0分成n0和n1,现在在把2个合并为一个。
Alter table orders_range reorganize partition n0,n1 into
(
Partition p0 values less than(10000)
);

修改所有的分区:在into关键字之前或之后都指定多个分区
Alter table orders_range reorganize partition p0,p1,p2,p3,p4,p5 into
(
Partition r0 values less than(25000),
Partition r1 values less than(50000),
Partition r2 values less than(maxvalue)
);

Coalesce 合并分区:
Merge分区的另一种方法就是alter table….coalesce partition语句,你不能对hash或key分区进行删除
Alter table orders_key coalesce partition1;

Redefine重定义分区
Alter table orders_range partition by hash(id) partitions 4;

对分区进行删除 (删除、删除所有分区)
Drop 分区:
可以对range或list类型的分区通过drop partition 关键字进行删除
Alter table orders_range drop partition p0;

注意:
1.对这个分区进行删除时,你会把这个分区的所有数据进行删除,与delete语句相等;
2.在做alter table..drop partition时,必须有drop权限;
3.运行这个删除命令,它不会返回删除了的行,可以通过select count()语句查看。
如果想对多个分区进行删除,可以使用如下命令语句:Alter table orders_range drop partition p1,p2;


删除所有分区
通过如下命令语句删除表中所有分区,最后是一个正规表.
Alter table orders_range remove partitioning;



当进行分区操作,了解对性能所产生的影响是非常有帮助的:
1.创建分区表比无分区的正规表要稍微慢些;
2.通过alter table….drop partition语句进行删除比delete语句要快些;
3.在range或list分区类型上添加分区(alter table…add partition语句)是相当快的,因为没有移动数据到新分区里。
4.当在一个key或hash类型的分区上执行alter table….add partition语句,要依赖表中已有多少行记录,数据越多,它添加一个新分区的时间就越长。当创建一个表时,使用线性hash或线性key分区是相当快的。
5.对成百上千的行记录,进行alter table …coalesce partition, alter table …reorganize partition, alter table…partition by操作命令时,是相当慢的。
6.当使用add partition命令时,线性hash和线性key分区会使coalesce partition操作更快, alter table …remove partitioning比其他都要快,因为mysql没有要求哪个文件来替代行,即使是移动数据。


各种存储引擎的分区
MySQL分区可以对所有MySQL支持的存储引擎进行分区,比如:myisam, innodb, archive, NDBcluster(只可以线性key),falcon, 不支持分区的引擎:merge, federated, csv, blackhole

注意:所有分区和子分区的表类型要一致;
      索引维护要依赖表类型;
      锁住某些行,也依赖于存储引擎;
      分区也属于存储引擎的顶层,所以进行update和insert时,性能不会产生很大的影响。

各种存储引擎使用分区时的限制:
MyISAM引擎:
Myisam引擎允许在使用分区时,把表的不同部分存储在不同地方,包括索引目录和数据目录。
下面是一个关于把数据分布到4个不同的物理磁盘上的myisam分区。
Create table orders_hash2
(
Id int auto_increment primary key, ……
) engine=myisam

Partition by hash(id)
(
Partition p0 index directory=’/data0/orders/idx’
data directory=’ /data0/orders/data’,
Partition p1 index directory=’/data1/orders/idx’
data directory=’ /data1/orders/data’,
Partition p2 index directory=’/data2/orders/idx’
data directory=’ /data2/orders/data’,
Partition p3 index directory=’/data3/orders/idx’
data directory=’ /data3/orders/data’,
);

注意:上面的具体4个分布,在windows系统上目前还不支持。

InnoDB引擎:
Innodb的分区管理与myisam引擎的管理是不同的。


分区的限制性
下面讲到的是一些关于MySQL分区的限制性约束
常见的限制:
所有的分区必须使用同种引擎;
批量装载很慢;
每个表的最大分区数为1024;
不支持三维数据类型(GIS);
不能对临时表进行分区;
不可能对日志表进行分区;

外键和索引方面:
不支持外键;
不支持全文表索引;
不支持load cache和load index into cache;

子分区方面:
只允许对range和list类型的分区再进行分区;
子分区的类型只允许是hash或key.

分区表达式方面:
Range,list, hash分区必须是int类型;
Key分区不可以有text,blob类型;
不允许使用UDF,存储函数,变量,操作符(|,,^,<<,>>,~)和一些内置的函数;
在表创建之后sql mode不可以改变;
在分区表达式中,不允许子查询;
分区表达式中必须包括至少一个列的引用,唯一索引列也可以(包括主键)

===================================================
Mysql分区表局限性总结

Mysql5.1已经发行很久了,本文根据官方文档的翻译和自己的一些测试,对Mysql分区表的局限性做了一些总结,因为个人能力以及测试环境的原因,有可能有错误的地方,还请大家看到能及时指出,当然有兴趣的朋友可以去官方网站查阅。

本文测试的版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.33-log | 
+------------+
1 row in set (0.00 sec)

一、关于Partitioning Keys, Primary Keys, and Unique Keys的限制

在5.1中分区表对唯一约束有明确的规定,每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。
这句话也许不好理解,我们做几个实验:

CREATE TABLE t1   
(      id INT NOT NULL,      
       uid INT NOT NULL,
       PRIMARY KEY (id)
)
PARTITION BY RANGE (id)   
(PARTITION p0 VALUES LESS THAN(5) ENGINE = INNODB,
 PARTITION p1 VALUES LESS THAN(10) ENGINE = INNODB
);
 
CREATE TABLE t1   
(      id INT NOT NULL,      
       uid INT NOT NULL,
       PRIMARY KEY (id)
)
PARTITION BY RANGE (id)   
(PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',
 PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp'
);
 
mysql> CREATE TABLE t1   
    -> (      id INT NOT NULL,      
    ->        uid INT NOT NULL,
    ->        PRIMARY KEY (id),
    ->        UNIQUE KEY (uid)
    -> )
    -> PARTITION BY RANGE (id)   
    -> (PARTITION p0 VALUES LESS THAN(5),
    ->  PARTITION p1 VALUES LESS THAN(10)
    -> );
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

二、关于存储引擎的限制
2.1 MERGE引擎不支持分区,分区表也不支持merge。
2.2 FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。
2.3 CSV引擎不支持分区
2.4 BLACKHOLE引擎不支持分区
2.5 在NDBCLUSTER引擎上使用分区表,分区类型只能是KEY(or LINEAR KEY) 分区。
2.6 当升级MYSQL的时候,如果你有使用了KEY分区的表(不管是什么引擎,NDBCLUSTER除外),那么你需要把这个表dumped在reloaded。
2.7 分区表的所有分区或者子分区的存储引擎必须相同,这个限制也许会在以后的版本取消。
不指定任何引擎(使用默认引擎)。
所有分区或者子分区指定相同引擎。

三、关于函数的限制
在mysql5.1中建立分区表的语句中,只能包含下列函数:
ABS()
CEILING() and FLOOR() (在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型),例如

mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
    -> PARTITION p0 VALUES IN (1,3,5),
    -> PARTITION p1 VALUES IN (2,4,6)
    -> );;
ERROR 1491 (HY000): The PARTITION function returns the wrong type
 
mysql> CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )(
    -> PARTITION p0 VALUES IN (1,3,5),
    -> PARTITION p1 VALUES IN (2,4,6)
    -> );
Query OK, 0 rows affected (0.01 sec)

DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK()

四、其他限制

4.1 对象限制
下面这些对象在不能出现在分区表达式
Stored functions, stored procedures, UDFs, or plugins.
Declared variables or user variables.

4.2 运算限制
支持加减乘等运算出现在分区表达式,但是运算后的结果必须是一个INT或者NULL。 |, &, ^, <<, >>, , ~ 等不允许出现在分区表达式。

4.3 sql_mode限制
官方强烈建议你在创建分区表后,永远别改变mysql的sql_mode。因为在不同的模式下,某些函数或者运算返回的结果可能会不一样。

4.4 Performance considerations.(省略)

4.5 最多支持1024个分区,包括子分区。
当你建立分区表包含很多分区但没有超过1024限制的时候,如果报错 Got error 24 from storage engine,那意味着你需要增大open_files_limit参数。

4.6 不支持外键。MYSQL中,INNODB引擎才支持外键。

4.7 不支持FULLTEXT indexes(全文索引),包括MYISAM引擎。

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> )
    -> PARTITION BY HASH(id)
    -> PARTITIONS 4;
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

4.8 不支持spatial column types。
4.9 临时表不能被分区。

mysql> CREATE Temporary TABLE t1   
    -> (      id INT NOT NULL,      
    ->        uid INT NOT NULL,
    ->        PRIMARY KEY (id)
    -> )
    -> PARTITION BY RANGE (id)   
    -> (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM,
    ->  PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM
    -> );
ERROR 1562 (HY000): Cannot create temporary table with partitions

4.10 log table不支持分区。

mysql> alter table mysql.slow_log PARTITION BY KEY(start_time) PARTITIONS 2;
ERROR 1221 (HY000): Incorrect usage of PARTITION and log table

5.11 分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。唯一的例外是当分区类型为KEY分区的时候,可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。

mysql> CREATE TABLE tkc (c1 CHAR)
    -> PARTITION BY KEY(c1)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE tkc2 (c1 CHAR)
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 4;
ERROR 1491 (HY000): The PARTITION function returns the wrong type
 
mysql> CREATE TABLE tkc3 (c1 INT)
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.00 sec)

5.12 分区键不能是一个子查询。 A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL

5.13 只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。

5.14 分区表不支持Key caches。

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CACHE INDEX login,user_msg,user_msg_p IN keycache1;
+-----------------+--------------------+----------+---------------------------------------------------------------------+
| Table           | Op                 | Msg_type | Msg_text                                                            |
+-----------------+--------------------+----------+---------------------------------------------------------------------+
| test.login      | assign_to_keycache | status   | OK                                                                  | 
| test.user_msg   | assign_to_keycache | status   | OK                                                                  | 
| test.user_msg_p | assign_to_keycache | note     | The storage engine for the table doesn't support assign_to_keycache | 
+-----------------+--------------------+----------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

5.15 分区表不支持INSERT DELAYED.

mysql> insert  DELAYED into user_msg_p values(18156629,0,0,0,0,0,0,0,0,0);
ERROR 1616 (HY000): DELAYED option not supported for table 'user_msg_p'

5.16 DATA DIRECTORY 和 INDEX DIRECTORY 参数在分区表将被忽略。
这个限制应该不存在了:

mysql> CREATE TABLE t1   
    -> (      id INT NOT NULL,      
    ->        uid INT NOT NULL,
    ->        PRIMARY KEY (id)
    -> )
    -> PARTITION BY RANGE (id)   
    -> (PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',
    ->  PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp'
    -> );
Query OK, 0 rows affected (0.01 sec)

5.17 分区表不支持mysqlcheck和myisamchk
在5.1.33版本中已经支持mysqlcheck和myisamchk

./mysqlcheck -u -p -r test user_msg_p;
test.user_msg_p                                    OK
 
./myisamchk -i /u01/data/test/user_msg_p#P#p0.MYI
Checking MyISAM file: /u01/data/test/user_msg_p#P#p0.MYI
Data records: 4423615   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:    0%
 
User time 0.97, System time 0.02
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 324, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 1, Involuntary context switches 5

5.18 分区表的分区键创建索引,那么这个索引也将被分区。分区键没有全局索引一说。
5.19 在分区表使用ALTER TABLE … ORDER BY,只能在每个分区内进行order by。


=================================================

MySQL 分区(Partition)脚本

MySQL 5.1 中新特性分区(partition) shell 脚本。注意 MySQL 只支持小于等于 1024 个分区。

 

#!/bin/sh


# Set these values
PART=0
ORI=5000
STEP=5000
MAX=3000000

for NUM in `seq -f %f $ORI $STEP $MAX | cut -d. -f1`
do
        echo “PARTITION $PART VALUES LESS THAN ($NUM),” >> /tmp/partition.sql
        part=`expr $PART + 1`
done
echo “PARTITION $PART VALUES LESS THAN MAXVALUE >> /tmp/partition.sql

 

 

 

https://blog.csdn.net/binger819623/article/details/5280267

posted @ 2018-03-29 18:12  dion至君  阅读(147)  评论(0编辑  收藏  举报