mysql에서 테이블을 분할해야 하는 경우

mysql에서 테이블 분할에 적합한 상황: 1. 데이터 양이 너무 많고 정상적인 운영 및 유지 관리가 비즈니스 액세스에 영향을 미치는 경우, 예를 들어 데이터베이스 백업에는 많은 양의 디스크 IO 및 네트워크 IO가 필요하고 DDL 수정이 필요합니다. 2. 비즈니스가 발전함에 따라 일부 필드를 수직으로 분할해야 합니다. 3. 단일 테이블의 데이터 양이 빠르게 증가하고 성능이 가까워지면 병목 현상, 수평 분할을 고려해야 합니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

모든 테이블을 분할할 필요는 없으며 주로 데이터 증가 속도에 따라 달라집니다. 세분화는 데이터 저장 및 쿼리를 수행하는 것 외에도 비즈니스의 요구 사항을 더 잘 실현하는 데 도움이 되는 중요한 작업 중 하나입니다.

"과도한 설계" 및 "조기 최적화"를 피하기 위해 꼭 필요한 경우가 아니면 하위 데이터베이스 및 하위 테이블의 트릭을 사용하지 마세요. 데이터베이스와 테이블을 분할하기 전에 단지 분할만을 위해 분할하지 마십시오. 하드웨어 업그레이드, 네트워크 업그레이드, 읽기 및 쓰기 분리, 인덱스 최적화 등 먼저 할 수 있는 일을 최선을 다해 수행하십시오. 데이터 양이 단일 테이블의 병목 현상에 도달하면 데이터베이스와 테이블을 샤딩하는 것을 고려하세요.

그럼 MySQL에서는 언제 하위 테이블을 고려해야 할까요?

1. 데이터 양이 너무 많아 정상적인 운영 및 유지 관리가 비즈니스 액세스에 영향을 미칩니다.

여기에 언급된 운영 및 유지 관리는 다음을 의미합니다.

• 단일 테이블이 너무 크고 백업 중에 많은 양의 디스크 IO 및 네트워크 IO가 필요한 경우 데이터베이스를 백업하십시오. 예를 들어, 1T의 데이터가 네트워크를 통해 전송되고 50MB를 차지하면 완료하는 데 20,000초가 소요됩니다. 전체 프로세스의 위험은 상대적으로 높습니다.

• 대형 테이블에 DDL 수정이 이루어지면 MySQL은 잠깁니다. 이 기간 동안 비즈니스는 이 테이블에 액세스할 수 없으며 이는 큰 영향을 미칩니다. pt-online-schema-change를 사용하면 사용 중에 트리거와 섀도우 테이블이 생성되는데 이 역시 시간이 오래 걸린다. 이 작업 중에는 위험 시간으로 계산됩니다. 데이터 테이블을 분할하고 총 금액을 줄이면 이러한 위험을 줄이는 데 도움이 될 수 있습니다.

• 대형 테이블은 자주 액세스하고 업데이트되므로 잠금 대기가 발생할 가능성이 더 높습니다. Disguise에서 데이터 분할, 공간 교환 및 액세스 부담 감소

2. 비즈니스가 발전함에 따라 일부 필드를 수직으로 분할해야 합니다

예를 들어, 사용자 테이블이 처음에 디자인된 경우 프로젝트는 다음과 같습니다:

프로젝트 초기 단계에서 이 디자인은 단순한 비즈니스 요구 사항을 충족하고 빠른 반복 개발을 촉진합니다. 사업이 급속히 발전하면 사용자 수가 10만 명에서 10억 명으로 급증하고, 사용자가 로그인할 때마다 last_login_name 필드가 업데이트되어 사용자 테이블이 지속적으로 업데이트되므로 매우 스트레스를 받습니다. 기타 필드: id, name, personal_info는 변경되지 않거나 거의 업데이트되지 않습니다. 비즈니스 관점에서 last_login_time을 분할하고 새 user_time 테이블을 생성해야 합니다.

personal_info 속성은 업데이트 및 쿼리 빈도가 줄어들고 텍스트 필드가 너무 많은 공간을 차지합니다. 이때 user_ext 테이블을 수직으로 분할할 필요가 있다.

3. 데이터 양이 빠르게 증가하고 있습니다

비즈니스의 급속한 발전과 함께 단일 테이블의 데이터 양은 계속해서 증가할 것입니다. 성능이 병목 현상에 가까워지면 수평 샤딩을 고려하여 생성해야 합니다. 데이터베이스와 테이블을 분리합니다. 이때 적절한 세분화 규칙을 선택하고 데이터 용량을 미리 예측해야 합니다

비즈니스 사례 분석

1. 사용자 센터 비즈니스 시나리오

사용자 센터는 주로 사용자에게 서비스를 제공하는 매우 일반적인 비즈니스입니다. with 등록, 로그인, 쿼리/수정 등과 같은 기능의 핵심 테이블은 다음과 같습니다.

비즈니스와 분리된 모든 아키텍처 설계는 하위 데이터베이스 및 테이블 하위 데이터베이스 이전에는 불량입니다. 비즈니스 시나리오 요구 사항을 정리해야 합니다.

• 사용자 측: 방문 횟수가 많은 프런트 엔드 액세스는 고가용성과 높은 일관성을 보장해야 합니다. 요구 사항에는 주로 두 가지 유형이 있습니다.

• • 사용자 로그인: login_name/phone/email을 통해 사용자 정보를 쿼리합니다. 요청의 1%가 이 유형에 속합니다.

  • 사용자 정보 쿼리: 로그인 후 쿼리 사용자가 uid 정보를 통해 요청의 99%가 이 유형입니다

