MySQL分区

xinqing5130

已于 2023-05-17 09:44:03 修改

阅读量326

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 分区

于 2023-03-29 21:29:47 首次发布

本文链接：https://blog.csdn.net/xinqing5130/article/details/129845891

1、查看MySQL data目录

show variables like '%datadir%'

可查看MySQL文件目录，这里面每个数据库对应一个子目录；

2、分区优点

可以让单表存储更多的数据；
分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备；
根据分区列查询，会过滤无效的分区，只从某些分区查询，提高查询效率；
完全在数据库层面操作，与代码解耦，不需要程序编码；
分区表的数据更容易维护和管理。

3、分区缺点

分区最多1024个；
要在WHERE条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区，否则将访问这个表的所有分区，可能会非常慢；
分区表还有一些其他限制，比如所有的底层表必须使用相同的存储引擎，某些存储引擎也不支持分区；
分区一般应用于一台服务器上，但一台服务器的物理资源总是有限的，当数据达到这个极限时，即使分区，性能也可能会很低，所以这个时候分库是必须的；
分区表无法使用外键约束；
NULL 值会使分区过滤无效。

4、分区的类型

range分区，按照范围进行分区，下面是创建一个range分区表；

drop table if exists `range_table`;
create table `range_table`(
	`id` int,
	`name` varchar(10)
) 
partition by range(id)(
	partition p1 values less than (10),
	partition p2 values less than (20),
	partition p3 values less than maxvalue
);

list分区，list分区就是在创建各分区时具体指定哪些值属于这些分区；

drop table if exists `list_table`;
create table `list_table`(
	`id` int,
	`name` varchar(10)
) 
partition by list(id)(
	partition p1 values in (1),
	partition p2 values in (10,15,50)
);

hash分区，有常规hash和线性hash两种，区别在于计算上的区别。常规hash是在分区字段上基于分区个数的取模运算，根据余数分区。线性hash是对分区字段进行二次方运算，根据运算结果分区，所以hash分区同样要求分区字段为整型或者是可以返回整型结果的表达式；
```
--常规
drop table if exists `hash_table`;
create table `hash_table`(
	`id` int,
	`name` varchar(10)
) 
partition by hash(id)
partitions 3;
--线性
drop table if exists `hash_linear_table`;
create table `hash_linear_table`(
	`id` int,
	`name` varchar(10)
) 
partition by linear hash(id)
partitions 3;
```

key分区，类似于hash分区，只不过分区列不再强制为整型，可以为除text和BLOB两种类型外的其它类型。key分区分为常规key和线性key，常规key对分区字段采用的是MD5算法，线性key对分区字段采用的是二次方算法，类似hash分区在计算上的区别。

-- 常规
drop table if exists `key_table`;
create table `key_table`(
	`id` int,
	`name` varchar(10) not null,
	unique `uk_name` (name)
) 
partition by key()
partitions 3;
-- 线性
drop table if exists `key_table`;
create table `key_table`(
	`id` int,
	`name` varchar(10) not null,
	unique `uk_name` (name)
) 
partition by linear key()
partitions 3;

指定分区查询

分区本身是SQL无关的，在增删查改的时候，我们不需要指定具体分区，MySQL会自动帮我们找到对应的分区，不过如果想查询某个分区，也可以指定，比如下面：

select * from range_table partition (p1);