mysql 테이블의 네 가지 파티셔닝 방법은 무엇입니까?

1. 테이블 파티션이란?

mysql 데이터베이스의 데이터는 기본적으로 /mysql/data 아래에 저장됩니다. (my.cnf의 datadir을 통해 볼 수 있습니다.) 하나의 테이블은 주로 세 개의 파일에 해당합니다. 하나는 frm이 테이블 구조를 저장하고, 하나는 myd가 테이블 데이터를 저장하고, 다른 하나는 myi가 테이블 인덱스를 저장하는 것입니다. 테이블의 데이터 양이 너무 많으면 myd와 myi가 매우 커지며, 데이터 검색 속도가 매우 느려지게 됩니다. 이때 mysql의 파티션 기능을 사용하여 이 테이블에 물리적으로 대응할 수 있습니다. 세 개의 파일이 여러 개의 작은 블록으로 나누어져 있으므로, 한 데이터 조각을 검색할 때 해당 데이터가 어느 블록에 있는지만 알고 그 안에서 검색하면 됩니다. 차단하다. 테이블에 많은 양의 데이터가 있는 경우 하나의 디스크가 모든 데이터를 보유할 수 없도록 데이터를 여러 디스크에 분산해야 할 수도 있습니다.

테이블 파티셔닝은 특정 규칙에 따라 데이터베이스의 테이블을 더 작고 관리하기 쉬운 여러 부분으로 분할하는 프로세스입니다. 논리적으로는 테이블이 하나뿐이지만 기본 계층은 여러 개의 물리적 파티션으로 구성됩니다.

2. 테이블 파티셔닝과 테이블 파티셔닝의 차이점

테이블 파티셔닝: 특정 규칙을 통해 테이블을 여러 개의 다른 테이블로 분해하는 것을 말합니다. 예를 들어, 사용자 주문 기록은 시간을 기준으로 여러 테이블로 구분됩니다. 분할과 테이블 샤딩의 차이점은 논리적으로 분할은 하나의 테이블인 반면, 테이블 샤딩은 하나의 테이블을 여러 테이블로 분할한다는 것입니다.

3. 테이블 파티셔닝의 장점은 무엇인가요?

(1) 단일 디스크나 파일 시스템 파티션에 비해 더 많은 데이터를 저장할 수 있습니다.

일반적으로 보존 가치가 없는 데이터는 연결된 파티션을 삭제하면 쉽게 지울 수 있습니다. 때로는 새 데이터를 쉽게 추가하기 위해 이러한 새 데이터를 특별히 저장할 새 파티션을 만들 수 있습니다.

(3) 일부 쿼리는 주어진 WHERE 문을 만족하는 데이터가 하나 이상의 파티션에만 저장될 수 있다는 사실 덕분에 크게 최적화될 수 있습니다. 검색 중입니다. 분할된 테이블이 생성된 후에 분할을 수정할 수 있으므로 처음 분할 구성표를 구성할 때 데이터를 재구성하지 않은 경우 일반적으로 사용되는 쿼리의 효율성을 높이기 위해 데이터를 재구성할 수 있습니다.

쿼리에 SUM() 및 COUNT()와 같은 집계 함수가 포함된 경우 병렬로 쉽게 처리할 수 있습니다. 이러한 쿼리의 간단한 예는 "SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;"입니다. "병렬" 쿼리는 쿼리가 각 파티션에서 동시에 실행될 수 있으며 최종 결과는 단순히 모든 파티션의 결과를 집계하는 것을 의미합니다.

(5) 여러 디스크에 데이터 쿼리를 분산하여 더 큰 쿼리 처리량을 얻습니다.

4. 파티션 테이블의 제한 요소

(1) 테이블은 최대 1024개의 파티션만 가질 수 있습니다.

MySQL 5.1에서 파티션 표현식은 정수 또는 정수를 반환할 수 있는 표현식만 가능합니다. 정수가 아닌 표현식 파티셔닝에 대한 지원은 MySQL 5.5에서 제공됩니다.

파티션 필드에 기본 키 또는 고유 인덱스가 포함된 컬럼이 포함된 경우 기본 키 컬럼 및 고유 인덱스 컬럼이 모두 포함되어야 합니다. 파티션 필드에 기본 키 및 인덱스 열이 포함되지 않거나 모든 기본 키 및 인덱스 열이 포함됩니다.

