1. Index [개념 중요]
- index는 검색을 빠르게 하기 위한 수단
- PK와 UK가 지정되면 따로 index를 생성된다.
Index 장단점
장점 : 검색 속도가 빨라지며 , 시스템 부하를 줄여 성능향상
단점 : 인덱스 생성에 시간과 공간 필요 / 삽입,갱신,삭제가 빈번히 일어날 경우 성능 저하
index 가 필요 / 불필요한 경우
필요 : 데이터가 많을 경우 / join이 많이 사용된 경우 -> 검색이 많을 경우
불필요 : 검색이 많이 안될 경우 / 데이터가 많지 않을 경우 / 삽입,갱신,삭제 빈번히 일어나는 경우
1.1 index 생성 방법 2가지
1.2 index 생성 방법 [고유 인덱스]
- 중복되지 않는 데이터에 대해서만 설정되는 인덱스
- Pk 나 유니크 키를 걸어주면 자동으로 생성되는 인덱스이다
- ※ 단. 인덱스 생성 시 중복 데이터가 있으면 에러 발생
- create unique index [인덱스 이름] on [테이블명](컬럼)1.3 index 생성 방법 [비고유 인덱스]
- FK 생성 시 이 인덱스가 걸려 있다.
- ※ 중복데이터가 있어도 인덱스 생성 가능
- create index [인덱스 이름] on [테이블명](컬럼)1.4 index 생성 방법 [결합(복합) 인덱스]
- 복합키 처럼 여러 컬럼을 조합해서 인덱스 생성이 가능 (최대 16개)
- create [unique] index [인덱스 이름] on [테이블명](컬럼,...)1.5 인덱스 확인 (방법)
- show index from [테이블명]1.6 인덱스 삭제
- 인덱스는 테이블 속성으로 취급 하여 alter... drop 사용
- alter table [테이블명] drop index [인덱스이름];2. in & Exists
- Exists가 in보다 속도가 빠르다
※ SQL 에서는 데이터를 확보해 놓고 다음작업을 진행하는 것이 빠르기 떄문
※ exists 는 메인쿼리를 통해 데이터를 확보해두고 , 서브쿼리 내용으로 일치하는 내용을 보여주기 때문에 in 보다 속도가 빠르다
2.1 in
- '=' 비교만 가능 / 크거나 작거나는 비교 불가능
- or 조건의 검색 결과를 가져온다
- 서브쿼리가 먼저 실행되고 그 결과를 가지고 메인 쿼리를 실행 한다.
2.2 exists
- 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 " 만족하는 값이 하나라도 존재 한다면" 참(1) / 거짓(0)
- ※ 메인 쿼리가 먼저 실행되고, 이후 서브퀴리가 실행 된다.
3. any
- 메인 쿼리의 비교조건이 서브쿼리의 결과중에 하나라도 일치하면 참
- in 처럼 사용 가능하고, 크다/작다를 활용 할 수 있다.
- (= Any) : in과 같은 효과
4. all
- any 와 반대
- 사용법은 =all. =any 와 같지만 And 조건이다
- ※ 서브쿼리 안의 값이 여러개가 나타날 경우 사용하면 안된다.
5. view (가장 중요)
- 여러 테이블의 데이터를 모아서 만든 가상 테이블
- 복잡한 쿼리를 통해 얻을 수 있는 결과를 간단한 쿼리로 얻을 수 있게 한다.
- 한개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.
- ※ 단 뷰는 자신만의 인덱스를 가질 수 없다.
-> ※ 뷰의 인덱스를 넣고 싶다면 컬럼의 원본 테이블에서 인덱스를 추가해야 됨
5.1 뷰 생성
- create [or replace] view [뷰이름] as [뷰를 생성할 쿼리] - ※ or replace : 기존 뷰를 수정할떄 사용
-> or replace 는 생성 시 사용하지 말고 / 수정 시 사용해주면 좋다
생성시 사용하지 않는 이유는 기존에 있는 데이터를 덮어쓰이기 떄문
수정시 사용하는 경우는 데이터가 있으면 수정하고 없으면 새로 만들어주기 때문
or replace를 사용할때 목적이 분명해야 한다. 수정으로 사용할지 뷰는 생성할지에 따라 사용여부가 달라지기 때문
5.2 뷰 확인
- show create view [뷰이름]5.3 뷰 사용
- 테이블 사용과 같음
select * from name_query;5.4 뷰 수정
- alter로도 수정 가능하지만 일반적으로 or repalce 를 사용한다
-> 데이터가 있으면 수정하고, 없으면 만들어 주기 떄문에
** ★ 뷰에서 수정을 가하면 원본 테이블의 데이터도 수정된다.**_
※ with check option : 뷰를 생성한 조건식을 만족하는 컬럼에 대해서는 수정을 막는 옵션
5.5 뷰 삭제
- drop view [뷰이름]6. auto_increment
- 자동으로 증가하는 속성 -> 카운트 할때
- 중복이 절대 나타날 수 없다. null도 나타날 수 없다. 테이블에 종속적이므로 PK에 자주 사용 - 중복되지 않는 데이터를 얻기 위해 사용
6.1 auto_increment 생성법
- 생성법 1 : 테이블 생성 시 함께 생성
- 생성법 2 : 이미 생성된 테이블에 추가
※ primary key 에 auto_increment 키워드를 추가해주면 됨
테이블 생성 시no int(10) primary key auto_increment
생성된 테이블에 추가 시alter table test modify no int(10) primary key auto_increment;7. Limit
- limit n : n개 보여주기
- limit n,m : n번 부터 m개 보여주기
- offset n : n번 부터 ~
- 위 내용은 일반적인 페이징 처리 방식
offset의 순서를 위에서 하나씩 세면서 내려가기 때문 위 방식은 데이터가 많아지면 느려진다
7.1 Limit 속도 개선 방법
- 1단계 : 데이터를 정렬 후 내용을 가져온다
- 2단계 : 인덱스가 없다면 인덱스 추가 / 인덱스가 있다면 활용
-> 이미 적용된 인덱스를 활용해서 값을 찾는 방식이므로 더 빠르다.
- 2단계 : 인덱스가 없다면 인덱스 추가 / 인덱스가 있다면 활용
Function
나중에
[JAVA]