4. 12_확장 검색, 13_파티션(~13.2)

[DaehunGwak]

진도

12. 확장 검색
13. 파티션 13.1 개요 13.2 주의사항

일시

2022.08.07.(일) 21:00

[DaehunGwak]

12.1 전문 검색 (full-text search)

• note: https://first-diadem-378.notion.site/12-1-full-text-search-2703ab27e6694fbbaa799cd853726b41
• 전문 검색을 위한 다른 db가 좋아보이긴 함
  • elasticsearch

12.2. 공간 검색 (spatial search)

보다보니 아직 미지원되는 것이 많아 다른 dbms 를 쓰는 것이 더 좋겠다 생각됨

• 용어들을 알 수 있어서 좋긴했음 (GCS, PCS, WKT, WKB, MBR 등)
  • 구글링 실력 상승!
• 아직 인덱스 사용하지 못하거나 최적화되지 않은 함수들이 많음
  • 다른 RDBMS 를 사용하자
    • dbms: PostgresSQL(extension: PostGIS), MongoDB (GeoJson)
    • cloud: snowflake, ms sql server, aws aurora
      • https://www.safe.com/blog/2021/11/7-spatial-databases-enterprise/
      • https://www.mongodb.com/docs/manual/core/2dsphere/
      • https://docs.microsoft.com/ko-kr/sql/relational-databases/spatial/spatial-indexes-overview?view=sql-server-ver16
      • https://aws.amazon.com/ko/blogs/database/amazon-aurora-under-the-hood-indexing-geospatial-data-using-z-order-curves/

[minkukjo]

확장 검색

• MySQL에서도 전문 검색이 가능하구나~ 정도로 느끼고 심도있게 살펴보진 않았다. (필요하다면 루씬을 쓰는게 더 낫지않나?)
• 전문 검색 쿼리모드를 지원하는데 크게 자연어 검색과 불리언 검색이 있다.
• 검색어 확장 기능은 자연어 검색 기능에 사용할 수 있는데, 검색어로 검색된 결과에서 공통으로 발견된 단어를 모아서 한 번 더 검색을 할 수 있다고 한다. ( 연관검색어 같은 기능인 듯? )

공간 검색

• MySQL의 공간 데이터 관리 기능은 다른 RDBMS보다 느린 편이지만 8버전에서 많은 기능이 보완되었다고 한다.
• 하필 또 공간 관련 도메인이라 좀 자세히 봐두려고 한다.
• 공간 검색에서 공간은 크게 SRS( 좌표계 ), GCS( 지리 좌표계 ), PCS( 투영 좌포계 )로 구분된다.
• 공간이라 하면 단순히 위/경도만 생각하면 되는 줄 알았는데, 각 나라별로 좌표계가 달라서 단순히 위/경도 만으로는 정확한 위치를 파악할 수 없다고 한다.

SRS

• SRS 좌표계는 MySQL에서 지원하는 것만 500여개가 넘는다.
• 일반적인 GPS는 좌표계의 이름을 갖고, 좌표계 고유 번호와 지리 좌표계와 투영 좌표계를 갖는다.
• WGS84는 웹 기반의 지도들 (구글맵)에서 사용되는 좌표계로 MySQL의 일부 공간 함수들만이 이 좌표계에 대한 데이터를 처리할 수 있다고 한다. (추후 더 생길 수 있다고 함)
• 8버전에서는 SRID가 지정되어 있어야 MySQL의 공간 함수들을 사용할 수 있다고 한다.

투영 좌표계와 평면 좌표계

• 지리 좌표계에 속하지 않는 평면 좌표계는 투영 좌표계와 비슷한 특징을 가지므로 같이 알아보자.
• 지구 구체 전체 또는 부분을 평면으로 투영한 좌표계를 투영 좌표계라 부른다.
• SRID=0은 평면 좌표계라고 한다. SRID=0은 단위를 가지지 않으며 X축과 Y축의 값이 제한이 없으므로 무한 평면 좌표계라 불리기도 한다.

지리 좌표계

• 지리 좌표계나 투영 좌표계를 사용하는공간 데이터는 ST_Distance_Sphere() 함수를 사용하여 좌표계 두 점의 거리를 계산할 수 있다.
• 안타깝게도 아직 MySQL에선 인덱스를 사용할 수 있는 반경 검색 기능(함수)은 없다고 한다. (풀스캔임;;)
• 그래서 MBR(Minimum Bounding Rectangle)을 사용한 ST_Within() 함수를 사용할 수 있는데, 주어진 위치를 기준으로 원을 감싸는 사각형(MBR)을 만들고, 그 사각형 포함되는 데이터들을 읽어올 수 있다.
• ST_Within 함수는 ST_Distance_Sphere 함수와 다르게 공간 인덱스를 사용하고 range 접근법으로 데이터를 읽어온다.
• 그림으로 표현하면 위와 같은 형태이다.
• MBR 사각형 내의 점들을 검색하므로 사각형 모서리 부분은 원의 반경을 범위를 벗어나도 결과에 포함된다고 한다.
• 원하는 것은 반경 N km 이내의 원형안에서 검색되는 결과를 원하기 때문에, 사각형과 원형의 교집합을 찾기 위해 ST_Distance_Sphere를 검색된 결과에 적용하여 해당 반경에 결과가 포함되는지를 한 번 더 판단해줄 수 있다.
• 이미 걸러진 결과물에 ST_Distance_Sphere를 사용하기 때문에 인덱스를 사용하지 않더라도 성능이 괜찮다.

