MySQL分区性能初探

关于MySQL分区讲解地很不错的一篇文章，转自：http://www.cnblogs.com/acpp/archive/2010/08/09/1795464.html

一，      分区概念 

分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。

分区和手动分表对比

手动分表	分区
多张数据表	一张数据表
重复数据的风险	没有数据重复的风险
写入多张表	写入一张表
没有统一的约束限制	强制的约束限制

 

MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：

• Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
• Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
• Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
• List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。
• Composite（复合模式） –以上模式的组合使用　

 

二，分区能做什么

• 逻辑数据分割
• 提高单一的写和读应用速度
• 提高分区范围读查询的速度
• 分割数据能够有多个不同的物理文件路径
• 高效的保存历史数据
• 一个表上的约束检查
• 不同的主从服务器分区策略，例如master按Hash分区，slave按range分区

 

三，分区的限制(截止5.1.44版)

•   只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列

•   最大分区数目不能超过1024

•   如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内

•   不支持外键

•   不支持全文索引（fulltext）

• 按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多

四，什么时候使用分区

•   海量数据表

•   历史表快速的查询，可以采用ARCHIVE+PARTITION的方式。

•   数据表索引大于服务器有效内存

•   对于大表，特别是索引远远大于服务器有效内存时，可以不用索引，此时分区效率会更有效。

五，分区实验

实验一：

使用 US Bureau of Transportation Statistics发布的数据(CSV格式).目前, 包括 1.13 亿条记录，7.5 GB数据5.2 GB索引。时间从1987到2007。

服务器使用4GB内存，这样数据和索引的大小都超过了内存大小。设置为4GB原因是数据仓库大小远远超过可能内存的大小，可能达几TB。对普通OLTP数据库来说，索引缓存在内存中，可以快速检索。如果数据超出内存大小，需要使用不同的方式。

创建有主键的表，因为通常表都会有主键。表的主键太大导致索引无法读入内存，这样一般来说不是高效的，意味着要经常访问磁盘，访问速度完全取决于你的磁盘和处理器。目前在设计很大的数据仓库里，有一种普遍的做法是不使用索引。所以也会比较有和没有主键的性能。

测试方法：

使用三种数据引擘MyISAM, InnoDB, Archive.
对于每一种引擘, 创建一个带主键的未分区表 (除了archive) 和两个分区表，一个按月一个按年。分区表分区方式如下：

CREATE TABLE by_year (

   d DATE

)

PARTITION BY RANGE (YEAR(d))

(

PARTITION P1 VALUES LESS THAN (2001),

PARTITION P2 VALUES LESS THAN (2002),

PARTITION P3 VALUES LESS THAN (2003),

PARTITION P4 VALUES LESS THAN (MAXVALUE)

)

CREATE TABLE by_month (

   d DATE

)

PARTITION BY RANGE (TO_DAYS(d))

(

PARTITION P1 VALUES LESS THAN (to_days(‘2001-02-01′)), — January

PARTITION P2 VALUES LESS THAN (to_days(‘2001-03-01′)), — February

PARTITION P3 VALUES LESS THAN (to_days(‘2001-04-01′)), — March

PARTITION P4 VALUES LESS THAN (MAXVALUE)

)

每一个都在 mysql服务器上的单独的实例上测试, 每实例只有一个库一个表。每种引擘, 都会启动服务, 运行查询并记录结果, 然后关闭服务。服务实例通过MySQL Sandbox创建。

加载数据的情况如下：

ID	引擘	是否分区	数据	大小	备注	加载时间 (*)
1	MyISAM	none	1.13亿	13 GB	with PK	37 min
2	MyISAM	by month	1.13亿	8 GB	without PK	19 min
3	MyISAM	by year	1.13亿	8 GB	without PK	18 min
4	InnoDB	none	1.13亿	16 GB	with PK	63 min
5	InnoDB	by month	1.13亿	10 GB	without PK	59 min
6	InnoDB	by year	1.13亿	10 GB	without PK	57 min
7	Archive	none	1.13亿	1.8 GB	no keys	20 min
8	Archive	by month	1.13亿	1.8 GB	no keys	21 min
9	Archive	by year	1.13亿	1.8 GB	no keys	20 min

*在dual-Xeon服务器上

为了对比分区在大的和小的数据集上的效果，创建了另外9个实例，每一个包含略小于2GB的数据。

查询语句有两种

• 聚集查询

SELECT COUNT(*)

FROM table_name

WHERE date_column BETWEEN start_date and end_date

• 指定记录查询

 SELECT column_list

FROM table_name

WHERE column1 = x and  column2 = y and column3 = z

