데이터베이스를 사용할 때 주의할 점

 

질문 : 응용 프로그램 개발자의 데이터베이스 개발 실수

---

응용 프로그램 개발자가 저지르는 일반적인 데이터베이스 개발 실수는 무엇입니까?

답변

---

1. 적절한 지표를 사용하지 않음

이것은 비교적 쉬운 일이지만 여전히 항상 발생합니다. 외래 키에는 인덱스가 있어야합니다. WHERE 에서 필드를 사용하는 경우에는 (아마도) 인덱스가 있어야합니다. 이러한 인덱스는 실행해야하는 쿼리를 기반으로 여러 열을 포함해야하는 경우가 많습니다.

2. 참조 무결성을 적용하지 않음

데이터베이스는 여기에서 다를 수 있지만 데이터베이스가 참조 무결성을 지원하는 경우 (즉, 모든 외래 키가 존재하는 엔터티를 가리 키도록 보장됨)이를 사용해야합니다.

MySQL 데이터베이스에서이 오류를 보는 것은 매우 일반적입니다. 나는 MyISAM이 그것을 지원한다고 믿지 않는다. InnoDB는 그렇습니다. MyISAM을 사용하거나 InnoDB를 사용하지만 어쨌든 사용하지 않는 사람들을 찾을 수 있습니다.

여기 더 :

• 항상 PHP로 데이터베이스 입력을 제어한다면 NOT NULL 및 FOREIGN KEY와 같은 제약 조건이 얼마나 중요합니까?
• 데이터베이스 디자인에 외래 키가 정말로 필요합니까?
• 데이터베이스 디자인에 외래 키가 정말로 필요합니까?

3. 대리 (기술) 기본 키가 아닌 자연 키 사용

자연 키는 (표면적으로) 고유 한 외부 적으로 의미있는 데이터를 기반으로하는 키입니다. 일반적인 예로는 제품 코드, 두 글자로 된 주 코드 (US), 사회 보장 번호 등이 있습니다. 대리 또는 기술 기본 키는 시스템 외부에서 전혀 의미가없는 키입니다. 이들은 순전히 엔터티를 식별하기 위해 발명되었으며 일반적으로 자동 증가 필드 (SQL Server, MySQL 등) 또는 시퀀스 (특히 Oracle)입니다.

제 생각에는 항상 대리 키를 사용해야합니다. 이 문제는 다음 질문에서 제기되었습니다.

• 기본 키는 어떻습니까?
• 테이블의 기본 키에 대한 모범 사례는 무엇입니까?
• 이 상황에서 어떤 형식의 기본 키를 사용 하시겠습니까?
• 대리 대 자연 / 비즈니스 키
• 전용 기본 키 필드가 있어야합니까?

이것은 당신이 보편적 합의를 얻지 못할 다소 논란의 여지가있는 주제입니다. 자연 키가 어떤 상황에서는 괜찮다고 생각하는 사람들을 찾을 수 있지만, 틀림없이 불필요한 것 외에는 대리 키에 대한 비판을 찾지 못할 것입니다. 저에게 물어 보면 그것은 아주 작은 단점입니다.

국가조차도 존재하지 않을 수 있습니다 (예 : 유고 슬라비아).

4. 작동 DISTINCT 가 필요한 쿼리 작성

ORM 생성 쿼리에서 자주 볼 수 있습니다. Hibernate의 로그 출력을 보면 모든 쿼리가 다음으로 시작하는 것을 볼 수 있습니다.

SELECT DISTINCT ...

이것은 중복 행을 반환하여 중복 객체를 얻지 않도록하는 약간의 지름길입니다. 때때로 사람들도이 일을하는 것을 볼 수 있습니다. 너무 많이 보면 진짜 위험 신호입니다. DISTINCT 가 나쁘거나 유효한 응용 프로그램이 없다는 것은 아닙니다. 두 가지 모두에 해당하지만 올바른 쿼리를 작성하기위한 대리 또는 임시 방편은 아닙니다.

내가 DISTINCT를 싫어하는 이유 에서 :

제 생각에 상황이 악화되기 시작하는 곳은 개발자가 실질적인 쿼리를 작성하고 테이블을 함께 결합 할 때 갑자기 그가 중복 (또는 더 많은) 행과 즉각적인 응답을 얻는 것처럼 보인다는 것을 깨닫는 것입니다. 이 "문제"에 대한 그의 "솔루션"은 DISTINCT 키워드를 사용하고 POOF 그의 모든 문제를 해결하는 것입니다.

