N+1 Selects 문제


N+1 문제란 ?

조회를 위해 select 쿼리(1번)를 날렸다가 관련 컬럼을 얻기 위해 N번의 셀렉트(N번)를 하게 되는 상황.


같은 결과를 낼지라도, 그 쿼리를 수행하는데에 있어 몇 번의 쿼리를 해야하는지에 따라 성능이 달라진다.


마이바티스의 association에서 Nested Select, Nested Results를 예로 들 수 있다.


Nested Select 는 결과에서 다시 한번 참조를 하기 위해 다음 Select구문을 실행하는 것을 말하고, Nested Results는 조인된 결과물에 대해 반복적으로 사용하여 결과를 맵핑하는 방법을 말한다. 


예시를 보도록 하겠다. (참고 : http://www.mybatis.org/mybatis-3/ko/sqlmap-xml.html#insert_update_and_delete)



위에서 만약 블로그와 저자와의 관계가 (1:N) 상황이라면 N+1의 셀렉트가 발생하게 된다. 각각의 author_id를 위해 셀렉트를 실행한다



하지만 위와 같이 조인된 결과에 대하여 맵핑을 하게 되면 위와 같이 N+1번의 호출이 일어나지 않게 된다.



MyBatis Insert not working



insert 구문을 계속 실행시켰지만 데이터베이스에 적용도 안되고 오류도 안나기에 무엇이 잘못된지 한참 찾아다녔다


결론적으로 SqlSession을 commit()시켜주니 적용이 되었는데, 당연히 JDBC처럼 auto commit이 되어있을 거란 생각을 했었다..


궁금해서 코드를 따라가보니 일단 SqlSessionFactory는 DefaultSqlSessionFactory였고,



이 팩토리의 opensession(boolean autoCommit)부분을 쭈욱 따라가보니 DefaultSqlSession에서 autoCommit이 설정안되있다는 것을 알게되었다. 

 


autoCommit을 설정하고 싶다면 다음과 같이 sqlSession을 생성할 때 true로 설정해야한다




ACID



Atomicity : 트랜잭션과 관련된 작업들이 부분적으로 실행되다가 중단되지 않는 것을 보장.

Consistency : 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지.

Isolation : 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것.

Durabillity : 성공적으로 수행된 트랜잭션은 영원히 반영되어야 함.


특히 격리성과 관련된 문제점들 중 대표적인 3가지가 아래에 있다.



리성 관련 문제점



(1) Dirty Read : 다른 트랜잭션이 커밋되지 않은 변경된 데이터를 읽게되고, 변경된 데이터가 롤백으로 인해 변경 전으로 돌아갔지만, 이미 다른 트랜잭션이 변경된 데이터를 읽게 된 것.


(2) Non-Repeatable Read : 트랜잭션 중간에 READ된 데이터에 대해 다른 트랜잭션이 중간에 끼어들어 값을 변경하고 커밋하였을 경우, 다시 READ했을 때 변경된 데이터가 나오게 되는 것.


(3) Phantom Read : 2번과 비슷해보이지만 추가적으로 값이 변경되는 경우가 아닌 컬럼 하나가 삭제 되거나 추가되는 수준.




Trancsaction Isolation Level



이러한 고립성(Isolation) 문제점들에 대해 데이터베이스에서는 4가지의 격리 레벨을 가지고 있다. 각각은 다음과 같다.


  • READ UNCOMMITED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

가장 위에서부터 설명하겠다.

1. READ UNCOMMITED : 두 개의 트랜잭션이 있을 때, 하나의 트랜잭션(T1)이 커밋하지 않은 변경된 데이터에 대해 다른 트랜잭션(T2)이 접근이 가능하다.이 때 T1이 롤백을 발생시킨다면 디비에 들어있는 실 정보와 T2가 방금전에 검색했던 값은 일치하지 않을 것이다.



2. READ COMMITED : 트랜잭션(T1)이 데이터를 READ하였고 UPDATE하였을 때, 다른 트랜잭션(T2)가 READ하면 T1가 변경하기 전인 데이터를 읽어온다. T1이 commit을 마쳤을 때 T2는 비로소 변경된 데이터를 읽을 수 있다.

3.REPEATABLE READ : 트랜잭션 내에서 한번 조회한 데이터를 반복해서 조회해도 같은 데이터가 조회 된다


4. SERIALIZABLE : 트랜잭션이 완료될 때까지 READ에 사용된 모든 데이터가 락이 거려 다른 트랜잭션이 수정 및 입력이 불가능하다




[출처 : https://lng1982.tistory.com/287http://feco.tistory.com/45]

오라클XE를 설치하면 부팅시 자동으로 켜져서 은근히 불편함을 느끼게 된다..


메모리도 많이 잡아먹고, 톰캣 포트랑도 엇갈려서 계속 바꿔주기도 귀찮고 ㅋ


시작 -> '서비스' 검색 -> OracleServiceXE 랑 OracleXETNSListener를 찾아서 자동에서 수동으로 변경시켜주자.



유저 액세스 제어

 

1.유저 생성(sys 계정으로 접속)

Create user demo identified by demo;

 

2.권한 부여

grant create session

    , create table

    , create sequence

    ,create view

to demo;

 

 

권한 부여를 할 때 매번 위와 같이 작성하는 것에 한계를 느낌

 

3.롤 생성

create role manager;

 

4.생성 롤에 권한 부여

grant create table, create view to manager;

 

5.테스트

create user alice identified by alice;

 

grant manager to alice;

 

6.암호 변경

Alter user demo identified by employ;

 

7.객체 권한(n1으로 접속)

grant select on employees to demo;

-- demo계정에게 자신의 employees테이블 select의 권한을 줌

 

grant update (department_name,location_id)

on  departments

to  demo,manager;

 -- demo계정과 manager(role)에게 department_idlocation_id update할 수 있는 권한 부여

 

8.권한 전달 : 시스템의 모든 유저가 권한을 받도록 설정(alice 계정)

Grant select on departments to pulbic;

 

9.sysgrant 권한 주기

grant select, insert

on   departments

to    demo

with grant option; -- 이 부분을 입력해주면 demo sys grant권한을 가지게 된다.

-- demo계정은 다음이 가능해 진다.

grant select on alice.departments to public;

-- alice departments select의 권한을 demo가 모두에게 뿌린다.

 

스키마 객체 관리

 

테이블 컬럼 삭제

alter table dept80

drop column job_id;

 

테이블 컬럼 변경

alter table dept80

modify last_name varchar2(30);

 

테이블 컬럼 추가

alter table dept80

add job_id varchar(9);

 

unused

: 데이터를 사용하지 않겠다고 설정.

: 데이터가 사라지는 것은 아님. 데이터가 엄청 많을 때 지우는 시간동안 서비스가 제대로 이루어 지지 않기 때문에 나중에 삭제하려고 unused체크

alter table dept80

set unused (last_name);

 

unused 체크한 것 삭제하기

alter table dept80

drop unused columns;

 

테이블 제약 조건 변경

ALTER TABLE emp2

MODIFY employee_id PRIMARY KEY;

 

 

테이블 제약 조건 추가

ALTER TABLE emp2

ADD CONSTRAINT emp_mgr_fk

FOREIGN KEY(manager_id)

REFERENCES emp2(employee_id);

 

on delete

: 외래키에서 상위 키가 삭제 될 때 하위 행의 행동을 지정

 

1)연쇄 삭제 : 참조하고 있는 행이 삭제되면 참조하던 행도 삭제

ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk

FOREIGN KEY (Department_id)

REFERENCES departments(department_id) ON DELETE CASCADE;

 

2)null로 만들기 : 참조하고 있는 행이 삭제되면 참조하던 행을 null로 고침

ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk

