개요

소프티어 부트캠프 프로젝트 중 주로 데이터베이스 관련 개발을 도맡아 하면서 쿼리 성능 개선에 도전하였고 그 과정들을 정리해봄
깃헙 링크 : https://github.com/softeerbootcamp/Team1-TDD

IN MySQL

CREATE TABLE

create table locations
(
    id        bigint auto_increment primary key,
    location POINT not null,
    post_id   bigint       not null,
    foreign key (post_id) references posts (id) on update cascade on delete cascade
);

• POINT( "longitude", "latitude")

SELECT DATA

• MySQL에서는 POINT 지오메트리 객체를 16진수로 표현함

SELECT location FROM locations;

• 이 16진수 표현을 위도 및 경도 좌표로 표현하려먼 ST_AsText() 함수를 사용해야 함

SELECT ST_AsText(location) FROM locations;

• 위도, 경도로 따로 보고 싶다면 다음과 같이 사용 (ST_X(), ST_Y())

SELECT ST_X(location) AS longitude, ST_Y(location) AS latitude
FROM locations;

MySQL 에서의 쿼리 성능 비교

• latitude, longitude 를 varchar 타입으로 각각 따로 저장하고 between 연산을 통한 검색
  • 87row -> 평균 0.28 sec

SELECT p.id FROM posts p
JOIN options o ON p.mycar_id = o.mycar_id
JOIN mycars m ON p.mycar_id = m.id
JOIN cars c ON m.car_id = c.id
JOIN appointments a ON p.id = a.post_id
JOIN locations l ON p.id = l.post_id
WHERE o.name IN ('2WD', '현대 스마트 센스')
AND c.id = 1
AND a.date IN('2023-02-22','2023-02-23','2023-02-25','2023-03-01','2023-03-02','2023-03-03', '2023-03-25')
AND l.latitude BETWEEN '36.2' AND '36.4'
AND l.longitude BETWEEN '127.2' AND '128'
AND a.status = 'PENDING'
GROUP BY p.id HAVING COUNT(DISTINCT o.name) = 2;

• 인덱스 설정 하였을 때 : ( latitude, longitude) -> index full scan
  • 87row -> 0.12 sec
• 커버링 인덱스를 설정하고 검색 범위를 좁혔을 때 (위도 36.2 ~ 36.4) -> index range scan
  • 9row -> 0.03 sec
• POINT 타입으로 저장, ST_Contains(), ST_MakeEnvelope() 함수를 통한 검색
  • 87row -> 평균 0.08 sec
  • ST_MakeEnvelope() 함수를 통해 검색 범위를 박스 형태로 만들고
    ST_Contains() 함수를 통해 그 박스 안에 속한 데이터들을 select

SELECT p.id FROM posts p
JOIN options o ON p.mycar_id = o.mycar_id
JOIN mycars m ON p.mycar_id = m.id
JOIN cars c ON m.car_id = c.id
JOIN appointments a ON p.id = a.post_id
JOIN locations l ON p.id = l.post_id
WHERE o.name IN ('2WD', '현대 스마트 센스')
AND c.id = 1
AND a.date IN('2023-02-22','2023-02-23','2023-02-25','2023-03-01','2023-03-02','2023-03-03', '2023-03-25')
AND ST_Contains(ST_MakeEnvelope(Point(127.2, 36.2), Point(128, 36.4)), l.location)
AND a.status = 'PENDING'
GROUP BY p.id HAVING COUNT(DISTINCT o.name) = 2;

• 검색 범위를 좁혔을 때 (위도 36.2 ~ 36.4)
  • 9row -> 0.07 sec
• SPATIAL INDEX 설정 후 (index range scan)
  • 범위 좁혔을 때 -> 0.04 sec
  • 범위 넓을 때 -> 0.10 sec

GIS IN POSTGRESQL

CREATE TABLE

• gist (일반 검색 트리)인덱스 활용

create table locations
(
    id       serial
        primary key,
    location geometry not null,
    geom     geometry
);

create index loc_idx
    on locations using gist (geom);

ST_INTERSECTS

ST_INTERSECTS는 두 GEOMETRY 객체가 공유하는 부분이 있을 때 1을 반환하는 함수이다. GEOMETRY 인자로 GEOMETRYCOLLECTION 타입이 올 경우에는 런타임 에러를 발생시킨다.

구성요소	설명
geom1	GEOMETRY 객체를 나타내는 GEOMETRY TYPE이어야 한다.
geom2	GEOMETRY 객체를 나타내는 GEOMETRY TYPE이어야 한다.

EXPLAIN ANALYZE
SELECT * FROM locations
WHERE ST_Intersects(geom, ST_MakeEnvelope(127.0, 35.0, 128.0, 36.4, 4326));

→ 평균 속도 : 0.08 sec

ST_CONTAINS

ST_CONTAINS는 GEOMETRY 객체 2가 GEOMETRY 객체 1의 외부에 존재하지 않고, 객체 1의 내부에 하나 이상의 포인트가 존재할 때만 1을 반환하는 함수이다. 두 GEOMETRY 객체 모두 GEOMETRY 객체가 아니어야 한다.

구성요소	설명
geom1	포함하는 GEOMETRY 객체를 나타내는 GEOMETRY TYPE이어야 한다.
geom2	포함되는 GEOMETRY 객체를 나타내는 GEOMETRY TYPE이어야 한다.

EXPLAIN ANALYZE
SELECT * FROM locations
WHERE ST_Contains(ST_MakeEnvelope(127.0, 35.0, 128.0, 36.4, 4326), geom);

→ 평균속도 : 0.04 sec

성능 분석 결과 정리

• 데이터 약 10만개
• 위도 35 ~ 36.4
• 경도 127 ~ 128

MySQL

• 0.06 sec (Using between && lat_long_idx - BTREE)
• 0.08 sec (Using Spatial Index - RTREE)

Postgres (Using GIST INDEX)

• 0.08 sec (Using ST_Intersects)
• 0.04 sec (Using ST_Contains)