파티셔닝 - mysql 파티션 select




Mysql 5.5 테이블 파티션 사용자 및 친구 (2)

1) 데이터를 선택하기 위해 항상 (또는 주로) ID 만 사용하는 경우,이 필드를 파티션 조건의 기본으로 사용하는 것이 분명합니다. 숫자이므로 해쉬 함수가 필요 없으며 단순히 범위 파티셔닝을 사용 합니다 . 만들 수있는 파티션 수 (테두리로 선택할 수있는 숫자)는 스스로 찾아야하지만 약 8-10 전에 언급 된 @TJChambers만큼 효율적이어야합니다.

삽입 테스트가 잘못되어 삽입 속도가 느립니다. 무작위성없이 1000000 개의 행을 하나씩 삽입하면됩니다. 유일한 차이점은 분할 된 테이블의 경우 mysql이 추가 시간 인 해시를 계산해야한다는 것입니다. 그러나 당신의 경우와 마찬가지로 ID는 파티셔닝을위한 조건의 기반입니다. 테이블 끝에 새로운 모든 행이 삽입되므로 아무 것도 얻을 수 없습니다.

예를 들어 GPS 현지화가있는 테이블을 lat 및 lon으로 분할 한 경우 예를 들어 각 파티션이 대륙이 다른 경우 삽입의 차이를 볼 수 있습니다. 그리고 무작위 (실제) 데이터가있는 테이블이 있고 선형이 아닌 임의의 값을 삽입하는 경우 차이가 발생합니다.

파티션 된 테이블에 대한 선택이 다시 느리기 때문에 다시 선택하십시오.

@TJChambers가 그것에 대해 저에게 편지를 썼습니다. 쿼리가 모든 파티션에서 작동해야합니다 (많은 테이블 작업과 같음). 그래서 시간이 연장되었습니다. 차이를 확인하기 위해 하나의 파티션의 데이터로 작업 할 위치를 사용해보십시오.

예를 들면 :

select count(*) from user_partition where id<99999;

select count(*) from user where id<99999;

당신은 차이를 볼 수 있습니다.

2) 이것은 어렵다. 데이터를 중복하지 않고 파티션을 나눌 방법이 없습니다. 적어도 내 생각에 떠오르는 아이디어는 없지만 액세스 시간 (속도 선택)이 가장 중요한 경우 가장 좋은 방법은 사용자 테이블과 동일한 방식으로 파티션을 분할하는 것입니다 (range on id 중 하나)를 만들고 각 관계에 대해 (a, b) 및 (b, a) 인 2 행을 삽입하십시오. 행 수를 두 배로 늘리지 만 4 개 이상의 파트로 분할하면 어쨌든 쿼리 당 레코드 수가 적어 지거나 또는 필요성을 검사 할 조건이 하나만 생깁니다.

이 스키마로 테스트 해 보았습니다.

CREATE TABLE `test`.`friends` (
`a` INT NOT NULL ,
`b` INT NOT NULL ,
INDEX ( `a` ),
INDEX ( `b` )
) ENGINE = InnoDB;

CREATE TABLE `test`.`friends_part` (
`a` INT NOT NULL ,
`b` INT NOT NULL ,
INDEX ( `a` , `b` )
) ENGINE = InnoDB
PARTITION BY RANGE (a) (
    PARTITION p0 VALUES LESS THAN (1000),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (3000),
    PARTITION p3 VALUES LESS THAN (4000),
    PARTITION p4 VALUES LESS THAN (5000),
    PARTITION p5 VALUES LESS THAN (6000),
    PARTITION p6 VALUES LESS THAN (7000),
    PARTITION p7 VALUES LESS THAN (8000),
    PARTITION p8 VALUES LESS THAN (9000),
    PARTITION p9 VALUES LESS THAN MAXVALUE
);

delimiter //
DROP procedure IF EXISTS fill_friends//
create procedure fill_friends()
begin
    declare i int default 0;
    declare a int;
    declare b int;
    while i<2000000
    do
    set a = rand()*10000;
    set b = rand()*10000;
    insert into friends values(a,b);
    set i = i + 1;
    end while;
end
//
delimiter ;

delimiter //
DROP procedure IF EXISTS fill_friends_part//
create procedure fill_friends_part()
begin
    insert into friends_part (select a,b from friends);
    insert into friends_part (select b as a, a as b from friends);
end
//
delimiter ;

내가 실행 한 쿼리는 다음과 같습니다.

select * from friends where a=317 or b=317;

