[TIL] 240226

Today

• SQL 프로그래밍

-- 2024-02-26

-- INDEX(색인) : 책의 목차와같은 부분을 데이터의 INDEX(색인)이라고 합니다.

/*
[색인 작동원리]
- 색인 없이 검색 시 전체 데이터를 하나하나 찾는 개념
- 색인을 설정하면 특정 열 값을 특정 기준으로 정렬하여 정렬된 데이터에서 값을 찾는 것입니다. (색인 방식마다 조금씩 정렬방법은 다름)
- 기본적으로 B-Tree 자료 구조를 이용합니다.

[색인 장 단점]
- 장점  1) 검색속도가 빨라질 수 있음
        2) 쿼리문 실행에 부하가 줄어 시스템 전체의 성능을 향상시킬 수 있음
- 단점  1) 색인을 저장할 공간이 필요해 전체적으로 DB 용량이 증가할 수 있음
        2) 대량의 데이터를 담고 있는 경우 색인을 생성하는데 시간이 오래걸릴 수 있음
        3) 데이터 변경이 많으면, 색인 변경도 많아지기 때문에 오히려 성능 저하 원인이 됨
        
[색인의 종류]
- 클러스터형 색인 : 기본키(PK)를 이용하는 색인 종류로 하나의 테이블에는 하나의 클러스터형 색인만 생성할 수 있습니다.
  ex ) 기본키(PK)
- 비클러스터형 색인 : UK, NON UK

- UK : 색인 값들의 중복이 허용안됨
- NON UK : 색인 값들의 중복이 허용 됨.

선생님 TIP : 주기적으로 체크필요
- 데이터가 많이 늘었거나
- 어느정도 시간이 지났거나
할 때 주기적으로 DB 성능 체크가 필요합니다.

PK > UK > PK&UK아닌 KEY > KEY아닌 것  으잉?
*/

USE maria;

-- 색인정보 확인하기
SHOW INDEX FROM tbl_member;
SHOW INDEX FROM tbl_orderinfo;
SHOW INDEX FROM tbl_orderdetail;

/*
Cardinality : 중복도를 말하며, 높으면 중복도가 낮고, 낮으면 중복도가 높다
*/

-- 인덱스의 상태 정보
SHOW TABLE STATUS LIKE 'tbl_order%';

-- 색인 생성
/*
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명);
*/

# CREATE 문으로 추가
CREATE INDEX IDX_tbl_orderInfo_memberId
ON tbl_orderinfo (memberId);


# ALTER 문으로 추가
ALTER TABLE tbl_orderinfo        # 드랍하고 새로 추가하기 위해 일단 드랍시킴
DROP INDEX IDX_tbl_orderInfo_memberId;

ALTER TABLE tbl_orderinfo
ADD INDEX IDX_tbl_orderInfo_memberId (memberId);
SHOW INDEX FROM tbl_orderinfo;

-- 실습 : tbl_member 테이블에 IDX_tbl_member_name 인덱스를 추가하시오.
ALTER TABLE tbl_member
ADD INDEX IDX_tbl_member_name (NAME);
SHOW INDEX FROM tbl_member;

ANALYZE TABLE tbl_member; # 뭔가 최적화 시킴...

-- 쿼리 실행 계획 분석
USE employees;
EXPLAIN SELECT * FROM salaries;   # 탈 수 있는 인덱스가 아무것도 없어서 인덱스 안타고 전체 검색 함.
EXPLAIN SELECT * FROM salaries LIMIT 10;   # LIMIT 줘봤자 다 데이터 갖고온다음 잘라서 갖고오는 거라 위에 꺼와 속도 별반 차이 없음 (인덱스 탈수 있는 것도 없음)
EXPLAIN SELECT * FROM salaries WHERE emp_no = '10017';   # WHERE로 프라이머리 키 인 emp_no로 찾게금하기 떄문에 속도가 확 줄음


-- 암호화 사용해보기

USE maria;

SELECT * FROM tbl_member;

-- SHA2(값, 사용할 비트) : 단방향 암호화
/*
[암호화]
SHA2(값, 사용할 비트);

[복호화] : 단방향이라 불가
- 해당 암호화하 그대로 찾으면 됨.
- 따라서 원래 비밀번호를 사용자가 아예 까먹으면 찾을 수가 없음 => 임시 비밀번호 발급 후 비밀번호 변경하게 되는 이유
*/
SELECT SHA2('1234', 256) AS pwd;

# 컬럼 추가해서 작업해보기
ALTER TABLE tbl_member
ADD COLUMN pwd_change_date
DATETIME NULL;

# 값 바꿔보기
UPDATE tbl_member 
SET pwd = SHA2(pwd, 256)
WHERE memberId = 'gee1'; # 트리거 만들어 놓은 것 때문에 조회가 안됨

# 급하게 트리거 없애기
SHOW TRIGGERS;
DROP TRIGGER TRG_tbl_orderDetail_update;

/*
암호화 뿐만아니라, 데이터를 변환할 떄는 해당 컬럼의 길이를 늘 확인하고 바꿔야함.
짧은 길이에 긴 데이터를 넣으려하면 바로 에러 뜨는데,
긴 길이 컬럼에 긴 데이터 넣고 해당 컬럼의 길이를 강제로 바꿀수있는 경우가 있음 이럴 때 문제가 될 수 있습니다.
*/

# 로그인 로직 예시보기
BEGIN NOT ATOMIC
SELECT pwd
INTO @pwd
FROM tbl_member WHERE memberId = 'gee1';
IF @pwd = SHA2('1234', 256) THEN
	SELECT 'TRUE';
ELSE
	SELECT 'FALSE';
END IF;
END;

-- 양방향 암호화
/*
AES_ENCRYPT(암호할 값, 대칭키 문자열) : 암호화 할 때
AES_DECRYPT(암호화된 값, 대칭키 문자열) : 복호화 할 때

◆ 암호화 하면, 문자셋이 깨지기 때문에 HEX()함수로 헥사코드로 변환하여 관리하고는 합니다.
   복호화할 떄는 UNHEX()로 헥사코드에서 원래 문자셋으로 변환하여 복호화합니다.
◆ 대칭키는 개발팀에서 관리하고 주기적으로 변경해야합니다.
*/

-- 백업 과 복구
/* 
◆ GUI 환경에서 저장할 테이블 선택 후 'SQL로 내보내기'
◆ 복구할 때는 저장해놓은 파일 '파일' - 'SQL 파일 실행'
*/

• 프로젝트 관련 주제 선정을 위해 발표 (좋다고 생각하는 교육 사이트!)

Review

• 사람마다 좋다고 생각한 기준이 다 다르다고 느낌
  나는 후보 중 OKKY (교육 사이트라 할 수 있나..?), K-MOOC이라는 사이트가 맘에 듬

TO DO

• Java 최종 정리 (~3/2일 까지로 연장)
• Maria DB 정리 수업진도 따라잡기
  정리하고 있는 부분 : 346p / 진도 : 완료! ㅎㅎ