DATABASE-Connection Pool

Aug 12, 2021 in Computer Science, DATABASE

목차
- Connection Pool에 대한 개념과 기본적인 원리
- Commons DBCP로 Connection Pool 이해하기

Connection Pool이란?

클라이언트의 요청에 따라 각 어플리케이션의 스레드에서 데이터베이스에 접근하기 위해서는 Connection이 필요하다
Connection pool은 이런 Connection을 여러 개 생성해 두어 저장해 놓은 공간, 또는 이 공간의 Connection을 필요할 때 꺼내 쓰고 반환하는 기법을 만한다

DB에 접근하는 단계

웹 컨테이너가 실행되면서 DB에 연결된 Connection 객체들을 미리 생성하여 pool에 저장한다
DB에 요청 시, pool에서 Connection 객체를 가져와 DB에 접근한다
처리가 끝나면 다시

Connection이 부족하면?

모든 요청이 DB에 접근하고 있고 남은 Connection이 없다면, 해당 클라이언트를 대기 상태로 전환시키고 Pool에 Connection이 반환되면 대기 상태에 있는 클라이언트에게 순차적으로 제공된다

왜 사용할까?

매 연결마다 Connection 객체를 생성하고 소멸시키는 비용을 줄일 수 있다
미리 생성된 Connection 객체를 사용하기 때문에, DB 접근 시간이 단축된다
DB에 접근하는 Connection 수를 제한하여, 메모리와 DB에 걸리는 부하를 조정할 수 있다

Commons DBCP로 Connection Pool 이해하기

Commons DBCP 속성 설정

Commons DBCP의 속성은 BasicDataSource 클래스의 setter 메서드로 설정할 수 있다

Spring 프레임 워크를 사용한다면 다음과 같이 bean 설정으로 속성을 등록한다

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"  
    destroy-method="close"
    p:driverClassName="${db.driverClassName }"
    p:url="${db.url}"
    p:username="${db.username}"
    p:password="${db.password}"
    p:maxActive="${db.maxActive}"
    p:maxIdle="${db.maxIdle}"
    p:maxWait="${db.maxWait}"
/>

커넥션의 개수

커넥션 풀의 저장 구조

커넥션 생성은 Commons DBCP에서 이루어진다
Commons DBCP는 PoolableConnection 타입의 커넥션을 생성하고 생성한 커넥션에 ConnectionEventListener를 등록한다
ConnectionEventListener에는 애플리케이션이 사용한 커넥션을 풀로 반환하기 위해 JDBC 트라이버가 호출할 수 있는 콜백 메서드가 있다
생선된 커넥션은 commons-pool의 addObject() 메서드로 커넥션 풀에 추가된다
commons-pool은 내부적으로 현재 시간을 담고 있는 타임 스탬프와 추가된 커넥션의 레퍼런스를 한 쌍으로하는 ObjectTimestampPari라는 자료구조를생헝한다
이들을 LIFO(last in first out) 형태의 CursorableLinkedList로 관리한다

커넥션 개수 관련 속성

속성 이름	설명
initialSize	BasicDataSource 클래스 생성 후 최초로 getConnection() 메서드를 호출할 때 커넥션 풀에 채워 넣을 커넥션 수
maxActive	동시에 사용할 수 있는 최대 커넥션 개수 (기본값: 8)
maxIdle	커넥션 풀에 반납할 때 최대로 유지될 수 있는 커넥션 개수 (기본값: 8)
minIdle	최소한으로 유지할 커넥션 개수 (기본값: 0)

8개의 커넥션을 최대로 활용할 수 있고 4개는 사용중이고 4개는 대기 중인 상태
커넥션 개수와 관련된 가장 중요한 성능 요소는 일반적을 커넥션의 최대 개수이다
maxActive 값은 DBMS의 설정과 애플리케이션 서버의 개수, Apache, Tomcat에서 동시에 처리할 수 있는 사용자 수 등을 고려해서 설정해야 한다.
DBMS가 수용할 수 잇는 커넥션 개수를 확인한 후에 어플리케이션 서버 인스턴스 1개가 사용하기에 적절한 개수를 설정한다
사용자가 몰려서 커넥션을 많이 사용할 때는 maxActive 값이 충분히 크지 않다면 병목 지점이 될 수 있다
반대로 사용자가 적어서 사용자가 적어서 상요 중인 커넥션이 많지 않은 시스템에서는 maxActive 값을 지나치게 작게 설정하지 않는 한 성능에 영향이 없다

커넥션을 얻기 전 대기 시간

BasicDataSource 클래스의 maxWait 속성은 커넥션 풀 안의 커넥션이 고갈됐을 때 커넥션 반납을 대기하는 시간이며 기본값은 무한정이다
사용자가 갑자기 급증하거나 DBMS에 장애가 발생했을 때 장애를 더욱 크게 확산시킬 수 있어 주의해야 한다.
적절한 maxWait 값을 설정하려면 TPS(transaction per seconds)와 Tomcat에서 처리 가능한 스레드 개수 등을 이해해야 한다

TPS(transaction per seconds)

maxActive = 5과 maxIdle = 5, minIdle = 5로 설정한 상황을 가정한다
요청 하나당 쿼리 10개를 실행한다고 가정한다
각 쿼리의 평균 실행 시간은 50밀리초라고 하면 전체 10개 쿼리의 실행 시간은 500밀리초 이다.
요청에 대한 초종 응답 시간은 500밀리초라고 생각할 수 있다.

커넥션 풀에 이용 가능한 유휴 상태의 커넥션이 5개일 때는 동시에 5개의 요청을 500밀리초 동안 처리한다
따라서 1초 동안에는 10개의 요청을 처리할 수 있고 성능 지수는 10TPS라고 볼 수 있다

TPS와 커넥션 개수와의 관계

처리할 요청 수가 증가해도 커넥션 풀의 커넥션 개수가 5개이면 10TPS 이상의 성능을 낼 수 없다
1번부터 5번까지 요청이 실행되는 동안은 커넥션 풀에 여분의 커넥션이 없다
6번부터 10번까지 요청은 대기(wait)상태가 되 여분의 커넥션이 생길 때까지 maxWait 값만큼 기다린다

이를 해결하는 가장 수운 방법은 maxActive 값을 높여서 커넥션 풀의 개수를 늘리는 것이다

커넥션의 개수를 5에서 10으로 늘리면 전체적인 성능도 10TPS로 증가한다
하지만 일반적을 DBMS의 리소스는 다른 서비스와 공유해 사용하는 경우가 많기 때문에 무조건 커넥션 개수를 크게 설정할 수 없다
따라서 예상 접속자 수와 서비스의 실제 부하를 측정해 최적의 값을 설정하는 것이 중요하다
대기 시간 (wait) 값 조절이 커넥션의 개수를 무한히 늘리지 않고 최적의 시스템을 환겨을 구축하는데 중요한 역할을 한다
maxWait 값을 어떻게 설정했는지가 일시적인 과부하 상태에서 드러나는 시스템의 전체적인 견고함을 결정짓는다

적당한 maxWait 값은 Commons DBCP 외에 Tomcat의 동작 방식도 고려해야 한다
Tomcat은 스레드 기반으로 동작해 사용자의 요청을 처리한다
Tomcateh 내부에 스레드 풀을 가지고 잇어서 사용자의 요청이 들어올 때마다 스레드 풀에서 하나씩 스레드를 꺼내 요청을 처리한다
1 ~ 5번의 요청이 처리되기 전에 또 다른 요청이 들어온다
즉 동시에 6개의 요청이 들어왔을 때 6번 요청은 여분의 커넥션이 없으므로 maxWait 값 만큼 기다린다 (Tomcat의 스레드가 기다리는 주체이다)

적절한 maxWait 값은?

위의 그림에서 maxWait 속성에 설정한 시간이 10,000 밀리초이면 처리량을 넘어서는 요청의 스레드는 10초 동안 대기 상태에 있게 된다
그리고 사용자의 요청이 계속 증가하면 결국 Tomcat 스레드 풀의 모든 스레드가 소진돼 Tomcat은 아래와 같은 오류를 출력하며 응답을 멈출 것이다
1
심각: All threads (512) are currently busy, waiting. Increase maxThreads (512) or check the servlet status
10초 동안의 대기 상태가 해제되고 커넥션을 획득해 사용자의 요청을 열심히 처리하고 응답을 보내도 그 응답을 받을 사용자는 이미 떠나고 난 뒤인 경우도 있다
이럴 경우 사용자가 인내할 수 있는 시간을 넘어서는 maxWait 값은 아무런 의미가 없다
반대의 경우에는 커넥션 풀에 여분이 없을 때마다 오류가 반환된다
maxWait 값도 사용자의 대기 가능한 시간 같은, 어플리케이션의 특성 및 자원의 상황을 고려해야한다
만약 갑작스럽게 사용자가 증가해 maxWait 값 안에 커넥션을 얻지 못하는 빈도가 늘어난다면 maxWait을 줄여서 스레드가 한도에 도달하지 않도록 한다
전체 시스템의 장애는 피하고 ‘간헐적 오류’가 발생하는 정도로 장애의 영향을 줄인다
이런 상황이 자주 있다면 Commons DBCP의 maxActive 값과 Tomcat의 maxThread 값을 동시에 늘이는 것을 고려한다
그러나 시스템 자원의 한도를 넘어선다면 시스템을 확충해야한다

커넥션의 검사와 정리

유효성 검사 쿼리 (validation query)와 Evictor 스레드 관련 설정으로도 애플리케이션의 안정성을 높일 수 있다

유효성 검사쿼리의 설정

JDBC 커넥션의 유효성에 대한 validationQuery 옵션
- testOnBorrow: 커넥션 풀에서 커넥션을 얻어올 때 테스트 실행(기본값: true)
- testOnReturn: 커넥션 풀로 커넥션을 반환할 때 테스트 실행(기본값: false)
- testWhileIdle: Evictor 스레드가 실행될 때 (timeBetwwenEvictionRunMills > 0) 커넥션 풀 안에 있는 유휴 상태의 커넥션을 대상으로 테스트 실행 (기본값: false)
검증에 지나치게 자원을 소모하지 않게 testOnBorrow와 testOnReturn 옵션을 false로 설정한다
오랫동안 대기상태였던 커넥션이 끊어지는 현상을 막기위해 testWhileIdle 옵션은 true로 설정한다
부적절한 상태의 커넥션이 반납되었을 때 testWhileIdle = true이면 커넥션 풀에서 오류가 발생하는 커넥션을 제외할 수 있다

Evictor 스레드와 관련된 속성

Evictor 스레드는 Commons DBCP 내부에서 커넥션의 자원을 정리하는 구성요소이며 별도의 스레드로 실행된다
관련 속성
- timeBetweenEvictionRunsMills: Evictor 스레드가 동작하는 간격, 기본값은 -1이며 Evictor 스레드의 실행이 비활성화되어있다
- numTestsPerEvictionRun: Evictor 스레드 동작 시 한 번에 검사할 커넥션 수
- minEvictableIdleTimeMillis: 스레드 동작 시 커넥션의 유휴 시간을 확인해 설정 값 이상일 경우 커넥션을 제거한다 (기본값: 30분)
역할
1. 커넥션 풀 내의 유휴 상태의 커넥션 중에서 오랫동안 사용되지 않은 커넥션을 추출해 제거한다
  - Evictor 스레드 실행 시 설정된 numTestsPerEvictionRun 속서값만큼 CursorableLinkedList의 ObjectTimestampPar을 확인한다
  - ObjectTimestampPair의 타임 스탬프 값과 현재 시간의 타임 스탬프 값의 차이가 minEvictableIdleTimeMillis 속성을 초과하면 해당 커넥션을 제거한다
  - 커넥션 숫자를 적극적으로 줄여야한다면 사용하지 않기
