MySQL 트랜잭션 프로그래밍 성능 및 문제 분석 [개발자 필수]

쓸데없는 정보가 가득한, 바로 분석으로 이동:

1. 루프에 제출된 문제

많은 개발자는 아래와 같이 루프에서 트랜잭션을 커밋하는 것을 좋아합니다. 그들이 자주 작성하는 저장 프로시저의 예는 다음과 같습니다.

DROP PROCEDURE IF EXISTS load1;
CREATE PROCEDURE load1(count INT UNSIGNED)BEGIN   
DECLARE s INT UNSIGNED DEFAULT 1;   
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
WHILE s <= count DO      
INSERT INTO t1 select NULL,c;      
COMMIT;      
SET s=s+1;   
END WHILE;
END;

위의 예에서 커밋 명령을 추가할지 여부는 중요하지 않습니다. MySQL innodb의 스토리지 엔진은 기본적으로 자동 제출로 설정되어 있으므로 저장 프로시저에서 커밋을 제거한 결과는 동일합니다. 아래와 같이 개발자가 쉽게 간과하는 또 다른 문제는 다음과 같습니다.

DROP PROCEDURE IF EXISTS load2;
 CREATE PROCEDURE load2(count INT UNSIGNED)BEGIN  
  DECLARE s INT UNSIGNED DEFAULT 1;   
  DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
  WHILE s <= count DO      
  INSERT INTO t1 select NULL,c;      
  SET s=s+1;   
  END WHILE;
  END;

위의 어떤 저장 프로시저와 관계없이 오류가 발생하면 데이터베이스는 알 수 없는 위치에 유지됩니다. 예를 들어 10,000개의 데이터를 삽입하려고 하는데 5,000개의 데이터를 삽입하는 중에 오류가 발생했습니다. 그런데 이 5,000개의 데이터가 데이터베이스에 저장되어 있습니다. 어떻게 처리해야 할까요? 다른 하나는 성능 문제입니다. 다음 저장 프로시저는 트랜잭션에 삽입을 넣기 때문에 위의 두 저장 프로시저 중 어느 것도 아래 저장 프로시저보다 빠르지 않습니다.

DROP PROCEDURE IF EXISTS load3; 
CREATE PROCEDURE load3(count INT UNSIGNED)BEGIN   
DECLARE s INT UNSIGNED DEFAULT 1;   
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
START TRANSACTION;   
WHILE s <= count DO      
INSERT INTO t1 select NULL,c;      
SET s=s+1;   
END WHILE;   
COMMIT;
END;

위의 세 가지 저장 프로시저에 대해 1을 삽입합니다. 실행 시간을 비교해 보면 세 번째 방법이 훨씬 빠르다는 것을 알 수 있는데, 이는 멘션마다 리두 로그를 작성해야 하므로 실제로 load1과 load2는 10만 개의 리두 로그를 작성하기 때문입니다. . 저장 프로시저 load3의 경우 리두 로그를 한 번만 작성했습니다.

먼저 테스트 테이블 준비

CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(500) NULL ,PRIMARY KEY (`id`)) ;

테스트 실행

09:50:44 test> call load1(1000000);
Query OK, 0 rows affected (1 min 4.90 sec)09:54:23 test> truncate table t1;
Query OK, 0 rows affected (0.05 sec)09:54:25 test> call load2(1000000);
Query OK, 1 row affected (1 min 3.38 sec)09:55:32 test> truncate table t1;
Query OK, 0 rows affected (0.20 sec)09:55:58 test> call load3(1000000);
Query OK, 0 rows affected (33.90 sec)

두 번째 저장 프로시저 load2의 경우 수동으로 트랜잭션을 열 수도 있고, 저장 프로시저 load3도 수동으로 열 수 있습니다. 달성 효과 및 실행 시간은 다음과 같습니다.

09:57:42 test> begin;
Query OK, 0 rows affected (0.00 sec)09:57:46 test> call load2(1000000);
Query OK, 1 row affected (34.08 sec)09:58:26 test> commit;
Query OK, 0 rows affected (0.76 sec)

2. 자동 제출 사용에 대해

위에서 언급한 것처럼 일부 특수한 시나리오에서는 자동 제출이 반드시 좋은 것은 아닙니다. 순환 제출 문제로 인해 MySQL 데이터베이스는 기본적으로 자동 제출(자동 커밋)으로 설정됩니다. 다음과 같은 방법으로 MySQL 제출 방법을 변경할 수 있습니다.

10:35:34 test> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

START TRANSATION 또는 BEGIN을 사용하여 명시적으로 트랜잭션을 시작할 수도 있습니다. MySQL은 자동으로

SET AUTOCOMMIT=0을 실행하고 COMMIT 또는 ROLLBACK이 트랜잭션을 종료한 후에 SET AUTOCOMMIT=1을 실행합니다.

3. 자동 롤백을 사용하여 예외 처리

스토어드 프로세스에서 예외가 발생하면 어떻게 해야 합니까? Innodb 스토리지 엔진은 HANDLER를 통해 트랜잭션의 자동 롤백을 지원합니다. 저장 과정에서 오류가 발생하면 자동으로 롤백 작업이 수행됩니다. 아래 예를 들면 다음과 같습니다.

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;

END;

테스트 테이블은 다음과 같습니다

CREATE TABLE `b` (  
 `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

위의 저장 프로시저를 실행하면 두 번째 레코드 1을 삽입할 때 오류가 발생하지만 자동 롤백 작업이 이루어지기 때문입니다. 활성화, 이 저장 프로시저의 실행 결과는 다음과 같습니다.

10:09:46 test> call sp_auto_rollback_demo;
Query OK, 0 rows affected (0.01 sec)
10:10:04 test> select * from b;Empty set (0.00 sec)

문제도 없고 동작도 비교적 정상적인 것 같은데, sp_auto_rollback_demo 실행 시 실행이 성공한 걸까요, 실패한 걸까요? 이를 다음과 같이 처리할 수 있는데, 예를 들면 다음과 같습니다.

DROP PROCEDURE IF EXISTS sp_auto_rollback_demo;
CREATE PROCEDURE sp_auto_rollback_demo()BEGIN   
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; 
END;   
START TRANSACTION;   
INSERT INTO b select 1;   
INSERT INTO b select 2;   
INSERT INTO b select 1;   
INSERT INTO b select 3;   
COMMIT;   
SELECT 1;
END;

오류가 발생하면 먼저 롤백한 후 -1을 반환하여 작업 중 오류가 발생했음을 나타냅니다. 1을 반환하면 정상 작동을 나타냅니다. 실행 결과는 다음과 같습니다.

10:16:19 test> call sp_auto_rollback_demo\G*************************** 1. row ***************************-1: -1
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
10:16:35 test> select * from b;
Empty set (0.00 sec)

위는 MySQL 트랜잭션 프로그래밍 성능 및 문제 분석 내용입니다.[개발 시 필독] 관련 내용은 PHP 중국어 홈페이지(www.kr)를 참고하시기 바랍니다. .php.cn)!