결과 집합 : 475 회 : 1.43, 0.02, 0.01

select * from friends_part where a=317;

결과 집합 : 475 회 : 0.10, 0.00, 0.00

select * from friends where a=4887 or b=4887;

결과 집합 : 483 회 : 1.33, 0.01, 0.01

select * from friends_part where a=4887;

결과 집합 : 483 회 : 0.06, 0.01, 0.00

나는 데이터의 고유성에 대해 귀찮게하지 않았지만 귀하의 예에서는 고유 한 인덱스를 사용할 수 있습니다. 또한 InnoDB 엔진을 사용했지만 대부분의 쿼리가 선택되어 있고 많은 쓰기를 수행하지 않을 경우 MyISAM이 더 좋습니다. 두 번째 및 세 번째 실행에는 캐싱 때문에 큰 차이가 없지만 첫 번째 실행에는 눈에 띄는 차이가 있습니다. 우리가 데이터베이스 설계의 주요 규칙 중 하나를 깨고 있기 때문에 더 빠르지 만, 결국은 수단을 정당화하여 정말 큰 테이블을위한 좋은 해결책이 될 수 있습니다. 만약 당신이 1M 이하의 기록을 가지고 있다면, 당신은 분할없이 살아남을 수 있다고 생각합니다.

나는 수백만 개의 행을 가진 두 개의 테이블을 가지고 있는데, 선택과 삽입은 점점 느려진다.

나는 spring + hibernate + mysql 5.5를 사용하고 샤딩에 관해서뿐만 아니라 테이블을 파티셔닝하고 내 테이블을 파티셔닝하는 아이디어를 좋아한다.

내 현재 Db 구조는 다음과 같습니다.

