본문 바로가기
computing

20250317_DB2

by greentworkshop 2025. 3. 17. 

CREATE TABLE table_name(
	column1 datatype UNIQUE,
	column2 datatype UNIQUE,
	column3 datatype,
		…
);

새로운DB, Query를 생성하는 명령어를 제외하고는 선택된 DB내에서 명령이 수행된다.

추가됨

실행화면

코드가 반영되어 manner_point값이 1100으로 증가

 

여기까지가 복습

인덱스

테이블에는 값이 저장된 순서로 나열됨

반면INDEX는 필요한 값을 묶어둠 = 정렬(참조속도 증가 = 검색성능 ↑↑ )

물리적인 디스크 차지(가상X)

 

질문1. 근데 INDEX도 데이터 다 뒤져봐야하는거 아닌가

질문2. TABLE에서 찾는게 더 빠른 경우도 있지 않을까

질문3. 

 

 

질문 1. 인덱스도 결국 데이터를 다 뒤져봐야 할 수도 있는 게 아닌가? 최악의 경우에 테이블보다 낮은 성능을 보여줄 수도 있지 않을까?

(인덱스 이해를 잘못함,

인덱스가 메모리 공간을 쓴다길래 테이블과 같이 테이터를 저장하는 테이블을 하나 더 만든다고 이해함
→ 그래서 '정돈된 테이블' vs '그냥 테이블' 비교하는 것으로 생각함)

 

인덱스는 데이터를 찾는 속도를 빠르게 하기 위한 자료구조입니다. 기본적으로 인덱스는 검색을 빠르게 할 수 있도록 도와주지만, 완벽한 해결책은 아니에요. 인덱스를 사용하더라도 몇 가지 경우에는 최악의 성능이 나올 수 있습니다.

  1. 인덱스가 잘못 설계된 경우: 예를 들어, 인덱스가 불필요하게 생성되었거나, 필요한 컬럼에 인덱스가 없을 경우에는 테이블을 전체적으로 스캔하는 것과 비슷한 성능을 보일 수 있습니다.
  2. 인덱스를 사용한 검색이 아닌 다른 작업: 인덱스는 검색을 빠르게 할 수 있도록 도와줍니다. 그러나 데이터 수정 (INSERT, UPDATE, DELETE) 작업이 있을 경우, 인덱스는 추가적인 오버헤드를 발생시킬 수 있습니다. 이 경우, 인덱스를 사용하더라도 전체적으로 성능이 저하될 수 있습니다.
  3. 인덱스의 구조 (B-Tree 등)에 따라 성능 차이: 인덱스는 B-Tree나 Hash 같은 자료구조를 기반으로 만들 수 있는데, 잘못된 자료구조를 선택하면 인덱스의 장점이 사라질 수 있습니다.

따라서 인덱스가 잘 설계되면 훨씬 빠른 성능을 보이지만, 잘못된 설계나 특정 조건에서는 테이블 전체 검색보다 성능이 나쁠 수 있습니다. 그러나 일반적으로는 인덱스가 훨씬 효율적인 경우가 많아요.

질문 2.  INDEX라는 자료구조가 DB에서 정확히 어떤 역할을 하는지 테이블과 비교해서 설명해줘

테이블과 인덱스는 각기 다른 역할을 수행합니다.

테이블:

  • 데이터 저장소: 테이블은 실제 데이터를 저장하는 곳입니다. 각 행(row)은 특정 데이터를 나타내며, 열(column)은 데이터의 특성을 나타냅니다. 테이블은 데이터를 순차적으로 저장합니다. 따라서, 원하는 데이터를 찾기 위해서는 전체 테이블을 순차적으로 탐색해야 할 수 있습니다. 이 과정을 테이블 스캔이라고 합니다.
  • 장점: 데이터의 모든 정보를 담고 있어 데이터 관리가 용이합니다.
  • 단점: 데이터가 많아지면 원하는 데이터를 찾기 위해 시간이 오래 걸릴 수 있습니다.

인덱스:

  • 빠른 검색을 위한 자료구조: 인덱스는 테이블 내 특정 컬럼에 대해 빠르게 데이터를 찾을 수 있도록 돕는 자료구조입니다. 인덱스를 통해 특정 조건을 만족하는 데이터를 빠르게 찾을 수 있습니다. 예를 들어, 이진 탐색(B-Tree 기반 인덱스)이나 해시 테이블 등을 사용하여 검색 속도를 개선합니다.
  • 장점: 데이터를 빠르게 찾을 수 있습니다. 데이터가 많아지면 인덱스를 사용하여 검색 성능이 크게 향상됩니다.
  • 단점: 인덱스는 별도의 메모리를 사용하고, 데이터가 변경될 때마다 인덱스도 업데이트되어야 하기 때문에, INSERT, UPDATE, DELETE 작업이 있을 때 성능 오버헤드가 발생합니다.

