一、 分区的概念
二、 为什么使用分区?(优点)
三、 分区类型
四、 子分区
五、 对分区进行修改(增加、删除、分解、合并)
六、 不同引擎的分区特性
七、 分区的限制性
分区概念
分区针对不同的数据库,具有不同的特性。在这里专门针对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