-
SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법QL책책책 책을 읽읍시다/프로그래밍 2023. 2. 23. 15:58
저자 : 빌 카윈
옮긴이 : 윤성준
표지 DBA가 아닌 프로그래머의 애플리케이션 개발 레벨에서의 자주 잘못 쓰는 SQL문 방식이나 테이블 설계에 대해 문제점과 대안을 제시하는 책이다. 어디서 봤더라. 2년전에 애플리케이션 개발하는데 SQL 종속적이지 않게 이끌어주는 책으로 잘 못 보아 구매했었다. SQL AntiPatterns가 아닌 Anti SQL Patterns로 잘 못 본 것이었다. 당시 있던 회사 서비스의 심각한 DB 종속적인 구조 때문에 스트레스를 많이 받았었고, 어떻게 뜯어 고칠까를 계속 고민했었다. 비즈니스 로직의 반이 쿼리나 스토어드 프로시저에 있었고, 자바는 이렇게 나온 데이터를 매핑해주는 역할에 그치는 경우가 많았다. 몇백줄에 이르는 쿼리나 프로시저 수정은 항상 부담이었고, 테스트는 더더욱 힘들었다. 결국 레거시라는 괴물은 JPA로 일부 뜯어내는데 성공했는데 그때 이 책도 도움이 될 것 같아 구매했...다가 이제서야 읽었다. 해당 프로젝트의 쿼리들이 책에서 소개된 안티패턴들에 많이 해당돼 그 때 읽었으면 더 도움이 되었을 것 같다.
개인적인 경험에 비추어 현업에서 꽤 쓸만한 내용이다 싶은 주제만 정리해보았다.
이 책에서 다루는 예제 데이터베이스
소프트웨어 버그를 처리하는 협업 툴을 개발한다고 가정한다.
예제 ERD CREATE TABLE Accounts ( account_id SERIAL PRIMARY KEY, account_name VARCHAR(20), first_name VARCHAR(20), last_name VARCHAR(20), email VARCHAR(100), password_hash char(64), portrait_image BLOB, hourly_rate NUMERIC(9,2) ); CREATE TABLE BugStatus ( status VARCHAR(20) PRIMARY KEY ); CREATE TABLE Bugs ( bug_id SERIAL PRIMARY KEY, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), resolution VARCHAR(1000), reported_by BIGINT UNSIGNED NOT NULL, assigned_to BIGINT UNSIGNED, verified_by BIGINT UNSIGNED, status VARCHAR(20) NOT NULL DEFAULT 'NEW', priority VARCHAR(20), hours NUMERIC(9,2), FOREIGN KEY (reported_by) REFERENCES Accounts(account_id), FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id), FOREIGN KEY (verified_by) REFERENCES Accounts(account_id), FOREIGN KEY (status) REFERENCES BugStatus(status) ); CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, bug_id BIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (author) REFERENCES Accounts(account_id) ); CREATE TABLE Screenshots ( bug_id BIGINT UNSIGNED NOT NULL, image_id BIGINT UNSIGNED NOT NULL, screenshot_image BLOB, caption VARCHAR(100), PRIMARY KEY (bug_id, image_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); CREATE TABLE Tags ( bug_id BIGINT UNSIGNED NOT NULL, tag VARCHAR(20) NOT NULL, PRIMARY KEY (bug_id, tag), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); CREATE TABLE Products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(50) ); CREATE TABLE BugsProducts ( bug_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (bug_id, product_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) );발췌본 정리
8장 다중 칼럼 속성
8.1 목표
tag 테이블이 없다고 가정하고, bug 데이터에 태그를 추가해 버그를 분류해야 한다. 어떤 버그는 인쇄, 리포트, 이메일과 같이 해당 버그가 영향을 미치는 소프트웨어 서브시스템에 따라 분류될 것이다. 어떤 버그는 결함의 성질에 따라 분류될 수도 있다. 예를 들어 프로그램이 죽는 버그는 crash 태그를 달고, 속도가 느린 문제는 performance 태그를 달고, 사용자 인터페이스에서 색상 선택이 부적절하다면 cosmetic 태그를 달 수 있다. 태그는 상호 배타적일 필요가 없기 때문에 여러 태그를 다는 것도 가능해야 한다. 어떤 결함은 여러 시스템에 영향을 미치거나 인쇄 속도에 영향을 줄 수 있다.
8.2 안티 패턴: 여러 개의 칼럼 생성
속성에 여러 값이 들어가는 것을 고려해야 하지만, 각 칼럼에는 하나의 값만을 저장해야 한다는 것을 안다. 테이블에 여러 개의 칼럼을 만들고 각 칼럼에 하나의 태그를 저장하게 하는 것이 자연스러워 보인다.
CREATE TABLE BugAntiPattern ( bug_id SERIAL PRIMARY KEY, description VARCHAR(1000), tag1 VARCHAR(20), tag2 VARCHAR(20), tag3 VARCHAR(20) );주어진 버그에 태그를 달 때, 이 세 칼럼 중 하나에 값을 넣는다. 사용되지 않는 칼럼은 NULL인 상태로 남는다.
bug_id description tag1 tag2 tag3 1234 저장 시 죽어버림 crash NULL NULL 3456 성능을 개선해야 함 printing performance NULL 5678 XML 지원해야 함 NULL NULL NULL 이제 일반적인 속성에서는 쉽게 할 수 있었던 작업이 훨씬 복잡해진다.
값 검색
주어진 태그를 가진 버그를 찾으려면 세 칼럼을 모두 확인해야 한다. 셋 중 어느 칼럼에 태그 문자열이 있는지 알 수 없기 때문이다. 예를 들어, performance 태그를 가진 버그를 조회하려면 다음과 같은 쿼리를 사용해야 한다.
SELECT * FROM BugAntiPattern WHERE tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance';값 추가와 삭제
칼럼 집합에 값을 추가하거나 삭제하는 것도 문제다. 어느 칼럼이 비어있는지(비어있는 게 있다면) 알 수 없기 때문에 단순히 UPDATE를 사용해 칼럼 중 하나를 변경하는 것은 안전하지 않다. 확인을 위해 애플리케이션에서 해당 행을 조회해야 할 것이다.
SELECT * FROM BugAntiPattern WHERE bug_id = 3456;이 경우(표 참조)에는 tag3이 NULL인 것을 확인할 수 있고, 이렇게 확인한 후 UPDATE 문을 만들 수 있다.
UPDATE BugAntiPattern SET tag3 = 'performance' WHERE bug_id = 3456;테이블을 조회한 다음 업데이트를 하기 전에 다른 클라이언트가 같은 행을 업데이트하기 위해 동일한 절차를 실행하고 있었다면 문제가 생긴다. 누가 먼저 업데이트를 했느냐에 따라, 둘 중 하나는 충돌로 인해 업데이트에 실패하거나 변경 내용을 덮어쓸 수 있다. 복잡한 SQL 포현을 사용하면 이 두 단계 작업을 피할 수 있다.
UPDATE BugAntiPattern SET tag1 = NULLIF(tag1, 'performance'), tag2 = NULLIF(tag2, 'performance'), tag3 = NULLIF(tag3, 'performance') WHERE bug_id = 3456;유일성 보장
여러 칼럼에 동일한 값이 나타나지 않게 하고 싶겠지만, 다중 칼럼 속성 안티패턴을 사용하는 경우에는 데이터베이스에서 이를 예방하지 못한다. 즉 다음과 같은 문장이 실행되는 것을 방지하지 못한다는 말이다.
INSERT INTO BugAntiPattern (description, tag1, tag2, tag3) VALUES ('printing is slow', 'printing', 'performance', 'performance');값의 수 증가 처리
이 설계의 또 다른 단점은 칼럼 세 개가 모자랄 수도 있다는 것이다. 한 칼럼에 하나의 값을 유지하기 위해서는 버그가 가질 수 있는 태그의 최대 개수만큼 칼럼을 정의해야 한다. 테이블을 정의하는 시점에 태그의 최대 개수가 얼마나 될지 어떻게 예측할 수 있겠는가? tag4, tag5와 같이 필요한 시점에 늘리는 것도 방법이지만 다음과 같은 이유로 비용이 많이 든다.
- 이미 데이터를 포함하고 있는 데이터베이스 테이블 구조를 변경하려면 테이블 전체를 잠금 설정하고 다른 틀라리언트의 접근을 차단하는 과정이 필요하다.
- 어떤 데이터베이스는 희망하는 구조의 새로운 테이블을 정의해 예전 테이블에서 모든 데이터를 복사한 다음 예전 테이블을 삭제하는 식으로 테이블 변경을 구현한다. 테이블에 많은 데이터가 쌓여 있다면 작업에 많은 시간이 걸린다.
- 다중 칼럼 속성의 집합에 칼럼을 추가할 경우, 모든 애플리케이션에서 이 테이블을 참조하는 모든 SQL 문을 확인해 새로운 칼럼을 지원하도록 수정해야 한다.
SELECT * FROM BugAntiPattern WHERE tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance' OR tag4 = 'performance'; -- 이 항목을 새로 추가해야 한다.8.3 안티패턴 인식 방법
사용자 인터페이스나 문서에 여러 개의 값을 할당할 수 있지만 최대 개수가 제한되어 있는 속성이 기술되어 있다면, 다중 칼럼 속성 안티패턴이 사용되고 있음을 나타내는 것으로 볼 수 있다.
물론 선택할 수 있는 최대 개수를 의도적으로 제한하는 속성이 있을 수 있다는 것은 인정하지만, 이런 제한이 없는 경우가 더 일반적이다. 이런 제한이 자의적이거나 납득하기 어렵다면, 이 안티패턴 때문일 수 있다.
이 안티패턴이 사용되고 있음을 나타내는 다른 징조는 다음과 같은 말이 들릴 때다.
- "태그를 최대 몇 개까지 붙일 수 있도록 지원해야 하지?" 태그와 같은 다중 값 속성을 위해 테이블에 얼마나 많은 칼럼을 정의해야 하는지를 결정하려는 것이다.
- "SQL에서 여러 칼럼을 한꺼번에 검색하려면 어떻게 해야 하지?" 주어진 값을 여러 칼럼에 걸쳐 검색해야 한다면, 이들 칼럼은 실제로 하나의 논리적 속성으로 저장되어야 함을 나타낸다.
8.4 안티패턴 사용이 합당한 경우
어떤 경우에는, 속성의 개수가 고정되고 선택의 위치나 순서가 중요할 수 있다. 예를 들어, 주어진 버그가 여러 사용자 계정과 연관될 수 있지만, 각 연관은 본질적으로 유일할 수 있다. 하나는 버그를 보고한 사용자, 다른 하나는 버그 수정을 위해 할당된 프로그래머, 나머지는 수정을 검증하기 위해 할당된 QA 담당자가 될 수 있다. 각 칼럼에 들어가는 값은 같은 종류지만, 그 의미와 사용처가 달라 논리적으로 다른 속성이 된다.
이런 세 속성을 저장하기 위해 Bugs 테이블에 보통 칼럼 세 개를 정의하는 것은 합당할 것이다. 여기에는 위에서 설명한 결점이 중요하지 않다. 세 속성을 따로따로 사용하기 때문이다. 때로는 주어진 버그와 관련된 모든 사람을 확인해야 하는 경우와 같이, 이 세 칼럼을 모두 조회해야 하는 경우가 있을 수 있다. 그러나 다른 대부분의 경우가 단순해짐ㅇ르 고려하면, 이런 몇몇 경우에 대해서는 복잡성을 수용할 만하다.
8.5 해법: 종속 테이블 생성
다중 값 속성을 위한 칼럼을 하나 가지는 종속 테이블을 만드는 것이다. 여러 개의 값을 여러 개의 칼럼 대신 여러 개의 행에 저장하는 것이다. 또한 종속 테이블에 FK를 정의해 해당 값이 Bugs 테이블의 부모 행과 연관되도록 한다.
CREATE TABLE Tags ( bug_id BIGINT UNSIGNED NOT NULL, tag VARCHAR(20) NOT NULL, PRIMARY KEY (bug_id, tag), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) );하나의 버그에 연관된 모든 태그가 한 칼럼에 있으면, 주어진 태그에 대한 버그를 검색하는 작업이 좀더 직관적이 된다.
SELECT * FROM Bugs JOIN Tags USING (bug_id) WHERE tag = 'performance';두 개의 태그가 붙은 버그를 찾는 것과 같은, 보다 복잡한 작업도 쉽게 처리할 수 있다.
SELECT * FROM Bugs JOIN Tags AS t1 USING (bug_id) JOIN Tags AS t2 USING (bug_id) WHERE t1.tag = 'printing' AND t2.tag = 'performance';연관을 추가하고 삭제하기도 다중 칼럼 안티패턴을 사용할 때보다 훨씬 쉬워진다. 단순히 종ㅎ속 테이블에 행을 추가하거나 삭제하면 된다. 여러 칼럼을 검사해 값을 추가할 곳을 찾을 필요가 없다.
PK 제약조건은 중복이 허용되지 않음을 보장한다. 주어진 태그는 특정 버그에 한 번만 적용될 수 있다. 중복된 태그 적용을 시도하면, SQL 실행 시 에러가 발생할 것이다.
Bugs 테이블에 태그 칼럼이 세 개만 있을 때처럼 태그의 개수가 세 개로 제한되지도 않는다. 버그에 필요한 만큼 태그를 적용할 수 있다.
10장 반올림 오류
버그 수정에 들어간 작업 시간에 기초해 프로그래머 비용을 계산한 보고서를 만들어 달라는 요청이 왔다. Accounts 테이블에 있는 각 프로그래머는 시간당 비용이 다르기 때문에, Bugs 테이블에 있는 버그를 수정하는 데 각각 몇 시간이 필요한지 기록하고, 여기에 해당 버그에 할당된 프로그래머의 hourly_rate를 곱한다.
SELECT b.bug_id , b.hours * a.hourly_rate AS cost_per_bug FROM Bugs AS b JOIN Accounts a ON (b.assigned_to = a.account_id);이 쿼리를 지원하기 위해, Bugs와 Accounts 테이블에 새로운 칼럼을 추가해야 한다. 비용을 정확하게 추적하기 위해, 두 칼럼 모두 소수를 지원해야 한다. 새로운 칼럼은 소수 값을 저장할 수 있는 데이터 타입인 FLOAT을 사용하기로 결정한다.
ALTER TABLE Bugs ADD COLUMN hours FLOAT; ALTER TABLE Accounts ADD COLUMN hourly_rate FLOAT;불행하게도 쿼리로 뽑은 보고서는 실제 계산과 맞지 않는다.
10.1 목표: 정수 대신 소수 사용
정수보다 정밀하고 산술 연산에 사용할 수 있는 숫자 타입을 사용하며, 산술 연산의 결과가 정확해야 한다.
안티패턴: FLOAT 데이터 타입 사용
SQL의 FLOAT 데이터 타입은 다른 프로그래밍 언어의 float과 마찬가지로 IEEE 754 표준(https://en.wikipedia.org/wiki/IEEE_754-1985)에 따라 실수를 이진 형식으로 부호화한다. 이 데이터 타입을 효과적으로 사용하려면 이 형식의 부동 소수점 수 특성을 이해할 필요가 있다.
필요에 의한 반올림
IEEE 754는 부동 소수점 수를 밑수가 2인 형식으로 표현한다. 이진수로 무한한 정도를 요하는 값과 십진수에서 무한한 정도를 요구하는 수는 다르다. 59.95와 같이 십진수에서 유한한 정도를 가지는 값을 이진수로 표현하려면 무한한 정도가 필요하다. FLOAT 데이터는 이렇게 할 수 없으므로, 밑수를 2로 하는 가장 가까운 값을 사용해 저장하는데, 이는 밑수를 10으로 했을 때 59.950000762939와 같다.
어떤 값들은 두 형식에서 모두 유한한 정도로 표현된다. 이론적으로는 IEEE 754 형식에서 숫자가 어떻게 저장되는지를 자세히 이해하면, 십진수 값이 이진수에서 어떻게 표현될지를 예상할 수 있다. 그러나 실제로 부동 소수점 수에 대해 이런 계산을 하는 사람은 거의 없을 것이다. 데이터베이스에 FLOAT으로 오차 없이 표현되는 값만 저장되어 있다고 할 수는 없으므로, 애플리케이션에서는 이런 칼럼에 있는 어떤 값이든 반올림되었다고 가정해야 한다.
SQL에서 FLOAT 사용
어떤 데이터베이스에서는 부정확한 값을 보정해, 의도한 값을 표현해주지만 IEEE 754의 이진 형식에 따라 유한 정도로 표현 가능한 값으로 반올림되어 표시되는 것이다. Mysql은 아래와 같이 애초에 근사값으로 저장된다.
INSERT INTO AccountsAntiPatterns values (123, 59.95); SELECT * FROM AccountsAntiPatterns;
mysql에서 FLOAT 데이터 타입에 소수를 저장하고 조회한 결과 그러므로 hourly_rate에 59.95로 동등 비교로 데이터를 찾으려고 하면 어떤 행도 나오지 않는다.
SELECT * from AccountsAntiPatterns WHERE hourly_rate = 59.95;
mysql FLOAT 동등 비교할 때 결과 없음 이 문제를 회피하는 흔한 방법은 두 부동 소수점 값이 일정 수준 이상 충분히 가까우면 '사실상 같은'값으로 다루는 것이다. 두 값의 차를 구한 후 SQL의 ABS() 함수를 이용해 절대 값을 만든다. 이 결과가 0이면 두 값은 정확하게 같은 것이다. 이 결과가 충분히 작다면 두 값을 사실상 같은 것으로 다룰 수 있다.
SELECT * FROM AccountsAntiPatterns WHERE ABS(hourly_rate - 59.95) < 0.000001;
FLOAT 데이터 타입 근사치로 비교하여 조회 결과 있음 그러나 두 값의 차는 충분히 커서, 정도를 높이면 여전히 결과를 얻는 데 실패한다.
SELECT * FROM AccountsAntiPatterns WHERE ABS(hourly_rate - 59.95) < 0.000000001;
다시 결과 없음 ㅠㅠ 적절한 기준은 상황에 따라 다르다. 차의 절대 값은 십진수에서의 값과 반올림된 이진수에서의 값이 다르기 때문이다. 많은 값을 집계할 때는 상황이 더 심각해진다. 작은 차이가 계속 누적되어 꽤 큰 차이가 나기도 한다.
금융 애플리케이션에서의 복리 계산은 연속한 곱을 적용하는 좋은 예다. 부정확한 부동 소수점 수를 사용하면 처음에는 오차가 아주 작지만, 계산을 반복할수록 오차가 누적되고 문제가 커지게 된다. 따라서 금융 애플리케이션에서는 정확한 값을 사용하는 것이 중요하다.
10.3 안티패턴 인식 방법
FLOAT, REAl, DOUBLE PRECISION 데이터 타입이 사용되는 곳이면 어디든 의심이 간다. 부동 소수점 수를 사용하는 대부분의 애플리케이션에서는 IEEE754 형식이 제공하는 넓은 범위의 값이 필요하지 않다. SQL에서는 FLOAT보다 더 좋은 데이터 타입이 있다.
10.4 안티패턴 사용이 합당한 경우
INTEGER나 NUMERIC 타입이 지원하는 것보다 큰 범위의 실수 값을 사용해야 할 때는 FLOAT이 좋은 데이터 타입이다. FLOAT 사용이 가장 적합한 예는 과학계산용 애플리케이션이다. 참고로 Oracle에서 FLOAT은 정확한 자릿수를 가지는 수치 타입이며, BINARY_FLOAT 타입이 IEEE 754를 사용해 수치를 표현하는 타입이다.
10.5 해법: NUMERIC 데이터 타입 사용
고정 소수점 수에는 FLOAT이나 이와 비슷한 타입을 사용하지 말고, NUMERIC 또는 DECIMAL 타입을 사용해야 한다.
ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9,2); ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9,2);이런 데이터 타입은 칼럼 정의에서 지정한 정도까지 수치를 정확하게 표현한다. VARCHAR 타입에서 길이를 지정하는 것과 비슷한 문법으로, 데이터 타입의 인수로 정도를 지정한다. 정도는 이 칼럼 값이 사용할 수 있는 전체 자릿수다. 정도 9는 123456789와 같은 값은 저장할 수 있지만, 1234567890은 저장할 수 없음을 뜻한다.
데이터 타입의 둘째 인수로 스케일을 지정할 수 있다. 스케일은 소수점 오른쪽의 자릿수다. 이 자릿수는 정도에 포함되기 때문에, 정도가 9고 스케일이 2라면 1234567.89는 저장할 수 있지만 12345678.91 또는 123456.789와 같은 값은 저장할 수 없다.
NUMERIC과 DECIMAL의 장점은 유리수가 FLOAT 타입에서와 같이 반올림되지 않고 저장된다는 것이다. 59.95란 값을 지정하면, 이 값이 정확하게 59.95로 저장된다고 확신할 수 있다.
16장 임의의 선택
매번 무작위로 광고를 선택해 모든 광고주의 광고가 균등한 기회로 보이게 하고 사용자는 같은 광고를 반복적으로 보지 않게 하는 광고 애플리케이션을 개발한다고 하자. 문제는 날이 갈수록 느려진다는 것인데, 운영환경과 비슷한 환경에서 테스트를 반복해 광고 선택 쿼리가 문제임을 알아냈다. 광고 수가 아주 많은 경우 쿼리 성능이 급격하게 떨어진다. 무작위로 광고를 선택하는 이 쿼리를 어떻게 개선할 수 있을까?
16.1 목표: 샘플 행 가져오기
전체 데이터 집합을 애플리케이션으로 가져와 샘플을 고르는 것보다 데이터베이스에 샘플을 요청하는 것이 낫다. 목표는 임의의 샘플 데이터만 리턴하는 효율적인 SQL 쿼리를 작성하는 것이다.
16.2 안티패턴: 데이터를 임의로 정렬하기
쿼리에서 임의의 행을 고르는 가장 흔한 SQL 기법은 데이터를 임의로 정렬한 다음 첫 행을 고르는 것이다. 이 방법은 이해하기도 쉽고 구현하기도 쉽다.
SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;인기있는 방법이긴 하지만, 약점이 금방 드러난다. 이 약점을 이해하기 위해, 먼저 일반적인 정렬과 비교해보자. 일반적인 정렬에서는 칼럼의 값을 비교해 어느 행이 큰 값을 가졌고 어느 행이 작은 값을 가졌는지에 따라 순서를 매긴다. 이런 식의 정렬은 반복적이어서, 여러 번 실행해도 같은 결과를 만든다. 또한 인덱스를 활용할 수도 있다. 인덱스는 본질적으로 특정 칼럼 값으로 미리 정렬한 집합이기 떄문이다.
RAND()와 같은 비결정적 수식으로 정렬하면 인덱스를 활용할 수 없게 된다. RAND() 함수가 리턴하는 값으로 된 인덱스는 없다. 랜덤이 될 때의 요점은 SELECT할 때마다 다르고 예측할 수 없다는 것이다.
이는 쿼리 성능을 고려할 때 문제가 된다. 인덱스를 사용하는 것은 정렬을 빠르게 하는 가장 좋은 방법 중 하나기 때문이다. 인덱스를 사용하지 못하는 결과, 데이터베이스가 쿼리 결과 집합을 직접 정렬해야 한다. 정렬해야 할 데이터가 많은 경우, 쿼리 결과 전체를 임시 테이블에 넣고 물리적으로 행을 교체해가며 정렬해야 한다. 이런 식의 정렬은 인덱스의 도움을 받는 정렬보다 훨씬 느리며, 데이터가 커질수록 성능 차이도 커진다.
임의로 정렬하는 기법의 또 다른 약점은, 전체 데이터 집합을 정렬하는 비용이 많이 드는 작업을 하는데, 이 작업 결과의 대부분이 낭비라는 것이다. 첫 행을 제외한 나머지는 곧바로 버리기 때문이다. 테이블에 수천 개의 행이 있는 경우, 하나의 행만 필요하다면 왜 수천 개의 행을 전부 읽어야 한단 말인가?
적은 데이터에서 쿼리를 실행하면 이런 문제가 눈에 띄지 않기 때문에, 개발하고 테스트하는 동안에는 좋은 방법으로 보일 수 있다. 그러나 시간이 지나면서 데이터베이스의 크기가 커지면, 이런 식의 쿼리는 문제가 되기 마련이다.
16.3 안티패턴 인식 방법
안티패턴에서 보인 기법은 간단하서 많은 프로그래머들이 블로그에서 배우거나 스스로 생각해내서 사용한다. 다음과 같은 말이 들리면 동료들이 안티패턴을 사용하고 있는 것일 수 있다.
- "SQL에서 임의의 행을 리턴하는 건 정말 느려" 임의의 샘플을 서택하기 위한 쿼리는 개발 기간이나 테스트 기간에 얼마 안 되는 데이터에서 실행하면 잘 동작한다. 그러나 실 환경에서 데이터가 늘어나기 시작하면 점점 느려진다. 서버를 튜닝하고 인덱스를 달고, 캐싱을 늘려서 해결할 수 있는 문제가 아니다.
- "애플리케이션에서 사용할 메모리를 어떻게 늘릴 수 있지? 모든 행을 가져와서 그 중 임의로 한 개를 선택해야 해" 모든 데이터를 애플리케이션으로 가져올 필요가 없어야 한다. 이렇게 하는 것은 대부분 낭비다. 게다가 데이터베이스는 애플리케이션 메모리에서 처리할 수 없을 정도로 커지는게 보통이다.
- "어떤 항목이 다른 것보다 좀더 자주 나오는 것 같아 보이지 않나? 랜덤이 별로 랜덤 같지 않은데?" 난수가 데이터베이스의 PK 값의 간격과 동기화되지 않았다.
16.4 안티패턴 사용이 합당한 경우
임의로 정렬하는 방법의 비효율성은 데이터 집합이 적을 때는 참을만하다.
16.5 해법: 테이블 전체 정렬 피하기
임의로 정렬하는 방법은 테이블 스캔과 비용이 많이 드는 수동정렬은 수반한다. SQL을 작성할 때, 이런 비효율적인 쿼리를 경계해야 한다. 최적화할 수 없는 쿼리를 최적화하려고 헛된 노력을 들이기보다는, 새로운 접근방법을 생각하는 편이 낫다.
1과 MAX 사이에서 임의의 키 값 고르기
테이블 정렬을 피하는 방법 중 하나는 1과 PK의 최댓값 사이에서 임의의 값을 선택하는 것이다.
SELECT b1.* FROM Bugs AS b1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id) AS b2 ON (b1.bug_id = b2.rand_id);이 방법은 PK 값이 1부터 시작해 연속적으로 존재한다고 가정한다. 즉, 1과 최댓값 사이에 빈 값이 없다는 뜻이다. 빈 값이 존재하는 경우, 임의로 선택한 값이 테이블의 행과 매치되지 않을 수 있다. 키가 1과 최댓값사이의 모든 값을 사용하는 경우에는 이 방법을 사용할 수 있다.
다음으로 큰 키 값 고르기
1과 최댓값 사이에 빈틈이 있는 경우에는 다음 쿼리를 쓸 수 있다.
SELECT b1.* FROM Bugs AS b1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS bug_id) AS b2 WHERE b1.bug_id >= b2.bug_id ORDER BY b1.bug_id LIMIT 1;이 방법은 빈틈 바로 앞에 있는 키 값이 더 자주 선택된다는 단점이 있다. 랜덤 값은 거의 균일하게 분포하지만, bug_id 값은 그렇지 않기 때문이다. 빈틈이 드믈게 존재하고 모든 키 값이 동일한 빈도로 선택되는 것이 중요하지 않을 때 이 방법을 사용할 수 있다.
모든 키 값의 목록을 구한 다음, 임의로 하나 고르기
결과 집합의 PK 값 하나를 고르는 애플리케이션 코드를 사용할 수 있다. 그런 다음 이 PK 값을 이용해 데이터베이스에서 전체 행을 조회한다. 다음 코드는 이 기법을 PHP로 구현한 것이다.
<?php $bug_id_list = $pdo->query("SELECT bug_id FROM Bugs")->fetchAll(); $rand = random( count($bug_id_list) ); $rand_bug_id = $bug_id_list[$rand]["bug_id"]; $stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id = ?"); $stmt->execute( array($rand_bug_id) ); $rand_bug = $stmt->fetch();이 기법은 테이블 정렬을 피하고, 각 키 값을 거의 같은 확률로 선택하지만, 다른 비용이 든다.
- 데이터베이스로부터 모든 bug_id 값을 불러올 때 리스트 크기가 엄청나게 클 수 있다. 심지어 애플리케이션 메모리 부족 문제가 발생할 수 있다.
- 쿼리를 두 번 해야 한다. 한 번은 PK의 목록을 생성하기 위해, 두 번째는 임의의 행을 가져오기 위해서다. 쿼리가 지나치게 복잡하고 비용이 많이 든다면, 이게 문제가 될 수도 있다.
결과 집합의 크기가 적장하고 단순한 쿼리로 임의의 행을 서택하는 경우에 이 기법을 사용할 수 있다. 이 방법은 불연속적인 목록에서 값을 선택할 때 좋다.
오프셋을 이용해 임의로 고르기
위 방법의 문제를 피할 방법이 있다. 데이터 집합에서 행의 개수를 세고 0과 행 개수 사이의 임의의 수를 고른 다음, 데이터 집합을 쿼리할 때 이 수를 오프셋으로 사용하는 것이다.
<?php $rand = "SELECT ROUND(RAND() * (SELECT COUNT(*) FROM Bugs))"; $offset = $pdo->query($rand)->fetch(PDO::FETCH_ASSOC); $sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset"; $bug_id_list = $pdo->query("SELECT bug_id FROM Bugs")->fetchAll(); $rand = random( count($bug_id_list) ); $stmt = $pdo->prepare($sql); $stmt->execute( $offset ); $rand_bug = $stmt->fetch();이 방법은 표준이 아닌 LIMIT 절에 의존하고 있다. LIMIT 절은 MySQL, PostgreSQL, SQLite에서 지원한다.
다른 대안은 Oracle, Microsoft SQL Server, IBM DB2에서 동작하는 ROW_NUMBER() 윈도 함수를 사용하는 것이다.
벤더 종속적인 방법
Microsoft SQL Server에서는 TABLESAMPLE절, Oracle에서는 SAMPLE 절을 사용하면 임의의 순서로 정렬한 행을 가져올 수 있다.
17장 가난한 자의 검색 엔진
17.1 목표: 전체 텍스트 검색
엘라스틱 서치와 같은 검색 엔진이 아닌 관계형 DB로 텍스트 검색 기능을 구현한다. 굳이 이렇게까지 하려는 건 백오피스 같은데서도 상품명과 같이 기본적인 텍스트 검색 기능이 필요한데, 여기에 ES를 붙여 검색하는 건 오버스펙이기도 하고 관리자 화면에는 원본 데이터가 필요하기 때문이다.
SQL(또는 관계형 이론)의 기본 원리 중 하나는 칼럼에 들어 있는 값이 원자적이어야 한다는 것이다. 즉, 한 값을 다른 값과 비교할 수 있지만, 비교를 할 때는 항상 전체 값과 비교해야 한다. SQL에서 부분문자열(substring)을 비교하는 것은 비효율적이거나 부정확하다. 그러나 우리는 긴 문자열과 짧은 문자열을 비교하고, 짧은 문자열이 긴 문자열에 나오는 경우를 찾을 방법이 필요하다. 이런 간극을 SQL로 어떻게 메울 수 있을까?
17.2 안티패턴: 패턴 매칭 사용
가장 널리 사용 되는 것은 LIKE 연산자다. LIKE 연산자는 0개 이상의 문자와 매치되는 와일드카드(%)를 지원한다. 키워드의 앞뒤에 와일드카드를 사용하면 해당 키워드를 포함하는 문자열과 매치된다. 첫 번째 와일드카드는 단어 앞의 어느 텍스트든 매치되고, 두 번째 와일드카드는 단어 뒤에 나오는 어느 텍스트든 매치된다.
SELECT * FROM Bugs WHERE description LIKE '%crash%';표준적인 방법은 아니지만 정규 표현식 또한 많은 데이터베이스 제품에서 지원된다. 정규 표현식은 패턴을 부분문자열에 매치시키기 때문에 와일드카드는 필요하지 않다. 다음은 MySQL에서 정규표현식 사용 예다.
SELECT * FROM Bugs WHERE description REGEXP 'crash';패턴 매칭 연산자의 가장 중요한 단점은 성능이 나쁘다는 것이다. 일반 인덱스를 활용할 수 없기 떄문에 테이블의 모든 행을 스캔해야 한다. 문자열 칼럼에 대한 패턴 매칭은 상당한 비용이 드는 연산이기 때문에(두 정수가 같은지 비교하는 등의 연산에 비해 상대적으로 비용이 높다), 이런 검색을 위해 테이블을 스캔할 때 전체비용은 매우 높아진다.
두 번째 문제는 LIKE나 정규 표현식을 사용한 단순한 패턴 매칭이 원치 않는 결과도 찾을 수 있다는 것이다.
SELECT * FROM Bugs WHERE description LIKE '%one%';이 쿼리는 단어 one을 포함한 텍스트를 찾아낼 뿐 아니라, money, prone, lonely 등과 같은 문자열도 찾아낸다. 공백을 경계로 하는 키워드 패턴으로 검색을 한다 해도, 단어가 텍스트의 맨 앞 또는 맨 뒤에 있거나 문장부호와 함께 있는 경우는 제대로 찾지 못할 것이다. 사용하는 데이터베이스에 따라 이런 문제를 해결하기 위해 단어 경계(word boundary)를 위한 특별한 정규 표현식 패턴을 지원할 수도 있다.
SELECT * FROM Bugs WHERE description REGEXP '[[:<:]]one[[:>:]]';성능과 확장적응성 문제, 정확한 매치를 위해서는 연습이 필요함을 고려할 때, 단순한 패턴 매칭은 키워드 검색을 위한 좋은 방법이 아니다.
17.3 안티패턴 인식 방법
다음과 같은 질문은 보통 가난한 자의 검색 엔진 안티패턴이 사용되고 있음을 나타낸다.
- "LIKE를 사용할 때 어떻게 하면 두 와일드카드 사이에 변수를 넣을 수 있지?" 프로그래머가 사용자로부터 입력 받은 텍스트로 패턴 매칭 검색을 하려고 할 때 보통 이런 질문이 나온다.
- "문자열이 여러 개의 주어진 단어를 포함하고 있는지, 문자열이 특정 단어를 포함하지 않고 있는지, 또는 문자열이 주어진 단어의 변형을 포함하고 있는지를 확인하는 정규 표현식을 작성하려면 어떻게 해야 하지?" 문제가 복잡해 정규 표현식으로 풀기에 어려워 보이면, 아마 정규 표현식으로는 못 풀 것이다.
- "우리 웹 사이트의 검색 기능은 데이터베이스에 많은 문서를 추가했을 때 사용하기 어려울 정도로 느려, 뭐가 잘못된 걸까?" 데이터 크기가 늘어날수록, 안티패턴을 이용한 방법의 문제는 심각해질 것이다.
17.4 안티패턴 사용이 합당한 경우
안티패턴 절에서 보인 표현은 모두 적법한 SQL 쿼리이고, 직관적일 뿐 아니라 사용하기도 쉽다. 이는 중요한 점이다.
물론 성능도 중요하지만, 어떤 쿼리는 아주 가끔씩만 실행되기 때문에 이를 최적화하기 위해 많은 자원을 투자하는 것이 의미가 없을 수 있다. 거의 사용되지 않는 쿼리에서 사용하기 위해 인덱스를 유지하는 것은, 그냥 해당 쿼리를 비효율적으로 실행시키는 것보다 더 많은 비용이 들 수 있다. 필요한 때만 가끔 사용하는 쿼리라면, 이를 위해 정의한 인덱스가 꼭 도움이 된다는 보장은 없다.
복잡한 쿼리에서 패턴 매칭 연산자를 사용하기는 어렵지만, 간단한 경우에 대한 패턴이라면 최소의 노력으로 올바른 결과를 얻는 데 도움이 될 수 있다.
17.5 해법: 작업에 맞는 올바른 도구 사용하기
SQL 대신 특화된 검색 엔진을 사용하는 것이 제일 좋다????ㅠㅠ 다른 대안은 검색 결과를 저장해 반복되는 비용을 줄이는 것이다.
벤더 확장기능
각 벤더별로 전문 검색(full-text search) 기능을 제공하지만 서로 호환되는 것은 아니다. 벤더 종속적으로 서비스를 운영할 생각이라면 이런 기능을 활용하는 것이 SQL 쿼리와 가장 잘 통합된 고성능 텍스트 검색을 위한 최선의 방법이다. MySQL의 예를 들어보자.
MySQL은 MyISAM 스토리지 엔진에서만 간단한 전체 텍스트 인덱스 타입을 제공한다. CHAR, VARCHAR 또는 TEXT 타입의 칼럼에 전체 텍스트 인덱스를 정의할 수 있다. 다음은 Bugs 테이블의 summary와 description 칼럼에 전체 텍스트 인덱스를 정의한 것이다.
ALTER TABLE Bugs ADD FULLTEXT INDEX bugfts (summary, description);인덱스가 걸린 텍스트에서 키워드를 검색할 때는 MATCH() 함수를 사용한다. 이떄 전체 텍스트 인덱스의 칼럼을 지정해줘야 한다. (그래야 같은 테이블의 다른 칼럼에 대한 인덱스도 사용할 수 있다.)
SELECT * FROM Bugs WHERE MATCH(summary, description) AGAINST ('crash');서드파티 검색 엔진
Sphinx와 Apache Lucene을 소개해주었는데, 본 글의 의도에서 벗어나므로 생략하겠다.
직접 만들기
전치 인덱스(inverted index)를 활용한다. 기본적으로, 전치 인덱스는 검색할 모든 단어의 목록이다. 다대다 관계에서 인덱스는 이 단어들과 각 단어를 포함한 텍스트 항목을 연관시킨다. 즉 crash란 단어는 많은 버그에 나타날 수 있고, 각 버그는 다른 많은 키워드를 가질 수 있다. 이에 착안하여 전치 인덱스를 만들어보자.
먼저, 사용자가 검색할 키워드 목록을 나타내는 Keywords 테이블을 정의하고, BugsKeywords 교차 테이블을 정의해 다대다 관계를 만든다.
추가한 키워드 테이블과의 관계도 CREATE TABLE Keywords ( keyword_id SERIAL PRIMARY KEY, keyword VARCHAR(40) NOT NULL, UNIQUE KEY (keyword) ); CREATE TABLE BugsKeywords ( keyword_id BIGINT UNSIGNED NOT NULL, bug_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (keyword_id, bug_id), FOREIGN KEY (keyword_id) REFERENCES Keywords(keyword_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) );그 다음, 주어진 버그에 대해 설명 텍스트와 매치되는 모든 키워드를 BugsKeywords에 추가한다. LIKE나 정규 표현식을 이용한 부분문자열 매치 쿼리를 사용해 매치되는지 여부를 결정할 수 있다. 이는 안티패턴 절에서 설명했던 고지식한 방법과 마찬가지로 비용이 많이 들지만, 이 검색을 한 번 수행한 다음에는 효율적으로 동작할 것이다. 결과를 교차 테이블에 저장하고 나면, 동일한 키워드에 대한 이후 검색은 훨씬 빨라질 것이다.
그 다음, 주어진 키워드에 대한 검색을 쉽게 하기 위한 저장 프로시저를 작성한다. 단어가 이미 검색된 적이 있다면, 그 키워드를 포함하는 문서 목록이 BugsKeywords에 존재하므로 쿼리가 빠르다. 주어진 키워드를 아직 아무도 검색한 적이 없으면, 어려운 방법으로 텍스트 항목의 집합을 검색해야 한다.
CREATE PROCEDURE BugsSearch(keyword VARCHAR(40) BEGIN SET @keyword = keyword; PREPARE s1 FROM 'SELECT MAX(keyword_id INTO @k FROM Keywords WHERE keyword = ?'; EXECUTE s1 USING @keyword; DEALLOCATE PREPARE s1; IF (@k IS NULL) THEN PREPARE s2 FROM 'INERT INTO Keywords (keyword VALUES (?)'; EXECUTE s2 USING @keyword; DEALLOCATE PREPARE s2; SELECT LAST_INSERT_ID() INTO @k; PREPARE s3 FROM 'INSERT INTO BugsKeywords (bug_id, keyword_id) SELECT bug_id, ? FROM Bugs WHERE summary REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>:]]'') or DESCRIPTION REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>:]]'')'; EXECUTE s3 USING @k, @keyword, @keyword; DEALLOCATE PREPARE s3; END IF; PREPARE s4 FROM 'SELECT b.* FROM Bugs b JOIN BugsKeywords k USING (bug_id) WHERE k.keyword_id = ?'; EXECUTE s4 USING @k; DEALLOCATE PREPARE s4; END- 사용자가 지정한 키워드를 검색한다. 정수형 PK인 Keywords.keyword_id 값을 리턴한다. 이전에 검색된 적이 없는 단어라면 NULL을 리턴한다.
- 단어가 없으면, 새로운 키워드로 등록한다.
- Keywords에서 생성된 PK 값을 조회한다.
- Bugs에서 새로운 키워드를 포함하는 행을 검색해 교차 테이블에 넣는다.
- 마지막으로, 키워드를 찾았든 새로운 항목을 추가했든 keyword_id를 이용해 Bugs의 전채 행을 조회한다.
이제 원하는 키워드로 이 저장 프로시저를 호출할 수 있다. 프로시저는 매칭되는 버그 집합을 리턴하는데, 새로운 키워드에 대해 매칭되는 버그를 찾고 교차 테이블에 데이터를 넣고 나서 리턴한 것일 수도 있고, 간단히 이전 검색의 결과를 이용할 것을 수도 있다.
CALL BugsSearch('crash');아직 좀 남아있다. 트리거를 정의해 새로운 버그가 등록될 때마다 교차 테이블에 데이터를 넣어줘야 한다. 버그 설명을 편집할 수 있는 경우에는, 텍스트를 다시 분석해 BugsKeywords 테이블에 행을 추가하거나 삭제하는 트리거도 작성해야 한다.
CREATE TRIGGER Bugs_Insert AFTER INSERT ON Bugs FOR EACH ROW BEGIN INSERT INTO BugsKeywords (bug_id, keyword_id) SELECT NEW.bug_id, k.keyword_id FROM Keywords k WHERE NEW.description REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]') OR NEW.summary REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]'); END키워드 목록은 사용자가 검색을 수행하면 자연히 생성되기 떄문에, 문서에 나오는 모든 단어로 키워드 목록을 채울 필요는 없다. 그러나 검색될 것 같은 키워드를 예상하여 이를 미리 검색해 두면, 각 키워드의 첫 검색에 드는 초기 비용을 사용자가 느끼지 못하게 할 수 있다.
저자는 Keywords 테이블에 num_searches 칼럼을 추가해 사용자가 키워드를 검색할 때마다 이 칼럼의 값을 하나씩 증가시켜 검색어 유입을 추적하였다고 한다.
결국 ES(lucene)처럼 역색인 구조로 가야되는 구나 ㅎㅎ
18장 스파게티 쿼리
버그 관리 애플리케이션에서 아래와 같은 요구사항을 받았다.
- 작업하는 제품 수
- 버그를 수정한 개발자 수
- 개발자당 평균 몇개의 버그를 수정하는지
- 수정한 버그 중 고객이 보고한게 얼마나 되는지
그리고 아래와 같은 한방 쿼리를 작성한다.
SELECT COUNT(bp.product_id) AS how_many_products, COUNT(dev.account_id) AS how_many_developers, COUNT(b.bug_id) / COUNT(dev.account_id) AS avg_bugs_per_developer, COUNT(cust.account_id) AS how_many_customers FROM Bugs b JOIN BugsProducts bp ON (b.bug_id = bp.bug_id) JOIN Accounts dev ON (b.assigned_to = dev.account_id) JOIN Accounts cust ON (b.reported_by = cust.account_id) WHERE cust.email NOT LIKE '%@example.com' GROUP BY bp.product_id;숫자가 나오긴 하는데 이상해 보인다. 제품이 수십 개가 되고, 평균 수정 버그수가 정확하게 1.0이 나오고, 고객이 보고한 버그 수가 아니라 고객 수가 나온다.
18.1 목표: SQL 쿼리 줄이기
많은 프로그래머들이 일하면서 가장 흔하게 수렁에 빠지는 경우 중 하나가 "이걸 어떻게 하나의 쿼리로 할 수 있을까?"하고 생각할 때다. 이런 질문은 거의 모든 작업에 나온다. 프로그래머들은 하나의 SQL 쿼리가 어렵고, 복잡하고, 비용이 많이 든다고 배웠기 때문에, SQL 쿼리가 두 개면 두 배로 나쁘다고 생각한다. 문제를 푸는 데, 두 개 이상의 SQL을 사용하는 것은 보통 생각하지도 않는다.
프로그래머는 작업의 복잡도를 줄일 수는 없지만, 방법은 단순화하고 싶어한다. 그들은 목표를 '우아한' 또는 '효율적인' 같은 용어로 말하며, 하나의 쿼리로 문제를 풀면 이 목표를 달성했다고 생각한다.
18.2 안티패턴: 복잡한 문제를 한 번에 풀기
SQL은 표현력이 뛰어난 언어다. 하나의 쿼리나 문장으로 많은 것을 할 수 있다. 그러나 모든 작업을 한 줄의 코드로 해치워야 한다는 접근방법이 좋은 생각이라는 뜻은 아니다. 다른 프로그래밍 언어를 사용할 때도 이런 습관을 가지고 있는가? 아마 아닐 것이다.
의도하지 않은 제품
모든 겨로가를 하나의 쿼리로 만들어내려고 시도할 때 나타나는 흔한 결과 중 하나가 카테시안 곱(Cartesian product)이다. 카테시안 곱은 쿼리에 사용된 두 테이블에 이들의 관계를 제한하는 조건이 없을 때 발생한다. 이런 제한이 없이 두 테이블을 조인하면, 첫 번째 테이블의 모든 행과 다른 테이블의 모든 행이 짝이 되고, 이런 각각의 짝이 결과 집합의 행이 된다. 그 결과 기대했던 것보다 훨씬 많은 행이 나오게 된다.
SELECT p.product_id, COUNT(f.bug_id) AS count_fixed, COUNT(o.bug_id) AS count_open FROM BugsProducts p LEFT OUTER JOIN (BugsProducts bpf JOIN Bugs f USING (bug_id) f ON (p.bug_id = f.bug_id AND f.status = 'FIXED') LEFT OUTER JOIN (BugsProducts bpo JOIN Bugs o USING (bug_id) o ON (p.bug_id = o.bug_id AND o.status = 'OPEN') WHERE p.product_id = 1 GROUP BY p.product_id ;주어진 제품에 대해 실제로는 11개의 수정된 버그가 있고 7개의 오픈된 버그가 있다는 것을 알고 있다. 따라서 쿼리 결과가 다음과 같이 나오는 것은 영문 모를 일이다.
product_id count_fixed count_open 1 77 77 어떤 이유 때문에 이렇게 틀려졌을까? 11 곱하기 7이 77이란 사실은 우연이 아니다. 이 예제는 Products 테이블을 Bugs의 부분집합 두 개와 조인하지만, 버그의 두 부분집합이 카테시안 곱이 되어 버린다. 수정된 버그 11개의 각 행과 오픈된 버그 7개의 각 행이 짝이된 것이다. 이 쿼리에서 표현된 관계는 BugsProducts 테이블과 Bugs의 각 부분집합 사이뿐이다. 수정된 버그와 오픈된 버그 사이의 관계를 제한하는 조건이 지정되지 않았고, 디폴트는 카테시안 곱이다. 그 결과 11 곱하기 7개의 행이 나온다. 이와 같이 한 쿼리로 여러 작업을 처리하려 할 때는 의도하지 않은 카테시안 곱을 생성하기 쉽다. 하나의 쿼리에서 관련 없는 작업을 더 많이 처리하려 시도하면, 또 다른 카테시안 곱으로 전체 행의 수가 다시 늘어날 것이다.
이런 쿼리는 잘못된 결과를 얻을 수 있을 뿐만 아니라, 작성하기도 어렵고, 수정하기도 어렵고, 디버깅하기도 어렵다는 점을 고려해야 한다. 데이터베이스 애플리케이션에서는 지속적인 개선 요청이 있을 것이라 예상해야 한다. 실행할 떄의 비용도 있다. 많은 조인과, 상호 연관된 서브쿼리, 그리고 다른 연산을 사용해야 하는 복잡한 SQL 쿼리는 SQL 엔진이 빠르게 최적화하여 실행하기도 어렵다. 프로그래머들은 적은 수의 SQL 쿼리를 실행시켜야 성능이 더 좋을 것이라 생각한다. SQL 쿼리가 같은 복잡도를 가진다면 맞는 말이다. 그러나 하나의 괴물 같은 쿼리가 실행되는 데 드는 비용은 지수적으로 증가할 수 있다. 여러 개의 단순한 쿼리를 사용하는 편이 훨씬 경제적이다.
18.3 안티패턴 인식 방법
프로젝트 구성원이 다음과 같은 말을 하는 게 들리면, 스파게티 쿼리 안티패턴이 사용되고 있음을 나타내는 것일 수 있다.
- "합계와 개수가 왜 이렇게 크지?" 의도하지 않은 카테시안 곱으로 데이터 집합이 뻥튀기 됐다.
- "나는 하루 종일 이 괴물 같은 SQL 쿼리와 씨름했어." SQL은 이렇게 복잡하지 않다. 지나치게 긴 SQL 쿼리와 씨름하고 있었다면, 접근 방법을 재고해야 한다.
- "우리 데이터베이스 리포트에는 아무것도 추가할 수 없어. SQL 쿼리가 어떻게 동작하는지 이해하려면 시간이 엄청나게 오래 걸릴 거야." 아무도 손댈 수 없는 지나치게 복잡한 쿼리는 작성하지 말아야한다.
- "쿼리에 DISTINCT를 하나 더 추가해봐." 카테시안 곱으로 인한 행의 폭발적 증가를 보정하기 위해, 프로그래머들은 쿼리 수정자 또는 집계 함수 수정자로 DISTINCT 키워드를 사용해 중복을 제거한다. 이렇게 하면 잘못된 쿼리의 증거는 감춰지지만, DBMS는 중간 결과 집합을 생성해서 데이터를 정렬하고 중복을 제거하는 부가 작업을 하게 된다.
어떤 쿼리가 스파게티 쿼리임을 나타내는 또 다른 실마리는 쿼리가 지나치게 오랫동안 실행되는 것이다. 안 좋은 성능은 다른 문제의 전조일 수도 있지만, SQL 문장 하나로 한꺼번에 지나치게 많은 일을 시도한 게 아닌지 확인해봐야 한다.
18.4 안티패턴 사용이 합당한 경우
하나의 쿼리로 복잡한 작업을 실행하는 가장 일반적인 경우는 프로그래밍 프레임워크나 비주얼 컴포넌트 라이브러리를 사용할 떄다. 간단한 BI(Business Intelligence) 도구나 리포팅 도구 또한 이런 범주에 속하며 보통 하나의 데이터 소스에 접속해 데이터를 표시한다. 그러나 좀더 정교한 BI 소프트웨어는 여러 데이터 소스의 결과를 병합할 수 있다.
데이터 소스가 하나의 SQL 쿼리라 가정하는 컴포넌트나 리포팅 도구는 사용하긴 쉽지만, 리포트에 모든 데이터를 종합하는 큰 덩어리의 쿼리를 사용하도록 조장한다. 이런 리포팅 애플리케이션을 사용한다면, 코드를 직접 작성하는 경우보다 복잡한 SQL 쿼리를 만들게 된다.
리포트 요구사항이 너무 복잡해 하나의 SQL 쿼리로 만들어내기 어렵다면, 리포트를 여러 개로 나누는 것이 나을 수 있다. 상사가 이걸 좋아하지 않는다면, 리포트의 복잡도와 리포트를 만드는 데 필요한 시간 사이의 관계를 설명하는 것이 좋다.
18.5 해법: 분할해서 정복하기
검약률이란 두 개의 이론이 동일한 예측을 한다면, 단순한 쪽이 좋다는 이론이다. SQL 역시 동일한 결과 집합을 만드는 쿼리 두개 중 하나를 선택해야 할 때 단순한 쪽을 선택해야 한다.
한 번에 하나씩
의도하지 않은 카테시안 곱이 생기는 두 테이블 사이에 논리적 조인 조건을 찾을 수 없다면, 그런 조건이 아예 없기 때문일 수도 있다. 카테시안 곱을 피하려면, 스파게티 쿼리를 단순한 여러 개의 쿼리로 나누어야 한다. 위 예제에서는 쿼리르 둘로 나누면 된다.
SELECT p.product_id, COUNT(f.bug_id) AS count_fixed FROM BugsProducts p LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED') WHERE p.product_id = 1 GROUP BY p.product_id ; SELECT p.product_id, COUNT(o.bug_id) AS count_open FROM BugsProducts p LEFT OUTER JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN') WHERE p.product_id = 1 GROUP BY p.product_id ;이 쿼리의 결과는 기대한 대로 11과 7이다.
쿼리를 여러 개로 나누는 '우아하지 못한' 방법을 사용한 것에 약간의 아쉬움을 느낄지도 모르겠다. 그러나 개발과 유지보수, 성능에 대한 여러 가지 긍정적 효과를 깨달으면 이런 아쉬움은 곧 안도감으로 바뀌게 된다.
- 이 쿼리는 원치 않은 카테시안 곱을 생성하지 앟는다. 따라서 쿼리가 정확한 결과를 보여준다는 확힌을 쉽게 가질 수 있다.
- 리포트에 새로운 요구사항을 추가해야 하는 경우, 이미 복잡한 쿼리에 다른 계산을 통합하는 것보다는 간단한 쿼리를 하나 추가하기가 더 쉽다.
- SQL 엔진은 보통 복잡한 쿼리보다는 단순한 쿼리를 쉽게 최적화하고 실행할 수 있다. 쿼리를 나누면 작업이 중복되는 것처럼 보여도, 전체적으로는 이득이다.
- 코드 리뷰나 팀원 온보딩 시간에 복집한 쿼리 하나를 설명하는 것보다는 간단한 쿼리 여러 개를 설명하는 것이 더 쉽다.
UNION 연산
UNION 연산을 사용하면 여러 쿼리의 결과를 하나의 결과 집합으로 묶을 수 있다. 하나의 쿼리를 실행시켜 하나의 결과 집합을 받는 것이 정말 필요하다면 UNION이 유용할 수 있다.
(SELECT p.product_id, f.status, COUNT(f.bug_id) AS bug_count FROM BugsProducts p LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED') WHERE p.product_id = 1 GROUP BY p.product_id, f.status) UNION ALL (SELECT p.product_id, o.status, COUNT(o.bug_id) AS bug_count FROM BugsProducts p LEFT OUTER JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN') WHERE p.product_id = 1 GROUP BY p.product_id, o.status) ORDER BY bug_count;최초 요구사항 해결하기
이 역시 작업을 분리하여 해결할 수 있다.
작업하는 작품 개수
SELECT COUNT(*) AS how_many_products FROM Products;버그를 수정한 개발자 수
SELECT COUNT(DISTINCT assigned_to) AS how_many_deelopers FROM Bugs b WHERE status = 'FIXED';개발자 당 평균 수정 버그 개수
SELECT AVG(bugs_per_developer) AS average_per_developer FROM (SELECT dev.account_id, COUNT(*) AS bugs_per_developer FROM Bugs b JOIN Accounts dev ON (b.assigned_to = dev.account_id) WHERE b.status = 'FIXED' GROUP BY dev.account_id) t;수정한 버그 중 고개이 보고한 것의 개수
SELECT COUNT(*) AS how_many_customer_bugs FROM Bugs b JOIN Accounts cust ON (b.reported_by = cust.account_id) WHERE b.status = 'FIXED' AND cust.email NOT LIKE '%@example.com';이중 일부 쿼리는 그 자체만으로 충분히 까다롭다. 이 모두를 하나의 쿼리로 통합해야 한다면 악몽이 될 것이다.
SQL을 이용한 SQL 자동 생성
복잡한 쿼리를 나누면 데이터 값에 따라 조금씩 변하는 비슷한 쿼리가 많이 나올 수 있다. 이런 쿼리를 작성하는 것은 따분하다. 따라서 코드 생성을 적용할 좋은 기회다.
코드 생성(code generation)은 새로운 코드를 출력하는 코드를 작성하는 기법이다. 코드를 수작업으로 작성하는 것이 지루한 작업이라면 이 방법을 사용할 가치가 있다.
'책책책 책을 읽읍시다 > 프로그래밍' 카테고리의 다른 글
엔터프라이즈 애플리케이션 아키텍처 패턴 2 - 객체&관계형 매핑, 리포지토리, 프런트 컨트롤러 (5) 2023.03.13 엔터프라이즈 애플리케이션 아키텍처 패턴 1 - 계층 구조 (0) 2023.03.12 오브젝트 6 - 다형성, 서브타이핑, 일관된 협력 (0) 2023.02.14 오브젝트 5 - 상속 vs. 합성 (0) 2023.02.14 오브젝트 4 - 메세지, 인터페이스, 의존성 (0) 2023.02.13