테이블과 인덱스의 차이:

  • 기능: 테이블은 데이터를 저장하고 관리하는 역할을 하며, 인덱스는 데이터를 빠르게 조회할 수 있게 돕는 역할을 합니다.
  • 저장 방식: 테이블은 순차적으로 데이터를 저장하는 반면, 인덱스는 정렬된 순서로 저장되거나 특정 자료구조(예: B-Tree, Hash)를 기반으로 저장되어 빠르게 찾을 수 있게 합니다.
  • 성능: 테이블은 데이터를 찾을 때 전체를 탐색해야 할 수도 있지만, 인덱스를 사용하면 특정 조건을 만족하는 데이터를 빠르게 찾을 수 있습니다.

결론적으로, 인덱스는 테이블의 데이터를 보다 효율적으로 검색할 수 있도록 돕는 자료구조로, 테이블은 실제 데이터를 저장하는 곳입니다. 인덱스는 검색 속도를 높여주지만, 그만큼 관리해야 할 오버헤드가 있기 때문에 적절하게 설계하고 사용해야 합니다.

인덱스는 실제로 메모리 공간을 차지하지만, 별도의 테이블을 만드는 것은 아니다

인덱스가 메모리를 차지하는 방식

  1. 별도의 테이블이 아님: 인덱스는 테이블의 데이터에 대한 참조를 저장하는 자료구조일 뿐입니다. 즉, 인덱스는 테이블 데이터를 복제하지 않고, 해당 데이터의 참조를 저장하는 방식으로 동작합니다. 예를 들어, name 컬럼에 대한 인덱스가 있다면, 인덱스는 name 값들만 정렬하고, 해당 값이 저장된 원본 테이블의 위치나 행 ID(row ID)를 함께 저장합니다. 실제 데이터는 테이블에 있고, 인덱스는 빠르게 그 데이터를 찾을 수 있도록 도와주는 역할을 합니다.
  2. 메모리 사용: 인덱스는 메모리 상에 별도의 자료구조를 사용합니다. 예를 들어, B-Tree 기반의 인덱스는 트리 구조로 데이터를 저장하며, 해시 인덱스는 해시 테이블로 데이터를 저장합니다. 이 자료구조들이 메모리 공간을 차지하게 됩니다.
    • 예를 들어, B-Tree 인덱스의 경우:
      • 트리 구조를 따라가며 검색할 수 있기 때문에, 인덱스는 각 데이터 항목에 대한 포인터(데이터가 테이블에서 어디에 있는지 알려주는 참조)를 포함합니다.
      • 이 포인터와 데이터를 정렬된 순서대로 저장하기 때문에, 인덱스는 그 자체로 메모리를 차지합니다.
  3. 데이터 저장 방식: 인덱스는 원본 테이블의 데이터와는 별개로, 정렬된 형태로만 저장됩니다. 즉, 원본 테이블에서 id, name, age 컬럼이 있을 경우, 인덱스는 예를 들어 name 컬럼에 대해 정렬된 이름 목록과 그에 대응하는 테이블 행 ID나 위치를 저장합니다. 실제 데이터를 인덱스에 저장하는 게 아니라, 해당 데이터의 위치 정보만 저장하는 방식이죠.

예시

  • 테이블: students 테이블이 있고, 이 테이블의 name 컬럼에 대해 인덱스를 생성한다고 가정해 보겠습니다.

정리:

  • 인덱스는 별도의 테이블이 아니며, 원본 테이블의 데이터를 복제하지 않습니다. 대신, 특정 컬럼에 대해 정렬된 형태로 데이터를 참조하는 자료구조를 사용해 검색 성능을 빠르게 합니다.
  • 이 자료구조는 메모리 공간을 차지하지만, 테이블 자체를 복제하지 않으며, 데이터의 위치 정보나 참조만 저장합니다.

 

부모 테이블을 참조하는 키, 또는 그 관계 = Foreign Key(외래키)

 

학번 학생이름 학년
1 김거북 1
2 박토끼 3

 

구내 커피숍메뉴 고객 결제금액
아이스아메리카노 김거북 2,000
에스프레소 박토끼 2,500

1) On Delete:

 Cascade : 부모 데이터 삭제 시 자식 데이터도 삭제

 Set null : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Null로 업데이트

 Set default : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

 Restrict : 자식 테이블이 참조하고 있을 경우, 데이터 삭제 불가

 No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

 

