mysql分区性能的详细介绍

第一，分区概念

分区允许一个表的多个部分被分配在一个基于规则的文件系统。表的不同部分被存储为单独的表在不同的locations.mysql开始从5.1.3分区支持。

分区和手动子表的比较
手动分表
分区

多表数据表
一个数据表

重复数据的风险
不存在重复数据的风险

写多个表
写一个表

没有统一的约束
强制性的约束
MySQL支持范围、列表、散列、键分区类型，而范围是最常用的：

范围（范围）-这种模式允许数据被划分在不同的范围内。
Hash（Hashi）-这个模式允许通过一个或多个列的表的散列密钥计算，最后分区散列码的不同的值对应的数据区。例如，你可以创建一个表，分区表的主键。
键（键值）——上散列模式的扩展，其中哈希键由MySQL系统生成。
列表（预定义列表）——这种模式允许系统通过预定义列表的值来分割数据。
复合（复合模式）-上述模式的组合
二，分区可以做什么

逻辑数据分割
提高单写读应用速度
提高分区范围内的读取和查询速度
分区数据可以有多个不同的物理文件路径。
历史数据的有效保存
表上的约束检查
不同的主从服务器分区策略，如由哈希分区、按范围分区划分
三、分区限制（截止5.1.44版）

只能对数据表的整数列进行分区，或者通过分区函数将数据列转换成整数列。

分区的最大数量不能超过1024。

如果有唯一索引或主键，则分区列必须包含在所有唯一索引或主键中。

不支持外键

没有全文索引（全文）

按日期划分是非常合适的，因为可以使用大量的日期函数，但字符串的适当分区函数并不太多。

四，何时使用分区

海量数据表

历史表快速查询，可以用文件+分区的方式使用。

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

对于大型表，尤其是当索引远远大于服务器的有效内存时，索引不能被使用，此时分区效率将更高。

五、分区试验

实验1：

美国交通统计局公布的数据（CSV格式）。目前，它包含1亿1300万条记录和7.5 GB数据5.2 GB索引，时间从1987到2007。

服务器使用4GB内存，数据和索引大于内存的大小为4GB的原因是数据仓库的规模远远超过可能的内存的大小，和可能是几个TB。对于一个普通的OLTP数据库，索引缓存在内存中，可以快速检索。如果数据超过内存大小，它需要用一种不同的方式。

创建带有主键的表，因为通常表具有主键。表的主关键字太大，导致索引无法读入内存，通常效率不高，这意味着经常访问磁盘。访问速度完全取决于磁盘和处理器。目前，在不使用索引的情况下，设计大型数据仓库通常是这样的，因此它也具有和没有主键的性能。

试验方法：

三种数据引擎MyISAM，InnoDB使用，档案。
对于每个引擎，创建一个分区表，其中主键不在（除存档）和两个分区表之间，每个月一年。分区表分区方法如下所示：

创建表（by_year

D日

）

按范围划分（年份（d））

（

分区P1值小于（2001），

分区P2值小于（2002），

分区P3值小于（2003），

分区P4值小于（最大值）

）

创建表（by_month

D日

）

按范围分区（to_days（D））

（

PARTITION P1 VALUES LESS THAN (to_days ('2001-02-01')), - January

分区P2的值小于（to_days（'2001-03-01 '）），二月

p3分区值小于（to_days（'2001-04-01 '）），3月

分区P4值小于（最大值）

）

每一个都在MySQL服务器上的单独实例上进行测试，每个实例只有一个表。每种引擎都将启动服务，运行查询并记录结果，然后关闭服务。

加载数据的情况如下：
身份证件
发动机
是否分区
数据
大小
评论
加载时间（*）

一
MyISAM
没有
1亿1300万
13 GB
PK
37分钟

二
MyISAM
通过一个月
1亿1300万
8 GB
不PK
19分钟

三
MyISAM
通过一年的
1亿1300万
8 GB
不PK
18分钟

四
InnoDB
没有
1亿1300万
16 GB
PK
63分钟

五
InnoDB
通过一个月
1亿1300万
10 GB
不PK
59分钟

六
InnoDB
通过一年的
1亿1300万
10 GB
不PK
57分钟

七
档案文件
没有
1亿1300万
1.8 GB
没有钥匙
20分钟

八
档案文件
通过一个月
1亿1300万
1.8 GB
没有钥匙
21分钟

九
档案文件
通过一年的
1亿1300万
1.8 GB
没有钥匙
20分钟

*在双至强服务器

为了比较分区对大数据集和小数据集的影响，创建了其他9个实例，每个实例的容量都小于2GB。

有两种查询语句

聚集查询

选择计数（*）

从table_name

在date_column start_date和end_date之间

指定记录查询

选择column_list

从table_name

其中column1 = X和Y和Z的位置方法= =