5. 조인보다 집계 선호

데이터베이스 응용 프로그램 개발자의 또 다른 일반적인 실수는 조인에 비해 GROUP BY

이것이 얼마나 널리 퍼져 있는지 알려 드리기 위해 저는 여기에이 주제에 대해 여러 번 글을 썼고 이에 대해 많이 비추천했습니다. 예를 들면 :

From SQL 문- "join"대 "group by and having" :

첫 번째 쿼리 :

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

쿼리 시간 : 0.312 초

두 번째 쿼리 :

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

쿼리 시간 : 0.016 초

맞습니다. 내가 제안한 조인 버전 은 집계 버전보다 20 배 빠릅니다.

6.보기를 통해 복잡한 쿼리를 단순화하지 않음

모든 데이터베이스 벤더가 뷰를 지원하는 것은 아니지만, 뷰를 지원하는 경우 신중하게 사용하면 쿼리를 크게 단순화 할 수 있습니다. 예를 들어, 한 프로젝트에서 CRM에 일반 당사자 모델을 사용했습니다. 이것은 매우 강력하고 유연한 모델링 기술이지만 많은 조인으로 이어질 수 있습니다. 이 모델에는 다음이 있습니다.

• 파티 : 사람과 조직;
• 당사자 역할 : 당사자가 수행 한 작업 (예 : 직원 및 고용주)
• 당사자 역할 관계 : 해당 역할이 서로 관련되는 방식.

예:

• Ted는 Party의 하위 유형 인 Person입니다.
• Ted는 많은 역할을 가지고 있으며 그중 하나는 직원입니다.
• 인텔은 조직이며 당사자의 하위 유형입니다.
• 인텔은 많은 역할을하고 있으며 그중 하나는 고용주입니다.
• 인텔은 Ted를 사용합니다. 즉, 각각의 역할간에 관계가 있습니다.

그래서 Ted를 그의 고용주와 연결하기 위해 결합 된 5 개의 테이블이 있습니다. 모든 직원이 개인 (조직이 아님)이라고 가정하고 다음 도우미보기를 제공합니다.

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

그리고 갑자기 원하는 데이터를 매우 간단하게 볼 수 있지만 매우 유연한 데이터 모델이 있습니다.

7. 입력을 살균하지 않음

이것은 거대한 것입니다. 이제 저는 PHP를 좋아하지만 무엇을하는지 모르면 공격에 취약한 사이트를 만드는 것이 정말 쉽습니다. 작은 Bobby Tables 의 이야기보다 더 잘 요약하는 것은 없습니다.

URL, 양식 데이터 및 쿠키 를 통해 사용자가 제공 한 데이터는 항상 적대적인 것으로 취급되고 삭제되어야합니다. 기대하는 것을 얻고 있는지 확인하십시오.

8. 준비된 진술을 사용하지 않음

WHERE 절에 사용 된 데이터를 뺀 쿼리를 컴파일 한 다음 나중에 제공 할 때입니다. 예를 들면 :

SELECT * FROM users WHERE username = 'bob'

vs

SELECT * FROM users WHERE username = ?

또는

SELECT * FROM users WHERE username = :username

플랫폼에 따라.

이 작업을 통해 데이터베이스가 무릎을 꿇는 것을 보았습니다. 기본적으로 모든 최신 데이터베이스가 새 쿼리를 만날 때마다 컴파일해야합니다. 이전에 본 쿼리가 발생하면 데이터베이스에 컴파일 된 쿼리와 실행 계획을 캐시 할 수있는 기회를 제공합니다. 쿼리를 많이 수행하면 데이터베이스가이를 파악하고 그에 따라 최적화 할 수있는 기회를 갖게됩니다 (예 : 컴파일 된 쿼리를 메모리에 고정).

준비된 문을 사용하면 특정 쿼리가 사용되는 빈도에 대한 의미있는 통계도 제공됩니다.

준비된 문은 또한 SQL 주입 공격으로부터 사용자를 더 잘 보호합니다.

9. 충분히 정규화하지 않음