对于第一种查询，创建不同的日期范围的语句。对于每一个范围，创建一组额外的相同范围日期的查询。每个日期范围的第一个查询是冷查询，意味着是第一次命中，随后的在同样范围内的查询是暖查询，意味着至少部分被缓存。查询语句在the Forge上。

结果：

1带主键的分区表

第一个测试使用复合主键，就像原始数据表使用的一样。主键索引文件达到5.5 GB. 可以看出，分区不仅没有提高性能，主键还减缓了操作。因为如果使用主键索引查询，而索引又不能读入内存，则表现很差。提示我们分区很有用，但是必须使用得当。

+——–+—————–+—————–+—————–+

| 状态   | myisam 不分区   |   myisam 月分区 |  myisam 年分区  |

+——–+—————–+—————–+—————–+

| cold   | 2.6574570285714 |       2.9169642 | 3.0373419714286 |

| warm   | 2.5720722571429 | 3.1249698285714 | 3.1294000571429 |

+——–+—————–+—————–+—————–+

ARCHIVE引擘

+——–+—————-+—————–+—————–+

|  状态  | archive不分区  |   archive月分区|   archive年分区 |

+——–+—————-+—————–+—————–+

| cold   |     249.849563 | 1.2436211111111 | 12.632532527778 |

| warm   |     235.814442 | 1.0889786388889 | 12.600520777778 |

+——–+—————-+—————–+—————–+

注意ARCHIVE引擘月分区的响应时间比使用MyISAM好。

2不带主键的分区表

因为如果主键的大小超出了可用的key buffer，甚至全部内存，所有使用主键的查询都会使用磁盘。新的方式只使用分区，不要主键。性能有显著的提高。

按月分区表得到了70%-90%的性能提高。

+——–+——————+——————+——————+

| 状态   | myisam 不分区    |   myisam 月分区  |  myisam 年分区   |

+——–+——————+——————+——————+

| cold   |  2.6864490285714 | 0.64206445714286 |  2.6343286285714 |

| warm   |  2.8157905714286 | 0.18774977142857 |  2.2084743714286 |

+——–+——————+——————+——————+

为了使区别更明显, 我使用了两个大规模查询，可以利用分区的分区消除功能。

# query 1 – 按年统计

SELECT year(FlightDate) as y, count(*)

FROM flightstats

WHERE FlightDate BETWEEN  “2001-01-01″ and “2003-12-31″

GROUP BY y

# query 2 – 按月统计

SELECT date_format(FlightDate,”%Y-%m”) as m, count(*)

FROM flightstats 

WHERE FlightDate BETWEEN “2001-01-01″ and “2003-12-31″

GROUP BY m

结果显示按月分区表有30%-60%，按年分区表有15%-30%性能提升。

+———-+———–+———–+———–+

| query_id | 不分       | 月分     |   年分    |

+———-+———–+———–+———–+

|        1 | 97.779958 | 36.296519 | 82.327554 |

|        2 |  69.61055 | 47.644986 |  47.60223 |

+———-+———–+———–+———–+

处理器因素

当以上测试在家用机(Intel Dual Core 2.3 MHz CPU)上测试的时候。对于原来的对于dual Xeon 2.66 MHz来说，发现新服务器更快！。

重复上面的测试，令人吃惊:

+——–+——————-+————-+—————–+

|状态    | myisam 不分区     |myisam 月分区|  myisam 年分区  |

+——–+——————-+————-+—————–+

| cold   | 0.051063428571429 |   0.6577062 | 1.6663527428571 |

| warm   | 0.063645485714286 |   0.1093724 | 1.2369152285714 |

+——–+——————-+————-+—————–+

myisam 不分区带主键的表比分区表更快. 分区表的表现和原来一样，但未分区表性能提高了，使得分区显得不必要。既然这台服务器似乎充分利用了索引的好处，我在分区表的分区列上加入了索引。

# 原始表

create table flightstats (

AirlineID int not null,

UniqueCarrier char(3) not null,

Carrier char(3) not null,

FlightDate date not null,

FlightNum char(5) not null,

TailNum char(8) not null,

ArrDelay double not null,

ArrTime datetime not null,

DepDelay double not null,

DepTime datetime not null,

Origin char(3) not null,

Dest char(3) not null,

Distance int not null,

Cancelled char(1) default ‘n’,

primary key (FlightDate, AirlineID, Carrier, UniqueCarrier, FlightNum, Origin, DepTime, Dest)

)

# 分区表

create table flightstats (

AirlineID int not null,

UniqueCarrier char(3) not null,

Carrier char(3) not null,

FlightDate date not null,

FlightNum char(5) not null,

TailNum char(8) not null,

ArrDelay double not null,

ArrTime datetime not null,

DepDelay double not null,

DepTime datetime not null,

Origin char(3) not null,

Dest char(3) not null,

Distance int not null,

Cancelled char(1) default ‘n’,

KEY (FlightDate)

)

