자동 증가 기본 키 없이 대규모 MySQL 테이블에 삽입하면 매우 느립니다.

Question

<p>최근에 간단한 INSERT 문을 완료하는 데 필요한 시간 차이가 크게 증가한 것을 확인했습니다. 이러한 명령문은 평균 약 11밀리초가 걸리지만 때로는 10~30초가 걸릴 수도 있으며 실행하는 데 5분 이상 걸리는 경우도 있습니다. </p> <p>MySQL 버전은 <code>8.0.24</code>이며 Windows Server 2016에서 실행됩니다. 제가 아는 한, 서버의 리소스가 과부하된 적은 없습니다. 서버에는 사용 가능한 CPU 오버헤드가 충분하며 32GB RAM이 할당되어 있습니다. </p> <p>이것은 제가 사용하고 있는 테이블입니다: </p> <pre class="brush:php;toolbar:false;">CREATE TABLE `saved_segment` ( `recording_id` bigint unsigned NOT NULL, `index` bigint unsigned NOT NULL, `start_filetime` bigint unsigned NOT NULL, `end_filetime` bigint unsigned NOT NULL, `offset_and_size` bigint unsigned NOT NULL DEFAULT '18446744073709551615', `storage_id`tinyint unsigned NOT NULL, 기본 키(`recording_id`,`index`) ) 엔진=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</pre> <p>이 테이블에는 다른 인덱스나 외래 키가 없으며 다른 테이블의 외래 키에 대한 참조로 사용되지 않습니다. 전체 테이블 크기는 20GB 정도이고, 행 수는 281M 정도로 그리 크지 않은 느낌입니다. </p> <p>이 테이블은 거의 대부분 읽기 전용 모드로 사용되며 초당 최대 1000회 읽기가 가능합니다. 이러한 모든 읽기는 복잡한 트랜잭션이 아닌 간단한 SELECT 쿼리에서 발생하며 기본 키 인덱스를 효율적으로 활용합니다. 이 테이블에 대한 동시 쓰기는 거의 없습니다. 이는 느린 삽입에 도움이 될지 알아보기 위해 의도적으로 수행되었지만 그렇지 않았습니다. 그때까지는 항상 최대 10개의 동시 삽입이 진행됩니다. 이 테이블에서는 UPDATE 또는 DELETE 문이 실행되지 않습니다. </p> <p>제가 문제를 겪고 있는 검색어는 모두 이런 방식으로 구성되었습니다. 거래에는 절대 나타나지 않습니다.클러스터된 기본 키를 기반으로 한 삽입은 추가 전용이 아니지만 쿼리는 거의 항상 1~20개의 인접한 행을 테이블에 삽입합니다. </p> <pre class="brush:php;toolbar:false;">saved_segment에 무시 삽입 (recording_id, `index`, start_filetime, end_filetime, offset_and_size, Storage_id) VALUES (19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10), (19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10), (19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10), (19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10), (19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10), (19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10), (19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10), (19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10), (19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10), (19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)</pre> <p>위 쿼리에 대한 EXPLAIN 문의 출력은 다음과 같습니다. </p> <테이블 클래스="s-테이블"> <머리> <tr> <번째> ID 유형 선택 <번째> 테이블 <번째>파티션 <번째> 유형 가능한 키 <번째> 키 <번째>key_len <번째> 참조 <일>알겠습니다</일> <th>필터링됨</th> <일>추가</일> </tr> </머리> <본문> <tr> <td>1</td> <td>삽입</td> <td>저장된 세그먼트</td> <td>비어있음</td> <td>모두</td> <td>비어있음</td> <td>비어있음</td> <td>비어있음</td> <td>비어있음</td> <td>비어있음</td> <td>비어있음</td> <td>비어있음</td> </tr> </tbody> </테이블> <p>이러한 문제는 비교적 새로운 문제이며 테이블이 두 배 정도 작을 때는 눈에 띄지 않았습니다. </p> <p>테이블의 동시 삽입 수를 약 10개에서 1개로 줄여 보았습니다. 또한 삽입 속도를 더욱 높이기 위해 일부 열에서 외래 키(<code>recording_id</code>)를 제거했습니다. <code>테이블 분석</code> 및 스키마 분석에서는 실행 가능한 정보가 나오지 않았습니다.</p> <p>제가 생각한 한 가지 해결책은 클러스터된 기본 키를 제거하고 <code>(recording_id, index)</code> 열에 자동 증가 기본 키와 일반 인덱스를 추가하는 것이었습니다. 내 생각에는 이것이 삽입을 "추가 전용"으로 만드는 데 도움이 될 것입니다.저는 어떤 제안이라도 환영합니다. 미리 감사드립니다! </p> <p>편집: 댓글과 답변에서 제기된 몇 가지 요점과 질문을 다루겠습니다. </p>