데이터베이스 정규화 는 기본적으로 데이터베이스 디자인을 최적화하는 프로세스 또는 데이터를 테이블로 구성하는 방법입니다.

이번 주에 누군가가 배열을 파열하여 데이터베이스의 단일 필드에 삽입 한 코드를 발견했습니다. 정규화는 해당 배열의 요소를 자식 테이블의 별도 행으로 처리하는 것입니다 (예 : 일대 다 관계).

이것은 또한 사용자 ID 목록을 저장하는 최상의 방법으로 나왔습니다.

다른 시스템에서 목록이 직렬화 된 PHP 배열에 저장되는 것을 보았습니다.

그러나 정규화의 부족은 여러 형태로 나타납니다.

더:

• 정규화 : 얼마나 멀리 떨어져 있습니까?
• SQL by Design : 데이터베이스 정규화가 필요한 이유

10. 너무 많은 정규화

이것은 이전 요점과 모순되는 것처럼 보일 수 있지만 많은 것들과 마찬가지로 정규화는 도구입니다. 그것은 목적을위한 수단이지 그 자체가 목적이 아닙니다. 많은 개발자들이 이것을 잊어 버리고 "수단"을 "끝"으로 취급하기 시작한다고 생각합니다. 단위 테스트가 이에 대한 대표적인 예입니다.

저는 한때 클라이언트를위한 거대한 계층 구조를 가진 시스템에서 일한 적이 있습니다.

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

의미있는 데이터를 얻으려면 약 11 개의 테이블을 함께 조인해야했습니다. 너무 멀리 떨어진 정규화의 좋은 예였습니다.

요컨대, 신중하고 고려 된 비정규 화는 성능에 큰 이점을 줄 수 있지만이 작업을 수행 할 때는 정말 조심해야합니다.

더:

• 너무 많은 데이터베이스 정규화가 나쁜 일이 될 수있는 이유
• 데이터베이스 디자인에서 정규화는 얼마나 걸리나요?
• SQL 데이터베이스를 정규화하지 않는 경우
• 정규화가 정상이 아닐 수 있음
• 코딩 공포에 대한 모든 데이터베이스 정규화 논쟁의 어머니

11. 독점적 인 호 사용

배타적 아크는 둘 이상의 외래 키를 사용하여 테이블을 생성하는 일반적인 실수이며 그중 하나만 널이 아닐 수 있습니다. 큰 실수. 한 가지는 데이터 무결성을 유지하는 것이 훨씬 더 어려워집니다. 결국, 참조 무결성이 있어도 둘 이상의 외래 키가 설정되는 것을 막는 것은 없습니다 (복잡한 검사 제약에도 불구하고).

실용적인 가이드에서 관계형 데이터베이스 설계까지 :

코드를 작성하는 것이 어색하고 유지 관리 문제가 더 많이 발생할 수 있으므로 가능한 한 독점적 인 아크 생성에 대해 강력히 권고했습니다.

12. 쿼리에 대한 성능 분석을 전혀 수행하지 않음

실용주의는 특히 데이터베이스 세계에서 최고를 지배합니다. 원칙이 교리가되었다는 원칙을 고수한다면 아마도 실수를했을 것입니다. 위에서 집계 쿼리의 예를 살펴 보겠습니다. 집계 버전은 "좋아"보일 수 있지만 성능이 좋지 않습니다. 성능 비교는 논쟁을 끝냈어야했지만 (그러나 그렇지 않았 음) 요점은 더 중요합니다. 애초에 그러한 정보가 부족한 견해를 내세우는 것은 무지하고 위험 할 수도 있습니다.

13. UNION ALL 및 특히 UNION 구조에 대한 과도한 의존

SQL 용어의 UNION은 일치하는 데이터 세트를 연결하기 만합니다. 즉, 동일한 유형과 열 수를가집니다. 그들 사이의 차이점은 UNION ALL은 단순한 연결이며 가능한 한 선호되어야하는 반면 UNION은 중복 된 튜플을 제거하기 위해 암시 적으로 DISTINCT를 수행합니다.

DISTINCT와 같은 UNION이 그 자리를 차지합니다. 유효한 신청이 있습니다. 그러나 특히 하위 쿼리에서 많은 작업을 수행하는 경우 아마도 뭔가 잘못하고있는 것입니다. 쿼리 구성이 잘못되었거나 데이터 모델이 잘못 설계되어 이러한 작업을 수행해야하는 경우 일 수 있습니다.