FOREIGN KEY (Department_id)

REFERENCES departments(department_id) ON DELETE SET NULL;

 

제약 조건 지연

 

1)     생성 시 제약 조건 지연(트랜잭션이 종료될 때까지 제약 조건 검사를 기다림)

ALTER TABLE dept2

ADD CONSTRAINT dept2_id_pk

PRIMARY KEY (department_id)

DEFERRABLE INITIALLY DEFERRED;

 

2)     (명령문 실행이 완료되면 제약 조건을 바로 검사)

CREATE TABLE emp_new_sal (salary NUMBER

CONSTRAINT sal_ck

CHECK (salary > 100)

DEFERRABLE INITIALLY IMMEDIATE;

 

3)     특정 제약 조건 속성 변경

SET CONSTRAINTS dept2_id_pk IMMEDIATE;

 

4)     세션의 모든 제약 조건 변경

ALTER SESSION

SET CONSTRAINTS= IMMEDIATE;

 

제약 조건 삭제

ALTER TABLE emp2

DROP CONSTRAINT emp_mgr_fk;

 

ALTER TABLE dept2

DROP PRIMARY KEY CASCADE; -- 연관된 foreign key 조건도 삭제

 

rename column

CREATE TABLE marketing (team_id NUMBER(10),

target VARCHAR2(50),

CONSTRAINT mktg_pk PRIMARY KEY(team_id));

 

 