2. 커넥션에 대해서 추가로 유효성 검사를 수행해 문제가 있을 경우 해당 커넥션을 제거한다
  - testWhileIdle = true 일때만 이 동작을 수행한다
  - 첫 번째 작업 시 minEvictableIdleTimeMillis 속성값을 초과하지 않은 커넥션에 대해서 추가로 유효성 검사를 수행하는 것이다.
3. 앞의 두 작업 이후 남아있는 커넥션의 개수가 minIdle 속성값보다 작으면 minIdle 속성값 만큼 커넥션을 생성해 유지한다
  
  testWhileIdle=true && timeBetweenEvictionRunMillis > 0이면 위의 3가지 역할을 다 수행하고,
  
  testWhileIdle=false && timeBetweenEvictionRunMillis > 0이면 두 번째 동작은 수행하지 않는다.
주의점
- Evictor 스레드는 동작 시에 커넥션 풀에 잠금(lock)을 걸고 동작하기 때문에 너무 자주 실행하면 서비스 실행에 부담을 줄 수 있다
- numTestsPerEvictionRun 값을 너무 크게 설정하면 Evictor 스레드가 검사해야하는 커넥션 수가 많아져 잠금 상태에 있는 시간이 길어지므로 실행에 부담이 된다.
유용한 방안
- IDC(internet data center) 정책에 따라서는 서버 간의 소켓 연결 후 정해진 시간 이상 아무런 패킷도 주고받지 않으면 연결을 종료한다
- timeBetweenEvictionRunsMills 속성으로 의도하지 않게 연결이 끊어지는 것을 방어할 수 있다.
- ex) 30분 동안 통신이 없을 때 연결이 끊어지는 정책으로 네트워크를 운영한다면, BasicDataSource가 풀링(pooling)하는 커넥션의 수가 30개라고 가정할 때
  - 30분 안에 모든 커넥션에 유효성 검사 쿼리를 한 번씩은 실행하는 것이 바람직하다.
  - Evictor 스레드가 5분에 한 번씩 실행되도록 설정했을 때 30분 동안 Evictor 스레드 실행 횟수는 6번이므로 매번 5개의 커넥션을 검사해야 전체 커넥션을 테스트할 수 있다.
  - 30분 안에 5분마다 Evctor 스레드가 실행되면 6번 실행되지만 오차를 감안해 5번으로 가정하면 이때 설정해야 할 numTestsPerEvictionRun 값은 다음과 같이 구할 수 있다
  - 6 * numTestsPerEvictionRun > 30개
  - 따라서 numTestsPerEvictionRun 속성값은 최소 6 이상이어야 한다. 일반적인 공식으로 정리하면 다음과 같다
  - ('IDC 정책에서 허용하는 최대 유휴 커넥션 유지 시간' / timeBetweenEvictionRunsMillis 속성값) * numTestsPerEvictionRun 속성값) > 전체 커넥션 개수

reference

읽어보면 좋은 것들

1부 : https://techblog.woowahan.com/2664/
2부 : https://techblog.woowahan.com/2663/

Comment and share

DATABASE-VIEW INDEX TRIGGER

Oct 27, 2020 in DATABASE, DDL

VIEW

Explain

가상 테이블로 일부분만 데이터로 보고자 할때 사용합니다.
실제 데이터를 저장하고 있지는 않습니다.
한마디로 특정 컬럼의 데이터를 보여주는 역할만 합니다.
뷰를 사용함으로 쿼리를 더 단순하게 만들수 있습니다.
한번 생성된 뷰는 수정이 불가능 하며 인덱스설정이 불가능 합니다.

Syntax

1	CREATE VIEW <뷰이름> AS (QUERY)

Example

국가코드와 국가이름이 있는 뷰

1
2
3

CREATE VIEW code_name AS
SELECT code, name
FROM country

city 테이블에 국가 이름 추가

SELECT *
FROM city
JOIN code_name
ON city.countrycode = code_name.code

INDEX

Explain

정의 : 테이블에서 데이터를 검색할때 빠르게 찾을수 있도록 해주는 기능입니다.
장점 : 검색속도가 빨라짐
단점 : 저장공간을 10% 정도 더 많이 차지 INSERT, DELETE, UPDATE 할때 속도가 느려짐
사용법 : SELECT시 WHERE 절에 들어가는 컬럼을 Index로 설정하면 좋다.
내부 작동 원리 (B-Tree)
- 루트노드와 리프노드의 계층적 구조로 루트노드를 이용하여 리프노드에서의 데이터를 빠르게 찾을수 있는 자료구조 알고리즘.

Syntax

1	CREATE INDEX <인덱스 이름> AS <테이블 이름(필드 이름)>

Example

employees 데이터 베이스에서 실행

실행계획을 확인하여 인덱스를 사용하는지 확인

EXPLAIN
SELECT *
FROM salaries
WHERE from_date < "1986-01-01"

EXPLAIN
SELECT *
FROM salaries
WHERE to_date < "1986-01-01"

인덱스 확인
1
SHOW INDEX FROM salaries;

인덱스 생성

