MySql建立分割槽

SportSky發表於2022-04-12

MySql

一、Mysql分割槽型別

1、RANGE 分割槽：基於屬於一個給定連續區間的列值，把多行分配給分割槽。

2、HASH分割槽：基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL中有效的、產生非負整數值的任何表示式。

3、KEY分割槽：類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。

4、複合分割槽：基於RANGE/LIST 型別的分割槽表中每個分割槽的再次分割。子分割槽可以是 HASH/KEY 等型別。

二、RANGE分割槽

缺點：1、只能通過整形型別的主鍵建進行分割槽

2、分割槽資料不平均

1、建立分割槽

DROP TABLE IF EXISTS `product_partiton_range`;
CREATE TABLE `product_partiton_range`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductId` int(11) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 

PARTITION BY RANGE (Id) PARTITIONS 3 (
PARTITION part0 VALUES LESS THAN (1000), 
PARTITION part1 VALUES LESS THAN (2000), 
PARTITION part2 VALUES LESS THAN MAXVALUE);

2、批量新增資料

DROP PROCEDURE IF EXISTS PROC_USER_INSERT;
delimiter $$
-- 建立儲存過程
CREATE  PROCEDURE PROC_USER_INSERT(
IN START_NUM INT,
IN MAX_NUM INT
)
BEGIN 

DECLARE TEMP_NUM INT DEFAULT 0;
SET TEMP_NUM=START_NUM;

WHILE TEMP_NUM<=MAX_NUM  DO
    INSERT INTO product_partiton_range(ProductName,ProductId) VALUES('XIAOHEMIAO',TEMP_NUM);
    SET TEMP_NUM=TEMP_NUM+1;
END WHILE;


END$$ ;
delimiter;

-- 呼叫儲存過程
CALL PROC_USER_INSERT(1,5000);

3、通過EXPLAIN PARTITIONS命令發現SQL優化器只需搜對應的區，不會搜尋所有分割槽

4、如果sql語句有問題，那麼會走所有區。會很危險。所以分割槽表後，select語句必須走分割槽鍵。

5、檢視當前表的分割槽情況

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='product_partiton_range';

二、Hash分割槽

優點：分割槽資料比較平均

缺陷：HASH分割槽只能對數字欄位進行分割槽，無法對字元欄位進行分割槽。如果需要對欄位值進行分割槽，必須包含在主鍵欄位內

1、建立分割槽

DROP TABLE IF EXISTS `product_partiton_hash`;
CREATE TABLE `product_partiton_hash`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductId` int(11) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 

PARTITION BY HASH (Id) PARTITIONS 3 ;

三、Key分割槽

優點：除了text，blob型別欄位，其他型別欄位都可以進行分割槽

缺陷：不支援text，blob(二進位制)型別的欄位進行分割槽

1、建立分割槽

DROP TABLE IF EXISTS `product_partiton_key`;
CREATE TABLE `product_partiton_key`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductId` int(11) NOT NULL,
  PRIMARY KEY (`Id`,`ProductName`) ,
  INDEX `ProductId_index`(`ProductId`) 
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 
PARTITION BY KEY (ProductName) PARTITIONS 3 ;

四、List分割槽

優點：支援列舉型別的欄位進行分割槽，比如商品狀態，商品型別

1、建立分割槽

DROP TABLE IF EXISTS `product_partiton_list`;
CREATE TABLE `product_partiton_list`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductId` int(11) NOT NULL,
    `ProductStatus` int(11) NOT NULL,
  PRIMARY KEY (`Id`,`ProductStatus`) ,
  INDEX `ProductId_index` (`ProductId`) 
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 
PARTITION BY LIST(ProductStatus)(
    PARTITION p0 VALUES in(0,1),
    PARTITION p1 VALUES in(2,3,4)
);

2、插入資料

INSERT INTO product_partiton_list(ProductName,ProductId,ProductStatus) VALUES('XIAOHEMIAO',1,0);
INSERT INTO product_partiton_list(ProductName,ProductId,ProductStatus) VALUES('XIAOHEMIAO',1,1);
INSERT INTO product_partiton_list(ProductName,ProductId,ProductStatus) VALUES('XIAOHEMIAO',1,2);
INSERT INTO product_partiton_list(ProductName,ProductId,ProductStatus) VALUES('XIAOHEMIAO',1,3);
INSERT INTO product_partiton_list(ProductName,ProductId,ProductStatus) VALUES('XIAOHEMIAO',1,4);