ALTER TABLE marketing RENAME COLUMN team_id

TO id;

 

 

ALTER TABLE marketing RENAME CONSTRAINT mktg_pk

TO new_mktg_pk;

 

 

임시 테이블

CREATE GLOBAL TEMPORARY TABLE today_sales

ON COMMIT PRESERVE ROWS AS

SELECT * FROM employees;

 

select * from today_sales;

 

commit;  -- 세션이후(접속 끊었다 다시 켜기)에는 today_sales 테이블에 정보가 없어져 있을 것이다.

 

CREATE GLOBAL TEMPORARY TABLE today_sales3

ON COMMIT delete ROWS AS

SELECT last_name, salary FROM employees;

 

insert into today_sales3 values ('test',10000);

 

select * from today_sales3;

 

commit; -- 커밋 후에는 정보 삭제

 

 

External Table

: 외부 파일(csv,excel)등을 오라클에 집어넣고 싶을 때 사용

1)External Table 디렉토리 생성

--먼저 sys로부터 권한을 받는다.

grant create any directory to n1;

revoke create any directory from n1;

 

create or replace directory emp_dir

as ‘c:/dev’; -- 폴더 지정

 

 

2)csv 만들기


다음과 같이 작성 후

c:/dev(방금전 지정한 폴더) csv 형태로 넣는다.

 

 

3)External Table 생성

CREATE TABLE oldemp3 (fname char(25), lname CHAR(25), team  char(20))

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY emp_dir  -- 지정폴더

ACCESS PARAMETERS

(FIELDS TERMINATED BY ',' (fname,lname,team))--csv는 쉼표단위

LOCATION ('testdb.csv')--2에서 저장한 데이터

)

PARALLEL 5

REJECT LIMIT 200;

 

select * from oldemp3;

 

시퀀스


시퀀스 = 정수 값을 생성하는 데이터베이스 객체

 

시퀀스 특징

1)     고유 번호를 자동으로 생성 -> Primary key로 자주 사용

2)     공유할 수 있는 객체

3)     시퀀스 값이 메모리에서 캐시된 경우 엑세스 속도 향상

 

시퀀스 생성

Create sequence seq_emp

increment by 1 – 부를 때마다1씩 증가

start with 1 –시작 번호는 1

maxvalue 100 –최대 값 100

nocache nocycle;--캐시를 사용하지 않고, 사이클을 사용하지 않음

 

시퀀스 확인

Select * from user_seqeunce

 

시퀀스 적용

Insert into t_emp values(seq_emp.nextval);  --처음 실행하면 당연히 1이 들어감

Insert into t_emp values(seq_emp.nextval);  -- 2

Insert into t_emp values(seq_emp.nextval);  -- 3

 



◆뷰


= 자주 쓰이는 쿼리를 저장하고 쓴다.

 

1)     권한 받기(sys로 접속)

grant create view to n1;

 

2)     뷰 생성

create view empvu80

as select employee_id, last_name,salary

from employees

where department_id =80;

 

3)     뷰 사용

select * from empvu80;

 

4)     뷰 삭제

drop view empvu80;

 

5)     뷰 수정

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)

AS SELECT employee_id, first_name || ' '

|| last_name, salary, department_id

FROM employees

WHERE department_id = 80;

 

6)     복합 뷰

CREATE OR REPLACE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)

AS SELECT d.department_name, MIN(e.salary),

