SQL 튜닝 - 선택성과 카디널리티

개요

이번 글에서는 선택성(Selectivity)와 카디널리티에 대해 자세히 알아본다. 선택성은 전체 테이블에서 반환되는 행의 비율이고 카디널리티는 선택되는 행의 수이다. 이는 옵티마이저의 비용 계산 실행 계획 관리 등에서 사용된다.

선택성이란?

선택성은 쿼리에서 반환되는 행의 비율이다. 테이블의 총 행 수에 대한 비율로 계산된다.

SELECT COUNT(*) FROM sales WHERE promo_id = 22;

 

sales 테이블에 약 90만 개의 레코드가 있다고 가정할 때 이 쿼리에서 반환되는 행 수가 약 80만개라고 하면 이 쿼리는 거의 모든 레코드를 반환하므로 선택성이 낮다. 반면에 promo_id를 33으로 변경하여 2000개의 행이 반환될 경우 선택성이 높아진다.

선택성은 쿼리에서 반환되는 행 수를 테이블의 총 행수로 나누어서 계한된다. 선택성이 0에 가까울 수록 선택성이 높고 1에 가까울 수록 선택성이 낮다.

2,000/900,000 = 0.0022... /*선택성이 높다 */
800,000/900,000 = 0.8888... /*선택성이 낮다 */

 

선택성과 카디널리티의 관계

카디널리티는 쿼리에서 예상되는 반환 행 수를 나타낸다. 선택성에 테이블의 총 행 수를 곱하여 계산한다.

0.0022.. * 900,000 = 2,000 (선택성 * 테이블 행 수)
0.8888.. * 900,000 = 800,000 (선택성 * 테이블 행 수)

선택성과 카디널리티의 중요성

옵티마이저는 선택성과 카디널리티를 사용하여 I/O 비용, 정렬 비용 등을 추정한다. 선택성은 쿼리의 I/O 비용 추정에 영향을 미치며 반환 행 수가 많을 수록 정렬 비용이 높아진다. 카디널리티는 조인, 정령 및 필터링 비용을 결정하는데 사용된다. 잘못된 선택성과 카디널리티는 실행 계획 비용 추정의 오류를 초래할 수 있다.

선택성과 카디널리티 추정

데이터베이스는 쿼리를 실행하기 전에 반환 행 수를 정확히 알 수 없기 때문에 옵티마이저는 이를 추정한다. 이를 위해 WHERE 절에 있는 열의 고유 값 수를 사용한다.

sales 테이블에 약 100만 개의 레코드가 있고 promo_id의 고유 값의 수가 4개라고 가정하면 옵티마이저는 각 고유값이 약 25만개의 행을 반환할 것으로 추정한다. 따라서 선택성이 낮은 쿼리가 된다. 반대로 고유값 수가 7000개라면 옵티마이저는 약 140개의 행을 반환할 것으로 추정하고 선택성이 높은 쿼리로 다루어진다.

여러 열의 선택성 계산

여러 열이 포함된 WHRE 절의 선택성은 각 열의 개별 선택성을 곱하여 계산된다.

SELECT * FROM sales WHERE promo_id = 22 AND channel_id = 3 AND cust_id = 123;

각 열의 고유 값 수가 다음과 같다고 가정한다:

• promo_id: 4
• channel_id: 5
• cust_id: 7,000

각 열의 선택성은 다음과 같다.

• promo_id: 1/4
• channel_id: 1/5
• cust_id: 1/7000

전체 선택성은 1/4 * 1/5 * 1/7,000 = 1/140,000 이다. 카디널리티는 900,000 * 1/140,000 = 약 6.4이다.

컬럼 간 관계의 중요성

컬럼 간의 관계도 중요하다. 예를 들어, promo_id와 channel_id가 밀접하게 관련되어 있으면 선택성 계산이 부정확할 수 있다. 이는 옵티마이저가 잘못된 실행 계획을 생성하게 하는 원인이 될 수 있다.

결론

이번 글에서는 선택성과 카디널리티에 대해 알아보았다. 선택성과 카디널리티는 쿼리 성능에 영향을 미치며 옵티마이저가 최적의 실행 계획을 생성하는데 중요한 역할을 한다. 반대로 잘못된 계획을 세우는 데에 영향을 끼칠 수 있으니 이를 이해하는 것이 요구된다.

감상

테이블에서 얼마만큼의 비율을 선택하는지에 따라서 계획을 선택해야하기에 중요한 개념으로 보인다. 테이블의 80, 90%를 반환해야한다면 그냥 풀 테이블 스캔을 하는 게 가장 효율적일 수 있으니 말이다. 수학적 개념이 나왔으나 그렇게 어려운 개념은 아닌 듯하다.