MySQL deterministic에 대해서(with function)

MySQL을 사용하면서 function을 처음 실무에 사용해봤다. 무려 8년 만에.....

 

그냥 그런게 있다는거만 알고 있었고 심지어 procedure는 아직 써보지를 못했다.

 

진행중인 프로젝트에서 function을 만들게 되었는데 정말 우연히 발견한건데 속도 차이가 나는것을 확인하였고, 

index를 타지 않는다는 원인을 확인 하였다.

 

아래는 만들었던 함수인데 그냥 참고용으로 보면된다. geo_map이라는 테이블 안에서 특정 경로 내의 바운딩 박스를 만들고 그것을 확장시키는 함수이다.

 

그리고 실제 사용처는 확장된 바운딩 박스를 활용해서 그 안에 있는 경로를 찾는 용도이다.

 

DROP FUNCTION if EXISTS calc_bounding_box;

DELIMITER $$
CREATE FUNCTION calc_bounding_box(
	start_id VARCHAR(12), 
	end_id VARCHAR(12)
) RETURNS GEOMETRY
DETERMINISTIC
BEGIN
	DECLARE start_path GEOMETRY;
	DECLARE end_path GEOMETRY; 
	DECLARE combine_path GEOMETRY;
	DECLARE bounding TEXT;
	DECLARE min_bounding GEOMETRY;
	DECLARE expand_bounding GEOMETRY;
	
	SELECT path FROM geo_map WHERE id=start_id
		INTO start_path;
	SELECT path FROM geo_map WHERE id=end_id
		INTO end_path;
		
	#union path
	SELECT ST_UNION(start_path,end_path) 
		INTO combine_path;
		
	#make minimun bounding box
	SELECT ST_ENVELOPE(combine_path)
		INTO min_bounding;
		
	#calculate expanded bounding box using buffer
	SELECT ST_ENVELOPE(ST_Buffer(min_bounding, 0.005, ST_Buffer_Strategy('join_miter',2)))
		INTO expand_bounding;
	
	RETURN expand_bounding;
END $$
DELIMITER;

 

위의 코드를 보면 안에 DETERMINISTIC이라는 문구가 있을 것이다.

 

처음에 해당 문구를 넣지 않으니 속도가 느렸었다. 정확하게는 sql의 where절에 함수를 넣으면 느렸었다.

 

정말 우연히 발견한 것이었는데 코드는 아래와 같다.

 

#INDEX IS NOT working
explain SELECT * FROM geo_map WHERE ST_Intersects(calc_bounding_box("A219AS318004","A219AS318250"),path);

#INDEX is working
SET @search_box = calc_bounding_box("A219AS318004","A219AS318250");
explain SELECT * FROM geo_map WHERE ST_INTERSECTS(@search_box,path);

explain으로 체크를 했으며 DETERMINISTIC을 넣지 않으면 where절 안에 함수를 넣은것은 인덱스를 타지 않는다.

 

지금 약 1800개의 데이터인데 0.05초(인덱스) 와 0.5초(인덱스X) 의 차이로 약 10배 가까이 차이가 났다.

나중에 데이터가 더 늘어나면 차이는 훨씬 심해질거로 보였고... 이리저리 찾은 결과 어쨌든 해결이 되었다.

 

기초가 부족하다고 매일 느끼고 있다 정말.....