1. 올바른 index 를 사용하지 않는 것

어렵지 않지만 자주 놓지는 것이다. 외부 키 (foreign key) 는 index 를 가져야 한다. WHERE 부분에 사용하는 field 도 (아마도) 인덱스를 가져야 한다. 쿼리에 따라, 어떤 인덱스들은 여러개의 컬럼을 사용하도록 만들기도 한다.

2. 참조 무결성 (referential integrity) 을 사용하지 않는 것

데이터베이스가 referential integrity 를 지원한다면 - 즉 foreign key 들이 가리키는 것이 항상 존재한다는 보장을 받을 수 있으면 - 이 기능을 이용하는 것이 좋다.

MySQL 에서 MyISAM 은 이 기능을 지원하지 않고, InnoDB 는 지원한다. MyISAM 을 사용하거나, InnoDB 를 사용하지만 referential integrity 기능을 사용하지 않는 사람들을 종종 본다.

더 많은 정보를 보려면:

3. surrogate (technical) primary key 대신에 natural key 를 이용하는 것

데이터중 unique 한 데이터를 키로 이용하는 것을 natural key 라고 한다. product 코드나, 두자리 state 코드 (미국), 주민등록 번호 등이 여기에 해당한다. 데이터베이스 바깥에서는 아무 의미가 없는 키를 surrogate 또는 technical key 라고 부른다. 데이터베이스에서 객체를 구별하기 위해 만들어지며 일반적으로 자동-증가되는 (auto-incrementing) 필드들이거나 (SQL Server, MySQL, others) 또는 sequence (Oracle) 이다.

내 생각에는, 항상 surrogate key 를 사용하는 것이 좋다. 다음 질문들에서도 이 문제가 다뤄졌다:

이 주제는 논란의 여지가 있으며, 일관된 답이 없긴 하다. natural key 가 어떤 경우에는 괜찮다고 하는 사람들이 있긴 하지만, 아마 surrogate key 가 불필요할수도 있다는 정도의 비판 이상의 비판을 찾기는 어려울 것이다. 내 생각에는 그 정도는 그리 큰 문제는 아니라고 생각한다.

나라가 없어지는 경우도 있다 (예를들어, Yugoslavia) 는 것을 명심하라.

4. DISTINCT 가 필요한 쿼리를 사용하는 것

ORM 이 만들어낸 쿼리에서 이 같은 경우를 보곤 한다. Hibernate 의 로그를 보면 다음 처럼 시작하는 쿼리문을 볼 수 있다:

SELECT DISTINCT ...

중복된 row 를 원치 않을 때 사용하는 일종의 편법이라고 볼 수 있다. 사람들도 저런 쿼리문을 사용하곤 한다. DISTINCT 가 너무 많이 사용된다는 것은 좋지 않은 신호이다. DISTINCT 가 나쁘다는 것도 아니고, 쓸모가 없다는 것도 아니다. 하지만 보다 좋은 쿼리문을 놔두고, 임시방편으로 사용하는 것은 좋지 않다는 것이다.

Why I Hate DISTINCT 라는 곳에서 이렇게 얘기한다:

문제가 시작되는 것은, 개발자가 여러개의 테이블을 join 하는 꽤나 복잡한 쿼리를 작성하다가, 어느 순간 중복된 row 들을 돌려받는 것처럼 보일 때 이다 ... 이 "문제" 에 대한 "해결책" 으로 DISTINCT 를 사용하고, 문제를 덮어버린다.

5. join 보다 aggregation 을 선호하는 것

database 개발자가 자주 실수하는 것중의 하나는, aggregation (즉 GROUP BY 문장) 이 join 에 비해 얼마나 비싼지 잘 모른다는 것이다.

얼마나 이 문제가 널리 퍼져있는지, 이 문제에 대해서 여러번 글을 썼는데, 매우 많은 downvote 를 받았다는 것이다. 예를들어:

From SQL statement - “join” vs “group by and having”:

First query:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3
Query time: 0.312 s

Second query:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1
Query time: 0.016 s

join version  aggreagate version 보다 **20 배나** 빠르다.

6. view 를 통해 복잡한 query 를 간략화 하지 않는것

모든 데이터베이스가 view 를 지원하는 것은 아니지만, 지원한다면 view 를 현명하게 사용하여 query 를 간단하게 만들 수 있다. 예를들어, CRM 을 만들 때 generic Party model 를 사용한 적이 있다. 굉장이 강력하고 유연한 모델링 기술이지만, 많은 조인을 유발할 수 있다. 다음과 같은 객체들이 존재 한다:

  • Party: people 과 organization
  • Party Role: 예를들어 Employee 와 Employer
  • Party Role Relationship: role 들이 어떤 관계가 있는지

