2023.01.02 TIL(데이터베이스 CS Q&A 정리)

💭 인덱스(Index)란?

https://mangkyu.tistory.com/96

• 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
• 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.
• 데이터베이스에서 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕는다.

• 인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다.
• 만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.

인덱스(index)의 관리

• INSERT: 새로운 데이터에 대한 인덱스를 추가함
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

인덱스(index)의 장점과 단점

• 장점
  • 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
  • 전반적인 시스템의 부하를 줄일 수 있다.
• 단점
  • 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
  • 인덱스를 관리하기 위해 추가 작업이 필요하다.
  • 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.

만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다. 앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 했다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.

인덱스(index)를 사용하면 좋은 경우

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼
• 기타 등등

인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요하다. 그러므로 사용되지 않는 인덱스는 바로 제거를 해주어야 한다.

인덱스(index)의 동작 원리

https://ssunws.tistory.com/45

• 데이터 파일의 블록이 10만개가 있다고 가정할때 select문 실행시
• server process가 구문분석과정을 마친후 database buffer cache에 조건에 부합하는 데이터가 있는지 확인
• 해당 정보가 buffer cache에 없다면 디스크 파일에서 조건에 부합하는 블럭을 찾아 database buffer cache에 가져온 뒤 사용자에게 보여줌.
  • index가 없으면 10만개 전부 database buffer cache로 복사한뒤 풀스캔으로 찾게됨
  • index가 있으면 where절의 조건의 컬럼이 index의 키로 생성되어있는지 확인한 뒤, 인덱스에 먼저 가서 조건에 부합하는 정보가 어떤 ROWID를 가지고 있는지 확인 후 ROWID에 있는 블럭을 찾아가 해당 블럭만 buffer cache에 복사

💭 모든 요소에 인덱스를 걸지 않는 이유는?

https://mungto.tistory.com/312

INDEX를 생성하게 되면 INSERT, DELETE, UPDATE 쿼리문을 실행할때 별도의 과정이 추가적으로 발생한다.
INSERT의 경우 INDEX에 대한 데이터도 추가해야 하므로 그만큼 성능손실이 생긴다.
DELETE의 경우 INDEX에 존재하는 값은 삭제하지 않고 사용안한다는 표시로 남게된다.
따라서 row의 수는 그대로인데 이러한 작업이 반복되면 실데이터가 10만건인데 데이터가 100만건이 있는 결과가 생길 수 있다.
UPDATE의 경우 INSERT, DELETE의 경우의 문제점을 동시에 수반한다.
이전 데이터가 삭제되고 그자리에 새 데이터가 들어오는 개념이기 때문이다.
칼럼을 이루고 있는 데이터의 형식에 따라서 인덱스의 성능이 악영향을 미칠 수 있다.
즉, 데이터의 형식에 따라 인덱스를 만들면 효율적이고 만들면 비효율적인 데이터의 형식이 존재한다.

💭 복합(결합) 인덱스란?

https://jhkang-tech.tistory.com/210

• 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 의미. 주로 SQL 문장에서 WHERE절의 조건 컬럼이 2개 이상 AND로 연결되어 함께 사용되는 경우에 많이 사용한다.

쿼리문

SELECT ename, sal
FROM emp
WHERE ename = 'SMITH'
AND sex = 'M'
;

결합 인덱스 생성 구문

CREATE INDEX idx_emp_comp
ON emp( ename, sex );

• 결합 인덱스는 AND 조건으로 검색되는 경우 성능에 아주 중요한 역할을 한다. 두개 이상의 조건이 OR로 조회되는 경우는 결합 인덱스를 만들면 안된다.

 

위의 CASE2에서 NAME 컬럼으로 최대한 걸러준 후, SEX 컬럼으로 검색하여 효율성을 높였다. 그래서 결합 인덱스에서는 첫번째 조건에서 최대한 많은 데이터를 걸러서 두번째 검사를 쉽게 만들어 주어야 한다.

결합 인덱스 컬럼의 설정 시 고려해야 할 우선순위

1. where절 조건에 많이 사용되는 컬럼이 우선시
2. Equal('=')로 사용되는 컬럼 우선
3. 분포도가 좋은 컬럼을 우선
4. 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정

💭 트랜잭션이란?

https://mommoo.tistory.com/62

• 트랜잭션(Transaction 이하 트랜잭션)이란, 데이터베이스의 상태를 변화시키기 해서 수행하는 작업의 단위를 뜻한다.
• 데이터베이스의 상태를 변화시킨다는 것은 간단하게 말해서 아래의 질의어(SQL)를 이용하여 데이터베이스를 접근 하는 것을 의미한다.

SQL

• SELECT
• INSERT
• DELETE
• UPDATE

작업의 단위는 질의어 한문장이 아니다. 작업단위는 많은 질의어 명령문들을 사람이 정하는 기준에 따라 정하는 것을 의미한다. 게시판을 예로 들어보자.

