그런데? 인덱스를 밀어넣고 난 이후에도 데이터가 제대로 보이지 않는다는 것이었다.

흠. 이 부분에 대해서 킹치만 많은 가설을 통해 검증을 시작하면

========================================
ubuntu@ip-172-31-40-155:~$ curl -X GET "http://localhost:9200/_cat/indices?v"
health status index                                        uuid                   pri rep docs.count docs.deleted store.size pri.store.size
yellow open   moyamo_3                                     9bPawqGQTseRptxkTTsy-A   5   1    1576206          774      1.3gb          1.3gb
yellow open   net.infobank.moyamo.models.photorecentwriter bYxm8ozGSzOmC8LpV59oWg   5   1          0            0       810b           810b

내가 예상한 데이터와 다르게 얼마 안들어갔다는 것이다?

이 부분을 계산해볼때 뭔가 이상한 느낌이 스물스물 나서 코드를 조사해봤는데

// SimdaSchedulerApplication.java:122
List<SimplePosting> postings = eventWorkRepositoryCustom.findPostingList(
    Posting.class, sinceId, maxId, 1000  // ← 최대 1000개만 조회!
);

1000개씩만 드신다는 것이었다.

그래서 이걸 진짜로 9~10시간에 걸려서 삽질을 해야되냐? 댓츠논노

우리는 무적의 m5.xlarge를 사용하고 있기 때문에 더욱 공격적으로 병렬로 때려넣을 수 있다는 것이다.

#!/bin/bash
# Elasticsearch 병렬 인덱싱 스크립트
# 8개의 프로세스로 병렬 처리 - m5.xlarge (4 vCPU, 16GB) 최적화
# 예상 소요 시간: 약 1시간

SCHEDULER_URL="http://localhost:8088"
MAX_COMMENTS=200

# ID 범위를 n등분해서 처리
reindex_range() {
    local START=$1
    local END=$2
    local WORKER=$3
    local BATCH_SIZE=1000
    local CURRENT=$START
    local COUNT=0

    echo "[Worker $WORKER] 시작: $START ~ $END"

    while [ $CURRENT -le $END ]; do
        local BATCH_END=$((CURRENT + BATCH_SIZE - 1))
        if [ $BATCH_END -gt $END ]; then
            BATCH_END=$END
        fi

        curl -s -X POST "$SCHEDULER_URL/indexing?sinceId=$CURRENT&maxId=$BATCH_END&max=$MAX_COMMENTS" --max-time 300 > /dev/null

        CURRENT=$((BATCH_END + 1))
        COUNT=$((COUNT + 1))

        # 500 배치마다 진행상황 출력
        if [ $((COUNT % 500)) -eq 0 ]; then
            echo "[Worker $WORKER] 진행 중... $CURRENT / $END"
        fi
    done

    echo "[Worker $WORKER] 완료!"
}

echo "========================================"
echo "병렬 인덱싱 시작 (8 workers)"
echo "서버: m5.xlarge (4 vCPU, 16GB RAM)"
echo "========================================"

START_TIME=$(date +%s)

# 8개의 범위로 나눠서 병렬 실행 (각 약 2백만 건)
reindex_range 114 2000000 1 &
reindex_range 2000001 4000000 2 &
reindex_range 4000001 6000000 3 &
reindex_range 6000001 8000000 4 &
reindex_range 8000001 10000000 5 &
reindex_range 10000001 12000000 6 &
reindex_range 12000001 14000000 7 &
reindex_range 14000001 16335566 8 &

# 모든 백그라운드 작업 완료 대기
wait

END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
HOURS=$((DURATION / 3600))
MINUTES=$(( (DURATION % 3600) / 60 ))
SECS=$((DURATION % 60))

echo "========================================"
echo "전체 인덱싱 완료!"
echo "소요 시간: ${HOURS}시간 ${MINUTES}분 ${SECS}초"
echo "========================================"

