MySQL을 사용하여 100GB 이상의 테이블에서 멀티 웨이 조인을 정기적으로 수행합니까? 데이터 색인화 된

배경 :
합리적으로 확장 할 수있는 웹 응용 프로그램을 만들었습니다. Google 또는 Twitter는 아니지만 내 앱은 각 사용자마다 상당히 많은 양의 데이터를 사용하므로 데이터 요구 사항이 상당히 높습니다. 나중에 모든 것을 다시 설계하지 않고도 합리적으로 확장 할 준비가 되었으면합니다.

나는 데이터베이스 전문가가 아닌 소프트웨어 개발자라고 생각한다. 내가 여기에 게시하는 이유입니다. 더 많은 데이터베이스 전문 지식을 가진 사람이 나에게 조언을 줄 수 있기를 바랍니다.

비교적 많은 수의 사용자가 있지만 Facebook 번호와 같은 것은 없지만 다음과 같은 DB가 있어야합니다.

하나의 “큰 테이블”:

  • 2 억 5 천만 레코드
  • 20 열
  • 약 100GB의 데이터
  • 색인화 된 bigint (20) 외래 키가 있습니다
  • 색인화 된 varchar (500) string_id 열이 있습니다
  • int (11) “value”열이 있습니다

다른 4 개의 테이블 :

  • 각 천만 레코드
  • 각각 약 2-4GB의 데이터
  • 이 각 테이블에는 4-8 개의 열이 있습니다.
  • 하나의 열은 datetime date_created입니다.
  • 하나의 열은 varchar (500) string_id 열입니다.
  • 각 테이블에서 하나 또는 두 개의 열이 조인에서 선택됩니다

이 테이블 중 하나는 평균 저장에 사용됩니다. 스키마는 bigint (20) id, varchar (20) string_id, datetime date_created, float average_value입니다.

내가하고 싶은 것 -비교적 비싼 두 쿼리 :

  1. 새로운 평균값을 계산하십시오.

    • 외래 키를 사용하여 큰 테이블에서 최대 수백만 개의 개별 레코드를 선택하십시오.
    • string_id로 그룹화하여 새 평균을 계산하십시오.
    • 평균 테이블에 결과를 삽입하십시오.
    • 현재 구성된대로이 쿼리는 두 개의 조인을 사용합니다.
  2. 사용자에게 서비스를 제공하기 위해 비정규 화 된 읽기 전용 레코드를 만듭니다.

    • 외래 키를 사용하여 큰 테이블에서 1,000-40,000 개의 레코드를 선택하십시오.
    • 문자열 ID 열을 사용하여 최신 레코드에서 다른 네 개의 테이블 각각과 결합하십시오.
    • 비정규 화 된 테이블에 결과를 삽입하십시오.
    • 이 레코드는 프런트 엔드에서 사용자에게 정보를 표시하는 데 사용됩니다.
    • 현재 구성된대로이 쿼리는 4 개의 조인을 사용합니다.

고가의 각 쿼리를 배치 백엔드 데이터베이스에서 실행하여 결과를 사용자의 요청을 처리하는 실시간 프론트 엔드 DB 서버로 푸시 할 계획입니다. 이러한 쿼리는 정기적으로 실행됩니다. 나는 얼마나 자주 결정하지 않았다. 평균 쿼리는 하루에 한 번 수행 될 수 있습니다. 비정규 화 쿼리는 아마도 몇 분마다 더 빈번해야합니다.

이러한 각 쿼리는 현재 “큰 테이블”에 100K 레코드의 데이터 세트가있는 초저가 시스템의 MySQL에서 몇 초 안에 실행됩니다. 스케일링 능력과 스케일링 비용이 모두 걱정됩니다.

질문 :

  1. 이 접근법이 건전 해 보입니까? 큰 그림으로 볼 때 분명히 문제가 있습니까?
  2. RDBMS가 올바른 도구입니까, 아니면 하둡 제품군과 같은 다른 “빅 데이터”솔루션을 봐야합니까? 데이터가 구조화되어 관계형 모델에 잘 맞기 때문에 RDBMS를 사용하는 경향이 있습니다. 그러나 특정 시점에서 더 이상 RDBMS를 사용할 수 없다는 것을 이해하고 있습니다. 그게 사실입니까? 이 스위치는 언제 필요할까요?
  3. 작동합니까? 이러한 쿼리를 적절한 시간 내에 실행할 수 있습니까? 쿼리 # 1을 기다리는 데 몇 시간이 걸릴 수 있지만 쿼리 # 2는 몇 분 안에 완료됩니다.
  4. 하드웨어 관점에서 무엇을 고려해야합니까? 내 RAM 및 CPU 병목 현상은 무엇입니까? RAM에 인덱스를 유지하는 것이 중요하다고 가정합니다. 고려해야 할 다른 것이 있습니까?
  5. 언젠가 데이터를 분할하고 여러 서버를 사용해야 할 것입니다. 내 유스 케이스가 이미 해당 카테고리에있는 것처럼 보입니까, 아니면 단일 머신을 수직으로 수직 확장 할 수 있습니까? 이것은 10 배의 데이터로 작동합니까? 100 배?