PARTITION BY RANGE …

结果是让人满意的,得到35% 性能提高。

+——–+——————-+——————-+——————-+

|状态    | myisam 不分区     |myisam 月分区      |  myisam 年分区   |

+——–+——————-+——————-+——————-+

| cold   | 0.075289714285714 | 0.025491685714286 | 0.072398542857143 |

| warm   | 0.064401257142857 | 0.031563085714286 | 0.056638085714286 |

+——–+——————-+——————-+——————-+

结论：

1.  使用表分区并不是性能提高的保证。它依赖于以下因素：

• 分区使用的列the column used for partitioning;
• 分区函数，如果原始字段不是int型;
• 服务器速度;
• 内存数量.

2.  在应用到生产系统前运行基准测试和性能测试

依赖于你的数据库的用途，你可能得到巨大的性能提高也可能一无所获。如果不小心，甚至有可能会降低性能。

比如：一个使用月分区的表，在总是进行日期范围查询时可以得到极优的速度。但如果没有日期查询，那么会进行全表扫描。 

分区对于海量数据性能提高是一个关键的工具。什么才是海量的数据取决于部署的硬件。盲目使用分区不能保证提高性能，但是在前期基准测试和性能测试的帮助下，可以成为完美的解决方案。

3.  Archive 表可以成为一个很好的折衷方案

Archive 表分区后可以得到巨大的性能提高。当然也依赖于你的用途，没有分区时任何查询都是全表扫描。如果你有不需要变更的历史数据，还要进行按时间的分析统计，使用Archive引擘是极佳的选择。它会使用10-20％的原空间，对于聚集查询有比MyISAM /InnoDB表更好的性能。

虽然一个很好的优化的分区MyISAM 表性能可能好于对应的Archive表, 但是需要10倍的空间。

 

实验二：

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 LESS THAN (1997) ,

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

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

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

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

PARTITION p11 VALUES LESS THAN MAXVALUE );

create table no_part_tab

(c1 int(11) default NULL,

c2 varchar(30) default NULL,

c3 date default NULL) engine=myisam;

2.建一个存储过程, 利用该过程向两个表插入各8百万条不同数据。

delimiter //

CREATE PROCEDURE load_part_tab()

begin

    declare v int default 0;

    while v < 8000000

    do

       insert into part_tab

values (v,’testing partitions’,adddate(‘1995-01-01′,(rand(v)*36520) mod 3652));

       set v = v + 1;

    end while;

end

//

然后执行

mysql> delimiter ;

mysql> call load_part_tab();

Query OK, 1 row affected (8 min 17.75 sec)

mysql> insert into no_part_tab select * from part_tab;

Query OK, 8000000 rows affected (51.59 sec)

Records: 8000000  Duplicates: 0  Warnings: 0

3.开始对这两表中的数据进行简单的范围查询吧。并显示执行过程解析：

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 (38.30 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 (3.88 sec)

mysql> explain select count(*) from no_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: 798458

        Extra: Using where

1 row in set (0.00 sec)

从上面结果可以看出，使用表分区比非分区的减少90％的响应时间。命令解析Explain程序可以看出在对已分区的表的查询过程中仅对第一个分区进行了扫描，其余跳过。进一步测试：

– 增加日期范围

mysql> select count(*) from no_part_tab where c3 > date ‘-01-01′and c3 < date ‘1997-12-31′;

+———-+

| count(*) |

+———-+

| 2396524 |

+———-+

1 row in set (5.42 sec)

mysql> select count(*) from part_tab where c3 > date ‘-01-01′and c3 < date ‘1997-12-31′;

+———-+

| count(*) |

+———-+

| 2396524 |

+———-+

1 row in set (2.63 sec)

– 增加未索引字段查询

mysql> select count(*) from part_tab where c3 > date ‘-01-01′and c3 < date

‘1996-12-31′ and c2=’hello’;

+———-+

| count(*) |

+———-+

| 0 |

+———-+

1 row in set (0.75 sec)

mysql> select count(*) from no_part_tab where c3 > date ‘-01-01′and c3 < da

te ‘1996-12-31′ and c2=’hello’;

+———-+

| count(*) |

+———-+

| 0 |

+———-+

1 row in set (11.52 sec)

 

结论：

• 分区和未分区占用文件空间大致相同 （数据和索引文件）
• 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间
• 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
• 对于大数据量，建议使用分区功能。
• 去除不必要的字段
• 根据手册，增加myisam_max_sort_file_size 会增加分区性能