MySQL 2천만 데이터 최적화 및 마이그레이션

최근 2천만 개의 레코드가 포함된 데이터 테이블을 최적화하고 마이그레이션해야 합니다. 2천만 개의 데이터는 MySQL에게는 매우 당황스러운 일입니다. 왜냐하면 인덱스 생성 속도는 여전히 매우 빠르며 아무리 최적화를 수행해도 속도를 크게 향상시킬 수 없기 때문입니다. 그러나 이러한 데이터는 중복된 필드와 오류 정보가 많아 통계 및 분석에 매우 불편합니다. 그래서 새 테이블을 만들고 기존 테이블에서 데이터를 하나씩 꺼내서 최적화한 다음 다시 새 테이블에 넣어야 합니다.

1. 필드 구조

2000W 데이터에서 쿼리 조건으로 사용할 수 있는 필드를 예측할 수 있습니다. 따라서 이 부분의 데이터에 대해서는 별도로 새로운 필드를 생성하고, 신분증 등 일반 데이터에 대해서는 필드 구조를 합리적으로 변경하는 것이 varchar(18)이다. 중요하지 않은 데이터의 경우 이를 병합하면 텍스트 구조가 있는 필드가 있습니다.

신분증 유형과 같은 일반적인 데이터를 계산해야 정확한 성별, 출생지, 생일, 나이를 얻을 수 있습니다.

2. 데이터 마이그레이션

데이터베이스에서 오래된 데이터를 꺼낸 후, 계산과 처리를 거쳐 원하는 새 데이터를 얻고, 마지막으로 새 데이터를 새 테이블에 추가합니다. 그러나 새로운 데이터를 얻는 과정에서 다음과 같은 문제가 발생했습니다.

select * from table_name limit 15000000,50000;

이 방법을 사용하면 데이터 양이 너무 많아지는 문제를 해결할 수 있지만, Limit의 첫 번째 매개변수가 커질수록 쿼리 속도가 느려집니다. (위의 SQL을 실행하는 데 35초가 소요됩니다.) 시간은 생명이므로 SQL 문을 최적화하기 시작했습니다. 최적화 후 다음과 같이 되었습니다.

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

   는 2000W 데이터를 이분법으로 분할할 수 있습니다. 최적화 후 SQL 실행 효율이 35초에서 9초로 크게 향상되었습니다.

   하지만 여전히 매우 느리고 시간이 생명입니다... 다행스럽게도 자체 증가 ID(제1법칙)가 있습니다. 데이터 테이블을 생성하려면 반드시(자동 증가 필드 사용) 최적화된 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밀리초로 바로 떨어졌습니다...

   데이터의 양이 너무 많아서 데이터를 추정할 수 없습니다. import 실패;

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

2. 데이터를 하나씩 삽입합니다.
   처음에는 분명히 생각할 것입니다. 삽입할 때마다 데이터베이스 IO 작업이 발생하기 때문에 이 솔루션은 확실히 작동하지 않습니다. 하지만 이 솔루션의 장점은 문제가 있는 데이터를 적시에 감지하고 수정 후에도 계속 실행할 수 있다는 것입니다. Oracle에서 "바인드 변수"를 사용하면 성능이 향상될 수 있으며 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 삽입을 효과적으로 방지할 수 있습니다.
1. 한 번에 50,000개의 데이터를 삽입합니다.
   이것이 제가 마침내 선택한 솔루션입니다. 첫째, 문제가 있는 데이터를 적시에 감지할 수 있고, 둘째, 가져온 데이터가 매우 안정적입니다. 중단점 재개를 지원하는 것과 마찬가지로 모든 단계에서 효과를 확인할 수 있습니다. 스크립트를 실행할 때 분석 논리 작성을 동시에 시작할 수도 있습니다.
   은 SQL 파일로 어셈블되고 최종적으로
   은 대규모 SQL로 어셈블됩니다. 파일로 저장하고 마지막으로 MySQL을 통해 내장 도구 가져오기도 훌륭합니다. 그러나 SQL 문 중 하나에 문제가 있는 경우 스크립트를 다시 실행해야 할 수도 있습니다. 9G 텍스트 파일에서 기호를 수정하는 것은 매우 고통스럽기 때문에...

3. 요약

다양한 최적화를 통해 드디어 스크립트 실행 시간이 20분 이내로 단축되었습니다. 최적화 후 데이터 품질은 높게 보장됩니다. 다음에는 2억 데이터 최적화 및 마이그레이션을 시도하겠습니다...

위 내용은 MySQL 2천만 데이터 최적화 및 migration, more 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!