[database] 응용 프로그램 개발자의 데이터베이스 개발 실수

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

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

2. 참조 무결성을 강제하지 않음

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

MySQL 데이터베이스에서이 실패를 보는 것이 일반적입니다. 나는 MyISAM이 그것을 지원한다고 생각하지 않습니다. InnoDB는 않습니다. MyISAM을 사용하거나 InnoDB를 사용하지만 사용하지 않는 사람들을 찾을 수 있습니다.

여기 더 :

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

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

자연 키는 (명시 적으로) 고유 한 외부 적으로 의미있는 데이터를 기반으로하는 키입니다. 일반적인 예로는 제품 코드, 2 자리 상태 코드 (미국), 주민등록번호 등이 있습니다. 대리 또는 기술 기본 키는 시스템 외부에서 전혀 의미가없는 키입니다. 그것들은 순수하게 엔티티를 식별하기 위해 발명되었으며 일반적으로 자동 증가 필드 (SQL Server, MySQL 등) 또는 시퀀스 (주로 Oracle)입니다.

내 의견으로는 항상 대리 키를 사용해야합니다. 이 문제는 다음과 같은 질문에서 나타났습니다.

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

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

유고 슬라비아 와 같은 국가 도 존재하지 않을 수 있습니다 .

4. DISTINCT작동 해야하는 쿼리 작성

ORM 생성 쿼리에서 종종이 문제가 나타납니다. 최대 절전 모드의 로그 출력을 보면 모든 쿼리가 다음과 같이 시작됩니다.

SELECT DISTINCT ...

이것은 중복 행을 반환하지 않으므로 중복 객체를 얻도록하는 약간의 지름길입니다. 때때로 사람들도이 일을하는 것을 보게 될 것입니다. 당신이 그것을 너무 많이 보면 그것은 진짜 붉은 깃발입니다. 하지 그DISTINCT 나쁜 또는 유효한 응용 프로그램이 없습니다. 그것은 (두 가지 모두에서) 수행하지만 올바른 쿼리를 작성하기위한 대리 또는 정지가 아닙니다.

에서 내가 DISTINCT 증오하는 이유 :

내 의견으로는 상황이 악화되기 시작하는 곳은 개발자가 실질적인 쿼리를 작성하고 테이블을 결합 할 때 갑자기 중복 (또는 더 많은) 행과 즉각적인 응답을 얻는 것처럼 보입니다 . 이 “문제”에 대한 그의 “해결책”은 DISTINCT 키워드를 던지고
그의 모든 문제는 사라집니다.

5. 조인에 대한 집계 선호

데이터베이스 응용 프로그램 개발자의 또 다른 일반적인 실수는 GROUP BY조인과 비교할 때 훨씬 더 비싼 집계 (즉, 절)를 인식하지 못하는 것 입니다.

이것이 얼마나 널리 퍼져 있는지에 대한 아이디어를 제공하기 위해이 주제에 대해 여러 번 글을 썼으며 많은 찬사를 받았습니다. 예를 들면 다음과 같습니다.

에서 SQL 문 – “로와 가진 그룹”대 “가입” :

첫 번째 질문 :

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.016s

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

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

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

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

예:

• 테드는 당사자의 하위 유형 인 개인입니다.
• Ted에는 많은 역할이 있으며 그 중 하나는 Employee입니다.
• 인텔은 당사국의 하위 유형 인 조직입니다.
• 인텔에는 많은 역할이 있으며 그 중 하나는 고용주입니다.
• 인텔은 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가 마음에 들지만 수행중인 작업을 모르는 경우 공격에 취약한 사이트를 만드는 것이 매우 쉽습니다. 작은 바비 테이블 이야기 보다 더 좋은 것은 없습니다. .

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 : 데이터베이스 정규화가 필요한 이유

10. 너무 많은 정규화

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

한때 클라이언트와 같은 거대한 계층 구조를 가진 시스템에서 일했습니다.

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

따라서 의미있는 데이터를 얻기 전에 약 11 개의 테이블을 결합해야했습니다. 정규화가 너무 멀리 취해진 좋은 예였습니다.

요컨대, 신중하고 고려 된 비정규 화는 성능상의 이점이 크지 만이를 수행 할 때는 실제로주의해야합니다.

더:

• 너무 많은 데이터베이스 정규화가 나쁜 것일 수있는 이유
• 데이터베이스 디자인에서 표준화를 어느 정도 수행해야합니까?
• SQL 데이터베이스를 정규화하지 않을 때
• 아마도 정규화가 정상이 아닐 수도 있습니다
• 모든 데이터베이스 정규화의 어머니가 코딩 공포에 대해 토론하다

11. 전용 아크 사용

배타적 아크는 둘 이상의 외래 키를 사용하여 테이블을 만들 때 흔히 발생하는 실수입니다. 단 하나의 키만 null이 될 수 없습니다. 큰 실수. 우선 데이터 무결성을 유지하는 것이 훨씬 더 어려워집니다. 결국 참조 무결성이 있어도 둘 이상의 외래 키가 설정되는 것을 막을 수있는 것은 없습니다 (복잡한 검사 제한 조건에도 불구하고).

에서 관계형 데이터베이스 디자인에 대한 실용 가이드 :

코드를 작성하기가 어려울 수 있고 유지 보수가 더 어려울 수 있기 때문에 가능한 한 독점적 아크 구성에 대해 강력히 권고했습니다.

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. 고성능 솔루션에 적합하도록 데이터 모델을 설계하지 않음

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

어떤면에서는이 점이 이전의 모든 것을 요약하지만 쿼리 최적화와 같은 작업은 종종 두 번째로 수행해야 할 때 먼저 수행된다는 것이 더 조심스러운 이야기입니다. 무엇보다도 성능을 최적화하기 전에 올바른 데이터 모델이 있는지 확인해야합니다. 크 누스가 말했듯이 :

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

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

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

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

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

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

이와 관련하여 데이터베이스 연결 계층 및 데이터베이스 엔진이 상호 작용하는 방식의 하위 조직에도주의를 기울이는 것이 좋습니다.

17. ‘세트 기반’패러다임을 이해하지 못한다

SQL 언어는 특정 종류의 문제에 적합한 특정 패러다임을 따릅니다. 다양한 공급 업체별 확장에도 불구하고이 언어는 Java, C #, Delphi 등의 언어에서 사소한 문제를 해결하기 위해 어려움을 겪고 있습니다.

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

• 데이터베이스에 너무 많은 절차 적 또는 명령 적 논리를 부적절하게 부과합니다.
• 커서가 부적절하거나 과도하게 사용되었습니다. 특히 단일 쿼리로 충분할 때.
• 여러 행 업데이트에서 영향을받는 행당 한 번만 트리거가 발생한다고 잘못 가정합니다.

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

답변

select e.first_name, e.last_name
from employee e
where e.start_date >
        (select max(ds.transaction_date)
         from daily_sales ds
         where ds.store_id = e.store_id and
               ds.total < 10000)

select e.first_name, e.last_name
from employee e,
     (select ds.store_id,
             max(s.transaction_date) transaction_date
      from daily_sales ds
      where ds.total < 10000
      group by s.store_id) dsx
where e.store_id = dsx.store_id and
      e.start_date > dsx.transaction_date