CREATE INDEX fdate
ON salaries (from_date)

CREATE INDEX tdate
ON salaries (to_date)

여러개의 컬럼을 가지는 인덱스 생성도 가능
1
2
CREATE INDEX ftdate
ON salaries (from_date, to_date)

인덱스 삭제

DROP INDEX fdate
ON salaries

DROP INDEX tdate
ON salaries

DROP INDEX ftdate
ON salaries

여러개의 컬럼을 조건으로 WHERE절에 사용하는 경우 인덱스 확인

인덱스가 하나의 컬럼에 있을때 보다 둘다 있을때가 더 빠름

EXPLAIN
SELECT *
FROM salaries
WHERE from_date < "1986-01-01" AND to_date < "1986-01-01"

TRIGGER

정의 : 특정 테이블을 감시하고 있다가 설정된 조건의 쿼리가 실행되면 미리 지정한 쿼리가 자동으로 실행되도록하는 방법

Syntax

create trigger {trigger name}
{before | after} {insert | update |delete}
on <table name> for each row
begin
    <excute query>
end

Example

database 생성

create table data1 (
    id varchar(20),
    msg varchar(50) not null
);

create table data_backup (
    id varchar(20),
    msg varchar(50) not null
);

Trigger 생성

create trigger backup
before delete on data1
for each row 
begin
    insert into data_backup(id, msg)
    values(old.id, old.msg);
end

Comment and share

DATABASE-Sub Query

Oct 27, 2020 in DATABASE, DML

Sub Query

sub query는 query 문 안에 있는 query를 의미합니다.

SELECT절, FROM절, WHERE 등에 사용이 가능합니다.

Example

전체 나라수, 전체 도시수, 전체 언어수를 출력 ( SELECT 절에 사용 )

`SUB QUERY 사용시 JOIN TABLE 크기를 줄일 수 있다`

SELECT
    (SELECT count(name) FROM city) AS total_city,
    (SELECT count(name) FROM country) AS total_country,
    (SELECT count(DISTINCT(Language)) FROM countrylanguage) AS total_language
FROM DUAL

800만 이상되는 도시의 국가코드, 이름, 도시인구수를 출력 ( FROM 절에 사용 )

SUB QUERY를 안 쓸때

city table row : 4079
coutnry table row : 239

JOIN TABLE : 4079 * 239

SELECT *
FROM
    city
JOIN
    (SELECT code, name
    FROM country) AS country
ON city.countrycode = country.code
HAVING city.population > 8000000;

SUB QUERY를 쓸 때

city table row : 10
coutnry table row : 239

JOIN TABLE : 10 * 239

SELECT *
FROM
    (SELECT countrycode, name, population
    FROM city
    WHERE population > 8000000) AS city
JOIN
    (SELECT code, name
    FROM country) AS country
ON city.countrycode = country.code;

800만 이상 도시의 국가코드, 국가이름, 대통령이름을 출력( WHERE 절에 사용 )

SELECT code, name, HeadOfState
FROM country
WHERE code IN (
    SELECT DISTINCT(countrycode) FROM city WHERE population > 8000000
)

Comment and share

DATABASE-JOIN UNION

Oct 26, 2020 in DATABASE, DML

JOIN

JOIN은 여러개의 테이블에서 데이터를 모아서 보여줄 때 사용됩니다.
JOIN에는 INNER JOIN, LEFT JOIN, RIGHT JOIN이 있습니다.

MAKE TEST TABLE & DATA

create table & data