2) On Update:

 Cascade : 부모 데이터 업데이트 시 자식 데이터도 업데이트

 Set null : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Null로 업데이트

 Set default : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

 Restrict : 자식 테이블이 참조하고 있을 경우, 업데이트 불가

 No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

DB명령어 종류

•데이터 제어어 – Data Control Language (DCL):

데이터베이스에 접근하거나 객체에 권한을 부여.

데이터 베이스의 접속 권한 등을 수정.

GRANT, REVOKE ...

•데이터 정의어 – Data Definition Language (DDL):

데이터 베이스를 정의하는 언어.

데이터베이스 안의 값들을 변경, 수정, 입력.

CREATE, DROP, ALTER ...

•데이터 조작어 – Data Manipulation Language (DML):

저장된 데이터를 실질적으로 처리하는데 사용하는 언어.

데이터 베이스의 생성 및 변경, 제거

SELECT, UPDATE, INSERT, DELETE ...

Primary Key

  • 테이블 당 하나만 존재할 수 있는 키
  • 중복X, NULL값 X

테이블을 새로 만들 때 (column1이 primary key일 경우):

CREATE TABLE table_name(

  column1 datatype NOT NULL PRIMARY KEY,

  column2 datatype,

  …

);

•이미 만들어진 테이블을 수정할 때:

ALTER TABLE table_name

ADD PRIMARY KEY (column_name);

이 경우에는 primary key로 설정하는 column이 NOT NULL이어야 한다.

Unique Key 

  • 값의 중복을 허용하지 않는 키
  • 하나의 테이블에 여러개 지정 가능
  • NULL값 허용
CREATE TABLE table_name(
	column1 datatype UNIQUE,
	column2 datatype UNIQUE,
	column3 datatype,
		…
);

 

 

Index Key

  • 중복 가능, NULL 가능
  • 검색속도 향상
ㄹㄹㄹ

실습

 

CREATE DATABASE day_3;

USE day_3;

CREATE TABLE account(nickname VARCHAR(30) NOT NULL PRIMARY KEY,
address VARCHAR(100),
manner_point INT, 
reg_date DATETIME);

CREATE TABLE market(market_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
seller VARCHAR(30),
product VARCHAR(30),
amount INT,
price BIGINT,
reg_date DATETIME,
FOREIGN KEY (seller) REFERENCES account(nickname));

데이터 값 입력

USE day_3;

INSERT INTO account VALUES('김영진', '서울', 100, '2023-03-07 10:10:10');
INSERT INTO account VALUES('김민준', '인천', 53, '2023-03-07 10:10:10');
INSERT INTO account VALUES('나수아', '부산', 45, '2023-03-09 12:48:20');
INSERT INTO account VALUES('남건우', '분당', 17, '2023-02-21 23:54:00');
INSERT INTO account VALUES('민지환', '부천', 43, '2023-02-16 17:54:24');
INSERT INTO account VALUES('홍진호', '천안', 60, '2022-02-02 02:02:02');
INSERT INTO account VALUES('조승우', '김해', 88, '2022-12-26 00:00:00');
INSERT INTO account VALUES('이가은', '제주', 78, '2023-03-01 10:55:00');
INSERT INTO account VALUES('최하린', '목포', 1, '2022-11-06 18:45:05');
INSERT INTO account VALUES('장승우', '오산', 13, '2022-12-24 12:00:00');

INSERT INTO market VALUES(NULL, '김영진', '닌텐도스위치', 1, 100000, '2023-03-07 10:10:10');
INSERT INTO market VALUES(NULL, '김민준', '사과', 50, 1300, '2023-03-07 10:10:10');
INSERT INTO market VALUES(NULL, '나수아', 'PS5', 2, 350000, '2023-03-09 12:48:20');
INSERT INTO market VALUES(NULL, '남건우', '아이폰14', 35, 1000000, '2023-02-21 23:54:00');
INSERT INTO market VALUES(NULL, '민지환', '책상', 10, 80000, '2023-02-16 17:54:24');
INSERT INTO market VALUES(NULL, '홍진호', '벙커벙커', 22, 222222, '2022-02-02 02:02:02');
INSERT INTO market VALUES(NULL, '조승우', '뮤지컬 티켓', 3, 80000, '2022-12-26 00:00:00');
INSERT INTO market VALUES(NULL, '이가은', '콜라', 35, 500, '2023-03-01 10:55:00');
INSERT INTO market VALUES(NULL, '최하린', '핸드크림', 12, 4500, '2022-11-06 18:45:05');
INSERT INTO market VALUES(NULL, '장승우', '이어폰', 2, 90000, '2022-12-24 12:00:00');

