月度归档： 2015年12月

MySQL分区性能测试
By 成江东, on 八月 31st, 2010
一，测试服务器配置：

Cpu: 2*4核Intel(R) Xeon(R) E5405 @ 2.00GHz

内存：4G

存储：36块1T的7.2K的SATA组成raid0。使用其中的1T

Mysql版本：5.1.45-community-log MySQL Community Server (GPL)

二，建测试表：

Myisam不分区不分表，单表10亿数据量：
[code type=sql]
create table biguser

(

ID bigint not null auto_increment comment ‘自增ID’,

Username varchar(30) not null comment ‘用户名’,

sex tinyint not null comment ‘性别’,

birthday datetime not null comment ‘生日’,

email varchar(50) not null comment ‘邮箱’,

tel int not null comment ‘手机’,

intro varchar(255) not null comment ‘个人简介’,

primary key (ID)

)

type = MYISAM

default charset=utf8;
[/code] Continue reading

分区表可以解决按月和按id物理分表引起的表数量过多的问题，提供一定的查询便利，使用过程中需要遵循一些约定，以避免查询扩散到所有分区。
Continue reading

MySQL 5.5的发布带来了许多增强的功能，虽然已经报道了很多增强功能，如半同步复制，但大家却忽略了分区方面的增强，有时甚至还对其真正意义产生了误解，在这篇文章中，我们希望解释一下这些很酷的增强，特别是我们大多数人还没有完全理解的地方。

MySQL 5.5分区增强

非整数列分区

任何使用过分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。

MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。我们假设有这样一个表：
[code type=sql]
CREATE TABLE expenses (
expense_date DATE NOT NULL,
category VARCHAR(30),
amount DECIMAL (10,3)
);
[/code]
如果你想使用MySQL 5.1中的分区类型，那你必须将类型转换成整数，需要使用一个额外的查找表，到了MySQL 5.5中，你可以不用再进行类型转换了，如：
[code type=sql]
ALTER TABLE expenses
PARTITION BY LIST COLUMNS (category)
(
PARTITION p01 VALUES IN ( ‘lodging’, ‘food’),
PARTITION p02 VALUES IN ( ‘flights’, ‘ground transportation’),
PARTITION p03 VALUES IN ( ‘leisure’, ‘customer entertainment’),
PARTITION p04 VALUES IN ( ‘communications’),
PARTITION p05 VALUES IN ( ‘fees’)
);
[/code]
这样的分区语句除了更加易读外，对数据的组织和管理也非常清晰，上面的例子只对category列进行分区。

在MySQL 5.1中使用分区另一个让人头痛的问题是date类型（即日期列），你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，如：
[code type=sql]
/* 在MySQL 5.1中*/
CREATE TABLE t2
(
dt DATE
)
PARTITION BY RANGE (TO_DAYS(dt))
(
PARTITION p01 VALUES LESS THAN (TO_DAYS(‘2007-01-01’)),
PARTITION p02 VALUES LESS THAN (TO_DAYS(‘2008-01-01’)),
PARTITION p03 VALUES LESS THAN (TO_DAYS(‘2009-01-01’)),
PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,
PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,
PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,
PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
[/code]
看上去非常糟糕，当然也有变通办法，但麻烦确实不少。使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。

但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单。
[code type=sql]
/*在MySQL 5.5中*/
CREATE TABLE t2
(
dt DATE
)
PARTITION BY RANGE COLUMNS (dt)
(
PARTITION p01 VALUES LESS THAN (‘2007-01-01’),
PARTITION p02 VALUES LESS THAN (‘2008-01-01’),
PARTITION p03 VALUES LESS THAN (‘2009-01-01’),
PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(dt)
(PARTITION p01 VALUES LESS THAN (‘2007-01-01’) ENGINE = MyISAM,
PARTITION p02 VALUES LESS THAN (‘2008-01-01’) ENGINE = MyISAM,
PARTITION p03 VALUES LESS THAN (‘2009-01-01’) ENGINE = MyISAM,
PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
[/code]
在这里，通过函数定义和通过列查询之间没有冲突，因为是按列定义的，我们在定义中插入的值是保留的。
Continue reading