基于MySQL分区性能的详细介绍

(编辑：jimmy 日期: 2024/11/18 浏览：3 次 )

一， 分区概念

分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。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版)

"1" sizset="42">

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

四，什么时候使用分区

"2" sizset="25"> 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. 可以看出，分区不仅没有提高性能，主键还减缓了操作。因为如果使用主键索引查询，而索引又不能读入内存，则表现很差。提示我们分区很有用，但是必须使用得当。

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

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

| cold | 2.6574570285714 | 2.9169642 | 3.0373419714286 |

| warm | 2.5720722571429 | 3.1249698285714 | 3.1294000571429 |

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

ARCHIVE引擘

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

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

| cold | 249.849563 | 1.2436211111111 | 12.632532527778 |

| warm | 235.814442 | 1.0889786388889 | 12.600520777778 |

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

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

2不带主键的分区表

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

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

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

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

| 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来说，发现新服务器更快！。

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

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

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

| 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% 性能提高。

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

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

| 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

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)

"1" sizset="49">

分区和未分区占用文件空间大致相同（数据和索引文件）

如果查询语句中有未建立索引字段，分区时间远远优于未分区时间

如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。

对于大数据量，建议使用分区功能。

去除不必要的字段

根据手册，增加myisam_max_sort_file_size 会增加分区性能

上一篇：记录mysql性能查询过程的使用方法
下一篇：php中使用$_REQUEST需要注意的一个问题