답변

더 많은 데이터를 정리하고 벤치마킹 해 보셨습니까? 100K 행은 중요하지 않습니다. 병목 현상의 위치를 ​​처리하고 확인해야하는 것처럼 250M 또는 500M을 사용해보십시오.

RDBMS는 제한 사항에주의를 기울이고 시스템의 강점을 사용하여 작업 할 경우 많은 작업을 수행 할 수 있습니다. 그것들은 어떤 것들에는 예외적으로 훌륭하고 다른 것들에는 끔찍하기 때문에 실험에 꼭 맞아야합니다.

일부 일괄 처리 작업의 경우 플랫 파일을 이길 수 없으며 데이터를 RAM에로드하고 일련의 루프와 임시 변수를 사용하여 스매싱하고 결과를 덤프 할 수 없습니다. MySQL은 결코 이런 종류의 속도와 일치 할 수는 없지만 제대로 조정하여 올바르게 사용하면 몇 배 안에 들어올 수 있습니다.

데이터를 어떻게 분할 할 수 있는지 조사해야합니다. 교차 링크 방식으로 너무 많은 데이터를 분할 할 수있는 하나의 큰 데이터 세트가 있습니까? 분할 할 수 있으면 전체 행 더미가있는 하나의 테이블이 없지만 잠재적으로 훨씬 작은 테이블이 있습니다. 인덱스가 훨씬 작은 작은 테이블은 성능이 더 좋은 경향이 있습니다.

하드웨어 관점에서 플랫폼의 성능을 테스트해야합니다. 때로는 메모리가 필수적입니다. 다른 경우에는 디스크 I / O입니다. 실제로 데이터로 무엇을하고 있는지에 달려 있습니다. CPU 사용에 세심한주의를 기울여야하고 문제의 위치를 ​​알기 위해 높은 수준의 IO 대기 시간을 찾아야합니다.

가능하면 데이터를 여러 시스템으로 분할하십시오. 용감하다고 느끼는 경우 MySQL 클러스터를 사용하거나 각각 독립적 인 MySQL 인스턴스를 여러 개 스핀하면 각 인스턴스가 의미있는 파티션 구성표를 사용하여 전체 데이터 세트의 임의 부분을 저장합니다.


답변

요약 테이블.

매일 해당 날짜의 데이터에 대한 집계 정보를 계산하십시오. 이것을 “요약”테이블에 넣으십시오. 그들에 대한 질문을하십시오. 10 배 빠른 속도.

더 자세한 논의를 위해

  • 테이블 만들기 (현재 상태)
  • 테이블 크기 (당신이 언급 한)
  • 제안 된 선택

명백한 것들 …

  • BIGINT는 거의 보증되지 않습니다. 8 바이트가 걸립니다. INT UNSIGNED는 4를 취하고 0..4billion의 값을 허용합니다. 그리고 MEDIUMINT 등이 있습니다
  • ‘fact’테이블의 여러 인덱스는 일반적으로 특히 INSERT의 경우 심각한 성능 문제입니다. 거기에 문제가 있습니까?
  • DATETIME은 8 바이트입니다. 타임 스탬프는 4
  • 명시 적 외래 키 제약 조건은 훌륭하지만 비용이 많이 듭니다.
  • JOIN은 성능 문제 일 수도 있고 아닐 수도 있습니다. SELECT와 CREATE를 볼 필요가 있습니다.
  • 100GB는 ‘큰’MySQL 데이터베이스에 적합한 크기입니다. 나는 Hadoop 등없이 작동 할 수 있다고 생각합니다. 지금은 하나의 db를 처리합니다. 데이터가 상당히 관련되어 있지만 대부분의 UI 페이지는 1 초 안에 응답합니다.
  • 어느 시점에서 데이터를 ‘퍼지’하시겠습니까? (이는 PARTITIONing의 주요 사용 사례로 이어집니다.)

“작게-> 더 캐시 가능-> 더 빠름


답변

프런트 엔드 데이터를 제공하기 위해 항상 인서트의 gob 및 gob가 없다면, 트리거를 사용하여 백엔드와 동기화되지만 데이터를 제공하도록 최적화 된 구체화 된 뷰에 삽입 할 수는 없습니다. 물론 이러한 트리거에서 조인 등을 최소로 유지해야합니다. 내가 사용한 전략 중 하나는 이러한 삽입 / 업데이트를 중간 테이블에 큐에 넣은 다음 1 분마다 전송하는 것입니다. 4GB의 레코드보다 하나의 레코드를 보내는 것이 훨씬 쉽습니다. 원하는 레코드를 빠르게 찾을 수 있어도 4GB의 데이터를 스트리밍하는 데 시간이 오래 걸립니다.

나는 태드 먼에 동의합니다. 가장 좋은 방법은 원하는 종류의 시스템에서 예상되는 종류의 데이터로 프로파일 링하는 것입니다.


답변