탈개를 꿈꿉시다

MySQL: MySQL에서는 LIKE 검색을 위해 SQL 쿼리에서 다음과 같이 사용합니다.이 쿼리는 "your_column"이라는 컬럼에서 "keyword"를 포함하는 모든 레코드를 선택합니다.

<select id="selectByKeyword" parameterType="String" resultType="YourResultType">

SELECT * FROM your_table WHERE your_column LIKE CONCAT('%', #{keyword}, '%')

</select>

Oracle: Oracle에서는 LIKE 검색을 위해 다음과 같이 사용합니다.이 쿼리는 "your_column"이라는 컬럼에서 "keyword"를 포함하는 모든 레코드를 선택합니다. Oracle에서는 문자열 연결 연산자로 "||"를 사용합니다.

<select id="selectByKeyword" parameterType="String" resultType="YourResultType">

SELECT * FROM your_table WHERE your_column LIKE '%' || #{keyword} || '%'

</select>

MSSQL: MSSQL에서는 다음과 같이 LIKE 검색을 수행할 수 있습니다.이 쿼리는 "your_column"이라는 컬럼에서 "keyword"를 포함하는 모든 레코드를 선택합니다. MSSQL에서는 문자열 연결을 위해 "+"를 사용합니다.

<select id="selectByKeyword" parameterType="String" resultType="YourResultType">

SELECT * FROM your_table WHERE your_column LIKE '%' + #{keyword} + '%'

</select>

요즘 부하가 걸릴정도의 트레픽을 다루는 서비스를 유지보수 하는일을 하고있다.

동접자가 6000까지 올라갈 정도로 인기? 가많은 서비스 인데 업데이트 실수가 있었다.

 범인은 바로 index .... 

쿼리 튜닝에 있어 index 는 기본중에 기본인데 오늘 새로 알았던 사실을 적어본다.

left join on 사용자정보.userId= 주문정보.userId

이런식의 조인 결과가 있었는데 

테이블 구조는 이렇다.

대충 이러한 구조인데 ... 이상한점을 금방 눈치차린 사람도 있겠지만 ... 초반에는 아무런 생각조차 못하고 있었다

물론 사용자테이블의 userId 는 기본키라 인덱스가 걸려있었고

주문테이블의 userId 는 인덱스 생성을 한 상태

당연히 인덱스끼리의 조인이라 별 문제 없을줄 알았으나 이는 slowquery 를 유발 했다

explain 으로도 엄청난 자원을 소모 하고 있었고 그이유는 타입 int 형과 타입 char 의 서로 상이한 타입과의 조인 이었다.

사용자테이블의 userId 는 int, 주문테이블의 사용자 아이디는 varchar 여서 서로 문제가 되었다.

이는 CAST 함수를 사용해서 해결 했다.

left join on CAST(사용자정보.userId AS CHAR)= 주문정보.userId

조인하는 한쪽을 형변환 하여 조인 하니 index 가 잘 걸리는 것을 확인 하였다.

cast 함수를 찾아보니

CAST(value AS datatype)

인자값은 다양하게 지원 하고 있는것을 확인 했다.

다음에 요기나게 사용 해볼수 있을 듯

사내 프로젝트 고도화 중 우편번호 검색을 맡게 되었다.

보통의 경우 주소 API를 쓰겠지만

사내 DB에 매달 업데이트 되는 전국주소테이블을 써야했기에 쿼리를 만들어 테스트해봤다.

헌데..

필요한 칼럼들 like or like or ... 하니 3.9초나 걸리네??

이건 도저히 못 써먹는 수준 아닌가..

심지어 인덱싱 걸려있는 칼럼들도 있는데 ㅠㅠㅠ

도저히 안 되겠다 싶어서 우리의 친구 구글에 이것저것 검색해보니

FULLTEXT가 나와서 적용해보기로 했다!

1. FULLTEXT 만드는 법!

ALTER TABLE 테이블명 ADD FULLTEXT INDEX 인덱스명 (컬럼명) WITH PARSER ngram;

사용할 테이블에 위의 방식대로 생성하게 되면 FULLTEXT 인덱스가 만들어진다!

만약 나처럼 여러개의 칼럼을 같이 검색하고 싶다면

ALTER TABLE 테이블명 ADD FULLTEXT INDEX 인덱스명 (컬럼명, 컬럼명, 컬럼명 .....) WITH PARSER ngram;

() 안에 넣고 싶은 칼럼들을 넣으면 생성이 완료된다!

ex)

-aaa테이블에 aos라는 칼럼을 bbb라는 인덱스이름으로 만들고 싶을 때

ALTER TABLE aaa ADD FULLTEXT INDEX bbb (aos) WITH PARSER ngram;

-aaa테이블에 aos, bos, cos라는 칼럼을 ccc라는 인덱스이름으로 만들고 싶을 때

ALTER TABLE aaa ADD FULLTEXT INDEX ccc (aos, bos, cos) WITH PARSER ngram;

1-1) WITH PARSER ngram > ??

ngram은 한글이 지원하며 2글자 단위로 토큰화 해준다!

     ex) 탈개를 꿈꾸다 > ["탈개", "개를", "꿈꾸", "꾸다"]

한글 지원해주니 꼭 이거 써주면 된다 ㅎㅎ

