2021 04 10 - sql Antipatterns

반올림 오류

소수를 가지고 계산해야 할때

당연히 컬럼의 속성을 FLOAT로 했는데, 계산결과 오류가 발생했다. 왜일까.

안티패턴

FLOAT 데이터 타입 사용

SQL의 FLOAT타입은 다른 프로그래밍 언어와 마찬가지로 실수를 이진형식으로 부호화 한다.

필요에 의한 반올림

십진수로 표현된 모든 수르 이진수로 표현할 수는 없다

어떤 수는 사용자의 의도와는 상관 없이 반올림 되어야 한다. 예를들어 3.333333..

컴퓨터의 해결책은 바로 유한소수를 사용하고, 3.333과 같이 원래의 값에 가까운 값을 선택하는것이다!

안티패턴 : SQL에서 FLOAT 사용

예를들어 아래 쿼리는 59.59를 리턴한다.

SELECT hourly_rate FROM Accounts WHERE account_id = 123;

하지만 FLOAT에 실제로 저장되어 있는 값은 이값과 정확하게 같지는 않다. 이 값에 10억을 곱해보면 바로 알 수 있을것이다.

SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id = 123;

이 쿼리는 59950000762.939를 리턴한다. 59950000000.000이 아니라.

이는, 첫번째 쿼리가 IEEE754의 이진 형식에 따라 소수점 전체가 아니라 반올림 된 결과임을 알 수 있다.

하지만 값을 비교할때는 이것만으로는 충분하지 않을 때가 있다.

예를들어, 첫번째 쿼리에서 account_id = 123에 해당하는 hourly_rate가 59.95임에도 불구하고, 아래의 결과는 아무런 결과도 없다.

SELECT * FROM Accounts WHERE hourly_rate = 59.95;

이 문제를 회피하는 방법은 두 부동 소수점값이 일정 수준으로 가까우면 '사실상 같은'값으로 다루는것이다.

두 값의 차를 구한 후 ABC함수를 이용해 절대값을 만든다. 이 결과가 충분히 작다면 두 값은 사실상 같은거으로 다룰수 있다.

SELECT * FROM Accounts WHERE ABS(hourly_rate - 59.95) < 0.000001;

안티패턴 인식방법

FLOAT, REAL, DOUBLE PRECISION 데이터 타입이 사용되는 곳.

안티패턴 사용이 합당한곳

FLOAT는 INTEGER나 NUMERIC 타입이 지원하느것보다 큰 범위의 실수값을 사용해야할때 좋다.
Oracle에서 FLOAT는 정확한 자리수를 가지는 수치타입이며, BINARY_FLOAT타입이 IEEE754를 사용해 수치를 표현하는 타입이다.

해법, 고정소수점수에는 NUMERIC || DECIMAL 데이터 타입 사용

왜냐하면, 이 타입은 수치를 정확하게 표현하는 정도를 결정 할 수 있기 때문이다.

-> but dbms에 따라 다르다고 함. 어떤 dbms에서는 numeric컬럼의 최댓값은 사용자가 지정한것보다 많은 자리수를 가지고 있음.

이미지를 저장하고 싶다!

목표 이미지 또는 벌크 미디어 저장

여러가지 방법

• 이미지를 BLOB 데이터로 저장
• 이미지를 파일시스템에 저장하고, 그 경로만 DB에 저장.

단점

• 이미지가 DBMS 밖에 있으면, 행을 삭제한다고 하더라도 이미지는 삭제되지 않는다.
  애플리케이션단에서 행 삭제 후 이미지도 함께 삭제하지 않는 한, 이미지는 고아파일이 된다.
• 이미지가 DBMS의 트랜잭션과 상관없이 사용자에게 노출되게 된다.
• ROLLBACK 문제 : 행삭제 & 파일삭제 후에 ROLLBACK을 한다 해도 행은 모르겠지만 파일은 살아나지 않는다. 등등...

결론

이미지를 BLOB으로 저장하자. 위에 나열한 단점을 극복 할 수 있다.

• BLOB의 최대 크기는 DBMS 대부분 이미지를 저장하는 데는 충분한다.
• Oracle은 LONG RAW와 BLOB을 지원하며 2/4GB까지 저장할 수 있다.
• 처음에는 파일시스템에 저장했더라도 당황하지 말자. 외부파일을 로드하는 함수도 존재한다.

인덱스 전쟁

++ 인덱스는 표준이 아니라고 한다! ANSI SQL에서는 INDEX에 대한 언급이 없다.. 결국 DBMS의 영역인듯..

