MySQL에서 수백만 개의 행 업데이트

MySQL에서 수백만 개의 행 업데이트

MySQL InnoDB 테이블에서 수백만 개의 행을 업데이트하려고 하는데 다음 오류가 발생합니다.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

제가 읽은 바로는 열 잠금과 많은 열에 문제가 있습니다.그래서 테이블 자체를 잠그고 쿼리를 LIMIT로 분할하여 행 잠금을 피하려고 했습니다.또한 innodb_buffer_pool_size= 4G로 구성을 변경했습니다.그러나 전체적으로 그것은 작동하지 않고 오류는 여전히 나타납니다.

MariaDB [data]> LOCK TABLES contacts WRITE;
Query OK, 0 rows affected (0.000 sec)

MariaDB [data]> UPDATE `contacts` SET `field1` = `field2` WHERE `field1` = 0 AND `field2` > 0 LIMIT 1000000;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

내가 뭘 잘못하고 있는 거지?

잘 부탁드립니다.왜 그런 일이 일어나는지 전혀 모르겠습니다.

(이것은 복제품처럼 들립니다.)

• 한 번에 1000개만 배치
• 갖고 있다INDEX(field1, field2, id)
• 테이블을 ID 순서대로 걷습니다.여기서 더 많은 토론: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

그 마지막 제안은 게임을 하는 것입니다.

SELECT ... WHERE id > $leftoff ORDER BY id LIMIT 1000,1

와 함께

UPDATE ... WHERE id > $leftoff AND id <= $newleftoff AND $filter

한 번에 1000개가 넘는 행을 만지지 않도록 하려면 먼저 고려할 행을 제한한 다음 해당 행에 대해 작업합니다.테이블이 다음에 "클러스터"되어 있기 때문에PRIMARY KEY(id)I/O가 거의 없습니다.블록에 두 번 이상 닿지 않습니다(읽기 한 번, 필요한 경우 쓰기 한 번).큰 테이블과 작은 buffer_pool에도 효율적입니다.

질의에 다른 최적화 기법을 구현하는 것

언급URL : https://stackoverflow.com/questions/72847407/updating-millions-of-rows-in-mysql