market_id값이 primary키로 지정되어 있어 중복되는 값을 입력하면 오류가 발생한다.

무결성

간단히 말해 정수형에는 1,2,3 문자형에는 '가', '나' 처럼 
데이터에 입력된 값에 결함이 없는 상태

무결성 제약조건

데이터 값에 결함이 없게 사용자의 권한을 제한하는 것 (저장, 삭제, 수정...)

  • 1.개체 무결성 제약조건 (기본키 제약조건, Primary key Constraint)
    - 각 테이블의 Primary key를 구성하는 속성(들)은 NULL 값이나 중복 값이 될 수 없다.
  • 2.참조 무결성 제약조건  (Foreign key constraint)
    - Foreign key 컬럼의 값은 NULL이거나 참조하는 부모 테이블 컬럼의 값과 동일해야 한다.
  • 3.도메인 무결성 제약조건 (Domain Constraint)
    - 속성 안에 들어가는 값들은 정의된 도메인에 속한 값이어야 한다.
  • 4. 고유 무결성 제약조건 (Unique Constraint)
    - 속성의 값들이 서로 달라야 한다. (단, NULL은 허용)

개념적 키(이론)

  • 슈퍼키 : 튜플을 식별할 수 있는 유일한 속성, 또는 그 집합
  • 후보키 : 두 개 이상의 키가 더해져 슈퍼키와 유사한 기능을 할 수 있는 키들의 최소 집합
    • 복합키 : 2개 이상의 속성으로 이루어진 키 
  • 기본키 : 여러 후보키 중 하나를 선정하여 대표로 삼는 키
  • 대리키 : 기본키로 사용할 마땅한 정보가 없거나 기본키가 보안을 요구하는 정보일 떄 가상으로 만들어 사용하는 키 
  • 대체키 : 기본키로 선정되지 못한 나머지 키

자료구조 B-Tree, Hash 비교

 

B-Tree와 Hash 자료구조는 데이터베이스에서 자주 사용되는 자료구조로, 각각의 특징, 장단점, 차이점 등을 이해하는 것은 중요합니다. 시각적으로도 비교해 드리겠습니다.

1. B-Tree와 Hash 자료구조의 특징

B-Tree

  • 정렬된 데이터: B-Tree는 정렬된 데이터를 저장하는 자료구조입니다. 이 구조는 주로 검색, 삽입, 삭제가 빠르게 이루어지도록 최적화되어 있습니다.
  • 균형 이진 트리: B-Tree는 균형 이진 트리입니다. 즉, 트리의 깊이가 균등하게 유지되므로, 데이터의 삽입과 삭제가 균등한 시간 복잡도를 유지합니다.
  • 검색 경로: 데이터를 찾을 때, 트리를 따라가며 검색 경로를 최소화하여 빠르게 접근할 수 있습니다.
  • 범위 검색에 효율적: 범위 검색(ex: BETWEEN 쿼리)에서 매우 빠릅니다.

Hash

  • 해시 함수 기반: Hash는 해시 함수를 사용하여 데이터를 저장합니다. 데이터의 키 값에 따라 해시 함수를 통해 특정 위치(버킷)에 데이터를 할당합니다.
  • 고정 크기의 버킷: 데이터를 저장하는 버킷의 크기가 고정되어 있으며, 해시 함수가 반환하는 위치에 데이터를 저장합니다.
  • 빠른 검색: 검색이 매우 빠르며, **O(1)**의 시간 복잡도를 가질 수 있습니다. 단, 충돌이 없을 경우에만 해당합니다.
  • 범위 검색에 비효율적: 해시 테이블은 범위 검색에 적합하지 않습니다. 특정 범위에 해당하는 데이터를 조회하는 데 시간이 많이 걸립니다.

2. B-Tree와 Hash의 차이점

특성B-TreeHash
구조 트리 구조 (균형 이진 트리) 배열과 버킷을 사용하는 해시 구조
검색 시간 O(log N) O(1) (충돌이 없을 경우)
데이터 순서 데이터를 정렬된 순서대로 저장 데이터를 정렬하지 않음
범위 검색 효율적 (범위 검색에 최적화) 비효율적 (범위 검색이 불가능)
삽입/삭제 O(log N) O(1) (충돌이 없을 경우)
충돌 처리 없음 충돌 발생 시, 추가적인 처리 필요 (체이닝, 개방 주소법 등)
적합성 정렬된 데이터가 필요하거나 범위 검색이 자주 발생하는 경우 빠른 검색이 필요하고, 순서가 중요하지 않은 경우

