기록

노마드코더 SQL 마스터클래스 강의 기록 04

als982001 2025. 3. 22. 17:43

 

1. 작성한 코드

SELECT * FROM movies WHERE director = 'Guy Ritchie';
CREATE INDEX idx_director ON movies (director);
DROP INDEX idx_director;


EXPLAIN QUERY plan SELECT
	title
FROM
	movies
WHERE
	revenue > 100 
  AND rating = 8 AND release_date > 2020;
  
  
CREATE INDEX idx ON movies (rating, release_date, revenue);
DROP INDEX idx;

SELECT
	title
FROM
	movies
WHERE
	rating > 7;
  
CREATE INDEX idx ON movies (rating);
CREATE INDEX idx ON movies (rating, title, director, release_date);
DROP INDEX idx;
-- MySQL 연습 코드
CREATE TABLE users (
  user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username CHAR(10) NOT NULL UNIQUE, -- 10자 고정
  email VARCHAR(50) NOT NULL UNIQUE, -- 가변적
  gender ENUM('Male', 'Female') NOT NULL,
  interests SET(
    'Technology', 
    'Sports', 
    'Music', 
    'Art', 
    'Travel', 
    'Food', 
    'Fashion', 
    'Science'
  ) NOT NULL,
  bio TEXT NOT NULL, -- TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
  profile_picture TINYBLOB, -- TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
  /*
  -- TINYINT
  -- Signed: -128 to 127
  -- Unsigned: 0 to 255
  
  -- SMALLINT
  -- Signed: -32,768 to 32,767
  -- Unsigned: 0 to 65,535
  
  -- MEDIUMINT
  -- Signed: -8,388,608 to 8,388,607
  -- Unsigned: 0 to 16,777,215
  
  -- INT
  -- Signed: -2,147,483,648 to 2,147,483,647
  -- Unsigned: 0 to 4,294,967,295
  
  -- BIGINT
  -- Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
 	-- Unsigned: 0 to 18,446,744,073,709,551,615
  */
  age TINYINT UNSIGNED NOT NULL,
  is_admin BOOLEAN DEFAULT FALSE NOT NULL, -- TINYINT(1, 0)
  balance FLOAT DEFAULT 0.0 NOT NULL, -- DECIMAL(p, s)
  /*
  -- TIMESTAMP -> '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
  -- DATETIME -> '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
  */
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  birth_date DATE NOT NULL, 
  bed_time TIME NOT NULL,
  graduation_year YEAR NOT NULL, -- 1901 to 2155

	CONSTRAINT chk_age CHECK (age < 100),
  CONSTRAINT uq_email UNIQUE (email)


)

DROP TABLE users;

 

