PHP가 수천만 개의 테이블을 MySQL을 최적화하는 방법에 대한 자세한 설명

우선 MySQL을 사용하여 수천억 개의 데이터를 저장한다는 것은 참으로 큰 도전입니다. 단일 Mysql 테이블은 실제로 10억 레벨의 데이터를 저장할 수 있지만 현재로서는 성능이 매우 낮습니다. 프로젝트의 많은 실험에서 단일 Mysql 테이블의 용량은 약 500만 개에 달하며 성능은 매우 낮습니다. 최선을 다해.

대형 테이블의 최적화는 주로 데이터베이스 하위 데이터베이스와 테이블 하위 테이블을 통해 해결됩니다. 현재 세 가지 일반적인 솔루션이 있습니다. 分区，分库分表，NoSql/NewSql. 실제 프로젝트에서는 이 세 가지 솔루션이 결합되어 있으며 현재 대부분 시스템의 핵심 데이터는 NoSql/NewSql 스토리지로 보완되는 RDBMS 스토리지입니다.

Partition

먼저 파티션 구성표를 이해해 봅시다.

파티션된 테이블은 여러 관련 기본 테이블로 구현됩니다. 이러한 기본 테이블은 핸들 개체로도 표시되므로 각 파티션에 직접 액세스할 수도 있습니다. 스토리지 엔진은 일반 테이블을 관리하는 것과 동일하게 파티션의 기본 테이블을 관리합니다(모든 기본 테이블은 동일한 스토리지 엔진을 사용해야 함). 파티션 테이블은 각 기본 테이블에 동일한 인덱스를 추가하는 것입니다. 이 솔루션은 쿼리 조건에 샤딩 열이 없더라도 정상적으로 작동할 수 있습니다(단, 현재 성능은 평균 수준입니다).

그러나 단점은 분명합니다. 연결 수, 네트워크 처리량 등 단일 시스템으로 인해 많은 리소스가 제한됩니다. 분할 방법은 실제 응용 분야의 핵심 요소 중 하나입니다.

예를 들어보겠습니다. 고객 정보를 예로 들면, 고객 데이터의 양은 5천만 개가 넘습니다. 프로젝트 배경에는 고객의 은행 카드 바인딩 관계, 고객의 문서 바인딩 관계 및 고객의 바인딩 비즈니스 정보를 저장해야 합니다.

이 비즈니스 배경에서 데이터베이스를 설계하는 방법. 프로젝트의 첫 번째 단계에서 우리는 각 고객에게 바인딩된 중복 비즈니스 정보가 포함된 고객 비즈니스 바인딩 관계 테이블을 구축했습니다.

기본 구조는 대략 다음과 같습니다.

조회하면 은행카드, 사업자번호, 인증서번호가 색인됩니다. 수요가 증가함에 따라 이 테이블의 인덱스는 10을 초과하게 됩니다. 또한, 고객이 계약을 해지한 후 다른 계약을 체결하는 경우 두 가지 데이터가 저장되지만 구속력은 다릅니다.

고객이 5천만 명, 업종이 5개, 고객당 평균 카드가 2개라고 가정하면 이 테이블의 데이터 양은 무려 5억 명에 달합니다. 실제로 우리 시스템의 사용자 수는 한 명도 넘지 않았습니다. 더 이상 불가능했습니다. 이러한 디자인은 삽입이든 쿼리이든 시스템이 중단되는 것은 절대 불가능합니다.

mysql 데이터베이스의 데이터는 기본적으로 /mysql/data 아래에 저장됩니다. (my.cnf의 datadir을 통해 볼 수 있습니다.) 하나의 테이블은 주로 세 개의 파일에 해당합니다. 하나는 frm이 테이블 구조를 저장하고, 하나는 myd가 테이블 데이터를 저장하고, 다른 하나는 myi가 테이블 인덱스를 저장하는 것입니다. 이 세 파일은 매우 큽니다. 특히 .myd 파일은 거의 5G에 달합니다. 첫 번째 파티션 최적화를 수행해 보겠습니다. Mysql에서 지원하는 네 가지 파티셔닝 방법은 다음과 같습니다.

