[23.10.18] 52일차
<<진도>>
[DB] MySQL
- 사용자 user 추가, 삭제 (localhost, ‘%’)
- grant / revoke
- 관계설정
- index
<<오늘의 팁>>
- DBMS에서 사용자USER는 일반이용자가 아닌 DB 이용권한 사용자를 말한다.
- ‘root’는 보안 관련 문제로 ‘외부접근’을 허용하지 않는다. (기본적으로 localhost로)
- ip ‘127.0.0.1’는 해당 사용중인 pc를 뜻한다 (= localhost)
- it 관련직종 면접관들 중엔 보안개념에 대한 이야기를 하는 경우도 있다. (개인정보관리, 브라우저 로그아웃, 기록삭제 등)
- 본인 pc에서 CMD로 DB접속할 경우 자동으로 본인 ip 입력됨
- CMD 주소표시줄에 실행 보여줌
- grid 형태가 아닌 Form Editor로 로우 하나를 하나로 보는 방식도 있다.
* grant_revoke
‘rey’ select 권한 부여
‘rey’ 사용자의 copy_departments 테이블 접근
select 조회가능
but, 수정은 불가
- ‘rey’ update 권한 부여
조회select 권한 가진 ‘rey’가 새 사용자‘park’에게 권한주기
-> MySQL은 불가.
- [권한 회수]
- 모든권한 상실
* user_password
DB 내 패스워드는 암호화가 필수 (과거에는 암호가 DB에 그대로 다 보였다. 보안위험)
- 암호변경
‘1111 -> 0000 pw 변경완료
but, instance 개체가 생성되는 create와 insert는 엄연히 다르다.
* grant_revoke_quiz
[관계설정] 참조
기본키와 외래키가 묶여있으면
제약이 따른다 (update, delete 등)
## [참조설정] #####################################################
## ON DELETE : 참조되는 테이블의 값(기본키)이 삭제될 경우 동작 구문
## ON UPDATE : 참조되는 테이블의 값(기본키)이 수정될 경우 동작 구문
## CASCADE : 참조되는 테이블의 데이터를 삭제하거나 수정하면,
참조하는 테이블에서 삭제,수정이 같이 이뤄짐.
## SET NULL: 참조되는 테이블의 데이터를 삭제하거나 수정하면,
참조하는 테이블의 데이터는 NULL로 설정됨.
## NO ACTION [기본 설정]
: 참조되는 테이블의 데이터를 삭제하거나 수정하면,
참조하는 테이블의 데이터는 변경되지 않음.
## RESTRICT: MySQL에서는 NO ACTION과 같다
: 참조<하>는 테이블의 데이터가 존재하면,
참조되는 테이블의 데이터를 삭제, 수정할 수 없음.
####################################################################
id가 PK – FK 로 참조
- [참조설정]하여 테이블 삭제 후 재생성
[cascade] 연쇄적으로 함께 처리 – 자동으로 함께 처리하므로
update에 주로 사용
[set null] 참조되는 데이터 삭제 시 참조하는 데이터는 NULL처리
(회원이 탈퇴해도 개인 정보만 삭제해서 비우고, 정확한 수치 데이터등에 오차 나지않기위해 사용 delete에 주로 사용)
이렇게 부모가 없어진 row를 고아row라고 하기도한다.
[no action] 부모에 수정 삭제해도 영향 없음
MySQL의 기본 설정 변경 필요 ( SET FOREIGN_KEY_CHECKS=0 )해제해야
NO ACTION이 기능
변화없음.
[RESTRICT] - MySQL의 기본설정이 있기 때문에 의미가없다. 설정을 풀면 그냥 NO ACTION과 같은 효과가 된다
** [INDEX] <> table과 또 다른 개체
## KEY로 설정이된 column은 DBMS 안에 index라는 개체가 따로 만들어진다.
## KEY로 설정된 column에 row데이터가 들어오면 반드시 index에 들어오고,
그 데이터는 자동으로 기본 오름차순 정렬을 하게된다.
## 테이블에 데이터가 입력되면, 무조건 마지막 행으로 들어오는데,
index는 기존 index 값들을 모두 비교하고 순차적으로 정렬이 된다.
## index가 있으면, 일반적으로 검색속도가 향상이 된다.
(옵티마이저가 순서있는 index로 찾음 => "인덱스를 탄다"라고 표현)
## 옵티마이저가 index가 있으면 실행계획을 변경한다.
## 옵티마이저가 세운 (최적화) 실행계획을 DBA담당자가 쿼리를 체크한다.
<Execution Plan>
[실행계획]
select * from employees;
** Full Table Scan : 전체 row를 모두 훑음
Query cost가 높을수록 복잡한 쿼리
[실행계획]
select * from employees
where emp_no = 11111;
단일 로우 cost 1
PRIMARY : 기본키의 index를 쓴 것.
마우스 오버 시 추가 정보
[실행계획]
select * from employees
where birth_date = '1953-09-02';
= Full Table Scan (birth_date에는 index가 없음 <= 옵티마이저가 인덱스 사용불가)
결국 조건검사를 하기 위해서는 전체 Full Table Scan이 필요함
<< index가 있다면 >>
옵티마이저는 인덱스를 계획에 포함시키고, 범위질의를 통해 오름차순으로 정렬되어있으므로 전체를 스캔할 필요가 없어진다.
[실행계획] birth_date에 인덱스를 만들어주기 (인덱스 설정)
기본적으로 index는 중복을 허용 안하지만, birth_date 데이터가 중복된다.
기존에 없던 index 개체를 만들어 주는것이므로 alter / create 모두 가능
unique 한 index는 아님.
<< 이제 옵티마이저가 index를 타고 실행계획을 세웠다>>
기본키 PRIMARY 가 아닌,
직접 설정해준 idx_emp_birth_date 인덱스를 타고 실행
cost 22.05
[인덱스 삭제]
