电玩城打鱼捕鱼-专业24小时上下分

MySQL分区表partition线上修改分区字段,Mysql数据表分区技术PARTITION浅析

六月 27th, 2019  |  电玩城上下分数据库

MySQL分区表partition线上修改分区字段

在这一章节里, 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)
 
Mysql 的分区技术与水平分表有点类似, 但是它是在逻辑层进行的水平分表,
对于应用而言它还是一张表, 换句话说:
分区不是实际真正的对一张表进行拆分,分区之后表还是一个表,它是把存储文件进行拆分。

高性能MySql进化论(十二):Mysql中分区表的使用总结。当数据量非常大时(表的容量到达GB或者是TB),如果仍然采用索引的方式来优化查询,由于索引本生的消耗以及大量的索引碎片的产生,查询的过程会导致大量的随机I/O的产生,在这种场景下除非可以很好的利用覆盖索引,否则由于在查询的过程中需要根据索引回数据表查询,会导致性能受到很大的影响,这时可以考虑通过分区表的策略来提高查询的性能。

 

在 Mysql 5.1(后) 有了几种分区类型:
 
RANGE分区: 基于属于一个给定连续区间的列值, 把多行分配给分区

不同的数据库管理系统对分区的实现可能有所区别,本文主要以MySQL为基础

公司线上在用partition,有一个表的分区字段错了,需要重建,结果发现没有办法像修改主键字段或者修改索引字段那样直接一条sql搞定。而是需要建临时表,有down
time,所以去仔细看了文档,研究下partition的细节问题。

LIST分区: 类似于按 RANGE 分区, 区别在于 LIST
分区是基于列值匹配一个离散值集合中的某个值来进行选择

1 分区的类型

自己公司线上采取的时候,凌晨1点业务低峰期,执行:

HASH分区: 基于用户定义的表达式的返回值来进行选择分区,
该表达式使用将要插入到表中的这些行的列值进行计算, 这个函数可以包含
Mysql 中有效的、产生非负整数值的任何表达式

1.1RANGE分区

建立临时表

KEY分区: 累世于按 HASH 分区, 区别在于 KEY 分区只支持计算一列或多列, 且
Mysql 服务器提供其自身的哈希函数
 
分区应该注意的事项:

按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠,使用VALUES
LESS THAN操作符来进行定义

