MySQL이 uuid를 기본 키로 사용할 수 없는 이유에 대해 이야기해 보겠습니다.

이 기사에서는 MySQL이 uuid를 기본 키로 사용할 수 없다는 관련 지식을 제공합니다. MySQL은 공식적으로 uuid 또는 불연속적이고 반복되지 않는 눈송이 ID를 사용하지 말 것을 권장하지만, 지속적인 자체 증가 기본 키 ID는 auto_increment입니다. uuid를 사용하는 것이 권장되지 않는 이유는 무엇입니까? 모든 사람에게 도움이 되기를 바랍니다.

머리말

mysql에서는 공식적으로 uuid 또는 불연속적이고 반복되지 않는 눈송이 ID(긴 모양의 고유한 단일 머신 증분)를 사용하지 말 것을 권장하지만 연속 자동을 권장합니다. -increment 기본 키 ID에 대한 공식 권장 사항은 auto_increment인데, uuid를 사용하는 것이 권장되지 않는 이유는 무엇입니까?

1. MySQL 및 프로그램 예제

1.1 이 문제를 설명하기 위해 먼저 세 개의 테이블을 만듭니다.

자동으로 증가하는 기본 키를 나타내는 user_auto_key, user_uuid, user_random_key이고 uuid는 기본 키 ,

기본 키로 무작위 키를 사용하고 나머지는 그대로 유지합니다.

제어 변수 방식에 따라 서로 다른 전략을 사용하여 각 테이블의 기본 키만 생성하고 다른 필드는 정확히 동일하고 테이블의 삽입 속도와 쿼리 속도를 테스트합니다.

참고: 여기서 임의 키는 실제로 눈송이 알고리즘으로 계산된 비연속, 비반복 및 불규칙 ID를 나타냅니다. 비트 긴 값 ​​

1.2. 이론만으로는 충분하지 않습니다. 프로그램으로 이동하여 Spring의 jdbcTemplate을 사용하여 추가 검사 테스트를 구현하세요.

기술 프레임워크: springboot+ jdbcTemplate+junit+hutool 프로그램의 원리는 자체 테스트 데이터베이스를 연결한 다음 동일한 환경에서 동일한 양의 데이터를 작성하고 삽입 시간을 분석하여 효율성을 종합적으로 분석하는 것입니다. 가장 현실적인 효과는 이름, 이메일 주소, 주소 등 모든 데이터가 무작위로 생성된다는 것입니다.

package com.wyq.mysqldemo;
import cn.hutool.core.collection.CollectionUtil;
import com.wyq.mysqldemo.databaseobject.UserKeyAuto;
import com.wyq.mysqldemo.databaseobject.UserKeyRandom;
import com.wyq.mysqldemo.databaseobject.UserKeyUUID;
import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
import com.wyq.mysqldemo.util.JdbcTemplateService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StopWatch;
import java.util.List;
@SpringBootTest
class MysqlDemoApplicationTests {
    @Autowired
    private JdbcTemplateService jdbcTemplateService;
    @Autowired
    private AutoKeyTableService autoKeyTableService;
    @Autowired
    private UUIDKeyTableService uuidKeyTableService;
    @Autowired
    private RandomKeyTableService randomKeyTableService;
    @Test
    void testDBTime() {
        StopWatch stopwatch = new StopWatch("执行sql时间消耗");
        /**
         * auto_increment key任务
         */
        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
        List<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
        stopwatch.start("自动生成key表任务开始");
        long start1 = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false);
            System.out.println(insertResult);
        }
        long end1 = System.currentTimeMillis();
        System.out.println("auto key消耗的时间:" + (end1 - start1));
        stopwatch.stop();
        /**
         * uudID的key
         */
        final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
        stopwatch.start("UUID的key表任务开始");
        long begin = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);
            System.out.println(insertResult);
        }
        long over = System.currentTimeMillis();
        System.out.println("UUID key消耗的时间:" + (over - begin));
        stopwatch.stop();
        /**
         * 随机的long值key
         */
        final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
        stopwatch.start("随机的long值key表任务开始");
        Long start = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);
            System.out.println(insertResult);
        }
        Long end = System.currentTimeMillis();
        System.out.println("随机key任务消耗时间:" + (end - start));
        stopwatch.stop();
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }

1.3. 프로그램 작성 결과

데이터 양이 100W 정도일 때 uuid의 삽입 효율이 가장 밑에 있고, 130W의 데이터가 추가되는 것을 알 수 있습니다. 포스트오더, 그리고 우디의 시간은 다시 급락했습니다.

시간 사용량에 따른 전체 효율성 순위는 auto_key>random_key>uuid이며, uuid는 효율성이 가장 낮습니다. 데이터 양이 많으면 효율성이 급락합니다. 그렇다면 왜 이런 일이 발생합니까? 의심스러운 부분이 있으면 이 문제에 대해 토론해 보겠습니다.

2. uuid와 self-increasing id를 사용한 인덱스 구조 비교

