本文地址：https://www.askmac.cn/archives/mysql-partition.html

 

第9章 分区(Partition)

 

章节概述

本章介绍在MySQL中分区的管理。你会了解：

• 理解分区概念
• 使用SHOW VARIABLES来确定服务端的分区支持
• 如何建立一张分区表
• 描述分区类型

 

9.1 分区概述

SQL标准中并不提供很多关于数据物理存储方面的指导。而SQL语句本身趋向于独立于数据结构或这些模式（schema/database），表，行或列下对应的介质进行运行。但是，大多数高级的数据库管理系统都会有一些方法来判断具体被用于存储的文件系统或硬件下的数据片的物理位置。在MySQL中，InnoDB存储引擎还支持表空间概念。在MySQL服务端，介绍分区之前，你可以配置不同的空物理目录来存储不同的数据库。

Tips：分区是从MySQL 5.1.14-Beta版本开始被引入的功能。

 

分区在此基础上更近一步，允许你在将单个表的各个部分分布在整个文件系统中（只要所设分区文件的大小遵守系统的规则）。实时上，一张表的不同部分可以如各个分割的表存储在不同位置。数据通过用户选择的规则进行的分割（我们称为分区功能），如按量值进行分区，或简单匹配一个值列范围进行分区，或使用内部哈希函数或一个线性函数进行分区等。如何分区由用户按分区类别来确定，其所用的功能匹配可以接受用户提供的表达式值作为参数，表达式可以是一个整型列值，或在对一个或多个列进行处理后来得出的一个整数来作为返回。表达式的值被传给分区功能函数，此函数会返回一个整数值代表了对应数据行应该被存放在哪个分区的分区号。此功能函数必须是非静态值和非随机值。它不能包含任何查询，但可以“虚拟的“使用在MySQL中有效的任意表达式（只要表达式返回的正整数小于最大可能的正整数值MAXVALUE即可）。

 

我们在这里所介绍的分区在概念上指水平分区（horizontal partitioning）– 即，表中的不同行可以在不同的物理分区中。MySQL不支持垂直分区（vertical partitioning），即表中的不同列被分派到不同的物理分区中。到现在为止，MySQL还未有任何计划来引入垂直分区功能。

 

9.1.1 查看分区功能启用状态

在MySQL 5.6版本之前，你可以通过使用以下语句来查看MySQL的分区功能是否已经启用：

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

不过从MySQL 5.6开始，have_partitioning环境变量已经被移除，因此你需要使用show plugins来查看partition的启用情况。

如果对应状态显示未被启用的话，则说明当前的MySQL服务端不支持分区功能。

对于建立分区表，你可以使用大多数MySQL服务端支持的存储引擎；MySQL分区引擎运行在一个分开的层上并与其它存储引擎进行交互。在MySQL中，同一个分区表下的所有分区必须使用相同的存储引擎；例如，你不能对同一个表中的某个分区使用MyISAM存储引擎，而另一个分区使用InnoDB。然而同一个MySQL数据库下的不同分区表还是可以使用不同的存储引擎的。

 

注意：MySQL分区不能被用于FEDERATED，MRG_MYISAM或CSV存储引擎。在MySQL 5.1.6之前，使用BLACKHOLE存储引擎建立分区表也是不行的。NDB Cluster存储引擎仅支持通过使用KEY（包括LINEAR KEY）进行的分区，其它用户定义的分区方式都不被支持。

 

9.1.2 建立一张分区表

为了对一个要被分区的表使用一个指定的存储引擎，对于非分区表来说，你可以仅使用[STORAGE] ENGINE表项来进行设置改变。你应该记住在使用CREATE TABLE语句建立的分区表语法中， [STORAGE] ENGINE表项(和其他表项)需要在分区项之前被列出。下面的例子显示了如何建立一张以Hash来进行分区的InnoDB引擎表：

注意，每个PARTITION语法中可以包括一个[STORAGE] ENGINE表项，但在MySQL中这种写法其实是没有效果的。

分区应用于表的所有数据和索引，但是你不能仅对数据分区而不对索引分区，反之亦然，你也不能仅对表中的一部分进行分区。

 

每个分区中的数据和索引可以在CREATE TABLE … PARTITION语法中使用DATA DIRECTORY和INDEX DIRECTORY项进行特定目录位置的设置。此外，MAX_ROWS和MIN_ROWS可以被用户决定存储于每个分区中的最大行数和预估的最小行数。

 

分区包括的有一些优势：

• 与存储在单个磁盘或文件系统分区相比，可以在一张表中存储更多数据。
• 那些表中不再有用的数据库，可以直接删除那个包含无用数据的分区。相反，可以快速的新增一个数据分区来存放相应新的数据。
• 满足WHERE语法的数据如果被存放在某个或多个分区中的话，一些情况下可以大大优化查询性能，因为其通过查询排除了其它一些分区。由于表分区在建立后还可以被修改，你可以对数据进行重新组织来优化一些频繁查询的性能（这些性能的提升可能在分区首次建立时并没有达到理想效果）。这种修改，我们一般称为“分区修剪（Partition Pruning）”。

 

使用分区还将包括以下这些好处，不过这些特性目前还未在MySQL分区中被实现，但在开发计划中处于高优先级，因此也请耐心等待：

• 涉及聚合函数如SUM()和COUNT()的查询可以被并行处理。举个简单的例子如查询:

SELECT salesperson_id, COUNT(orders) as order_total
  FROM sales GROUP BY salesperson_id;

“并行“，即意味着，查询可以在每个分区上同步进行，然后会对所有分区的查询结果进行汇总处理。

• 将数据查询分布在多个磁盘上，可以获得更高的查询性能。