• 운영 측면: 백그라운드 액세스, 운영 요구 사항 지원, 연령, 성별, 로그인 시간, 등록 시간 등에 따라 페이징 쿼리를 수행합니다. 이는 액세스량이 적고 가용성 및 일관성에 대한 요구 사항이 낮은 내부 시스템입니다.

2. 수평 분할 방법

데이터의 양이 점점 많아질 경우 데이터베이스를 수평으로 분할해야 합니다. 위에서 설명한 분할 방법에는 "수치 모듈로 기준"이 있습니다. ".

"숫자 범위 기준": 기본 키 uid를 기준으로 uid 범위에 따라 데이터를 여러 데이터베이스로 수평으로 나눕니다. 예를 들어 user-db1은 uid 범위가 0~1000w인 데이터를 저장하고 user-db2는 uid 범위가 1000w~2000wuid인 데이터를 저장합니다.

• 장점은 확장이 간단하고, 용량이 부족하면 DB를 새로 추가하면 됩니다.

• 단점은 요청량이 고르지 않다는 것입니다. 일반적으로 새로 등록된 사용자가 더 활동적이므로 새로운 user-db2는 user-db1보다 로드가 높아져 서버 활용도가 불균형해집니다

" 수치 모듈로에 따르면 ": 기본 키 uid도 분할 기준으로 사용되며 데이터는 uid의 모듈로 값을 기준으로 수평으로 여러 데이터베이스로 분할됩니다. 예를 들어, user-db1은 uid 데이터 모듈로 1을 저장하고, user-db2는 uid 데이터 모듈로 0을 저장합니다.

• 장점은 데이터량과 요청량이 고르게 분포되어 있다는 점

• 단점은 용량이 부족할 경우 새로운 DB를 추가하려면 재해시가 필요하다는 점이다. 데이터의 원활한 마이그레이션을 고려해야 합니다.

Non-uid 쿼리 방법

수평 분할 후 uid별 쿼리 요구를 잘 충족할 수 있으며 특정 데이터베이스로 직접 라우팅될 수 있습니다. login_name과 같이 UID가 아닌 쿼리의 경우 어떤 라이브러리에 액세스해야 하는지 알 수 없으며, 이 경우 모든 라이브러리를 순회해야 하므로 성능이 많이 저하됩니다.

사용자 측에서는 "uid가 아닌 속성에서 uid로의 매핑 관계 설정" 솔루션을 채택할 수 있고, 운영 측에서는 "프런트엔드와 백엔드 분리" 솔루션을 채택할 수 있습니다.

1. uid가 아닌 속성에서 uid로 매핑 관계를 설정합니다. 그리고 이를 저장하기 위해 인덱스 테이블이나 캐시를 사용합니다. login_name에 접근할 때 먼저 매핑 테이블을 통해 login_name에 해당하는 uid를 쿼리한 후 uid를 통해 특정 라이브러리를 찾습니다.

매핑 테이블은 열이 2개뿐이므로 많은 양의 데이터를 담을 수 있습니다. 데이터 양이 너무 많으면 매핑 테이블을 가로로 분할할 수도 있습니다. 이러한 유형의 kv 형식 인덱스 구조는 캐시를 사용하여 쿼리 성능을 최적화할 수 있으며 매핑 관계는 자주 변경되지 않으며 캐시 적중률이 매우 높습니다.

• Gene method

브레이킹 유전자: uid를 통해 라이브러리를 8개로 나누면 uid%8을 사용하여 라우팅이 수행됩니다. 이때 uid의 마지막 3비트가 이 행의 특정 사용자 데이터를 결정합니다. . 어느 라이브러리에 속하는지, 이 3비트는 하위 라이브러리 유전자로 간주될 수 있습니다.

2. 프론트엔드와 백엔드 분리

• 사용자 측의 주요 요구 사항은 login_name/phone/email에서 uid로의 매핑 관계를 설정하는 것이 필요합니다. 이러한 필드의 쿼리 문제를 해결하세요.

작업 측면에서는 일괄 페이징과 다양한 조건을 포함하는 쿼리가 많습니다. 이러한 쿼리는 많은 양의 계산이 필요하고, 많은 양의 데이터를 반환하며, 데이터베이스의 높은 성능을 소모합니다. 이때 동일한 배치의 서비스나 데이터베이스를 사용자 측에서 공유하는 경우 적은 수의 백그라운드 요청이 많은 양의 데이터베이스 리소스를 점유하여 사용자 측 액세스 성능 저하 또는 시간 초과가 발생할 수 있습니다.

이러한 유형의 비즈니스는 "프런트엔드와 백엔드 분리" 솔루션을 채택하는 것이 가장 좋습니다. 운영 측면의 백엔드 비즈니스는 프런트엔드 비즈니스 시스템과의 결합을 해결하기 위해 독립적인 서비스와 DB를 추출합니다. 운영측에서는 가용성과 일관성에 대한 요구사항이 높지 않기 때문에 실시간 라이브러리에 접근하지 않고 binlog를 통해 비동기적으로 데이터를 운영 라이브러리에 동기화하여 접근하는 것이 가능하다. 데이터의 양이 많을 경우 ES 검색 엔진이나 Hive를 활용하여 백그라운드에서 복잡한 쿼리 방식을 충족할 수도 있습니다.

【관련 추천: mysql 비디오 튜토리얼

】

위 내용은 mysql에서 테이블을 분할해야 하는 경우의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!