Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

博客文章2026】MySQL 9.x数据库管理技巧7：分区和分区性能

2026-3-22 17:29| 发布者: admin| 查看: 251| 评论: 0|原作者: Bo Tang

摘要: 分区是指将数据库或其组成元素划分为若干独立的、互不重叠的部分。MySQL 支持水平分区，即将表中的特定行分配到不同的行子集中，数据根据分区函数进行划分。此外，本博客还通过实验演示了如何利用分区来提升性能，以及相关的其他分区操作。

【博客文章2026】MySQL 9.x数据库管理技巧7：分区和分区性能

Author: Bo Tang

1. 创建RANGE分区：

登录Mysql客户端，查看innodb_file_per_table变量的值：

mysql> show variables like '%innodb%per\_%';

+------------------------------+-------+

| Variable_name | Value |

+------------------------------+-------+

| innodb_cmp_per_index_enabled | OFF |

| innodb_file_per_table | ON |

+------------------------------+-------+

2 rows in set (0.005 sec)

创建一个range分区表，共分4个分区。值分段点是“100”、“300”、“500”和剩余更大的值：

mysql> CREATE TABLE countrylanguage2 (

-> CountryCode char(3) NOT NULL DEFAULT '',

-> Language char(30) NOT NULL DEFAULT '',

-> IsOfficial enum('T','F') NOT NULL DEFAULT 'F',

-> Percentage int(4) NOT NULL DEFAULT '0.0'

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-> PARTITION BY RANGE (Percentage) (

-> PARTITION p0 VALUES LESS THAN (100),

-> PARTITION p1 VALUES LESS THAN (300),

-> PARTITION p2 VALUES LESS THAN (500),

-> PARTITION p3 VALUES LESS THAN MAXVALUE );

Query OK, 0 rows affected, 1 warning (0.025 sec)

使用“show table status”命令，查看分区表的信息：

mysql> show table status like 'countrylanguage2'\G

*************************** 1. row ***************************

Name: countrylanguage2

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 65536

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2026-03-23 05:14:37

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_0900_ai_ci

Checksum: NULL

Create_options: partitioned

Comment:

1 row in set (0.008 sec)

2. 向RANGE分区表填充数据：

使用“insert into ... select”命令，填充数据：

mysql> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.007 sec)

mysql> insert into countrylanguage2(CountryCode,Language,IsOfficial,Percentage)

-> select CountryCode,Language,IsOfficial,Percentage*10 from countrylanguage;

Query OK, 984 rows affected (0.046 sec)

Records: 984 Duplicates: 0 Warnings: 0

查看一下InnoDB中每个分区所对应的一个文件：

[root@station95 ~]# cd /var/lib/mysql/world_y

[root@station95 world_y]# ls -l

total 1696

-rw-r----- 1 mysql mysql 622592 Mar 22 14:56 city.ibd

-rw-r----- 1 mysql mysql 131072 Mar 12 22:31 country.ibd

-rw-r----- 1 mysql mysql 229376 Mar 12 22:31 countryinfo.ibd

-rw-r----- 1 mysql mysql 163840 Mar 23 05:23 countrylanguage2#p#p0.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 05:23 countrylanguage2#p#p1.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 05:23 countrylanguage2#p#p2.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 05:23 countrylanguage2#p#p3.ibd

-rw-r----- 1 mysql mysql 245760 Mar 12 22:31 countrylanguage.ibd

因为每个分区的数据量不尽相同，Range分区的每个文件很难做到大小一致。下面查看数据字典中的分区信息：

mysql> SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION

-> FROM INFORMATION_SCHEMA.PARTITIONS

-> WHERE TABLE_NAME = 'countrylanguage2';

+----------------+------------------+-----------------------+

| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |

+----------------+------------------+-----------------------+

| p3 | RANGE | MAXVALUE |

| p2 | RANGE | 500 |

| p1 | RANGE | 300 |

| p0 | RANGE | 100 |

+----------------+------------------+-----------------------+

4 rows in set (0.004 sec)

3. 查看RANGE分区表在不同查询场景中的性能（分区裁剪）：

看看SQL执行计划中分区裁剪操作是否会发生：

mysql> EXPLAIN FORMAT=TRADITIONAL SELECT Percentage FROM countrylanguage2 WHERE Percentage =99\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: countrylanguage2

partitions: p0

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 633

filtered: 10.00

Extra: Using where

1 row in set, 1 warning (0.001 sec)

可以看到，单值查询场景中，Range分区表的分区裁剪的确发生了：查询语句只访问了p0这个分区，而不是整个表。下面再执行范围查询：

mysql> EXPLAIN FORMAT=TRADITIONAL SELECT Percentage FROM countrylanguage2 WHERE Percentage <99\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: countrylanguage2

partitions: p0

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 633

filtered: 33.33

Extra: Using where

1 row in set, 1 warning (0.001 sec)

可以看到，范围查询场景中，Range分区的分区裁剪也发生了。

4. 将RANGE分区表转变为KEY分区表：

mysql> alter table countrylanguage2 partition by key (Percentage) partitions 4;

Query OK, 984 rows affected (0.119 sec)

Records: 984 Duplicates: 0 Warnings: 0

下面查看数据字典中的分区信息：

mysql> SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION

-> FROM INFORMATION_SCHEMA.PARTITIONS

-> WHERE TABLE_NAME = 'countrylanguage2';

