미생

1. 인덱스의 정의

• 인덱스(Index)는 테이블의 특정 컬럼(column)에 대해 검색 속도를 높이기 위해 추가로 만든 자료 구조
• 책의 목차처럼, 원하는 데이터가 테이블 전체를 뒤지지 않고도 바로 위치를 찾을 수 있게 해주는 구조

2. 인덱스의 종류

1) B-Tree 인덱스 (가장 흔함)

• 균형 이진 트리(B+Tree) 구조 사용
• 장점:
  • 특정 값 검색: O(log n)
  • 범위 검색: 효율적 (e.g., WHERE age BETWEEN 20 AND 30)
• 사용 예시: PRIMARY KEY, UNIQUE 인덱스

2) Hash 인덱스

• 해시 테이블 기반
• 장점:
  • 정확한 값 검색: O(1) 평균
• 단점:
  • 범위 검색에는 부적합
• 사용 예시: MySQL MEMORY 엔진

3) 기타

• Full-text 인덱스: 문자열 검색 최적화
• Spatial 인덱스: 지리정보 데이터 최적화
• Composite(복합) 인덱스: 여러 컬럼 조합 검색에 사용

B-Tree 와 Hash Index 시간 복잡도 비교

■ 행, 열 변환 작업

테이블의 행 데이터를 특정 기준으로 열 데이터로 변환하는 작업

■ 예시

과일 가게에서 판매하고 있는 과일은 [수박], [사과], [오렌지] 라고 특정할때, 날짜 별로 과일들의 수량을 계산하여 통계치를 구하려고 한다.

왼쪽은 과일이 판매될때 마다 작성한 데이터이고, 오른쪽은 구하고자 하는 결과 데이터이다. 

■ 작업

첫번째, 과일 가게 가계부 테이블를 생성한다.

CREATE TABLE `tbl_fruit_shop` (
  `pay_time` date NOT NULL COMMENT '구매 날짜',
  `fruit` varchar(50) NOT NULL COMMENT '과일',
  `cnt` int(11) NOT NULL COMMENT '수량'
)

두번째, 과일 가게 가계부 테이블를 조회했을 때 결과를 확인한다.

 * 결과 데이터는 [예시]의 왼쪽 데이터와 동일하다고 가정한다.

결과)

세번째, 각 행을 과일 별로 구분하여 데이터를 출력한다.

 * [CASE 문] 을 통해 행으로 구분되어 있던 데이터를 열로 수정

SELECT pay_time,
          CASE WHEN fruit='수박' THEN cnt END AS 수박,
          CASE WHEN fruit='사과' THEN cnt END AS 사과,
          CASE WHEN fruit='오렌지' THEN cnt END AS 오렌지
FROM tbl_fruit_shop
ORDER BY pay_time

결과)

네번째, 날짜 별로 과일들의 수량을 합산한다.

 * [SUM 함수] 와 [GROUP BY 문법]을 통해 데이터를 그룹화 함과 동시에 과일별 수량 합산

SELECT pay_time,
          SUM(CASE WHEN fruit='수박' THEN cnt END) AS 수박,
          SUM(CASE WHEN fruit='사과' THEN cnt END) AS 사과,
          SUM(CASE WHEN fruit='오렌지' THEN cnt END) AS 오렌지
FROM tbl_fruit_shop
GROUP BY pay_time
ORDER BY pay_time

결과)

다섯째, 날짜 별로 판매되지 않은 과일의 수량은 0으로 치환한다.

 * [COALESCE 함수]를 이용하여 NULL인 데이터는 0으로 치환

SELECT pay_time,
          COALESCE(SUM(CASE WHEN fruit='수박' THEN cnt END), 0) AS 수박,
          COALESCE(SUM(CASE WHEN fruit='사과' THEN cnt END), 0) AS 사과,
          COALESCE(SUM(CASE WHEN fruit='오렌지' THEN cnt END), 0) AS 오렌지
FROM tbl_fruit_shop
GROUP BY pay_time
ORDER BY pay_time

결과)

■ AUTO_INCREMENT 초기화

ALTER TABLE [테이블명] AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE [테이블명] SET [컬럼명] = @COUNT:=@COUNT+1;

■ 예시

인덱스는 1부터 시작하며 로우가 추가됨에 따라 1씩 증가하는 컬럼으로 수정

ALTER TABLE tbl_user AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE tbl_user SET user_id = @COUNT:=@COUNT+1;

주의 !! mysql 5.7 이하 미지원

■ WITH RECURSIVE 문

• 메모리 상에 가상의 테이블을 저장
• 재귀 쿼리를 이용하여 실제로 테이블을 생성하거나 데이터삽입(INSERT)을 하지 않아도 가상 테이블을 생성할 수 있다.

WITH RECURSIVE 테이블명 AS(
  SELECT 초기값 AS 컬럼별명1
  UNION ALL
  SELECT 컬럼별명1 계산식 FROM 테이블명 WHERE 제어문
 )

■ 예시

h(컬럼)이 초기값 1 부터 제어문에 합당하는 5까지의 데이터를 갖는 가상 테이블 생성

WITH RECURSIVE CTE AS(
  SELECT 1 AS h
  UNION ALL
  SELECT h+1 FROM CTE WHERE h<5
)

SELECT * FROM CTE;

※ 결과 확인

■ CASE 문

※ 사용법

• WHEN~THEN 절 : 컬럼명에 해당하는 데이터가 조건에 합당할경우 해당 조건에 결과를 반환한다.
• ELSE 절 : 상위 조건에 모두 합당하지 않을경우 ELSE 의 결과를 반환한다.

CASE 컬럼명 WHEN 조건1 THEN 결과1
                 WHEN 조건2 THEN 결과2
                                 :
                                 :
                 WHEN 조건3 THEN 결과3
                 ELSE 결과4
END

■ 예시

1) CASE_TEST 테이블 데이터 확인

2) TEST_COL 컬럼 CASE 문 처리

SELECT (CASE TEST_COL WHEN 'A' THEN 1
                      WHEN 'B' THEN 2
                      WHEN 'C' THEN 3
                      ELSE 4
                      END) AS TEST_COL
FROM CASE_TEST;

3) 결과 확인

CASE_TEST 테이블의 TEST_COL 컬럼의 데이터가 "A","B","C"가 아닌 데이터는 모두 4의 값으로 반환된다.

■ DATE_FORMAT 형식 종류

■ 예시

Datetime 형식의 (2020-07-20 13:44:41) 데이터를 2020-07-20 형태로 변환