MySQL 성능: 단일 테이블 및 파티션에서 여러 개의 테이블 대 인덱스

MySQL 성능: 단일 테이블 및 파티션에서 여러 개의 테이블 대 인덱스

성능 면에서 더 효율적이고 빠른 것은 무엇인지 궁금합니다.
하나의 큰 테이블에 인덱스가 있거나 인덱스가 없는 여러 개의 작은 테이블에 인덱스가 있습니까?

이것은 꽤 추상적인 문제이므로 좀 더 실용적으로 만들어 보겠습니다.
저는 사용자에 대한 통계(사용자 2만 명, 전체 행 수 약 3천만 개)가 있는 표를 하나 가지고 있습니다.에는 다를 .user_id,actions,timestamps.
가장 일반적인 응용프로그램은 다음과 같습니다.user_id(user_id합니다())SELECT다가 여러 개되지 않습니다.user_id's).

까지 저는 를 있습니다.INDEXuser_id 쿼리는 다와 것으로 보입니다.

SELECT * FROM statistics WHERE user_id = 1

이제 행이 많아질수록 테이블은 점점 더 느려집니다.INSERT 때문에 .INDEX 더 ;다.SELECT검색할 행이 더 많기 때문에 문이 느려집니다.

이제 각 사용자에 대해 통계 테이블을 하나씩 가지고 쿼리 구문을 이와 같은 방식으로 변경하는 것이 어떨까 생각했습니다.

SELECT * FROM statistics_1

1합니다를 .user_id물론. 뻔하지.
이쪽으로 안 돼요INDEX하고 각 다.INSERT그리고.SELECT진술이 훨씬 빨라야 합니다.

, 한 번 제 , 합니다입니다.
(에는 20,000 ) 로 하나의 하는 대신 많은 을 하는 데 한 점이 ?INDEX?
제 접근 방식이 실제로 속도를 높이는 것일까요, 아니면 테이블을 검색하는 것이 결국 모든 것보다 더 속도를 늦출 수 있을까요?

20,000개의 테이블을 만드는 것은 좋지 않은 생각입니다.조만간 4만 개의 테이블이 필요할 것이고, 그 다음에는 더 많은 테이블이 필요할 것입니다.

저는 제 책 SQL Antipatterns Volume 1에서 이 신드롬을 메타데이터 트라이블이라고 불렀습니다."X 단위 테이블" 또는 "X 단위 열"을 작성할 때마다 이 문제가 발생합니다.

이로 인해 수만 개의 테이블이 있을 때 실제 성능 문제가 발생합니다.각 테이블은 내부 데이터 구조, 파일 설명자, 데이터 사전 등을 유지하기 위해 MySQL을 필요로 합니다.

또한 실질적인 운영상의 결과도 있습니다.새로운 사용자가 가입할 때마다 새 테이블을 만들어야 하는 시스템을 정말로 만들고 싶으십니까?

대신 MySQL Partitioning을 사용하는 것이 좋습니다.

테이블을 분할하는 예는 다음과 같습니다.