성능 주의사항

• 지리 좌표계인 SRID가 4326인 좌표계는 ST_Contains 함수등으로 포함 관계를 비교한느 경우 투영 좌표계보다 느리다고 한다.
• 일반적인 휴대폰과 같은 모바일 장치로 수신 받는 GPS는 WGS 84이고, SRID 4326인 지리 좌표계로 표현된다고 한다.
• SRID 3857은 WGS 84 좌표를 평면으로 투영한 투영 좌표계여서 SRID 4326을 3857 또는 그 반대로 상호 변환이 가능하다고 한다.
• 그러나 MySQL 8.025버전은 아직 3857에 대한 변환은 지원하지 않는다고 한다.

파티션

• 파티션은 하나의 테이블을 논리적으로는 하나이지만 물리적으로는 여러 개의 테이블로 쪼개서 관리할 수 있게 해주는 기능이다.
• 주로 대용량 테이블을 물리적으로 여러 개의 소규모 테이블로 분산하는데 그 목적이 있다.
• 그러나 무조건 대용량 테이블을 쪼갠다고 성능이 빨라지는 건 아니다.
• 어떤 쿼리를 사용하느냐에 따라 오히려 성능이 더 나빠지는 경우도 있다고 한다.

파티션을 쓰는 이유

• 테이블이 커지면 무조건 파티션을 한다고 빨라지는건 아니다.
• 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나 데이터 특성상 주기적인 삭제 작업이 필요한 경우라면 파티션을 고려해볼 법 하다.

단일 INSER와 범위 SELECT의 빠른 처리

• 인덱스를 단순히 검색용으로 생각하지만, UPDATE나 DELETE처럼 대상 레코드를 찾기 위해서도 인덱스는 사용된다.
• 그러나 인덱스 크기가 커지면 SELECT는 물론이요 INSERT, UPDATE, DELETE도 함께 느려진다.
• 활발히 사용되는 데이터를 Working Set이라 부르는데, 파티션을 하면 이 Working Set을 메모리에 올려두고 사용할 수 있기 떄문에 효율적이다.
• 파티셔닝을 통해 큰 테이블과 인덱스를 잘개 쪼개서 물리적으로 메모리를 효율적으로 사용할 수 있게 된다.

데이터의 물리적 저장소를 분리

• 데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크면, 테이블의 데이터나 인덱스를 파일 단위로 관리하는 MySQL 특성 상 백업이나 관리 작업이 어려워진다.
• 파티션을 통해 파일의 크기를 조절하거나 파티션별 파일들이 저장될 위치나 디스크를 구분해 해결해볼 수 있다.

파티션된 테이블의 레코드 INSERT

• 파티션 키를 찾고, 그 레코드가 저장될 적절한 파티션을 결정한다.

파티션된 테이블의 레코드 UPDATE

• 파티션 키 외의 컬럼이 변경되면 파티션이 적용되지 않은 테이블과 동일하게 값만 변경한다.
• 그러나 파티션 키를 바꾸면 변경 대상 레코드를 지우고, 바뀐 파티션에 레코드를 추가해준다.

파티션 테이블의 검색

• WHERE 조건으로 검색할 파티션을 선택할 수 있는가?
• WHER절의 조건이 효율적으로 인덱스를 사용할 수 있는가?
• 위 두개가 파티션 테이블 검색에 크게 영향을 미친다.
• 물론 두 번째의 경우는 일반 테이블에서도 같은 영향을 갖는다.

파티션 테이블의 인덱스 스캔과 정렬

• 인덱스는 파티션별로 생성되며 파티션과 관계 없이 글로벌하게 통합된 인덱스는 지원하지 않는다.
• 각 파티션에서는 인덱스를 기준으로 정렬되지 않는데, 막상 인덱스 레인지 스캔을 해보면 실행 계획에 file sort와 같이 별도로 정렬을 해주는 메시지가 없다는 것을 알 수 있다.
• 실제로 MySQL에서는 여러 파티션에 대해 인덱스 스캔을 수행할 때 각 파티션으로 부터 조건에 일치하는 레코드를 우선순위 큐에 임시로 저장해둔다고 한다.
• 그리고 우선순위 큐에서 다시 필요한 순서로 데이터를 가져가는 것이다.
• 이는 각 파티션에서 읽은 데이터들이 이미 정렬되어있기 때문에 가능한 방법이다.

파티션 프루닝

• 옵티마이저에서 파티션 중 읽을 필요 없는 파티션은 접근하지 않는다. 이를 파티션 프루닝이라 부른다.

주의 사항

• 파티션 키로 사용되는 컬럼은 유니크 인덱스로 반드시 PK에 포함되어야한다.
• 파티션은 최대 8192개
• 파티션 테이블은 외래키를 사용할 수 없다.
• 공간 데이터를 저장하는 컬럼 타입은 파티션 테이블에서 쓸 수 없다. (엇..!?)
• 임시 테이블은 파티션 기능 못씀
• 파티션 테이블은 모두 로컬 인덱스이며 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다.