mysql一张表分区步骤_MySQL基础教程（16）表分区的创建于管理

短命女 2022-10-27 02:49 76阅读 0赞

一、MySQL表分区介绍

1、为什么要对表进行分区

由于MySQL默认是以文件的形式将表存储在磁盘上，如果一个表存放了太多数据，那么这个文件的体积就会变大，比如一个表文件就几十个G，这种情况对查询效率和数据安全都是不利的。为了解决这个问题就需要对表进行表分区操作，表分区和分表是不同的两个概念，表分区是将一个大表分成了多个逻辑块，在磁盘上也会按照表名+分区名创建出多个文件，但是对应用来说这些分区表是透明的，看上去仍然是一张表一个表名，属于集中式。分表则是将一张大表拆成了多张小表，可以存放在不同的实例上，属于分布式。除了分区分表外，还可以根据业务来对数据进行分库操作，比如用户信息都单独放在一个用户库里，又或者每个地区有每个地区自己的用户库等。

2、MySQL表分区有哪些限制

· 从5.6开始每张表支持8192个分区

· 分区字段必须有主键/唯一索引列

· 不能使用外键约束，不能使用存储过程，不支持全文索引，不支持临时表

· 分区还可以建立子分区，也叫复合分区

3、MySQL表分区类型

range：范围分区，适合按照月份或者编号范围进行分区，比如ID1-10000为一个分区，以此类推。

hash：哈希分区，按照HASH算法来随机分到每个区

list：列表分区，适合对字符进行分区，比如按省份分区，但是数据不会太均匀

key：类似HASH分区

二、MySQL表分区示例——范围分区

1、创建一张表，并且按年份字段进行分区

create table student (

\`id\` int not null,

\`name\` varchar(20) not null,

\`age\` varchar(10) not null,

\`sex\` varchar(10) not null,

\`joindate\` datetime not null,

\`email\` varchar(20) not null,

primary key (\`id\`,\`joindate\`)

)

partition by range(YEAR(joindate))

(

partition student\_2018 values less than (2019), \#2019以前的数据都在student\_2018分区中

partition student\_2019 values less than (2020), \#2020以前的数据都在student\_2019分区中

partition student\_2020 VALUES LESS THAN MAXVALUE \#大于2020的数据都在student\_2020分区中

);

2、插入测试数据，一条是2019年，一条是2020年

insert into \`student\` (\`id\`, \`name\`, \`age\`, \`sex\`, \`joindate\`, \`email\`) values('1','tom','33','man','2019-01-24 15:16:39','12@qq.com');

insert into \`student\` (\`id\`, \`name\`, \`age\`, \`sex\`, \`joindate\`, \`email\`) values('2','tom2','11','man','2020-09-08 15:17:08','24@qq.com');

3、查询数据，通过执行计划检查是否使用到了分区表

EXPLAIN SELECT \* FROM student WHERE joindate='2019-01-24 15:16:39'

EXPLAIN SELECT \* FROM student WHERE joindate='2020-09-08 15:17:08'

4、执行结果中可以看到分区表信息，即便在一个分区中进行了全表扫描，影响的行数也比不分区的要小很多

![b6945b4d150fda131802d5f0edd594a6.png][]

5、对已分区的表增加分区

show create table student;

alter table student drop partition student\_2020; \#先删除最后一个分区，这个过程不会清空该分区数据，只会重新分配到其它分区中

alter table student add partition (partition student\_2020 values less than (2021)) \#为2020新增分区

alter table student add partition (partition student\_2021 values less than maxvalue) \#再创建一个新的最后分区

三、MySQL表分区示例——列表分区

create table student (

id int not null,

name varchar(20) not null,

age int not null

)

partition by list(age)

(

partition age\_1 values in (20),

partition age\_2 values in (30,40)

);

四、MySQL表分区示例——HASH分区

用hash分区可以指定一个分区数量，然后数据会很平均的分配到这些分区中

create table student (

id int not null,

name varchar(20) not null,

age int not null

)

partition by hash(id)

partitions 5 \#创建了5个分区，假如插入5W数据，那么每个分区大概就是1W条数据

[b6945b4d150fda131802d5f0edd594a6.png]: /images/20221024/71bb103b44e14a3fbbafcd648d5b1441.png

发表评论取消回复

表情：

评论列表（有 0 条评论，76人围观）

还没有评论，来说两句吧...

相关阅读

相关查询mysql分区表其中一个分区的数据

MySQL分区表的一个分区的数据可以使用如下的查询语句获取: SELECT FROM table_name WHERE partition_column_name

╰+哭是因爲堅強的太久メ/ 2024年03月25日 13:14/ 0 赞/ 60 阅读

相关 mysql一张表分区步骤_MySQL基础教程（16）表分区的创建于管理

一、MySQL表分区介绍 1、为什么要对表进行分区由于MySQL默认是以文件的形式将表存储在磁盘上，如果一个表存放了太多数据，那么这个文件的体积就会变大，比如一个表文件就

短命女/ 2022年10月27日 02:49/ 0 赞/ 77 阅读

相关 mysql 分区表优化_MySQL优化之分区表

当数据库数据量涨到一定数量时，性能就成为我们不能不关注的问题，如何优化呢？常用的方式不外乎那么几种： 1、分表，即把一个很大的表达数据分到几个表中，这样每个表数据都不多。

素颜马尾好姑娘i/ 2022年10月27日 02:15/ 0 赞/ 343 阅读

相关 mysql —— 分表分区

面对当今大数据存储，设想当mysql中一个表的总记录超过1000W，会出现性能的大幅度下降吗？答案是肯定的，一个表的总记录超过1000W，在操作系统层面检索也是效率非常低的

旧城等待，/ 2022年08月26日 15:21/ 0 赞/ 251 阅读

相关 mysql 表分区按年分区，季度子分区

alter table zz_info partition by range(year(date2)) subpartition by hash(quarter(dat

た入场券/ 2022年06月13日 13:29/ 0 赞/ 569 阅读

相关创建mysql表分区的方法

from http://www.jb51.net/article/42544.htm 表分区是最近才知道的哦，以前自己做都是分表来实现上亿级别的数据了，下面我来给大家介绍一

野性酷女/ 2022年06月13日 01:12/ 0 赞/ 207 阅读

相关 mysql分区、分表学习

> 现在学习mysql表分区，百度了资料，特整理一下，原文章 [https://my.oschina.net/ydsaky...][https_my.oschina.net_y

ゝ一纸荒年。/ 2022年06月02日 09:35/ 0 赞/ 315 阅读

相关 mysql分区和分表

mysql分区和分表？分区 1）为什么要分区？如果一张表的数据量太大的话，索引和数据空间会很大，查找数据就会变的很慢。这个时候我们可以利用mysql的分区功能

一时失言乱红尘/ 2022年05月19日 00:07/ 0 赞/ 279 阅读

相关 mysql创建表分区

文章目录创建表分区创建批量查询测试数据的存储过程创建表分区注意：创建分区个数最好是质数 DROP TABLE IF EXISTS `

怼烎@/ 2021年11月02日 17:31/ 0 赞/ 489 阅读

相关 mysql分表分区

1、为什么要分表？数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。 mysq

爱被打了一巴掌/ 2021年09月21日 23:52/ 0 赞/ 525 阅读