+----------------+------------------+-----------------------+

| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |

+----------------+------------------+-----------------------+

| p3 | KEY | NULL |

| p2 | KEY | NULL |

| p1 | KEY | NULL |

| p0 | KEY | NULL |

+----------------+------------------+-----------------------+

4 rows in set (0.004 sec)

发现分区类型已经从“Range”改变为“Key”。查看一下InnoDB中每个分区所对应的一个文件：

[root@station95 ~]# cd /var/lib/mysql/world_y

[root@station95 world_y]# ls -l

total 1680

-rw-r----- 1 mysql mysql 622592 Mar 22 14:56 city.ibd

-rw-r----- 1 mysql mysql 131072 Mar 12 22:31 country.ibd

-rw-r----- 1 mysql mysql 229376 Mar 12 22:31 countryinfo.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 23:36 countrylanguage2#p#p0.ibd

-rw-r----- 1 mysql mysql 147456 Mar 23 23:36 countrylanguage2#p#p1.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 23:36 countrylanguage2#p#p2.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 23:36 countrylanguage2#p#p3.ibd

-rw-r----- 1 mysql mysql 245760 Mar 12 22:31 countrylanguage.ibd

因为Key分区表的每个分区的数据量都是基本相同的（对应于Oracle数据库的HASH分区），Key分区的每个文件基本上大小一致。

5. 查看KEY分区表在不同查询场景中的性能（分区裁剪）：

看看SQL执行计划中分区裁剪操作是否会发生：

mysql> EXPLAIN FORMAT=TRADITIONAL SELECT Percentage FROM countrylanguage2 WHERE Percentage =99\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: countrylanguage2

partitions: p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 164

filtered: 10.00

Extra: Using where

1 row in set, 1 warning (0.001 sec)

可以看到，单值查询场景中，Key分区表的分区裁剪的确发生了：查询语句只访问了p2这个分区，而不是整个表。下面再执行范围查询：

mysql> EXPLAIN FORMAT=TRADITIONAL SELECT Percentage FROM countrylanguage2 WHERE Percentage <99\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: countrylanguage2

partitions: p0,p1,p2,p3

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 984

filtered: 33.33

Extra: Using where

1 row in set, 1 warning (0.001 sec)

可以看到，范围查询场景中，Key分区表的分区裁剪不会发生。由于原值经过哈希运算，已经失去了排序意义，所以Key分区表不适用于范围查询。

6. 删除分区操作：

无法删除Key分区表的分区：

mysql> alter table countrylanguage2 drop partition p0;

ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

如果要删除其分区，只能先将其转化成Range分区：

mysql> alter table countrylanguage2 partition by

-> RANGE (Percentage) (

-> PARTITION p0 VALUES LESS THAN (100),

-> PARTITION p1 VALUES LESS THAN (300),

-> PARTITION p2 VALUES LESS THAN (500),

-> PARTITION p3 VALUES LESS THAN MAXVALUE );

Query OK, 984 rows affected (0.103 sec)

Records: 984 Duplicates: 0 Warnings: 0

删除分区：

mysql> alter table countrylanguage2 drop partition p0;

Query OK, 0 rows affected (0.016 sec)

Records: 0 Duplicates: 0 Warnings: 0

查看一下InnoDB中每个分区所对应的一个文件：

[root@station95 ~]# cd /var/lib/mysql/world_y

[root@station95 world_y]# ls -l

total 1536

-rw-r----- 1 mysql mysql 622592 Mar 22 14:56 city.ibd

-rw-r----- 1 mysql mysql 131072 Mar 12 22:31 country.ibd

-rw-r----- 1 mysql mysql 229376 Mar 12 22:31 countryinfo.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 23:45 countrylanguage2#p#p1.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 23:44 countrylanguage2#p#p2.ibd

-rw-r----- 1 mysql mysql 114688 Mar 23 23:44 countrylanguage2#p#p3.ibd

-rw-r----- 1 mysql mysql 245760 Mar 12 22:31 countrylanguage.ibd

7. 将分区表还原为普通表：

mysql> alter table countrylanguage2 remove partitioning;

Query OK, 351 rows affected (0.042 sec)

Records: 351 Duplicates: 0 Warnings: 0

使用“show table status”命令，查看分区表的信息不存在：

mysql> show table status like 'countrylanguage2'\G

*************************** 1. row ***************************

Name: countrylanguage2

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 984

Avg_row_length: 133

Data_length: 131072

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2026-03-23 23:48:17

Update_time: 2026-03-23 05:23:17

Check_time: NULL

Collation: utf8mb4_0900_ai_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.004 sec)

查看文件：

[root@station95 ~]# cd /var/lib/mysql/world_y

[root@station95 world_y]# ls -l

total 1328

-rw-r----- 1 mysql mysql 622592 Mar 22 14:56 city.ibd

-rw-r----- 1 mysql mysql 131072 Mar 12 22:31 country.ibd

-rw-r----- 1 mysql mysql 229376 Mar 12 22:31 countryinfo.ibd

-rw-r----- 1 mysql mysql 131072 Mar 23 23:48 countrylanguage2.ibd

-rw-r----- 1 mysql mysql 245760 Mar 12 22:31 countrylanguage.ibd

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2026】MySQL 9.x数据库管理技巧6：事务隔离和锁---与Oracle数据库对比

博客文章2026】MySQL 9.x数据库管理技巧7：分区和分区性能

相关分类