인덱스 적용 #919
Replies: 9 comments 1 reply
-
인덱스 사용 기준 잡기인덱스는 검색 속도를 높이기 위해서 사용한다. 하지만 특정 기준을 잡지 않고 인덱스를 사용하면 오히려 성능이 낮아질 수 있다. 어떤 기준으로 인덱스를 설정할까? 1. Cardinality가 높은 컬럼
2.
|
Beta Was this translation helpful? Give feedback.
-
인덱스를 붙였을 때 발생하는 리소스1. 공간 사용:인덱스는 추가적인 데이터 구조이기 때문에 디스크 공간을 더 차지한다. 2. 인덱스 업데이트 비용
데이터베이스가 특정 데이터를 더 쉽게 찾을 수 있도록 만들어둔 "목차" |
Beta Was this translation helpful? Give feedback.
-
인덱스 적용한 부분1. Review 테이블 - review_group_id 컬럼
2. ReviewGroup 테이블 - review_request_code 컬럼
|
Beta Was this translation helpful? Give feedback.
-
인덱스 미적용 부분Templatetemplate은 현재 데이터 2개, 늘어난다고 해도 크게 늘어나지 않음. 따라서 인덱스를 붙이면 아주 작은 차이로 빠르긴하나 체감할 수 없는 정도임. 하지만 인덱스를 붙였을 때의 인지적 부담을 갖고 가야하는 점이 단점으로 느껴져 인덱스를 적용하지 않음
Sectionsection은 현재 데이터 11개, Template과 같은 이유로 인덱스 미적용 QuestionoptionItem은 현재 데이터 20개, Template과 같은 이유로 인덱스 미적용 OptionGroupoptionGroup은 현재 데이터 9개, Template과 같은 이유로 인덱스 미적용 OptionItemoptionItem은 현재 데이터 63개 List<OptionItem> findAllByOptionType(OptionType optionType);
public enum OptionType {
CATEGORY, KEYWORD,
}CheckBoxAnswer, TextBoxAnswerreviewId가 각 테이블의 외래키라 이미 넌클러스트 인덱스가 붙어져있어서 붙일 필요가 없음. TemplateSectionTemplateSection은 현재 데이터 11개, 늘어난다고 해도 크게 늘어나지 않음. 따라서 테이블 규모가 작으므로 인덱스 미적용. SectionQuestionSectionQuestion은 현재 데이터 20개, 늘어난다고 해도 크게 늘어나지 않음. 따라서 테이블 규모가 작으므로 인덱스 미적용. CheckBoxAnswerSelectedOptioncheckbox_answer_id가 fk라 이미 넌클러스트 인덱스가 붙어져있음 |
Beta Was this translation helpful? Give feedback.
-
인덱스 성능 확인을 위한 대량 데이터 삽입서비스가 성장하는 상황을 구성하기 위해 대량의 더미 데이터를 구성한다. MySQL의 Procedure를 통해 SQL을 반복적으로 실행하여 데이터를 추가했다. -- Procedure 생성
DELIMITER $$
CREATE PROCEDURE insert_dummy_review_group_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i <= 100000 DO -- 반복 횟수 설정
INSERT INTO review_group (group_access_code, project_name, review_request_code, reviewee, template_id)
VALUES (
LPAD(FLOOR(RAND() * 10000), 4, '0'), -- group_access_code 4자리
CONCAT('Project_', i),
LPAD(FLOOR(RAND() * 100000000), 8, '0'), -- review_request_code 8자리
CONCAT('Reviewee_', i),
1
);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
리뷰 그룹 더미 데이터 생성을 위한 프로시저를 생성하는 쿼리이다. 생성된 프로시저는 아래 쿼리로 확인 가능하다. -- Procedure 확인
SELECT ROUTINE_NAME
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_SCHEMA = 'review_group';생성된 프로시저는 CALL 함수를 통해 실행할 수 있다. -- Procedure 실행
CALL insert_dummy_review_data(); 프로시저가 실행되면 설정된 반복 횟수만큼 SQL을 실행하여 데이터를 반복적으로 추가한다. |
Beta Was this translation helpful? Give feedback.
-
데이터 추가 전 용량 확인데이터를 얼만큼 추가할 것인지 확인하기 위해 데이터 추가 전 디스크 용량을 확인했다. -- 전체 데이터베이스 용량 조회
SELECT table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'total_Size(MB)',
ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'data_Size(MB)',
ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'index_Size(MB)'
ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema;
-- review_me 데이터베이스의 테이블별 조회
SELECT table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'total_Size(MB)',
ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'data_Size(MB)',
ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'index_Size(MB)'
ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free Space (MB)'
FROM information_schema.tables
WHERE table_schema = 'review_me'
GROUP BY table_schema, TABLE_NAME;
이유를 알아보기 위해 InnoDB의 상태를 확인했다. -- InnoDB 상태 조회
SHOW ENGINE INNODB STATUS;InnoDB 상태를 조회하면 현재 상황에 대한 여러 정보를 확인할 수 있다.
-- 첫번째 조회
Buffer pool size 8191 // 전체 버퍼 공간
Free buffers 5178 // 남은 버퍼 공간
Database pages 2994 // 버퍼에 쌓인 데이터양
Old database pages 1085 // 자주 사용되지 않는 페이지수
Modified db pages 326 // 변경되었으나, 아직 디스크 반영되지 않은 페이지
Pages read 1648, created 1813, written 8402
-- 두번째 조회
Buffer pool size 8191
Free buffers 4971
Database pages 3201
Old database pages 1161
Modified db pages 375
Pages read 1648, created 2020, written 9084버퍼 풀 크기과 메모리에서 관리되는 데이터의 상태 변화를 확인할 수 있다.
-- 첫번째 조회
Log sequence number 107415339
Log written up to 107415339
Log flushed up to 107414959 -- 로그 플러시
Pages flushed up to 100894603 -- 페이지 플러시
Last checkpoint at 100792276 -- 체크포인트
-- 두번째 조회
Log sequence number 122261270
Log written up to 122261270
Log flushed up to 122260892
Pages flushed up to 112219066
Last checkpoint at 111831684트랜잭션 처리 로그를 확인할 수 있다. 페이지 플러시 숫자가 증가한 것을 보아 버퍼에 있는 페이지가 디스크에 flush 된 것을 알 수 있다. 위 정보를 통해 프로시저를 통해 넣은 데이터를 바로 디스크에 넣지 않고, 데이터 버퍼에 추가하고 일정 단위씩 디스크로 flush 하고 있다는 것을 알 수 있었다. (버퍼 풀이 가득찰 경우 디스크로 flush 하여 공간을 만든다.) 그렇다면 디스크에 flush를 했는데 왜 디스크 용량을 그대로 일까? 알아보니 InnoDB는 테이블의 통계를 실시간으로 즉시 업데이트하지 않기 때문에, ANALYZE TABLE review_group;이후 다시 용량 조회 쿼리를 돌려보니 용량이 반영되었다.
|
Beta Was this translation helpful? Give feedback.
-
성능 비교를 위한 실행 결과 확인법인덱스 사용 전후 조회 쿼리 성능 비교를 위해 실행하는 쿼리를 분석할 필요가 있다. 실행하는 쿼리에 EXPLAIN SELECT * FROM review_group rg WHERE review_request_code = '02920153';
EXPLAIN ANALYZE SELECT * FROM review_group rg WHERE review_request_code = '02920153';
쿼리 실행 결과-> Filter:
(rg.review_request_code = '02920153')
(cost=31679 rows=29855)
(actual time=0.0979..169 rows=1 loops=1)
-> Table scan on rg
(cost=31679 rows=298554)
(actual time=0.0468..142 rows=300001 loops=1)
먼저 수행된 단계가 들여쓰기 되어 표시된다. 따라서 Table scan 단계 이후 Filter 단계가 수행되었다. Table scan 단계
Filter 단계
|
Beta Was this translation helpful? Give feedback.
-
인덱스 생성하는 법review_group 테이블에 review_request_code 인덱스를 생성했다. CREATE INDEX review_group_idx_review_request_code ON review_group (review_request_code);테이블에 인덱스가 잘 생성되었는지 다음과 같은 쿼리로 확인할 수 있다. SHOW INDEX FROM review_group;
PK를 기준으로 클러스터드 인덱스가 자동 생성된 것을 볼 수 있고, reviewRequestCode를 기준으로 방금 생성한 보조 인덱스도 확인할 수 있다. |
Beta Was this translation helpful? Give feedback.
-
인덱스 설정 유무에 따른 성능 차이 보기1. review_group 테이블에 review_requets_code 컬럼에 인덱스 적용인덱스 사용 전 쿼리 실행 결과-> Filter:
(rg.review_request_code = '02920153')
(cost=31679 rows=29855)
(actual time=0.0979..169 rows=1 loops=1)
-> Table scan on rg
(cost=31679 rows=298554)
(actual time=0.0468..142 rows=300001 loops=1)인덱스 생성review_group 테이블에 review_request_code 인덱스를 생성했다. CREATE INDEX review_group_idx_review_request_code ON review_group (review_request_code);
Cardinality 컬럼을 보면 인덱스 키가 해당 컬럼에 얼마나 존재하는지 확인 할 수 있다. PK와 동일한 것으로 보아 인덱스 효율이 높을 것으로 예상된다. 인덱스 사용 후 쿼리 실행 결과이제 다시 이전의 쿼리를 실행하고 다음과 같은 결과가 나왔다. EXPLAIN ANASELECT * FROM review_group rg WHERE review_request_code = '02920153'-> Index lookup on rg using review_group_idx_review_request_code
(review_request_code='02920153')
(cost=1.1 rows=1)
(actual time=0.0358..0.0376 rows=1 loops=1)
1.1의 비용으로 1개의 결과가 나올 것으로 예상했다. 결과는 0.0358밀리초(0.038초)의 비용으로 1개의 row를 찾았다. 앞선 결과에서 300,001 개의 행을 스캔하고 1개의 결과를 찾는 데 169밀리초 (0.169초)가 소요되었으나, 인덱스 사용 시 1개의 row 만을 스캔하고 찾는 데 0.0358밀리초(0.0000358초)로 약 4720.67배 더 빨랐다! |
Beta Was this translation helpful? Give feedback.
-
2. review 테이블의 review_group_id 컬럼에 인덱스 적용이번엔 review 테이블의 review_group_id 에 대한 인덱싱을 위해 약 100만 개의 더미 데이터를 생성했다. DELIMITER $$
CREATE PROCEDURE insert_dummy_review_data2()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE review_group_id INT DEFAULT 266; -- review_group_id 시작 값
WHILE i <= 500000 DO
인덱스 생성 쿼리는 다음과 같다 CREATE INDEX review_idx_review_group_id ON review (review_group_id);생성된 인덱스 확인
하지만 review의 review_group_id는 review 내에서 중복될 수 있기 때문에 상대적으로 카디널리티가 낮다. 계산을 해보면 review_group_id로 조회 시, 전체 데이터의 약 14% 정도의 결과가 조회될 것으로 예상된다. 이는 인덱스 손익분기점인 15%로 이내로 보아 풀 스캔 보다 인덱스 효율이 좋을 것으로 예상된다. 인덱스 사용 유무에 따른 실행 결과 비교review의 경우 10만 건의 데이터일 때와 100만 건일 때 각각 비교를 진행했다. 기본 쿼리를 실행하면 이미 인덱스가 생성되었기 때문에 인덱스를 사용한다. EXPLAIN SELECT * FROM review WHERE review_group_id = '123';
인덱스가 사용되지 않은 상태에서 성능도 봐야 하는데 쿼리가 실행되면 인덱스를 사용한다. 쿼리 실행 시, 인덱스를 사용하지 않으려면 어떻게 해야 할까? EXPLAIN ANALYZE SELECT * FROM review IGNORE INDEX (review_idx_review_group_id) WHERE review_group_id = '123';위와 같이 실행 쿼리에 이제, 인덱스 유무에 따른 실행 결과를 비교하자. 10만 건 비교
-> Filter: (r.review_group_id = 56)
(cost=10094 rows=10006)
(actual time=0.075..39.1 rows=12 loops=1)
-> Table scan on r
(cost=10094 rows=100058)
(actual time=0.0621..33 rows=100137 loops=1)
-> Index lookup on r using review_idx_review_group_id
(review_group_id=56)
(cost=4.2 rows=12)
(actual time=0.0551..0.0585 rows=12 loops=1):: cost 약 82% 감소 100만 건 비교
-> Filter:
(r.review_group_id = 56)
(cost=107773 rows=10.3)
(actual time=0.0674..1387 rows=12 loops=1)
-> Table scan on r
(cost=107773 rows=1.07e+6) // 107773
(actual time=0.0555..1320 rows=1.1e+6 loops=1) // 1,100,000
-> Index lookup on r using review_idx_review_group_id
(review_group_id=56)
(cost=5 rows=12)
(actual time=3.36..3.36 rows=12 loops=1)
-> Index lookup on r using review_idx_review_group_id
(review_group_id=300011),
with index condition:
(r.review_group_id = 300011)
(cost=9616 rows=38402)
(actual time=0.152..25.5 rows=19787 loops=1)
-> Filter: (r.review_group_id = 300011)
(cost=3888 rows=38530)
(actual time=0.0353..6.74 rows=19787 loops=1)
-> Covering index lookup on r using review_idx_review_group_id
(review_group_id=300011)
(cost=3888 rows=38530)
(actual time=0.0341..4.93 rows=19787 loops=1)
:: cost 약 97% 감소 |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
인덱스가 왜 필요한가?
Beta Was this translation helpful? Give feedback.
All reactions