对于第一个查询，创建一个不同的日期范围语句。对于每个范围，创建一组具有相同范围日期的附加查询。每个日期范围内的第一个查询是冷查询，这意味着第一次命中。在同一范围内的后续查询是温暖查询，这意味着至少其中一部分被缓存。

结果uff1a

1带主键的分区表

第一次测试使用的复合主键，只是因为它是在原来的数据表。当主键索引文件达到5.5 GB，可以看出分区不仅不会提高性能，但是主键也减慢操作。因为如果使用主密钥索引查询索引不能被读入内存，性能很差，这表明我们的分区是非常有用的，但必须正确使用。

+ u2013 +——+——+ u2013——+

|状态| MyISAM | MyISAM | MyISAM分区分区|年

+ u2013 +——+——+ u2013——+

|冷| 2.6574570285714 | 2.9169642 | 3.0373419714286 |

|温暖| 2.5720722571429 | 3.1249698285714 | 3.1294000571429 |

+ u2013 +——+——+ u2013——+

档案机

+ + + +—————

|状态|档案档案档案| |分区分区|年

+ + + +—————

|冷| 249.849563 | 1.2436211111111 | 12.632532527778 |

|温暖| 235.814442 | 1.0889786388889 | 12.600520777778 |

+ + + +—————

请注意，档案机响应时间可能分区而不是使用MyISAM。

2没有主键的分区表

因为如果主键大于可用键缓冲区，甚至是所有内存，则使用主键的所有查询都将使用磁盘。新方法只使用分区，而不使用主键。

70% - 90%的性能通过每月分区表进行了改进。

+ u2013 + + + - + - +

|状态| MyISAM | MyISAM | MyISAM分区分区|年

+ u2013 + + + - + - +

|冷| 2.6864490285714 | 0.64206445714286 | 2.6343286285714 |

|温暖| 2.8157905714286 | 0.18774977142857 | 2.2084743714286 |

+ u2013 + + + - + - +

为了使差异更加明显，我使用了两个大型查询，这些查询可以用来删除分区函数。

#查询- 1年度统计

选择年（flightdate）Y、计数（*）

从FlightStats

在flightdate之间2001-01-01 和2003-12-31

集团通过Y

#查询2统计月报

选择date_format（flightdate，% % m m y），计数（*）

从FlightStats

在flightdate之间2001-01-01 和2003-12-31

组的M

结果表明，分区表具有30%—60%，年分区表具有15%～30%的性能改善。

+ + + + - +

| query_id |点|年|是|月

+ + + + - +

| 1 97.779958 36.296519 82.327554 | | | |

| 2 69.61055 47.644986 47.60223 | | | |

+ + + + - +

处理器的因素

当上述试验在国内试验机（英特尔双核心CPU 2.3 MHz），原来的双至强2.66兆赫，找到新的服务器的速度！

重复上述测试真是太好了。

+ + +……

|状态| MyISAM | MyISAM四月| MyISAM分区分区|

+ + +……

|冷| 0.051063428571429 | 0.6577062 | 1.6663527428571 |

|温暖| 0.063645485714286 | 0.1093724 | 1.2369152285714 |

+ + +……

MyISAM不分区主键表的分区表的速度比。分区表的性能与以前相同，但是分区表的性能得到了改善，这使得分区不必了。因为这个服务器似乎充分利用了索引的好处，所以我在分区表列中添加了一个索引。

#原始表

FlightStats（创建表

airlineid INT NOT NULL，

UniqueCarrier char（3）不为空，

载波字符（3）不为空，

flightdate日期不为空，

FlightNum char（5）不为空，

TailNum char（8）不为空，

ArrDelay双不空，

arrtime日期不为空，

DepDelay双不空，

deptime日期不为空，

原点char（3）不是null，

目的（3）不为空字符，

距离int不是空的，

取消char（1）默认n，

主键（flightdate，airlineid、载体、UniqueCarrier、flightnum，起源，deptime，dest）

）

#分区表

FlightStats（创建表

airlineid INT NOT NULL，

UniqueCarrier char（3）不为空，

载波字符（3）不为空，

flightdate日期不为空，

FlightNum char（5）不为空，

TailNum char（8）不为空，

ArrDelay双不空，

arrtime日期不为空，

DepDelay双不空，

deptime日期不为空，

原点char（3）不是null，

目的（3）不为空字符，

距离int不是空的，

取消char（1）默认n，

关键（flightdate）

）

按范围划分…

结果令人满意，35%种性能得到改善。

+ + + +

|状态| MyISAM | MyISAM四月| MyISAM分区分区|

+ + + +

|冷| 0.075289714285714 | 0.025491685714286 | 0.072398542857143 |

|温暖| 0.064401257142857 | 0.031563085714286 | 0.056638085714286 |

+ + + +

结论：

1。使用表分区并不能保证性能的提高，这取决于以下因素：

分区使用的列用于分区；
如果原始字段不是int类型，则分配函数；
服务器的速度；
内存量。

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

根据您的数据库的使用，您可能会获得巨大的性能改进，但您可能一无所获，如果不小心，甚至可能降低性能。