특히 조인 또는 종속 하위 쿼리에 사용될 때 UNION은 데이터베이스를 손상시킬 수 있습니다. 가능한 한 피하십시오.

14. 쿼리에서 OR 조건 사용

이것은 무해한 것처럼 보일 수 있습니다. 결국 AND는 괜찮습니다. 아니면 괜찮을까요? 잘못된. 기본적으로 AND 조건은 데이터 세트를 제한 하는 반면 OR 조건은 데이터 세트를 증가 시키지만 최적화에 적합하지는 않습니다. 특히 다른 OR 조건이 교차하여 최적화 프로그램이 결과에 대한 DISTINCT 연산을 효과적으로 수행 할 수있는 경우.

나쁜:

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

보다 나은:

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

이제 SQL 최적화 프로그램이 첫 번째 쿼리를 두 번째 쿼리로 효과적으로 전환 할 수 있습니다. 그러나 그렇지 않을 수도 있습니다. 그냥 하지마.

15. 고성능 솔루션에 적합하도록 데이터 모델을 설계하지 않음

이것은 정량화하기 어려운 점입니다. 일반적으로 그 효과로 관찰됩니다. 비교적 간단한 작업에 대한 질의를 작성하거나 비교적 간단한 정보를 찾기위한 쿼리가 효율적이지 않다면 데이터 모델이 좋지 않을 수 있습니다.

어떤면에서이 요점은 모든 이전 항목을 요약하지만 쿼리 최적화와 같은 작업을 두 번째로 수행해야 할 때 먼저 수행하는 경우가 많다는 경고 이야기입니다. 무엇보다도 성능을 최적화하기 전에 좋은 데이터 모델이 있는지 확인해야합니다. Knuth가 말했듯이 :

조기 최적화는 모든 악의 근원입니다

16. 데이터베이스 트랜잭션의 잘못된 사용

특정 프로세스에 대한 모든 데이터 변경은 원자 적이어야합니다. 즉, 작업이 성공하면 완전히 수행됩니다. 실패하면 데이터는 변경되지 않습니다. - '반 완료'변경 가능성이 없어야합니다.

이상적으로이를 달성하는 가장 간단한 방법은 전체 시스템 설계가 단일 INSERT / UPDATE / DELETE 문을 통해 모든 데이터 변경을 지원하도록 노력하는 것입니다. 이 경우 데이터베이스 엔진이 자동으로 처리해야하므로 특별한 트랜잭션 처리가 필요하지 않습니다.

그러나 어떤 프로세스에서 데이터를 일관된 상태로 유지하기 위해 여러 명령문을 하나의 단위로 수행해야하는 경우 적절한 트랜잭션 제어가 필요합니다.

• 첫 번째 명령문 전에 트랜잭션을 시작하십시오.
• 마지막 문 이후 트랜잭션을 커밋합니다.
• 오류가 발생하면 트랜잭션을 롤백하십시오. 그리고 매우 NB! 오류 뒤에 오는 모든 명령문을 건너 뛰거나 중단하는 것을 잊지 마십시오.

또한 이와 관련하여 데이터베이스 연결 계층과 데이터베이스 엔진이 상호 작용하는 방법의 세부 사항에주의를 기울이는 것이 좋습니다.

17. '집합 기반'패러다임을 이해하지 못함

SQL 언어는 특정 종류의 문제에 적합한 특정 패러다임을 따릅니다. 다양한 벤더별 확장에도 불구하고이 언어는 Java, C #, Delphi 등과 같은 언어에서 사소한 문제를 처리하는 데 어려움을 겪습니다.

이러한 이해 부족은 몇 가지 방식으로 나타납니다.

• databse에 너무 많은 절차 적 또는 명령 적 논리를 부적절하게 부과합니다.
• 부적절하거나 과도한 커서 사용. 특히 단일 쿼리로 충분할 때.
• 트리거가 다중 행 업데이트에 영향을받는 행당 한 번 실행된다고 잘못 가정합니다.

책임의 명확한 구분을 결정하고 적절한 도구를 사용하여 각 문제를 해결하도록 노력하십시오.

출처 : https://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers