DBMS 공부 4 - SQL의 이해와 종류 (DCL , INDEX)

1. DCL(Data Control Language)

• 권한 및 역할을 설정하는 언어 
• 특정 테이블에 대한 CRUD 권한을 설정한다.  
• 권한부여  /  권한 회수로 나뉜다 .
• 주로 DBA가 설정한다.

- 유저생성 , 삭제 

사용자 sampleUser를 새로 추가 / 삭제하라 

use mysql;   // mysql 설정 관련 데이터베이스
select user , host from user;    //  mysql user 관련 테이블
create user '유저명'  @  ' 호스트명(localhost) ' identified by '비밀번호';    // user 추가
drop user 사용자명@호스트   // 유저 삭제
flush privileges,    //반영하기

 

-권한 부여 , 삭제 

sampleUser(localhost)의 접속을 허용하라
localhost의 sampleuser에게 world DB의 검색 /추가 권한을 부여하라
localhost의 sampleuser에게 world DB의 city테이블의 도시명의 업데이트 권한을 부여하라
localhost의 sampleuser의 모든 권한을 삭제하라 .

show grants for 유저명  ;  // 해당 유저에 관한 권한을 보여준다.
grant all privileges on world.* to 'ugo@localhost' // world DB의 모든 테이블에 대해 접속을 허용한다.
grant all privileges on *.* to 'ugo@localhost' // 모든 DB의 모든 테이블에 대해 권한을 부여한다. 
grant select , insert world.* to 'ugo'@'localhost' // world DB 의 모든 테이블에 관해 select,  insert 만 가능하도록 한다.
revoke all privileges  on *.* from 'ugo2'@'localhost'; // 유저의 모든 권한을 삭제한다

 

2  . INDEX

• 검색을 빠르게 하기 위한 자료구조 (주로 B트리계열)
• 추가/삭제보다 검색이 많은 서비스에 사용하면 좋다(추가/삭제시 인덱스를 다시설정하게됨)
• 기본키는 자동으로 인덱스 설정된다.
• 인덱스 on/off를 통해 검색속도 차이 체감
• 조인시에도 영향을 줌

- 인덱스 추가/ 삭제

• create index 인덱스명 on 테이블명(컬럼명)
• create index 인덱스명 on 테이블명(컬럼명1,컬럼명2)
• create unique index 인덱스명 on 테이블명(컬럼명)  //
• alter table 테이블명 drop index 인덱스명

 

- 인덱스 사용 유무에 따른 쿼리 수행시간 비교

(https://github.com/datacharmer/test_db  <- 링크의 샘플데이터를 활용하여 테스트했습니다.)

 

* 왼쪽 파란 숫자대로 설명

1. 총 레코드수가 2,844,047인  테이블에서  인덱스가 없이   salary 가 158220인  레코드를 찾을때 0.54초의 시간이 걸린다.

2. salary 컬럼에 인덱스를 추가하였고 1.55초가걸렸다.

3. 인덱스가 추가된 후 1번과 같은 쿼리를 다시 날렸을때 시간은 0초가 걸린다. 

 

* 주의할 점

• 2번에서 인덱스가 추가될때 1.55초가 걸리는 걸 알 수 있다. 만약 salary 컬럼에 수정이 일어나게 될 경우 인덱스도 같이 수정되기 때문에 업데이트 시간이 늘어날 수 있다 . 
• 실제 성능차이가 눈에 띄게 차이나려면 수 십만 개이상의 레코드가 필요하다 
• 데이터가 수백- 수천 개의 경우 차이가 미미하다 .
• 데이터가 100만 개가 넘어가면 인덱스 유/무가 커다란 차이가 난다.

 

- 인덱스 사용 유무에 따른 조인 쿼리 수행시간 비교

인덱스가 없는 상태

salary 컬럼에 인덱스가 달린 상태

조인 쿼리에서도 인덱스 유무에 따라 수행속도가 차이가 남을 알 수 있다.  

 

INDEX 정리

• 데이터 검색을  빠르게 하기 위한 보조 자료구조이다. 
• RDBMS의 핵싱 기능이다 . 
• 데이터 추가 / 수정 / 삭제시 미리 정렬하고 검색에 필요한 자료구조 생성이 필요하다.
• 대량의 데이터 일수록  인덱스 유무에 따른 성능 차이가 크다 .
• 인덱스 설정기준은 보통 쿼리시간이 0.5~1초 이상 걸리는 경우에 사용한다. 
• 데이터 추가 / 수정 / 삭제의 성능이 중요한 경우에 인덱스를 사용하지 않을 것을 고려해야한다.