MySQL 성능 개선 프로젝트 3

 

자동으로 주문에서 배송기사를 할당해 배숭 주문 테이블에 데이터를 생성하는 배송 주문 할당 기능을 구현해야 한다.

결제완료에서 상품준비중으로 상태가 변경되면 배송 주문 테이블에 데이터가 생성되어 배송기사가 배송을 시작하면 배송중으로 바뀌는 것이다.

이때 배송기사와 고객간의 채팅 채널도 생성되어야 한다

delimiter //
DROP PROCEDURE if EXISTS insert_delivery_order;
CREATE PROCEDURE insert_delivery_order(pm_order_id bigint)
BEGIN
	DECLARE selected_storage_id BIGINT;
	DECLARE selected_delivery_type VARCHAR(255);
	DECLARE selected_worker_id BIGINT;
	
	SELECT o.storage_id, o.delivery_type, dw.worker_id
	INTO selected_storage_id, selected_delivery_type, selected_worker_id
	FROM orders o
	JOIN delivery_worker dw ON dw.zone_id = o.storage_id
	WHERE o.order_id = pm_order_id 
		AND dw.status = "대기중"
		and dw.working_time = 
			(case
				when delivery_type = "일반배송" then "주간"
				when delivery_type = "새벽배송" then "야간"
			ELSE  "주간"
			END)
		AND dw.today_task < 20
	LIMIT 1;

	INSERT INTO delivery_order(
		order_id,
		worker_id,
		storage_id,
		delivery_type
	) VALUE (
		pm_order_id,
		selected_worker_id,
		selected_storage_id,
		selected_delivery_type
	);
	
	UPDATE delivery_worker SET today_task = today_task+1 WHERE worker_id = selected_worker_id;
	
END //
delimiter ;

 

해당 프로시저에 주문 상태가 상품준비중으로 변경된 주문의 ID값을  입력한다.

그럼 담당 구역에 맞는 배송 기사의 ID값을 변수로 저장해놓는다.

이 과정에서 한 가지 문제점은 여러 배송 기사가 배송 주문을 나눠 할당받아야하지만 한 명의 배송 기사가 배송 주문을 몰아받는다는 것이었다.

그래서 배송 기사 테이블에 그날 할당된 배송 건수를 기록하는 컬럼을 추가하고 일정량에 도달하면 다른 배송기사가 할당받는 식으로 조건을 추가해주었다.

이렇게 배송 주문 테이블에 데이터가 들어가면 해당 배송 주문을 할당받은 배송 기사의 배송 건수를 올린다. 이렇게 배송 주문이 쌓이고 배송 건수가 20에 도달하게되면 다른 배송 기사에게 배송주문이 할당되게 될것이다.

 

delimiter //
DROP PROCEDURE if EXISTS insert_chat_channel;
CREATE PROCEDURE insert_chat_channel(pm_dv_order_id BIGINT)
BEGIN
	DECLARE selected_customer_id BIGINT;
	DECLARE selected_worker_id BIGINT;	
	
	SELECT customer_id, worker_id
	INTO selected_customer_id, selected_worker_id
	FROM delivery_order DO
	JOIN orders o ON DO.order_id = o.order_id
	WHERE DO.dv_order_id = pm_dv_order_id;
	
	
	INSERT INTO chat_channel(
		dv_order_id,
		customer_id,
		worker_id,
		is_active	
	) VALUES(
		pm_dv_order_id,
		selected_customer_id,
		selected_worker_id,
		1
	);
END//
delimiter ;

 

해당 프로시저는 방금 생성된 delivery_order 테이블의 ID값으로 고객과 배송기사간의 채팅 채널을 생성한다.

이 둘을 하나의 프로시저로 다시 묶어서 한번에 실행될 수 있도록 한다.

만약 여기에 새로운 프로세스가 추가되어야 한다면 새로운 프로시저를 만들어서 한번에 실행되게 하면 될것이다.