基于MySQL分区性能的详细介绍
一， 分区概念
分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。
分区和手动分表对比
MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：
二，分区能做什么
三，分区的限制(截止5.1.44版)
• 只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
• 最大分区数目不能超过1024
• 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内
• 不支持外键
• 不支持全文索引（fulltext）
四，什么时候使用分区
• 海量数据表
• 历史表快速的查询，可以采用ARCHIVE+PARTITION的方式。
• 数据表索引大于服务器有效内存
• 对于大表，特别是索引远远大于服务器有效内存时，可以不用索引，此时分区效率会更有效。
五，分区实验
实验一：
使用USBureauofTransportationStatistics发布的数据(CSV格式).目前,包括1.13亿条记录，7.5GB数据5.2GB索引。时间从1987到2007。
服务器使用4GB内存，这样数据和索引的大小都超过了内存大小。设置为4GB原因是数据仓库大小远远超过可能内存的大小，可能达几TB。对普通OLTP数据库来说，索引缓存在内存中，可以快速检索。如果数据超出内存大小，需要使用不同的方式。
创建有主键的表，因为通常表都会有主键。表的主键太大导致索引无法读入内存，这样一般来说不是高效的，意味着要经常访问磁盘，访问速度完全取决于你的磁盘和处理器。目前在设计很大的数据仓库里，有一种普遍的做法是不使用索引。所以也会比较有和没有主键的性能。
测试方法：
使用三种数据引擘MyISAM,InnoDB,Archive.
对于每一种引擘,创建一个带主键的未分区表(除了archive)和两个分区表，一个按月一个按年。分区表分区方式如下：
CREATETABLEby_year(
dDATE
)
PARTITIONBYRANGE(YEAR(d))
(
PARTITIONP1VALUESLESSTHAN(2001),
PARTITIONP2VALUESLESSTHAN(2002),
PARTITIONP3VALUESLESSTHAN(2003),
PARTITIONP4VALUESLESSTHAN(MAXVALUE)
)
CREATETABLEby_month(
dDATE
)
PARTITIONBYRANGE(TO_DAYS(d))
(
PARTITIONP1VALUESLESSTHAN(to_days(‘2001-02-01′)),—January
PARTITIONP2VALUESLESSTHAN(to_days(‘2001-03-01′)),—February
PARTITIONP3VALUESLESSTHAN(to_days(‘2001-04-01′)),—March
PARTITIONP4VALUESLESSTHAN(MAXVALUE)
)
每一个都在mysql服务器上的单独的实例上测试,每实例只有一个库一个表。每种引擘,都会启动服务,运行查询并记录结果,然后关闭服务。服务实例通过MySQLSandbox创建。
加载数据的情况如下：
*在dual-Xeon服务器上
为了对比分区在大的和小的数据集上的效果，创建了另外9个实例，每一个包含略小于2GB的数据。
查询语句有两种
SELECTCOUNT(*)
FROMtable_name
WHEREdate_columnBETWEENstart_dateandend_date
SELECTcolumn_list
FROMtable_name
WHEREcolumn1=xand column2=yandcolumn3=z
对于第一种查询，创建不同的日期范围的语句。对于每一个范围，创建一组额外的相同范围日期的查询。每个日期范围的第一个查询是冷查询，意味着是第一次命中，随后的在同样范围内的查询是暖查询，意味着至少部分被缓存。查询语句在theForge上。
结果：
1带主键的分区表
第一个测试使用复合主键，就像原始数据表使用的一样。主键索引文件达到5.5GB.可以看出，分区不仅没有提高性能，主键还减缓了操作。因为如果使用主键索引查询，而索引又不能读入内存，则表现很差。提示我们分区很有用，但是必须使用得当。
+——?+—————?+—————?+—————?+
|状态 |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不带主键的分区表
因为如果主键的大小超出了可用的keybuffer，甚至全部内存，所有使用主键的查询都会使用磁盘。新的方式只使用分区，不要主键。性能有显著的提高。
按月分区表得到了70%-90%的性能提高。
+——?+——————+——————+——————+
|状态 |myisam不分区 | myisam月分区 | myisam年分区 |
+——?+——————+——————+——————+
|cold | 2.6864490285714|0.64206445714286| 2.6343286285714|
|warm | 2.8157905714286|0.18774977142857| 2.2084743714286|
+——?+——————+——————+——————+
为了使区别更明显,我使用了两个大规模查询，可以利用分区的分区消除功能。
#query1?按年统计
SELECTyear(FlightDate)asy,count(*)
FROMflightstats
WHEREFlightDateBETWEEN “2001-01-01″and“2003-12-31″
GROUPBYy
#query2?按月统计
SELECTdate_format(FlightDate,”%Y-%m”)asm,count(*)
FROMflightstats
WHEREFlightDateBETWEEN“2001-01-01″and“2003-12-31″
GROUPBYm
结果显示按月分区表有30%-60%，按年分区表有15%-30%性能提升。
+———-+———?+———?+———?+
|query_id|不分 |月分 | 年分 |
+———-+———?+———?+———?+
| 1|97.779958|36.296519|82.327554|
| 2| 69.61055|47.644986| 47.60223|
+———-+———?+———?+———?+
处理器因素
当以上测试在家用机(IntelDualCore2.3MHzCPU)上测试的时候。对于原来的对于dualXeon2.66MHz来说，发现新服务器更快！。
重复上面的测试，令人吃惊:
+——?+——————-+————-+—————?+
|状态 |myisam不分区 |myisam月分区| myisam年分区 |
+——?+——————-+————-+—————?+
|cold |0.051063428571429| 0.6577062|1.6663527428571|
|warm |0.063645485714286| 0.1093724|1.2369152285714|
+——?+——————-+————-+—————?+
myisam不分区带主键的表比分区表更快.分区表的表现和原来一样，但未分区表性能提高了，使得分区显得不必要。既然这台服务器似乎充分利用了索引的好处，我在分区表的分区列上加入了索引。
#原始表
createtableflightstats(
AirlineIDintnotnull,
UniqueCarrierchar(3)notnull,
Carrierchar(3)notnull,
FlightDatedatenotnull,
FlightNumchar(5)notnull,
TailNumchar(8)notnull,
ArrDelaydoublenotnull,
ArrTimedatetimenotnull,
DepDelaydoublenotnull,
DepTimedatetimenotnull,
Originchar(3)notnull,
Destchar(3)notnull,
Distanceintnotnull,
Cancelledchar(1)default‘n",
primarykey(FlightDate,AirlineID,Carrier,UniqueCarrier,FlightNum,Origin,DepTime,Dest)
)
#分区表
createtableflightstats(
AirlineIDintnotnull,
UniqueCarrierchar(3)notnull,
Carrierchar(3)notnull,
FlightDatedatenotnull,
FlightNumchar(5)notnull,
TailNumchar(8)notnull,
ArrDelaydoublenotnull,
ArrTimedatetimenotnull,
DepDelaydoublenotnull,
DepTimedatetimenotnull,
Originchar(3)notnull,
Destchar(3)notnull,
Distanceintnotnull,
Cancelledchar(1)default‘n",
KEY(FlightDate)
)
PARTITIONBYRANGE…
结果是让人满意的,得到35%性能提高。
+——?+——————-+——————-+——————-+
|状态 |myisam不分区 |myisam月分区 | myisam年分区 |
+——?+——————-+——————-+——————-+
|cold |0.075289714285714|0.025491685714286|0.072398542857143|
|warm |0.064401257142857|0.031563085714286|0.056638085714286|
+——?+——————-+——————-+——————-+
结论：
1. 使用表分区并不是性能提高的保证。它依赖于以下因素：
2. 在应用到生产系统前运行基准测试和性能测试
依赖于你的数据库的用途，你可能得到巨大的性能提高也可能一无所获。如果不小心，甚至有可能会降低性能。
比如：一个使用月分区的表，在总是进行日期范围查询时可以得到极优的速度。但如果没有日期查询，那么会进行全表扫描。
分区对于海量数据性能提高是一个关键的工具。什么才是海量的数据取决于部署的硬件。盲目使用分区不能保证提高性能，但是在前期基准测试和性能测试的帮助下，可以成为完美的解决方案。
3. Archive表可以成为一个很好的折衷方案
Archive表分区后可以得到巨大的性能提高。当然也依赖于你的用途，没有分区时任何查询都是全表扫描。如果你有不需要变更的历史数据，还要进行按时间的分析统计，使用Archive引擘是极佳的选择。它会使用10-20％的原空间，对于聚集查询有比MyISAM/InnoDB表更好的性能。
虽然一个很好的优化的分区MyISAM表性能可能好于对应的Archive表,但是需要10倍的空间。
实验二：
1.建两个表，一个按时间字段分区，一个不分区。
CREATETABLEpart_tab
(
c1intdefaultNULL,
c2varchar(30)defaultNULL,
c3datedefaultNULL
)engine=myisam
PARTITIONBYRANGE(year(c3))(PARTITIONp0VALUESLESSTHAN(1995),
PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHAN(1997),
PARTITIONp3VALUESLESSTHAN(1998),PARTITIONp4VALUESLESSTHAN(1999),
PARTITIONp5VALUESLESSTHAN(2000),PARTITIONp6VALUESLESSTHAN(2001),
PARTITIONp7VALUESLESSTHAN(2002),PARTITIONp8VALUESLESSTHAN(2003),
PARTITIONp9VALUESLESSTHAN(2004),PARTITIONp10VALUESLESSTHAN(2010),
PARTITIONp11VALUESLESSTHANMAXVALUE);
createtableno_part_tab
(c1int(11)defaultNULL,
c2varchar(30)defaultNULL,
c3datedefaultNULL)engine=myisam;
2.建一个存储过程,利用该过程向两个表插入各8百万条不同数据。
delimiter//
CREATEPROCEDUREload_part_tab()
begin
declarevintdefault0;
whilev<8000000
do
insertintopart_tab
values(v,"testingpartitions",adddate(‘1995-01-01′,(rand(v)*36520)mod3652));
setv=v+1;
endwhile;
end
//
然后执行
mysql>delimiter;
mysql>callload_part_tab();
QueryOK,1rowaffected(8min17.75sec)
mysql>insertintono_part_tabselect*frompart_tab;
QueryOK,8000000rowsaffected(51.59sec)
Records:8000000 Duplicates:0 Warnings:0
3.开始对这两表中的数据进行简单的范围查询吧。并显示执行过程解析：
mysql>selectcount(*)fromno_part_tabwherec3>date‘1995-01-01′andc3<date‘1995-12-31′;
+———-+
|count(*)|
+———-+
| 795181|
+———-+
1rowinset(38.30sec)
mysql>selectcount(*)frompart_tabwherec3>date‘1995-01-01′andc3<date‘1995-12-31′;
+———-+
|count(*)|
+———-+
| 795181|
+———-+
1rowinset(3.88sec)
mysql>explainselectcount(*)fromno_part_tabwherec3>date‘1995-01-01′andc3<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:Usingwhere
1rowinset(0.00sec)
mysql>explainpartitionsselectcount(*)frompart_tabwhere
->c3>date‘1995-01-01′andc3<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:Usingwhere
1rowinset(0.00sec)
从上面结果可以看出，使用表分区比非分区的减少90％的响应时间。命令解析Explain程序可以看出在对已分区的表的查询过程中仅对第一个分区进行了扫描，其余跳过。进一步测试：
?增加日期范围
mysql>selectcount(*)fromno_part_tabwherec3>date‘-01-01′andc3<date‘1997-12-31′;
+———-+
|count(*)|
+———-+
|2396524|
+———-+
1rowinset(5.42sec)
mysql>selectcount(*)frompart_tabwherec3>date‘-01-01′andc3<date‘1997-12-31′;
+———-+
|count(*)|
+———-+
|2396524|
+———-+
1rowinset(2.63sec)
?增加未索引字段查询
mysql>selectcount(*)frompart_tabwherec3>date‘-01-01′andc3<date
‘1996-12-31′andc2="hello";
+———-+
|count(*)|
+———-+
|0|
+———-+
1rowinset(0.75sec)
mysql>selectcount(*)fromno_part_tabwherec3>date‘-01-01′andc3<da
te‘1996-12-31′andc2="hello";
+———-+
|count(*)|
+———-+
|0|
+———-+
1rowinset(11.52sec)
结论：
相关文章
- 改善MySQL存储引擎：提高数据库性能（修改mysql存储引擎）
- MySQL在PHP中的配置须知（php中的mysql配置）
- MySQL: 不止1的世界（mysql不等于1）
- MySQL性能提升的窍门：条数的调优（mysql性能条数）
- MySQL的默认函数：强力升级你的数据库性能（mysql默认函数）
- MySQL性能测试：试验数据报告（mysql测试数据）
- 加MySQL 连接数提升：提高性能及改善稳定性（mysql连接数增）
- 优化MySQL性能：研究更好的线程管理（mysql线程优化）
- Mysql状态监控：保证性能及稳定性（mysql状态监控）
- 更新MySQL：使用Yum升级MySQL（yum升级mysql）
- MySQL开启Binlog：步骤与性能优化（mysql开启binlog）
- MSSQL数据导入MySQL详细步骤（mssql导入mysql）
- Mac电脑：快速卸载MySQL的详细方法（mac如何卸载mysql）
- MySQL 集群：提升性能，改善可用性（mysql集群优点）
- 【MySQL索引：提高查询效率的秘诀】（mysql索引关联）
- MySQL学生成绩表：实时反映学习情况（mysql学生成绩表）
- MySQL索引碎片优化：提升数据库性能（mysql索引碎片）
- MySQL 分组查询：找出排名前几的数据（mysql 分组 top）
- 查询MySQL在百万次慢查询中的表现（mysql 百万 慢）
- 阿里巴巴MySQL数据库：稳健、高效、高可用性完美融合（阿里mysql 数据库）
- 构建App端Mysql数据库，打造智能体验（app mysql数据库）
- CMD下控制MySQL入门指南（cmd下 控制mysql）
- MySQL的及格率如何评估数据库性能（mysql中及格率）
- MySQL存储引擎区别简介（mysql不同引擎区别）
- MySQL索引查看技巧大全，详细介绍MySQL如何查看和优化索引（mysql 下面查看索引）