2. 배운 개념

  • Table Scan
    • 데이터베이스가 무언가를 찾기 위해 테이블의 모든 행을 하나씩 찾아보는 것
  • query planner
    • SQL문을 완료하기 위한 가장 좋은 알고리즘이나 쿼리 계획을 찾아내는 것
  • query plan
    • SQL문을 DB에서 어떻게 처리할지에 관한 것으로, 어떤 방식과 순서로 실행되는 것이 가장 효울적일지 결정하는 실행 계획
    • SQL은 선언형이기에 주어진 쿼리를 실행하기 위해 수많은 방법이 존재하는 것이 일반적이며, 이에 따라 다양한 성능 차이를 보임.
  • Index
    • 테이블에서 데이터의 위치를 가리키는 개념으로, 추가적인 쓰기 작업과 저장 공간을 이용해 데이터베이스 테이블의 검색 속도를 향상시키기 위함
    • 모든 경우에 인덱스를 생성하는 것은 데이터베이스의 성능을 저해할 수 있기에 필요에 따라 적절한 상황에서 생성해야 함
    • 인덱스 생성
      • CREATE INDEX 인덱스_이름 ON 테이블_이름 (컬림_리스트)
    • 인덱스 제거
      • DROP INDEX 인덱스_이름
  • B+ Tree
    • 데이터베이스와 파일 시스템에서 많이 쓰이는 균형 잡인 다진 트리(Balanced Tree) 자료 구조.
    • 빠르게 데이터를 삽입, 삭제, 검색하기 위한 구조
    • 인덱스를 저장하는 데에 최적화되어 있음
  • Multi Column Indexes
    • 데이터베이스 테이블에서 두 개 이상의 컬럼을 조합하여 만든 인덱스
    • 쿼리 조건에 여러 컬림이 함께 사용될 때, 성능을 더욱 최적화할 수도 있음
    • 정의된 컬럼의 순서대로 성능 최적화에 영향을 미침.
      • 예를 들어, ... (col1, col2, col3) 처럼 컬럼을 이용한 경우, col1을 조건으로 사용하거나 col1과 함께 col2, col3을 조건으로 사용할 때만 유효하게 작동하며, col2, col3만을 조건으로 사용할 때에는 인덱스가 적용되지 않음
      • 여러 컬럼이 조합된 조건으로 자주 검색되는 경우 성능을 크게 향상시킴
  • Covering Index
    • 쿼리를 처리하는 데 필요한 모든 컬럼이 인덱스에 포함되어 있어, 테이블을 읽지 않고도 결과를 가져올 수 있는 인덱스
    • 일반적으로 인덱스를 타고 원하는 행의 위치를 찾은 후, 테이블에서 실제 데이터를 다시 읽는데, 쿼리에 필요한 모든 컬럼이 인덱스에 있다면 다시 테이블을 읽을 필요가 없기에 성능 향상을 기대할 수 있음.
    • SELECT에 있는 모든 컬럼이 인덱스에 포함되어야 함
  • Index 사용과 관련하여
    • Index를 사용하면 좋은 경우
      • WHERE, ORDER BY, JOIN에서 자주 사용하는 열이 있는 경우
      • 고유한 값을 가진 열이 있는 경우 (높은 cardinality)
      • 테이블이 클 경우
    • 삽입, 수정, 삭제 작업이 지연될 수 있으므로 과도한 인덱스 사용은 금지
    • 작업 완료 후 쿼리 최적화
    • 여러 열을 함께 필터링하거나 정렬하는 쿼리의 경우 multi columns 혹은 covering 인덱스 사용
    • 큰 텍스트 열의 경우, B-tree 대신 full-text index 이용
  • MySQL에서 이용할 수 있는 타입/문법들
    • CHAR: 고정 길이 문자열(남는 부분은 공백으로 채워짐)
    • VARCHAR: 가변 길이 문자열(길이에 따라 저장 공간 사용)
    • ENUM: 미리 정의된 값 중 하나를 선택하는 문자열
    • SET: 미리 정의된 값들 중 여러 개를 선택할 수 있는 문자열 집합
    • TEXT: 긴 가변 길이 텍스트 데이터
      • TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
    • BLOB: 바이너리 데이터 (이미지, 파일 등) 저장을 위한 이진 객체
      • TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
    • INT: 정수 값 저장 
      • 음수 포함 여부에 따라 Signed, Unsigned로 구분
      • TINYINT
        • Signed: -128 to 127
        • Unsigned: 0 to 255
      • SMALLINT
        • Signed: -32,768 to 32,767
        • Unsigned: 0 to 65,535
      • MEDIUMINT
        • Signed: -8,388,608 to 8,388,607
        • Unsigned: 0 to 4,294,967,295
      • INT
        • Signed: -2,147,483,648 to 2,147,483,647
        • Unsigned: 0 to 4,294,967,295
      • BIGINT
        • Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
        •  Unsigned: 0 to 18,446,744,073,709,551,615
    • BOOLEAN: TRUE/FALSE, 실제로는 TINYINT(1, 0)로 저장
  • 제약 조건
    • CONSTRAINT로 컬럼의 제약 조건을 추가할 수 있음
    • 예: CONSTARINT chk_age CHECK (age < 100): age 컬럼의 값을 100 미만으로 제약