[MySQL] UPDATE

 

UPDATE 방식은 여러가지가 있다. 최근 본 프로젝트에서 UPDATE문을 JOIN으로 하는것을 보고 너무 신기해서 UPDATE에 대해 좀더 알아보기로 했다. 일반적으로 사용하는 UPDATE ~ SET ~ WHERE, 파라미터로 값을 받아서 처리하는 방식 외에도 많은 방식으로도 있는 것을 알게 되었다.

 

1. 단일 테이블 업데이트

       기본적으로 사용하는 업데이트

UPDATE users
SET status = 'inactive'
WHERE last_login < '2023-01-01';

 

2. CASE 문을 사용한 조건부 업데이트
        'CASE' 문을 사용한 업데이트

UPDATE users
SET status = CASE 
    WHEN last_login < '2023-01-01' THEN 'inactive'
    WHEN last_login >= '2023-01-01' AND last_login < '2023-06-01' THEN 'semi-active'
    ELSE 'active'
END;

 

3. 변수 사용

        'SET'으로 변수를 설정해서 업데이트

UPDATE users
SET status = CASE 
    WHEN last_login < '2023-01-01' THEN 'inactive'
    WHEN last_login >= '2023-01-01' AND last_login < '2023-06-01' THEN 'semi-active'
    ELSE 'active'
END;

 

4. 데이터 복사 후 대체(REPLACE 또는 INSERT INTO SELECT)

        'INSERT INTO SELECT' , 'REPLACE'를 사용해서 데이터 복사,교체 업데이트
          (대규모로 데이터를 업데이트, 테이블 재구조화 할때 좋다)

INSERT INTO users_backup (id, name, status)
SELECT id, name, 'inactive'
FROM users
WHERE last_login < '2023-01-01';

REPLACE INTO users SELECT * FROM users_backup;

 

5. 트리거(Trigger) 사용

       트리거는 테이블 데이터가 변경되면 변경된 테이블과 연결된 테이블의 데이터도 변경시켜준다. 
        (복잡한 데이터 일관성을 유지하는데 좋다.)

CREATE TRIGGER update_user_status
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE users
    SET total_orders = total_orders + 1
    WHERE users.id = NEW.user_id;
END;

        

6. 멀티 테이블 업데이(Muti-Table Update)

        두개 이상의 테이블을 동시에 업데이트, 조인된 여러 테이블에 대해 한번에 업데이트가 필요한 경우

UPDATE users, orders
SET users.status = 'inactive', orders.status = 'cancelled'
WHERE users.id = orders.user_id
AND users.last_login < '2023-01-01';

 

7. 서브쿼리 업데이트

UPDATE users
SET total_orders = (
    SELECT COUNT(*)
    FROM orders
    WHERE orders.user_id = users.id
)
WHERE users.status = 'active';

 

 

8. JOIN 업데이트

UPDATE users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id
SET u.total_orders = o.order_count
WHERE u.status = 'active';

        

성능적으로는 서브쿼리보다는 JOIN쿼리 방식을 더 선호하고 쿼리가 더 직관적이고 유지하기 쉽다고 한다. 하지만 웹서비스 같은 OLTP환경에서는 데드락을 유발하기도 하기 때문에 사용 하는 환경에 따라서 어느쪽에 더 좋은지 좀더 고민하고 사용하는것이 좋지 않을까..?

 

서브쿼리 vs. JOIN을 사용하는 이유

1. 성능: 서브쿼리는 각 행에 대해 서브쿼리를 실행하는 경우가 많아 성능이 저하될 수 있습니다. 반면, JOIN을 사용하면 조인된 테이블의 데이터를 한 번에 가져와 업데이트하므로 성능이 향상될 수 있습니다.

2. 가독성: 서브쿼리를 사용하면 쿼리가 복잡해질 수 있지만, JOIN을 사용하면 쿼리가 더 직관적이고 읽기 쉬워집니다. 특히 여러 테이블을 조인하여 다양한 조건을 적용하는 경우, JOIN이 더 명확한 표현 방법이 될 수 있습니다.

3. 유연성: JOIN을 사용하면 여러 테이블에서 데이터를 쉽게 결합하고, 결합된 데이터를 기반으로 원하는 필드를 업데이트할 수 있습니다. 이는 서브쿼리를 사용할 때보다 더 유연하게 조건을 설정할 수 있게 해줍니다