기본값이 2여서 2글자 단위로 토큰화 해주는데

만약 3글자, 4글자부터 시작하는 검색을 구현하고 싶으면 따로 설정 해주면 된다!

- 단 이 경우 3글자 미만인 글자들은 파싱 안해주니 주의!

ex) "함께 가즈아"를 3글자 단위로 토큰화하면 '함께'는 파싱 안해줌

2. FULLTEXT 쓰는 법!

바쁜 현대사회인을 위해 다 생략하고 바로 적용하기 좋은 걸 알려주자면

select * from 테이블명 where match(풀텍스트인덱스를 한 칼럼명) against ('검색어' in boolean mode);

이 방식대로 쓰면 된다!

ex)

- 예시로 들었던 bbb인덱스 사용법

select * from aaa where match(aos) against ('검색어' in boolean mode);

- 예시로 들었던 ccc인덱스 사용법

select * from aaa where match(aos, bos, cos) against ('검색어' in boolean mode);

ngram이 2글자 단위로 토큰화 되니

먼저 2글자로 검색해보면!

똑같은 두글자로 검색했는데

약 3.9s에서 71ms로 단축됐으니.. 이 얼마나 엄청난 성능차이인지 ㅠㅠㅠ

헌데 여기서 끝나면 안된다!!

2-1) 검색어가 3글자 이상일 때 처리하는 방법!

특정 검색어가 3글자 이상일 경우 느려지는 현상이 있을 것이다.

이건 ngram이 2글자 단위로 파싱해줘서 생긴 문제다.

이것에 대한 해결법은

- 검색어가 "풀텍스트 검색"일 경우

select * from aaa where match(aos) against ('+풀텍 +텍스+ +스트 +검색' in boolean mode);

이렇게 하면 다시 빠릿하게 검색되는 게 느껴질 것이다!

검색어를 저렇게 넘기는 건 알고리즘을 짜주면 된다

필자의 경우 for문과 substring을 활용해서 넘겨주었다 ㅎㅎ

3. FULLTEXT 주의사항!

위에 예시로 들었던

ALTER TABLE aaa ADD FULLTEXT INDEX ccc (aos, bos, cos) WITH PARSER ngram;

ccc 인덱스를 아래와 같이

select * from aaa where match(aos, bos) against ('검색어' in boolean mode);

써도 될까?!

정답은 X다!

ccc 인덱스에 aos,bos,cos 칼럼을 걸어놨으니 무조건

(aos,bos,cos) 3개를 다 써야한다!

만약 aos, bos만 쓰고 싶다면 aos,bos를 쓰는 FULLTEXT Index를 새로 만들어주면 된다 ㅎㅎㅎ

출처 및 글 인용 --

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%92%80%ED%85%8D%EC%8A%A4%ED%8A%B8-%EC%9D%B8%EB%8D%B1%EC%8A%A4Full-Text-Index-%EC%82%AC%EC%9A%A9%EB%B2%95
https://kabkee.github.io/mysql/mysql-full-text-search/
https://m.blog.naver.com/jjdo1994/222348191751
https://gongzza.github.io/database/mysql-fulltext-search/

SELETE

1. PARSE

동일한 문장을 라이브러리 캐쉬에서 검색

동일한 문장이 없을경우 새로운 공간 생성 ( 있으면 기존 공간을 사용)

문법 검사와 스키마, 권한을 검사

실행 계획을 생성 ( 옵티마이저)

2. BIND ( bind variable를 사용하는 경우에 해당)

3. excute

찾는 block이 버퍼 캐쉬에 존재하는지 확인하고 있으면 사용, 없으면 데이터 파일로 메모리 로드 후 서버 프로세스에 의한 데이터 처리

4. FETCH

UPDATE

1. PARSE

동일한 문장이 LIBRARY CACHE에 존재하는지 확인

동일한 문장이 존재하면 기존 공간을 사용, 존재하지 않으면 새롭게 공간을 확보

문법 검사, schema 검사, 권한 검사

실행 계획 생성(OPTIMIZER)

2. BIND : bind variable을 사용하는 경우

3. EXECUTE

찾고자 하는 block이 데이터베이스버퍼캐쉬에 존재하는지 확인

존재하면 그냥 사용, 존재하지 않으면 데이터파일로부터 메모리로 load

데이터베이스 버퍼 캐시 내에서 UNDO SEGMENT BLOCK을 점유

해당 행에 락을 걸면서 UNDO SEGMENT BLOCK에 BEFORE 이미지 기록

해당 BLOCK의 실제 데이터를 변경(AFTER 이미지)

리두 로그 버퍼에 REDO ENTRI를 기록

ROLLBACK && COMMIT

ROLLBACK

1. UNDO SEGMENT BLOCK에 있는 이후 이미지가 원 데이터 블럭에 덮어 쓰여 짐

2. LOCK 해제

COMMIT

1. REDO LOG BUFFER에 있는 REDO ENTRY들이 LGWR에 의해서 REDO LOG FILE에 기록

2. LOCK 해제

좋은 기회가 생겨 한국 데이터베이스 진흥원에서 교육을 받던차 DML 문 정도의 프로세스는 기억해 두는게 좋겠다 생각되어 정리 해 놓습니다.