例如，当您总是查询日期范围时，使用分区的表可以得到很好的速度。但是如果没有日期可用，则将进行一次完整的表扫描。

分区是提高海量数据性能的一个关键工具，大量的数据依赖于部署的硬件，盲目使用分区不能保证性能的提高，但在以往的基准测试和性能测试的帮助下，这是一个很好的解决方案。

3。归档表可以是一个很好的折衷方案。

存档表分区可以获得巨大的性能改进。当然，这还取决于您的使用。没有查询是全表扫描时没有分区的分区，如果你已不需要变化的历史数据，还可以根据时间的统计分析，利用档案的引擎是一个很好的选择，它将使用10-20%的原始的空间，它表现为聚集查询比MyISAM / InnoDB表更好。

虽然一个好的优化划分MyISAM表可以有更好的表现比相应的档案表，它需要10倍的空间。

实验二：

1、建立两个表，一个由时间域划分，另一个不分区。

创建表part_tab

（

默认值为null，

C2 varchar（30）默认为空，

日期默认值为null

MyISAM引擎=）

按范围分区（年（C3））（分区P0值小于（1995）），

分区P1值小于（1996），分区P2值小于（1997），

分区P3值小于（1998），分区P4值小于（1999），

分区P5值小于（2000），分区P6值小于（2001），

分区P7值小于（2002），分区P8值小于（2003），

分区P9值小于（2004），分区P10值小于（2010），

分区P11值小于最大值）；

创建表no_part_tab

（int int（11）默认NULL，

C2 varchar（30）默认为空，

C3日期默认为空）= MyISAM引擎；

2。构建一个存储过程，使用此过程将800万个不同的数据插入两个表中。

分隔符

创建程序load_part_tab（）

开始

声明int默认值0；

而V＜8000000

做

Insert into part_tab

值（V，'testing分区，adddate（' 1995-01-01 '，（（RAND（V）* 36520）mod 3652）））；

设置V＝v + 1；

结束的时候；

终点
然后执行

分隔符；

MySQL >电话load_part_tab（）；

查询OK，1行受影响（8分钟17.75秒）

MySQL >插入no_part_tab SELECT * FROM part_tab；

查询OK，8000000行受影响（51.59秒）

记录：8000000个重复：0个警告：0

三.对两个表中的数据进行简单的范围查询，并显示执行过程解析：

MySQL > select count（*）从no_part_tab在C3 >日期1995-01-01和C3 <日期；

+——+

|计数（*）|

+——+

795181 | |

+——+

1行集（38.30秒）

MySQL > select count（*）从part_tab在C3 >日期1995-01-01和C3 <日期；

+——+

|计数（*）|

+——+

795181 | |

+——+

1行集（3.88秒）

MySQL >解释select count（*）从no_part_tab在C3 >日期1995-01-01和C3 <<排除>

*************************** 1。行***************************

编号：1

select_type：简单

表：no_part_tab

类型：所有

possible_keys：空

关键词：零

key_len：空

参考：空

行数：8000000

附加：使用在哪里

1行集（0秒）

MySQL >解释分区select count（*）从part_tab哪里

> >日期1995-01-01 C3和C3的日期1995-12-31 G.

*************************** 1。行***************************

编号：1

select_type：简单

表：part_tab

分区：P1

类型：所有

possible_keys：空

关键词：零

key_len：空

参考：空

行数：798458

附加：使用在哪里

1行集（0秒）

从上面的结果可以看出，表分区的使用减少了90%的响应时间，而非分区。命令解析解释程序可以看到只有第一个分区在分区表的查询过程中被扫描，其余的将跳过：

-增加日期范围

MySQL > select count（*）从no_part_tab在C3 >日期01-01和C3 <日期；

+——+

|计数（*）|

+——+

2396524 | |

+——+

1行集（5.42秒）

MySQL > select count（*）从part_tab在C3 >日期01-01和C3 <日期；

+——+

|计数（*）|

+——+

2396524 | |

+——+

1行集（2.63秒）

-增加对非索引字段的查询

MySQL > select count（*）从part_tab在C3 >日期01-01和C3 <日期

1996-12-31和C2 =你好；

+——+

|计数（*）|

+——+

0 | |

+——+

1行集（0.75秒）

MySQL > select count（*）从no_part_tab在C3 >日期01-01和C3 <大

TE的1996-12-31和C2 =你好；

+——+

|计数（*）|

+——+

0 | |

+——+

1行集（11.52秒）
结论：

分区和非分区占用文件空间大致相同（数据和索引文件）
如果在查询语句中没有建立索引字段，则分区时间远远优于非分区时间。
如果索引中的字段被索引，那么分区和非分区之间的差异就缩小了，分区比非分区稍微好一些。
对于大量的数据，建议使用分区函数。
删除不必要的字段
根据手册，添加myisam_max_sort_file_size会增加分区的性能