(4) 분할된 테이블에서는 외래 키 제약 조건을 사용할 수 없습니다.

(5) MySQL 파티셔닝은 테이블의 모든 데이터와 인덱스에 적용됩니다. 테이블 데이터만 파티셔닝할 수 있고 인덱스는 파티셔닝할 수 없으며, 인덱스만 파티셔닝하고 테이블은 파티셔닝할 수 없으며, 데이터의 일부만 파티셔닝할 수 없습니다. 테이블의.

5. MySQL이 현재 파티셔닝을 지원하는지 확인하는 방법은 무엇입니까?

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

have_partintioning 값은 YES이며 파티셔닝이 지원됨을 나타냅니다.

6. MySQL에서 지원하는 파티션 유형은 무엇입니까?

(1), RANGE 파티셔닝: 주어진 연속 간격에 속하는 열 값을 기준으로 여러 행을 파티션에 할당합니다.

(2) LIST 파티셔닝: RANGE별 파티셔닝과 유사하지만, LIST 파티셔닝은 이산형 값 집합에서 특정 값과 일치하는 컬럼 값을 기준으로 선택된다는 차이점이 있습니다.

(3), HASH 파티셔닝: 테이블에 삽입할 행의 컬럼 값을 이용하여 계산한 사용자 정의 표현식의 반환 값을 기준으로 파티션을 선택합니다. 음수가 아닌 정수 값을 생성하는 모든 유효한 MySQL 표현식이 이 함수에 포함될 수 있습니다.

(4) KEY 파티셔닝: HASH 파티셔닝과 유사하지만 KEY 파티셔닝은 하나 이상의 열 계산만 지원하고 MySQL 서버는 자체 해시 함수를 제공한다는 점이 다릅니다. 정수 값을 포함하는 열이 하나 이상 있어야 합니다.

참고: MySQL 버전 5.1에서 RANGE, LIST 및 HASH 파티션에서는 파티션 키가 INT 유형이거나 표현식을 통해 INT 유형을 반환해야 합니다. KEY 파티셔닝 시 BLOB, TEXT 타입 컬럼 외에 다른 타입의 컬럼도 파티션 키로 사용할 수 있다.

6.1. RANGE 파티션

범위에 따른 파티션입니다. 범위는 연속적이어야 하지만 겹치지 않아야 합니다. PARTITION BY RANGE, VALUES LESS THAN 키워드를 사용하세요. COLUMNS 키워드를 사용하지 않는 경우 RANGE 대괄호는 정수 필드 이름이거나 명확한 정수를 반환하는 함수여야 합니다.

6.1.1, 수치 범위에 따름

drop table if exists employees;
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 default 0,
    store_id int not null default 0
)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)
);

insert into employees (id,fname,lname,hired,store_id) values(1,'张三','张','2015-05-04',1);
insert into employees (id,fname,lname,hired,store_id) values(2,'李四','李','2016-10-01',5);
insert into employees (id,fname,lname,hired,store_id) values(3,'王五','王','2016-11-14',10);
insert into employees (id,fname,lname,hired,store_id) values(4,'赵六','赵','2017-08-24',15);
insert into employees (id,fname,lname,hired,store_id) values(5,'田七','田','2018-05-20',20);

按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求。

对于包含数据(6,'亢八','亢','2018-06-24',13)的一个新行，可以很容易地确定它将插入到p2分区中。

insert into employees (id,fname,lname,hired,store_id) values(6,'亢八','亢','2018-06-24',13);

但是如果增加了一个编号为第21的商店(7,'周九','周','2018-07-24',21)，将会发生什么呢？在这种方案下，由于没有规则把store_id大于20的商店包含在内，服务器将不知道把该行保存在何处，将会导致错误。

insert into employees (id,fname,lname,hired,store_id) values(7,'周九','周','2018-07-24',21);
 
ERROR 1526 (HY000): Table has no partition for value 21

要避免这种错误，可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句，该子句提供给所有大于明确指定的最高值的值：

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 default 0,
    store_id int not null default 0
)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),
  partition p4 values less than MAXVALUE 
);

