데이터베이스 질문과 답변
1. Mysql 8.0 에서 사용자 함수 사용시 인덱스를 잘탈까요? 실행계획이 보이지않아서요
explain , explain analyze 시 확인이 안됩니다
Oracle 에선 개별 쿼리마다 인덱스를 타고 실행계획이 생성된다고하는데.. mysql 은 알방법이 없네요
2. 사용자 function 성능개선 방법이 있을까요?
1.2 만 건수 테이블의 pk컬럼을 값으로 페이징쿼리로
limit 로 10건 혹은 20건은 1초정도 걸리고 50건부터는 5초 이상 걸리는 쿼리입니다
한쿼리에 2개 function 20번 사용합니다
3. Mysql 8.0 사용자 function 과 count(1) over() 윈도우 함수를 같은 select 절에 사용시 성능이 엄청 느립니다 왜그럴까요?
함수수행쿼리 실행계획입니다
-> Limit: 20 row(s) (actual time=419352.233..419352.307 rows=20 loops=1)
-> Window aggregate with buffering: count(1) OVER () (actual time=419352.232..419352.304 rows=20 loops=1)
-> Nested loop inner join (cost=8927.16 rows=8027) (actual time=111.874..1240.618 rows=11365 loops=1)
-> Nested loop semijoin (cost=7827.13 rows=8027) (actual time=111.857..1214.503 rows=11365 loops=1)
-> Nested loop inner join (cost=6622.54 rows=8027) (actual time=111.748..621.480 rows=12361 loops=1)
-> Nested loop inner join (cost=5619.16 rows=8027) (actual time=111.698..333.299 rows=11574 loops=1)
-> Nested loop inner join (cost=4615.79 rows=8027) (actual time=111.667..175.703 rows=11575 loops=1)
-> Invalidate materialized tables (row from G) (cost=3612.41 rows=8027) (actual time=111.643..140.332 rows=11575 loops=1)
-> Sort: g.GOODS_CD (cost=3612.41 rows=8027) (actual time=111.642..138.315 rows=11575 loops=1)
-> Filter: ((g.SALE_SCN = '00') and (g.GOODS_CL_CD is not null) and (g.GOODS_CD is not null)) (cost=3612.41 rows=8027) (actual time=0.252..99.713 rows=11575 loops=1)
-> Index range scan on G using tp_goods_ix01, with index condition: ((g.RGST_DTIME >= <cache>(str_to_date('2021-01-01','%Y-%m-%d'))) and (g.RGST_DTIME < <cache>((str_to_date('2022-05-01','%Y-%m-%d') + interval 1 day)))) (cost=3612.41 rows=8027) (actual time=0.247..95.800 rows=11597 loops=1)
-> Single-row index lookup on M using PRIMARY (MD_CD=g.MD_CD) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=11575)
-> Index lookup on K using TP_GOODS_CL_ix01 (GOODS_CL_CD=g.GOODS_CL_CD), with index condition: (g.GOODS_CL_CD = k.GOODS_CL_CD) (cost=0.25 rows=1) (actual time=0.011..0.013 rows=1 loops=11575)
-> Index lookup on P using TP_GOODS_PRC_ix01 (GOODS_CD=g.GOODS_CD) (cost=0.25 rows=1) (actual time=0.020..0.024 rows=1 loops=11574)
-> Index lookup on Z using <auto_key0> (APLY_DTIME=p.APLY_DTIME, GOODS_CD=g.GOODS_CD) (actual time=0.002..0.002 rows=1 loops=12361)
-> Materialize (invalidate on row from G) (cost=0.38..0.38 rows=1) (actual time=0.047..0.047 rows=1 loops=12361)
-> Limit: 1 row(s) (cost=0.28 rows=1) (actual time=0.020..0.020 rows=1 loops=11365)
-> Sort: i.APLY_DTIME DESC, limit input to 1 row(s) per chunk (cost=0.28 rows=1) (actual time=0.020..0.020 rows=1 loops=11365)
-> Index lookup on I using TP_GOODS_PRC_ix01 (GOODS_CD=g.GOODS_CD), with index condition: (i.APLY_DTIME <= <cache>(now())) (actual time=0.011..0.012 rows=1 loops=11365)
-> Single-row index lookup on E using PRIMARY (ENTP_CD=g.ENTP_CD) (cost=0.37 rows=1) (actual time=0.002..0.002 rows=1 loops=11365)
사용자 함수는 건건이 수행되기 때문에 좋지 않습니다.
사용자 함수를 대체할 수 있으면 좋고
사용자 함수를 사용해야 한다면
사용자 함수 내부 로직에 비효율은 없는지 확인이 필요합니다.
함수 내부 구문과 함수를 사용하는 쿼리를 볼 수 있을까요?
함수 내부 구문과 함수를 사용하는 쿼리 는 첨부파일로 업로드하였습니다
잘부탁드립니다
-- 전체에 대해 조인이나 함수 사용 후 정렬 LIMIT 하지 말고
-- 정렬 LIMIT 부터 하고 난 후 일부만 가지고 조인이나 함수 사용하세요.
SELECT ...
FROM (SELECT goods_cd /* 상품코드 */
, goods_nm /* 상품명 */
, puchs_meth
, cust_dscnt_yn
, entp_cd /* 거래처코드 */
, md_cd /* md코드 */
, lrcl /* 대분류 */
, mdcl /* 중분류 */
, smcl /* 소분류 */
, dtcl /* 세분류 */
, taxt_yn
, puchs_meth
, goods_cl_cd
, rgst_dtime
, prc_dscnt_imposbl_yn
, lmps_dscnt_imposbl_yn
, set_goods_yn
FROM tp_goods
WHERE rgst_dtime >= STR_TO_DATE('2021-01-01', '%Y-%m-%d')
AND rgst_dtime < DATE_ADD(STR_TO_DATE('2022-05-01','%Y-%m-%d'), INTERVAL 1 DAY)
AND sale_scn = '00'
ORDER BY goods_cd
LIMIT 0, 20
) g
, tp_goods_prc p
, tp_entp e
, tp_md m
, tp_goods_cl k
WHERE ...
;
페이징쿼리로 상품의 전체 갯수를 보여줘야해서 count(1) over() 를 써야되서
정렬 LIMIT 후 count(1) over() 써버리면 원하는 결과가 안나오네요..
기존 1만2천건 변경후 LIMIT 건수가나옵니다
페이징 쿼리와 전체 건수 쿼리는 별도로 분리하는게 좋습니다.
한번에 COUNT(*) OVER() 를 하게 되면 페이징 쿼리의 장점을 살리지 못합니다.
개발자분들이 싫어하시네요..^^;;
감사합니다 선생님 !! 많은 도움감사합니다 !!
) 버튼을 클릭하여 작성 하시면 됩니다.