우리 프로젝트에서는 범위 파티셔닝과 목록 파티셔닝에는 바인딩 번호를 기반으로 범위 또는 목록 파티셔닝을 만드는 경우 바인딩이 사용됩니다. 숫자는 실제 비즈니스 의미가 없으며 이를 통해 쿼리할 수 없습니다. 따라서 HASH 파티션과 KEY 파티션만 남습니다. HASH 파티션은 int 유형 열의 분할만 지원하며 열 중 하나입니다.

KEY 파티셔닝은 여러 열을 지원할 수 있지만 열 중 하나가 int 유형이어야 합니다. 라이브러리 테이블 구조를 보면 어떤 열도 int 유형이 아닌 것을 알 수 있습니다. 바인딩 시간 컬럼인 컬럼을 추가하고 이 컬럼을 int 유형으로 설정한 후 바인딩 시간에 따라 분할하고 매일 바인딩되는 사용자를 동일한 영역으로 나눕니다.

이 최적화 후에는 삽입 속도가 훨씬 빨라졌지만 쿼리는 여전히 매우 느립니다. 이유는 무엇입니까?

쿼리를 할 때 시간을 기준으로 하지 않고 은행 카드나 ID 번호만 기준으로 쿼리하기 때문에 이는 모든 쿼리와 동일하며 MySQL은 모든 파티션 테이블을 쿼리합니다.

두 번째 솔루션 최적화가 수행됩니다. HASH 파티셔닝과 KEY 파티셔닝에서는 열 중 하나가 int 유형이어야 하므로 파티셔닝을 위해 int 유형의 목록을 생성할 수 있습니까?

분석 결과 은행 카드의 일련번호에 비밀이 있는 것으로 나타났습니다. 은행 카드는 일반적으로 16~19자리 숫자의 문자열입니다. 그 중 하나를 테이블 파티션으로 사용하는 것이 가능합니까? 분석을 통해 이 숫자 문자열 중 하나가 실제로 있음을 발견했습니다. 0부터 9까지의 난수를 생성하며, 은행카드번호 + 난수를 기준으로 KEY 파티셔닝을 수행합니다. 쿼리할 때마다 계산을 통해 난수를 가로채서 카드번호를 추가하고 공동 쿼리를 수행하여 목적을 달성합니다. 파티션 쿼리. 파티션을 나눈 후 생성된 인덱스도 파티션 열이어야 합니다. 그렇지 않으면 MySQL은 여전히 ​​모든 파티션 테이블의 데이터를 쿼리합니다.

은행 카드번호를 통해 바인딩 관계를 확인하던 문제가 해결되었습니다. ID 번호를 통해 바인딩 관계를 확인하는 방법은 무엇인가요?

앞서 언급했듯이 파티션 상태에서 인덱싱을 수행해야 합니다. 그렇지 않으면 전체 테이블 스캔이 발생합니다. 고객의 ID 번호 바인딩 관계를 저장하기 위해 새 테이블을 만들었는데, 새 ID 번호 바인딩 관계 테이블에서는 ID 번호가 기본 키로 사용됩니다. 고객의 문서 정보는 ID 번호, 홍콩, 마카오 및 대만 여권, 자동차 운전 면허증 등을 포함하여 비교적 복잡합니다. 무질서한 문서 번호에서 파티션 키를 찾는 방법.

이 문제를 해결하기 위해 ID 번호 바인딩 관계 테이블을 두 개로 나눕니다. 테이블 중 하나는 ID 카드 유형의 ID 번호를 저장하는 테이블이고, 다른 테이블은 다른 ID 유형의 ID 번호를 저장하는 테이블입니다. 아이덴티티에서는 인증서 종류의 인증서 결합관계 테이블에서 ID 번호의 월 수를 파티션 키로 분할하고, 같은 달에 태어난 고객의 ID 번호를 같은 영역에 저장하여 12개로 나누었습니다. 인증서 유형의 인증서 번호 데이터 볼륨이 100,000을 초과하지 않으면 분할할 필요가 없습니다.

