mysql 쿼리 최적화

1. 필요한 데이터만 select 하기

X : SELECT * FROM user
O : SELECT id,username,tel FROM user

 

2. or 이 아닌 UNION ALL 을 사용한다.

먼저 join은 수직결합, union은 수평결합이다.  union all과 union distinct의 차이점은 union은 중복 데이터값을 다 삭제하고 연결하며, union all은 중복되는 값을 모두 연결한다. 

or 조건은 풀스캔으로 인덱스를 타지 않기때문에 unino all 으로 바꿔 인덱스를 타게 해줘야한다. 

X : SELECT * FROM user WHERE id=1 OR salary=5000
O : SELECT * FROM user WHERE id=1
      UNION ALL
      SELECT * FROM user WHERE salary=5000

 

3. primary key(기본키) 에는 null 값이 들어갈 수 없으며, 값은 중복되면 안 된다. 

참고로 mysql 에는 boolean 존재하지 않는다.  따라서 가장 작은 데이터 유형인 tinyint를 사용해야한다.

O : primary key  -> best type int or tinyint
      ex) sex(gender)  : 0 /1 tinyint

 

4.  char(100) 말고 varchar(100)을 사용 

char은 고정형 문자열이라서 2byte의 문자열을 넣어도 100byte만큼의 데이터를 잡게된다. 하지만 varchar은 가변형 문자열이라서 입력한 크기만큼의 공간만 차지하게 된다. 하지만 char 타입은 추후에 연산할 필요가 없기 때문에 사이즈가 고정되어 있을때 사용하면 더 효율적이다. (ex . 주민번호..)

X : `address` char(100) DEFAULT NULL COMMENT 'address'
O : `address` varchar(100) DEFAULT NULL COMMENT 'address'

 

5. 아무리 좋은 인덱스가 있어도 is null 또는 is not null 이 사용되면 전체 테이블을 full scan하기 때문에 인덱스 스캔이 가능한 조건으로 변경해야한다.

X : SELECT * FROM user WHERE age IS NOT NULL
O : SELECT * FROM user WHERE age>0

 

6.  부정형으로 조건을 사용했을때 인덱스를 타지 않는다.

X : SELECT * FROM user WHERE salary!=5000
O :  SELECT * FROM user WHERE salary<>5000

 

7.  inner join → left join → right join 순으로 고려한다.

inner join 、left join、right join，최우선으로 고려한다 inner join

 

8. 같은 칼럼에 Insert를 할때는 두번을 연속으로 하지 않고, 한번에 insert를 한다. (db connection을 줄이기)

X : INSERT INTO user (id,username) VALUES(1,'编程');
     INSERT INTO user (id,username) VALUES(2,'妲己');
O : INSERT INTO user (id,username) VALUES(1,'jun'),(2,'haezllll');

 

9. select 시 index를 where문 제일 우선순위에 조건 걸기.

인덱스는 where문을 건 순서대로 작동함. 따라서 id나 createdBy같은 데이터로 인덱스를 걸면 db 범위를 스캔을 줄일 수 있다.

index : id
X : select id,name from user where name = 'jun' and id = 1
O : select id,name from user where id = 1 and  name = 'jun'

 

10. 인덱스를 걸면 해당 칼럼은 정렬이 된다.

정렬된 문자열 앞부분부터 검색해야 하는데 Like 절이 %로 시작하면 앞 문자열을 모르니 인덱스를 사용할 수 없게 된다. 따라서 최대한 다른 칼럼의 조건을 활용해 해당 조건으로 충분히 필터링이 된후에 and 절로 like연산이 수행되도록 설계해야한다.

X : select * from citys where name like '%jun' (all)
X : select * from citys where name like '%jun%' (all)
O : select * from citys where name like 'jun%' (index)

 

11. index 성능 최고 순서 : system > const > eq_ref > ref > range > index > all. 보통 const가 최고 성능.