minkukjo
commented
2 years ago ~ 11.4
- 기본적인 MySQL 8 버전 옵션들과 연산자들, MySQL 내장 함수들에 대한 설명이라 슥슥 넘겼다.
- 하나 주목할 부분은 in 쿼리가 8버전에 와서 굉장히 최적화가 많이 된 부분인데, 8버전 이전까지만 해도 최적화가 굉장히 불안했던 반면 8버전 부터 in 쿼리의 최적화가 많이 개선되어서 적절히 사용하면 좋은 성능을 낼 수 있는 것으로 보인다.
- between 쿼리가 인덱스 범위의 데이터를 메모리에 올려놓고 비교하는 반면, in 쿼리의 동작은 조건에 부합하는 데이터를 일일이 동등 비교 하기 때문에 성능이 매우 빠르다고 한다.
SELECT
- RDBMS에서 4개의 쿼리 ( CRUD ) 중 가장 많은 문제를 발생시키는 쿼리
SELECT의 질의 순서
- SELECT에서의 쿼리 질의 순서는 다음과 같다.
- 드라이빙 테이블 + N개의 드리븐 테이블의 WHERE 적용 및 조인 실행 -> GROUP BY -> DISTINCT -> HAVING -> ORDER BY -> LIMIT
- GROUP BY가 없는 쿼리에서는 드라이빙 테이블의 WHERE 적용 후 바로 ORDER BY를 수행하고 그 이후에 드리븐 테이블 조인을 수행한다.
주의 사항
- WHERE 절에 컬럼에 상수 값을 연산하면 인덱스 안타니까 조심하자.
- 컬럼의 타입과 실제 쿼리에 사용된 데이터 타입이 달라도 인덱스를 안타니까 조심하자.
- OR 연산은 인덱스가 있떠라도 풀테이블 스캔을 한다. WHERE 조건에 OR 연산을 안넣게 조심하자.
- GROUP BY에서 컬럼을 선언할 때는 인덱스 컬럼 순서와 동일하게 지정하도록 하자. 그래야 인덱스 탄다.
- GROUP BY에 사용된 컬럼 중 하나라도 인덱스에 없으면 GROUP BY 절 자체는 아예 인덱스를 타지 않는다.
- MySQL 인덱스는 모든 컬럼이 오름차순 정렬이므로 ORDER BY 절의 모든 컬럼이 오름차순이거나 내림차순인 경우에만 사용할 수 있다.
- WHERE + ORDER BY 절에서 사용되는 컬럼은 둘 다 하나의 인덱스에 연속해서 포함되어있을 경우 굉장히 빠른 성능을 보인다.
- MySQL은 NULL 값이 포함된 레코드도 인덱스로 관리된다.
- NULL을 검색할 때는 IS NULL 구문을 쓰자. 인덱스 탄다.
시간
-
DATE VS DATETIME
- DATE : 날짜만 저장
- DATETIME : 날짜 + 시간
- TIMESTAMP : 날짜 + 시간
- TIME : 시간
-
는 그럼 DATETIME이랑 TIMESTAMP는 뭐가 다른거지? 하고 찾아봄.
-
대충 아래와 같은 차이가 있음
Both the data types store data in “YYYY-MM-DD HH:MM:SS” format and include date as well as time. In spite of these similarities they are having the following differences −
Range − Datetime data type supports a date along with time in the range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. But timestamp data type supports a date along with time in the range between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’.
Size − Datetime requires 5 bytes along with 3 additional bytes for fractional seconds’ data storing. On the other hand, timestamp datatype requires 4 bytes along with 3 additional bytes for fractional seconds’ data storing. But before MySQL 5.6.4, DateTime requires 8 bytes along with 3 additional bytes for fractional seconds’ data storing.
Conversion from one timezone to other − Actually in MySQL5+, timestamp value converts from current time to UTC and vice-versa while datetime does not do any conversion.
Indexing − Indexing can be done on timestamp data but datetime data cannot be indexed.
Caching of query − queries having timestamp datatype can be cached but queries having datetime datatype cannot be cached.- DATETIME 컬럼 일 때 TIMESTAMP로 비교해보면 잘 동작하는 것 같지만 내부는 그렇지 않다. ( 반대도 마찬가지 )
- 가급적이면 두 타입을 맞춰서 비교하는 편이 좋다.
- 시간 관련 비교를 할 때 함수나 시간 값 자체를 더하거나 빼거나 타입 변환하는 짓은 애플리케이션에서 하자. ( 혹여나 그런 연산을 SQL에서 하면 인덱스를 안타니까 )
Short Circuit Evaluation
- 가급적이면 복잡한 연산이나 또 다른 테이블의 레코드를 읽어야 하는 서브쿼리 조건은 WHERE 절의 뒤쪽으로 배치하는 게 성능상 도움이 된다.
- 물론 인덱스를 사용할 수 있는 조건이라면 어느 절에 배치해도 가장 먼저 평가되므로 신경안써도 된다.
Distinct
- 특정 컬럼에 유니크한 값을 조회하기 위한 구문
- 조인할 때 Distinct는 조심해서 쓰자.
LIMIT
- 몇 개만 가져오는 쿼리
- LIMIT 쿼리는 얼핏 보면 되게 효율적인 쿼리 같아 보이는데 여기엔 함정이 있다.
- SELECT * FROM TABLE ORDER BY salary LIMIT 200000, 10;
- 위 쿼리는 2000010건의 레코드를 읽은 후 200000개를 버리고 마지막 10건만 반환한다.
- 즉 실제 사용자 화면에선 10개만 보이는데 MySQL 입장에선 200000개를 읽어야하니 쿼리가 느려진다.
- 이걸 회피하기 위한 기법으로는
이전 쿼리에서 읽어온 결과값 중 가장 마지막 id를 다음 쿼리에 질의에 사용해서 그 조건으로부터 N개만큼 가져오게 하는기법이 가장 전통적이고 확실한 방법이다. (요 기법 이름이 있었는데 잘 기억이 나질 않는다.)
COUNT
- COUNT(1)과 COUNT(*)의 성능은 같다.
JOIN
- 드라이빙 테이블과 드리븐 테이블의 컬럼이 모두 인덱스가 있으면 통계 정보를 사용해 옵티마이저가 적절하게 드라이빙,드리븐 테이블을 선정해준다.
- 한쪽에만 있으면 인덱스가 없는 쪽을 드라이빙, 인덱스가 있는 쪽을 드리븐으로 선택한다.
- 모두 인덱스가 없으면 옵티마이저가 적절하게 선택한다고 한다.
- 조인할 때 비교 데이터 타입은 꼭꼭 맞춰주도록 하자. 아니면 인덱스 안타니깐.
- 가급적이면 아우터조인 보다는 이너조인을 사용하도록 하자.
ORDER BY
- 인덱스는 항상 정렬되어있으니 ORDER BY를 안써도 될 것이다? 댓츠 노노. 무조건 정렬이 필요하면 써야하는 구문임.
DaehunGwak
Edwin222
진도
일시
2022.07.10.(일) 21:00