03.21 학습! Index! 소규모 DB 프로젝트! 🟥🟧🟨🟩🟦🟪🟫⬜⬛🫢🔔😎😊🤔😭⭐🫢
DB 🟥
인덱스 🟧
데이터의 검색 효율성을 높이기 위해 데이터 베이스 객체를 생성!
하나 이상의 컬럼을 기준으로 정렬하여 데이터의 빠른 검색을 지원
Primary Key, Unique, Foreign Key는 인덱스 자동 생성됨!
장점
조회 속도 향상
단점
잦은 DML은 인덱스 엔트리 변경을 동반하여 성능 저하 발생
추가적인 저장 공간 필요
Index 논리 유형
Single Index VS Concatenated Index
Unique Index VS Non_unique Index
Covering Index VS Functional Based Index
Index 활용 시 주의 사항
WHERE, JOIN, ORDER BY, GROUP BY에 자주 사용되는 컬럼 고려
컬럼의 분포도가 10~15퍼 이내인 경우 적용하면 좋음
Cardinality가 높은 컬럼 우선 고려
복합 컬럼 인덱스 고려
커버링 인덱스 고려
Concatenated Index
여러 컬럼의 조합으로 하나의 인덱스 생성
점 조건 vs 선분 조건
index 실습
select * from attractions limit 10;
show index from attractions;
Primary Key, Unique, Foreign Key는 인덱스 자동 생성된 것을 확인!
explain
select * from attractions limit 1000;
explain 키워드로 SIMPLE 확인!
이 버튼으로 확인한다면?
Query cost 가 5340.95 인 것을 확인!
explain analyze
select * from attractions limit 1000;
EXPLAIN ANALYZE 키워드로 상세 내용 글로 확인 가능!
인덱스를 이용한 탐색! pk인 no 를 이용한 탐색
SELECT * FROM attractions WHERE no = 56644;
훨씬 빠름!
인덱스를 이용한 탐색2 pk인 no를 이용하여 범위 탐색!
SELECT * FROM attractions WHERE no between 56640 and 56650;
index Range Scan 을 통해서 빠른 탐색!
인덱스 생성 확인 삭제
create index idx_content_id on attractions(content_id);
show index from attractions;
SELECT * FROM attractions WHERE content_id between 2733960 and 2733970;
복합 인덱스 생성!
create index idx_content_id_no on attractions(content_id,no);
show index from attractions;
DB 프로젝트 (여행 웹사이트)
대표 sql
- 지역별 관광지 정보 조회
예시로 “서울” 지역의 관광지 정보를 조회합니다.
SELECT a.no, a.title, s.sido_name, g.gugun_name, a.addr1, a.first_image1
FROM ssafytrip.attractions a
JOIN ssafytrip.sidos s ON a.area_code = s.sido_code
JOIN ssafytrip.guguns g ON a.si_gun_gu_code = g.gugun_code
WHERE s.sido_name = '서울';
2.지역별 + 컨텐츠별 정보 조회
예시로 “서울” 지역이며, 콘텐츠가 “관광지”인 경우를 조회합니다
SELECT a.no, a.title, s.sido_name, g.gugun_name, ct.content_type_name, a.addr1, a.first_image1
FROM ssafytrip.attractions a
JOIN ssafytrip.sidos s ON a.area_code = s.sido_code
JOIN ssafytrip.guguns g ON a.si_gun_gu_code = g.gugun_code
JOIN ssafytrip.contenttypes ct ON a.content_type_id = ct.content_type_id
WHERE s.sido_name = '서울' AND ct.content_type_name = '관광지';
- 게시판 확인용 조회
게시판(boards) 테이블과 작성자 정보(회원 테이블)를 함께 조회하여, 게시글 목록을 확인하는 예시입니다.
SELECT b.board_id, m.username, b.title, b.content, b.created_at
FROM ssafytrip.boards b
JOIN ssafytrip.members m ON b.member_id = m.member_id;
- 사용자가 좋아요 누른 여행지를 저장
예시로 일반 사용자(sampleUser)가 attraction_no가 56644인 여행지를 좋아요한 경우입니다.
INSERT INTO ssafytrip.likes (member_id, attraction_no)
VALUES (
(SELECT member_id FROM ssafytrip.members WHERE username = 'sampleUser'),
56644
);
- 사용자가 좋아요 누른 여행지를 조회하는 쿼리
회원이 좋아요한 관광지 정보를 조회합니다. 예시로 sampleUser가 좋아요한 여행지를 확인하는 쿼리입니다.
SELECT l.like_id, m.username, a.no AS attraction_no, a.title, a.addr1, a.first_image1, l.liked_at
FROM ssafytrip.likes l
JOIN ssafytrip.members m ON l.member_id = m.member_id
JOIN ssafytrip.attractions a ON l.attraction_no = a.no
WHERE m.username = 'sampleUser';
