<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>