게시판 사용자는 게시글을 작성하고, 올리기 버튼을 누른다. 그 후에 다시 게시판에 돌아왔을때,
게시판은 자신의 글이 포함된 업데이트된 게시판을 보게 된다.
이러한 상황을 데이터베이스 작업으로 옮기면, 사용자가 올리기 버튼을 눌렀을 시, Insert 문을 사용하여
사용자가 입력한 게시글의 데이터를 옮긴다. 그 후에, 게시판을 구성할 데이터를 다시 Select 하여 최신 정보로
유지한다. 여기서 작업의 단위는 insert문과 select문 둘다 를 합친것이다. 이러한 작업단위를 하나의 트랜잭션이라 한다.

관리자나 개발자가 하나의 트랜잭션 설계를 잘하는 것이 데이터를 다루는 것에 많은 이점이 있다.

트랜잭션의 특징

트랜잭션의 특징은 크게 4가지로 구분된다.

• 원자성 (Atomicity)
  • 트랜잭션이 데이터베이스에 모두 반영되던가, 아니면 전혀 반영되지 않아야 한다는 것
  • 트랜잭션은 사람이 설계한 논리적인 작업 단위로서, 일처리는 작업단위 별로 이루어져야 사람이 다루는데 무리가 없다.
  • 만약 트랜잭션 단위로 데이터가 처리되지 않는다면, 설계한 사람은 데이터 처리 시스템을 이해하기 힘들 뿐만 아니라, 오작동 했을시 원인을 찾기가 매우 힘들어 질 것이다.
• 일관성 (Consistency)
  • 트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 한다는 것
  • 트랜잭션이 진행되는 동안에 데이터베이스가 변경 되더라도 업데이트된 데이터베이스로 트랜잭션이 진행되는것이 아니라, 처음에 트랜잭션을 진행 하기 위해 참조한 데이터베이스로 진행된다. 이렇게 함으로써 각 사용자는 일관성 있는 데이터를 볼 수 있는 것이다.
• 독립성 (Isolation)
  • 둘 이상의 트랜잭션이 동시에 실행되고 있을 경우 어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에 끼어들 수 없다.
  • 하나의 특정 트랜잭션이 완료될때까지, 다른 트랜잭션이 특정 트랜잭션의 결과를 참조할 수 없다.
• 지속성 (Durability)
  • 트랜잭션이 성공적으로 완료됐을 경우, 결과는 영구적으로 반영되어야 한다는 점.

💭 데이터베이스 정규화

https://hoyeonkim795.github.io/posts/normalization/

• 데이터의 중복을 줄이고 무결성을 향상시키는 등 여러 목적을 달성하기 위해서 재디자인 하는 것. 즉, 정규화는 릴레이션 내에서 중복을 제거하는 과정이다.

데이터베이스 정규화 목적

• 불필요한 데이터를 제거, 데이터 중복을 최소화(저장 공간 최소화)
• 각종 이상 현상(Anomaly)을 방지
• 데이터테이블 구성을 논리적, 직관적으로 하기 위해(자료 구조의 안정성 최대화)
• 다양한 관점에서의 query를 지원하기 위해(효과적인 검색 알고리즘)
• 무결성 제약조건의 시행을 간단하게 하기 위해(데이터 무결성 유지)

데이터베이스 정규화 장점

• 응용프로그램 단에서 불필요 로직을 없앨 수 있다.
• 올바른 데이터만 얻을 수 있다.(변칙 방지)
• 불필요한 쿼리(예, 서브 쿼리) 제거로 성능 향상

데이터베이스 정규화 단점

• 릴레이션 분해로 인해 릴레이션 간의 연산(JOIN 연산)이 많아짐 → 시스템 실행과다 및 비효율적 검색시간, 응답 시간이 느려짐
• 과다한 검색 조건문 발생으로 부자연스러운 데이터베이스 semantics(코드 조각) 초래 우려

💭 Base64 인코딩

https://effectivesquid.tistory.com/entry/Base64-%EC%9D%B8%EC%BD%94%EB%94%A9%EC%9D%B4%EB%9E%80

인코딩이란?

인코딩(encoding)은 정보의 형태나 형식을 표준화, 보안, 처리 속도 향상, 저장 공간 절약 등을 위해서 다른 형태나 형식으로 변환하는 처리 혹은 그 처리 방식을 말한다. 동영상이나 이미지영역에서도 많이 사용되는 용어지만 우리는 Binary Data를 Text로 바꿔주는 Base64 인코딩에 대해서 알아봐야하기 때문에 이하는 생략하겠다.

Base64 인코딩

Base64란 Binary Data를 Text로 바꾸는 Encoding(binary-to-text encoding schemes)의 하나로써 Binary Data를 Character set에 영향을 받지 않는 공통 ASCII 영역의 문자로만 이루어진 문자열로 바꾸는 Encoding이다.

