📂 목차

• SQL 명령 정리
• INDEX 깊이 있게 다루기
  • EXPLAIN
  • FULLTEXT INDEX
    • MATCH … AGAINST 구문
    • 제약사항
• Data Types 심화
  • Date and Time Types in SQL
  • Default Values
  • Large-Object Types
  • User-Defined Types
    • Domain
    • Generating Unique Key Values
• JDBC
  • Connecting to the DB
  • Statement
  • Exception
  • PreparedStatement
  • Callable Statements
    • 자동 생성된 키 가져오기

📚 본문

SQL 명령 정리

• CREATE TABLE: 테이블 생성
• ALTER TABLE: 테이블 구조 변경
• DROP TABLE: 테이블 삭제
• RENAME: 이름 변경
• TRUNCATE: 테이블의 모든 데이터 삭제
• COMMENT: 테이블에 설명 추가

INDEX 깊이 있게 다루기

인덱스는 테이블의 검색 속도를 향상시키기 위해 특정 컬럼에 대해 생성하는 자료구조이다.

-- 복합 인덱스 생성
create index emp_name_sal_idx
on employees(name, salary);

-- 인덱스 확인
show index from employees;

-- 인덱스 삭제
alter table employees
drop index emp_name_sal_idx;

유니크 값이 많은 컬럼이 효율적이다.

이제 인덱스 실행 계획을 보자.

EXPLAIN

쿼리의 성능을 분석하고 병목 구간을 찾기 위해 사용하는 구문이다. db 가 테이블 스캔 방식이나 인덱스 사용 여부, 조인 순서 등을 어떻게 결정했는지 확인이 가능하다.

-- 인덱스 사용 전
EXPLAIN SELECT * FROM employees 
WHERE hire_date = '2005-01-01';

-- 인덱스 생성
CREATE INDEX emp_hire_idx ON employees(hire_date);

-- 인덱스 사용 후
EXPLAIN SELECT * FROM employees 
WHERE hire_date = '2005-01-01';

여기서 index 로 설정된 열의 값을 where 절에서 변질시키면 안된다. 예를들어서 index 가 있는 name 이라는 속성을 where 절에 썼다고 해보자. 그러면 substring(name, 1, 1) 등으로 이를 변질시키면 index 를 사용하지 못하게 된다. LIKE 를 써서 이를 변질시키지 말고 해야한다.

FULLTEXT INDEX

문자열 컬럼(CHAR, VARCHAR, TEXT)를 대상으로 빠른 키워드 검색을 지원하는 인덱스이다. LIKE '%keyword%' 보다 훨씬 효율적이고, 자연어 처리 기반으로 동작하며, 단어 단위로 인덱스를 생성한다.

MySQL 5.6 이상에서 InnoDB 엔진에서도 FULLTEXT 지원

-- 테이블 생성할 때 생성
create table t1 (
    varchar(36) id primary key,
    varchar(20) username not null,
    varchar(255) password not null,
    text introduction not null,
    fulltext idx_intro (introduction)
);

-- 테이블 생성 후 인덱스 생성
create fulltext index idx_intro
on t1(introduction)

MATCH … AGAINST 구문

fulltext 로 인덱스를 생성했다면 해당 컬럼에 대한 검색을 수행할 수 있다.

-- 자연어 모드
select * from t1
where match(introduction)
against('hello, ');

-- boolean 모드
select * from t1
where match(introduction)
against('+hello -name' in boolean mode);

-- query expansion 모드
select * from article
where match(introduction)
against('database' with query expansion);

• Natural Language Mode: 단어의 등장 빈도(TF-IDF) 기반으로 점수 계산 -> 연관도 높은 순으로 결과를 반환
• Boolean Mode: +(필수 포함), -(제외), *(와일드카드), “(정확히 일치)
• Query Expansion: 첫 검색 결과의 연관 단어들을 다시 확장하여 검색(추천 검색과 유사)

제약사항

이러한 FULLTEXT 는 다음과 같은 제약사항을 따른다.

• ft_min_word_len 보다 짧으면 인덱싱이 안됨
  • 최소 단어 길이 변수는 MySQL 서버 시스템 변수를 따라가며 기본값은 4로 되어 있다.
  • DB 서버 재시작 후 다시 적용해야 한다(rebuilding 필요)
• 기본적으로 불용어(a, an, the, …)등은 기본적으로 무시한다
• collation 의 문자 정렬 규칙에 따라 대소문자 구분 여부를 정할 수 있다.
  • utf8_general_ci: 대소문자는 구분하지 않음
  • utf8_bin 계열: 대소문자 구분
• 영어/라틴 문자 중심으로 설계가 되어서 한국어/일본어/중국어 등등의 언어들은 n-gram 파서를 활성화해야 한다.