출처 : chatgpt

 

JO IN UPDATE는 조인되는 모든 테이블에 대해 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 칼럼이 변경되는 테이블은 쓰기 잠금이 걸린다. 그래서 JO IN UPDATE 문장이 웹 서비스 같은 OLTP 환경에서는 데드락을 유발할 가능성이 높으므로 너무 빈번하게 사용하는 것은 피하는 것이 좋 다. 하지만 배치 프로그램이나 통계용 UPDATE 문장에서는 유용하게 사용할 수 있다.

출처 : Real My SQL 8.0 2

 

 

 

 

++

UPDATE JOIN , DEADLOCK

UPDATE JOIN과 UPDATE 서브쿼리를 비교할 때, 성능과 동시성 제어(예: 데드락 회피) 사이에서 균형을 잡아야 합니다. 상황에 따라 각각의 접근 방식이 적합할 수 있으며, 아래와 같은 내용을 고려할 필요가 있습니다.

1. UPDATE JOIN vs. UPDATE 서브쿼리
- UPDATE JOIN: 일반적으로 두 개 이상의 테이블을 조인하여 업데이트할 때 사용됩니다. 한 번의 쿼리로 여러 테이블     의 데이터를 조회하고 동시에 업데이트할 수 있어, 단일 쿼리로 많은 작업을 처리할 수 있습니다. 이는 성능 측면에       서 이점이 될 수 있습니다.
- UPDATE 서브쿼리: 주로 서브쿼리를 사용하여 특정 조건에 맞는 데이터를 업데이트할 때 사용됩니다. 이는 좀 더 직    관적일 수 있고, 복잡한 로직을 처리하는 데 유리합니다.

2. 데드락 발생 가능성
- UPDATE JOIN은 조인되는 테이블들이 동시에 잠금을 걸게 되며, 이로 인해 데드락(Deadlock)이 발생할 가능성이
   있습니다. 특히, 여러 테이블에서 동시에 데이터에 접근하고 잠금을 걸 때, 서로 교착 상태에 빠질 위험이 있습니다.
- UPDATE 서브쿼리는 일반적으로 단일 테이블을 타겟으로 하는 경우가 많아, 잠금이 상대적으로 덜 발생할 수 있습니
   다. 그러나, 서브쿼리 자체가 복잡하거나 많은 데이터를 처리할 경우에도 잠금과 성능 문제가 발생할 수 있습니다.

3. 데드락 회피 및 성능 최적화 전략
1. 트랜잭션의 순서 조정: 동일한 테이블에 접근하는 트랜잭션들이 일정한 순서로 잠금을 걸도록 설계하여 데드락을 방지합니다.
2. 잠금 범위 최소화: UPDATE JOIN을 사용하는 경우, 업데이트하는 범위를 좁혀 잠금 시간을 최소화합니다.
3. 트랜잭션 분할: 큰 트랜잭션을 작은 트랜잭션으로 나누어 한 번에 잠기는 레코드 수를 줄입니다.
4. 인덱스 활용: UPDATE JOIN에서 인덱스를 잘 활용하여 잠금 범위를 줄이고, 성능을 향상시킵니다.
5. LOCK TIMEOUT 설정: 데드락 발생 시 빠르게 타임아웃을 설정하여 다른 작업이 수행될 수 있도록 합니다.
6. 트랜잭션 격리 수준 조정: MySQL의 경우 READ COMMITTED 또는 REPEATABLE READ 격리 수준을 사용해 데드락 발생 가능성을 줄일 수 있습니다.

결론

- 성능을 우선: 만약 성능이 매우 중요한 웹서비스라면, UPDATE JOIN을 사용하면서 잠금 범위를 최소화하고 트랜잭션 순서를 조정하여 데드락 가능성을 줄이는 것이 좋습니다.
- 안정성을 우선: 데드락이 빈번하게 발생하는 환경이라면, UPDATE 서브쿼리를 사용하는 것이 좋을 수 있습니다. 이는 잠금이 덜 발생하도록 하고, 트랜잭션을 좀 더 안정적으로 처리할 수 있습니다.

상황에 따라 두 방법을 적절히 사용하고, 필요에 따라 데드락 회피 전략을 적용하는 것이 중요합니다.

출처 : chatgpt