Base64를 글자 그대로 직역하면 64진법이라는 뜻이다. 64진법은 컴퓨터한테 특별한데 그 이유는 64가 2의 제곱수 64=2^6이며 2의 제곱수에 기반한 진법 중 화면에 표시되는 ASCII 문자들로 표시할 수 있는 가장 큰 진법이기 때문이다. (ASCII에는 제어문자가 다수 포함되어 있기 때문에 화면에 표시되는 ASCII 문자는 128개가 되지 않는다.)

핵심은 Base64 Encoding은 Binary Data를 Text로 변경하는 Encoding이다.

변경하는 방식을 간략하게 설명하면 Binary Data를 6 bit 씩 자른 뒤 6 bit에 해당하는 문자를 아래 Base64 색인표에서 찾아 치환한다. (실제로는 Padding을 더해주는 과정이 추가된다.)

Why Base64?

Base64 Encoding을 하게되면 전송해야 될 데이터의 양도 약 33% 정도 늘어난다. 6bit당 2bit의 Overhead가 발생하기 때문이다. http://www.base64encode.org/에서 직접 테스트해보면 어렵지 않게 확인 할 수 있다. Encoding전 대비 33%나 데이터의 크기가 증가하고, Encoding과 Decoding에 추가 CPU 연산까지 필요한데 우리는 왜 Base64 Encoding을 하는가?

문자를 전송하기 위해 설계된 Media(Email, HTML)를 이용해 플랫폼 독립적으로 Binary Data(이미지나 오디오)를 전송 할 필요가 있을 때, ASCII로 Encoding하여 전송하게 되면 여러가지 문제가 발생할 수 있다. 대표적인 문제는

ASCII는 7 bits Encoding인데 나머지 1bit를 처리하는 방식이 시스템 별로 상이하다.
일부 제어문자 (e.g. Line ending)의 경우 시스템 별로 다른 코드값을 갖는다.
위와 같은 문제로 ASCII는 시스템간 데이터를 전달하기에 안전하지가 않다. Base64는 ASCII 중 제어문자와 일부 특수문자를 제외한 64개의 안전한 출력 문자만 사용한다.
(* 안전한 출력 문자는 문자 코드에 영향을 받지 않는 공통 ASCII를 의미한다).

즉, “Base64는 HTML 또는 Email과 같이 문자를 위한 Media에 Binary Data를 포함해야 될 필요가 있을 때, 포함된 Binary Data가 시스템 독립적으로 동일하게 전송 또는 저장되는걸 보장하기 위해 사용한다” 라고 정리 할 수 있을 것 같다.

💭 사용자 패스워드를 전송하고 보관하는 방법

https://velog.io/@vkdtksdl789/%EC%82%AC%EC%9A%A9%EC%9E%90-%ED%8C%A8%EC%8A%A4%EC%9B%8C%EB%93%9C%EB%A5%BC-%EC%A0%84%EC%86%A1%EB%B3%B4%EA%B4%80%ED%95%98%EB%8A%94-%EB%B0%A9%EB%B2%95

• 암호화
  어떠한 데이터를 암호화 시켜 다른 이들이 직접 그 의미를 알지 못하도록 하는 작업
• 복호화
  암호화 되어있는 데이터를 해독하는 작업
• 단방향
  복호화가 불가능하다는 것이고 이를 '해싱'이라고 부른다.
• 양방향
  '해싱'과는 다른 '암호화'이며 역으로 복호화도 가능하다.
• 단방향 해시 함수(단방향)
  어떤 수학적 연산 또는 알고리즘에 의해 원본 데이터를 매핑시켜 완전히 다른 암호화된 데이터로 변환시키는 것이다. 이를 다이제스트라고 부른다. 단방향으로 원본 데이터를 구할 수 없다.
  대표적인 해시 함수는 SHA, MD, HAS, WHIRLPOOL이 있다.
• 다이제스트(digest)
  해시에 의해 암호호된 데이터
• 단방향 해시 함수의 단점
  • 동일한 메시지는 동일한 다이제스트를 갖는다.
    해커들은 여러 값들을 대입해보면서 얻었던 다이제스트들을 모아놓은 리스트에서 메시지의 원문을 찾는다. 이러한 다이제스트들의 테이블을 레인보우 테이블이라고 한다.
  • 무차별 대입 공격(브루트포스)
    해시 함수는 원래 빠른 데이터 검색을 위한 목적으로 설계되었기 때문에 원문과 다이제스트는 금방 얻을 수 있다. 해커들은 '상징성'있는 문자를 추려서 조합하여 해킹을 시도한다.
• 단방향 해시 함수 보완
  • 해시 함수 여러 번 수행(Key Stretching)
    다이제스트를 여러 번 해시로 암호화 시키는 작업이다. 브루트포스를 무력화 시킬 수 있다.
  • 솔트(Salt)
    해시함수를 돌리기 전 원문에 임의의 문자열을 덧붙이는 것이다. 한 명의 패스워드가 유출되더라도 다른 사용자는 안전할 수 있다.