首頁 > 軟體

Mysql四種分割區方式以及組合分割區落地實現詳解

2022-04-14 10:03:51

看這篇文章前需要先了解一下以下幾個問題~

一、問題

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

總結

到此這篇關於Mysql四種分割區方式以及組合分割區落地實現的文章就介紹到這了,更多相關Mysql組合分割區落地內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com