MySQL从5.1版本开始支持分区操作。对MySQL使用分区操作不仅能够存储更多的数据而且在数据查询效率和数据吞吐量方面也能够得到显著的提升。本章将简单介绍如何在MySQL中分区存储。目录分区介绍不同版本MySQL的分区MySQL5.6以下MySQL5.7MySQL8分区的优势存储更多的数据优化查询并行处理快速删除更大的数据吞吐量分区类型RANGE分区LIST分区HASH分区KEY分区COLUMNS分区子分区总结分区介绍分区是指将一张表中的数据和索引分散存储到同一台计算机或不同计算机磁盘上的多个文件中。分区操作对于上层访问是透明的用户访问MySQL中的分区表时不必关心当前访问的数据存储到数据表的哪个分区中。对MySQL中的数据表进行分区也不会影响上层的业务逻辑。不同版本MySQL的分区MySQL5.6以下在MySQL 5.6以下的版本中可以使用SHOW VARIABLES语句来查看当前MySQL是否支持分区操作。mysql SHOW VARIABLES LIKE %partition%; --------------------------------------------- | Variable_name | Value | --------------------------------------------- | have_partition_engine | YES | --------------------------------------------- 1 row in set (0.00 sec)输出结果显示当前MySQL支持分区操作如果输出的结果数据为空或者have_partition_engine的值不为YES则表示当前MySQL不支持分区操作。在MySQL 5.1版本中同一张数据表的所有分区必须使用同一个存储引擎即一张数据表中不能对一个分区使用一种存储引擎而对另一个分区使用其他存储引擎。但是可以在不同的数据库中对不同的数据表使用不同的存储引擎。MySQL5.7在MySQL 5.6及5.6以上的版本中需要使用SHOW PLUGINS语句查看是否支持分区操作。比如查看MySQL 5.7版本是否支持分区操作mysql SHOW PLUGINS; ---------------------------------------------------------------------------- | Name | Status | Type | Library | License | ---------------------------------------------------------------------------- | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL ############################# 此处省略N行 ################################# | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | ---------------------------------------------------------------------------- 44 rows in set (0.04 sec)从输出结果中可以看到下面一行信息| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |说明当前MySQL支持分区操作。在MySQL 5.7及以下的版本中支持使用大部分存储引擎创建分区表例如可以使用MyISAM、InnoDB和Memory等存储引擎创建分区表其他诸如MERGE、CSV等存储引擎不支持创建分区表。例如在MySQL 5.7中基于InnoDB存储引擎创建分区表。mysql CREATE TABLE tbl_partition_innodb( - id INT NOT NULL, - name VARCHAR(30) - )ENGINEInnoDB - PARTITION BY HASH(id) - PARTITIONS 5; Query OK, 0 rows affected (1.27 sec)创建分区表成功接下来基于MERGE存储引擎创建分区表。mysql CREATE TABLE tbl_partition1( - id INT NOT NULL, - name VARCHAR(30) - )ENGINEMERGE - PARTITION BY HASH(id) - PARTITIONS 5; ERROR 1572 (HY000): Engine cannot be used in partitioned tables创建分区表失败MySQL报错信息为“存储引擎不能用于分区表”。MySQL8在MySQL 8.x版本中MyISAM存储引擎已经不允许再创建分区表了只能为实现了本地分区策略的存储引擎创建分区表截至MySQL 8.0.18版本只有InnoDB和NDB存储引擎支持创建分区表。比如在MySQL 8.0.18版本中为MyISAM存储引擎创建分区表。mysql CREATE TABLE tbl_partition_myisam( - id INT NOT NULL - ) ENGINEMyISAM - PARTITION BY HASH(id) - PARTITIONS 5; ERROR 1178 (42000): The storage engine for the table doesnt support native partitioningMySQL报错报错信息为“存储引擎不支持本地分区策略”。分区的优势对数据表进行分区的优点。存储更多的数据MySQL中的数据表能够存储更多的数据。当没有使用分区时同一个MySQL实例中的同一个数据表中的数据只能存储到同一台计算机的同一磁盘的同一个数据文件中。使用分区后同一个MySQL实例中的同一张数据表中的数据能够存储到同一台计算机或不同计算机的不同磁盘上的不同的数据文件中相比没有分区时能够分散存储更多的数据。优化查询分区后在WHERE条件语句中包含分区条件时能够只扫描符合条件的一个或多个分区来查询数据而不必扫描整个数据表中的数据从而提高了数据查询的效率。并行处理当查询语句中涉及SUM()、COUNT()、AVG()、MAX()和MIN()等聚合函数时可以在每个分区上进行并行处理再统计汇总每个分区得出的结果从而得出最终的汇总结果数据整体上提高了数据查询与统计的效率。快速删除数据如果数据表中的数据已经过期或者不需要再存储到数据表中可以通过删除分区的方式快速删除数据表中的数据。删除分区比删除数据表中的数据在效率上要高得多。更大的数据吞吐量分区后能够跨多个磁盘分散数据查询每个查询之间可以并行进行能够获得更大的查询吞吐量提升数据查询的性能。分区类型MySQL的分区在总体上可以分为RANGE分区、LIST分区、HASH分区和KEY分区在此基础上又派生出了COLUMNS分区和子分区。RANGE分区根据一个连续的区间范围将数据分散存储于不同的分区支持对字段名或表达式进行分区。LIST分区根据给定的值列表将数据分散存储到不同的分区支持对字段名或表达式进行分区。HASH分区根据给定的分区个数结合一定的HASH算法将数据分散存储到不同的分区可以使用用户自定义的函数。KEY分区与HASH分区类似但是只能使用MySQL自带的HASH函数。COLUMNS分区为解决MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区而在MySQL 5.5版本新引入的分区类型。子分区对数据表中的每个分区再次进行分区。注意RANGE分区与LIST分区有一定的相似性RANGE分区是基于一个连续的区间范围分区而LIST分区是基于一个给定的值列表进行分区HASH分区与KEY分区类似HASH分区既可以使用MySQL本身提供的HASH函数进行分区也可以使用用户自定义的表达式分区而KEY分区只能使用MySQL本身提供的函数进行分区。在MySQL所有的分区类型中进行分区的数据表可以不存在主键或者唯一键如果存在主键或者唯一键则不能使用主键或唯一键之外的其他字段进行分区操作。例如数据表tbl_partition_test和id为主键使用year字段进行RANGE分区。mysql CREATE TABLE tbl_partition_test( - id INT NOT NULL PRIMARY KEY, - year INT - )ENGINEInnoDB - PARTITION BY RANGE(year)( - PARTITION part0 VALUES LESS THAN (2010), - PARTITION part1 VALUES LESS THAN (2020), - PARTITION part3 VALUES LESS THAN (2030) - ); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function以主键之外的其他字段进行分区MySQL会报错此时去除主键约束。mysql CREATE TABLE tbl_partition_test( - id INT NOT NULL, - year INT - )ENGINEInnoDB - PARTITION BY RANGE(year)( - PARTITION part0 VALUES LESS THAN (2010), - PARTITION part1 VALUES LESS THAN (2020), - PARTITION part3 VALUES LESS THAN (2030) - ); Query OK, 0 rows affected (0.13 sec)结果显示分区数据表创建成功。总结本文系统介绍了MySQL数据库中的分区操作。文章首先讲解了分区的基本概念以及不同版本MySQL5.6以下、5.7、8.x对分区的支持情况和存储引擎限制随后阐述了分区的五大优势包括存储更多数据、优化查询、并行处理、快速删除和更大的数据吞吐量最后介绍了RANGE、LIST、HASH、KEY、COLUMNS等主要分区类型及子分区并说明了分区表与主键/唯一键之间的约束关系。 
长河君 · 2026/9/8 17:00:49 · 阅读 1.2k MySQL分区介绍
MySQL从5.1版本开始支持分区操作。对MySQL使用分区操作不仅能够存储更多的数据而且在数据查询效率和数据吞吐量方面也能够得到显著的提升。本章将简单介绍如何在MySQL中分区存储。目录分区介绍不同版本MySQL的分区MySQL5.6以下MySQL5.7MySQL8分区的优势存储更多的数据优化查询并行处理快速删除更大的数据吞吐量分区类型RANGE分区LIST分区HASH分区KEY分区COLUMNS分区子分区总结分区介绍分区是指将一张表中的数据和索引分散存储到同一台计算机或不同计算机磁盘上的多个文件中。分区操作对于上层访问是透明的用户访问MySQL中的分区表时不必关心当前访问的数据存储到数据表的哪个分区中。对MySQL中的数据表进行分区也不会影响上层的业务逻辑。不同版本MySQL的分区MySQL5.6以下在MySQL 5.6以下的版本中可以使用SHOW VARIABLES语句来查看当前MySQL是否支持分区操作。mysql SHOW VARIABLES LIKE %partition%; --------------------------------------------- | Variable_name | Value | --------------------------------------------- | have_partition_engine | YES | --------------------------------------------- 1 row in set (0.00 sec)输出结果显示当前MySQL支持分区操作如果输出的结果数据为空或者have_partition_engine的值不为YES则表示当前MySQL不支持分区操作。在MySQL 5.1版本中同一张数据表的所有分区必须使用同一个存储引擎即一张数据表中不能对一个分区使用一种存储引擎而对另一个分区使用其他存储引擎。但是可以在不同的数据库中对不同的数据表使用不同的存储引擎。MySQL5.7在MySQL 5.6及5.6以上的版本中需要使用SHOW PLUGINS语句查看是否支持分区操作。比如查看MySQL 5.7版本是否支持分区操作mysql SHOW PLUGINS; ---------------------------------------------------------------------------- | Name | Status | Type | Library | License | ---------------------------------------------------------------------------- | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL ############################# 此处省略N行 ################################# | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | ---------------------------------------------------------------------------- 44 rows in set (0.04 sec)从输出结果中可以看到下面一行信息| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |说明当前MySQL支持分区操作。在MySQL 5.7及以下的版本中支持使用大部分存储引擎创建分区表例如可以使用MyISAM、InnoDB和Memory等存储引擎创建分区表其他诸如MERGE、CSV等存储引擎不支持创建分区表。例如在MySQL 5.7中基于InnoDB存储引擎创建分区表。mysql CREATE TABLE tbl_partition_innodb( - id INT NOT NULL, - name VARCHAR(30) - )ENGINEInnoDB - PARTITION BY HASH(id) - PARTITIONS 5; Query OK, 0 rows affected (1.27 sec)创建分区表成功接下来基于MERGE存储引擎创建分区表。mysql CREATE TABLE tbl_partition1( - id INT NOT NULL, - name VARCHAR(30) - )ENGINEMERGE - PARTITION BY HASH(id) - PARTITIONS 5; ERROR 1572 (HY000): Engine cannot be used in partitioned tables创建分区表失败MySQL报错信息为“存储引擎不能用于分区表”。MySQL8在MySQL 8.x版本中MyISAM存储引擎已经不允许再创建分区表了只能为实现了本地分区策略的存储引擎创建分区表截至MySQL 8.0.18版本只有InnoDB和NDB存储引擎支持创建分区表。比如在MySQL 8.0.18版本中为MyISAM存储引擎创建分区表。mysql CREATE TABLE tbl_partition_myisam( - id INT NOT NULL - ) ENGINEMyISAM - PARTITION BY HASH(id) - PARTITIONS 5; ERROR 1178 (42000): The storage engine for the table doesnt support native partitioningMySQL报错报错信息为“存储引擎不支持本地分区策略”。分区的优势对数据表进行分区的优点。存储更多的数据MySQL中的数据表能够存储更多的数据。当没有使用分区时同一个MySQL实例中的同一个数据表中的数据只能存储到同一台计算机的同一磁盘的同一个数据文件中。使用分区后同一个MySQL实例中的同一张数据表中的数据能够存储到同一台计算机或不同计算机的不同磁盘上的不同的数据文件中相比没有分区时能够分散存储更多的数据。优化查询分区后在WHERE条件语句中包含分区条件时能够只扫描符合条件的一个或多个分区来查询数据而不必扫描整个数据表中的数据从而提高了数据查询的效率。并行处理当查询语句中涉及SUM()、COUNT()、AVG()、MAX()和MIN()等聚合函数时可以在每个分区上进行并行处理再统计汇总每个分区得出的结果从而得出最终的汇总结果数据整体上提高了数据查询与统计的效率。快速删除数据如果数据表中的数据已经过期或者不需要再存储到数据表中可以通过删除分区的方式快速删除数据表中的数据。删除分区比删除数据表中的数据在效率上要高得多。更大的数据吞吐量分区后能够跨多个磁盘分散数据查询每个查询之间可以并行进行能够获得更大的查询吞吐量提升数据查询的性能。分区类型MySQL的分区在总体上可以分为RANGE分区、LIST分区、HASH分区和KEY分区在此基础上又派生出了COLUMNS分区和子分区。RANGE分区根据一个连续的区间范围将数据分散存储于不同的分区支持对字段名或表达式进行分区。LIST分区根据给定的值列表将数据分散存储到不同的分区支持对字段名或表达式进行分区。HASH分区根据给定的分区个数结合一定的HASH算法将数据分散存储到不同的分区可以使用用户自定义的函数。KEY分区与HASH分区类似但是只能使用MySQL自带的HASH函数。COLUMNS分区为解决MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区而在MySQL 5.5版本新引入的分区类型。子分区对数据表中的每个分区再次进行分区。注意RANGE分区与LIST分区有一定的相似性RANGE分区是基于一个连续的区间范围分区而LIST分区是基于一个给定的值列表进行分区HASH分区与KEY分区类似HASH分区既可以使用MySQL本身提供的HASH函数进行分区也可以使用用户自定义的表达式分区而KEY分区只能使用MySQL本身提供的函数进行分区。在MySQL所有的分区类型中进行分区的数据表可以不存在主键或者唯一键如果存在主键或者唯一键则不能使用主键或唯一键之外的其他字段进行分区操作。例如数据表tbl_partition_test和id为主键使用year字段进行RANGE分区。mysql CREATE TABLE tbl_partition_test( - id INT NOT NULL PRIMARY KEY, - year INT - )ENGINEInnoDB - PARTITION BY RANGE(year)( - PARTITION part0 VALUES LESS THAN (2010), - PARTITION part1 VALUES LESS THAN (2020), - PARTITION part3 VALUES LESS THAN (2030) - ); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function以主键之外的其他字段进行分区MySQL会报错此时去除主键约束。mysql CREATE TABLE tbl_partition_test( - id INT NOT NULL, - year INT - )ENGINEInnoDB - PARTITION BY RANGE(year)( - PARTITION part0 VALUES LESS THAN (2010), - PARTITION part1 VALUES LESS THAN (2020), - PARTITION part3 VALUES LESS THAN (2030) - ); Query OK, 0 rows affected (0.13 sec)结果显示分区数据表创建成功。总结本文系统介绍了MySQL数据库中的分区操作。文章首先讲解了分区的基本概念以及不同版本MySQL5.6以下、5.7、8.x对分区的支持情况和存储引擎限制随后阐述了分区的五大优势包括存储更多数据、优化查询、并行处理、快速删除和更大的数据吞吐量最后介绍了RANGE、LIST、HASH、KEY、COLUMNS等主要分区类型及子分区并说明了分区表与主键/唯一键之间的约束关系。