chaechae.life

SQLALCHEMY MYSQL DB INDEX 설정하기

프로필 사진mingke
SQLAlchemy
sqlalchemy mysql db index

목차

MYSQL DB 인덱스

백엔드 개발자가 되기 위해 하다보면 데이터베이스에 대해서도 공부하게 됩니다. 그중에서도 DB 인덱스와 관련해서는 주니어 이하의 면접에서도 많이 다뤄지는 주제라고 생각됩니다. 개인적으론 최근 치렀던 과제테스트와 면접에서도 다뤄졌던 내용인데요. 오늘은 이와 관련해서 블로그 포스팅을 해보려고합니다.

DB 인덱스는 책의 목차와 유사한 역할을 합니다. 사용자가 필요한 정보를 더 빠르게 찾을 수 있도록 도와주는 역할을 합니다. 데이터 조회 작업의 성능을 최적화하기 위해 필요합니다. 인덱스가 없다면 테이블을 full-scan합니다. 조회하는 테이블에 데이터양이 많아져서 조회성능이 떨어졌을 때 고려해볼만 합니다.

인덱스를 설정하면 인덱스된 컬럼에 있는 데이터에 대한 포인터를 포함하는 별도의 데이터 구조가 생성됩니다. 포인터들은 데이터의 실제 저장위치를 가리키고 있습니다. 사용자가 특정 조건에 대해 조회를 할 때 full-scan 대신 index-scan으로 빠르게 데이터 위치를 찾아 조회할 수 있습니다.

인덱스 유형

단일 컬럼 인덱스, 복합 컬럼 인덱스, 유니크 인덱스에 대해서만 알아보겠습니다.

단일 컬럼 인덱스

한 개의 열에 대해서만 생성된 인덱스입니다. 가장 쉽고 기본적인 형태의 인덱스로, 단일 조건 검색에 유용한게 쓰일 수 있습니다.

CREATE INDEX 인덱스이름 on 테이블이름(컬럼이름);
 
/*예시*/
CREATE INDEX user_index on USER(USERNAME);

복합 컬럼 인덱스

두 개 이상의 컬럼을 포함하는 인덱스로 조회 쿼리에서 여러 컬럼이 동시에 사용될 경우에 유용하게 쓰일 수 있습니다.

CREATE INDEX 인덱스이름 ON 테이블이름(컬럼1, 컬럼2, ...);
 
/*예시*/
CREATE INDEX user_composite_index on USER(USERNAME, AGE);

유니크 인덱스

컬럼의 모든 값이 unique해야 하는 것을 보장해야할 때 사용하면 유용합니다.

CREATE UNIQUE INDEX 인덱스이름 ON 테이블명(컬럼명);
 
/*예시*/
CREATE INDEX user_unique_index on USER(USERNAME);

인덱스 주의사항

  • 인덱스는 데이터 삽입, 수정, 삭제 작업 시에는 부정적 영향을 끼칠 수 있기 때문에 주의해야합니다. 인덱스가 있는 컬럼에 데이터를 추가하거나 변경할 때마다, 인덱스도 함께 업데이트를 진행하여 속도가 느려질 수 있습니다.
  • 인덱스를 많이 사용하면 추가 공간을 사용하는 것이기 때문에 스케일링을 해야하는 문제가 빨리 발생할 수 있습니다.
  • 이런 주의사항들 때문에 데이터베이스에 인덱스가 많이 걸려있으면 관리상 복잡도가 올라 갈 수 있습니다. 이 인덱스가 꼭 필요한지에 대한 고민이 선행되어야 합니다.

SQLALCHEMY INDEX

최근에 ORM중엔 SQLALCHEMY를 가장 많이 사용하고 있기 때문에 SQLALCHEMY에서 사용하는 방법을 알아보겠습니다.

  • 단일 컬럼 인덱스
from sqlalchemy import Integer, String, DateTime, ForeignKey, UniqueConstraint
from sqlalchemy.schema import Index
from sqlalchemy.orm import mapped_column
 
class Book(Base):
    __tablename__ = 'books'
 
    id = mapped_column(Integer, primary_key=True)
    title = mapped_column(String(255), index=True)  # 단일 컬럼 인덱스
    author = mapped_column(String(255))
  • 복합 컬럼 인덱스
    • __table_args__ 에 Index를 넣어줍니다.
class Post(Base):
    __tablename__ = 'posts'
    __table_args__ = (Index('ix_user_id_created_at', 'user_id', 'created_at'),) # 복합 컬럼 인덱스
 
    id = mapped_column(Integer, primary_key=True)
    user_id = mapped_column(Integer, ForeignKey('users.id'))
    created_at = mapped_column(DateTime)
    content = mapped_column(String(255))
  • 유니크 인덱스
    • 단일 유니크 인덱스는 컬럼에, 복합은 UniqueConstraint에 넣어줍니다.
class User(Base):
    __tablename__ = 'users'
 
    id = mapped_column(Integer, primary_key=True)
    email = mapped_column(String, unique=True)  # 이메일 필드에 유니크 인덱스 설정
 
 
class Product(Base):
    __tablename__ = 'products'
    # 'name'과 'category'의 조합의 유니크
    __table_args__ = (
        UniqueConstraint('name', 'category', name='uix_name_category'),
    )
 
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String)
    category = mapped_column(String)

마무리

사실 작은 기업에서만 근무를 해봤고 인덱스를 설정할 만큼 많은 데이터를 다뤄보지는 못했지만, 면접을 보러갈 때마다 질문 받는 것 같아 간단하게 정리해봤습니다. 생각해보니 완전 쌩신입으로 면접보러 갔을 때도 받았던 질문이네요. 신입분들 인덱스에 대해서 잘 숙지하고 가시기 바랍니다. 실무에서도 인덱스 관련된 작업을 해볼 수 있었으면 좋겠습니다. 실제 DB로 마이그레이션을 위해 alembic을 권장합니다.