Mysql四种分区方式以及组合分区落地实现详解
作者:广海_小疯疯丶 发布时间:2024-01-23 05:05:25
标签:mysql,分区,落地
看这篇文章前需要先了解一下以下几个问题~
一、问题
1.分区是什么
分区:就是把一张表数据分块存储
目的:提升索引的查询效率
2.Mysql为什么要使用分区
先从数据分析
然后进行索引优化
然后引入分区
3.Mysql中分区原理
客户端---------> Id 和分区键进行比较------------->找到指定分区---------->和数据库查询一致
4.Mysql中分区局限
必须使用分区字段才行,不然分区查询就会失败。走所有分区。
目前Range是范围分区,但是有时候我们会发现。分区大小永远是静态的。
所以会存在一个分区表大小不均。如何让分区表大小均衡呢?
二、分区落地实现
1.Range分区
条件
Product-Partiton表
步骤
1、先创建Product-Partiton-Range
CREATE TABLE `product-Partiton-Range` (
`Id` BIGINT(8) NOT NULL,
`ProductName` CHAR(245) NOT NULL DEFAULT '1',
`ProductId` CHAR(255) NOT NULL DEFAULT '1',
`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`),
INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (Id) PARTITIONS 3 (
PARTITION part0 VALUES LESS THAN (12980),
PARTITION part1 VALUES LESS THAN (25960),
PARTITION part2 VALUES LESS THAN MAXVALUE);
2、然后查询分区表
select * from product-Partiton-Range where Id = 25000
2.Hash分区
步骤
1、先创建Product-Partiton-Hash
CREATE TABLE `product-Partiton-Hash` (
`Id` BIGINT(8) NOT NULL,
`ProductName` CHAR(245) NOT NULL DEFAULT '1',
`ProductId` CHAR(255) NOT NULL DEFAULT '1',
`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`),
INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (Id) PARTITIONS 3;
Hash分区只能进行数字字段进行分区,无法进行字符字段进行分区。如果需要对字段值进行分区。
必须包含在主键字段内。
3.Key分区
步骤
1、先创建Product-Partiton-Key
CREATE TABLE `product-Partiton-Key` (
`Id` BIGINT(8) NOT NULL,
`ProductName` CHAR(245) NOT NULL DEFAULT '1',
`ProductId` CHAR(255) NOT NULL DEFAULT '1',
`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`),
INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY (ProductName) PARTITIONS 3;
#建立复合主键
CREATE TABLE `product-Partiton-Key` (
`Id` BIGINT(8) NOT NULL,
`ProductName` CHAR(245) NOT NULL DEFAULT '1',
`ProductId` CHAR(255) NOT NULL DEFAULT '1',
`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`),
INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY (ProductName) PARTITIONS 3;
以上分区都是一个特点:所有的分区必须连续和连续大小进行分区。
我们再来看一个场景:如何对商品订单分区。
4.Mysql中如何落地List分区
步骤
1、先创建Product-Partiton-List
CREATE TABLE `product-Partiton-List` (
`Id` BIGINT(8) NOT NULL,
`ProductName` CHAR(245) NOT NULL DEFAULT '1',
`ProductId` CHAR(255) NOT NULL DEFAULT '1',
`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
`ProductStatus` int NOT NULL DEFAULT 0,
PRIMARY KEY (`Id`),
INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(ProductId) (
PARTITION a VALUES IN (1,5,6),
PARTITION b VALUES IN (2,7,8)
);
商品主键和商品名称进行分区。
5.Mysql中如何落地组合分区
步骤
CREATE TABLE `product-Partiton-flex` (
`Id` BIGINT(8) NOT NULL,
`ProductName` CHAR(245) NOT NULL DEFAULT '1',
`ProductId` CHAR(255) NOT NULL DEFAULT '1',
`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`,`ProductName`),
INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (Id) PARTITIONS 3
SUBPARTITION BY KEY(ProductName)
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (12980),
PARTITION p1 VALUES LESS THAN (25960),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
三、Mysql如何管理分区
1.删除分区
ALERT TABLE users DROP PARTITION p0;
#删除分区 p0
2.重建分区
2.1RANGE 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
#将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
2.2 LIST 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
#将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
2.3 HASH/KEY 分区重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
#用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
3. 新增分区
3.1 新增 RANGE 分区
#新增一个RANGE分区
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');
3.2 新增 HASH/KEY 分区
ALTER TABLE users ADD PARTITION PARTITIONS 8; #将分区总数扩展到8个。
3.3 给已有的表加上分区
alter table results partition by RANGE (month(ttime))
(
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) ,
PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) ,
PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) ,
PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) ,
PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) ,
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13)
);
4.默认分区限制分区字段必须是主键(PRIMARY KEY)的一部分,去除此限制
[方法1] 使用ID:
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
#ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
[方法2] 将原有PK去掉生成新PK
mysql> alter table results drop PRIMARY KEY;
Query OK, 5374850 rows affected (7 min 4.05 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
mysql> alter table results add PRIMARY KEY(id, ttime);
Query OK, 5374850 rows affected (7 min 4.05 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
来源:https://blog.csdn.net/qq_34202873/article/details/121111232
0
投稿
猜你喜欢
- 这里有numpy数组的相关介绍https://www.jb51.net/article/130657.htm排序numpy与python列表
- 直接看例子:#!/usr/bin/python# -*- coding: utf-8 -*-from bs4 import Beautifu
- 微信小程序与php 实现微信支付的简单实例小程序访问地址:payfee.PHP:include 'WeixinPay.php'
- 1. 采用工作区设置默认解释器的方式(推荐)下载完vscode,并安装python支持之后。使用vscode打开一个空文件夹。点击左侧的运行
- 本文实例讲述了python通过urllib2获取带有中文参数url内容的方法。分享给大家供大家参考。具体如下:对于中文的参数如果不进行编码的
- 背景:有一个爬虫服务,需要定时从公开网站上拉取一些数据,为了避免被识别为爬虫(防爬虫的识别需要根据很多特征,时间仅仅是其中一个维度),需要在
- UPDATE语句的速度更新查询的优化同SELECT查询一样,需要额外的写开销。写速度依赖于更新的数据大小和更新的索引的数量。没有更改的索引不
- 本文研究的主要是Python处理文本换行符的相关内容,具体如下。源文件每行后面都有回车,所以用下面输出时,中间会多了一行try:  
- 目前可实现:MD5算法、SHA256算法、先MD5后SHA256、先SHA256后MD5、两次MD5、两次SHA256、前8位MD5算法后8
- 这里分享一些轨迹聚类的基本方法,涉及轨迹距离的定义、kmeans聚类应用。需要使用的python库如下import pandas as pd
- 这几天在学习PyQt编程,用CSDN来记录一下学过的东西,免得以后忘记了大笑import sys from PyQt4 import QtG
- 一、日志输出到文件使用模块:logging可以生成自定义等级日志,可以输出日志到指定路径日志等级:debug(调试日志) < info
- 一、背景交通大数据是由交通运行管理直接产生的数据(包括各类道路交通、公共交通、对外交通的刷卡、线圈、卡口、GPS、视频、图片等数据)、交通相
- 需求背景:用户希望他登录之后,浏览器就帮他记住登录状态,这样他就不用每次进入的时候,都需要登录一次。session过期时间:如果我们没有设置
- 本文实例为大家分享了python实现计算器功能的具体代码,供大家参考,具体内容如下前缀表达式运算符在数字的前面1 + (2 + 3) * 4
- 在Pandas中读取CSV数据时,会默认将第一列设为索引列index。但有时候我们并不需要索引,或者希望指定自己的索引列。这时就需要在导入C
- 关于怎么获得,我想其实网上有很多答案。list.index( )获得值的索引值,但是如果list中含有的值一样,例如含有两个11,22,这样
- 目录Python的内置数据类型中的数字1、变量2、数据类型总览3、Python是弱类型的语言4、各数据类型的详细介绍4.1 整数(int)4
- 这个效果前些日子有人在论坛问起,今天有空研究了下。我打了原创标记,因为我在写这个方法时的确没有参考过别人的代码。我的方法其实就是层遮罩加AL
- 英文文档:format(value[, format_spec])Convert a value to a “formatted” repr