목표: 성능 최적화

인덱스는 테이블을 처음부터 끝까지 검색하는 무식한 방법보다 훨씬 빠르게 원하는 값을 찾을 수 있게 해준다.

안티패턴: 무계획하게 인덱스 사용하기

• 인덱스를 정의하지 않을때 혹은 너무많은 인덱스를 생성할때
• 도움이 되지 않는 인덱스를 정의할때
• 인덱스도 도움이 될 수 없는 쿼리를 짰을때

DML을 사용할때마다 DB는 인덱스 데이터 구조를 업데이트 해야한다. 테이블을 일관적인 상태로 유지해야 하기 때문.

좋은 인덱스를 선정하는법!

• M: 측정
  
  Microsoft Sql Server + Oracle은 SQL Trace 기능과 이 결과를 분석하는 도구를 가지고 ㅇ있다.
  Ms: SQL Server Profiler/Oracle: TKProf
  
  Mysql과 Postgre는 지정된 시간보다 오래 수행된 쿼리에 대해 로그를 남길수있다.
  
  쿼리 성능을 측정할 때는 쿼리 결과 캐싱기능을 비활성화 해야함. 쿼리 실행과 인덱스 사용을 우회할 수 있기때문. -> 측정이 끝난후에 원복하긔.
  
• E: 실행 계획 확인
  
  쿼리의 실행계획을 본다. 쿼리 앞에 explain (oracle은 explain plan)을 붙임으로써.
  
• N: 지명
  
  쿼리에 대한 옵티마이저의 실행 계획을 이제 알고있으니, 인덱스를 타지않는 부분을 살펴보자.
  
• -> 도와주는 툴 : 쿼리 튜닝 권고자
  
• T: Test
  
• O: 최적화
  
  인덱스를 캐시메모리에 미리 로딩해놓아 이득을 얻을 수 있다.
  
• R: 재구성
  
  위에서 살펴봤던것처럼, 데이터가 DML될때마다 인덱스는 업데이트가 된다.
  
  그러한 과정에서 인덱스도 점점 균형을 잃는다(?) 그래서 정비해줘야 한다.(Rebuild)
  

네가 의도한 그룹이 이게 맞느냐

가장 최근에 등록된 버그를 찾는 쿼리

SELECT product_id, MAX(date_reported) AS latest
FROM Bugs JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

이 쿼리는 product_id에 대해 여러개의 lates 값을

안티패턴

단일 값 규칙

쿼리에서 SELECT 목록에 있는 모든 컬럼은 그룹당 하나의값을 가져야 한다. 이를 단일값 규칙 이라 한다.

GROUP BY 절 뒤에 쓴 컬럼 들은 얼마나 많은행이 그룹에 대응되는지에 상관없이 각 그룹당 정확히 하나의 값만 나온다.

MAX() 함수 또한 각 그룹당 하나의 값만 내보낸다.

하!지!만! SELECT의 나머지 행에 대해서는 그룹당 값이 하나일지, 아니면 여러개일지 확신할 수 없다!!!

그룹 안에서 모든행에 같은 값이 나오는지를 보장할 수없는것이다.

위 쿼리에서 다음과 같은 의문을 가질 수 있다.

• 두 버그의 date_reported가 동일하고, 이 값이 그룹 내 최댓값이라면 둘중 어느 버그를 보여줘야 하는가?
  
• 쿼리에서 두가지 다른 집계함수(예를들어 MAX, MIN)을 사용한다면, 쿼리가 어떤 bug_id를 리턴할것인가..?

해법

• 쉬운방법: 걍 단일행이 보장되지 않는 컬럼을 삭제한다.
  
• 상호 연관된 서브쿼리 사용하기. 상호 연관 서브쿼리는 바깥쪽 쿼리에 대한 참조를 가지고 있다.
  핵심은 NOT EXISTS
  상호 연관된 쿼리는 바깥쪽 쿼리의 각 행에 대해 한번씩 실행되므로 성능 최적화적인 방법은 아니다.

SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id
FROM Bugs b1 JOIN BugsProducts bp1 USING (bug_id)
WHERE NOT EXISTS
(SELECT * FROM Bugs b2 JOIN BugsProducts bp2 USING (bug_id)
WHERE bp1.product_id = bp2.product_id
AND b1.date_reported < b2.date_reported);

• 유도 테이블 사용하기
  
  날짜와 product_id와 bug_i만을 저장하는 테이블을 새로 생성한다.