7/10 ~ 7/14
지역 or 이름 or 나이 or 경력 으로 진행할 전체 검색 남음. 검색 결과 정렬도 가능해야함
아마 성별은 빼고
특이사항 추가하고
경력 빼고
조회수 남음 (락 학습, 도입)
조회수 구현
1. redis의 hyperloglog를 이용하여 구현하기로 하였다. 왜냐하면 조회수 특성상 정확도가 100%일 필요가 없고, 기존 세션, 쿠키, ip 등으로 진행하면 단점들이 각각 존재. 특히 메모리 용량을 많이 차지함. but, hyperloglog는 매우 낮은 메모리 사용 & 정확도 100은 아니지만 상당히 일치함.
하지만 redis를 이용하는 것이다보니 redis가 이용 불가능할 시, 조회수를 이용 못함. 즉, 백업을 해두어야 함.
이는 rdb로 진행을 해야 한다.
스케쥴러를 통해 하는게 좋다.
==============
7/12
ALTER TABLE members
ADD FULLTEXT INDEX idx_members_addr_name (addr, name);
ALTER TABLE caregivers
ADD FULLTEXT INDEX idx_caregivers_specialization_significant (specialization, caregiverSignificant);
전체 검색 ftidx로 해보는 중이다. 하지만 역시 keyword=경기도 수원시는 빠르지만
significant3으로 검색하면 모든 데이터가 다 significant~이기 때문에 매우 느린걸 확인 가능.
즉, 서울시로 시작하는 단어 검색 시 매우 느린것과 같음.
그리고 member와 caregiver테이블이 달라서 where절에 or이 들어갔는데, or이 들어가면 인덱스를 타지 않고 FTS 한다. 조건에 caregiver빼고 돌리니 괜찮았음. 즉, or이 들어가면 인덱스를 안탄다?? 왜? where절에 컬럼들이 많아서?
explain select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts
from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id
where (match(c1_1.addr, c1_1.name) against ('수원시 영통구' IN NATURAL LANGUAGE MODE)>0.0
or match(c1_0.specialization, c1_0.caregiverSignificant) against ('수원시 영통구' IN NATURAL LANGUAGE MODE)>0.0)
and c1_1.isProfilePublic
order by c1_0.member_id desc limit 6;위 커리 FTS.
explain select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts
from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id
where match(c1_1.addr, c1_1.name) against ('수원시 영통구' WITH QUERY EXPANSION)>0.0 and c1_1.isProfilePublic
order by c1_0.member_id desc limit 6;이건 full text index 탄다.
http://localhost:8080/api/v1/patient/search?keyword=Significant3 이건 아예 결과가 안나온다.
Hibernate:
select
c1_0.member_id,
c1_1.name,
c1_1.age,
c1_1.gender,
c1_1.addr,
c1_1.addrDetail,
c1_1.zipCode,
c1_0.rating,
c1_0.experienceYears,
c1_0.specialization,
c1_1.profileImageUrl,
c1_1.viewCounts
from
caregivers c1_0
join
members c1_1
on c1_0.member_id=c1_1.member_id
where
match(c1_1.addr, c1_1.name) against (? WITH QUERY EXPANSION)>?
and c1_1.isProfilePublic
order by
c1_0.member_id desc
limit
?
2024-07-12T15:25:53.755+09:00 WARN 14480 --- [backend] [nio-8080-exec-5] o.m.jdbc.message.server.ErrorPacket : Error: 188-HY000: FTS query exceeds result cache limit
2024-07-12T15:25:54.018+09:00 INFO 14480 --- [backend] [nio-8080-exec-5] p6spy : #1720765554014 | took 368261ms | statement | connection 5| url jdbc:mariadb://localhost/patientpal?user=root&password=***&useSSL=false&allowPublicKeyRetrieval=true
select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id where match(c1_1.addr, c1_1.name) against (? WITH QUERY EXPANSION)>? and c1_1.isProfilePublic order by c1_0.member_id desc limit ?
select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id where match(c1_1.addr, c1_1.name) against ('서울시 강남구' WITH QUERY EXPANSION)>0.0 and c1_1.isProfilePublic order by c1_0.member_id desc limit 6;
2024-07-12T15:25:54.216+09:00 WARN 14480 --- [backend] [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 188, SQLState: HY000
2024-07-12T15:25:54.219+09:00 ERROR 14480 --- [backend] [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : (conn=95) FTS query exceeds result cache limit
2024-07-12T15:25:54.697+09:00 INFO 14480 --- [backend] [nio-8080-exec-5] p6spy : #1720765554697 | took 53ms | rollback | connection 5| url jdbc:mariadb://localhost/patientpal?user=root&password=***&useSSL=false&allowPublicKeyRetrieval=true
;
2024-07-12T15:25:54.834+09:00 ERROR 14480 --- [backend] [nio-8080-exec-5] c.p.b.c.advice.RestApiExceptionHandler : JDBC exception executing SQL [select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id where match(c1_1.addr, c1_1.name) against (? WITH QUERY EXPANSION)>? and c1_1.isProfilePublic order by c1_0.member_id desc limit ?] [(conn=95) FTS query exceeds result cache limit] [n/a]
org.springframework.orm.jpa.JpaSystemException: JDBC exception executing SQL [select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id where match(c1_1.addr, c1_1.name) against (? WITH QUERY EXPANSION)>? and c1_1.isProfilePublic order by c1_0.member_id desc limit ?] [(conn=95) FTS query exceeds result cache limit] [n/a]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:341) ~[spring-orm-6.1.6.jar:6.1.6]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241) ~[spring-orm-6.1.6.jar:6.1.6]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550) ~[spring-orm-6.1.6.jar:6.1.6]
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.1.6.jar:6.1.6]
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335) ~[spring-tx-6.1.6.jar:6.1.6]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:164) ~[spring-data-jpa-3.2.5.jar:3.2.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223) ~[spring-aop-6.1.6.jar:6.1.6]
at jdk.proxy2/jdk.proxy2.$Proxy183.searchPageOrderByDefault(Unknown Source) ~[na:na]
at com.patientpal.backend.patient.service.PatientService.searchPageOrderByDefault(PatientService.java:150) ~[main/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.1.6.jar:6.1.6]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:392) ~[spring-tx-6.1.6.jar:6.1.6]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768) ~[spring-aop-6.1.6.jar:6.1.6]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720) ~[spring-aop-6.1.6.jar:6.1.6]
at com.patientpal.backend.patient.service.PatientService$$SpringCGLIB$$0.searchPageOrderByDefault(<generated>) ~[main/:na]
at com.patientpal.backend.patient.controller.PatientControllerV1.searchCaregivers(PatientControllerV1.java:133) ~[main/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.6.jar:6.1.6]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.1.6.jar:6.1.6]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564) ~[tomcat-embed-core-10.1.20.jar:6.0]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.6.jar:6.1.6]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.20.jar:6.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:206) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.20.jar:10.1.20]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
at com.patientpal.backend.security.jwt.JwtAuthTokenFilter.doFilterInternal(JwtAuthTokenFilter.java:35) ~[main/:na]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:108) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.security.web.FilterChainProxy.lambda$doFilterInternal$3(FilterChainProxy.java:231) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:365) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.access.intercept.AuthorizationFilter.doFilter(AuthorizationFilter.java:100) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:126) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:120) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:131) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:85) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:100) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:179) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at com.patientpal.backend.security.jwt.JwtAuthTokenFilter.doFilterInternal(JwtAuthTokenFilter.java:35) ~[main/:na]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:227) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:221) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.oauth2.client.web.OAuth2AuthorizationRequestRedirectFilter.doFilterInternal(OAuth2AuthorizationRequestRedirectFilter.java:181) ~[spring-security-oauth2-client-6.2.4.jar:6.2.4]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:107) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:93) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:82) ~[spring-security-web-6.2.4.jar:6.2.4]
at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:69) ~[spring-security-web-6.2.4.jar:6.2.4]
===============
7/13
http://localhost:8080/api/v1/patient/search?addr=수원시 영통구
이런식으로 검색했는데 시간 매우 느림. 55초. 왜?
select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id where c1_1.addr='수원시 영통구' and c1_1.isProfilePublic order by c1_0.member_id desc limit 6;
실행 계획 확인해봐야함. 현재 image 다 삭제중이라 디비 못건듬.
인덱스를 타긴 하나, 300000 건을 조회하고 member_id순으로 정렬하기 때문에 시간이 오래걸린다. 50초.
=> 인덱스 타는 이유 : order by를 caregiver테이블에서 하기 때문에, member의 PRIMARY는 안타고, member에 addr_ispp인덱스가 있어서 그걸 탄다. 즉, 가장 효율적인 인덱스가 addr_ispp이다. 하지만 이렇게 인덱스를 타도, 30만건을 order id순으로 정렬해야해서 느리다. using filesort, using temporary.
select c1_0.member_id,c1_1.name,c1_1.age,c1_1.gender,c1_1.addr,c1_1.addrDetail,c1_1.zipCode,c1_0.rating,c1_0.experienceYears,c1_0.specialization,c1_1.profileImageUrl,c1_1.viewCounts
from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id
where c1_1.addr='수원시 영통구' and c1_1.isProfilePublic
limit 6;
정렬을 안하면 1초 언저리임. 빠른편.
=> 그치 빠르지. 왜냐하면 where에 해당하는 조건문들 인덱스 잘 탔고, 따로 inno_db가 내부적으로 정렬을 할 필요가 없으니까.
즉, 정렬을 했을 때 using temporary, using filesort가 발생하면서 모든 row들을 mysql이 내부에서 정렬 후에 값을 반환한다. 이를 해결해야 함.
=> 어떻게? 바로 저 inno_db가 내부적으로 정렬을 하지 않게 하면 되는 것이야. 즉, order by까지 인덱스를 같이 태우면 됨.(만약 order by를 id로 한다면 pk는 기본적으로 클러스터링 인덱스 이므로, 매우 빠름. 하지만, 문제가 있다. 인덱스는 하나만 적용이 됨. (join은 실행계획이 두 개 던데요?) 그래서 order by를 id로 하면 무조건 인덱스가 PRIMARY로 적용이 됨. 하지만 이러면 addr과 ispp는 인덱스를 못타기 때문에 많은 탐색 발생함. = 느림). 그래서 order by를 id가 아닌 날짜로 하면 최신순도 만족하면서 인덱스도 잘 탈 수 있다. (addr_ispp_날짜)
* 여기서 의문, 그렇다면 정렬이 되는 모든 order by 이후 컬럼을 다 인덱스를 태워야 하나? 현재 최신순, 조회순, 후기순 이렇게 있다고 하면 addr_ispp_최신순, addr_ispp_조회순, addr_ispp_후기순 이렇게 각각 인덱스를 설정해야함?
아니면 복합인덱스? addr_ispp_최신순_조회순_후기순 이렇게??? <- 이거 어떻게 동작하는지 자세히 모름.
**또 의문. 현재 member로만 가져오는데, 그러면 검색 조건에 간병인 경력같은건 못넣나? 넣고 싶으면 caregiver로 가져와야 하는데. from(caregiver)과 order by (member.id)가 에러 났다. 여기서 orm과 기존 db의 차이가 발생하는 듯.
그러면 order by(caregiver.날짜)는 괜찮나? 괜찮을거같은데? QClass 업데이트하고 다시 해보자. 애초에 order by를 caregiver에서 해도 되늰건가? caregiver에는
애초에 caregiver.id.desc로 하면 안됐던 이유는, using filesort, using temporary때문이였음. 즉, addr_ispp 인덱스로 데이터를 가져와서, caregiver 테이블에서 다시 정렬을 하려고 하니 오래걸렸다.
그러면 날짜 또한 caregiver테이블에서 다시 정렬을 해야하나? 해보자 이건. 안되면 caregiver테이블에 컬럼 추가? 근데 인덱스 어차피 못타잖아 테이블이 다르니까
날짜로 하니까 된다. using temporary가 나오지 않아.
id로 정렬했을 때는 분명 using temporary가 나왔는데 왜 날짜는 안나오지?
쿼리를 보면 member테이블에서 order by가 일어난다. querydsl에서 분명 caregiver.날짜.desc로 했는데 말이지..
기존에 id로 할때는 caregiver.id.desc로 하면 order by도 caregiver테이블에서 order by가 일어났다.
=> caregiver테이블에 id가 존재한다면 그걸로 정렬하고, 날짜 처럼 caregiver에 없고 부모인 member에 있다면 member테이블에서 정렬하나? 하지만 caregiver테이블에 존재하는 것은 member_id이다. caregiver_id가 아니란 말임. 하지만 jpa는 동일시 여기나? 실제 caregiver테이블의 member_id는 기본키면서 외래키이다.
order by를 caregiver에서 하면
member에서 하면
jpa 상속-join 전략에서 인덱스 설정이 쉽지 않다.. 테이블간에 인덱스 복합 설정이 불가능하기 때문에.
querydsl에서 member_id로 order by를 해야 제대로 인덱스를 타는데, 지금은 caregiver_id로 정렬해서 using filesort가 발생함. 즉, 테이블이 다르기 때문에. 이는 orm과 db간의 차이때문에 발생한것 같다.
JPA JOIN 전략에서, JOIN을 통해 정보를 가져온다. 하지만 CAREGIVER로만 querydsl을 진행하면 인덱스를 걸 수 가 없다. 현재 member의 addr, isProfilePublic, member_id에 인덱스를 걸어야 하는데, order by를 caregiver_id로 진행하기 때문에 인덱스 안걸리고 매우 느림.
해결 => 1. 내가 생각한건 member테이블에 caregiver_id를 추가?
커버링 인덱스로, 9초 + 0.1초
------------------
아래 처럼 하면
+ using temporary + using filesort
----------------------------
아래 처럼 하면 (order by 를 member로)
+ using filesort
---------------------------------------
결론 : order by를 member테이블에서 하고, index를 idx_member_addr_isProfilePublic_member_id 이거 써야 한다.
하지만 인덱스 force를 안하면
---------------------------------------------
아래처럼 하면 (order by는 member인데 인덱스를 설정을 안함. = index가 PRIMARY로 잡힘.
-------------------------------------------------
아래처럼 하면 (order by 를 caregiver에서 하고 인덱스 설정을 안함 = index는 idx_member_addr_isProfilePublic 이게 잡힘
-----------------------------------------------------
-----------------------------------------------------------
---------------------------------------------------------
**문제 1.
explain select c1_0.member_id
from caregivers c1_0
join members c1_1 on c1_0.member_id=c1_1.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.member_id desc limit 5;
여기서 왜 현재 Member테이블에 idx_member_addr_isProfilePublic_member_id 이게 있는데 인덱스 key가 PRIMARY로 잡히는지 ? Force Index를 써야하나? 왜 옵티마이저가 이런 선택을?!
문제 2. 위의 문제도 order by를 member테이블에서 진행함으로써, idx_member_addr_isProfilePublic_member_id 이 인덱스를 탈 수 있는건데(비록 지금은 primary 타지만) querydsl에서 order by
이 쿼리는 현재 코드에서 실행되는 쿼리이다.
인덱스는 제대로 탄다.
*아마 c1_0의 member_id는 인덱스를 안타고, where절에 addr과 isProfilePublic으로 인해 인덱스를 idx_member_addr_isProfilePublic_member_id 이거 타는 것 같다. 즉, order by에서의 member_id는 인덱스를 안타는거다. 왜냐하면 caregiver의 member_id로 정렬을 하니까. 그래서 using temporary, using filesort가 나오는것이고.
하지만 속도는 느리다. 왜??? 바뀐건 order by를 member에서 하느냐(빠름) caregiver에서 하느냐(느림) 차이인데?? 심지어 인덱슨느 order by를 caregiver에서 해야 제대로 idx_member_addr_isProfilePublic_member_id 이걸 탄다.
member에서 하면 인덱스가 primary가 타네???
우선 순위
1. 우선 order by를 member에서 하는 게 필수임. 그래야 빠름.
2. 근데 member에서 order by를 하면 인덱스가 PRIMARY 잡힘. force index 우선 해야함.
3. native query 밖에 없나?
----
이게 현재 인덱스임
-------------
List<Long> memberIds = queryFactory
.select(member.id)
.from(member)
.where(
cursorId(lastIndex),
nameEq(condition.getName()),
genderEq(condition.getGender()),
addressEq(condition.getAddr()),
experienceYearsGoe(condition.getExperienceYearsGoe()),
member.isProfilePublic
)
.orderBy(member.id.desc())
.limit(pageable.getPageSize())
.fetch();
List<CaregiverProfileResponse> content = queryFactory
.select(new QCaregiverProfileResponse(
caregiver.id,
caregiver.name,
caregiver.age,
caregiver.gender,
caregiver.address,
caregiver.rating,
caregiver.experienceYears,
caregiver.specialization,
caregiver.profileImageUrl,
caregiver.viewCounts))
.from(caregiver)
.where(caregiver.id.in(memberIds))
.orderBy(caregiver.id.desc())
.fetch();
위 처럼 하면
이렇게 나옴. 대신 caregiver의 필드를 사용하는
experienceYearsGoe(condition.getExperienceYearsGoe()),
ㅇ . .
이거 사용 못함. 요청 보낼 시에 에러 나옴.
그러나 여기서 force index만 해주면 제대로 나올듯.
근데 왜 index를 PRIMARY 타는거지? 이유 밝히자. 분명 제대로 인덱스가 있는데??
1. backward index scan이 원인인가 해서 member_id를 desc로 바꿔서 인덱스 생성해봄.
==================
인덱스를 추가했다.
create index idx_member_member_id_addr_isProfilePublic on members (member_id desc, addr, isProfilePublic);
select c1_0.member_id
from caregivers c1_0 join members c1_1 /*force index(idx_member_addr_isProfilePublic_member_id)*/ on c1_0.member_id=c1_1.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.member_id desc limit 5;이 쿼리 실행하니까
새로 생성한 인덱스를 잘 타고, filesort도 일어나지 않는다. 하지만 시간이 3초네. 현재는 member로 order by중임.
caregiver로 order by하면 역시
이 인덱스를 탄다. 즉 member_id가 인덱스를 안탄다는 것. 테이블이 다르기에.
우선순위 다시 1. order by는 member에서 한다.
select c1_0.member_id
from caregivers c1_0 join members c1_1 force index(idx_member_addr_isProfilePublic_member_id) on c1_0.member_id=c1_1.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.member_id desc limit 5;그런데 여전히 force index로 위 쿼리를 실행하는게 월등히 빠르다.
force index를 강제로 쓸 수 밖에 없나.. 인덱스를 새로 생성해보았지만 시간이 3초로 단축은 됐지만 여전히 force index를 쓰는 것보다 느리다. 7배..
심지어
select c1_1.member_id
from members c1_1 /*force index(idx_member_addr_isProfilePublic_member_id) join caregivers c1_0 on c1_0.member_id=c1_1.member_id*/ -- join을 안걸면 인덱스 잘타나?
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.member_id desc limit 5;join도 안걸고 그냥 member 테이블에서 조회했는데,
인덱스는 잘 타는데,
join을 빼니까 시간이 좀 줄긴 했다.
하지만 역시 force index를 활성화 하니 500ms 내로 준다. 이유가 뭐지???
filter 차이가 나네. idx_member_member_id_addr_isProfilePublic 이 인덱스는 filter율이 5.16인 반면에,
idx_member_addr_isProfilePublic_member_id 이거는 filter가 100이다. 대신 filesort가 사용됨.
인덱스 순서 차이로 인해 filter율이 달라지고, 속도도 다르다. 약 3배.
밑에는 지금 현재 인덱스
-------------------
explain select c1_1.member_id
from members c1_1 /*force index(idx_member_addr_isProfilePublic_member_id)*/ /* join caregivers c1_0 on c1_0.member_id=c1_1.member_id*/ -- join을 안걸면 인덱스 잘타나?
where c1_1.addr='수원시 영통구' and c1_1.isProfilePublic
order by c1_1.member_id desc limit 5;이제 이 쿼리는
인덱스가 기본키가 아니라 idx_member_member_id_addr_isProfilePublic 이 인덱스를 타긴한다. 하지만 성능은 idx_member_addr_isProfilePublic_member_id 를 타는 것보다 느림. 하지만 idx_member_member_id_addr_isProfilePublic 이 인덱스가 없으면 PRIMARY를 타버림..그리고 extra에 Using where; Backward index scan 나옴. Backward index scan이란??????????
----
join이 있으면 explain이 두 번 실행되네. 없으면 한 번.
새로 만든 addr_ispp만 있어도 매우 빠름. but force index 없을 시 primary로 실행됨.
그렇다면 addr_ispp VS addr_ispp_member_id 중 뭐가 더 인덱스로 나을까? 일단 속도는 차이 없음. 실행계획도 차이 없음.
==============
처음 실행 코드와 속도를 보자
=> index를 타긴하지만, mysql inno db에서 정렬이 일어나 속도가 매우 느리다.
force index를 해보자.
똑같이 느리다. 그럴수밖에. force index를 안해도 index가 제대로 타긴한다. 근데 매우 느려.
======================
order by만 caregiver에서 member로 바꾸어 봤다. force index X
인덱스가 PRIMARY로 잡힌다.
-----------------
force index를 하면
18초가 걸린다.
즉, 위의 코드에 따른 기존 쿼리는 order by를 member로 하든, caregiver로 하든 다 느리다. member로 하고 force index를 안하면 24초로 조금 빨라지고, member로 하고 force index를 해도 18초가 걸린다. 그러나 뭐를 선택해도 느리긴 마찬가지이다. 그래서 커버링 인덱스를 도입하였다.
==============================
List<Long> memberIds = queryFactory
.select(member.id)
.from(member)
.where(
cursorId(lastIndex),
nameEq(condition.getName()),
genderEq(condition.getGender()),
addressEq(condition.getAddr()),
experienceYearsGoe(condition.getExperienceYearsGoe()),
member.isProfilePublic
)
.orderBy(member.id.desc())
.limit(pageable.getPageSize())
.fetch();
List<CaregiverProfileResponse> content = queryFactory
.select(new QCaregiverProfileResponse(
caregiver.id,
caregiver.name,
caregiver.age,
caregiver.gender,
caregiver.address,
caregiver.rating,
caregiver.experienceYears,
caregiver.specialization,
caregiver.profileImageUrl,
caregiver.viewCounts))
.from(caregiver)
.where(caregiver.id.in(memberIds))
.orderBy(caregiver.id.desc())
.fetch();커버링 + 기존 아무것도 손 안댄 쿼리.
역시 아까 커버링x 코드 쿼리에서 테스트할 때처럼 아무것도 건들지 않고 실행하면 인덱스를 제대로 탄다. 속도도 6초로 많이 줄어들었다. order by caregiver에서 할때임.
force index를 할 필요가 없다. 인덱스를 제대로 타기 때문에.
--------------------------------
order by만 member로 바꿔보았다.
역시나 인덱스가 제대로 타지 않는다. PRIMARY를 탄다. using index도 나오지 않네. 시간은 무려 25초.
------------------------------------
인덱스만 force index 설정해주었다.
속도가 매우 빨라졌다.
*의문
select c1_0.member_id
from caregivers c1_0 join members c1_1 force index(idx_member_addr_isProfilePublic) on c1_0.member_id=c1_1.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_0.member_id desc
limit 5;여기서 order by만 달라지는데, caregiver로 하면 5초. member로 하면 0.5초 걸리네. 왜그러지?? 인덱스는 다 force index로 시켰는데.
=> caregiver로 하면 extra에 using temporary가 추가된다. member는 using temporary가 없음. 둘다 using filesort는 존재.
이게 성능이 10배가 차이난다.
즉, 무조건 order by를 member로 해야하는게 확정임.
하지만 member로 order by를 하면 인덱스가 PRIMARY를 탄다. 왜그러지?
기본 키 Member_id가 현재 PRIMARY 클러스터링 인덱스이다. 그래서 옵티마이저가 기본적으로 member_id를 선택함. 그래서 모든 member_id를 첫 출발로 하기 때문에, filter율이 5.16밖에 되지 않음. addr부터 먼저 출발하게 해야함.
근데 caregiver로 order by하면 인덱스가 잘 탄다. 왜지?
그니까 인덱스만 addr_ispp만 잘 타도 빨라. 0.5초, 5초.
하지만 인덱스를 PRIMARY타는 순간 느려. 그런데, caregiver 테이블로 order by하면 addr_ispp잘타는데 member테이블로 order by하면 PRIMARY를 타. caregiver도 PRIMARY키가 있는데 왜 caregiver는 인덱스가 적용되고 member는 안될까?
order by를 id로 꼭 해야하나? id로 해서 primary로 인덱스가 타는데, lastModifiedDate로 하면 되는 거 아님?
인덱스 이제 PRIMARY 안탐. 시간은 18초.
인덱스에
idx_member_addr_isProfilePublic_lastModifiedDate추가해 봄.
추가한 인덱스 잘 타고 속도도 빠르다. 그러나 현재 최신순을 구현중인데, 업데이트 마지막으로 한 순서가 맞는지? 그럼 사진만 바꾸거나 특이사항만 바꿔도 가장 우선순위가 높아지는데? 안될듯하다.생성한 순서가 맞다. 그러나 가입만 하고 프로필 등록을 한 달뒤에 하면? createdDate는 빠른데, ispp가 false이다. 즉, ispp를 true로 해도 최신순 정렬 시에 저 뒤로 밀린다. 즉, 프로필 리스트에 등록 버튼을 누른 순서가 최신 순이 맞다. (ispp가 true가 되는 순간. 그러려면 시간을 하나 더 추가해야한다.)
인덱스에 수정, 삭제,삽입이 자주 일어나는 컬럼을 넣으면 오히려 안좋다.
리스트에 등록과 해제는 자주 발생하나? 등록, 해제를 할 때마다 ispptime이 바뀌겠지. but, 조회가 더 많이 일어나므로 어느정도 타협해야함.
*
idx_member_member_id_addr_isProfilePublic이건 모두 인덱스를 이거 탔던 이유가,. member테이블로 order by해도 기본 PRIMARY키가 member_id인데 이건 id + addr + ispp니까 더 구체적이니까 이거 탔던 거네.
caregiver로 order by는 이게 아니라 addr_ispp탔던듯. 위에 찾아보자.