mysql 分区

最新推荐文章于 2023-04-29 11:29:01 发布

Lei_Da_Gou

最新推荐文章于 2023-04-29 11:29:01 发布

阅读量224

点赞数

分类专栏： mysql 文章标签： mysql分区学习

本文链接：https://blog.csdn.net/Lei_Da_Gou/article/details/87803299

版权

mysql 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

一、分区类型

1.RANGE：范围分区如ID在1-10000的分一个区，ID在10001-20000的分一个区

2.LIST：集合分区如ID集合为1、3、5、7的为一个分区、ID集合为2、4、6、8的为一个分区

3.HASH：分区键必须为整数、因为底层用的取模mod或者2的幂（powers-of-two、线性哈希分区）运行法则进行分区，按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。线性哈希分区的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

4.key：KEY 分区和 HASH 分区相似，但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区，而 HASH 分区只支持数字分区，KEY 分区不允许使用用户自定义的表达式进行分区，KEY 分区使用系统提供的 HASH 函数进行分区。

注意点1：表分区成功、分区键必须为主键、或者为联合主键的一部分、或者表不设置主键。（如你想将create_time作为分区键，这该表中要么没有主键、要么该字段为联合主键的一部分、要么该字段为主键。）

2.分区创建

-- 表创建
CREATE TABLE `t_student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学生',
  `name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `id_card` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '身份证号',
  `password` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '密码（默认身份证号后6位）',
  `student_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '学籍号',
  `gender` ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
  `nation` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名族',
  `native_place` VARCHAR(100) DEFAULT '' COMMENT '籍贯',
  `address` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '家庭地址',
  `phone` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '联系电话',
  `classroom_id` INT(11) NOT NULL DEFAULT '0' COMMENT '所属班级ID',
  `school_id` INT(11) NOT NULL DEFAULT '0' COMMENT '所属学校ID',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `deleted` INT(1) NOT NULL DEFAULT '0' COMMENT '0-正常 1-删除',
  PRIMARY KEY (`id`),
  KEY `idCard` (`id_card`) USING BTREE,
  KEY `studentCode` (`student_code`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生表_分区';
-- 分区创建（这里为修改表从不分区至分区）
ALTER TABLE t_student PARTITION BY LIST(school_id) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3),
    PARTITION p4 VALUES IN (4)
);

-- 创建表时直接分区
CREATE TABLE T1 (
     id int(8) NOT NULL ,
     createtime datetime NOT NULL
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p19 VALUES LESS ThAN  MAXVALUE);
--------------------- 
作者：泊川 
来源：CSDN 
原文：https://blog.csdn.net/wantken/article/details/31764361

注意：上面表我将id和school_id修改为联合主键才能分区成功。

3.查看分区表的情况

-- 查看表分区情况1
SELECT
	partition_name part,
	partition_expression expr,
	partition_description descr,
	table_rows
FROM 
	information_schema.partitions 
WHERE
	table_schema = SCHEMA()
	AND table_name='t_student';

-- 查看表分区情况2
EXPLAIN 
PARTITIONS SELECT * FROM `t_student`

4.插入数据、school_id为1,2,3,4分别1000条

-- 创建过程
DELIMITER $$
DROP PROCEDURE  IF EXISTS load_part_tab$$ 
CREATE PROCEDURE load_part_tab()
BEGIN
    -- declare v int default 0;
    DECLARE v INT;
    SET v = 0;
    WHILE v < 1000
    DO
        INSERT INTO 
		t_student
	VALUES 
		(NULL,'名称','511622','110','222','男','汉族','四川绵阳','四川绵阳涪城','10086',(RAND(v)*36520) MOD 3652,1,NULL,0),
		(NULL,'名称','511622','110','222','男','汉族','四川绵阳','四川绵阳涪城','10086',(RAND(v)*36520) MOD 3652,2,NULL,0),
		(NULL,'名称','511622','110','222','男','汉族','四川绵阳','四川绵阳涪城','10086',(RAND(v)*36520) MOD 3652,3,NULL,0),
		(NULL,'名称','511622','110','222','男','汉族','四川绵阳','四川绵阳涪城','10086',(RAND(v)*36520) MOD 3652,4,NULL,0);
        SET v = v + 1;
    END WHILE;
END $$
DELIMITER ;
-- 调用过程
CALL load_part_tab();

5.测试查看

-- 未分区表
EXPLAIN
SELECT * FROM t_student_no WHERE school_id=1 AND classroom_id BETWEEN 500 AND 1000
-- 分区表
EXPLAIN
SELECT * FROM t_student WHERE school_id=1 AND classroom_id BETWEEN 500 AND 1000

结果分别为：

说明：t_student_no 为未分区的表、t_student为分区后的表。t_student_no的表数据是从t_student蠕虫复制过去的，数据一模一样。sql为：

-- 蠕虫复制插入数据库很快
INSERT INTO t_student_no SELECT * FROM t_student;

-- 查看mysql表物理位置
SHOW GLOBAL VARIABLES LIKE '%datadir%'
-- 查看分区插件是否打开的
SHOW PLUGINS;
-- 查看mysql版本，5.1以上的好像才支持
SELECT VERSION();

分区后的表明显遍历的记录从4000变为1000条。效率确实提高了。测试完毕。

总结下：

0）如果不走分区键、即查询条件无分区键、建议不要使用分区，否则很有可能造成全表锁。

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难