InnoDB调优-分区表

最新推荐文章于 2024-05-17 19:54:16 发布

原创

最新推荐文章于 2024-05-17 19:54:16 发布 · 2.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #分区表 #innodb #mysql优化

本文介绍了MySQL的InnoDB分区表特性，详细讲解了RANGE、LIST、HASH、KEY和COLUMNS五种分区类型，强调了分区修剪在查询性能提升上的作用。同时，讨论了分区表的常用策略和需要注意的问题，包括分区数量、查询过滤、NULL值处理、索引与分区列匹配等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分区表是MySQL数据库在 5.1 版本时引入的新特性，主要用于数据库高可用性的管理。

分区表是一个独立的逻辑表，底层由多个物理子表组成；而分区的过程实际上就是将一个表分解为更小、更可管理的部分，对分区表的请求会被转化为对存储引擎的接口调用。当然，这对于应用来说是完全透明的。

MySQL在创建表时使用 PARTITION BY 语句指定分区类型和定义分区存放数据。因为分区不需要精确定位到每条数据的位置，也就无须额外的数据结构去记录每个分区有哪些数据，所以其代价非常低。

在执行查询的时候，优化器只需要根据一个简单的表达式就可以知道每个分区存放的是什么数据，然后过滤那些没有我们需要数据的分区。

use db_test;

create table t_p_test(
create_time datetime
)engine=innodb
-- 定义分区信息
partition by range ( year( create_time))(
partition p2016 values less than (2017),
partition p2017 values less than (2018)
);

创建分区表后，我们可以通过 INFORMATION_SCHEMA.PARTITIONS 查询到分区明细，很明显能够看到这是分成了2个区，分区的表达式为 year(create_time)

select PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS 
from INFORMATION_SCHEMA.PARTITIONS 
where TABLE_SCHEMA = schema() and TABLE_NAME = 't_p_test';

这里写图片描述

进入数据库数据文件存储路径，能够看到这个表实际上是被划分成了多个物理子表的。普通表是由一个 ibd文件组成，而分区后就变成了由建立分区时的各个分区 ibd文件组成。分区文件名遵循 “ 表名#P#分区名.ibd ” 格式

这里写图片描述

分区表底层由多个物理子表组成，而MySQL数据库目前仅支持局部分区（局部分区是指同一个分区中即存放了数据又存放了索引；全局分区则是数据放在各分区中，但是所有数据的索引放在一个对象中），这也就意味着索引也是按照分区子表定义的，没有全局索引。这一点在创建和使用分区时需要特别注意。

Partiton Pruning（分区修剪）

分区的本意是将数据按照一个较粗粒度分在不同的表中，便于聚拢数据做批量操作（例如一次批量删除整个分区的数据就变得十分的方便了）。

但是由于在对分区表进行查询时优化器能够根据分区表达式过滤一些分区，减少了需要扫描的数据量，这对于需要频繁地扫描一张很大的表的应用来说，无疑是一个可以很好地提高查询性能的方法。

相较与普通表，分区表在使用上并没有什么明显不同的地方

-- 写入
insert into t_p_test( create_time) 
values ('2016-01-01'), ('2016-12-04'), ('2017-02-01'), ('2017-07-10');

-- 查询
select * from t_p_test where create_time = '2017-07-10';

在执行了写入语句之后，数据其实是被写入了不同的分区的

这里写图片描述

而在进行查询时，也并没有扫描所有的数据，仅仅扫描了查询数据所在的分区。查看对分区表进行操作的执行计划明细，可以使用 EXPLAINS PARTITIONS 命令

explain partitions
select * from t_p_test where create_time = '2017-07-10';

这里写图片描述

我们能够看到在进行查询的时候，SQL优化器通过过滤函数直接扫描了数据所在的分区，并不会去扫描所有的分区，因而提升了查询的速度。这被称为“分区修剪”（Partition Pruning）。

分区类型

分区功能并不是在存储引擎层完成的，因此并非只有 InnoDB存储引擎支持分区。当然，因为不同存储引擎之间在对数据处理上存在差异，所以不同的存储引擎底层对分区数据的操作也会有细微的差别。

MySQL数据库支持 RANGE、LIST、HASH、KEY 这四种分区类型，在MySQL 5.5 版本后又新增 COLUMNS分区（可视为 RANGE分区和 LIST分区的一种进化）。在介绍这几种分区之前，我们需要牢记一点：MySQL数据库支持的分区类型为水平分区，也就是说不论创建何种类型的分区，都是根据 “行” 将数据分配到不同的物理文件中的。

1、RANGE分区

RANGE分区是比较常用的一种分区类型，行数据基于属于一个给定连续区间的列值被放入分区，简单的说就是根据给定范围进行划分。

例如先前定义的 t_p_test 表使用的便是 RANGE分区类型，根据时间列 create_time 列进行分区。这也是 RANGE分区较为常用的场景。

定义RANGE分区语句为 PARTITION BY RANGE( expr) ，这里的表达式 expr