CREATE TABLE statistics (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 101;

이를 통해 하나의 논리 테이블을 정의하는 동시에 파티션 키의 특정 값을 쿼리할 때 보다 신속하게 액세스할 수 있도록 테이블을 여러 물리적 테이블로 분할할 수 있습니다.

예를 들어 예제와 같은 쿼리를 실행하면 MySQL은 특정 user_id가 포함된 올바른 파티션에만 액세스합니다.

mysql> EXPLAIN PARTITIONS SELECT * FROM statistics WHERE user_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: statistics
   partitions: p1    <--- this shows it touches only one partition 
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
        Extra: Using where; Using index

분할의 해시 방법은 정수 분할 키의 모듈러스에 의해 행이 분할된 상태에 놓이는 것을 의미합니다.이는 많은 user_id의 맵이 동일한 파티션에 있지만 각 파티션의 행 수는 평균 N분의 1에 불과하다는 것을 의미합니다(여기서 N은 파티션 개수).또한 일정한 수의 파티션으로 테이블을 정의하므로 새 사용자가 생길 때마다 테이블을 확장할 필요가 없습니다.

최대 1024개(MySQL 5.6의 경우 8192개)의 파티션을 선택할 수 있지만, 성능 문제가 발생했다고 보고한 사람도 있습니다.

소수의 파티션을 사용하는 것이 좋습니다.user_id 값이 짝수만 사용하는 것과 같이 패턴을 따르는 경우 소수의 파티션을 사용하면 데이터를 보다 균등하게 분배하는 데 도움이 됩니다.

---

질문을 댓글로 다시 작성합니다.

공명 가능한 수의 파티션을 결정하려면 어떻게 해야 합니까?

해시 파티셔닝의 경우 위의 예제에서 보여준 것처럼 101개의 파티션을 사용할 경우 주어진 파티션의 행 수는 평균 1% 정도입니다.통계 테이블에 3천만 개의 행이 있다고 하셨기 때문에 이 파티션을 사용하면 파티션당 300k 개의 행만 가질 수 있습니다.MySQL을 읽는 것이 훨씬 더 쉽습니다.인덱스를 사용할 수도 있고 사용해야 합니다. 각 파티션에는 고유한 인덱스가 있으며 분할되지 않은 전체 테이블의 인덱스보다 1%만 클 뿐입니다.

따라서 합리적인 수의 파티션을 결정할 수 있는 방법에 대한 답은 전체 테이블의 크기와 파티션의 평균 크기입니다.

시간이 지날수록 파티션의 양이 늘어나야 하지 않을까요?그렇다면 어떻게 자동화할 수 있습니까?

해시 파티셔닝을 사용하는 경우 파티션 수가 증가할 필요는 없습니다.최종적으로 총 300억 개의 행이 생성될 수도 있지만, 데이터 용량이 몇 배씩 증가할 경우 새로운 아키텍처가 필요하다는 사실을 알게 되었습니다.데이터가 이렇게 커질 경우 여러 서버를 공유하고 여러 테이블로 분할해야 할 수도 있습니다.

즉, ALTER TAB을 사용하여 테이블을 다시 분할할 수 있습니다.LE:

ALTER TABLE statistics PARTITION BY HASH(user_id) PARTITIONS 401;

대부분의 ALTER TABLE 변경 사항처럼 테이블을 재구성해야 하므로 시간이 오래 걸릴 것으로 예상됩니다.

파티션의 데이터 및 인덱스 크기를 모니터링할 수 있습니다.

SELECT table_schema, table_name, table_rows, data_length, index_length
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE partition_method IS NOT NULL;

다른 테이블과 마찬가지로 SELECT 쿼리 중 MySQL이 버퍼 풀 내 인덱스 일부를 스왑해야 하는 경우 성능이 저하되기 때문에 활성 인덱스의 전체 크기가 버퍼 풀에 맞도록 할 수 있습니다.

RANGE 파티션 또는 LIST 파티션을 사용하는 경우 파티션 추가, 삭제, 병합 및 분할이 훨씬 일반적입니다.http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-range-list.html 참조

파티셔닝에 대한 설명서 섹션을 읽고 이 멋진 프레젠테이션을 확인하시기 바랍니다.MySQL 5.1 파티션으로 성능 향상

이는 자주 문의할 계획인 유형에 따라 다를 수 있으며, 가장 좋은 방법은 두 가지 모두의 프로토타입을 구현하고 성능 테스트를 수행하는 것입니다.

그렇기 때문에, 대부분의 DBMS 시스템은 데이터를 찾아서 큰 테이블에 삽입하는 정확한 상황을 처리하기 위해 매우 최적화되어 있기 때문에 인덱스가 있는 하나의 (큰) 테이블이 전체적으로 더 나은 결과를 얻을 것으로 기대합니다.성능 향상을 기대하며 작은 테이블을 많이 만들려고 한다면, 대개는 더 나은 옵티마이저(Optimizer.

또한, 한 테이블이 미래를 위해 더 실용적일 수도 있다는 것을 명심하세요.모든 사용자에 대한 집계 통계를 얻으려면 어떻게 해야 합니까?20,000개의 테이블을 갖추면 실행하기가 매우 어렵고 비효율적입니다.이러한 스키마의 유연성도 고려해 볼만합니다.테이블을 그렇게 분할하면 미래를 위한 코너로 자신을 설계하는 것일 수도 있습니다.

구체적인 예:

저는 사용자에 대한 통계(사용자 2만 명, 전체 행 수 약 3천만 개)가 있는 표를 하나 가지고 있습니다.테이블에는 user_id, 작업, 타임스탬프 등 10개 정도의 열이 있습니다.가장 일반적인 응용프로그램은 다음과 같습니다.user_id로 데이터를 삽입하고 user_id로 데이터를 검색합니다(SELECT 문에는 여러 user_id가 포함되지 않음).

작업:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 ...
PRIMARY KEY(user_id, id),
INDEX(id)

있는 것.user_idPK의 시작 부분에서 "참조의 로컬리티"를 제공합니다.즉, 하나의 사용자에 대한 모든 행이 함께 클러스터링되어 I/O를 최소화할 수 있습니다.

idPK 끝에는 PK가 고유해야 하기 때문입니다.

하게 생긴 .INDEX(id)보관하는 것입니다AUTO_INCREMENT해.

추상적 질문:

• 동일한 표를 여러 개 가져서는 안 됩니다.
• 사용하다PARTITIONinghttp://mysql.rjweb.org/doc.php/partitionmaint 에 나열된 사용 사례 중 하나를 충족하는 경우에만 해당됩니다.
• A PARTITIONed테이블에는 partition가 아닌 동등한 테이블과는 다른 인덱스 집합이 필요합니다.
• 대부분의 경우 분할되지 않은 단일 테이블이 최적입니다.
• 쿼리를 사용하여 인덱스를 설계합니다.

빌 카윈스의 답변에 추가할 것은 거의 없습니다.그러나 한 가지 힌트는 사용자를 위한 모든 데이터가 항상 완벽하게 상세하게 필요한지 확인하라는 것입니다.

사용 통계, 방문 횟수 등을 제공하려면 일반적으로 현재 시점에서 2009년과 같은 연도에 대해 단일 작업과 초 단위의 세부 정보를 얻을 수 없습니다.따라서 집계 테이블과 아카이브 테이블(물론 엔진 아카이브는 아님)을 구축하여 작업 기반의 최신 데이터와 이전 작업에 대한 개요를 파악할 수 있습니다.

오래된 행동은 변하지 않는다고 생각합니다.

예를 들어 아카이브 테이블에 week_id가 있는 집계에서 자세히 살펴볼 수도 있습니다.

사용자당 1개의 테이블에서 1개의 테이블로 이동하는 대신 파티셔닝을 사용하여 중간에 있는 여러 테이블/테이블 크기 비율을 맞출 수 있습니다.

또한 사용자에 대한 통계를 유지하여 '활성' 사용자를 하나의 테이블로 이동시켜 시간이 지남에 따라 액세스해야 하는 테이블 수를 줄일 수 있습니다.

결론적으로, 할 수 있는 것은 많지만, 크게는 프로토타입과 테스트를 구축하고 다양한 변경 사항의 성능 영향을 평가해야 합니다.

언급URL : https://stackoverflow.com/questions/16721772/mysql-performance-multiple-tables-vs-index-on-single-table-and-partitions