Table에 생성되어 있는 Indexes 의 용량 확인
select
database_name,
table_name,
index_name,
round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB,
round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis
where stat_name='size'
and table_name = 'TargetTable'
and database_name = 'targetDB'
show table status from database_name; -- databases에 있는 테이블 상세정보 확인
-- https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html 를 참조
-- [MySQL] SHOW TABLE STATUS
by 혀나Lee 2020. 12. 14.
SHOW TABLE STATUS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW TABLE STATUS 는 SHOW TABLES의 동작과 비슷하지만, non-TEMPORARY 테이블에 대한 많은 정보를 보여줍니다. mysqlshow --status db_name을 명령어를 DB에 해당하는 테이블 정보 목록을 가져올 수 있고, LIKE 나 WHERE 조건을 통해서 테이블 Name 에 원하는 조건을 걸어서 조회할 수 있습니다.
columns
Name : 테이블 이름
Engine
: 테이블의 스토리지 엔진. See Chapter 14, The InnoDB Storage Engine, and Chapter 15, Alternative Storage Engines. 파티션을 나눈 테이블의 경우 Engine은 모든 파티션에서 사용하는 스토리지 엔진의 이름을 표시합니다.
Version : 테이블 .frm 파일의 버전
Row_format
: 행 저장 형식 (Fixed, Dynamic, Compresssed, Redundant, Compact).
For MyISAM tables, Dynamic correspondes to what myisamchk -dvv reports as Packed.
InnoDB 테이블 형식은 Antelope 파일 형식을 사용하는 경우 Redundant 또는 Compact 이고 Barracuda 파일 형식을 사용하는 경우 Compressed 또는 Dynamic입니다.
Rows
: The number of rows. MyISAM 과 같은 일부 스토리지 엔진은 정확한 개수를 저장합니다.
InnoDB와 같은 스토리지 엔진의 경우 rows 값이 근사치이며 실제 값에서 40% ~ 50% 다를 수 있습니다. 이러한 경우 SELECT COUNT(*) 로 정확한 수를 구해야합니다.
INFORMATION_SCHEMA의 경우 Rows 값은 NULL이며, InnoDB 테이블의 경우 SQL 최적화에 사용되는 대략적인 추정치입니다.
Avg_row_length : 평균 행 길이.
Data_length
: MyISAM의 경우 data file 의 길이 (Byte).
InnoDB의 경우 클러스터형 인덱스에 할당 된 대략적인 공간 (Byte). ( = clusted index size (페이지 단위) * InnoDB page size )
Max_data_length
: MyISAM의 경우 data file 의 최대 길이. 사용된 data pointer size를 고려하여 테이블에 저장할 수 있는 총 data byte 수
InnoDB는 사용하지 않습니다.
Index_length
: MyISAM의 경우 index file 의 길이 (byte)
InnoDB의 경우 non-clusted index에 할당된 대략적인 공간 (byte). ( = sum of non-clusted index size * InnoDB page size )
Data_free : 할당되었지만 사용되지 않은 byte.
Auto_increment : 다음 AUTO_INCREMENT 값
Create_time : 테이블이 만들어진 시기
Update_time : data file 이 마지막으로 업데이트된 시기.
Check_time : When the table was last checked. Not all storage engines update this time, in which case, the value is always NULL. For partitioned InnoDB tables, Check_time is always NULL.
Collation : The table default collation. The output does not explicitlyu list the table default chracter set, but the collation name begins with the character set name.
Checksum : The live checksum value, if any
Create_options : Extra options used with CRATE TABLE.
Comment : 테이블 생성시에 작성한 comment
Table 최고 사이즈 확인 방법
| SHOW TABLE STATUS FROM DB name LIKE ‘Table name’; |
이 명령어 이후 Create_options에 있는 max_rows와 avg_row_length를 곱한 값이 용량이 된다. MB 단위로 환산하려면 1024로 두번 나누어 주면 된다.
DB 사이즈 확인 방법
SELECT count(*) NUM_OF_TABLE, table_schema,concat(round(sum(table_rows)/1000000,2),’M’) rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA, concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; |
각 테이블 사이즈 확인 방법
SELECT table_name, table_rows, round(data_length/(1024*1024),2) as ‘DATA_SIZE(MB)’, round(index_length/(1024*1024),2) as ‘INDEX_SIZE(MB)’ FROM information_schema.TABLES WHERE table_schema = ‘DB_name’ GROUP BY table_name ORDER BY data_length DESC LIMIT 10; |
- 테이블에 빈 공간이 많다면.. ( data_free )
optimize table 을 시행 해주면 좋은데 이때 Indexes도 rebuild하기 때문에 indexes를 먼저 drop해준다음에 optimize table을 하고 create indexes를 해주는게 총 시간면에서 이득이 있음.
ALTER TABLE 테이블명 ENGINE=InnoDB, ALGORITHM=INPLACE , LOCK=NONE;
-- 5.6 이상 버전 부터는 이렇게 하면... Table Lock 없이 Optimize 된다- show indexes from 테이블명
에서 나온 결과에서 인덱스 컬럼별 cardinality를 주의 깊게 보자.. 이 값이
클수록 다른 값이 많은거고
작을 수록 다른 값이 적은거다.. 그래서 이 값이 큰 걸 위주로 ( 복합키에서는 큰거 부터 작은거 순으로 ) 인덱스를 만들어야지 속도에 이득이 있다.
1. explain의 정의
explain Plan란 SQL을 수행하기전 데이터를 어떻게 가져올 건지에 대한 실행계획을 의미하며
데이터 performance를 확인 하고자 할때 explain Plan 명령어를 사용한다.
2. 사용방법
2.1 ) SELECT 에서 explain 사용하기
select explain을 사용하려면 SELECT 키워드 앞에 explain을 붙여주면된다. 간단하게 user라는 테이블과 author이라는 테이블이 있다라고 할때 두 테이블을 조인한 테이블에 대하여 explain을 주었다.
아래는 두 테이블에 대한 explain 결과이다.
[그림 1]
EXPLAIN SELECT user.user_name, author.authority_seq
ㅠ FROM tb_user_m user JOIN tb_authority_user author ON user.user_seq = author.user_seq ;
explain은 쿼리에 있는 테이블 하나당 한 행씩 출력이 된다.
위에 [그림1] 또한 두개의 테이블을 조인한 결과이기 때문에 두개의 행이 출력되었다.
* 여기서 말하는 테이블은 서브쿼리 일수도 있고, union 결과일 수도 있다.
2.2) SELECT가 아닌 쿼리구문에서 explain 사용하기
select 쿼리에서는 앞에 explain만 붙여줌으로써 실행계획을 확인할 수 있지만 INSERT,UPDATE,DELETE와 같이 입력,수정,삭제와 같은 것들은 실행계획을 확인 할 수 없다.
그렇기때문에 INSERT,UPDATE, DELETE로 작성된 쿼리에서 사용된 칼럼으로 SELECT문으로 재구성시켜줘야한다.
이해하기 좀 어려울 듯해서 아래에 예시를 들어보겠다.
UPDATE tb_user_m SET user_name='najung' ;
라는 update 쿼리가 있다.
위에 나오는 update쿼리구문을
EXPLAIN SELECT user_name FROM tb_user_m ;
으로 바꿔서 실행계획을 확인하는 것이다.
3. explain 컬럼
위 [그림1]을 하나씩 쪼개서 설명해보겠다.
3.1) id 칼럼
[그림2]
id칼럼은 구문에 따라 순차적으로 번호가 부여된다. 현재 JOIN같은 경우 하나의 쿼리에서 두 테이블이 동시에 실행됬기때문에 모든 행에 1이라는 값이 부여됬지만 union이나 서브쿼리가 구문에 들어있다면 위 예제는 달라진다.
아래는 union을 이용한 쿼리이다.
EXPLAIN
SELECT user_name,user_id FROM tb_user_m UNION SELECT result_code, register_time
FROM tb_login_h
이러한 쿼리를 통해 explain했을 때 결과 값은
[그림3]
[그림4]
처음에 나온 1이 tb_user_m에 대한 쿼리 열이고 두번째 2는 tb_login_h에 대한 쿼리열이다.
여기서 뒤에 select type 칼럼을 설명하면서 한번 더 이야기 하겠지만,
mysql은 select 쿼리를 간단한 타입과 복잡한 타입으로 설정한다.
복잡한 타입은 유도된 테이블(sub쿼리), union으로서 복잡한 쿼리를 실행할 경우 id가 복잡해진다.
또한 [그림4] union 경우 3.null부분은 mysql에서 union결과가 임시테이블에 저장되었다가 다시 읽혀지는데 임시테이블은 sql에서 나와있지 않아 컬럼이 null을 가진다.
3.2) select_type 칼럼
select_type은 간단한 쿼리인지 복잡한 쿼리인지 를 나타낸다.
[그림 1] 같이 union이나 서브쿼리가 없을 경우 SIMPLE
[그림 3] 같이 union이나 서브쿼리가 있을 경우에는
가장 밖에 있는 부분은 PRIMARY로 표시되고
나머지는 다음과 같다.
3.2.1) SUBQUERY : 쿼리문장의 FROM절이 아닌 SELECT절에 나타나는 서브쿼리인 경우 SUBQUERY라 한다.
3.2.2) DERIVED : FROM절에 있는 서브쿼리를 표시하는데 사용된다.
3.2.3) UNION : UNION을 사용한 쿼리절일 경우 사용된다.
3.2.4) UNION_RESULT : UNION의 결과 값이 임시테이블에 저장되며, 그 임시테이블을 표현할 때 사용된다.
3.3) table 칼럼
table 칼럼은 어떤 테이블에 접근하는지 보여준다. 대부분의 경우 테이블의 이름이나 sql에서 지정해준 ( ex : AS user ) 값을 가진다.
table 컬럼은 간단한 예제는 이해하기 쉬우나 , 복잡한 쿼리같은 경우 이해하기 어렵다.
아래의 예시를 보자
EXPLAIN
SELECT *
FROM
(SELECT cast(@rnum := @rnum+1 as unsigned) AS seq,
union_result.*
FROM (SELECT @rnum := 0) r,
(
(SELECT article_h.regist_time as time ,
article_h.user_id as id,
article_h.user_institution as institution,
article_h.user_position as position,
article_h.user_name as name,
article_h.register_ip as ip,
concat(article_h.module_name, ' | ', article_h.article_name, ' | ', article_h.action_name) as action_name
FROM tb_article_h article_h
)
UNION ALL
(SELECT login_h.register_time as time,
login_h.user_id as id,
user.user_institution as institution,
code2.code_name AS position,
login_h.user_name as name,
login_h.register_ip as ip,
code.code_name as action
FROM tb_common_code code
INNER JOIN tb_login_h login_h
ON code.code=login_h.result_code,
tb_common_code code2
INNER JOIN tb_user_m user
ON code2.code=user.user_position_code
WHERE user.user_id = login_h.user_id
)
)
union_result
WHERE 1=1
ORDER BY time ASC
) result
WHERE seq BETWEEN 0 AND 10
ORDER BY seq DESC
[그림 5]
[그림 5]에 대한 테이블을 설정하자면 이러하다.
1. id 값이 1인 table명은 derived2 이다. 이러한 테이블 명은 id값이 2이고 select_type이 DERIVED인 테이블을 참조하는 테이블이라고 이해하면 된다. 빨간색으로 표시한 부분이 derived2이다.
2. id값이 2인 table명은 derived3 과 derived4 이다.
이하 동문 ...
* 마지막 10행이 이해가 가지않음
3.4) type 칼럼
mysql 메뉴얼에는 조인 방식이라고 나타내고 있으나 테이블에서 행을 어떻게 찾는지 라고 이해하는게 더 쉽다.
type칼럼 종류를 나쁜 방식에서 좋은 방식 순으로 설명하도록 하겠다.
3.4.1) ALL : mysql이 행을 찾기 위해 처음부터 끝까지 스캔해야한다는 것을 의미한다.
3.4.2) INDEX : 인덱스 순서로 스캔 한다는 것을 제외하면ALL과같다. 일반적인 경우 인덱스가 테이블보다 사이즈가 작기 때문에,ALL보다는 빠를 가능성이 높다. (이해가 잘 되지 않음 )
3.4.3) RANGE : 제한된 형태의 인덱스 스캔이다. RANGE스캔경우 인덱스 특정부분에서 시작해서 특정범위에 있는 값을 가지는 행을 반환한다. 키컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL,<=>, BETWEEN 또는 IN 연산에 사용될 때 적용된다.
SELECT * FROM tb_user_m WHERE user_seq = 10;
SELECT * FROM tb_user_m WHERE user_seq BETWEEN 10 and 20;
SELECT * FROM tb_user_m WHERE user_seq IN (10);
SELECT * FROM tb_user_m WHERE user_seq= 10 AND user_seq IN (20);
3.4.4) ref : 어떤 값 하나에 매치되는 행들을 반환해주는 인덱스 접근방식이다. 인덱스에 매치되는 값이 많지 않은 경우 나쁘지 않다. PRIMARY KEY 또는 UNIQUE 인덱스가 아닐 경우에 ref가 사용되며 = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다.
SELECT * FROM tb_user_m WHERE user_name='손화정';
SELECT * FROM tb_user_m user, tb_login_h login where user.user_id = login.user_id ;
* 기울림된 쿼리 두개의 차이가 이해가 가지 않음
3.4.5) eq_ref : 테이블에서 찾은 값중 단 하나의 값만 해당 테이블에 존재하는 경우 이런 인덱스 탐색법이 사용된다. 기본키 혹은 unique 인덱스에 비교할 떄 이런 접근 방법을 많이 사용한다. eq_ref는 = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용될 수 있다.
SELECT * FROM tb_authority_user author JOIN tb_user_m user ON user.user_seq = author.user_seq ;
3.4.6) const : 쿼리의 일부를 상수로 대체시킬 수 있을 때 사용한다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다.const는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값(constant value)과 비교를 할 때 사용된다.
SELECT * FROM tb_user_m WHERE user_seq= 7
3.4.7) system : 무조건 하나의 열만을 가지고 있는 테이블 . 이것은 const의 특별한 경우이다.
3.5) possible_key 칼럼
possible_keys 컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 possible_key컬럼은 최적화단계에서 시작하기 떄문에 최적화가 끝나는 단계가 진행됨에 따라 쓸모가 없어질 수도 있다.
만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다.
3.6) key칼럼
key 컬럼은 MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다 .
3.7) key_len 컬럼
인덱스 필드가 가질 수 있는 최대의 길이를 출력한다.
3.8) ref 칼럼
키 칼럼에 나와 있는 인덱스에서 찾기 위한 선행 테이블의 어떤 칼럼이 사용되었는지 나타낸다.
3.9) row칼럼
row칼럼 원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할지 예측값을 의미한다.
tb_user_m에 50명의 회원이 있을 때 tb_user_m에 대한 테이블의 row칼럼은 50이 될 것이다.
3.10) extra칼럼
3.10.1)using index : mysql 테이블에 접근하지 않도록 커버링 인덱스를 사용한다는 것을 알려준다.
*커버링 인덱스란 , 쿼리를 실행시키기 위해 필요한 데이터가 모두 포함된 인덱스를 말한다.
3.10.2)using where : mysql서버가 값을 가져온 뒤 행을 필터링 한다는 것을 의미한다.
3.10.3)using temporary : mysql이 쿼리결과를 정렬하기위해 임시테이블을 사용한다는 것을 의미한다.
3.10.4)using filesort : mysql이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이 아니라 외부 정렬을 사용해야한다는 것을 의미한다.
3.10.5)range checked for each record (index map:N) : 적합한 인덱스가 없으므로 각 레코드 조인에서 각 엔덱스들을 재평가한다는 것을 의미한다. MySQL 인덱스에 관해 정리를 하였습니다.
MySQL을 잘 알아서 정리를 한것이 아니라, 잘 알고 싶어서 정리한 것이라 오류가 있을수도 있습니다.
1. 인덱스란?
인덱스 == 정렬
인덱스는 결국 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다.
insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다.
여기서 주의하실 것은 update, delete 행위가 느린것이지, update, delete를 하기 위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회가 됩니다.
인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는것을 추천드립니다.
인덱스 구조
(B-Tree 인덱스 구조)
인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 저장소 순으로 진행됩니다.
예를 들어 Branch (페이지번호 2) 는 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있습니다.
즉, dept_no=d001 and emp_no=10018로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터파일의 주소를 불러와 반환하는 과정을 하게 됩니다.
인덱스의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있습니다.
즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있습니다.
만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존하는 관계가 됩니다.
디스크에서 읽는 것은 메모리에서 읽는것보다 성능이 훨씬 떨어집니다.
결국 인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있습니다.
인덱스의 갯수는 3~4개 정도가 적당합니다.
너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
인덱스 역시 공간을 차지합니다. 많은 인덱스들은 그만큼 많은 공간을 차지합니다.
특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높습니다.
2. 인덱스 키 값의 크기
InnoDB (MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리 됩니다.
(B-Tree 인덱스 구조에서 Root, Branch, Leaf 참고)
페이지는 16KB 로 크기가 고정되어 있습니다.
만약 본인이 설정한 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소(위 인덱스 구조 그림 참고)가 담긴 크기가 12 Byte 정도로 잡으면, 16*1024 / (16+12) = 585로 인해 하나의 페이지에는 585개가 저장될 수 있습니다.
여기서 인덱스 키가 32 Byte로 커지면 어떻게 될까요?
16*1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있게 됩니다.
조회 결과로 500개의 row를 읽을때 16byte일때는 1개의 페이지에서 다 조회가 되지만, 32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발행하게 됩니다.
인덱스의 키는 길면 길수록 성능상 이슈가 있습니다.
3. 인덱스 컬럼 기준
먼저 말씀드릴 것은 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다는 점입니다.
카디널리티(Cardinality)란 해당 컬럼의 중복된 수치를 나타냅니다.
예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기합니다.
반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기합니다.
인덱스로 최대한 효율을 뽑아내려면, 해당 인덱스로 많은 부분을 걸러내야 하기 때문입니다.
만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못합니다.
하지만 주민등록번호나 계좌번호 같은 경우엔 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능합니다.
3-1. 여러 컬럼으로 인덱스 구성시 기준
자 그럼 여기서 궁금한 것이 있습니다.
여러 컬럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성해야 할까요?
카디널리티가 낮은->높은순으로 구성하는게 좋을까요?
카디널리티가 높은->낮은순으로 구성하는게 좋을까요?
실제 실험을 통해 확인해보겠습니다.
테스트 환경은 AWS EC2 Ubuntu 16.04를 사용했습니다.
최대한 극적인 비교를 위해 메모리는 1G, 디스크는 마그네틱(SSD X)을 사용했습니다.
테이블 형태는 아래와 같습니다.
CREATE TABLE `salaries` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`is_bonus` tinyint(1) unsigned zerofill DEFAULT NULL,
`group_no` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
전체 Row는 약 1700만건으로 생성했습니다.
각 컬럼의 카디널리티는 다음과 같습니다.
salaries_카디널리티
자 그럼 인덱스를 2가지 형태로 생성해보겠습니다.
CREATE INDEX IDX_SALARIES_INCREASE ON salaries
(is_bonus, from_date, group_no);
CREATE INDEX IDX_SALARIES_DECREASE ON salaries
(group_no, from_date, is_bonus);
첫번째 인덱스는 is_bonus, from_date, group_no순으로 카디널리티가 낮은순에서 높은순 (중복도가 높은 순에서 낮은순으로) 으로,
두번째 인덱스는 group_no, from_date, is_bonus순으로 카디널리티가 높은순에서 낮은순 (중복도가 낮은 순에서 높은순으로) 으로 생성했습니다.
사용한 쿼리는 다음과 같습니다.
select SQL_NO_CACHE *
from salaries
use index (IDX_SALARIES_INCREASE)
where from_date = '1998-03-30'
and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')
and is_bonus = true;
select SQL_NO_CACHE *
from salaries
use index (IDX_SALARIES_DECREASE)
where from_date = '1998-03-30'
and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')
and is_bonus = true;
옵티마이저가 인덱스를 자동 선택해버리니 Index Hint (use index (IDX_SALARIES_INCREASE)) 로 강제로 인덱스를 사용하도록 하였습니다.
이 인덱스 2개를 총 10회로 테스트하였습니다.
결과가 어떻게 될까요?
IDX_SALARIES_INCREASE IDX_SALARIES_DECREASE
1 110ms 46.9ms
2 89.5ms 24.6ms
3 95.4ms 38.1ms
4 85.6ms 29.3ms
5 83.6ms 29.3ms
6 85.2ms 38.2ms
7 59.4ms 26.1ms
8 64.2ms 29.4ms
9 93.7ms 25.7ms
10 102ms 35.4ms
평균 86.86ms 32.3ms
월등한 차이가 나진 않지만 10회만으로 비교는 가능한것 같습니다.
즉, 여러 컬럼으로 인덱스를 잡는다면 카디널리티가 높은순에서 낮은순으로 (group_no, from_date, is_bonus) 구성하는게 더 성능이 뛰어납니다.
정확한 성능비교를 위해서는 MySQL 캐시 이외에 OS 캐시도 비워야만 했습니다.
그래서 쿼리의 조건 (group_no in ()) 에 포함되는 값들을 하나씩 추가하면서 쿼리가 캐시 안되게 하여 비교하였습니다.
3-2. 여러 컬럼으로 인덱스시 조건 누락
꼭 인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아닙니다.
그렇다면 인덱스 컬럼중 어떤 것들은 누락되어도 되고, 누락되면 안되는 것은 어떤 것일까요?
예를 들어 아래와 같이 인덱스가 잡혀있습니다.
인덱스: group_no, from_date, is_bonus
여기서 중간에 있는 from_date를 제외한 조회 쿼리와 가장 앞에 있는 group_no를 제외한 조회 쿼리를 사용해보겠습니다.
첫번째 조회쿼리의 실행계획을 보면
조회순서1
이렇게 정상적으로 인덱스를 사용했음을 확인할 수 있습니다.
filtered가 10% 인만큼 효율적으로 사용하지는 못했지만, 인덱스를 태울 수 있는 쿼리입니다.
그럼 두번째 조회쿼리의 실행계획은 어떻게 될까요?
조회순서2
전혀 인덱스를 사용하지 못했음을 확인할수 있습니다.
조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야만 합니다.
첫번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 타지 않는다는 점을 기억하시면 됩니다.
4. 인덱스 조회시 주의 사항
between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다.
즉, group_no, from_date, is_bonus으로 인덱스가 잡혀있는데 조회 쿼리를 where group_no=XX and is_bonus=YY and from_date > ZZ등으로 잡으면 is_bonus는 인덱스가 사용되지 않습니다.
범위조건으로 사용하면 안된다고 기억하시면 좀 더 쉽습니다.
반대로 =, in 은 다음 컬럼도 인덱스를 사용합니다.
in은 결국 =를 여러번 실행시킨 것이기 때문입니다.
단, in은 인자값으로 상수가 포함되면 문제 없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생합니다.
in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in 은 체크조건으로 실행되기 때문입니다.
AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.
WHERE 에서 OR을 사용할때는 주의가 필요합니다.
인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용됩니다.
인덱스는 가공된 데이터를 저장하고 있지 않습니다.
where salary * 10 > 150000;는 인덱스를 못타지만, where salary > 150000 / 10; 은 인덱스를 사용합니다.
컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.
null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능
5. 인덱스 컬럼 순서와 조회 컬럼 순서
최근엔 이전과 같이 꼭 인덱스 순서와 조회 순서를 지킬 필요는 없습니다.
인덱스 컬럼들이 조회조건에 포함되어 있는지가 중요합니다.
조회컬럼과인덱스순서
(3-1 실험과 동일한 인덱스에 조회 순서만 변경해서 실행한 결과)
보시는것처럼 조회 컬럼의 순서는 인덱스에 큰 영향을 끼치지 못합니다.
단, 옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정이 추가되지만 거의 차이가 없긴 합니다.
(그래도 이왕이면 맞추는게 조금이나마 낫겠죠?)
6. 페이징 성능 개선 팁
위 인덱스 지식을 통해 페이징 성능 개선을 원하시는 분들은 기존의 포스팅을 참고하시면 좋습니다.
NoOffset 사용하기
커버링 인덱스 사용하기
count 쿼리 최적화 1, 22. 커버링 인덱스 사용하기
앞서 1번글 처럼 No Offset 방식으로 개선할 수 있다면 정말 좋겠지만, NoOffset 페이징을 사용할 수 없는 상황이라면 커버링 인덱스로 성능을 개선할 수 있습니다.
커버링 인덱스란 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 이야기합니다.
즉, select, where, order by, limit, group by 등에서 사용되는 모든 컬럼이 Index 컬럼안에 다 포함된 경우인데요.
여기서 하나의 의문이 드는 것은 select절까지 포함하게 되면 너무 많은 컬럼이 인덱스에 포함되지 않겠냐는 것인데요.
그래서 실제로 커버링 인덱스를 태우는 부분은 select를 제외한 나머지만 우선으로 수행합니다.
예를 들어 아래와 같은 페이징 쿼리를
SELECT *
FROM items
WHERE 조건문
ORDER BY id DESC
OFFSET 페이지번호
LIMIT 페이지사이즈
아래처럼 처리한 코드를 이야기합니다.
SELECT *
FROM items as i
JOIN (SELECT id
FROM items
WHERE 조건문
ORDER BY id DESC
OFFSET 페이지번호
LIMIT 페이지사이즈) as temp on temp.id = i.id
위 쿼리에서 커버링 인덱스가 사용된 부분이 JOIN에 있는 쿼리입니다.
(아래 쿼리입니다.)
SELECT id
FROM items
WHERE 조건문
ORDER BY id DESC
OFFSET 페이지번호
LIMIT 페이지사이즈
select절을 비롯해 order by, where 등 쿼리 내 모든 항목이 인덱스 컬럼으로만 이루어지게 하여 인덱스 내부에서 쿼리가 완성될 수 있도록 하는 방식인데요.
이렇게 커버링 인덱스로 빠르게 걸러낸 row의 id를 통해 실제 select 절의 항목들을 빠르게 조회해오는 방법입니다.
2-1. 커버링 인덱스는 왜 빠른가?
일반적으로 인덱스를 이용해 조회되는 쿼리에서 가장 큰 성능 저하를 일으키는 부분은 인덱스를 검색하고 대상이 되는 row의 나머지 컬럼값을 데이터 블록에서 읽을 때 입니다.
페이징 쿼리와 무관하게 인덱스를 탔음에도 느린 쿼리의 경우 이 select절 항목 때문입니다.
이를테면 커버링 인덱스를 태우지 않은 일반적인 조회 쿼리는 order by, offset ~ limit 을 수행할때도 데이터 블록으로 접근을 하게 됩니다.
covering_intro
반대로 커버링 인덱스 방식을 이용하면, where, order by, offset ~ limit 을 인덱스 검색으로 빠르게 처리하고, 이미 다 걸러진 10개의 row에 대해서만 데이터 블록에 접근하기 때문에 성능의 이점을 얻게 됩니다.
covering_intro2
select, where, order by, group by, having 등에 사용되는 컬럼에 대한 커버링 인덱스의 적용 방식은 이전에 작성된 커버링 인덱스 시리즈를 참고하시면 좋습니다.
1. 커버링 인덱스 (기본 지식 / WHERE / GROUP BY)
2. 커버링 인덱스 (WHERE + ORDER BY / GROUP BY + ORDER BY )
예를 들어 앞서 1번에서 사용된 기존의 페이징 쿼리는
select id, book_no, book_type, name
from book
where name like '200%'
order by id desc
limit 10 offset 10000;
select에서 사용된 book_no, book_type이 인덱스 (idx_book_1(name))에 포함되지 않기 때문에 커버링 인덱스가 될 수가 없습니다.
id도 인덱스에 없지 않나요? 라고 질문하신다면 앞 글에서도 언급하고 있지만 클러스터 인덱스(PK)인 id는 모든 인덱스에 자동 포함됩니다.
그래서 쿼리에서 오래걸리는 페이징 작업까지는 커버링 인덱스로 빠르게 처리후, 마지막 필요한 컬럼들만 별도로 가져오는 형태를 사용합니다.
2-2. 구현 코드
자 그럼 이제 한번 실제로 코드를 구현해볼텐데요.
이는 2가지 버전으로 보여드리겠습니다.
Querydsl-JPA
먼저 querydsl-jpa에서 커버링 인덱스를 사용해야 한다면 2개의 쿼리로 분리해서 진행할 수 밖에 없습니다.
이유는 Querydsl-jpa에서는 from절의 서브쿼리를 지원하지 않기 때문인데요.
JPQL자체에서 지원하지 않습니다.
그래서 이를 우회할 수 있는 방법으로 소개드립니다.
커버링 인덱스를 활용해 조회 대상의 PK를 조회
해당 PK로 필요한 컬럼항목들 조회
Querydsl-jpa로 구현하면 다음과 같습니다.
public List<BookPaginationDto> paginationCoveringIndex(String name, int pageNo, int pageSize) {
// 1) 커버링 인덱스로 대상 조회
List<Long> ids = queryFactory
.select(book.id)
.from(book)
.where(book.name.like(name + "%"))
.orderBy(book.id.desc())
.limit(pageSize)
.offset(pageNo * pageSize)
.fetch();
// 1-1) 대상이 없을 경우 추가 쿼리 수행 할 필요 없이 바로 반환
if (CollectionUtils.isEmpty(ids)) {
return new ArrayList<>();
}
// 2)
return queryFactory
.select(Projections.fields(BookPaginationDto.class,
book.id.as("bookId"),
book.name,
book.bookNo,
book.bookType))
.from(book)
.where(book.id.in(ids))
.orderBy(book.id.desc())
.fetch(); // where in id만 있어 결과 정렬이 보장되지 않는다.
}
(1) select에는 id만 포함하여 커버링 인덱스를 활용해 빠르게 조회
(1-1) 조회 결과 id가 없으면 빈값 반환
querydsl 특성상 in에 Empty List가 들어가게 되면 오류가 발생 하는 것을 방지
불필요하게 추가 쿼리 발생하는 것을 방지
(2) 1의 결과로 발생한 id로 실제 select절 조회
where id in () 밖에 없기 때문에 정렬 된 상태로 조회 되지 않으므로 .orderBy(book.id.desc())를 추가
아무래도 한번의 쿼리로 해결하지 못하다보니 조금 번잡한 느낌은 듭니다.
JdbcTemplate
두번째로 jdbcTemplate을 이용하여 문자열 쿼리를 직접 사용하는 방식인데요.
이렇게 진행하시게 되면 querydsl-jpa를 쓸때처럼 쿼리르 분리할 필요 없이, 커버링 인덱스를 from절에 그대로 사용하시면 됩니다.
public List<BookPaginationDto> paginationCoveringIndexSql(String name, int pageNo, int pageSize) {
String query =
"SELECT i.id, book_no, book_type, name " +
"FROM book as i " +
"JOIN (SELECT id " +
" FROM book " +
" WHERE name LIKE '?%' " +
" ORDER BY id DESC " +
" LIMIT ? " +
" OFFSET ?) as temp on temp.id = i.id";
return jdbcTemplate
.query(query, new BeanPropertyRowMapper<>(BookPaginationDto.class),
name,
pageSize,
pageNo * pageSize);
}
다만 jdbcTemplate를 이용한 방식은 querydsl이 갖고 있는 컴파일체크/자동완성 등 장점이 완전히 사라지기 때문에 개인적으로 선호하진 않습니다.
2-3. 성능 비교
자 그럼 이렇게 개선된 방식으로는 성능이 얼마나 좋아질까요?
실행 계획과 함께 비교해보겠습니다.
아래와 같은 환경에서 비교를 해봅니다.
테스트 DB
AWS RDS Aurora MySQL r5.large
테스트 테이블
1억 row
5개 컬럼
기존 페이징
먼저 기존의 페이징방식입니다.
1편과 동일합니다.
@Test
void 기존_페이징_방식() throws Exception {
//given
String name = "200";
//when
List<BookPaginationDto> books = bookPaginationRepository.paginationLegacy(name, 10_000, 10);
//then
assertThat(books).hasSize(10);
}
explain_legacy
result_legacy
실제 수행 시간은 26.47초 입니다.
Querydsl 커버링 인덱스
2번째는 커버링 인덱스로 pk조회 & 컬럼 조회로 이루어지는 Querydsl 입니다.
@Test
void 커버링인덱스() throws Exception {
//given
String name = "200";
//when
List<BookPaginationDto> books = bookPaginationRepository.paginationCoveringIndex(name, 1, 10);
//then
assertThat(books).hasSize(10);
}
explain_querydsl
result_querydsl
앞서 기존 방식에 비해 말도 안되게 개선된 0.57초 입니다.
JdbcTemplate 커버링 인덱스
마지막으로 한번의 쿼리로 수행되는 JdbcTemplate 방식입니다.
@Test
void 커버링인덱스_jdbc() throws Exception {
//given
String name = "200";
//when
List<BookPaginationDto> books = bookPaginationRepository.paginationCoveringIndexSql(name, 1, 10);
//then
assertThat(books).hasSize(10);
}
explain_jdbc
result_jdbc
마찬가지로 앞서 기존 방식에 비해 말도 안되게 개선된 0.27초 입니다.
즉, Querydsl을 이용한 방식이나 JdbcTemplate을 이용한 방식 모두다 기존 페이징 방식에 비해 엄청나게 개선된 성능을 자랑하는 것을 확인할 수 있습니다.
2-4. 단점
커버링 인덱스 방식은 일반적인 페이징 방식에서는 거의 대부분 적용할 수 있는 효과적인 개선 방법인 것을 확인하였는데요.
No Offset 방식처럼 UX의 변경 등이 필요하지도 않다보니 단점이 없어보이지만 실제로는 몇개의 단점이 있습니다.
너무 많은 인덱스가 필요하다
결국 쿼리의 모든 항목이 인덱스에 포함되어야하기 때문에 느린 쿼리가 발생할때마다 인덱스가 신규 생성될 수도 있다.
인덱스 크기가 너무 커진다
인덱스도 결국 데이터이기 때문에 너무 많은 항목이 들어가면 성능 상 이슈가 발생할 수 밖에 없는데, where절에 필요한 컬럼외에도 order by, group by, having 등에 들어가는 컬럼들까지 인덱스에 들어가게 되면 인덱스 크기가 너무 비대해진다.
데이터 양이 많아지고, 페이지 번호가 뒤로 갈수록 NoOffset에 비해 느리다.
시작 지점을 PK로 지정하고 조회하는 NoOffset 방식에 비해서 성능 차이가 있음 (NoOffset과 동일한 데이터 조회시 커버링 인덱스 방식은 272ms, No Offset은 83ms)
테이블 사이즈가 계속 커지면 No Offset 방식에 비해서는 성능 차이가 발생
대부분의 페이징 쿼리는 No Offset과 커버링 인덱스로 최적화가 됩니다.앞서 포스팅에서 실질 페이징 쿼리 성능을 올리는 방법들을 소개 드렸는데요.
1. 페이징 성능 개선하기 - No Offset 사용하기
2. 페이징 성능 개선하기 - 커버링 인덱스 사용하기
페이징 기능을 구현하는데 있어, 페이징 쿼리 자체를 개선하는 것도 방법이지만 그 외 다른 기능을 개선하는 방법도 함께할 수 있습니다.
여기서 말하는 그 외 기능은 바로 count 쿼리입니다.
일반적인 페이징 기능에 있어 데이터 조회와 함께 매번 함께 수행되는 것이 바로 count 쿼리인데요.
해당 조건으로 조회되는 총 건수를 알아야만 아래와 같이 pageNo들을 노출시킬 수 있기 때문입니다.
(총 건수 / pageSize)
count_no
당연히 No Offset을 사용한다면 사용되지 않는 쿼리입니다.
여기서 count 쿼리에 대해 크게 생각하지 않으시는 분들도 계시는데요.
(조회 건수에 따라 차이가 나지만) 실제 데이터 조회만큼 오래 걸리기도 합니다.
이유는 총 몇건인지 확인하기 위해 전체를 확인해야하기 때문입니다.
데이터 조회는 limit 10 등으로 지정된 사이즈만큼 읽고 나서는 더이상 읽지 않아도 되지만, count는 끝까지 읽어서 몇 건인지 확인해야하기 때문에 페이징 쿼리의 성능 이슈 중 하나가 됩니다.
legacy_time
(어떤 조회 환경에서는 count 쿼리만 10초가 걸리기도 합니다.)
simple_query
(이렇게 단순한 쿼리도 1억건 이상일 경우 1초 이상 수행되기도 합니다.)
그래서 이 문제를 개선할 수 있는 방법은 크게 2가지가 있습니다.
검색 버튼 사용시 페이지 건수 고정하기
첫 페이지 조회 결과 cache 하기
하나씩 알아보겠습니다.
3-1. 검색 버튼 사용시 페이지 건수 고정하기
구글 검색을 이용해보신 분들은 간혹 경험하실텐데요.
처음 검색 버튼을 클릭 했을때는 6페이지 혹은 10페이지로 보던 검색 결과가
google_before
실제 페이지 버튼을 클릭해서 넘어가면 페이지 결과가 줄어드는 것을 볼 수 있는데요.
google_after
여기에서 컨셉을 참고할 수 있습니다.
굳이 사용율이 떨어지는 페이지 버튼을 위해 매번 전체 count 쿼리가 수행될 필요가 있을까를 한번 고민해볼 필요가 있는데요.
실제로 구글의 검색 페이지 결과가 어떻게 구현되어있는지는 알 수 없기 때문에 컨셉만 참고했다는 것을 말씀드립니다.
구글 같이 엄청나게 방대한 데이터를 적재해서 제공하는 서비스에서 이렇게 단순한 RDBMS 를 사용하진 않을테니 "구글이 이렇게 하더라" 라고 생각하시면 안됩니다.
즉, 다음과 같은 상황에서 이 방법을 고려해보시면 좋습니다.
대부분의 조회 요청이 검색 버튼 클릭 (즉, 첫 조회)에서 발생하고
페이지 버튼을 통한 조회 요청이 소수일 경우
이럴 경우 검색 버튼을 클릭한 경우만 Page 수를 고정하는 것 입니다.
즉, 다음 페이지로 이동하기 위해 페이지 버튼을 클릭했을때만 실제 페이지 count 쿼리를 발생시켜 정확한 페이지수를 사용하고, 대부분의 요청이 발생하는 검색 버튼 클릭시에는 count 쿼리를 발생시키지 않는 것 입니다.
자 그럼 실제 코드를 한번 보겠습니다.
3-1-1. 구현 코드
먼저 기존 페이징 쿼리는 아래와 같습니다.
public Page<BookPaginationDto> paginationCount(Pageable pageable, String name) {
JPQLQuery<BookPaginationDto> query = querydsl().applyPagination(pageable,
queryFactory
.select(Projections.fields(BookPaginationDto.class,
book.id.as("bookId"),
book.name,
book.bookNo,
book.bookType
))
.from(book)
.where(
book.name.like(name + "%")
)
.orderBy(book.id.desc()));
List<BookPaginationDto> items = query.fetch(); // 데이터 조회
long totalCount = query.fetchCount(); // 전체 count
return new PageImpl<>(items, pageable, totalCount);
}
private Querydsl querydsl() {
return Objects.requireNonNull(getQuerydsl());
}
이 코드를 검색 버튼 클릭시에는 10개 페이지를 고정으로 노출하도록 개선하기 위해서는 다음의 코드가 추가되어야 하는데요.
검색 버튼 클릭한 경우(useSearchBtn)에는 10개 페이지가 노출되도록 TotalCount (fixedPageCount) 를 반환한다.
페이지 버튼을 클릭한 경우 실제 쿼리를 수행해 결과를 반환한다
페이지 버튼을 클릭하였지만, 전체 건수를 초과한 페이지 번호로 요청이 온 경우에는 마지막 페이지 결과를 반환한다.
마지막 3번이 조금 복잡한 로직인데,
이런 경우가 발생하는 이유는 다음과 같습니다.
1번으로 인해서 노출된 페이지 번호는 10개
실제 전체 건수와 무방하게 강제로 10개 페이지를 노출시켰기 때문에 사용자는 언제든 10번째 페이지 번호를 클릭할 수 있음
10번째 페이지를 클릭했는데, 막상 전체 데이터가 그만큼 안된다면 (ex: 전체 건수가 70개라면 pageSize=10 라서 실제 전체 페이지 수가 7개밖에 안되는 경우) 노출할 데이터가 없습니다.
자 그래서 이들을 다 적용하게 되면 다음의 코드가 됩니다.
public Page<BookPaginationDto> paginationCountSearchBtn(boolean useSearchBtn, Pageable pageable, String name) {
JPAQuery<BookPaginationDto> query = queryFactory
.select(Projections.fields(BookPaginationDto.class,
book.id.as("bookId"),
book.name,
book.bookNo,
book.bookType
))
.from(book)
.where(
book.name.like(name + "%")
)
.orderBy(book.id.desc());
JPQLQuery<BookPaginationDto> pagination = querydsl().applyPagination(pageable, query);
if(useSearchBtn) { // 검색 버튼 사용시
int fixedPageCount = 10 * pageable.getPageSize(); // 10개 페이지 고정
return new PageImpl<>(pagination.fetch(), pageable, fixedPageCount);
}
long totalCount = pagination.fetchCount();
Pageable pageRequest = exchangePageRequest(pageable, totalCount); // 데이터 건수를 초과한 페이지 버튼 클릭시 보정
return new PageImpl<>(querydsl().applyPagination(pageRequest, query).fetch(), pageRequest, totalCount);
}
Pageable exchangePageRequest(Pageable pageable, long totalCount) {
/**
* 요청한 페이지 번호가 기존 데이터 사이즈를 초과할 경우
* 마지막 페이지의 데이터를 반환한다
*/
int pageNo = pageable.getPageNumber();
int pageSize = pageable.getPageSize();
long requestCount = (pageNo - 1) * pageSize; // pageNo:10, pageSize:10 일 경우 requestCount=90
if (totalCount > requestCount) { // 실제 전체 건수가 더 많은 경우엔 그대로 반환
return pageable;
}
int requestPageNo = (int) Math.ceil((double)totalCount/pageNo); // ex: 71~79이면 8이 되기 위해
return PageRequest.of(requestPageNo, pageSize);
}
여기서 exchangePageRequest() 메소드를 좀 더 객체지향적으로 분리하기 위해 별도의 Dto 클래스로 추출할 수도 있습니다.
public class FixedPageRequest extends PageRequest {
protected FixedPageRequest(Pageable pageable, long totalCount) {
super(getPageNo(pageable, totalCount), pageable.getPageSize(), pageable.getSort());
}
private static int getPageNo(Pageable pageable, long totalCount) {
int pageNo = pageable.getPageNumber();
int pageSize = pageable.getPageSize();
long requestCount = pageNo * pageSize; // pageNo:10, pageSize:10 일 경우 requestCount=90
if (totalCount > requestCount) { // 실제 건수가 요청한 페이지 번호보다 높을 경우
return pageNo;
}
return (int) Math.ceil((double)totalCount/pageNo); // 실제 건수가 부족한 경우 요청 페이지 번호를 가장 높은 번호로 교체
}
}
이렇게 할 경우 Repository는 다음처럼 개선됩니다.
public Page<BookPaginationDto> paginationCountSearchBtn2(boolean useSearchBtn, Pageable pageable, String name) {
JPAQuery<BookPaginationDto> query = queryFactory
.select(Projections.fields(BookPaginationDto.class,
book.id.as("bookId"),
book.name,
book.bookNo,
book.bookType
))
.from(book)
.where(
book.name.like(name + "%")
)
.orderBy(book.id.desc());
JPQLQuery<BookPaginationDto> pagination = querydsl().applyPagination(pageable, query);
if(useSearchBtn) {
int fixedPageCount = 10 * pageable.getPageSize(); // 10개 페이지 고정
return new PageImpl<>(pagination.fetch(), pageable, fixedPageCount);
}
long totalCount = pagination.fetchCount();
Pageable pageRequest = new FixedPageRequest(pageable, totalCount);
return new PageImpl<>(querydsl().applyPagination(pageRequest, query).fetch(), pageRequest, totalCount);
}
자 이렇게 됨으로써 "3. 전체 건수를 초과한 페이지 요청에는 마지막 페이지 결과 반환"에 대해서는 FixedPageRequest 클래스가 담당하게 되었으니 테스트 코드 역시 별도로 진행할 수 있게 되었습니다.
그럼 바로 테스트 코드를 보겠습니다.
3-1-2. 테스트 코드
먼저 테스트 해볼 것은 FixedPageRequest 클래스 입니다.
앞서 설명 드린것처럼 FixedPageRequest 는 "전체 건수를 초과한 페이지 번호 요청에는 마지막 페이지 요청"으로 변환하는 역할을 합니다.
@ParameterizedTest
@CsvSource({
"10, 100, 10", // (1)
"10, 101, 10", // (2)
"10, 91, 10", // (3)
"10, 90, 9", // (4)
"10, 79, 8"}) // (5)
void dto_exchange_page_request(int pageNo, long totalCount, int expectedPageNo) throws Exception {
//given
Pageable pageRequest = PageRequest.of(pageNo, 10);
//when
Pageable result = new FixedPageRequest(pageRequest, totalCount);
//then
assertThat(result.getPageNumber()).isEqualTo(expectedPageNo);
}
검증 케이스는 다음과 같습니다.
(1) 페이지번호:10 / 전체 건수: 100 / 변환 후 받은 페이지 번호: 10
(2) 페이지번호:10 / 전체 건수: 101 / 변환 후 받은 페이지 번호: 10
(3) 페이지번호:10 / 전체 건수: 91 / 변환 후 받은 페이지 번호: 10
(4) 페이지번호:10 / 전체 건수: 90 / 변환 후 받은 페이지 번호: 9
(5) 페이지번호:10 / 전체 건수: 79 / 변환 후 받은 페이지 번호: 8
바로 테스트를 돌려보면?
dto-test-result
정상적으로 통과하는 것을 확인할 수 있습니다.
자 그럼 Repository 테스트를 해볼텐데요.
Repository 테스트는 2개로 나뉩니다.
실제 건수와 무관하게 10개 페이지의 개수가 리턴되는 케이스
@Test
void 검색버튼사용시_10개_페이지_건수가_리턴된다() throws Exception {
PageRequest pageRequest = PageRequest.of(1, 10);
boolean useSearchBtn = true;
Page<BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountSearchBtn(useSearchBtn, pageRequest, prefixName);
//then
assertThat(page.getTotalElements()).isEqualTo(100); // 10 (pageCount) * 10 (pageSize)
}
search_btn_result
실제 건수가 리턴되는 케이스
@Test
void 페이지버튼사용시_실제_페이지_건수가_리턴된다() throws Exception {
PageRequest pageRequest = PageRequest.of(1, 10);
boolean useSearchBtn = false;
Page<BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountSearchBtn(useSearchBtn, pageRequest, prefixName);
//then
assertThat(page.getTotalElements()).isEqualTo(30);
}
page_btn_result
Repository의 테스트 코드 역시 정상적으로 수행 되는 것을 확인할 수 있습니다.
3-1-3. 결론
실제 제가 진행했던 몇몇 프로젝트에서는 검색 버튼을 클릭하는 경우가 검색의 80%를 넘기도 했는데요.
페이지 버튼을 클릭하는 경우가 전체 검색에서 20%도 안되는 상황에서 매번 10초대의 쿼리가 수행되는 것은 부담스러운 일입니다.
이번 방법은 이런 경우에 많은 효과를 볼 수 있습니다.지난 시간에 이어 count와 관련된 2번째 개선 방법은 첫 번째 쿼리의 결과를 Cache하기 인데요.
방법은 간단합니다.
처음 검색시 조회된 count 결과를 응답결과로 내려주어 JS에서 이를 캐싱하고, 매 페이징 버튼마다 count 결과를 함께 내려주는 것입니다.
그리고 Repository에서는 요청에 넘어온 항목 중, 캐싱된 count값이 있으면 이를 재사용하고, 없으면 count 쿼리를 수행합니다.
query_cache
이미지 원작자님께 허락을 받고 사용하였습니다. :) (다시 한번 감사드립니다!)
이 방식은 다음과 같은 상황에서 도움이 되는데요.
조회 요청이 검색 버튼과 페이지 버튼 모두에서 골고루 발생하고
실시간으로 데이터 적재 되지 않고, 마감된 데이터를 사용할 경우
이럴 경우에 사용하신다면 매 페이지 버튼 클릭시마다 발생하는 count 쿼리를 처음 1회만 호출되고 이후부터는 호출되지 않아 count 성능을 향상 시킬 수 있습니다.
물론 JS에서 캐싱하고 있기 때문에 브라우저를 새로고침하게 되면 count는 다시 초기화가 되어 이후 첫 조회시 다시 쿼리가 수행되게 됩니다.
자 그럼 실제 코드를 한번 보겠습니다.
3-2-1. 구현 코드
지난 시간에 이어서 이번에도 역시 기존 페이징 쿼리는 동일합니다.
public Page<BookPaginationDto> paginationCount(Pageable pageable, String name) {
JPQLQuery<BookPaginationDto> query = querydsl().applyPagination(pageable,
queryFactory
.select(Projections.fields(BookPaginationDto.class,
book.id.as("bookId"),
book.name,
book.bookNo,
book.bookType
))
.from(book)
.where(
book.name.like(name + "%")
)
.orderBy(book.id.desc()));
List<BookPaginationDto> items = query.fetch(); // 데이터 조회
long totalCount = query.fetchCount(); // 전체 count
return new PageImpl<>(items, pageable, totalCount);
}
private Querydsl querydsl() {
return Objects.requireNonNull(getQuerydsl());
}
이 코드를 검색/페이징 버튼 클릭시에 cache된 count를 사용하도록 개선하기 위해서는 다음의 코드가 추가되어야 하는데요.
프론트 영역에서 넘겨준 count값이 요청 필드에 포함시킨다.
Repository에서는 해당 count 값이 있을 경우엔 그대로 페이징 결과에 포함시키고, 없으면 실제 count 쿼리를 실행한다.
굉장히 심플한 로직이기에 바로 해당 로직을 적용해보겠습니다.
물론 JS영역 (혹은 모바일 앱)에서 응답객체의 count값을 저장하는 것도 추가되어야 합니다.
여기서는 Querydsl에서 해야하는 것만 소개드립니다.
// (1)
public Page<BookPaginationDto> paginationCountCache(Long cachedCount, Pageable pageable, String name) {
JPQLQuery<BookPaginationDto> query = querydsl().applyPagination(pageable,
queryFactory
.select(Projections.fields(BookPaginationDto.class,
book.id.as("bookId"),
book.name,
book.bookNo,
book.bookType
))
.from(book)
.where(
book.name.like(name + "%")
)
.orderBy(book.id.desc()));
List<BookPaginationDto> elements = query.fetch(); // 데이터 조회
long totalCount = cachedCount != null ? cachedCount : query.fetchCount(); // (2) 전체 count
return new PageImpl<>(elements, pageable, totalCount);
}
private Querydsl querydsl() {
return Objects.requireNonNull(getQuerydsl());
}
(1) Long cachedCount
프론트 영역에서 넘겨준 count 값입니다.
(2) cachedCount != null ? cachedCount : query.fetchCount()
Request로 넘어온 cachedCount가 있으면, 해당 값을, 없으면 실제 count 쿼리를 수행시켜 사용합니다.
자 그럼 한번 정상적으로 작동하는지 테스트 해보겠습니다.
3-2-2. 테스트 코드
먼저 cached된 경우입니다.
@BeforeEach
void setUp() {
for (int i = 1; i <= 30; i++) {
bookRepository.save(Book.builder()
.name(prefixName +i)
.bookNo(i)
.build());
}
}
@Test
void cache된_count를_사용한다() throws Exception {
PageRequest pageRequest = PageRequest.of(1, 10);
Long cachedCount = 100L;
Page<BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountCache(cachedCount, pageRequest, prefixName);
//then
assertThat(page.getTotalElements()).isEqualTo(cachedCount);
}
위 테스트를 돌려보면?
test_1
정상적으로 결과가 반환되는 것을 확인할 수 있습니다.
자 그럼 2번째로 cache가 없을 경우 실제 count 값을 사용하는 경우를 테스트해보겠습니다.
@BeforeEach
void setUp() {
for (int i = 1; i <= 30; i++) {
bookRepository.save(Book.builder()
.name(prefixName +i)
.bookNo(i)
.build());
}
}
@Test
void cache된_count를_사용한다() throws Exception {
PageRequest pageRequest = PageRequest.of(1, 10);
Long cachedCount = 100L;
Page<BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountCache(cachedCount, pageRequest, prefixName);
//then
assertThat(page.getTotalElements()).isEqualTo(cachedCount);
}
위 테스트를 수행해보면?
test_2
원하던대로 실제 count가 반환되는 것을 확인할 수 있습니다.
3-2-3. 결론
한번 조회된 동일 조건의 count에 대해서는 클라이언트 영역에서 저장후 요청시마다 재사용하는 방식을 사용하게 되면 추가적인 쿼리 요청이 최소화 된다는 장점이 있습니다.
반면 다음과 같은 단점도 있는데요.
첫 페이지 조회가 대부분일 경우 효과가 없습니다.
추가적인 페이징 조회가 필요하지 않으면 결국 매번 첫 조회라서 cache count를 사용할 수가 없습니다.
실시간으로 데이터 수정이 필요해 페이지 버튼 반영이 필요한 경우 사용할 수 없습니다.
결국 새로고침 (or 버튼 클릭을 통한 페이지 이동) 하기 전까지는 페이지 버튼들이 계속 그대로 유지 되기 때문에 실시간성이 떨어집니다.
마감된 데이터 혹은 실시간을 유지할 필요 없을 경우에만 사용할 수 있습니다.* Mysql query cache hit ratio 계산하기 ( 그런데 5.7 부터 사용 안한다.. ㅠㅠ )
쿼리캐시 히트율은 다음의 공식으로 구할 수 있다.
Query Cache Hit Rate = Qcache_hits / ( Qcache_hits + Com_select ) * 100
Qcache_his 는쿼리 캐시로 처리된 SELECT 쿼리의 수 이고, Com_select는 캐시에서 결과를 찾지 못하여 MySQL 서버가 쿼리를 실행한 횟수를 의미한다.
그렇다면 Qcache_hits 와 Com_select 를 어떻게 구하는지 알아보자
show variables like ‘query_%’;
다음 사진에서 query_cache_limit가 Qcache_hits 이다.
그 다음 Com_select를 구하기 위해 다음 명령어를 작성하자.
show global status like ‘Com_select’
이제 공식으로 계산해보면 다음과 같은 수치를 확인할 수 있다.
1048576/ ( 1048576 + 18 ) * 100 = 99.99..mysql) 쿼리의 진행 과정, 진행 시간을 확인하고 싶을 때
명령어를 확인하기 하기전에 설정을 바꿔줘야한다.
mysql> set profiling=1;
mysql> SET PROFILING_HISTORY_SIZE=30;
설정을 바꾸었다면 진행단계 또는 진행 시간을 확인하고 싶은 쿼리를 작성한다.
mysql> select * from 테이블명
마지막으로 다음 명령어를 입력하면 다음 사진과 같이 자신이 작성한 쿼리문과 쿼리문이 진행된 시간을 확인 할 수 있다.
show profiles;
작성된 쿼리의 진행 과정을 확인하고 싶으면, 다음 명령어를 작성하자
show profile for query 3(진행과정을 보고싶은 쿼리의 Query_ID);