3. B-Tree와 Hash의 공통점

  • 빠른 데이터 조회: 둘 다 빠른 데이터 검색을 위해 설계되었습니다. B-Tree는 트리 구조를 통해, Hash는 해시 함수를 통해 데이터를 빠르게 찾을 수 있습니다.
  • 데이터 삽입/삭제: 둘 다 데이터를 삽입하거나 삭제하는 데 있어서 효율성을 제공합니다. B-Tree는 트리 구조에서 균형을 유지하며, Hash는 해시 버킷에 데이터를 추가합니다.
  • 메모리 효율성: 두 자료구조 모두 메모리 효율적으로 데이터를 저장하며, 필요한 데이터를 빠르게 찾을 수 있도록 최적화되어 있습니다.

4. B-Tree와 Hash의 장점과 단점

B-Tree 장점

  • 범위 검색에 효율적: 데이터를 정렬된 순서대로 저장하기 때문에 범위 검색에서 매우 효율적입니다.
  • 균형이 잘 유지되어 데이터가 많아져도 검색 시간이 일정하게 유지됩니다 (O(log N)).
  • 트리 구조로 삽입, 삭제 시 균형을 유지하기 때문에 데이터의 무결성이 보장됩니다.

B-Tree 단점

  • **검색이 O(log N)**으로 빠르지만 **O(1)**의 검색 속도를 자랑하는 해시와 비교하면 상대적으로 느립니다.
  • 구조가 복잡하기 때문에 구현과 관리가 비교적 어렵습니다.
  • 메모리를 추가적으로 사용하는 경향이 있을 수 있습니다.

Hash 장점

  • 검색 속도가 매우 빠릅니다 (이론상 O(1)).
  • 구현이 단순하고 효율적입니다.
  • 빠른 데이터 삽입/삭제가 가능합니다.

Hash 단점

  • 범위 검색이 불가능합니다. 데이터가 정렬되지 않기 때문에 범위 검색을 할 수 없습니다.
  • 충돌이 발생할 수 있으며, 충돌을 해결하기 위한 추가적인 작업이 필요합니다.
  • 메모리 사용이 비효율적일 수 있습니다. 해시 버킷의 크기를 과다하게 설정해야 할 수 있기 때문에 메모리 낭비가 발생할 수 있습니다.

5. B-Tree와 Hash의 시각화

B-Tree (예시: 10, 20, 30, 40, 50)

css
코드 복사
[30] / \ [20] [40, 50] / \ [10] [20]
  • B-Tree는 정렬된 트리로, 각 노드는 값을 가지며 자식 노드로 분리됩니다.
  • 검색: 10을 찾으려면 30을 지나 20을 지나 10에 도달합니다.
  • 범위 검색: 10부터 30까지의 값을 효율적으로 찾을 수 있습니다.

Hash (예시: 10, 20, 30, 40, 50)

less
코드 복사
해시 버킷: [10] [20] [30] [40] [50]
  • Hash는 해시 함수를 통해 각 값을 특정 버킷에 할당하여 저장합니다.
  • 검색: 30을 찾으려면 hash(30)이 위치한 버킷에서 바로 O(1) 시간에 값을 찾을 수 있습니다.
  • 범위 검색: 범위 검색은 불가능하며, 모든 데이터를 순차적으로 찾아야 합니다.

결론

  • B-Tree는 정렬된 데이터가 필요하고 범위 검색에 효율적이며, 균형을 유지하며 성능을 보장합니다.
  • Hash는 빠른 검색이 필요한 경우 유용하지만, 범위 검색이 불가능하고 충돌 처리가 필요한 경우가 많습니다.

각 자료구조는 사용 목적에 따라 선택해야 하며, 검색 성능이 중요한 경우에는 Hash, 정렬된 데이터와 범위 검색이 중요한 경우에는 B-Tree가 적합합니다.

 

이름트리

1차. ㄱㄴㄷㄹㅁㅂㅅ...

2차. 김, 강, 고...

3차. 김강민, 김수한무..

저작자표시 비영리 변경금지 (새창열림)

'computing' 카테고리의 다른 글

20250318_OS3  (0) 2025.03.18
PCB(Process Control Block)의 기능, 특징  (2) 2025.03.18
명언 잠금화면 앱 개발_기획서  (4) 2025.03.16
20250310_DB1  (0) 2025.03.10
20250306_java_1  (0) 2025.03.06

관련글

티스토리툴바