고객 검색 최적화 트러블 슈팅

고객 검색 최적화 트러블 슈팅 경험

많은 부분이 부족할 수 있고, 제 경험을 적는 글이니 양해 부탁한다. 이번 글에서는 고객 검색 최적화 작업을 통해 경험했던 트러블 슈팅 사례를 공유하고자 한다.

배경

기존의 고객 검색은 ORM을 통해 이루어졌으며, 인덱스를 제외한 최적화가 이루어지지 않았다.(2~6초 이상)
더 세부적인 검색 요구 사항이 발생함에 따라, ORM을 쿼리문으로 변경하고 최적화에 초점을 맞추고자 했다.

전제 조건

1. 고객 테이블:
   • 고객 테이블의 휴대폰 번호는 하이픈이 포함된 상태로 저장됩니다.
   • 고객 등록 시 하이픈 제거된 번호와 하이픈 제거된 번호가 역순으로 저장된 컬럼이 존재합니다.
   • 모든 쿼리는 딕셔너리 형태로 저장됩니다.
   • 하이픈 제거된 번호와 역순 번호는 STORED 가상 열로 저장됩니다.
     (STORED, VIRTUAL 가상열 관련 포스트는 나중에 진행할 예정입니다.)

1. 검색 가능 영역:
   • 고객 번호(pk 아님), 부모 이름, 아이 이름, 부모 휴대폰 번호(뒤 8자리, 4자리 검색 가능)를 포함한 여러 세부적인 검색 요구가 있습니다. 이번 글에서는 이 중 일부만 다루겠습니다.

최적화 방법

문제점

스튜디오의 고객 테이블에는 다양한 정보가 포함되어 있다.

모든 컬럼을 검색하면 비용이 많이 발생할 수 있습니다. 따라서 필요한 컬럼만을 담는 임시 테이블을 활용하기로 결정.

 

해결책

1. 임시 테이블 생성:
   • 필요한 컬럼만 담는 임시 테이블을 생성한다. 이 테이블에는 고객 등록일, 고객 번호(pk 아님), 아기 이름, 엄마 이름, 엄마 휴대폰 번호, 아빠 이름, 아빠 휴대폰 번호, 가입 경로, 계약 건수, 촬영 건수 등이 포함된다.
   • 임시 테이블은 속도를 위해 MEMORY 엔진으로 생성한다.

검색 절차:

1. 임시 테이블 생성
   

CREATE TEMPORARY TABLE temp_erp_customer (
    row_num INT UNSIGNED AUTO_INCREMENT,
    customer_id_id INT NOT NULL,
    customer_no VARCHAR(20),
    registdate VARCHAR(20),
    baby_name VARCHAR(50),
    mom_name VARCHAR(50),
    mom_phone VARCHAR(50),
    dad_name VARCHAR(20),
    dad_phone VARCHAR(20),
    customer_visit1 VARCHAR(50),
    customer_visit2 VARCHAR(50),
    customer_visit3 VARCHAR(50),
    baby_duedate VARCHAR(20),
    baby_birth VARCHAR(20),
    TM VARCHAR(255) NULL,
    contract_count VARCHAR(20) NULL,
    photo_count VARCHAR(20) NULL,
    contract_totamount VARCHAR(255) NULL,
    contract_payadvance VARCHAR(255) NULL,
    contract_payinterim VARCHAR(255) NULL,
    contract_balance VARCHAR(255) NULL,
    delivery_fee VARCHAR(255) NULL,
    PRIMARY KEY (row_num),
    UNIQUE KEY (customer_id_id)
) ENGINE=MEMORY;

 

1. 사용자 입력 값 준비:
   • 입력 값을 두 가지 버전(정상 버전과 역순 버전)으로 만듭니다. 두 버전 모두 하이픈을 제거한다.
2. 쿼리 생성:
   • 검색 가능한 필드들에 대해 딕셔너리 형태로 쿼리를 저장합니다.

_dict["WHERE"] += f"""
    AND (
        customer.customer_no = '{_str}'
        OR customer.mom_name LIKE '%{_str}%'
        OR customer.dad_name LIKE '%{_str}%'
        OR customer.baby_name = '%{_str}%'
        OR customer.mom_phone_without_hyphen LIKE '%{_str}'
        OR customer.dad_phone_without_hyphen LIKE '%{_str}'
        OR customer.mom_phone_without_hyphen_reverse LIKE '{_str2}%'
        OR customer.dad_phone_without_hyphen_reverse LIKE '{_str2}%'
    )
"""

 

_dict['LIMIT'] += f"""
    LIMIT {DATA.get('limit_start')}, {DATA.get('limit_end')}
"""

LIMIT를 활용하여 최대한 적은 데이터를 삽입하도록 한다.

 

3.인덱스:

• 엄마와 아빠 이름에는 LIKE를 사용했습니다. 인덱스를 타지 않아도 된다는 판단하에 데이터 정확성을 위해 선택했습니다. 성능에 큰 영향을 미치지 않을 것이라고 판단했습니다.

4.휴대폰 번호 최적화:

• 하이픈이 제거된 컬럼을 조회하여 성능을 최적화했습니다.

5. 검색 결과 임시 테이블에 삽입:

_dict['INSERT'] = """
    INSERT INTO temp_erp_customer (
    customer_id_id, customer_no, registdate, baby_name, mom_name, mom_phone, dad_name, dad_phone, customer_visit1,
    customer_visit2, customer_visit3, baby_duedate, baby_birth
    )
"""

 

6. 결과 조회 및 리턴:

• 임시 테이블에서 데이터를 조회하고 리턴합니다.

SELECT *
FROM temp_erp_customer

 

7. 임시 테이블 정리:

• 세션 종료 시 임시 테이블은 자동으로 사라집니다.
  하지만 오류를 대비하여 검색할 때마다 임시 테이블 데이터를 제거하고,  위의 과정을 재 진행 합니다.

if table_exists("temp_erp_customer"):
    # 테이블이 존재하면 데이터를 삭제합니다.
    execute_query("DELETE FROM temp_erp_customer;", "delete_temp_table")

 

 

결론

이번 최적화 작업을 통해 고객 데이터(150만개 이상) 검색 속도 (0초대를 유지) 를 크게 향상시킬 수 있었다.
임시 테이블을 활용하여 필요한 데이터만 처리함으로써 비용을 절감하고, 사용자 경험을 개선할 수 있엇다.
이 경험을 바탕으로 다양한 최적화 기법을 지속적으로 탐구하고 적용해 나갈 예정이며.

 

후에는  STORED, VIRTUAL (가상열)과 InnoDB, MEMORY 엔진 관련 포스트도 진행할 예정이며.
MYSQL 관련 기능 및 공부하였던 내역을 공유 하고자 한다.

더 깊이 있는 DB 지식을 공유할 수 있도록 하겠습니다.

 

감사합니다.