MAX(e.salary),AVG(e.salary)

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

GROUP BY d.department_name;

 

7)     동의어 생성 및 제거

create synonym d_sum for dept_sum_vu; -- 생성

 

select * from d_sum;  -- 확인

 

drop synonym d_sum; -- 동의어 제거

 

 

8)     뷰를 쓸 때 주의 사항

A.     단순 뷰는 DML을 수행할 수 있다.

-       with check option 절을 사용함으로 DML을 수행하지 못하도록 막을 수 있다.

CREATE OR REPLACE VIEW empvu20

AS SELECT *

FROM employees

WHERE department_id = 20

WITH CHECK OPTION CONSTRAINT empvu20_ck ;

 

B.      뷰에 그룹 함수, group by, distinct , rownum ,표현식으로 정의되는 열 등이 포함되어 있을 경우 뷰의 데이터를 수정,삭제,추가가 불가능

◆DDL

 

1)데이터베이스 5가지 객체

객체

설명

테이블

기본 저장 단위

하나 이상의 테이블에 있는 데이터의 논리적 부분 집합

시퀀스

숫자 값을 생성

인덱스

일부 쿼리 성능 향상

동의어

객체에 다른 이름 부여

 

2)테이블 이름 명명 규칙

-문자로 시작

-길이는 1- 30

-특수문자 _,$,# 사용가능

-동일한 유저가 소유한 다른 객채의 이름과 중복되지 않아야 함.

-예약어(sum,table등 등) 사용할 수 없습니다.

 

3)데이터 유형

varchar char 무슨 차이일까?

 

char형은 처음 지정한 크기만큼 저장을 하지만

varchar는 처음 지정한 크기보다 적은 데이터가 들어간다면 그 데이터만큼만 할당을 하는 데이터 유형이다.

 

4)제약 조건

select * from user_constraints;  -- 접속 계정의 제약 조건들 보기

 

 

not null

create table employees(

employee_id number(6),

frist_name varchar2(20),

job_id varchar(10) CONSTRAINT emp_emp_job_nn not null,

CONSTRAINT emp_emp_id_pk

PRIMARY KEY (employee_id)

);

 

unique

create table employees(

    employee_id number(6) ,

    last_name varchar2(25) not null,

    email varchar2(25) constraint emp_email_uk unique,

    primary key(employee_id)

);

 

create table t_friend(

    user_id varchar2(20),

    f_id    varchar2(20)

);

 

alter table t_friend add constraint c_friend_uk unique(user_id,f_id);

 

foreign key

create table t_emp(

    empno number(4),

    ename varchar2(30) not null,

    hp varchar2(11),

    sal number,

    deptno number(2),

    constraint t_emp_no_pk primary key(empno),

    constraint t_emp_dept_fk foreign key (deptno) references t_dept(deptno)

);

 

 

foreign key 3가지 추가 제약 조건

1)     on delete cascade : 연쇄적으로 삭제 , 상위 테이블의 행이 삭제되면 하위에 연관되어있던 테이블의 종속 행들도 삭제한다.

2)     on delete set null : null로 대체 , 상위 테이블의 행이 삭제되면 하위에 연관되어있던 테이블들의 값은 null로 대체한다.

3)     default 기본 : 갱신이나 삭제를 허용하지 않음.

 

check

create table t_friend(

    user_id varchar2(20),

    f_id    varchar2(20),

    sal number

    constraint t_friend_sal_ck check(sal > 1000)

);

 

default

create table café(

 café_num number primary key,

 café_name varchar2(20) not null,

 café_cnt number defalut 1

);

 

서브 쿼리를 이용한 테이블 생성(ctas)

CREATE TABLE dept80

AS

SELECT employee_id, last_name,

salary*12 ANNSAL,

hire_date

FROM employees

WHERE department_id = 80;

 

테이블 변경

alter table employees read only;--읽기 전용

 

alter table employees read write;--읽기 쓰기 둘 다 가능


alter table [테이블명] modify ([컬럼명] [데이터타입] [제약조건]);

 

테이블 삭제

drop table dept purge;


◆트랜잭션 :최소작업단위



트랜잭션의 시작 : 첫 번째 DML문 이후