예제:

  • Ted 은 Person 이고, Person 은 Party 의 subtype 이다
  • Ted 는 많은 role 을 가지고 있고, 그 중 하나는 Employee 이다.
  • Intel 은 organisation 이고, organization 은 Party 의 subtype 이다.
  • Intel 은 많은 role 을 가지고 있고, 그중의 하나는 Employer 이다.
  • Intel 은 Ted 를 고용한다. 즉 이 둘이 가지고 있는 role 들 사이에 연관관계가 있다.

Ted 와 고용주 (employer) 를 연결하기 위해 5 개의 테이블이 필요하다. 모든 employee 가 Person (즉 organization 이 아님) 이라고 가정하고, 다음과 같은 helper view 를 만든다:

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

이제 이 유연한 데이터 모델을 사용하면서도, 데이터를 손쉽게 query 할 수 있는 view 를 갖게 되었다.

7. input 을 안전하게 변형하지 않는 것

아주 큰 문제이다. 나는 PHP 를 좋아하지만, 제대로 사용하지 않으면, 공격받기 쉬운 사이트를 만들게 된다. 이 문제에 대해서 story of little Bobby Tables 에 정말 잘 요약되어 있다.

URL 과, form 데이타, *cookie 를 이용하여, 유저로부터 전달받은 데이터는 언제나 적대적인 의도가 있다고 여기고 변형하여야 (sanitize) 한다.

8. prepared statement 를 사용하지 않는 것

prepared statement 는 query 에서 insert/update/where 에 사용할 데이터를 제거한 다음에, 이 데이터들을 나중에 제공하는 것을 말한다. 예를들어

SELECT * FROM users WHERE username = 'bob'

vs

SELECT * FROM users WHERE username = ?

or

SELECT * FROM users WHERE username = :username

등의 형태를 띈다.

나는 이 때문에 (prepared statement 를 사용하지 않아서) 망가지는 데이터베이스들을 많이 보았다. 새로운 query 를 받으면, 데이터베이스는 쿼리를 컴파일한다. 이전에 보았던 쿼리를 전달하면, 데이터베이스에게 쿼리와 실행계획 (execution plan) 을 캐쉬할 기회를 주는 것이다. query 를 자주 사용하면 데이터베이스가 쿼리를 메모리에 보관하는 등의 최적화를 하게 된다.

prepared statement 를 사용하면, 특정 쿼리들이 얼마나 자주 쓰였는지에 대한 통계도 알게 될 것이다.

prepared statement 는 SQL injection 공격으로 부터도 더 안전하다.

9. normalize 를 충분히 하지 않는 것

Database normalization 은 기본적으로 database design 을 최적화하는 과정이고, 어떤 table 들로 데이터를 정리할 것인가를 결정한다.

얼마전에 배열을 데이터베이스의 필드에 저장하는 코드를 보았다. 이 데이터를 nomalize 한다면, 배열의 원소들을 새로운 자식 table 의 row 로 (즉 one-to-many 관계가 생성됨) 저장할 것이다.

Best method for storing a list of user IDs 에서도 이 문제에 대해서 다룬다.

list 를 serialize 한 PHP array 로 저장하는 시스템을 본적이 있다.

nomalization 이 부족한 것은 이 외에도 여러 형태로 나타난다.

더 많은 정보:

10. Normalizing 을 너무 많이 하는 것

이전 주장과 상반되는 것처럼 보이는 주장이다. normalization 은 다른 많은 것들 처럼, 툴일 뿐이다. normalization 은 목적을 이루기 위한 수단이지, 그 자체가 목적은 아니라는 것이다. 많은 개발자들이 이를 잊어먹고, 마치 "수단" 을 "목적" 처럼 여긴다. Unit testing 도 마찬가지 문제가 있다.

나는 매우 복잡한 단계를 가진 시스템 작업한 적이 있다:

Licensee ->  Dealer Group -> Company -> Practice -> ...

너무 복잡해서 의미있는 데이터를 얻으려면 11 개의 테이블을 조인 해야만 했다. normalization 을 너무 많이한 예라고 할 수 있다.

더군다나, 신중하게 생각한 denormalization 은 성능을 크게 증가 시킬 수 있다.

더 많은 정보:

11. exclusive arc 를 사용하는 것

exclusive arc 는 한 테이블에 여러개의 foreign key 가 있고, 이들 중 오직 하나만이 null 이 아닌 경우를 말한다. 아주 큰 실수이다. 우선 data integrity 를 관리하기가 힘들어진다. 두개 이상의 foreign key 가 null 이 아니게 되기를 막을 수 있는 방법이 없다.

A Practical Guide to Relational Database Design 에서 말하길:

우리는 가능한한 exclusive arc 를 사용하지 말기를 권한다. 코드를 작성하기도 어렵고, 관리하기도 어렵다.

12. query 의 performance 를 확인하지 않는 것

