SEQUENCE* ( autoincrement대체 ) and INDEX* ( WHERE서치를 문자열에서 빠르게 내부binary파일생성)

총정리

SEQUENCE 정리

1. SEQUENCE 만들기 : CREATE SEQUENCE 시퀀스명 (START n); 
2. 해당테이블의 해당칼럼 default값을 SEQUENCE로 하나씩 꺼내기 : ALTER TABLE + ALTER COLUMN + SET DEFAULT nextval('시퀀스명');
3. SEQUENCE를 해당테이블.해당칼럼에 종속시켜 같이 사라지게 하기 : ALTER SEQUENCE 시퀀스명 OWNED BY 테이블명.칼럼명;
4. 이제 INSERT INTO 테이블시 SEQUENCE칼럼빼고 값 입력하기

INDEX 정리

1. INDEX는 만들기 : CREATE INDEX 인덱스명 ON 테이블명(칼럼명);
2. 자주 검색조건에 들어가는 문자열을 WHERE로 서치해도 속도가 빨라진다. 내부 binary tree파일 생성

 

SEQUENCE

-- PostgreSQL에서는 autoincrement가 없다. 대신 우리는 SEQUENCE를 생성해야한다.
SELECT * FROM users;

-- users테이블의 (id, name)에서 id는 자동으로 증가하고 name만 넣어도 되도록 해보자.
-- 1) 일단은 name만 입력시키는 것은 안된다.( id가 primary key로 not null인데 null이 들어갔다고 나옴)
INSERT INTO public.users(name)
VALUES ('chojaeseong')

-- 2) 먼저, SEQUENCE 생성하기 [  CREATE SEQUENCE table명_적용할칼럼명_seq ]
--    우리는 이미 3명이 차있기 때문에, default로 SEQUENCE 생성시 1로 시작해서 오류가 날것이다.
--    [  CREATE SEQUENCE table명_적용할칼럼명_seq  START 4; ]로 시작번호도 알려주자.
CREATE SEQUENCE users_id_seq START 4;


-- 3) ALTER TABLE로 users테이블의 id칼럼의 default value를 SEQUENCE로 SET해주기 
--    만약, SET DEFAULT 9 으로 주면,, 칼럼의 모든 값이 9가 되어버린다.
--    9가 아니라 nextval(' seq name ')이라는 pgSQL의 함수로 SEQUENCE에서 하나씩 꺼내어 대입하는 방식이다.
ALTER TABLE public.users
ALTER COLUMN id
SET DEFAULT nextval('users_id_seq');


SELECT * FROM users;

-- 4) 이제 SEQUENCE를 해당 칼럼에 종속시켜 다른데서 안쓰이고, 지울때 같이 지워지도록 설정해준다.
ALTER SEQUENCE users_id_seq OWNED BY public.users.id;


-- 5) SEQUENCE가 적용된 users테이블의 id값을 주지말고, 다시 name만 입력해보자.
--    id칼럼에 autoincrement같은 SEQUENCE가 적용되면, 빼고 INSERT 해줘도 자동으로 증가한다.
INSERT INTO public.users(name)
VALUES ('chojaeseong')

SELECT * FROM users;

INDEX

-- INDEX 
-- 인덱스는 WHERE 서치를 빠르게 해주는데 유용하다.
SELECT * FROM public.users;

-- 1) 먼저, id가 아닌 name으로 사람의 검색해보고 걸리는 시간을 우측하단에서 확인해보자.
--    만약 데이터가 엄청 많다면, 많은 시간이 걸릴 것이다. 왜냐하면 normal column이기 때문!
SELECT * FROM public.users WHERE name = 'chojaeseong'; -- 12~15msec

-- 2) 인덱스는 자주검색하는 문자열(name 등)에 내부적으로 orderd된 index를 주어 직접 문자열 검색안하고도 빠르게 서치가 가능하게 한다.
--    하지만, 인덱스는 공짜가 아니라서, binary tree를 디스크에 저장해야한다.
--    그러므로, 서칭용 칼럼이 아니여서 INSERT나 UPDATE하는 칼럼에는 적용시키면, 느려진다.
--    즉, 자주 검색하는 칼럼에 index를 지정해주고 내부적으로는 파일이 생성될 뿐.
--     primary key는 특별한 종류의 index이다. 하지만, id로 검색하진 않는다.
--    검색을 자주하는 name칼럼을 인덱스로 지정해보자.
--      [CREATE INDEX 인덱스명(테이블명_칼럼명_index) ON 테이블명(칼럼명);]
CREATE INDEX users_name_index ON public.users(name);

-- 3) 다시 한번 name으로 사람을 검색해보고 시간을 측정해보자.
SELECT * FROM public.users WHERE name = 'chojaeseong'; -- 11 msec까지 나온다.


-- 4) 만약, 2개의 칼럼을 동시에 필터링 하는 작업을 자주한다면, 멀티칼럼 인덱스를 생성할 수 있다.
--     마지막 ON 테이블명(칼럼1, 칼럼2) 형식으로 지정해주면 된다. 그러나 필수는 아니다.
-- 예시)    CREATE INDEX index_name On pulbic.movies(id, user_id) 

-- 5) 다양한 UPDATE로 인해서, index가 꼬였다면, REINDEX TABLE 인덱스명;을 통해 해준다.