CREATE TABLE tbname_TMP (

1、 做分区时,要么不定义主键,要么把分区字段加入到主键中
2、 分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量 NOT NULL
 
首先你可以查看下你的 Mysql 版本是否支持 PARTITION

假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分区模式的CREATE
TABLE 语句的一个例子如下所示。

    SHARD_ID INT NOT NULL,

复制代码 代码如下:

例如,你可能决定通过添加一个PARTITION BY RANGE子句把这个表分割成4个区间

    …

mysql> show plugins;
 
| partition    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

CREATE TABLE employees (

    xxx_DATE DATETIME NOT NULL,

或者:

id INT NOT NULL,

    PRIMARY KEY (xxx_DATE,shard_id)

复制代码 代码如下:

fname VARCHAR(30),

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

mysql> show variables like “%part%”;
 
+——————-+——-+
| Variable_name     | Value |
+——————-+——-+
| have_partitioning | YES   |
+——————-+——-+

lname VARCHAR(30),

PARTITION BY LIST(MONTH(xxx_DATE)) (

RANGE 分区  
假定你创建了一个如下的表, 该表保存有20家音像店的职员记录,
这20家音像店的编号从1到20。 如果你想将其分成4个小分区,
那么你可以采用RANGE分区, 创建的数据库表如下:

hired DATE NOT NULL DEFAULT ‘1970-01-01’,

    PARTITION m1 VALUES IN (1),

复制代码 代码如下:

separated DATE NOT NULL DEFAULT ‘9999-12-31’,

    PARTITION m2 VALUES IN (2),

mysql-> CREATE TABLE employees (
     ->     id INT NOT NULL,
     ->     fname VARCHAR(30),
     ->     lname VARCHAR(30),
     ->     hired DATE NOT NULL DEFAULT ‘1970-01-01’,
     ->     separated DATE NOT NULL DEFAULT ‘9999-12-31’,
     ->     job_code INT NOT NULL,
     ->     store_id INT NOT NULL
     -> ) ENGINE=Myisam DEFAULT CHARSET=utf8
     -> PARTITION BY RANGE (store_id) (
     ->     PARTITION P0 VALUES LESS THAN (6),
     ->     PARTITION P1 VALUES LESS THAN (11),
     ->     PARTITION P2 VALUES LESS THAN (16),
     ->     PARTITION P3 VALUES LESS THAN (21)
     -> );

job_code INT,

    PARTITION m3 VALUES IN (3),

如果你想把不同时期离职的员工进行分别存储, 那么你可以将日期字段
separated (即离职时间) 作为一个 key, 创建的 SQL 语句如下:

store_id INT

    PARTITION m4 VALUES IN (4),

复制代码 代码如下:

)

    PARTITION m5 VALUES IN (5),

mysql-> CREATE TABLE employees (
     ->     id INT NOT NULL,
     ->     fname VARCHAR(30),
     ->     lname VARCHAR(30),
     ->     hired DATE NOT NULL DEFAULT ‘1970-01-01’,
     ->     separated DATE NOT NULL DEFAULT ‘9999-12-31’,
     ->     job_code INT NOT NULL,
     ->     store_id INT NOT NULL
     -> ) ENGINE=Myisam DEFAULT CHARSET=utf8
     -> PARTITION BY RANGE (YEAR(separated)) (
     ->     PARTITION P0 VALUES LESS THAN (2001),
     ->     PARTITION P1 VALUES LESS THAN (2011),
     ->     PARTITION P2 VALUES LESS THAN (2021),
     ->     PARTITION P3 VALUES LESS THAN MAXVALUE
     -> );

PARTITION BY RANGE (YEAR(separated)) (

    PARTITION m6 VALUES IN (6),

 
List 分区  
同样的例子, 如果这20家影像店分布在4个有经销权的地区,

PARTITION p0 VALUES LESS THAN (1991),

    PARTITION m7 VALUES IN (7),

复制代码 代码如下:

PARTITION p1 VALUES LESS THAN (1996),

    PARTITION m8 VALUES IN (8),

+——————+————————————–+
| 地区             | 音像店 ID 号                         |
+——————+————————————–+
| 北区             | 3, 5, 6, 9, 17                       |
| 东区             | 1, 2, 10, 11, 19, 20                 |
| 西区             | 4, 12, 13, 14, 18                    |
| 中心区           | 7, 8, 15, 16                         |
+——————+————————————–+
 
mysql-> CREATE TABLE employees (
     ->     id INT NOT NULL,
     ->     fname VARCHAR(30),
     ->     lname VARCHAR(30),
     ->     hired DATE NOT NULL DEFAULT ‘1970-01-01’,
     ->     separated DATE NOT NULL DEFAULT ‘9999-12-31’,
     ->     job_code INT NOT NULL,
     ->     store_id INT NOT NULL
     -> ) ENGINE=Myisam DEFAULT CHARSET=utf8
     -> PARTITION BY LIST (store_id) (
     ->     PARTITION pNorth   VALUES IN (3, 5, 6, 9, 17),
     ->     PARTITION pEast    VALUES IN (1, 2, 10, 11, 19, 20),
     ->     PARTITION pWest    VALUES IN (4, 12, 13, 14, 18),
     ->     PARTITION pCentral VALUES IN (7, 8, 15, 16)
     -> );

PARTITION p2 VALUES LESS THAN (2001),

    PARTITION m9 VALUES IN (9),

当你创建完之后, 你可以进入 Mysql 数据储存文件, 该文件夹位置定义在
Mysql 配置文件中

PARTITION p3 VALUES LESS THAN MAXVALUE

    PARTITION m10 VALUES IN (10),

复制代码 代码如下:

);

    PARTITION m11 VALUES IN (11),

[email protected]:~$
sudo vi /etc/mysql/my.cnf;
 
[mysqld]
datadir         = /var/lib/mysql
 
[email protected]:~$
cd /var/lib/mysql/dbName
[email protected]:/var/lib/mysql/dbName$
ll
 
显示如下:
8768 Jun  7 22:01 employees.frm
  48 Jun  7 22:01 employees.par
   0 Jun  7 22:01 employees#P#pCentral.MYD
1024 Jun  7 22:01 employees#P#pCentral.MYI
   0 Jun  7 22:01 employees#P#pEast.MYD
1024 Jun  7 22:01 employees#P#pEast.MYI
   0 Jun  7 22:01 employees#P#pNorth.MYD
1024 Jun  7 22:01 employees#P#pNorth.MYI
   0 Jun  7 22:01 employees#P#pWest.MYD
1024 Jun  7 22:01 employees#P#pWest.MYI

插入一些测试数据后发现P1的数据文件明显增大 [sql] view plain copy
print?

    PARTITION m12 VALUES IN (12)

从这里可以看出, 它是把存储文件根据我们的定义进行了拆分

mysql> DELIMITER $$

);

复制代码 代码如下:

mysql> DROP PROCEDURE IF EXISTS SampleProc$$

切换表名字,修改表结构

employees.frm = 表结构
employees.par = partition, 申明是一个分区表
.MYD = 数据文件
.MYI = 索引文件
 

Query OK, 0 rows affected (0.00 sec)

RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;

HASH 分区  
HASH 分区主要用来确保数据在预先确定数目的分区中平均分布
如果你想把不同时期加入的员工进行分别存储, 那么你可以将日期字段 hired
作为一个 key

mysql> CREATE PROCEDURE SampleProc()

导入原始数据

复制代码 代码如下:

-> BEGIN

insert into xxx select * from xxx_DELETEDxxx_DELETED;

mysql-> CREATE TABLE employees (
     ->     id INT NOT NULL,
     ->     fname VARCHAR(30),
     ->     lname VARCHAR(30),
     ->     hired DATE NOT NULL DEFAULT ‘1970-01-01’,
     ->     separated DATE NOT NULL DEFAULT ‘9999-12-31’,
     ->     job_code INT NOT NULL,
     ->     store_id INT NOT NULL
     -> ) ENGINE=Myisam DEFAULT CHARSET=utf8
     -> PARTITION BY HASH (YEAR(hired)) (
     ->     PARTITIONS 4
     -> );
     
#这里注意的是 PARTITIONS, 多了一个 s

-> DECLARE x INT;

OK,一切搞定,整个过程50分钟,MMM
failover切换中后outline操作表结构变更以及数据导入,实际downtime不包括修改表结构分区字段的时间,只包括failover切换时间
为30秒

这里要提一下的就是, 如上的例子都是使用的是 Myisam
存储引擎,它默认使用独立表空间,
所以你可以在上面的磁盘空间里看到不同的分区
而 InnoDB 引擎则默认使用共享表空间, 此时就算你对 InnoDB 表进行分区,
你查看下会发现, 它并没有像 Myisam 那么样进行物理上的分区,
所以你需要修改下 Mysql 配置文件:

-> SET x = 1000;

MySQL
Partition,看的官方英文资料,翻译水平有限,有些不翻译成中文了,直接贴英文了。

复制代码 代码如下:

-> WHILE x<= 2000 DO

1 list partition table 

[email protected]:~$
sudo vi /etc/mysql/my.cnf;
 
#添加:
innodb_file_per_table=1
 
#重启 mysql
[email protected]:~$
sudo /etc/init.d/mysql restart

-> insert into
employees(id,fname,lname,hired,separated,job_code,store_id)
values(x,concat(‘firstname’,x),concat(‘ai’,x),’1994-01-01′,’1995-01-01′,10,20);

mysql> CREATE TABLE `eh` (

此时你再对 InooDB 进行分区, 则会有如下效果:

-> SET x = x + 1;

    ->   `id` int(11) NOT NULL,

复制代码 代码如下:

-> END WHILE;

    ->   `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,

8768 Jun  7 22:54 employees.frm
   48 Jun  7 22:54 employees.par
98304 Jun  7 22:54 employees#P#pCentral.ibd
98304 Jun  7 22:54 employees#P#pEast.ibd
98304 Jun  7 22:54 employees#P#pNorth.ibd
98304 Jun  7 22:54 employees#P#pWest.ibd

-> END$$

    ->   `ENTITLEMENT_ID` bigint(20) NOT NULL,

分区管理  
删除分区

Query OK, 0 rows affected (0.00 sec)

    ->   `USER_ID` bigint(20) NOT NULL,

复制代码 代码如下:

mysql> call SampleProc() $$

    ->   `DATE_CREATED` datetime NOT NULL,

mysql> alter table employees drop partition pWest; 

Query OK, 1 row affected (22.55 sec)

    ->   `STATUS` smallint(6) NOT NULL,

新增分区

mysql> delimiter ;

    ->   `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,

复制代码 代码如下:

CREATE TABLE employees (

    ->   `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,

#range添加新分区 
mysql> alter table employees add partition ( partition p4 values less
than (26) ); 
  
#list添加新分区 
mysql> alter table employees add partition( partition pSouth values
in (21, 22, 23) ); 
  
#hash重新分区 
mysql> alter table employees add partition partitions 5; 

id INT NOT NULL,

    ->   `DATE_MODIFIED` datetime NOT NULL,

我们来了解下 Mysql 中的分区技术
(RANGE, LIST, HASH) Mysql 的分区技术与水平分表有点类似,
但是它是在逻辑层进行的水平分表…

fname VARCHAR(30),

    ->   PRIMARY KEY (`DATE_MODIFIED`,`id`)

lname VARCHAR(30),

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

hired DATE NOT NULL DEFAULT ‘1970-01-01’,

    -> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))

separated DATE NOT NULL DEFAULT ‘9999-12-31’,

    -> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,

job_code INT,

    ->  PARTITION m2 VALUES IN (2) ENGINE = InnoDB,

store_id INT

    ->  PARTITION m3 VALUES IN (3) ENGINE = InnoDB,

)

    ->  PARTITION m4 VALUES IN (4) ENGINE = InnoDB,

PARTITION BY LIST(store_id)

    ->  PARTITION m5 VALUES IN (5) ENGINE = InnoDB,

PARTITION pNorth VALUES IN (3,5,6,9,17),

    ->  PARTITION m6 VALUES IN (6) ENGINE = InnoDB,

PARTITION pEast VALUES IN (1,2,10,11,19,20),

    ->  PARTITION m7 VALUES IN (7) ENGINE = InnoDB,

PARTITION pWest VALUES IN (4,12,13,14,18),

    ->  PARTITION m8 VALUES IN (8) ENGINE = InnoDB,

PARTITION pCentral VALUES IN (7,8,15,16)

    ->  PARTITION m9 VALUES IN (9) ENGINE = InnoDB,

);

    ->  PARTITION m10 VALUES IN (10) ENGINE = InnoDB,

CREATE TABLE employees (

    ->  PARTITION m11 VALUES IN (11) ENGINE = InnoDB,

id INT NOT NULL,

    ->  PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;

fname VARCHAR(30),

Query OK, 0 rows affected (0.10 sec)

lname VARCHAR(30),

 

hired DATE NOT NULL DEFAULT ‘1970-01-01’,

2 rang partition table 

separated DATE NOT NULL DEFAULT ‘9999-12-31’,

mysql> CREATE TABLE rcx (

job_code INT,

    ->     a INT,

store_id INT

    ->     b INT,

)

    ->     c CHAR(3),

PARTITION BY HASH(store_id)

    ->     d INT

PARTITIONS 4;

    -> )

CREATE TABLE tk (

    -> PARTITION BY RANGE COLUMNS(a,d,c) (

col1 INT NOT NULL,

    ->     PARTITION p0 VALUES LESS THAN (5,10,’ggg’),

col2 CHAR(5),

    ->     PARTITION p1 VALUES LESS THAN (10,20,’mmmm’),

col3 DATE

    ->     PARTITION p2 VALUES LESS THAN (15,30,’sss’),

)

    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

PARTITION BY LINEAR KEY (col1)

    -> );

PARTITIONS 3;

Query OK, 0 rows affected (0.15 sec)

 

3 create range use less character 

CREATE TABLE employees_by_lname (

标签:, , , , , , , , , , , , , ,

Your Comments

近期评论

    功能


    网站地图xml地图