MySQL에서 2천만 개의 데이터를 최적화하고 마이그레이션하는 방법

최근 2천만 개의 레코드가 포함된 데이터 테이블을 최적화하고 마이그레이션해야 합니다. 2000W 데이터는 ://www.php.cn/wiki/1160.html" target="_blank">MySQL에 당황스럽습니다. 속도는 여전히 상당히 빠릅니다. 아무리 최적화를 해도 속도는 크게 향상되지 않습니다. 하지만 이러한 데이터에는 중복된 필드가 많고 오류 메시지가 있어 통계 및 분석에 매우 불편합니다. 분석을 하기 때문에 새로운 테이블을 생성하고 기존 테이블의 데이터를 하나씩 꺼내서 최적화한 후 다시 새 테이블에 넣어야 합니다. 1. 중복 데이터 지우기 및 필드 구조 최적화

2000W 데이터에서 쿼리의 조건으로 사용할 수 있는 필드를 이미 알고 있으므로 데이터의 이 부분에 대해 새 필드를 생성하고 varchar인 ID 카드와 같은 일반 데이터에 대한 필드 구조를 합리적으로 변경합니다(18).

일부 관련 데이터의 경우 정확한 성별, 출생을 얻기 위해 신분증 유형 등을 계산해야 합니다.

데이터 마이그레이션

데이터베이스에서 오래된 데이터를 꺼낸 후, 계산과 처리를 통해 원하는 새 데이터를 새 테이블에 삽입합니다. 그러나 새 데이터를 가져올 때 다음과 같은 문제가 발생합니다. 🎜>한 번에 얻을 수 있는 데이터의 양이 너무 많습니다(2000W의 데이터를 메모리에 던지는 것은 꽤 무서운 일입니다).

MySQL의 Limit 구문을 통해 일괄적으로 얻을 수 있습니다. 예를 들어 매번 50,000을 얻으려면 SQL 문은 다음과 같습니다.

select * from table_name limit 15000000,50000;

이 방법은 데이터 양이 너무 많은 문제를 해결할 수 있지만 제한이 있습니다. 첫 번째 매개 변수는 점점 커지고 쿼리 속도가 엄청나게 느려지므로(위 SQL 실행에는 35초가 소요됨) SQL 문을 최적화하기 시작했고 최적화 후 다음과 같이 되었습니다.

1. select * from table_name order by id desc limit 5000000,50000;

   .

   2000W 데이터를 1000W 데이터를 실행하면 데이터가 반전됩니다. 최적화 후 SQL 실행 효율성은 35초에서 9초로 크게 향상됩니다.

   그러나 여전히 시간이 생명입니다. 다행히도 자동 증가 ID가 있습니다(데이터 테이블 생성의 첫 번째 법칙, 자동 증가 필드가 있어야 함). 최적화된 SQL은 다음과 같습니다.

   1. select * from table_name where id>15000000 and id<15050000； 2. select * from table_name where id>15000000 limit 50000;

   직관적인 설명을 위해 두 개의 SQL을 작성했습니다. 동일한 기능을 수행하는 경우 두 번째 SQL의 제한으로 인해 인덱스 히트가 더 심해지고 첫 번째 SQL의 실행 시간이 2밀리초로 단축됩니다. 두 번째 SQL은 5밀리초입니다.(평균값을 취했습니다.) 각 데이터의 쿼리 속도가 35초에서 2밀리초로 바로 떨어졌습니다...

   데이터의 양이 너무 많습니다. 데이터를 추정할 수 없으며 일부 특수 데이터로 인해 데이터 가져오기가 실패할 수 있습니다.

   새 데이터를 새 테이블에 저장하는 세 가지 옵션은 다음과 같습니다.

2. 데이터를 하나씩 삽입
   ;
처음에는 삽입할 때마다 데이터베이스 IO 작업이 발생하기 때문에 이 솔루션이 작동하지 않을 것이라고 확실히 생각했습니다. 하지만 이 솔루션의 장점 중 하나는 문제가 있는 데이터를 적시에 감지하고 수정 후에도 계속 실행할 수 있다는 점입니다. Oracle의 "바인드
1. 변수

   "를 사용하면 성능이 향상될 수 있으며, MySQL도 "바인드" 변수' 기능을 제공합니다. 따라서 논리를 변경하지 않고 데이터 저장 속도를 최적화해 보세요. 코드는 다음과 같습니다.

   public function actionTest(array $data)
   {
       $mysqli = new mysqli("192.168.1.106", "username", "password", "test");
       $sql = "insert into table_name(name,identity) values (?,?)";
   
       $stmt = $connection->prepare($sql);
       $name = "";
       $identity = "";
       //使用绑定变量
       $stmt->bind_param("si", $name, $identity);
       foreach($data as $val)
       {
           $name = $val[name];
           $identity = $val[card_id];
           //执行
           $stmt->execute();
       }
       $stmt->close();
   }

   최종 효과는 그리 좋지 않습니다. MySQL의 "바인드 변수"는 속도 향상을 가져오지는 않지만 SQL 삽입을 효과적으로 방지할 수 있습니다.
   한 번에 50,000개의 데이터를 삽입합니다.

   이것이 제가 마침내 선택한 솔루션입니다. 첫째, 문제가 있는 데이터를 적시에 감지할 수 있고, 둘째, 가져온 데이터가 매우 안정적입니다. 중단점 재개를 지원하는 것과 마찬가지로 모든 단계에서 효과를 확인할 수 있습니다. 스크립트를 실행할 때 분석 로직 작성을 동시에 시작할 수도 있습니다.

   은 SQL 파일로 어셈블되고 최종적으로는 균일하게 가져옵니다.

   대용량 SQL 파일을 모아서 MySQL과 함께 제공되는 도구를 통해 최종적으로 가져오는 것도 좋습니다. 그러나 SQL 문 중 하나에 문제가 있는 경우 스크립트를 다시 실행해야 할 수도 있습니다. 9G 텍스트 파일에서 기호를 수정하는 것은 매우 고통스럽기 때문에...

3. 요약

이를 통해 일종의 최적화를 통해 마침내 스크립트 실행 시간이 20분 미만으로 단축되었습니다. 최적화 후 데이터 품질은 매우 보장됩니다. 다음에는 2억 개의 데이터를 최적화하고 마이그레이션할 수 있도록 노력하겠습니다...

위 내용은 MySQL에서 2천만 개의 데이터를 최적화하고 마이그레이션하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!