Mysql 5.1分区特性及5.5的关于分区的升级

Mysql 5.1分区特性

以前，当 MySQL的总记录数超过了100万后，会出现性能的大幅度下降，但是性能下降的比率不一而同，要看系统的架构、应用程序、还有包括索引、服务器硬件等多 种因素而定。当有网友问DBA这个问题的时候，最常见的回答一般就是：分表，可以根据id区间或者时间先后顺序等多种规则来分表。分表很容易，然而由此所 带来的应用程序甚至是架构方面的改动工作却不容小觑，还包括将来的扩展性等。在以前，一种解决方案就是使用 MERGE类型，这是一个非常方便的做法。架构和程序基本上

用做改动，不过，它的缺点是显见的： 1只能在相同结构的 MyISAM 表上使用 2.无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索 3.它需要使用更多的文件描述符 4.读取索引更慢 这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了： 1.与单个磁盘或文件系统分区相比，可以存储更多的数据 2.很容易就能删除不用或者过时的数据 3.一些查询可以得到极大的优化　　 4.涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行 5.IO吞吐量更大 分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。 现在我们来了解下分区的类型 1. range分区：基于属于一个给定连续区间的列值，把多行分配给分区。（一般我们会把时间作为range分区，但是这里我们得注意点时间的类型）date类型，datetime类型都是支持用years()函数 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 RANGE (YEAR(separated)) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.04 sec)

CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATEtime NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT)PARTITION BY RANGE (YEAR(separated)) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.02 sec)

同时也支持to_days() CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired date NOT NULL DEFAULT '1970-01-01', separated datetime NOT NULL DEFAULT '0000-00-00 00:00:00', job_code INT, store_id INT  )  PARTITION BY RANGE (to_days(separated)) ( PARTITION p0 VALUES LESS THAN (to_days('1991-01-01')), PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')),  PARTITION p2 VALUES LESS THAN (to_days('2001-01-01')), PARTITION p3 VALUES LESS THAN MAXVALUE  )

然而5.1和5.5中timestamp类型都不支持用years()

CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired date NOT NULL DEFAULT '1970-01-01', separated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', job_code INT, store_id INT)PARTITION BY RANGE (YEAR(separated)) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE);

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

5.1．41中用timestamp类型分区的话得用to_days() CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired date NOT NULL DEFAULT '1970-01-01',

separated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

job_code INT, store_id INT

) PARTITION BY RANGE (to_days(separated)) ( PARTITION p0 VALUES LESS THAN (to_days('1991-01-01')), PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')), PARTITION p2 VALUES LESS THAN (to_days('2001-01-01')), PARTITION p3 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.01 sec)

CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired date NOT NULL DEFAULT '1970-01-01', separated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', job_code INT, store_id INT

) PARTITION BY RANGE (UNIX_TIMESTAMP(separated)) ( PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('1991-01-01 00:00:00')), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('1996-01-01 00:00:00')), PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2001-01-01 00:00:00')), PARTITION p3 VALUES LESS THAN MAXVALUE ); ERROR 1564 (HY000): This partition function is not allowed

在5.5中则只能用unix_timestamp（）来进行格式转换

CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired date NOT NULL DEFAULT '1970-01-01', separated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', job_code INT, store_id INT 

)  PARTITION BY RANGE (UNIX_TIMESTAMP(separated)) ( PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('1991-01-01 00:00:00')),      PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('1996-01-01 00:00:00')),      PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2001-01-01 00:00:00')),      PARTITION p3 VALUES LESS THAN MAXVALUE  ); Query OK, 0 rows affected (0.30 sec)r

CREATE TABLE employees (

id INT NOT NULL, fname VARCHAR(30),

lname VARCHAR(30), hired date NOT NULL DEFAULT '1970-01-01', separated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', job_code INT, store_id INT

)PARTITION BY RANGE (to_days(separated)) (

PARTITION p0 VALUES LESS THAN (to_days('1991-01-01')), PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')), PARTITION p2 VALUES LESS THAN (to_days('2001-01-01')), PARTITION p3 VALUES LESS THAN MAXVALUE );

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

2. LIST 分区：类似于按RANGE分区，区别在于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) )； 插入分区的数据必须得存在于分区列表中，否则会出错

3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL 中有效的、产生非负整数值的任何表达式。他主要按取模mod()进行Hash分配：比如插入ID为5的，分区共有4个，则根据取模公式MOD(expr, num)，数据将会被存在mod(5,4)=1 第一个分区中 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 Query OK, 0 rows affected (0.03 sec) 所谓的hash分区：HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集 合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。 如果列值与表达式值之比的曲线图越接近由等式“y=nx（其中n为非零的常数）描绘出的直线，则该表达式越适合于 哈希。这是因为，表达式的非线性越严重，分区中数据产生非均衡分布的趋势也将越严重。

4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。 然而在5.5中，分区表中有了主要的改变在于

1） 多列分区：COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区； 2） 可用性增强：truncate分区。分区最吸引人的一个功能是瞬间移除大量记录的能力，DBA都喜欢将历史记录存储到按日期分区的分区表中，这样可以定期 删除过时的历史数据。　但当你需要移除分区中的部分数据时，事情就不是那么简单了，删除分区没有问题，但如果是清空分区，就很头痛了，要移除分区中的所有 数据，但需要保留分区本身，你可以：使用DELETE语句，但我们知道DELETE语句的性能都很差。使用DROP PARTITION语句，紧跟着一个EORGANIZE PARTITIONS语句重新创建分区，但这样做比前一个方法的成本要高出许多。MySQL 5.5引入了TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。TRUNCATE PARTITION应该是DBA工具箱中的必备工具； 3） 更多微调功能：TO_SECONDS：分区增强包有一个新的函数处理DATE和DATETIME列，使用TO_SECONDS函数，你可以将日期/时间列 转换成自0年以来的秒数，如果你想使用小于1天的间隔进行分区，那么这个函数就可以帮到你。

今天先讲道这里，下次研究子分区特性。