6.1.2、根据TIMESTAMP范围

drop table if exists quarterly_report_status;
create table quarterly_report_status(
  report_id int not null,
  report_status varchar(20) not null,
  report_updated timestamp not null default current_timestamp on update current_timestamp
)
partition by range(unix_timestamp(report_updated))(
  partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
  partition p1 values less than (unix_timestamp('2008-04-01 00:00:00')),
  partition p2 values less than (unix_timestamp('2008-07-01 00:00:00')),
  partition p3 values less than (unix_timestamp('2008-10-01 00:00:00')),
  partition p4 values less than (unix_timestamp('2009-01-01 00:00:00')),
  partition p5 values less than (unix_timestamp('2009-04-01 00:00:00')),
  partition p6 values less than (unix_timestamp('2009-07-01 00:00:00')),
  partition p7 values less than (unix_timestamp('2009-10-01 00:00:00')),
  partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')),
  partition p9 values less than maxvalue
);

6.1.3、根据DATE、DATETIME范围

添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势：

drop table if exists member;
create table member(
  firstname varchar(25) not null,
  lastname varchar(25) not null,
  username varchar(16) not null,
  email varchar(35),
  joined date not null
)
partition by range columns(joined)(
  partition p0 values less than ('1960-01-01'),
  partition p1 values less than ('1970-01-01'),
  partition p2 values less than ('1980-01-01'),
  partition p3 values less than ('1990-01-01'),
  partition p4 values less than maxvalue
)

6.1.4、根据多列范围

drop table if exists rc3;
create table rc3(
  a int,
  b int
)
partition by range columns(a,b)(
  partition p0 values less than (0,10),
  partition p1 values less than (10,20),
  partition p2 values less than (20,30),
  partition p3 values less than (30,40),
  partition p4 values less than (40,50),
  partition p5 values less than (maxvalue,maxvalue)
)

6.1.5、RANGE分区在如下场合特别有用

drop table if exists staff;
create table staff(
  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 default 0,
  store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(year(separated))(
  partition p0 values less than (1991),
  partition p1 values less than (1996),
  partition p2 values less than (2001),
  partition p4 values less than MAXVALUE
);

只需删除分区，就能清除“旧的”数据。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用”alter table staff drop partition p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如”delete from staff WHERE year(separated) <= 1990;”这样的一个DELETE查询要有效得多。

（2）、想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。

（3）、经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如”select count(*) from staff where year(separated) = 200 group by store_id;”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

6.2、LIST分区

根据具体数值分区，每个分区数值不重叠，使用PARTITION BY LIST、VALUES IN关键字。在不使用COLUMNS关键字的情况下，与Range分区类似，List括号内必须是整数字段名或返回确定整数的函数。

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

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

====================

地区 商店ID 号

北区 3, 5, 6, 9, 17

东区 1, 2, 10, 11, 19, 20

4, 12, 13, 14, 18是西区的编号

中心区 7, 8, 15, 16

drop table if exists staff;
create table staff(
  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 default 0,
  store_id int not null default 0
)
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)
);

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE staff DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE（删除）“DELETE FROM staff WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。

如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。

当插入多条数据出错时，如果表的引擎支持事务（Innodb），则不会插入任何数据；如果不支持事务，则出错前的数据会插入，后面的不会执行。

与Range分区相同，添加COLUMNS关键字可支持非整数和多列。

6.3、HASH分区

Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。

要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1

drop table if exists staff;
create table staff(
  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 default 0,
  store_id int not null default 0
)
partition by hash(store_id)
partitions 4;

drop table if exists staff;
create table staff(
  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 default 0,
  store_id int not null default 0
)
partition by hash(year(hired))
partitions 4;

Hash分区也存在与传统Hash分表一样的问题，可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法－线性Hash分区，只需要在定义分区时添加LINEAR关键字。

drop table if exists staff;
create table staff(
  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 default 0,
  store_id int not null default 0
)
partition by linear hash(year(hired))
partitions 4;

线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。

6.4、KEY分区

Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。

drop table if exists staff;
create table staff(
  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 default 0,
  store_id int not null default 0
)
partition by key(store_id)
partitions 4;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

另外，当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。

위 내용은 mysql 테이블의 네 가지 파티셔닝 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!