2.1 self-increasing id를 사용한 내부 구조

Auto- 증가 기본 키의 값은 순차적이므로 Innodb는 각 레코드를 레코드 뒤에 저장합니다. 페이지의 최대 채우기 비율에 도달하면(InnoDB의 기본 최대 채우기 비율은 페이지 크기의 15/16이며 향후 수정을 위해 공간의 1/16이 남습니다):

① 다음 레코드가 작성됩니다. 새로운 한 페이지에 이 순서대로 데이터가 로드되면 기본 키 페이지가 거의 순차적인 레코드로 채워져 페이지의 최대 채우기 비율이 높아지고 페이지 낭비가 발생하지 않습니다

② 새로 삽입된 행 확실히 원본에 있을 것입니다. 가장 큰 데이터 행의 다음 행, mysql 위치 지정 및 주소 지정이 매우 빠르며 새 행의 위치를 ​​계산하는 데 추가 소비가 발생하지 않습니다

③페이지 분할 및 조각 생성을 줄입니다

2.2. uuid 내부 인덱스 구조 사용

uuid는 순차적 자동 증가 ID에 비해 불규칙하기 때문에 새 행의 값이 반드시 이전 기본 키의 값보다 클 필요는 없으므로 innodb가 항상 추가할 수는 없습니다. 새 행은 인덱스 끝에 삽입되지만 새 공간을 할당하려면 새 행에 적합한 새 위치를 찾아야 합니다.

이 과정에는 추가 작업이 많이 필요합니다. 무질서한 데이터로 인해 데이터가 분산되어 다음과 같은 문제가 발생합니다.

① 작성된 대상 페이지가 디스크에 플러시되고 캐시에서 제거되었을 가능성이 높습니다. , 또는 캐시에 로드되지 않은 경우 innodb는 삽입하기 전에 대상 페이지를 디스크에서 메모리로 찾아서 읽어야 합니다. 이로 인해 많은 무작위 IO가 발생합니다

② 쓰기 순서가 잘못되었기 때문에 innodb는 자주 페이지를 읽어야 합니다. 새 행에 공간을 할당하기 위해 분할 작업이 수행됩니다. 페이지 분할을 수행하면 한 번의 삽입을 위해 최소 3페이지를 수정해야 합니다.

③잦은 페이지 분할로 인해 페이지가 희박해지고 불규칙하게 채워져 결국 데이터 조각화가 발생하게 됩니다.

향후에는 클러스터형 인덱스(innodb 기본 인덱스)에 임의의 값(uuid 및 Snowflake id)을 로드합니다. , 때로는 OPTIMEIZE TABLE을 수행하여 테이블을 다시 작성하고 페이지 채우기를 최적화해야 하며, 이는 일정 시간이 걸립니다.

결론: innodb를 사용할 때는 기본 키의 자동 증가 순서로 최대한 많이 삽입해야 하며, 새로운 행을 삽입하려면 단조롭게 증가하는 클러스터 키 값을 사용해 보십시오

2.3. -증가하는 ID

그럼 자동증가되는 ID를 사용해도 문제가 없는 걸까요? 아니요, 자체 증가 ID에는 다음과 같은 문제도 있습니다.

① 다른 사람이 귀하의 데이터베이스를 크롤링하면 데이터베이스의 자체 증가 ID를 기반으로 귀하의 비즈니스 성장 정보를 얻을 수 있으며 귀하의 비즈니스 상황을 쉽게 분석할 수 있습니다

② 동시성 로드가 높은 경우 InnoDB는 기본 키로 삽입할 때 명백한 잠금 경합을 유발합니다. 모든 삽입이 여기에서 발생하고 동시 삽입으로 인해 갭 잠금이 발생하기 때문입니다.

3 Auto_Increment 잠금 메커니즘은 자동 증가 잠금 경쟁을 유발하여 특정 성능 손실을 초래합니다.

첨부: Auto_increment 잠금 경쟁 문제, 이를 개선하려면 innodb_autoinc_lock_mode 구성을 조정해야 합니다

3 . 요약

이 블로그는 먼저 처음에 질문하고, 테이블을 만들고, jdbcTemplate을 사용하여 대량의 데이터를 삽입할 때 다양한 ID 생성 전략의 성능을 테스트하는 것으로 시작합니다. 그런 다음 인덱스 구조와 장단점을 분석합니다. MySQL의 다양한 ID 메커니즘에 대한 심층 설명 이 문서에서는 uuid 및 임의의 비반복 ID가 데이터 삽입 시 성능 손실을 일으키는 이유를 설명하고 이 문제를 자세히 설명합니다.

실제 개발에서는 mysql의 공식 권장사항에 따라 self-increasing ID를 사용하는 것이 가장 좋습니다. MySQL은 넓고 심오하며, 최적화할 만한 내부 포인트가 많이 있습니다.

추천 학습: mysql 비디오 튜토리얼

위 내용은 MySQL이 uuid를 기본 키로 사용할 수 없는 이유에 대해 이야기해 보겠습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!