collation

-- Collations 확인
show full columns from t1;

-- 변경
alter table t1
modify introduction varchar(255)
collate utf8_bin;

n-gram 사용

-- MySQL 5.7 부터 지원
create fulltext index idx_test
on t1(introduction)
with parser ngram;

Data Types 심화

Date and Time Types in SQL

• date: 연도, 월, 일
• time: 시, 분, 초, time(p) 사용 시 초의 소수 자릿수를 지정 가능 또한 time with timezone 을 사용시 시간대 정보도 함께 저장 가능
• timestamp: 날짜와 시간을 결합, timestamp(p) 도 사용가능(기본값 6), timestamp with timezone 을 지정 시 시간대 정보도 함께 저장

중요

MySQL 은 datetime 타입을 제공해저서 timestamp with timezone 과 같은 기능을 얻을 수 있다.

Default Values

default 는 속성에 기본값을 지정하도록 하는 제약조건이다. create 문에서 지정할 수 있으며, 다음과 같이 선언한다.

create table student
(
  ID varchar(5),
  name varchar(20) not null,
  dept_name varchar(20),
  tot_cred numeric(3,0) default 0,
  primary key (ID)
);

tot_cred 속성의 기본값은 0이기에 레코드를 삽입할 때, 값을 제공하지 않으면 자동으로 0으로 설정된다.

Large-Object Types

사진, 고해상도 의료 이미지, 동영상 등과 같은 큰 데이터 항목을 도메인으로 갖는 속성을 저장하고 싶을 때는 다음을 사용한다:

• CLOB: 문자 데이터용 대형 객체
• BLOB: 이진 데이터용 대형 객체

book_review CLOB(10KB)
image BLOB(10MB)
movie BLOB(2GB)

여기서 이들을 결과로 내보낼때 대형 객체를 메모리 상에 올리는 것은 현실적으로 불가능하기에 이 대신 Locator 라는 것을 주고 로케이터를 통해 어플리케이션이 작성된 호스트 언어에서 객체를 참조하는 형태로 조작하게 된다.

운영체제의 read 와 유사하다.

User-Defined Types

SQL 은 두 가지 형태의 사용자 정의 데이터 타입을 지원한다.

• distinct type
• structured data type: 중첩된 레코드 구조, 배열, 멀티셋 등

distinct type

create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;

create table department
(
  dept_name varchar(20),
  building varchar(15),
  budget Dollars
);

개념적으로 서로 달라야 하지만 타입은 유사한 것들은 distinct type 을 쓰자.

단점으로는 위 예시에서는 (department.budget + 20) 이라는 표현식이 strong type checking 때문에 허용되지 않는 표현식이다. 따라서 다음 식을 통해 바꾸는 것을 수행해줘야 한다.

cast(department.budget as numeric(12,2))

이렇게 하면 숫자로 바꿔서 결과를 낼 수 있지만 이를 다시 저장할 때는 또 바꿔줘야 한다.

Domain

이 대안으로 domain 을 만들 수 있는데,

create domain DDollars as numeric(12,2) not null;

DDollars 는 다음 차이점이 있다.

Domain 특징

• Dollars 와는 달리 not null 제약조건을 추가가능, primary key 로 정의 가능
• 강력한 타입이 아니기 때문에 기본 타입이 호환되는 한 한 도메인 타입의 값을 다른 도메인 타입에 할당할 수 있음

예시

-- 범위 제한
create domain YearlySalary numeric(8,2)
constraint salary_value_test check(value >= 29000.00);

-- 특정 값으로의 제한
create domain degree_level varchar(10)
constraint degree_level_test
check (value in ('Bachelors', 'Masters', 'Doctorate'));

Generating Unique Key Values

표준 SQL 에서는 다음과 같이 값을 unique 하게 생성하여 저장할 수 있도록 하는 옵션이 있다.

ID number(5) generated always as identity

여기서 주의할 점은 이는 숫자형 키 값에 대해서만 작동한다.

generated as identity

• PostgreSQL: SERIAL 을 쓴다.
• MySQL: AUTO_INCREMENT 를 쓴다.

JDBC

이제 이 sql 서버를 구동중이고 이를 다루는 다양한 기술들 중 JDBC를 본다.

기본적으로 sql 만으로는 표현할 수 있는 것에 한계가 있기 때문에 이러한 쿼리를 작성하려고 하면 embedded 하여 사용해야 한다. 또한, 비선언적 작업은 SQL 만으로 수행할 수 없다. 따라서 보고서를 출력하거나, 사용자와 상호작용하거나, 쿼리를 GUI 에 전달하는 등의 작업이다. 나머지 구성 요소는 범용 프로그래밍 언어로 작성되므로 통합된 어플리케이션을 만드려면 SQL 과 범용 프로그래밍 언어를 결합할 수단이 필요하다.

