su3inni
commented
10 months ago 3. SQL
3-1. SQL 종류와 주의사항
DML : Data Manipulation Language
-
select, insert , update, delete
- select 쿼리 사용시 조회하고자 하는 컬럼만 명시하며
- from 절 3 depth 이상 들어가지 않게 하고
- select 절에 subquery 가 들어가지 않도록 한다.
- select 쿼리 처리 순서 FROM => WHERE => GROUP BY => SELECT => ORDER BY => LIMIT
- insert 쿼리 사용시 대량의 insert 의 경우 주기적으로 commit하기
- lock으로 인한 서비스 이슈가 발생할 수 있기 때문
- insert into table select ''' from table 구분에서는 select 성능을 확인 > 양쪽 테이블에 lock이 걸리기 때문에 select 성능 먼저 확인하기
- replace는 데이터를 삭제 후 insert 하는 방식이다.
- update 쿼리 사용시 where 절이 있는지 확인
- transaction으로 묶어서 rollback이 가능하도록 수행하기
- DML작업은 DBA가 지원하지 않음
- delete 쿼리 수행 전 많이 확인하기
- transaction으로 묶어서 rollback이 가능하도록 수행하기
- 실제 디스크 공간을 반납하지 않으며 auto_increment도 유지
DDL : Data Definition Language
- select 쿼리 사용시 조회하고자 하는 컬럼만 명시하며
- create, alter, drop, truncate
delete 와 truncate 차이 -delete from table은 실제 디스크 공간을 반납하지 않고 auto_increment 값도 유지하고 있으나 -truncate table은 drop&create 하는 것과 같으므로 디스크 공간을 반납하며 auto_increment 값도 초기화한다.
DCL : Data Control Language
- grant, revoke
Transaction Control
- commit, rollback
3-2. Join
UNION은 행을 결합 , JOIN은 열을 결합
- Inner Join : 두개 이상의 테이블의 공통 값 도출 ( 내부 결합 )
- Outer Join : left / right 기준 테이블에 맞는 값으로 결합
- Cross Join : 두 테이블의 모든 가능한 조합으로 결합
Inner Join != Equi Join Inner Join은 값의 허용을 의미함 : 동등 조건이 아니어도 됨 Equi Join은 조인 조건
명시 방법
- Ansi 표준 > 가독성
- Where 절에 명시
- Join 조건을 직관적으로 보기 힘드므로 Ansi 표준으로 작성하기
Nested Loop Join
-
두 개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 row를 결합하는 방식
-
MySQL 은 Nested Loop Join 방식으로 Join 수행
-
순차적으로 처리된다.
- 먼저 액세스되는 테이블의 처리 범위에 의해 처리량이 결정된다.
- 모든 인덱스가 사용되는 것이 아니며 연결 방향에 따라 사용되는 인덱스가 달라질 수 있다.
- index 유무에 따라 액세스 방향 및 수행 속도에 많은 차이 발생
-
Nested Loop Join 사용시 주의사항
- 어떤 테이블이 먼저 액세스 되느냐에 따라 수행 속도에 많은 영향을 미친다.
- 선행 테이블의 처리 범위가 많거나 , 연결 테이블의 랜덤 액세스 양이 아주 많다면 성능 저하됨
- 후행 테이블의 경우 Join 조건에 대한 인덱스가 반드시 필요하다.
3-3. 실행계획
쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어있는지 통계 정보를 참조하여 최적의 실행 계획을 수립하는 작업이 필요하다.
옵티마이저
- 테이블의 인덱스의 크기 및 인덱스 칼럼에 대한 cardinality(unique한 값의 개수), 데이터 분포의 통계 정보를 활용하여 처리 비용 계산
- 비용 = I/O 비용(random block seek 횟수)
- 카디널리티, 선택도, 분포도 확인
- 선택도와 분포도는 쿼리 성능 최적화에 큰 영향
HINT
straight_join명령어로 Join 순서 정렬/고정하는 힌트use index명령어로 어떤 인덱스를 사용할지 명시하는 힌트
UNION / UNION ALL
- 중복 허용 여부 차이 > Union : 중복 제거 , Union all : 중복 허용
3-4. Key & Index
KEY
- 테이블의 레코드를 식별할 때 사용하는 논리적인 구조
- Primary Key , Unique Key, Foreign Key
- Primary Key 기본키
- 테이블 내에서 각 행을 고유하게 식별하는 열로
- 중복을 허용하지 않으며
- 여러 컬럼이 그룹화되어 기본키가 될 수 있다.
- 기본키는 Null 값이 될 수 없다.
- Foreign Key 외래키
- 다른 테이블의 기본키를 참조하는 열로
- 테이블 간의 관계를 구축할 때 사용된다.
- Unique Key
- Null 값이 허용되며
- 여러 컬럼에 적용할 수있다.
- 기본키와 중복 방지용이라는 점에서 공통점을 가지긴한다.
INDEX
-
데이터 처리 속도를 최적화할 때 사용하는 물리적인 구조
- B-Tree 구조
-
튜닝에 가장 중요한 포인트 > Cost가 가장 적게 드는 방법
- 적절한 인덱스 선택
- 인덱스 변경
- 인덱스 생성
- 인덱스 삭제
- 어떤 컬럼으로 Index 를 구성해야하는지
- 조건절에 있는 컬럼 > Full Scan하지 않도록
간단한 쿼리라도 Full Scan 여부를 확인하고 Index 설정 여부도 확인한다. - Cardinality가 높은 컬럼 > Cardinality가 높은 순서에서 낮은 순으로
-
Index를 활용하지 못하는 경우
- where절에 명시된 컬럼명에 함수가 씌여진 경우
- Like 검색시 %로 조회조건을 입력한 경우
- 부정 연산자를 사용하는 경우
- 암시적인 형변환이 일어나는 경우 > 조건 입력시 데이터 타입 제대로 확인하기
- NULL, NOT NULL에 대한 경우
3-5. NULL
없거나 알려지지 않은 값을 의미하는 것으로 0이나 빈 문자열, 공백 문자열이 아니다. 꼭 IS NULL / IS NOT NULL 로 검색해야한다.
3-6. Character Set
- MySQL만의 특징으로, varchar(30)에서 괄호 안 숫자는 byte가 아니라 글자 수이다. 즉, 최대 120byte인 것이다.
- row는 최대 65535 bytes까지 지원하므로 컬럼 사이즈를 고려하여 컬럼 정의한다.
- Character Set 변경시 주의 사항 ( UTF8 > UTF8MB4 )
- 업데이트 하는 컬럼과 Join이 있는지 확인
- 메모리 여유공간 확인
- 테이블의 변경은 테이블 전체에 Lock이 걸리므로 영향 파악
Character Set 형 변환 후 Join 되는 컬럼간 Character Set 일치가 되는지 꼭 확인하기. 강제 형변환 Slow Query가 대량으로 발생하면 서비스 장애로 이어질 수 있음
0. Database ?
데이터를 효율적으로 접근할 수 있도록 가공 및 저장한 것
1. DBMS
데이터베이스를 관리하기 위한 시스템 ex. ORACLE, MySQL, MSSQL ..
DBMS 유형
2. 데이터 모델링
2-1. ERD
서비스 요구 분석사항을 그림으로 관계를 표현한 것
정규화 / 반정규화