[049] MySQL: 인덱스 (+ScoularTodo 제작)

 

2024.07.17(수)
 
MySQL 6일차
 
인덱스의 대한 전체적인 내용 정리하고,

ScoulaTodo 제작에 필요한 몇 코드를 적어놨다.

---

 
이것이 MySQL이다
 

PART01 MySQL 설치 및 DB 구축과정 미리 실습하기

• CH01 DBMS 개요와 MySQL 소개
• CH02 MySQL 설치
• CH03 MySQL 전체 운영 실습

PART02 MySQL 기본

• CH04 데이터베이스 모델링
• CH05 MySQL 유틸리티 사용법
• CH06 SQL 기본
• CH07 SQL 고급

PART03 MySQL 고급

• CH08 테이블과 뷰
• CH09 인덱스
• CH10 스토어드 프로그램
• CH11 전체 텍스트 검색과 파티션

---

 

CH09 인덱스

 

인덱스의 개념

 

인덱스의 장점과 단점

• 장점: 검색 속도 향상.
• 단점: 추가적인 공간 필요, 인덱스 생성 시 많은 시간 소요 (정렬 시간).
• Tradeoff 관계: 속도와 메모리 양은 서로 반비례 관계 (둘 다 좋을 수 없음).

인덱스의 종류와 자동 생성

1. 클러스터형 인덱스 (Clustered Index)
   • 특징: 행 데이터를 자신의 열(PK)을 기준으로 정렬.
   • 제한: 테이블당 하나만 생성 가능 (테이블 당 행의 집합은 하나이기 때문).
2. 보조 인덱스 (Secondary Index)
   • 특징: 별도의 공간을 추가하여 지정한 컬럼만 정렬.
   • 제한: 컬럼 단위로 여러 개의 인덱스 생성 가능 (추가 메모리 사용).
   • 주의: LIKE 'c%'와 같은 패턴 매칭에는 인덱스 사용 불가.

자동 생성 인덱스

• Primary Key 제약 조건: 클러스터형 인덱스 자동 생성.
• Unique 제약 조건: 보조 인덱스 자동 생성.
• Primary Key 없고, Unique 및 Not Null 제약 조건: 클러스터형 인덱스 자동 생성.

인덱스의 내부 작동

B-Tree (Balanced Tree, 균형 트리)

• 페이지 (Page): B-Tree의 노드. 16Kbyte의 고정 크기로 데이터를 저장.
• 페이지 분할: 페이지에 공간이 없을 경우 새로운 페이지를 생성.

인덱스의 생성, 변경, 삭제

• 인덱스 생성 명령어

 

 

• 인덱스 삭제 명령어

DROP INDEX idx_usertbl_addr ON usertbl;

 

테이블 분석 및 처리

• 테이블 분석

ANALYZE TABLE usertbl;

• 테이블 상태 확인

SHOW TABLE STATUS LIKE 'usertbl';

 

---

예제와 실습

클러스터형 인덱스 생성 예제

CREATE TABLE usertbl ( userID INT PRIMARY KEY, name VARCHAR(100), birthYear INT );

 

보조 인덱스 생성 예제

CREATE INDEX idx_usertbl_birthYear ON usertbl(birthYear);

 

보조 인덱스 삭제 예제

DROP INDEX idx_usertbl_birthYear ON usertbl;

정렬 작업

정렬이 발생하는 경우

• I/U/D 작업: Insert, Update, Delete 작업 시 정렬 필요.
• 정렬 알고리즘: 배열에서 정렬은 'Quick 정렬', 검색은 'Binary 검색' 사용.

 

ScoulaTodo

 

...

 

TodoService 작성

• 각각의 기능을 구현 후 메뉴에 추가
• 로그인 사용자 정보는 컨텍스트를 통해 획득 (Contextget)
  표현이 너무 많다면? 람다식 사용
  Supplier 인터페이스 활용

---

 

이전 내용 복습

1. Runnable 인터페이스:

( ) -> void / 매개변수가 없고, 반환 타입이 void인 run 메서드 정의

 

2. Predicate 인터페이스:

(T t) -> boolean / 주어진 인자를 테스트하여 boolean 값으로 반환하는 test 메서드 정의

• 스트림의 filter 메서드는 Predicate를 인수로 받아서, 스트림의 각 요소를 검사
•  true를 반환하는 요소만으로 구성된 새 스트림 반환
• 대표적으로 사용되는 함수형 인터페이스 (★)
  하나의 추상 메서드는 가지는 인터페이스

그외 Runable, Predicate, Function, Consumer, Supplier

 

• 예외는 언제 발생?
  길이 제한을 넘어설 때

 

실습: 인덱스

 

• 스스로 작성하지 못한 코드

-- ALTER를 사용하여 usertbl에서 PRIMARY KEY 제약조건을 제거하고, name 컬럼에 pk_name이라는 제약조건명으로 기본키를 설정하세요.
ALTER TABLE usertbl DROP primary key;
ALTER TABLE usertbl
ADD constraint pk_name primary key(name);

SELECT * FROM usertbl;

 

• 데이터, 인덱스 크기 확인

-- usertbl의 데이터 크기와 인덱스의 크기 확인
SHOW TABLE STATUS LIKE 'usertbl';

 

• 컬럼 조합으로 인덱스 생성

-- name, birthYear 조합으로 인덱스 생성
-- 컬럼 조합시 ','로 이어서 넣어준다
-- 여러 개의 컬럼으로 인덱스를 만들 경우 각 컬럼당 한 행이 추가된다.
CREATE UNIQUE INDEX idx_usertbl_name_birthYear
ON usertbl(name, birthYear);

 

하나의 곳을 참조

 

Dao 코드 학습 기록

 

모듈 시험 대비 코드 주석 추가

 

UserDao 인터페이스 구축

public interface UserDao {
    // 회원 등록
    // 매개변수: UserVO 객체 (사용자의 정보가 담긴 객체)
    // 반환값: 회원 등록의 성공 여부를 나타내는 int 값 (일반적으로 1은 성공, 0은 실패를 나타냄)
    // 예외: SQL 처리 중 발생할 수 있는 SQLException을 던짐
    int create(UserVO user) throws SQLException;

    // 회원 목록 조회
    // 매개변수: 없음
    // 반환값: UserVO 객체들의 리스트 (모든 회원의 정보를 담고 있음)
    // 예외: SQL 처리 중 발생할 수 있는 SQLException을 던짐
    List<UserVO> getList() throws SQLException;

    // 회원 정보 조회
    // 매개변수: String id (조회하려는 회원의 ID)
    // 반환값: Optional<UserVO> 객체 (ID에 해당하는 회원의 정보가 담긴 객체, 회원이 없을 경우 비어 있을 수 있음)
    // 예외: SQL 처리 중 발생할 수 있는 SQLException을 던짐
    Optional<UserVO> get(String id) throws SQLException;

    // 회원 수정
    // 매개변수: UserVO 객체 (수정할 회원의 정보가 담긴 객체)
    // 반환값: 회원 수정의 성공 여부를 나타내는 int 값 (일반적으로 1은 성공, 0은 실패를 나타냄)
    // 예외: SQL 처리 중 발생할 수 있는 SQLException을 던짐
    int update(UserVO user) throws SQLException;

    // 회원 삭제
    // 매개변수: String id (삭제하려는 회원의 ID)
    // 반환값: 회원 삭제의 성공 여부를 나타내는 int 값 (일반적으로 1은 성공, 0은 실패를 나타냄)
    // 예외: SQL 처리 중 발생할 수 있는 SQLException을 던짐
    int delete(String id) throws SQLException;
}

 

 

---