CREATE TABLE `user` (
  `id` BIGINT(20) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `location_id` bigint(20) default NULL,
  `updated_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `FK3DC99772C476E06B` (`location_id`),
  CONSTRAINT `FK3DC99772C476E06B` FOREIGN KEY (`location_id`) REFERENCES `places` (`id`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE `friends` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT(20) DEFAULT NULL,
  `friend_id` BIGINT(20) DEFAULT NULL,
  `updated_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_friend` (`user_id`,`friend_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

이제는 파티셔닝을 더 잘 사용하는 방법을 테스트하고 있습니다. 사용자 테이블은 사용법에 따라 좋은 것이라고 생각합니다.

CREATE TABLE `user_partition` (
  `id` BIGINT(20) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `location_id` bigint(20) default NULL,
  `updated_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `FK3DC99772C476E06B` (`location_id`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY HASH(id DIV 100000)
PARTITIONS 30;

두 테이블에 데이터를로드하고 두 테이블의 성능을 검사하는 절차를 만들었습니다.

DELIMITER //
CREATE PROCEDURE load_partition_table()
BEGIN
DECLARE v INT DEFAULT 0;
    WHILE v < 1000000
    DO
    INSERT INTO user_partition (id,NAME,email)
    VALUES (v,CONCAT(v,' name'),CONCAT(v,'@yahoo.com')),
    (v+1,CONCAT(v+1,' name'),CONCAT(v+1,'@yahoo.com')),
    (v+2,CONCAT(v+2,' name'),CONCAT(v+2,'@yahoo.com')),
    (v+3,CONCAT(v+3,' name'),CONCAT(v+3,'@yahoo.com')),
    (v+4,CONCAT(v+4,' name'),CONCAT(v+4,'@yahoo.com')),
    (v+5,CONCAT(v+5,' name'),CONCAT(v+5,'@yahoo.com')),
    (v+6,CONCAT(v+6,' name'),CONCAT(v+6,'@yahoo.com')),
    (v+7,CONCAT(v+7,' name'),CONCAT(v+7,'@yahoo.com')),
    (v+8,CONCAT(v+8,' name'),CONCAT(v+8,'@yahoo.com')),
    (v+9,CONCAT(v+9,' name'),CONCAT(v+9,'@yahoo.com'))
    ;
    SET v = v + 10;
    END WHILE;
    END
    //

CREATE PROCEDURE load_table()
BEGIN
DECLARE v INT DEFAULT 0;
    WHILE v < 1000000
    DO
    INSERT INTO user (id,NAME,email)
    VALUES (v,CONCAT(v,' name'),CONCAT(v,'@yahoo.com')),
    (v+1,CONCAT(v+1,' name'),CONCAT(v+1,'@yahoo.com')),
    (v+2,CONCAT(v+2,' name'),CONCAT(v+2,'@yahoo.com')),
    (v+3,CONCAT(v+3,' name'),CONCAT(v+3,'@yahoo.com')),
    (v+4,CONCAT(v+4,' name'),CONCAT(v+4,'@yahoo.com')),
    (v+5,CONCAT(v+5,' name'),CONCAT(v+5,'@yahoo.com')),
    (v+6,CONCAT(v+6,' name'),CONCAT(v+6,'@yahoo.com')),
    (v+7,CONCAT(v+7,' name'),CONCAT(v+7,'@yahoo.com')),
    (v+8,CONCAT(v+8,' name'),CONCAT(v+8,'@yahoo.com')),
    (v+9,CONCAT(v+9,' name'),CONCAT(v+9,'@yahoo.com'))
    ;
    SET v = v + 10;
    END WHILE;
    END
    //

결과가 놀랍고, 비 파티션 테이블에 삽입 / 선택하면 더 나은 결과를 얻을 수 있습니다.

mysql> select count(*) from user_partition;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.40 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.00 sec)


mysql> call load_table();
Query OK, 10 rows affected (20.31 sec)

mysql> call load_partition_table();
Query OK, 10 rows affected (21.22 sec)

mysql> select * from user where id = 999999;
+--------+-------------+------------------+---------------------+
| id     | name        | email            | updated_time        |
+--------+-------------+------------------+---------------------+
| 999999 | 999999 name | 999999@yahoo.com | 2012-11-27 08:06:54 |
+--------+-------------+------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from user_no_part where id = 999999;
+--------+-------------+------------------+---------------------+
| id     | name        | email            | updated_time        |
+--------+-------------+------------------+---------------------+
| 999999 | 999999 name | 999999@yahoo.com | 2012-11-27 08:03:14 |
+--------+-------------+------------------+---------------------+
1 row in set (0.00 sec)

그래서 두 가지 질문

1) inserts 및 select도 빠르며 location_id FOREIGN KEY를 제거하도록 user 테이블을 분할하는 가장 좋은 방법은 무엇입니까? 파티션 키의 기본에 액세스 할 경우에만 파티션이 좋을 수 있습니다. 제 경우에는 ID로만 테이블을 읽으 려합니다. 파티션 테이블에서 삽입 속도가 느린 이유는 무엇입니까?

2) 모든 친구를 동일한 파티션에 배치하고 항상 user_id를 사용하여 액세스하려는 경우 user_id 를 기준으로 친구를 분할하려면 friend 테이블을 분할하는 가장 좋은 방법은 무엇입니까? friend.id에 기본 키를 놓거나 기본 키에 user_id를 추가해야합니까?


가장 먼저 5.6.5 또는 그 이후 버전의 MySQL로 업그레이드하여 가능하면 파티셔닝을 최적의 성능으로 활용하는 것이 좋습니다. GA 문제로 인해 항상 가능한 것은 아니지만 제 경험은 5.5와 5.6 사이의 성능 차이가 있고 5.6은 다른 유형의 파티션을 제공한다는 것입니다.

1) 내 경험에 따르면 삽입 및 업데이트는 분할 된 집합에서 더 빠르며 쿼리에서 분할되는 열을 포함하는 한 오래 선택합니다. 모든 파티션에서 모든 레코드의 수를 요청하면 느린 응답이 표시됩니다. 파티션이 LIKE 별도의 테이블처럼 작동하기 때문에 예상되는 것입니다. 따라서 30 개의 파티션이있는 경우 하나가 아닌 30 개의 테이블을 읽는 것과 같습니다.

분할하는 값을 기본 키에 포함시켜야하며 레코드 수명 기간 동안 안정적이어야합니다.

2) 기본 키에 user_id와 id를 포함합니다. 친구 테이블 인 user_id와 id가 레코드가 설정되면 (즉, 변경 사항은 삭제 / 삽입 일 것임) 변경되지 않는다고 가정합니다. 제 경우에는 "중복"이었지만 접근 할만한 가치가있었습니다. user_id / id 또는 id / user_id를 선택하는지 여부는 자주 액세스하는 방법에 따라 다릅니다.

최종 메모. 처음에 데이터를 파티션으로 분할하기 시작했을 때 많은 양의 파티션을 만들려고 시도했는데 소수만이 최적의 파티션을 찾은 것으로 나타났습니다. 6-12 개의 파티션이 가장 잘 작동하는 것 같았습니다. YMMV.





database-partitioning