트랜잭션의 종료 : 1) commit or rollback

       2) DDL문 실행

       3) DCL문 실행

       4) 툴 종료

       5) 시스템 문제가 생겼을 때




프로세스1

DML문(시작)--------

Update employees

set salary += salary+1000

where employee_id = 113;

-----------------------------------커밋x -> 확정 x

.

.

.

.

Commit(확정)!!

 


*확정 전까지는 하나의 트랜잭션



 

프로세스1

프로세스2

Update employees

set salary += salary+1000

where employee_id = 113;

 

 

 

 

 

 

 

 

 

Update employees

set salary += salary+1000

where employee_id = 50;

대기

데드락

 

 

 

Update employees

set salary += salary+1000

where employee_id = 50;

 

Update employees

set salary += salary+1000

where employee_id = 113;

대기

 

 

 

 

 

데드락

 *양방향에서 서로를 기다리게 되면서 무한대기에 빠진다.

 

-명시적 트랜잭션 제어문


COMMIT = 모든 데이터 변경 사항을 영구적으로 적용

SAVEPOINT = 현재 트랜잭션 안에 저장점을 만든다.

ROLLBACK = 커밋 전의 데이터 변경사항을 폐기

ROLLBACK TO SAVEPOINT = 지정한 저장점으로 롤백

 

--Lorentz : 4200

update tcltest set salary = salary * 1.5 where last_name = 'Lorentz';

 

select * from tcltest;

 

--Lorentz = 6300

 

savepoint s1;

 

update tcltest set salary = salary * 2 where last_name = 'Lorentz';

select * from tcltest;

 

--Lorentz : 12600

 

savepoint s2;

 

rollback to s1;

 

rollback;


'Programming > 데이터베이스' 카테고리의 다른 글

Oracle 문법 정리 - 시퀀스, 뷰  (0) 2018.01.09
Oracle 문법 정리 - DDL  (0) 2018.01.08
Oracle 문법 정리 - 트랜잭션  (0) 2018.01.05
Oracle 문법 정리 - DML  (0) 2018.01.04
Oracle 문법 정리 - 집합 연산자  (0) 2018.01.03
Oracle 문법 정리 - join  (0) 2018.01.02

◆DML

sql문을 나누자면 DML ,DDL ,DCL ,TCL 등이 있습니다. 글에서 설명할 것은 그 중에서도

데이터를 직접적으로 조작하는 데이터 조작 DML을 봅니다.



1.데이터 삽입(insert)

 

a)첫 번째 방법  *모든 컬럼을 다 써줘야 한다속성이 3개면 3개를 써야 하고 속성이 5개면 5

insert into friends2 values(1,'신해철','010-1111-1111');



b)두 번째 방법  *명시해준 것만 넣을 수 있다. (*not null은 무조건 써야 하지만…)

insert into friends2(no,name,phone) values(1,'신해철','010-1111-1111');


 

c)다른 테이블로 부터 한번에 삽입

insert into sales_reps(id,name,salary,commission_pct)

select employee_id,last_name,salary,commission_pct

from employees

where job_id like '%REP%';

 

2. 데이터 수정(update)

Update friends2 set name=’모종닷컴’ where no = 1;

 

3. 데이터 삭제(delete)

delete from departments where department_id = 40;

 

4. 테이블 삭제(drop)

drop table copy_emp purge;

*purge는 바로삭제를 의미함. 휴지통에 남겨두는 것이 아닌


5. 완전 삭제(truncate) : 롤백으로도 돌리지 못하는

truncate table rno;

 

delete   VS    truncate


delete는 쿼리를 수행 후 커밋 하지 않았다면 롤백으로도 돌릴 수 있으나,

truncate는 롤백으로 돌리지 못한다.



'Programming > 데이터베이스' 카테고리의 다른 글

Oracle 문법 정리 - DDL  (0) 2018.01.08
Oracle 문법 정리 - 트랜잭션  (0) 2018.01.05
Oracle 문법 정리 - DML  (0) 2018.01.04
Oracle 문법 정리 - 집합 연산자  (0) 2018.01.03
Oracle 문법 정리 - join  (0) 2018.01.02
Oracle 문법 정리 - 서브쿼리  (0) 2018.01.02

+ Recent posts