데이터베이스 세상에는, 실행되면 그만이라는 생각이 지배적이다. 어떤 원칙을 지나치게 맹신한 나머지 독단적이 되면, 아마 오류를 범하게 될 가능성이 크다. 위에서 언급한 aggregate query 예를 보라. aggreate version 이 "멋있어" 보일지 모르지만, 성능은 후지다. 성능 비교가 논쟁을 불식시켰어야 맞다 (하지만 그렇지 않았다). 그리고 그런 잘못된 지식을 전파하는 것은 무식하며, 위험한 것이다.

13. UNION ALL 특히 UNION 에 과하게 의존하는 것

SQL 에서 UNION 은, 똑같이 생긴, 즉 같은 타입과 같은 수의 컬럼을 갖는, row 들을 합쳐준다. UNION 과 UNION ALL 의 차이점은 UNION 이 중복을 제거하기 위해 DISTINCT 를 사용한다는 것이고, 따라서 가능한 UNION ALL 을 사용하는 것이 맞다.

DISTINCT 처럼 UNION 도 분명히 쓸모가 있다. 하지만, UNION 을 너무 많이 사용하고 있다면, 특히나 subquery 에서, 아마도 무엇인가 잘못하고 있는 것이다. query 를 잘못 작성한 것이거나, data model 을 잘못 디자인한 경우가 많다.

join 이나 subquery 에서 UNION 을 많이 사용하면 database 를 느리게 만들 수 있다. 가능하면 사용을 피하는 게 좋다.

14. query 에 OR 조건 을 사용하는 것

아무 해가 없는 것처럼 보일 수 있다. AND 가 괜찮다면, OR 도 괜찮아야 되는 것 아닌가? 틀렸다. AND 조건은 데이터를 제한하지만, OR 조건은 데이터를 더 많이 얻어오게 한다. 그리고 OR 조건문을 데이터베이스가 최적화하기 쉽지 않다. OR 조건으로 걸리는 데이터들이 겹친다면, 데이터베이스의 optimizer 가 결과들에 DISTINCT 를 수행하여 중복을 제거하여야 할 것이다.

Bad:

... WHERE a = 2 OR a = 5 OR a = 11

Better:

... WHERE a IN (2, 5, 11)

SQL optimizer 가 첫번째 query 를 두번째 것으로 변형할 수도 있다. 하지만 그렇지 않을 수도 있다. 그러니 첫번째 형태는 사용하지 말아라.

15. 성능 좋은 query 를 할 수 없는 데이터 모델을 만드는 것

수치로 접근하기 어려운 개념이다. 상당히 간단한 작업을 하기 위해서 매우 복잡한 쿼리를 작성해야 하거나, 간단한 정보를 찾기위한 쿼리가 효율적이지 않다면, 아마도 데이터 모델이 잘 디자인 되지 않은 것이다.

어떤면에서, 이 것이 이전의 열거한 모든 내용들을 포함하는 것일 수 있다. 하지만 query 최적화를 먼저 하려고 하진 말아라. 최적화 이전에 우선 좋은 데이터 모델을 만드는 것이 중요하다. Knuth 는 다음처럼 말했다:

Premature optimization is the root of all evil

16. transaction 을 잘못 사용하는 것

특정 작업을 위한 모든 데이터 변경은 atomic (한번에 이루어져야) 한다. 작업이 성공적이라면, 모든 것이 수행되어야 하고, 작업이 실패하면, 데이터의 변경이 전혀 없어야 한다. - '하다가 만' 상태라는 것이 없어야 한다.

이상적으로는, 모든 데이터 변경이 하나의 INSERT/UPDATE/DELETE 문으로 이루어진다면 위 목적을 이룰 수 있을 것이다. 이렇게 되면, 특별히 trasaction 을 처리할 것이 없게 된다.

하지만, 데이터를 일관된 (consistent) 상태로 유지하기 위해서 여러개의 statement 을 실행해야한다면, transaction 이 필요하다.

  • 첫번째 문장 전에 trasaction 을 시작한다.
  • 마지막 문장 후에 transaction 을 commit 한다.
  • error 가 발생하면, trasaction 을 취소한다. 그리고! 에러후에는 모든 문장들을 건너띄는 것을 까먹지 마라.

그리고 데이터베이스의 연결 layer 와 engine 이 이와 관련해 어떻게 상호작용 하는지 주의를 기울이기 바란다.

17. 'set' 기반의 사고방식을 이해하지 못하는 것

SQL 언어는 특정 문제를 풀기 위한 특정한 paradigm 을 사용한다. (역주 set-based paradigm).

이를 이해하지 못하면 다음과 같은 문제들이 발생한다.

  • database 에 너무 많은 procedural 또는 imperative 한 명령을 너무 많이 주려 한다.
  • cursor 를 불필요한 곳에 사용하거나 너무 많이 사용한다. 특히나 단일 쿼리로 충분할 때 말이다.
  • 여러개의 row 를 update 할 때 trigger 가 row 마다 발생할 것이라 예상한다.

책임을 명확하게 구분하여, 각 문제에 해당하는 적당한 툴을 사용하도록 노력하라.