이런 식으로 쿼리할 때마다 먼저 인증서 유형을 기준으로 어떤 테이블을 쿼리할지 결정한 다음 쿼리할 파티션 키를 계산합니다. 파티션 설계를 한 후 2천만 개의 사용자 데이터를 저장할 때 은행 카드 테이블의 데이터 저장 파일을 10개의 작은 파일로 나누고, 인증서 테이블의 데이터 저장 파일을 12개의 작은 파일로 나누어 이를 해결했습니다. 쿼리 문제 및 한 가지 질문이 남습니다. 사업장 번호로 무엇을 해야 합니까?

고객이 여러 계약 업체를 보유하고 있는데 어떻게 저장하나요? 현재로서는 파티셔닝 솔루션을 사용하는 것은 적절하지 않습니다. 테이블 파티셔닝 솔루션이 필요합니다.

테이블

앞서 mysql의 경우 데이터 파일이 파일 형식으로 디스크에 저장된다고 언급했습니다. 데이터 파일이 너무 크면 운영 체제에서 큰 파일을 처리하는 것이 번거롭고 시간이 많이 걸리며, 일부 운영 체제에서는 대용량 파일을 지원하지 않습니다. 이때 테이블을 나누어야 합니다.

또한, mysql에 일반적으로 사용되는 스토리지 엔진은 Innodb이며, 기본 데이터 구조는 B+ 트리입니다. 데이터 파일이 너무 큰 경우 노드를 쿼리하면 많은 수준이 쿼리될 수 있으며 이로 인해 필연적으로 여러 IO 작업이 메모리에 로드되어 시간이 많이 소모됩니다.

또한 B+ 트리에 대한 Innodb의 잠금 메커니즘이 있습니다. 각 노드가 잠긴 경우 테이블 구조가 변경되면 트리가 잠기게 됩니다. 테이블 파일이 크면 이를 달성할 수 없는 것으로 간주될 수 있습니다. 따라서 요약하면 하위 테이블과 하위 데이터베이스의 작업을 수행해야 합니다.

데이터베이스 및 테이블의 샤딩을 수행하는 방법 현재 인터넷에는 다양한 버전이 있습니다. 가장 잘 알려진 솔루션은 다음과 같습니다: Alibaba의 TDDL, DRDS 및 cobar, JD Finance의 sharding-jdbc; ; Meituan의 Zebra, NetEase, 58, JD.com 및 기타 회사는 자체 개발한 미들웨어를 보유하고 있습니다.

많은 샤딩 미들웨어 솔루션은 클라이언트 모드와 프록시 모드라는 두 가지 범주로 요약될 수 있습니다.

클라이언트 모드

프록시 모드

클라이언트 모드인지 프록시 모드인지. SQL 구문 분석, 재작성, 라우팅, 실행, 결과 병합 등 몇 가지 핵심 단계는 동일합니다. 개인적으로 저는 클라이언트 모드를 선호합니다. 아키텍처가 간단하고 성능 손실이 상대적으로 적으며 운영 및 유지 관리 비용이 저렴합니다.

비즈니스 유형에 대한 하위 데이터베이스 및 테이블을 만드는 방법. 데이터베이스 및 테이블 분할에서 가장 중요한 단계는 분할 열 선택입니다. 분할 열 선택의 품질에 따라 전체 데이터베이스 분할 및 테이블 분할 체계가 궁극적으로 성공할지 여부가 직접적으로 결정됩니다. 샤딩 컬럼의 선택은 비즈니스와 밀접한 관련이 있습니다.

우리 프로젝트 시나리오에서 샤딩 열에 대한 최선의 선택은 의심할 여지 없이 사업장 번호입니다. 사업장 번호를 통해 고객의 다양한 바인딩 계약 서비스가 서로 다른 테이블에 저장되고 사업장 번호에 따라 쿼리를 위해 해당 테이블로 라우팅되어 SQL을 더욱 최적화합니다.

더 많은 관련 PHP 지식을 보려면 php 튜토리얼을 방문하세요!

위 내용은 PHP가 수천만 개의 테이블을 MySQL을 최적화하는 방법에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!