<li><code>autocommit</code>가 <code>ON</code></li>로 설정되었습니다.
• innodb_buffer_pool_size의 값은 21474836480이고, innodb_buffer_pool_chunk_size의 값은 134217728입니다. </li>
• 한 의견에서는 읽기에 사용되는 읽기 잠금과 쓰기에 사용되는 배타적 잠금 간의 경합에 대한 우려를 제기했습니다. 테이블은 다소 캐시처럼 사용되므로 성능 향상을 의미한다면 항상 테이블의 최신 상태를 반영하기 위해 읽을 필요가 없습니다. 그러나 테이블은 서버 충돌 및 하드웨어 오류가 발생하는 경우에도 내구성을 유지해야 합니다. 보다 완화된 트랜잭션 격리 수준을 사용하여 이를 달성할 수 있습니까? </li> <li>아키텍처는 확실히 최적화될 수 있습니다. <code>recording_id</code>는 4바이트 정수일 수 있으며, <code>end_filetime</code> start_filetime도 더 작을 수 있습니다. 이러한 변경으로 인해 절약된 공간을 보상하기 위해 테이블 ​​크기가 커질 때까지 문제가 잠시 미루어질 뿐입니다. </li>
• 테이블에 대한 삽입은 항상 연속적입니다. 테이블에서 수행된 SELECT는 다음과 같습니다. </li> </ul> <pre class="brush:php;toolbar:false;">TRUE 선택 저장된_세그먼트에서 녹음 ID = ? AND `색인` = ?</pre> <pre class="brush:php;toolbar:false;">SELECT 인덱스, start_filetime, end_filetime, offset_and_size, Storage_id 저장된_세그먼트에서 녹음 ID = 어디입니까? start_filetime >= ? start_filetime <= ? ORDER BY `index` ASC</pre> <p>두 번째 유형의 쿼리는 인덱스를 사용하면 확실히 향상될 수 있지만 이로 인해 INSERT 성능이 더욱 저하될까 걱정됩니다. </p> <p>또 언급하지 않은 점은 이와 매우 유사한 테이블이 존재한다는 것입니다. 쿼리와 삽입은 정확히 동일하지만 추가 IO 기아가 발생할 수 있습니다. </p> <p>편집 2: <code>SHOW TABLE STATUS</code> 테이블 <code>saved_segment</code> 및 매우 유사한 테이블 <code>saved_screenshot</code> null</code> 열의 추가 인덱스).</p> <테이블 클래스="s-테이블"> <머리> <tr> <번째> 이름 <th>엔진</th> <번째> 버전 <번째> 행 형식 <일>알겠습니다</일> <th>평균 줄 길이</th> 데이터 길이 최대 데이터 길이 Index_length <일>데이터 없음</th> <th>자동 증가</th> <번째> 생성 시간 <일>업데이트됨</일> <th>시간 확인</th> <번째> 조직 <번째> 체크섬 생성 옵션 <번째>댓글 </tr> </머리> <본문> <tr> <td>저장된 스크린샷</td> <td>InnoDB</td> <td>10</td> <td>뉴스</td> <td>483430208</td> 61 <td>29780606976</td> 0 <td>21380464640</td> <td>6291456</td> <td>비어있음</td> <td>“2021-10-21 01:03:21” <td>'2022-11-07 16:51:45'</td> <td>비어있음</td> <td>utf8mb4_0900_ai_ci</td> <td>비어있음</td> <td></td> <td></td> </tr> <tr> <td>저장된 세그먼트</td> <td>InnoDB</td> <td>10</td> <td>뉴스</td> <td>281861164</td> <td>73</td> <td>20802699264</td> 0 0 <td>4194304</td> <td>비어있음</td> <td>'2022-11-02 09:03:05'</td> <td>'2022-11-07 16:51:22'</td> <td>비어있음</td> <td>utf8mb4_0900_ai_ci</td> <td>비어있음</td> <td></td> <td></td> </tr> </tbody> </table></p>