# 최종 문서 수 확인
echo "인덱싱된 문서 수:"
curl -s "http://localhost:9200/moyamo_3/_count"
echo """

하지만 이렇게 처리한 이후에 CPU는 영 좋지못한 상황으로 가버리는데

ubuntu@ip-172-31-40-155:~$ chmod +x reindex-parallel.sh 
ubuntu@ip-172-31-40-155:~$ ./reindex-parallel.sh 
========================================
병렬 인덱싱 시작 (8 workers)
서버: m5.xlarge (4 vCPU, 16GB RAM)
========================================
[Worker 1] 시작: 114 ~ 2000000
[Worker 2] 시작: 2000001 ~ 4000000
[Worker 7] 시작: 12000001 ~ 14000000
[Worker 6] 시작: 10000001 ~ 12000000
[Worker 4] 시작: 6000001 ~ 8000000
[Worker 3] 시작: 4000001 ~ 6000000
[Worker 5] 시작: 8000001 ~ 10000000
[Worker 8] 시작: 14000001 ~ 16335566

괘..괜찮아.. 새벽에 돌리는 Scheduler니까 서비스는 영향받지 않는다굿!

문제 상황

대략적으로 서술해보자면 서비스는 일단 502 Bad Gateway가 나면서 내려가고 식은땀이 줄줄 흐르는 상황이 발생한 것인데, 에러 로그만 수집해보면 다음과 같다.

Information Gathering

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
[Rest][2026-03-30 03:49:03][ERROR][SpringApplication.java][reportFailure(860)] : Application run failed
org.springframework.context.ApplicationContextException: Unable to start web server; nested exception is org.springframework.boot.web.server.WebServerException: Unable to start embedded Tomcat
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.onRefresh(ServletWebServerApplicationContext.java:162)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:577)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144)
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:771)
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:763)
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:438)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:339)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1329)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1318)
        at net.infobank.moyamo.MoyamoApplication.main(MoyamoApplication.java:16)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:108)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:58)
        at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:88)
Caused by: org.springframework.boot.web.server.WebServerException: Unable to start embedded Tomcat
        at org.springframework.boot.web.embedded.tomcat.TomcatWebServer.initialize(TomcatWebServer.java:142)
        at org.springframework.boot.web.embedded.tomcat.TomcatWebServer.<init>(TomcatWebServer.java:104)
        at org.springframework.boot.web.embedded.tomcat.TomcatServletWebServerFactory.getTomcatWebServer(TomcatServletWebServerFactory.java:450)
        at org.springframework.boot.web.embedded.tomcat.TomcatServletWebServerFactory.getWebServer(TomcatServletWebServerFactory.java:199)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.createWebServer(ServletWebServerApplicationContext.java:181)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.onRefresh(ServletWebServerApplicationContext.java:159)
        ... 17 common frames omitted

그리고 2번째 놈의 로그

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'tokenAuthorizationFilter' defined in URL [jar:file:/home/ubuntu/moyamo-0.0.1-SNAPSHOT.jar!/BOOT-INF/classes!/net/infobank/moyamo/configuration/TokenAuthorizationFilter.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'authorizationService' defined in URL [jar:file:/home/ubuntu/moyamo-0.0.1-SNAPSHOT.jar!/BOOT-INF/classes!/net/infobank/moyamo/api/service/AuthorizationService.class]: Unsatisfied dependency expressed through constructor parameter 1; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userSecurityRepository' defined in net.infobank.moyamo.repository.UserSecurityRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Cannot create inner bean '(inner bean)#59221b97' of type [org.springframework.orm.jpa.SharedEntityManagerCreator] while setting bean property 'entityManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#59221b97': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:800)
        at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:229)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1354)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1204)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:564)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:213)
        at org.springframework.boot.web.servlet.ServletContextInitializerBeans.getOrderedBeansOfType(ServletContextInitializerBeans.java:212)
        at org.springframework.boot.web.servlet.ServletContextInitializerBeans.addAsRegistrationBean(ServletContextInitializerBeans.java:175)
        at org.springframework.boot.web.servlet.ServletContextInitializerBeans.addAsRegistrationBean(ServletContextInitializerBeans.java:170)
        at org.springframework.boot.web.servlet.ServletContextInitializerBeans.addAdaptableBeans(ServletContextInitializerBeans.java:155)
        at org.springframework.boot.web.servlet.ServletContextInitializerBeans.<init>(ServletContextInitializerBeans.java:87)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.getServletContextInitializerBeans(ServletWebServerApplicationContext.java:259)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.selfInitialize(ServletWebServerApplicationContext.java:233)
        at org.springframework.boot.web.embedded.tomcat.TomcatStarter.onStartup(TomcatStarter.java:53)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5161)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
        at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909)
        at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:829)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
        at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909)
        at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.StandardService.startInternal(StandardService.java:433)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:930)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.startup.Tomcat.start(Tomcat.java:486)
        at org.springframework.boot.web.embedded.tomcat.TomcatWebServer.initialize(TomcatWebServer.java:123)
        ... 22 common frames omitted

3번째 로그와 4번째 로그

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'authorizationService' defined in URL [jar:file:/home/ubuntu/moyamo-0.0.1-SNAPSHOT.jar!/BOOT-INF/classes!/net/infobank/moyamo/api/service/AuthorizationService.class]: Unsatisfied dependency expressed through constructor parameter 1; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userSecurityRepository' defined in net.infobank.moyamo.repository.UserSecurityRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Cannot create inner bean '(inner bean)#59221b97' of type [org.springframework.orm.jpa.SharedEntityManagerCreator] while setting bean property 'entityManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#59221b97': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:800)
        at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:229)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1354)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1204)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:564)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
        at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1380)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1300)
        at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:887)
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:791)
        ... 63 common frames omitted
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userSecurityRepository' defined in net.infobank.moyamo.repository.UserSecurityRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Cannot create inner bean '(inner bean)#59221b97' of type [org.springframework.orm.jpa.SharedEntityManagerCreator] while setting bean property 'entityManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#59221b97': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:389)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:134)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1689)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1434)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:601)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
        at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1380)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1300)
        at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:887)
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:791)
        ... 77 common frames omitted

로그로그

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#59221b97': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:342)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:113)
        at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:693)
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:510)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1334)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:564)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:374)
        ... 91 common frames omitted
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:330)
        ... 99 common frames omitted
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:421)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396)
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782)
        ... 106 common frames omitted

Caused by: org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'moyamo_3'
        at org.hibernate.search.elasticsearch.schema.impl.DefaultElasticsearchSchemaMigrator.migrate(DefaultElasticsearchSchemaMigrator.java:77)
        at org.hibernate.search.elasticsearch.impl.ElasticsearchIndexManager.initializeIndex(ElasticsearchIndexManager.java:321)
        at org.hibernate.search.elasticsearch.impl.ElasticsearchIndexManager.initializeIndex(ElasticsearchIndexManager.java:266)
        at org.hibernate.search.elasticsearch.impl.ElasticsearchIndexManager.setSearchFactory(ElasticsearchIndexManager.java:257)
        at org.hibernate.search.indexes.impl.IndexManagerHolder.setActiveSearchIntegrator(IndexManagerHolder.java:150)
        at org.hibernate.search.engine.impl.MutableSearchFactoryState.setActiveSearchIntegrator(MutableSearchFactoryState.java:244)
        at org.hibernate.search.spi.SearchIntegratorBuilder.buildNewSearchFactory(SearchIntegratorBuilder.java:214)
        at org.hibernate.search.spi.SearchIntegratorBuilder.buildSearchIntegrator(SearchIntegratorBuilder.java:128)
        at org.hibernate.search.hcore.impl.HibernateSearchSessionFactoryObserver.boot(HibernateSearchSessionFactoryObserver.java:127)
        at org.hibernate.search.hcore.impl.HibernateSearchSessionFactoryObserver.sessionFactoryCreated(HibernateSearchSessionFactoryObserver.java:94)
        at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:385)
        at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:468)
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259)
        at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)
        ... 110 common frames omitted
        Suppressed: org.hibernate.search.exception.SearchException: HSEARCH400035: Could not update mappings in index 'net.infobank.moyamo.models.photorecentwriter'
                ... 127 common frames omitted
        Caused by: org.hibernate.search.exception.SearchException: HSEARCH400020: Could not create mapping for entity type net.infobank.moyamo.models.PhotoRecentWriter
                at org.hibernate.search.elasticsearch.schema.impl.ElasticsearchSchemaAccessor.putMapping(ElasticsearchSchemaAccessor.java:111)
                at org.hibernate.search.elasticsearch.schema.impl.DefaultElasticsearchSchemaMigrator.migrate(DefaultElasticsearchSchemaMigrator.java:73)
                ... 126 common frames omitted
        Caused by: org.hibernate.search.exception.SearchException: HSEARCH400007: Elasticsearch request failed.
Request: PUT /net.infobank.moyamo.models.photorecentwriter/net.infobank.moyamo.models.PhotoRecentWriter/_mapping with parameters {}
Response: 400 'Bad Request' with body
{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "Mapper for [id] conflicts with existing mapping in other types:\n[mapper [id] has different [store] values]"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "Mapper for [id] conflicts with existing mapping in other types:\n[mapper [id] has different [store] values]"
  },
  "status": 400
}
                at org.hibernate.search.elasticsearch.work.impl.SimpleElasticsearchWork.handleResult(SimpleElasticsearchWork.java:101)
                at org.hibernate.search.elasticsearch.work.impl.SimpleElasticsearchWork.lambda$execute$3(SimpleElasticsearchWork.java:63)
                at java.util.concurrent.CompletableFuture.uniCompose(CompletableFuture.java:966)
                at java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:940)
                at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
                at java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:1975)
                at org.hibernate.search.elasticsearch.client.impl.DefaultElasticsearchClient$1.onFailure(DefaultElasticsearchClient.java:120)
                at org.elasticsearch.client.RestClient$FailureTrackingResponseListener.onDefinitiveFailure(RestClient.java:857)
                at org.elasticsearch.client.RestClient$1.completed(RestClient.java:560)
                at org.elasticsearch.client.RestClient$1.completed(RestClient.java:537)
                at org.apache.http.concurrent.BasicFuture.completed(BasicFuture.java:122)
                at org.apache.http.impl.nio.client.DefaultClientExchangeHandlerImpl.responseCompleted(DefaultClientExchangeHandlerImpl.java:181)
                at org.apache.http.nio.protocol.HttpAsyncRequestExecutor.processResponse(HttpAsyncRequestExecutor.java:448)
                at org.apache.http.nio.protocol.HttpAsyncRequestExecutor.inputReady(HttpAsyncRequestExecutor.java:338)
                at org.apache.http.impl.nio.DefaultNHttpClientConnection.consumeInput(DefaultNHttpClientConnection.java:265)
                at org.apache.http.impl.nio.client.InternalIODispatch.onInputReady(InternalIODispatch.java:81)
                at org.apache.http.impl.nio.client.InternalIODispatch.onInputReady(InternalIODispatch.java:39)
                at org.apache.http.impl.nio.reactor.AbstractIODispatch.inputReady(AbstractIODispatch.java:114)
                at org.apache.http.impl.nio.reactor.BaseIOReactor.readable(BaseIOReactor.java:162)
                at org.apache.http.impl.nio.reactor.AbstractIOReactor.processEvent(AbstractIOReactor.java:337)
                at org.apache.http.impl.nio.reactor.AbstractIOReactor.processEvents(AbstractIOReactor.java:315)
                at org.apache.http.impl.nio.reactor.AbstractIOReactor.execute(AbstractIOReactor.java:276)
                at org.apache.http.impl.nio.reactor.BaseIOReactor.execute(BaseIOReactor.java:104)
                at org.apache.http.impl.nio.reactor.AbstractMultiworkerIOReactor$Worker.run(AbstractMultiworkerIOReactor.java:591)
                at java.lang.Thread.run(Thread.java:750)
        Caused by: org.hibernate.search.exception.SearchException: HSEARCH400090: Elasticsearch response indicates a failure.
                at org.hibernate.search.elasticsearch.work.impl.DefaultElasticsearchRequestSuccessAssessor.checkSuccess(DefaultElasticsearchRequestSuccessAssessor.java:107)
                at org.hibernate.search.elasticsearch.work.impl.DefaultElasticsearchRequestSuccessAssessor.checkSuccess(DefaultElasticsearchRequestSuccessAssessor.java:90)
                at org.hibernate.search.elasticsearch.work.impl.SimpleElasticsearchWork.handleResult(SimpleElasticsearchWork.java:92)
                ... 24 common frames omitted
Caused by: org.hibernate.search.exception.SearchException: HSEARCH400020: Could not create mapping for entity type net.infobank.moyamo.models.AdoptComment
        at org.hibernate.search.elasticsearch.schema.impl.ElasticsearchSchemaAccessor.putMapping(ElasticsearchSchemaAccessor.java:111)
        at org.hibernate.search.elasticsearch.schema.impl.DefaultElasticsearchSchemaMigrator.migrate(DefaultElasticsearchSchemaMigrator.java:73)
        ... 126 common frames omitted
Caused by: org.hibernate.search.exception.SearchException: HSEARCH400007: Elasticsearch request failed.
Request: PUT /moyamo_3/net.infobank.moyamo.models.AdoptComment/_mapping with parameters {}
Response: 400 'Bad Request' with body
{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "mapper [relation.posting.title] of different type, current_type [text], merged_type [keyword]"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "mapper [relation.posting.title] of different type, current_type [text], merged_type [keyword]"
  },
  "status": 400
}


        at org.hibernate.search.elasticsearch.work.impl.SimpleElasticsearchWork.handleResult(SimpleElasticsearchWork.java:101)
        at org.hibernate.search.elasticsearch.work.impl.SimpleElasticsearchWork.lambda$execute$3(SimpleElasticsearchWork.java:63)
        at java.util.concurrent.CompletableFuture.uniCompose(CompletableFuture.java:966)
        at java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:940)
        at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
        at java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:1975)
        at org.hibernate.search.elasticsearch.client.impl.DefaultElasticsearchClient$1.onFailure(DefaultElasticsearchClient.java:120)
        at org.elasticsearch.client.RestClient$FailureTrackingResponseListener.onDefinitiveFailure(RestClient.java:857)
        at org.elasticsearch.client.RestClient$1.completed(RestClient.java:560)
        at org.elasticsearch.client.RestClient$1.completed(RestClient.java:537)
        at org.apache.http.concurrent.BasicFuture.completed(BasicFuture.java:122)
        at org.apache.http.impl.nio.client.DefaultClientExchangeHandlerImpl.responseCompleted(DefaultClientExchangeHandlerImpl.java:181)
        at org.apache.http.nio.protocol.HttpAsyncRequestExecutor.processResponse(HttpAsyncRequestExecutor.java:448)
        at org.apache.http.nio.protocol.HttpAsyncRequestExecutor.inputReady(HttpAsyncRequestExecutor.java:338)
        at org.apache.http.impl.nio.DefaultNHttpClientConnection.consumeInput(DefaultNHttpClientConnection.java:265)
        at org.apache.http.impl.nio.client.InternalIODispatch.onInputReady(InternalIODispatch.java:81)
        at org.apache.http.impl.nio.client.InternalIODispatch.onInputReady(InternalIODispatch.java:39)
        at org.apache.http.impl.nio.reactor.AbstractIODispatch.inputReady(AbstractIODispatch.java:114)
        at org.apache.http.impl.nio.reactor.BaseIOReactor.readable(BaseIOReactor.java:162)
        at org.apache.http.impl.nio.reactor.AbstractIOReactor.processEvent(AbstractIOReactor.java:337)
        at org.apache.http.impl.nio.reactor.AbstractIOReactor.processEvents(AbstractIOReactor.java:315)
        at org.apache.http.impl.nio.reactor.AbstractIOReactor.execute(AbstractIOReactor.java:276)
        at org.apache.http.impl.nio.reactor.BaseIOReactor.execute(BaseIOReactor.java:104)
        at org.apache.http.impl.nio.reactor.AbstractMultiworkerIOReactor$Worker.run(AbstractMultiworkerIOReactor.java:591)
        at java.lang.Thread.run(Thread.java:750)
Caused by: org.hibernate.search.exception.SearchException: HSEARCH400090: Elasticsearch response indicates a failure.
        at org.hibernate.search.elasticsearch.work.impl.DefaultElasticsearchRequestSuccessAssessor.checkSuccess(DefaultElasticsearchRequestSuccessAssessor.java:107)
        at org.hibernate.search.elasticsearch.work.impl.DefaultElasticsearchRequestSuccessAssessor.checkSuccess(DefaultElasticsearchRequestSuccessAssessor.java:90)
        at org.hibernate.search.elasticsearch.work.impl.SimpleElasticsearchWork.handleResult(SimpleElasticsearchWork.java:92)
        ... 24 common frames omitted

대충 상황요약

HSEARCH400035: Could not update mappings in index 'moyamo_3'
Mapper for [id] conflicts with existing mapping in other types:
[mapper [id] has different [store] values]

ElasticSearch + Hibernate Search 관련 매핑 충돌 에러였다.

이게 왜 문제가 발생했냐면 PhotoRecentWriter 엔티티 id 필드 매핑이 기존 ElasticSearch 인덱스 매핑이랑 충돌한 것인데.

쥔짜 식은땀을 흘렷는데 예전 개발자녀석 왜 이렇게 짜놧는지 이해가 안감. 대충 아래의 소스를 살펴보면

package net.infobank.moyamo.models;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import org.hibernate.search.annotations.Indexed;
import org.hibernate.search.annotations.NumericField;

import javax.persistence.*;
import java.time.ZonedDateTime;

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Indexed
public class PhotoRecentWriter {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @NumericField
    private Long id;

    @NonNull
    @Embedded
    private UserPostingRelation relation;

    private ZonedDateTime createdAt;

    public PhotoRecentWriter(@NonNull UserPostingRelation userPostingRelation, ZonedDateTime createdAt){
        this.relation = userPostingRelation;
        this.createdAt = createdAt;
    }
}

이 부분에서 저기 보이는 Indexed 어노테이션을 사용하면서 생기는 문제였다. 인덱스 이름을 지정하지 않아서 생기는 문제인 것인데, 그래서 기본 클래스명인 net.infobank.moyamo.models.photorecentwriter 으로 별도 인덱스가 만들어졌던거임.

근데? 에러 로그를 보면 다음과 같다.

Could not update mappings in index 'moyamo_3'

moyamo_3 인덱스 내에서 PhotoRecentWriter의 id 필드가 다른 엔티티 의 id 필드 매핑이랑 store 값이 충돌하기 때문

대충 차이점을 잡고가자면 이런 부분이다

• User.java, Posting.java 의 id:@NumericFiled + @SortableField (store 속성 없음 = 기본값 Store.NO)
• PhotoRecentWriter.java의 id:@NumericField만 있다는 것

이 부분을 해결하려는 방법은 되게 간단했다.

PhotoRecentWriter의 id 필드에 동일한 어노테이션을 적용하거나, 아예 ElasticSearch 인덱싱이 필요 없으면 Indexed 어노테이션을 제거하면 된다.

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
public class PhotoRecentWriter {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NonNull
    @Embedded
    private UserPostingRelation relation;

    private ZonedDateTime createdAt;

    public PhotoRecentWriter(@NonNull UserPostingRelation userPostingRelation, ZonedDateTime createdAt){
        this.relation = userPostingRelation;
        this.createdAt = createdAt;
    }
}

실제로 PhotoRecentWriter 가 검색기능에 사용되지 않으므로 제거하면되는 부분이므로 JPA엔티니니까 DB에서만 쓰는거임 ㅇㅇ

그런데? 난 사실 이걸 먼저하기보다 이 짓을 먼저했음

curl -X DELETE "localhost:9200/net.infobank.moyamo.models.photorecentwriter"

그러니까 502 Bad Gateway는 해결되서 올라오긴 하는데, 사람들이 예전에 쓴 글이 안올라온다고 하는 대략난감의 사태가 벌어졌다는 거쉼..

이게 영향이 있냐 없냐 고민을 하다보니 100% 있다는 것임!

그렇게 시작되었다. Hibernate Search에서 기존 DB 데이터를 ElasticSearch에 다시 인덱싱하는 개삽질의 작업

식은땀을 줄줄 흘리다가. 나자신 생각했다. 하늘이 무너져도 솟아날 구멍? 댓츠논노

어쨋든 저놈들은 죄다 ElasticSearch 기반의 작업일꺼니까 인덱스만 다시 치면 된다는 생각이었음.

솔직히 JPA 안에 본체가 있는데 ElasticSearch가 캐싱하는 데이터 구도라면? 캐싱만 치면 되는 부분임

심지어 찾아보니 땅굴을 좀 파놧는데 이런 것들이 있었음

1. Posting 인덱스 재구축

# POST 요청 - sinceId부터 maxId까지 게시글 인덱싱
curl -X POST "http://<scheduler-server>/indexing?sinceId=0&maxId=999999999&max=200"

또는 api-server 에서

# 특정 게시글 ID들 인덱싱
curl -X POST "http://<api-server>/v2/postings/index?ids=1,2,3,4,5&max=200"

2. User 인덱스 재구축

# moyamo-api-server에서
curl -X GET "http://<api-server>/v2/users/index"

그런데 아무리 생각해봐도 API에다가 저짓을 하다가는 서버가 죽을 것이 뻔했음.

MySQL 에서 데이터를 조사함

-- MySQL에서 게시글 ID 범위 확인
SELECT MIN(id), MAX(id) FROM posting WHERE is_delete = false;

참으로 기모찌한 결과가 나온 것인데, 이걸 curl 로 나눠서 쏘다가는 꽤 귀찮을 것 같다는 거쉬..

그래서 인덱스 스크립트를 만들어서 때려박기로 했다.

#!/bin/bash
# Elasticsearch 인덱스 재구축 스크립트 (Linux/Mac용)
# 게시글 ID 범위: 114 ~ 16,335,566

SCHEDULER_URL="http://localhost:8088"  # moyamo-scheduler 포트: 8088
BATCH_SIZE=10000                        # 한 번에 처리할 ID 범위
MAX_COMMENTS=200                        # 댓글 수 제한
DELAY_SECONDS=2                         # 배치 간 대기 시간

MIN_ID=114
MAX_ID=16335566

CURRENT_ID=$MIN_ID
TOTAL_BATCHES=$(( (MAX_ID - MIN_ID + BATCH_SIZE - 1) / BATCH_SIZE ))
CURRENT_BATCH=0

echo "========================================"
echo "Elasticsearch 인덱스 재구축 시작"
echo "총 배치 수: $TOTAL_BATCHES"
echo "ID 범위: $MIN_ID ~ $MAX_ID"
echo "배치 크기: $BATCH_SIZE"
echo "========================================"

START_TIME=$(date +%s)

while [ $CURRENT_ID -le $MAX_ID ]; do
    CURRENT_BATCH=$((CURRENT_BATCH + 1))
    END_ID=$((CURRENT_ID + BATCH_SIZE - 1))
    if [ $END_ID -gt $MAX_ID ]; then
        END_ID=$MAX_ID
    fi

    PROGRESS=$(echo "scale=1; $CURRENT_BATCH * 100 / $TOTAL_BATCHES" | bc)
    echo "[$CURRENT_BATCH/$TOTAL_BATCHES] ($PROGRESS%) 인덱싱 중: $CURRENT_ID ~ $END_ID"

    RESPONSE=$(curl -s -X POST "$SCHEDULER_URL/indexing?sinceId=$CURRENT_ID&maxId=$END_ID&max=$MAX_COMMENTS" --max-time 300)

    if [ $? -eq 0 ]; then
        echo "  완료: $RESPONSE"
    else
        echo "  오류 발생, 재시도 중..."
        sleep 5
        RESPONSE=$(curl -s -X POST "$SCHEDULER_URL/indexing?sinceId=$CURRENT_ID&maxId=$END_ID&max=$MAX_COMMENTS" --max-time 300)
        if [ $? -eq 0 ]; then
            echo "  재시도 완료: $RESPONSE"
        else
            echo "  재시도 실패, 다음 배치로 진행"
        fi
    fi

    CURRENT_ID=$((END_ID + 1))

    if [ $CURRENT_ID -le $MAX_ID ]; then
        sleep $DELAY_SECONDS
    fi
done

END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
HOURS=$((DURATION / 3600))
MINUTES=$(( (DURATION % 3600) / 60 ))
SECONDS=$((DURATION % 60))

echo "========================================"
echo "인덱싱 완료!"
echo "소요 시간: ${HOURS}시간 ${MINUTES}분 ${SECONDS}초"
echo "========================================"

일단 테스트도 완료했겠다 신나게 돌려본다는 것인데..

그 전에 주머니에서 주섬주섬 moyamo-common 부터 말은 이후에 api-server를 말아서 build.jar로 만든다음에 배포 했다는 스토리는 생략하도록 하고, 저게 돌아가는건 결국 스케쥴러가 돌아가는 인스턴스에서 돌아가고 있는 부분이었으니까 포트부터 조사하면

스크립트 → moyamo-scheduler → Elasticsearch
              (/indexing API)      (내부적으로 연결)

이런 구조가 나온다는 것이었다.

생각해보니 Tools 서버를 껏다키면서 ElasticSearch 때문에 잘 안올라오는 다는 부분을 생각해보면서 볼륨 detach 했다가 다시 attach 하고 그런 부분 와중에 데몬을 제대로 안살렸었더라는 기억이 있었으므로 스케쥴러부터 살려줌

ubuntu@ip-172-31-40-155:~/workspace/scheduler/logs$ ps -ef | grep scheduler
ubuntu   15665  6442  0 12:34 pts/1    00:00:00 grep --color=auto scheduler
ubuntu@ip-172-31-40-155:~/workspace/scheduler/logs$ cd ..
ubuntu@ip-172-31-40-155:~/workspace/scheduler$ ls -alk
total 40
drwxrwxr-x 5 ubuntu ubuntu  4096 Oct  7  2024 .
drwxrwxr-x 4 ubuntu ubuntu  4096 Mar 30  2021 ..
drwxrwxr-x 2 ubuntu ubuntu  4096 Oct 21  2024 bin
drwxrwxr-x 2 ubuntu ubuntu 16384 Mar 30 12:32 logs
-rwxrwxr-x 1 ubuntu ubuntu   253 Oct 13  2023 start.sh
drwxrwxr-x 3 ubuntu ubuntu  4096 Sep  7  2020 static
-rwxrwxr-x 1 ubuntu ubuntu    58 Apr 16  2021 stop.sh
ubuntu@ip-172-31-40-155:~/workspace/scheduler$ vi start.sh 
ubuntu@ip-172-31-40-155:~/workspace/scheduler$ vi start.sh 
ubuntu@ip-172-31-40-155:~/workspace/scheduler$ ./start.sh 
ubuntu@ip-172-31-40-155:~/workspace/scheduler$ ps -ef | grep scheduler
ubuntu   18233     1 99 12:36 pts/1    00:00:11 java -Xmx1500m -Xms1500m -jar -Dspring.profiles.active=product -Dmoyamo.jobs.rankings.cron=0 */1 * * * * -Dmoyamo.jobs.statistics.cron=0 */1 * * * * /home/ubuntu/workspace/scheduler/bin/moyamo-scheduler-0.0.1-SNAPSHOT.jar
ubuntu   18373  6442  0 12:36 pts/1    00:00:00 grep --color=auto scheduler

이제 언제 살아날지 모르는 대망의 작업을 시작한다.

ubuntu@ip-172-31-40-155:~$ chmod +x reindex-elasticsearch.sh 
ubuntu@ip-172-31-40-155:~$ ./reindex-elasticsearch.sh 
========================================
Elasticsearch 인덱스 재구축 시작
총 배치 수: 1634
ID 범위: 114 ~ 16335566
배치 크기: 10000
========================================
[1/1634] (0%) 인덱싱 중: 114 ~ 10113
  완료: end
[2/1634] (.1%) 인덱싱 중: 10114 ~ 20113
  완료: end
[3/1634] (.1%) 인덱싱 중: 20114 ~ 30113
  완료: end
[4/1634] (.2%) 인덱싱 중: 30114 ~ 40113
  완료: end
[5/1634] (.3%) 인덱싱 중: 40114 ~ 50113
  완료: end
[6/1634] (.3%) 인덱싱 중: 50114 ~ 60113
  완료: end
[7/1634] (.4%) 인덱싱 중: 60114 ~ 70113
  완료: end
[8/1634] (.4%) 인덱싱 중: 70114 ~ 80113
  완료: end
[9/1634] (.5%) 인덱싱 중: 80114 ~ 90113
  완료: end
[10/1634] (.6%) 인덱싱 중: 90114 ~ 100113
  완료: end
[11/1634] (.6%) 인덱싱 중: 100114 ~ 110113
  완료: end
[12/1634] (.7%) 인덱싱 중: 110114 ~ 120113
  완료: end
[13/1634] (.7%) 인덱싱 중: 120114 ~ 130113
  완료: end
[14/1634] (.8%) 인덱싱 중: 130114 ~ 140113
  완료: end
[15/1634] (.9%) 인덱싱 중: 140114 ~ 150113
  완료: end
[16/1634] (.9%) 인덱싱 중: 150114 ~ 160113

개요

MySQL 서버를 포함한 RDBMS를 사용하다 보면, No-SQL DBMS 서버에 비해서 많은 데이터 타입들을 가지고 있다는 것을 알고 있을거에요. 하지만 RDBMS를 사용하면서 이런 다양한 데이터 타입에 대해서 정확한 용도와 특성을 모르면 RDBMS 서버가 어렵게 구현하고 있는 장점을 놓쳐 버릴 가능성이 높아요.

오늘은 많은 개발자와 DBA들이 잘 모르고 있는 MySQL 서버의 VARCHAR 와 TEXT 타입의 특성과 작동 방식에 대해서 좀 살펴보려고 해요.

 

VARCHAR 타입 궁금증

MySQL 서버를 사용해 본 개발자라면 누구나 한번쯤은 이런 궁금증을 가져 본 적이 있을거에요.

• 만약 10 글자 이하로만 저장된다면 컬럼의 타입을 VARCHAR(10)으로 하거나 VARCHAR(1000)으로 해도 아무런 차이가 없는 것 아닐까? 오히려 VARCHAR(1000)으로 만들어 두면 나중에 더 큰 값을 저장해야 할 때 더 유연하게 대응할 수 있지 않을까 ?

아래와 같이 모든 컬럼을 VARCHAR(1000) 타입으로 또는 모든 컬럼을 TEXT 타입으로 생성한 테이블은 어떻게 생각하나요 ? 이런 모델링이 잘못되었다고 생각한다면 그 근거는 무엇인가요 ?

CREATE TABLE user (
  id       BIGINT NOT NULL,
  name     VARCHAR(1000),
  phone_no VARCHAR(1000),
  address  VARCHAR(1000),
  email    VARCHAR(1000),
  PRIMARY KEY(id)
);

그냥 지금까지 습관적으로 해오던 데이터 모델링 방법과는 다르기 때문에 잘못된 것일까요 ? MySQL 서버가 내부적으로 어떻게 작동하는지를 모르면, 이 질문에 명확한 답변을 하기는 어려울 수 있어요.

테이블의 컬럼이 많은 경우, 이 질문에 대해서 명확한 답변이 될만한 근거가 한가지 있어요. 간단한 테스트를 위해서 길이가 매우 긴 VARCHAR 컬럼을 가진 테이블을 한번 만들어 볼까요 ?

mysql> CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd1 VARCHAR(1000000));
ERROR 1074 (42000): Column length too big for column 'fd1' (max = 16383); use BLOB or TEXT instead

mysql> CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd1 VARCHAR(16383));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd VARCHAR(16382));
Query OK, 0 rows affected (0.19 sec)

mysql> ALTER TABLE tb_long_varchar ADD fd2 VARCHAR(10);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

tb_long_varchar 테이블은 하나의 VARCHAR 컬럼이 있는데, VARCHAR 타입의 최대 저장 가능 길이를 어느 정도로 하느냐에 따라서 테이블을 생성하지 못하게 되는 것을 확인할 수 있어요. 그리고 네번째 ALTER TABLE 문장의 실행 예제를 보면, 새로운 컬럼 추가가 실패한 것을 알 수 있어요. 이는 (에러 메시지에서도 잘 설명하고 있듯이) 이미 tb_long_varchar 테이블은 하나의 레코드가 저장할 수 있는 최대 길이가 65,535 바이트를 초과했기 때문에 더이상 새로운 컬럼을 추가할 수 없게 된 거에요.

이 예제를 통해서 MySQL 서버에서는 하나의 VARCHAR 컬럼이 너무 큰 길이를 사용하면, 다른 컬럼들이 사용할 수 있는 최대 공간의 크기가 영향을 받게 된다는 것을 확인했어요. 그래서 MySQL 서버에서는 레코드 사이즈 한계로 인해서, VARCHAR 타입의 최대 저장 길이 설정시에 공간을 아껴서 설정해야 해요.

이는 MySQL 서버 메뉴얼에서 이미 자세히 설명하고 있어요. 참고로 TEXT나 BLOB와 같은 LOB 컬럼은 이 제한 사항에 거의 영향을 미치지 않아요. 그래서 많은 컬럼을 가진 테이블에서는 VARCHAR 타입 대신 TEXT 타입을 사용해야 할 수도 있어요.

그런데 VARCHAR 타입의 길이 설정에 주의해야 하는 이유가 이거 하나 뿐일까요 ? 예를 들어서 추가로 새로운 컬럼이 필요치 않아서 아래와 같이 테이블을 모델링했다면, 이건 아무 문제가 없는 걸까요 ?

CREATE TABLE user (
  id       BIGINT NOT NULL,
  name     VARCHAR(4000),
  phone_no VARCHAR(4000),
  address  VARCHAR(4000),
  email    VARCHAR(4000),
  PRIMARY KEY(id)
);

TEXT 타입 궁금증

VARCHAR 타입은 저장 길이 설정에 대한 주의가 필요하다는 것을 간단히 한번 살펴보았어요. 그런데 VARCHAR 대신 TEXT 타입을 사용하면 길이 제한 문제가 싹 사라진다는 것을 쉽게 확인할 수 있어요. 그래서 아래와 같이 테이블을 만들면 VARCHAR 타입의 길이 설정에 대한 제약뿐만 아니라 저장하는 값의 길이 제한도 훨씬 크고 유연하게 테이블을 만들 수 있어요.

CREATE TABLE user (
  id       BIGINT NOT NULL,
  name     TEXT,
  phone_no TEXT,
  address  TEXT,
  email    TEXT,
  PRIMARY KEY(id)
);

여기에서 또 하나의 궁금증이 생기기 시작할거에요.

• 문자열 저장용 컬럼을 생성할 때, VARCHAR와 TEXT 중에서 굳이 VARCHAR를 선택할 이유가 있을까 ? TEXT 타입은 저장 가능 길이도 훨씬 크고 테이블 생성할 때 굳이 길이 제한을 결정하지 않아도 되니 더 좋은 것 아닐까 ? 근데 왜 우리가 모델링하는 테이블에서 대부분 문자열 저장용 컬럼은TEXT 컬럼이 아니라 VARCHAR 컬럼이 사용될까 ?

 

VARCHAR vs TEXT

일반적인 RDBMS에서, TEXT(또는 CLOB)나 BLOB와 같은 대용량 데이터를 저장하는 컬럼 타입을 LOB(Large Object) 타입이라고 해요. 그리고 RDBMS 서는 LOB 데이터를 Off-Page 라고 하는 외부 공간에 저장해요. 일반적인 RDBMS에서와 같이, MySQL 서버도 레코드의 컬럼 데이터는 B-Tree (Clustering Index)에 저장(이를 Inline 저장이라고 함)하지만, 용량이 큰 LOB 데이터는 B-Tree 외부의 Off-Page 페이지(MySQL 서버 메뉴얼에서는 External off-page storage라고 해요)로 저장해요.

하지만 MySQL 서버는 LOB 타입의 컬럼을 항상 Off-Page로 저장하지는 않고, 길이가 길어서 저장 공간이 많이 필요한 경우에만 Off-Page로 저장해요.

예를 들어서 아래와 같이 2개의 레코드가 있을 때, 1번 레코드(id=1)의 fd 컬럼에 8,100 글자(8,100바이트)를 저장하면 Off-Page가 아닌 B-Tree(Clustering Index)에 Inline으로 저장해요. 하지만 2번 레코드(id=2)의 fd 컬럼에 8,101글자(8,101바이트)를 저장하면, MySQL 서버는 fd 컬럼을 Off-Page로 저장해요. 이는 MySQL 서버의 레코드 포맷에 따라서 조금씩 다르게 작동하는데, 이 예제는 innodb_default_row_format=DYNAMIC 설정을 기준으로 테스트해본 결과에요.

CREATE TABLE tb_lob (
  id INT PRIMARY KEY,
  fd TEXT
);

INSERT INTO tb_lob VALUES (1, REPEAT('A',8100));  -- // Inline 저장소
INSERT INTO tb_lob VALUES (2, REPEAT('A',8101));  -- // Off-Page 저장소

MySQL 서버의 레코드 크기 제한은 65,535 바이트이지만, InnoDB 스토리지 엔진의 레코드 크기 제한은 페이지(블록)의 크기에 따라서 달라지는데, 대부분 페이지 크기의 절반이 InnoDB 스토리지 엔진의 최대 레코드 크기 제한으로 작동해요.

InnoDB 스토리지 엔진은 레코드의 전체 크기가 이 제한 사항(16KB 페이지에서는 8,117 바이트)을 초과하면 길이가 긴 컬럼을 선택해서 Off-Page로 저장하게 되는데, 이 예제의 두번째 레코드(id=2)의 fd 컬럼 값이 커서 이 컬럼을 Off-Page로 저장한 것이에요.

MySQL 서버의 InnoDB row_format에 따른 Off-Page 저장 방식 차이는 MySQL 서버 메뉴얼을 참고해주세요. 페이지 크기가 64KB인 경우 InnoDB 최대 레코드 크기의 제한 사항이 예외적으로 조금 다르므로, MySQL 서버와 InnoDB 스토리지 엔진의 레코드 크기 제한에 대한 자세한 설명은 MySQL 서버 메뉴얼을 참고해주세요.

그런데 동일한 테스트를 아래와 같이 VARCHAR 타입 컬럼으로 해보면, VARCHAR 컬럼에 저장된 값이 큰 경우에도 Off-Page로 저장된다는 것이에요.

CREATE TABLE tb_varchar (
  id INT PRIMARY KEY,
  fd VARCHAR
);

INSERT INTO tb_varchar VALUES (1, REPEAT('A',8100)); -- // Inline 저장소
INSERT INTO tb_varchar VALUES (2, REPEAT('A',8101)); -- // Off-Page 저장소

VARCHAR 타입은 인덱스를 생성할 수 있는 반면 LOB 타입은 인덱스 생성을 할 수 없다는 이야기를 하는 사람도 있지만, 사실은 둘다 최대 크기 길이 제한만 충족시켜 주면 인덱스를 생성 할 수 있어요.

-- // 컬럼 그대로 사용시, 인덱스 생성 불가
mysql> ALTER TABLE tb_varchar ADD INDEX ix_fd (fd);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

mysql> ALTER TABLE tb_lob ADD INDEX ix_fd (fd);
ERROR 1170 (42000): BLOB/TEXT column 'fd' used in key specification without a key length

-- // 컬럼 값의 길이(프리픽스)를 지정하면, 인덱스 생성 가능
mysql> ALTER TABLE tb_varchar ADD INDEX ix_fd ( fd(50) );
mysql> ALTER TABLE tb_lob ADD INDEX ix_fd ( fd(50) );

B-Tree 인덱스뿐만 아니라 전문 검색 인덱스도 TEXT 타입과 VARCHAR 타입 컬럼 모두 동일하게 생성할 수 있어요. 보면 볼수록 TEXT와 VARCHAR의 차이가 명확해지기 보다는 오히려 모호해지고 있다는 느낌일 거에요. 도대체 TEXT 컬럼과 VARCHAR 컬럼의 차이는 무엇이며, 어떤 경우에 TEXT 타입을 사용하고 어떤 경우에 VARCHAR 타입을 사용해야 할까요 ?

 

VARCHAR와 TEXT의 메모리 활용

MySQL 서버는 스토리지 엔진과 Handler API를 이용해서 데이터를 주고 받는데, 이때 MySQL 엔진과 InnoDB 스토리지 엔진은 uchar* records[2] 메모리 포인터를 이용해서 레코드 데이터를 주고 받아요. 이때 records[2] 메모리 객체는 실제 레코드의 데이터 크기에 관계 없이 최대 크기로 메모리를 할당해둬요. VARCHAR 타입은 최대 크기가 설정되기 때문에 메모리 공간을 records[2] 버퍼에 미리 할당받아둘 수 있지만, TEXT나 BLOB와 같은 LOB 컬럼 데이터의 경우 실제 최대 크기만큼 메모리를 할당해 두면 메모리 낭비가 너무 심해지는 문제가 있어요. 그래서 records[2] 포인터가 가리키는 메모리 공간은 VARCHAR는 포함하지만 TEXT 컬럼을 위한 공간은 포함하지 않아요.

uchar* records[2] 메모리 공간은 TABLE 구조체(struct) 내에 정의되어 있으며 TABLE 구조체는 MySQL 서버 내부에 캐싱되어서 여러 컨넥션에서 공유해서 사용될 수 있도록 구현되어 있어요. 즉, records[2] 메모리 버퍼는 처음 한번 할당되면 많은 컨넥션들에 의해서 재사용될 수 있도록 설계된 것이에요.
하지만 TEXT나 BLOB과 같은 LOB 컬럼을 위한 메모리 공간은 records[2]에 미리 할됭되어 있지 않기 때문에 매번 레코드를 읽고 쓸 때마다 필요한 만큼 메모리가 할당되어야 해요.

예를 들어서 아래와 같은 테이블을 생성했다면,

CREATE TABLE tb_lob (
  id INT PRIMARY KEY,
  fd TEXT
);

CREATE TABLE tb_varchar1 (
  id INT PRIMARY KEY,
  fd VARCHAR(100)
);

CREATE TABLE tb_varchar2 (
  id INT PRIMARY KEY,
  fd VARCHAR(10000)
);

tb_lob 테이블을 위한 records[2] 버퍼 공간은 16 * 2 바이트만큼 할당되고, tb_varchar1 테이블의 records[2] 버퍼 공간으로는 408 * 2 바이트를 할당해요. 그리고 마지막 tb_varchar2 테이블을 위해서는 40008 * 2 바이트를 할당해요.

• tb_lob 테이블은 INT 타입의 컬럼(id)을 위한 4 바이트와 TEXT 값을 위한 포인터 공간 8바이트 그리고 헤더 공간 4바이트
• tb_varchar1 테이블은 INT 타입의 컬럼(id)을 위한 4 바이트와 VARCHAR(100)타입 컬럼을 위한 공간 400바이트 그리고 헤더 공간 4바이트
• tb_varchar2 테이블은 INT 타입의 컬럼(id)을 위한 4 바이트와 VARCHAR(10000) 타입 컬럼을 위한 공간 40000바이트 그리고 헤더 공간 4바이트

그래서 VARCHAR 타입의 컬럼을 읽을 때는 새롭게 메모리를 할당받는 것이 아니라 TABLE 구조체의 records[2] 버퍼를 이용해요. 하지만 TEXT나 BLOB와 같은 LOB 타입의 컬럼을 읽을 때는 (미리 할당해 둔 메모리 공간이 없기 때문에) 매번 필요한 크기만큼 메모리를 할당해서 사용후 해제해야 해요. LOB 컬럼의 값을 읽기 위해서 할당 및 해제하는 메모리 공간은 Performance_schema에 의해서 측정되지 않아요 (MySQL 8.0.33 기준). 그래서 LOB용 메모리 할당 해제가 실행되는지 알 수 없어서 성능 영향도를 파악하기가 어려운 상황이에요. 한가지 더 주의해야 할 것은 VARCHAR 타입에 저장된 값의 길이가 길어서 Off-Page로 저장된 경우, MySQL 서버는 TABLE 객체의 records[2] 버퍼를 사용하지 못하고 새롭게 메모리 공간을 할당해서 사용해요. 그래서 VARCHAR 타입에 매우 큰 값이 빈번하게 저장되는 경우는 주의가 필요해요.

 

컬럼 타입 선정 규칙

MySQL 서버의 내부적인 작동에서, VARCHAR와 TEXT 타입의 큰 차이점을 살펴보았어요. 지금까지 살펴본 내용을 토대로 VARCHAR나 TEXT 타입을 선택하는 규칙을 다음과 같이 정리해 볼 수 있어요.

VARCHAR

• 최대 길이가 (상대적으로) 크지 않은 경우
• 테이블 데이터를 읽을 때 항상 해당 컬럼이 필요한 경우
• DBMS 서버의 메모리가 (상대적으로) 충분한 경우

TEXT

• 최대 길이가 (상대적으로) 큰 경우
• 테이블에 길이가 긴 문자열 타입 컬럼이 많이 필요한 경우
• 테이블 데이터를 읽을 때 해당 컬럼이 자주 필요치 않은 경우

상대적이라는 단어가 많이 사용된 것은 DBMS 서버의 스펙이나 데이터 모델 그리고 유입되는 트래픽에 따라서 미치는 영향도가 다르기 때문이에요. 뿐만 아니라 DBMS 서버의 튜닝은 생산성(속도)과 효율성 사이에서 최적점(sweet-spot)을 찾는 과정이기 때문에 숫자 값 하나를 모든 판단의 기준으로 정하는 것은 불가능해요.

https://medium.com/daangn/varchar-vs-text-230a718a22a1

•  MySQL 5.5
• MySQL 8.0

목차

• 1. 개요
• 2. 사전 조건
• 3. 중복 처리 방법
  • 3-1) INSERT IGNORE
  • 3-2) REPLACE INTO
  • 3-3) ON DUPLICATE UPDATE

1. 개요

MySQL에는 아래 3가지 방법을 이용하여 중복 레코드를 관리할 수 있다.

1. INSERT IGNORE ...
2. REPLACE INTO ...
3. INSERT INTO ... ON DUPLICATE UPDATE

각 방법의 특징을 요약하면 다음과 같다.

방법특징

2. 사전 조건

중복 레코드 관리를 위해선 테이블에 PRIMARY KEY 혹은 UNIQUE INDEX가 필요하다.

본 예제에서는 아래와 같은 person 테이블을 이용하여 설명한다.

CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
  address VARCHAR(255),
  PRIMARY KEY (id),
  UNIQUE INDEX (name) -- 중복 검사용 필드
);

person 테이블에서 PRIMARY KEY로 지정된 id 필드는 AUTO_INCREMENT를 위해 만든 필드이며, 본 문서에는 name 필드를 이용하여 중복을 검사한다.

만약 기존에 만들어진 테이블에 PRIMARY KEY 혹은 UNIQUE INDEX를 추가하려면 아래의 SQL을 이용하면 된다.

-- PRIMARY 추가하는 방법
ALTER TABLE person ADD PRIMARY KEY (name)

-- UNIQUE INDEX를 추가하는 방법
ALTER TABLE person ADD UNIQUE INDEX (name)

3. 중복 처리 방법

3-1) INSERT IGNORE

INSERT IGNORE는 중복 키 에러가 발생했을 때 신규로 입력되는 레코드를 무시하는 단순한 방법이다.

다음의 예를 보면 중복 키 에러가 발생했을 때 INSERT 구문 자체는 오류가 발생하지 않고, 대신’0 row affected’가 출력된 것을 볼 수 있다.

mysql> INSERT IGNORE INTO person VALUES (NULL, 'James', 'Seoul');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person VALUES (NULL, 'Cynthia', 'Yongin');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person VALUES (NULL, 'James', 'Seongnam');
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO person VALUES (NULL, 'Cynthia', 'Seoul');
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO person VALUES (NULL, 'James', 'Incheon');
Query OK, 0 rows affected (0.00 sec)

SELECT의 결과는 2건만 존재한다.

mysql> SELECT * FROM person;
+----+---------+---------+
| id | name    | address |
+----+---------+---------+
|  1 | James   | Seoul   |
|  2 | Cynthia | Yongin  |
+----+---------+---------+
2 rows in set (0.00 sec)

James의 주소가 최초 입력한 Seoul이다. 즉, 최초에 입력된 레코드가 남아 있는 걸을 볼 수 있다.

또한 AUTO_INCREMENT 컬럼의 값이 1, 2인 것에 주목하라.

MySQL에서 AUTO_INCREMENT는 식별키 용도로 많이 사용하는데, 중복 발생 여부에 따라 식별 키가 변경되는 경우 여러 가지 불편한 점이 생긴다.

INSERT IGNORE에서는 AUTO_INCREMENT의 값이 변경되지 않는다는 장점이 있다.

3-2) REPLACE INTO

REPLACE INTO는 중복이 발생되었을 때 기존 레코드를 삭제하고 신규 레코드를 INSERT하는 방식이다.

person 테이블을 drop 후 다시 생성한 뒤에 아래의 레코드를 입수해보자.

mysql> REPLACE INTO person VALUES (NULL, 'James', 'Seoul');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person VALUES (NULL, 'Cynthia', 'Yongin');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person VALUES (NULL, 'James', 'Seongnam');
Query OK, 2 rows affected (0.00 sec)

mysql> REPLACE INTO person VALUES (NULL, 'Cynthia', 'Seoul');
Query OK, 2 rows affected (0.00 sec)

mysql> REPLACE INTO person VALUES (NULL, 'James', 'Incheon');
Query OK, 2 rows affected (0.00 sec)

세번째 레코드를 입수할 때부터는 ‘2 rows affected’가 출력되었다. (INSERT IGNORE에서는 ‘0 rows affected’가 출력되었었다)

mysql> SELECT * FROM person;
+----+---------+---------+
| id | name    | address |
+----+---------+---------+
|  4 | Cynthia | Seoul   |
|  5 | James   | Incheon |
+----+---------+---------+
2 rows in set (0.00 sec)

id가 4, 5로 변하였다. 또한 James의 주소가 “Incheon”으로 변경되었다.

이를 ‘2 rows affected’와 함께 종합적으로 판단한다면 “REPLACE INTO”는 다음과 같이 작동하는 것을 알 수 있다.

• 중복 키 오류 발생 시 기존 레코드를 삭제 -> 첫 번째 레코드가 affected되었음
• 이후 새로운 레코드를 입력 -> 두 번째 레코드가 affected되었음

그래서 ‘2 rows affected’가 출력되었다.

새로운 레코드가 입력되면서 AUTO_INCREMENT 컬럼의 값이 매번 새롭게 발급되었다.

“REPLACE INTO”는 그다지 좋은 방법이 아닌데 앞서 이야기한 것처럼 AUTO_INCREMENT는 레코드를 식별할 수 있는 id로 사용되는데 중복 발생 시 id가 변경되기 때문이다.

3-3) ON DUPLICATE UPDATE

ON DUPLICATE UPDATE의 장점은 중복 키 오류 발생 시 사용자가 원하는 값을 직접 설정할 수 있다는 점이다.

우선 기본적인 사용 방법을 보자.

마찬가지로 테이블을 drop후 새로 생성했다.

mysql> INSERT INTO person VALUES (NULL, 'James', 'Seoul')
           ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'Cynthia', 'Yongin')
           ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'James', 'Seongnam')
           ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 2 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO person VALUES (NULL, 'Cynthia', 'Seoul')
           ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 2 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'James', 'Incheon')
           ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 2 rows affected, 1 warning (0.01 sec)

(‘2 rows affected’의 의미는 아래 내용을 읽고 각자 생각해보자)

SELECT 결과를 보자.

mysql> SELECT * FROM person;
+----+---------+---------+
| id | name    | address |
+----+---------+---------+
|  1 | James   | Incheon |
|  2 | Cynthia | Seoul   |
+----+---------+---------+
2 rows in set (0.00 sec)

이번에는 id 값이 최초 입수된 레코드의 값 그대로이다. 하지만 address의 값이 마지막에 입수한 레코드로 변경되어 있다.

INSERT INTO ... ON DUPLICATE KEY UPDATE의 장점은 중복 발생 시 필드의 값을 내 맘대로 UPDATE할 수 있다는 점이다.

id 필드만 놓고 보면 INSERT IGNORE와 동일하지만 address의 값이 변경된 것이 INSERT IGNORE와 INSERT INTO ... ON DUPLICATE KEY UPDATE의 차이점이다.

ON DUPLICATE KEY UPDATE를 이용하면 재미있는 것들을 할 수 있다. 중복 레코드가 총 몇 번이나 입수되었는지를 기록해보자.

이를 위해 inserted_cnt 필드를 추가하였다.

CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
  address VARCHAR(255),
  inserted_cnt INT, -- 레코드가 몇 번 입수되었는지 확인용 필드
  PRIMARY KEY (id),
  UNIQUE INDEX (name)
);

mysql> INSERT INTO person VALUES (NULL, 'James', 'Seoul', 1)
           ON DUPLICATE KEY UPDATE inserted_cnt = inserted_cnt + 1;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'Cynthia', 'Yongin', 1)
           ON DUPLICATE KEY UPDATE inserted_cnt = inserted_cnt + 1;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'James', 'Seongnam', 1)
           ON DUPLICATE KEY UPDATE inserted_cnt = inserted_cnt + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'Cynthia', 'Seoul', 1)
           ON DUPLICATE KEY UPDATE inserted_cnt = inserted_cnt + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO person VALUES (NULL, 'James', 'Incheon', 1)
           ON DUPLICATE KEY UPDATE inserted_cnt = inserted_cnt + 1;
Query OK, 2 rows affected (0.00 sec)

SELECT를 해 보면 inserted_cnt에는 해당 중복 값이 몇 번 INSERT 시도가 되었는지 기록되어 있을 것이다.

mysql> SELECT * FROM person;
+----+---------+---------+--------------+
| id | name    | address | inserted_cnt |
+----+---------+---------+--------------+
|  1 | James   | Seoul   |            3 |
|  2 | Cynthia | Yongin  |            2 |
+----+---------+---------+--------------+
2 rows in set (0.00 sec)

inserted_cnt 필드의 값을 보면 알겠지만, 레코드가 몇 번 입수되었는지 저장되어 있다.

주의해야 할 점은 새로온 레코드의 값으로 UPDATE하고자 할 때는 항상 “VALUES(column)”과 같이 VALUES()로 감싸야 한다는 점이다. 만약 다음과 같이 VALUES()` 없이 필드명만 사용하는 것은 기존 레코드의 컬럼 값을 의미하게 된다.

INSERT INTO person VALUES (NULL, 'James', 'Incheon')
    ON DUPLICATE KEY UPDATE address = address;

따라서 address의 값이 UPDATE되지 않는다.

Origin

https://jason-heo.github.io/mysql/2014/03/05/manage-dup-key2.html