CREATE TABLE user (
    user_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(30) DEFAULT NULL,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE addr (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    addr varchar(30) DEFAULT NULL,
    user_id int(11) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user(name)
VALUES ("jin"),
("po"),
("alice"),
("petter");

INSERT INTO addr(addr, user_id)
VALUES ("seoul", 1),
("pusan", 2),
("deajeon", 3),
("deagu", 5),
("seoul", 6);

INNER JOIN

두 테이블 사이에 공통된 값이 없는 row는 출력하지 않는다.

두 테이블을 합쳐 id, name, addr 출력

SELECT id, user.name, addr.addr
FROM user
JOIN addr
ON user.user_id = addr.user_id

world 데이터베이스에서 도시이름과 국가이름을 출력

SELECT city.name AS country_name, country.name AS city_name
FROM city
JOIN country
ON city.CountryCode = country.code

아래와 같이 사용할수도 있다.

1
2
3

SELECT city.name AS country_name, country.name AS city_name
FROM city, country
WHERE city.CountryCode = country.code

LEFT JOIN

왼쪽 테이블을 기준으로 왼쪽 테이블의 모든 데이터가 출력되고 매핑되는 키값이 없으면 NULL로 출력된다.

두 테이블을 합쳐 id, name, addr 출력

SELECT id, user.name, addr.addr
FROM user
LEFT JOIN addr
ON user.user_id = addr.user_id

RIGHT JOIN

오른쪽 테이블을 기준으로 왼쪽 테이블의 모든 데이터가 출력되고 매핑되는 키값이 없으면 NULL로 출력된다.

두 테이블을 합쳐 id, name, addr 출력

SELECT id, user.name, addr.addr
FROM user
RIGHT JOIN addr
ON user.user_id = addr.user_id

JOIN과 DISDINCT의 사용

지역과 대륙별 사용하는 언어 출력

SELECT DISTINCT country.Region, country.continent, countrylanguage.Language
FROM country
JOIN countrylanguage
ON countrylanguage.CountryCode = country.Code

대륙과 지역별 사용하는 언어의 수 출력

SELECT sub1.region, sub1.continent, count(*) as count
FROM (
SELECT DISTINCT country.Region, country.continent, countrylanguage.Language
FROM country
JOIN countrylanguage
ON countrylanguage.CountryCode = country.Code
) AS sub1
GROUP BY sub1.region, sub1.continent

테이블 세개 조인하기

국가별, 도시별, 언어의 사용율

SELECT country.name as country_name, city.name as city_name,
countrylanguage.language, countrylanguage.percentage
FROM country
JOIN city
ON country.code = city.countrycode
JOIN countrylanguage
ON country.code = countrylanguage.countrycode

SELECT country.name as country_name, city.name as city_name,
countrylanguage.language, countrylanguage.percentage
FROM country, city, countrylanguage
WHERE country.code = city.countrycode and country.code = countrylanguage.countrycode

UNION

UNION은 SELECT 문의 결과 데이터를 하나로 합쳐서 출력합니다.

컬럼의 갯수와 타입, 순서가 같아야 합니다.

UNION은 자동으로 distinct를 하여 중복을 제거해 줍니다.

중복제거를 안하고 컬럼 데이터를 합치고 싶으면 UNION ALL을 사용합니다.

또한 UNION을 이용하면 Full Outer Join을 구현할수 있습니다.

UNION
- user 테이블의 name 컬럼과 addr 테이블의 addr 컬럼의 데이터를 하나로 함쳐서 출력
  1
  2
  3
  4
  5
  SELECT name
  FROM user
  UNION
  SELECT addr
  FROM addr
UNION ALL
- 중복데이터를 제거하지 않고 결과 데이터 합쳐서 출력
  1
  2
  3
  4
  5
  SELECT name
  FROM user
  UNION ALL
  SELECT addr
  FROM addr

FULL OUTER JOIN

union을 이용하여 full outer join 구현

SELECT id, user.name, addr.addr
FROM user
LEFT JOIN addr
ON user.user_id = addr.user_id
UNION
SELECT id, user.name, addr.addr
FROM user
RIGHT JOIN addr
ON user.user_id = addr.user_id

Comment and share

DATABASE-Function

Oct 26, 2020 in DATABASE

Functions

CEIL, ROUND, TRUNCATE는 소수점 올림, 반올림, 버림 함수입니다.
- CEIL
  
  CEIL는 실수 데이터를 올림 할 때 사용합니다.
- 12.345를 올림하여 정수로 나타냄
  1
  SELECT CEIL(12.345)
- 국가별 언어 사용 비율을 소수 첫번째자리에서 올림하여 정수로 나타냄
  1
  2
  SELECT CountryCode, Language, Percentage, CEIL(Percentage)
  FROM countrylanguage
- ROUND
  
  ROUND는 실수데이터를 반올림 할 때 사용합니다.
  - 12.345를 소수 둘째자리까지 나타내고 소수 셋째자리에서 반올림
    1
    SELECT ROUND(12.345, 2)
  - 국가별 언어 사용 비율을 소수 첫번째자리에서 반올림하여 정수로 나타냄
    1
    2
    SELECT CountryCode, Language, Percentage, ROUND(Percentage, 0)
    FROM countrylanguage
- TRUNCATE
  
  TRUNCATE는 실수 데이터를 버림 할 때 사용합니다.
  - 12.345를 소수 둘째자리까지 나타내고 소수 셋째자리에서 버림
    1
    SELECT TRUNCATE(12.345, 2)
  - 국가별 언어 사용 비율을 소수 첫번째자리에서 버림하여 정수로 나타냄
    1
    2
    SELECT CountryCode, Language, Percentage, TRUNCATE(Percentage, 0)
    FROM countrylanguage
    1
    2
    3
    SELECT CountryCode, Language, Percentage, ROUND(Percentage, 0),
    TRUNCATE(Percentage, 0)
    FROM countrylanguage
DATE_FORMAT

DATE_FORMAT은 날짜 데이터에 대한 포멧을 바꿔줍니다.

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
- sakila 데이터 베이스에서 월별 총 수입
  1
  2
  3
  SELECT DATE_FORMAT(payment_date, "%Y-%m") AS monthly, SUM(amount) AS amount
  FROM payment
  GROUP BY monthly

조건문 IF, IFNULL, CASE

SQL에서도 다른 언어에서 처럼 조건문 사용이 가능합니다. IF, CASE 에 대해서 설명합니다.
IF(조건, 참, 거짓)

- 도시의 인구가 100만이 넘으면 "big city" 그렇지 않으면 "small city"를 출력하는 city_scale 컬럼을 추가
    <!--hexoPostRenderEscape:<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">name</span>, population, <span class="keyword">IF</span>(population &gt; <span class="number">1000000</span>, <span class="string">&quot;big city&quot;</span>, <span class="string">&quot;small city&quot;</span>) <span class="keyword">AS</span> city_scale</span><br><span class="line"><span class="keyword">FROM</span> city</span><br></pre></td></tr></table></figure>:hexoPostRenderEscape-->

IFNULL(참, 거짓)

독립년도가 없는 데이터는 0으로 출력

1 2	SELECT IndepYear, IFNULL(IndepYear, 0) as IndepYear FROM country

CASE

CASE 
WHEN (조건1) THEN (출력1)
WHEN (조건2) THEN (출력2)
END AS (컬럼명)

나라별로 인구가 10억 이상, 1억 이상, 1억 이하인 컬럼을 추가하여 출력

SELECT name, population,
CASE
WHEN population > 1000000000 THEN "upper 1 bilion"
WHEN population > 100000000 THEN "upper 100 milion"
ELSE "below 100 milion"
END AS result
FROM country

Comment and share

DATABASE-DML-INSERT UPDATE DELETE

Oct 26, 2020 in DATABASE, DML

INSERT

테이블 이름 뒤에 오는 컬럼이름은 생략이 가능하며 대신에 VALUES 뒤에 value 값이 순서대로 와야 합니다.
1
2
INSERT INTO <table_name>(<column_name_1>, <column_name_2>, ...)
VALUES(<value_1>, <value_2>, ...)
test 데이터 베이스 선택
1
sql> USE test;

user1 테이블에 user_id, name, email, age, rdate를 입력

INSERT INTO user1(user_id, name, email, age, rdate)
VALUES (1, "jin", "pdj@gmail.com", 30, now()),
(2, "peter", "peter@daum.net", 33, '2017-02-20'),
(3, "alice", "alice@naver.com", 23, '2018-01-05'),
(4, "po", "po@gmail.com", 43, '2002-09-16'),
(5, "andy", "andy@gmail.com", 17, '2016-04-28'),
(6, "jin", "jin1224@gmail.com", 33, '2013-09-02');

city_2 테이블 생성

CREATE TABLE city_2 (
    Name VARCHAR(50),
    CountryCode CHAR(3),
    District VARCHAR(50),
    Population INT
)

select 절에서 나온 결과데이터를 Insert

INSERT INTO city_2
SELECT Name, CountryCode, District, Population
FROM city
WHERE Population > 8000000;

UPDATE SET

업데이트시에는 항상 select-where로 변경할 데이터를 확인하고 update 해줘야 실수를 줄일수 있습니다. 또한 limit도 함께 사용해주면 좋습니다.

1
2
3

UPDATE <table_name>
SET <column_name_1> = <value_1>, <column_name_2> = <value_2>
WHERE <condition>

jin 이름을 가지고 있는 사람의 나이를 20, 이메일을 pdj@daum.net으로 변경
1
2
3
sql> UPDATE user1
SET age=20, email="pdj@daum.net"
WHERE name="jin"

DELETE, DROP, TRUNCATE

조건을 설정하여 데이터를 삭제할수 있습니다.

1 2	DELETE FROM <table_name> WHERE <condition>

2016-01-01 이전 데이터 삭제 (DML)

1 2	sql> DELETE FROM user1 WHERE rdate < "2016-01-01"

테이블 구조를 남기고 모든 데이터를 삭제 (DLL)
1
sql> TRUNCATE FROM user1
테이블 전체를 모두 삭제 (DLL)
1
sql> DROP FROM user1

Comment and share

DATABASE-Constraint

Oct 26, 2020 in DATABASE, MYSQL, DDL

Constraint : 제약조건

데이터 베이스의 테이블을 생성할때 각 컬럼은 각각의 제약조건을 갖습니다.

NOT NULL

NULL 값 (비어있는 값)을 저장할수 없습니다.
UNIQUE

같은 값을 저장할수 없습니다.
PRIMARY KEY
- NOT NULL과 UNIQUE 의 제약조건을 동시에 만족해야 합니다.
- 그러므로 컬럼에 비어 있는 값과 동일한 값을 저장할수 없습니다.
- 하나의 테이블에 하나의 컬럼만 조건을 설정할수 있습니다.
AUTO_INCREMENT

주로 테이블의 PRIMARY KEY 데이터를 저장할때 자동으로 숫자를 1씩 증가시켜 주는 기능으로 사용합
니다.

FOREIGN KEY

다른 테이블과 연결되는 값이 저장됩니다.

테이블 수정으로 FK 설정

alter table money
add constraint fk_user
foreign key (user_id)       # money의 user_id
references user (user_id);  # user의 user_id

테이블 생성시 FK 설정

create table money(
    money_id int primary key auto_increment,
    income int, 
    user_id int,
    foreign key (user_id) references user(user_id)
);

ON DELETE, ON UPDATE 설정
- 참조를 받는 데이터가 수정하거나 삭제될때 참조 하는 데이터를 설정(수정,삭제 등등)
- CASECADE : 참조 받는 데이터가 수정, 삭제하면, 참조하는 데이터도 수정 삭제
- SET NULL : 참조 받는 데이터가 수정 삭제하면, 참조하는 데이터는 NULL로 수정

DEFAULT

데이터를 저장할때 해당 컬럼에 별도의 저장값이 없으면 DEFAULT로 설정된 값이 저장됩니다.

create table money(
    money_id int primary key auto_increment,
    income int, 
    user_id int,
    foreign key (user_id) references user(user_id)
    on update cascade on delete set null
);

Comment and share

DATABASE-DATA TYPE

Oct 26, 2020 in DATABASE, DDL

DATA TYPE

데이터 베이스의 테이블을 생성할때 각 컬럼은 데이터 타입을 가집니다.

reference : https://dev.mysql.com/doc/refman/5.7/en/data-types.html

Numberic

reference : https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html

정수 타입 ( integer types )
실수 (floating-point types)

소수점을 나타내기 위한 데이터 타입으로 아래의 두가지 데이터 타입이 있습니다.

두가지의 데이터 타입은 데이터 저장공간의 차이가 있습니다.

FLOAT (4byte), DOUBLE (8byte)

또한 아래와 같이 고정 소수점 타입으로도 사용이 가능합니다.

FLOAT(M,D), DOUBLE(M,D)

Date & Time

reference : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html

DATE

DATE는 날짜를 저장하는 데이터 타입이며, 기본 포멧은 “년-월-일” 입니다.
DATETIME

DATETIME은 날짜와 시간을 저장하는 데이터 타입이며, 기본 포멧은 “년-월-일 시:분:초” 입니다.
TIMESTAME

TIMESTAME는 날짜와 시간을 저장하는 데이터 타입이며,

DATETIME과 다른점은 날짜를 입력하지 않으면 현재 날짜와 시간을 자동으로 저장할수 있는 특징이 있습니다.
TIME

TIME은 시간을 저장하는 데이터 타입이며, 기본 포멧은 “시:분:초” 입니다.
YEAR

YEAR는 연도를 저장할수 있는 데이터 타입입니다.

YEAR(2)는 2자리의 연도를 저장할수 있으며 YEAR(4)는 4자리의 연도를 저장할수 있습니다.

String

reference
https://dev.mysql.com/doc/refman/5.7/en/string-types.html

CHAR & VARCHAR

TEXT
- CHAR와 VARCHAR는 대체로 크기가 작은 문자열을 저장할때 사용되며 크기가 큰 문자열을 저장할 때는 TEXT를 사용합니다.
- TEXT의 타입에 따라서 아래와 같이 크기를 가집니다.

Comment and share

DATABASE-DDL

Oct 26, 2020 in DATABASE, DDL

CREATE USE ALTER DROP

USE

test 데이터 베이스 선택
1
USE test;
현재 데이터 베이스 확인
1
SELECT DATABASE()

CREATE

데이터 베이스 생성
1
CREATE DATABASE <database_name>;
test 데이터 베이스 생성
1
CREATE DATABASE test;

테이블 생성

CREATE TABLE <table_name> (
    column_name_1 column_data_type_1 column_constraint_1,
    column_name_2 column_data_type_2 column_constraint_2,
    ...
)

제약조건이 없는 user1 테이블 생성

CREATE TABLE user1(
    user_id INT,
    name Varchar(20),
    email Varchar(30),
    age INT(3),
    rdate DATE
)

제약조건이 있는 user2 테이블 생성

CREATE TABLE user2(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name Varchar(20) NOT NULL,
    email Varchar(30) UNIQUE NOT NULL,
    age INT(3) DEFAULT '30',
    rdate TIMESTAMP
)

ALTER

Database

사용중인 데이터베이스의 인코딩 방식 확인
1
SHOW VARIABLES LIKE "character_set_database"

test 데이터 베이스의 문자열 인코딩을 utf8으로 변경

1 2	ALTER DATABASE world CHARACTER SET = ascii ALTER DATABASE world CHARACTER SET = utf8

Table

ALTER를 이용하여 Table의 컬럼을 추가하거나 삭제하거나 수정할수 있습니다.
ADD
- user2 테이블에 TEXT 데이터 타입을 갖는 tmp 컬럼을 추가
  1
  ALTER TABLE user2 ADD tmp TEXT
MODIFY
- user2 테이블에 INT 데이터 타입을 갖는 tmp 컬럼으로 수정
  1
  ALTER TABLE user2 MODIFY COLUMN tmp INT
DROP
- user2 테이블의 tmp 컬럼을 삭제
  1
  ALTER TABLE user2 DROP tmp

DROP

DATABASE

tmp 데이터 베이스 생성
1
2
CREATE DATABASE tmp;
SHOW DATABASES;
tmp 데이터 베이스 삭제
1
2
DROP DATABASE tmp;
SHOW DATABASES;

TABLE
- tmp 데이터 베이스 생성
  1
  CREATE DATABASE tmp;
- tmp 데이터 베이스 선택
  1
  USE tmp;
- tmp 테이블 생성
  1
  CREATE TABLE tmp( id INT );
- tmp 테이블 삭제
  1
  DROP TABLE tmp;

Comment and share

DATABASE DML SELECT

Oct 20, 2020 in DATABASE, DML

SELECT

정의 : 데이터를 검색할때 사용되는 문법

SELECT FROM

기본 포멧

SELECT <column_name_1>, <column_name_2>, ... FROM <table_name>
전체 컬럼 데이터 조회
1
2
sql> SELECT *
FROM world.country

code, name 세개의 컬럼 데이터 조회
1
sql> SELECT code, name FROM world.country
데이터 베이스 선택 : FROM 절에 world. 을 사용할 필요가 없다.
1
2
sql> USE world;
sql> SELECT * FROM country

alias : 컬럼의 이름을 변경할수 있습니다.

1	sql> SELECT code as country_code, name as country_name FROM country

데이터 베이스, 테이블, 컬럼 리스트 확인

1
2
3

sql> SHOW DATABASES;
sql> SHOW TABLES;
sql> DESC city;

WHERE

특정 조건을 주어 데이터를 검색하는데 사용되는 문법

비교연산 - 인구가 1억이 넘는 국가를 출력
1
SELECT * FROM country WHERE Population >= 100000000

논리 연산 : AND, OR - 인구가 7000만에서 1억인 국가를 출력

1 2	SELECT * FROM country WHERE Population >= 70000000 AND Population <= 100000000

범위 연산 : BETWEEN

인구가 7000만에서 1억인 국가를 출력

1 2	SELECT * FROM country WHERE Population BETWEEN 70000000 AND 100000000

아시아와 아프리카 대륙의 국가 데이터를 출력

1 2	SELECT * FROM country WHERE Continent = "Asia" OR Continent = "Africa"

특정 조건을 포함 : IN, NOT IN
- 아시아와 아프리카 대륙의 국가 데이터를 출력
  1
  2
  SELECT * FROM country
  WHERE Continent IN ("Asia", "Africa")
- 아시아와 아프리카 대륙의 국가가 아닌 데이터를 출력
  1
  2
  SELECT * FROM country
  WHERE Continent NOT IN ("Asia", "Africa")
- 아시아와 아프리카 대륙의 국가가 아닌 데이터를 출력 (논리연산 사용)
  1
  2
  SELECT * FROM country
  WHERE Continent != "Asia" AND Continent != "Africa"
특정 문자열이 포함된 데이터 출력 : LIKE
- 정부형태에 Republic이 포함된 데이터 출력
  1
  2
  SELECT * FROM country
  WHERE GovernmentForm LIKE "%Republic%"

ORDER BY

특정 컬럼의 값으로 데이터 정렬에 사용되는 문법

오름차순 인구순으로 국가의 리스트를 출력 (ASC는 생략이 가능)
1
SELECT * FROM country ORDER BY population ASC
내림차수 인구순으로 국가의 리스트를 출력
1
SELECT * FROM country ORDER BY population DESC
국가 코드를 알파벳 순으로 정렬하고 같은 국가 코드를 가지면 인구순으로 내림차순으로 정렬
1
SELECT * FROM city ORDER BY CountryCode ASC, Population DESC

LIMIT

LIMIT은 조회하는 데이터의 수를 제한할수 있습니다.

인구가 많은 상위 5개 국가 데이터를 출력

1	SELECT * FROM country ORDER BY population DESC LIMIT 5

인구가 많은 상위 6위 ~ 8위의 3개 국가 데이터를 출력
1
SELECT * FROM country ORDER BY population DESC LIMIT 5, 3

GRUOP BY, HAVING

GROUP BY는 여러개의 동일한 데이터를 가지는 특정 컬럼을 합쳐주는 역할을 하는 명령입니다.

SQL에는 아래와 같은 그룹함수가 있습니다.
COUNT, MAX, MIN, AVG, VAR_SAMP, STDDEV

COUNT
- city 테이블의 CountryCode를 묶고 각 코드마다 몇개의 데이터가 있는지 확인
  1
  2
  sql> SELECT CountryCode, COUNT(CountryCode) FROM city
  GROUP BY CountryCode
- countrylanguage 테이블에서 전체 언어가 몇개 있는지 구하시오.
  - DISTINCT 중복을 제거해주는 문법
    1
    2
    sql > SELECT COUNT(DISTINCT(Language)) as language_count
    FROM countrylanguage

MAX

대륙별 인구수와 GNP 최대 값을 조회

        sql> SELECT continent, MAX(Population) as Population, MAX(GNP) as GNP
        FROM country GROUP BY continent
        ```    
- MIN
    - 대륙별 인구수와 GNP 최소 값을 조회 (GNP와 인구수가 0이 아닌 데이터 중에서)
        ``` 
        sql> SELECT continent, MIN(Population) as Population, MIN(GNP) as GNP FROM country
        WHERE GNP != 0 AND Population != 0 GROUP BY continent

SUM

대륙별 총 인구수와 총 GNP

1
2
3

sql> SELECT continent, SUM(Population) as Population, SUM(GNP) as GNP
FROM country
WHERE GNP != 0 AND Population != 0 GROUP BY continent

AVG

대륙별 평균 인구수와 평균 GNP 결과를 인구수로 내림차순 정렬

sql> SELECT continent, AVG(Population) as Population, AVG(GNP) as GNP
FROM country
WHERE GNP != 0 AND Population != 0 GROUP BY continent
ORDER BY Population DESC

HAVING

GROUP BY에서 반환되는 결과에 조건을 줄수 있습니다.

대륙별 전체인구를 구하고 5억이상인 대륙만 조회

1
2
3

sql> SELECT continent, SUM(Population) as Population FROM country
GROUP BY continent
HAVING Population > 500000000

대륙별 평균 인구수, 평균 GNP, 1인당 GNP한 결과를 1인당 GNP가 0.01 이상인 데이터를 조회하고 1인당 GNP를 내림차순으로 정렬

sql> SELECT continent, AVG(Population) as Population, AVG(GNP) as GNP, AVG(GNP) / AVG(Population) * 1000 as AVG
FROM country
WHERE GNP != 0 AND Population != 0 GROUP BY continent
HAVING AVG > 0.01
ORDER BY AVG DESC

WITH ROLLUP

고객과 스탭별 매출과 고객별 매출의 총합을 출력

1 2	sql> SELECT customer_id, staff_id, SUM(amount) as amount FROM payment GROUP BY customer_id, staff_id WITH ROLLUP

Comment and share

Connection Pool이란?

DB에 접근하는 단계

Connection이 부족하면?

왜 사용할까?

Commons DBCP로 Connection Pool 이해하기

Commons DBCP 속성 설정

커넥션의 개수

커넥션 풀의 저장 구조

커넥션 개수 관련 속성

커넥션을 얻기 전 대기 시간

TPS(transaction per seconds)

TPS와 커넥션 개수와의 관계

적절한 maxWait 값은?

커넥션의 검사와 정리

유효성 검사쿼리의 설정

Evictor 스레드와 관련된 속성

reference

읽어보면 좋은 것들

VIEW

Explain

Syntax

Example

INDEX

Explain

Syntax

Example

TRIGGER

Syntax

Example

Sub Query

Example

SUB QUERY 사용시 JOIN TABLE 크기를 줄일 수 있다

JOIN

MAKE TEST TABLE & DATA

INNER JOIN

LEFT JOIN

RIGHT JOIN

JOIN과 DISDINCT의 사용

테이블 세개 조인하기

UNION

Functions

조건문 IF, IFNULL, CASE

INSERT

UPDATE SET

DELETE, DROP, TRUNCATE

Constraint : 제약조건

DATA TYPE

Numberic

Date & Time

String

CREATE USE ALTER DROP

USE

CREATE

ALTER

Database

Table

DROP

SELECT

정의 : 데이터를 검색할때 사용되는 문법

SELECT FROM

WHERE

ORDER BY

LIMIT

GRUOP BY, HAVING

Yechan Kim

`SUB QUERY 사용시 JOIN TABLE 크기를 줄일 수 있다`