sql

SQL 관련 메모

Record vs Tuple vs Row

어디서 부르느냐에 따라서 차이가 있다.

File System	Database Modeling	RDBMS
File	Entity	Table
Record	Tuple	Row
Key	Identifier	Primary Key
Field	Attribute	Column

Where vs Having

Having 은 Group By 뒤에 쓰인다. Group By 로 묶인 값들을 대상으로 Having 에 조건을 부여하는 것이다.

Index

Index 를 사용하는 이유

• 조건을 만족하는 tuple 을 빠르게 조회🔥하기 위해
  • UPDATE, INSERT, DELETE 속도는 저하될 수 있다. 😱
  • Table 의 Index 정보를 갱신하는 추가 비용 발생
  • 효율적인 Index 설계가 중요

이미 존재하는 테이블에 인덱스 적용

CREATE INDEX $인덱스_이름 ON $테이블_이름 ($컬럼_이름);

-- 다중 컬럼 인덱스
CREATE UNIQUE INDEX $인덱스_이름
ON $테이블_이름 ($컬럼_1, $컬럼_2);

테이블 생성 시 인덱스 적용

Primary Key 에는 Index 가 자동으로 생성된다.

CREATE TABLE 테이블_이름 (
  id bigint NOT NULL PRIMARY KEY('id') auto_increment,
  name varchar(255) NOT NULL,
  -- ...
  INDEX $인덱스_이름(생략가능) ($컬럼_이름),
  -- ...
  UNIQUE INDEX $인덱스_이름(생략가능) ($컬럼_1, $컬럼_2)
  -- ...
)

Index 확인

SHOW INDEX FROM $테이블_이름;

다중 컬럼 Index 가 필요한 경우

간단한 예시를 살펴보면,

사원(table = employee) 이 10만명인 어떤 회사에서 나이 28, 여자인 직원을 검색한다고 가정하자.

SELECT * FROM employee WHERE empl_age=28 AND empl_gender=female

우선 나이가 28인 직원을 검색하고, 그리고 해당 결과 전체를 스캔하며 여자에 해당하는 직원을 찾는다. 풀 스캔을 하며 찾기 때문에 튜플의 개수가 많아지면 속도가 느려진다.

이럴 때,

CREATE INDEX $인덱스_이름 ON employee (empl_age, empl_gender);

위와 같이 다중 컬럼 인덱스를 사용하는 경우, 나이 + 성별 조합으로 검색할 때 굉장히 빠르게 결과값을 찾는다.

• 단, 다중 컬럼 인덱스는 단일 컬럼 인덱스보다 더 비효율적인 INDEX/UPDATE/DELETE 를 수행한다.
• 충분한 테스트가 필요하다.

explain 명령어

한 컬럼에 여러 인덱스가 걸려있는 경우, 쿼리 맨 앞에 EXPLAIN 키워드를 붙이면 어떤 key 를 사용했는지 알려준다.

Index 부여 시 고려 사항

• 무조건 많이 설정하지 않기 ⛔
• 조회 시 자주 사용되는 컬럼, JOIN 이 빈번하게 일어나는 컬럼
• 가급적 변하지 않는 고유한 값 위주
• 단일 인덱스 여러 개 보다는 다중 커럼 인덱스를 고려
• UPDATE 가 자주 일어나지 않는 컬럼
  • table 에 write 할 때, index 가 바뀌며 오버헤드 발생 ⚠️
• 인덱스 부여 시, 가장 효율적인 자료형은 int

Covering Index

SELECT 문에 포함된 컬럼이 이미 Index 에 존재하는 경우, covering index 라고 한다.

• 의도적으로 covering index 를 사용해서 조회 성능을 끌어올릴 수 있다.

Full Scan 이 더 좋은 경우

• 튜플이 100~1000개 정도를 넘지 않음
• 조회하려는 데이터가 테이블의 다수를 차지하는 경우

출처

• https://spiderwebcoding.tistory.com/6