이때 JDBC를 활용하여 SQL 과 소통을 할 수 있다.

Connecting to the DB

// https://mvnrepository.com/artifact/mysql/mysql-connector-java
implementation 'mysql:mysql-connector-java:8.0.33'

우선 위를 gradle.build 에 넣어 mysql driver 를 추가하자.

Java 에서 java.sql 패키지에서는 db 와 연결할 수 있는 커넥션이라는 인터페이스를 제공한다. 여기서 커넥션의 설정을 DriverManager 로 설정하고 설정된 Connection 을 가져올 수 있다.

Connection = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/hr", // url
    userid, // user
    passwd // password
);

• url 매개변수: 서버가 실행중인 url, 포트 번호, db 이름 을 차례대로 입력해주면 해당 DB로 넘어가게 된다. db 는 생략되도 된다.
• user 매개변수: 문자열을 넣고 db 사용자 id 를 입력
• password 매개변수: 유저에 대한 비밀번호이다. 보통은 Java 에 직접치지 않고 따로 저장하여 운용한다.

오라클 프로토콜은 jdbc:oracle:thin 이다.

Statement

DB 연결이 됐다면, 이제 SQL 문을 실행할 준비가 된 것이다.

java 에서는 이 DB에게 명령을 내리기 위해 Statement 클래스가 있는데, 이를 통해서 전송하고 실행할 수 있게 된다.

Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into instructor values(’77987’,’Kim’,’Physics’,98000)");

Statement 는

• executeQuery()
• executeUpdate()

메서드를 가지고 있어 이를 호출할 수 있는데 query 로 메서드를 실행하면 ResultSet 을 반환하며, 쿼리문이 아닌 문장은 결과 집합을 반환하지 않고 int 를 반환하게 된다. 이때는 삽입, 갱신, 삭제 된 튜플 수를 반환하게 된다.

업데이트 값이 너무 많아 int 값 범위를 벗어날 때, executeLargeUpdate() 를 쓸 수 있다.

Exception

SQL 메서드를 실행할 때에는 반드시 예외처리를 해주어야 하는데, SQLException 의 처리를 통해 try-catch 를 해주면 되겠다.

또한, Connection, Statement, 기타 JDBC를 여는 객체는 시스템 자원을 소모하기 때문에 프로그래머는 반드시 close() 를 호출해주어야 한다. 이때는 try-resource-with 과 함께 사용한다.

PreparedStatement

Statement 만 계속 사용하면 특정 sql 문장을 여러개 사용하고 싶을때, 수행하고 싶은 만큼의 문장들을 선언해주어야 한다. 이를 방지하기 위해 java 에서는 PreparedStatement 를 제공해준다.

conn.prepareStatement("insert into instructor values(?,?,?,?)");

? 를 파싱하여 다른 값들로 대체하여 넣을 수 있도록 해준다.

pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
pStmt.executeUpdate();
pStmt.setString(1, "88878");
pStmt.executeUpdate();

마지막에서는 1번 파라미터만 88878 로 바꾸고 나머지는 동일하게 한 후 실행하게 할 수 있다.

Callable Statements

DBMS 측에 선언된 함수와 프로시저에 대한 수행을 하라고도 할 수 있다.

CallableStatement cStmt1 = conn.prepareCall("{? = call some_function(?)}");
CallableStatement cStmt2 = conn.prepareCall("{call some_procedure(?,?)}");

자동 생성된 키 가져오기

add, update, delete 와 같은 처리는 반환 값이 굳이 없어도 되지만(boolean 으로 처리 완료를 나타내는 값을 반환하기도 한다), 가끔 DB 자체에서 생성하는 고유한 ID 들은 프로그래밍 단에서는 ID를 생성하지 않기 때문에 가져올 수 없다. 이 기본키가 가장 중요한 값인데, 이를 가져오지 못하면 저장한 행에 대해 후처리를 할 수가 없다. 이를 위해서 Statement 에서는 RETURN_GENERATED_KEYS 를 제공하고 있다.

사용법은 다음과 같다:

public Long insertAndGetId(String email, String name, String password) {
    String sql = "INSERT INTO user (email, name, password) VALUES (?, ?, ?)";
    Long generatedId = null;

    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement(sql, 
                Statement.RETURN_GENERATED_KEYS)) {

        ps.setString(1, email);
        ps.setString(2, name);
        ps.setString(3, password);

        int updateCount = ps.executeUpdate();

        // 생성된 키 가져오기
        if (updateCount > 0) {
            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) {
                    generatedId = rs.getLong(1);
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return generatedId;
}