Answer 1

이 답변으로 고민하겠습니다.

가설

• innodb_buffer_pool_size의 값은 20MB보다 약간 작습니다. 및
• 초당 1,000개의 선택 항목이 테이블의 무작위 부분에 도착하고 그런 다음

다음 Select에 필요한 "다음" 블록이 buffer_pool에 캐시되지 않는 경우가 점점 많아지면서 최근 시스템이 I/O 바인딩되었습니다.

간단한 해결책은 더 많은 RAM을 확보하고 이 튜너블의 설정을 늘리는 것입니다. 하지만 테이블은 구매하는 다음 한도까지만 증가합니다.

대신 몇 가지 부분적인 해결 방법은 다음과 같습니다.

• 숫자가 너무 크지 않으면 처음 두 열이 INT UNSIGNED（4 个字节而不是 8），甚至可能是 MEDIUMINT UNSIGNED（3 个字节） ）。注意 ALTER TABLE테이블을 오랫동안 잠글 수 있습니다.
• 이러한 시작 및 종료 시간은 분수 초가 포함된 타임스탬프처럼 보이며 항상 ".000"입니다. DATETIME 和 TIMESTAMP 5바이트를 사용합니다(8바이트 대신).
• 귀하의 예에서는 경과 시간이 0으로 표시됩니다. (종료-시작)이 일반적으로 매우 작은 경우 종료 시간 대신 경과 시간을 저장하면 데이터가 더욱 축소됩니다. (그러나 종료 시간을 사용하면 상황이 혼란스러울 수 있습니다.)
• 제공하신 예시 데이터는 "연속"으로 보입니다. 이는 자동 증가만큼 효율적입니다. 이것이 표준입니까? 그렇지 않은 경우 INSERT는 I/O 스래싱의 일부일 수 있습니다.
• 삽입 작업이 두 배로 늘어나는 보조 인덱스뿐만 아니라 인공 지능도 추가하는 것이 좋습니다.

더보기

네, 그렇습니다.

이를 INDEX，或者更好的是，作为 PRIMARY KEY의 시작 부분으로 사용하면 두 가지 질문 모두에 대해 최고의 도움을 받을 수 있습니다.

으아악

답글:

으아악

다른 SQL을 제어하는 ​​데 사용되는 경우 다른 SQL에 추가하는 것을 고려해 보세요.

으아악

이 쿼리(어느 형식이든)에는 이미 보유하고 있는 콘텐츠가 필요합니다.

으아악

기타 문의사항

으아악

그래서 색인을 추가하세요. 또는 ...

더 좋습니다... 이 조합은 두 가지 모두에 더 좋습니다 : SELECT 으아악

이 조합으로

단일 행 존재 확인은 "포함"되어 있으므로 "색인을 사용하여" 수행됩니다.
또 다른 쿼리는 PK에서 함께 클러스터된 모든 관련 행을 찾습니다.
(PK에는 고유해야 하기 때문에 이 3개의 열이 있습니다. 이 순서로 열을 두면 두 번째 쿼리에 도움이 됩니다. 또한 INDEX가 아니라 PK이므로 인덱스 BTree의 BTree 사이에 있을 필요가 없습니다. 반송과 데이터 사이)
"클러스터링"
• 은 이러한 쿼리에 필요한 디스크 블록 수를 줄여 성능을 향상시킬 수 있습니다. 이렇게 하면 buffer_pool의 "스래싱"이 줄어들어 RAM을 늘릴 필요성이 줄어듭니다.
내 색인 생성 제안은 대부분 내 데이터 유형 제안과 ​​직교합니다.