MySql创建分区的方法实例

连忻乐

一、Mysql分区类型

1、RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
2、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
3、KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
4、复合分区：基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

二、RANGE分区

缺点：1、只能通过整形类型的主键建进行分区
2、分区数据不平均
1、创建分区

1. DROP TABLE IF EXISTS `product_partiton_range`;
   
2. CREATE TABLE `product_partiton_range`  (
   
3.   `Id` int(11) NOT NULL AUTO_INCREMENT,
   
4.   `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
   
5.   `ProductId` int(11) NOT NULL,
   
6.   PRIMARY KEY (`Id`) USING BTREE
   
7. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4
   
8. 
   
9. PARTITION BY RANGE (Id) PARTITIONS 3 (
   
10. PARTITION part0 VALUES LESS THAN (1000),
    
11. PARTITION part1 VALUES LESS THAN (2000),
    
12. PARTITION part2 VALUES LESS THAN MAXVALUE);

复制代码

2、批量添加数据

1. DROP PROCEDURE IF EXISTS PROC_USER_INSERT;
   
2. delimiter $$
   
3. -- 创建存储过程
   
4. CREATE  PROCEDURE PROC_USER_INSERT(
   
5. IN START_NUM INT,
   
6. IN MAX_NUM INT
   
7. )
   
8. BEGIN
   
9. 
   
10. DECLARE TEMP_NUM INT DEFAULT 0;
    
11. SET TEMP_NUM=START_NUM;
    
12. 
    
13. WHILE TEMP_NUM<=MAX_NUM  DO
    
14.     INSERT INTO product_partiton_range(ProductName,ProductId) VALUES('XIAOHEMIAO',TEMP_NUM);
    
15.     SET TEMP_NUM=TEMP_NUM+1;
    
16. END WHILE;
    
17. 
    
18. 
    
19. END$$ ;
    
20. delimiter;
    
21. 
    
22. -- 调用存储过程
    
23. CALL PROC_USER_INSERT(1,5000);

复制代码

3、通过EXPLAIN PARTITIONS命令发现SQL优化器只需搜对应的区，不会搜索所有分区

4、如果sql语句有问题，那么会走所有区。会很危险。所以分区表后，select语句必须走分区键。

5、查看当前表的分区情况

1. SELECT
   
2. partition_name part,
   
3. partition_expression expr,
   
4. partition_description descr,
   
5. table_rows
   
6. FROM information_schema.partitions WHERE
   
7. table_schema = SCHEMA()
   
8. AND table_name='product_partiton_range';

复制代码

二、Hash分区

优点：分区数据比较平均
缺陷：HASH分区只能对数字字段进行分区，无法对字符字段进行分区。如果需要对字段值进行分区，必须包含在主键字段内
1、创建分区

1. DROP TABLE IF EXISTS `product_partiton_hash`;
   
2. CREATE TABLE `product_partiton_hash`  (
   
3.   `Id` int(11) NOT NULL AUTO_INCREMENT,
   
4.   `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
   
5.   `ProductId` int(11) NOT NULL,
   
6.   PRIMARY KEY (`Id`) USING BTREE
   
7. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4
   
8. 
   
9. PARTITION BY HASH (Id) PARTITIONS 3 ;

复制代码

三、Key分区

优点：除了text，blob类型字段，其他类型字段都可以进行分区
缺陷：不支持text，blob(二进制)类型的字段进行分区
1、创建分区

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

复制代码

四、List分区

优点：支持枚举类型的字段进行分区，比如商品状态，商品类型
1、创建分区

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

复制代码

2、插入数据

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

复制代码

3、查看当前表的分区情况

附：查询分区，各区表数据量

1. SELECT PARTITION_NAME,TABLE_ROWS
   
2. FROM INFORMATION_SCHEMA.PARTITIONS
   
3. WHERE TABLE_NAME = 'table';

复制代码

总结

1、分区字段必须是主键
2、分区字段，必须以分区字段进行查询，否则分区失效

友情链接

http://bbs.yzwlo.com/article/244256.htm
http://bbs.yzwlo.com/article/244313.htm
http://bbs.yzwlo.com/article/244318.htm
到此这篇关于MySql创建分区的文章就介绍到这了,更多相关MySql创建分区内容请搜索晓枫资讯以前的文章或继续浏览下面的相关文章希望大家以后多多支持晓枫资讯！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

山丽佳

感谢楼主，顶。