이지표 8주차 학습일지

Like after join

평균 RPS 0.3, 실패율 73%

Like and join

평균 RPS 4.7, 실패율 79%

커넥션 풀 100개 (Like and join)

평균 RPS 4.2, 실패율 90%

인덱스 생성

create index idx_restaurants_name on restaurants(name);

create index idx_menus_name on menus(name);

create index idx_restaurants_delivery_location on restaurants(delivery_location);

SELECT r.uuid, r.name, r.minimum_order_amount, COALESCE(rs.average_rating, 0.0), COALESCE(rs.review_count, 0), m.name
FROM restaurants r
LEFT JOIN menus m ON m.restaurant_id = r.id
LEFT JOIN review_statistics rs ON rs.restaurant_id = r.id
WHERE r.delivery_location = '서울특별시_송파구_방이동'
AND (LOWER(r.name) LIKE '%%' ) OR LOWER(m.name) LIKE '%%'
GROUP BY r.id, r.uuid, r.name, r.minimum_order_amount, rs.average_rating, rs.review_count;

실행시간 8초,,,

LIKE %검색어%로 인해 인덱스를 사용하지 않고 전부 풀 스캔을 때린다.

-- 전문 검색 인덱스 추가하기
ALTER TABLE restaurants ADD FULLTEXT INDEX ft_restaurants_name (name);
ALTER TABLE menus ADD FULLTEXT INDEX ft_menus_name (name);

전문 검색 인덱스 추가하기

SELECT r.uuid, r.name, r.minimum_order_amount, COALESCE(rs.average_rating, 0.0), COALESCE(rs.review_count, 0), m.name
FROM restaurants r
LEFT JOIN menus m ON m.restaurant_id = r.id
LEFT JOIN review_statistics rs ON rs.restaurant_id = r.id
WHERE r.delivery_location = '서울특별시_송파구_방이동'
AND MATCH(r.name) AGAINST('치킨' IN BOOLEAN MODE) OR MATCH(m.name) AGAINST('치킨' IN BOOLEAN MODE)
GROUP BY r.id, r.uuid, r.name, r.minimum_order_amount, rs.average_rating, rs.review_count;

30초를 초과하여, connection이 끊김…

쿼리 분리하기

SELECT * 
FROM restaurants r
WHERE r.delivery_location = '서울특별시_송파구_방이동';

SELECT * 
FROM menus m
LEFT JOIN (SELECT * 
			FROM restaurants r
			WHERE (r.delivery_location = '서울특별시_송파구_방이동')) AS r 
ON r.id = m.restaurant_id
WHERE LOWER(m.name) LIKE '%%' AND (LOWER(r.name) LIKE '%%');

굉장히 개선했다!!

SELECT m.*, r.*
FROM menus m JOIN restaurants r ON r.id = 1
WHERE m.name LIKE '%%' OR r.name LIKE '%%';

review_statistics Index 추가

create index idx_review_statistics_restaurant_id on review_statistics(restaurant_id);

SELECT * 
FROM menus m
LEFT JOIN (SELECT id, name, uuid, minimum_order_amount
			FROM restaurants r
			WHERE (r.delivery_location = '서울특별시_송파구_방이동')) AS r 
ON r.id = m.restaurant_id
LEFT JOIN review_statistics rs ON rs.restaurant_id = r.id
WHERE LOWER(m.name) LIKE '%%' OR (LOWER(r.name) LIKE '%%')
GROUP BY r.id, r.uuid, r.name, r.minimum_order_amount, rs.average_rating, rs.review_count;

SELECT r.uuid, r.name, r.minimum_order_amount, COALESCE(rs.average_rating, 0.0), COALESCE(rs.review_count, 0), m.name
FROM restaurants r
LEFT JOIN menus m ON m.restaurant_id = r.id
LEFT JOIN review_statistics rs ON rs.restaurant_id = r.id
WHERE r.delivery_location = '서울특별시_송파구_방이동'
AND (LOWER(r.name) LIKE '%%' ) OR LOWER(m.name) LIKE '%%'
GROUP BY r.id, r.uuid, r.name, r.minimum_order_amount, rs.average_rating, rs.review_count;

SELECT * 
FROM menus m
WHERE m.restaurant_id = 100000;

어떻게하면 인덱스를 타도록 할까?

SELECT r.uuid, r.name, r.minimum_order_amount, COALESCE(rs.average_rating, 0.0), COALESCE(rs.review_count, 0), m.name
FROM (SELECT tm.restaurant_id, tm.name
		FROM restaurants tr
		LEFT JOIN menus AS tm
        ON tr.id = tm.restaurant_id
		WHERE tr.delivery_location = '서울특별시_송파구_방이동') AS m
LEFT JOIN (SELECT id, name, uuid, minimum_order_amount
			FROM restaurants r
			WHERE (r.delivery_location = '서울특별시_송파구_방이동')) AS r 
ON r.id = m.restaurant_id
LEFT JOIN review_statistics rs ON rs.restaurant_id = r.id
WHERE LOWER(m.name) LIKE '%%' OR (LOWER(r.name) LIKE '%%')
GROUP BY r.id, r.uuid, r.name, r.minimum_order_amount, rs.average_rating, rs.review_count;

(12 ~ 17)초 → (0.2 ~ 0.6)초로 개선!!

테스트 결과

최대 유저 100명, 초당 10명 증가!

평균 RPS 27.9, 실패율 0%

평균 응답시간 598ms

테스트 2

최대 유저 10000명, 초당 100명 증가 평균 RPS 45

실패율 19%

테스트3

최대 유저 2000명, 초당 50명 증가

평균 RPS 60, 최대 70

응답시간 27초

새로운 쿼리

SELECT DISTINCT r.id, r.uuid, r.name, r.minimum_order_amount, 
       COALESCE(rs.average_rating, 0.0) AS average_rating, 
       COALESCE(rs.review_count, 0) AS review_count, 
       m.name AS menu_name
FROM restaurants r
LEFT JOIN menus m ON r.id = m.restaurant_id
LEFT JOIN review_statistics rs ON rs.restaurant_id = r.id
WHERE r.delivery_location = '서울특별시_송파구_방이동'
  AND (LOWER(m.name) LIKE '%피자%' OR LOWER(r.name) LIKE '%피자%');

괄호를 추가하니까 모든 인덱스를 사용한다.