인덱싱 및 쿼리 최적화

1) 인덱싱 및 최적화 목표

대기 시간: P50/P95/P99 감소.
처리량: 스케일 아웃없이 QPS 성장.
예측 가능성: 응답 시간에 안정적인 계획과 "점프" 없음.
저축: IO/CPU가 적고 클라우드 요금이 적습니다.
신뢰성: 올바른 액세스로 인한 자물쇠 및 교착 상태 감소.

• 모든 최적화는 정확성과 일관성을 유지해야합니다.
• 메트릭 및 계획 로그의 효과를 추적하십시오.

2) 기본 인덱스 구조와 적용 시점

2. 1 B- 트리 (기본값)

동일/범위, 정렬, 'ORDER 것'.
대부분의 시간/ID/상태 필터에 좋습니다.

2. 2 해시

순수한 평등 ('='), 메모리는 저렴하지만 순서가 맞지 않습니다 (PG: 제약 조건은 제거되었지만 여전히 틈새 선택).

2. 3 GIN/GiST (PostgreSQL)

GIN: 어레이/JSONB 키, 전체 텍스트 (tsvector), 격리 ('@>').
GiST: 지오, 범위, kNN.

2. 4 BRIN (PostgreSQL)

"자연적으로 분류 된" 테이블별로 초저가 인덱스 (시간별로 추가). 큰 테이블이있는 시계열에 좋습니다.

2. 5 비트 맵 (MySQL/InnoDB: 네이티브 없음; DW-DBMS/OLAP)

낮은 카디널리티 및 패싯에 효과적이며 컬럼 스토리지에서 더 자주 사용됩니다.

2. 6 열 지수 (Clickhouse)

기본 키 + 데이터 건너 뛰기 (minmax), 보조 지정 ре' 건너 뛰기 인덱스 '(블룸, 세트).
집계 및 범위가있는 OLAP 쿼리.

2. 7 역 색인 (엘라 스틱 검색/OpenSearch)

전체 텍스트, 패셋, 하이브리드 검색. 정확한 필터는 키워드 필드와 문서 값을 사용하십시오.

2. 8 몽골 DB

단일, 복합, 멀티 키 (어레이), 부분, TTL, 텍스트, 해시 (균일 한 키 샤딩 용).

3) 키 및 복합 인덱스 설계

3. 왼쪽 접두사 규칙 1 개

인덱스의 필드 순서에 따라 유용성이 결정됩니다.
쿼리 'WHERE 테넌트 _ id =? 그리고 _ at> = 를 만들었습니다 RORDER BYWI는 _ at DESC '→ ин함수도' (테넌트 _ id, 생성 된 _ at DESC, id DESC) 를 생성했습니다.

3. 타이 브레이커 2 개

안정적인 정렬을 위해 고유 한 꼬리 (일반적으로 'id') 를 추가하고 페이지 매김을 찾으십시오

3. 3 부분/필터링 된 인덱스

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 개의 적용 지수

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 기능/계산

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) 파티셔닝 및 샤딩

4. 1 파티셔닝 (PG 기본/테이블 상속; MySQL RANGE/LIST)

시간별 당사자 회전 ('매일/매주') 은 'VACUUM/DELETE' 를 단순화합니다.
인덱스는 로컬 파티션 → B-Tree보다 작으며 더 빠른 계획입니다.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 파티션 키

OLTP에서-' 테넌트 _ id '(로드 현지화).
시계열/OLAP - 'ts' (범위 쿼리).
하이브리드: '(테넌트 _ id, ts)' + 하위 당사자.

4. 3 샤딩

'테넌트 _ id' 또는 시간에 따라 일관된 해싱/레인지 샤드.
크로스 샤드 쿼리 → 산란 수집 및 k- 웨이 병합; 파편 당 커서를 유지하십시오.

5) 통계, 카디널리티 및 계획

5. 최신 통계 1 개

자동 분석 ('자동 진공/자동 분석') 을 사용하고 더러운 분포에 대한 '기본 _ 통계 _ 대상' 을 증가시킵니다.

5. 2 고급 통계 (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 실행 계획

• '행', '루프', '실제 시간', '공유 읽기/적중', '레체 콘드'.
• 계약: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bit맵 Heap Scan.

5. 4 계획의 안정성

구급대 원 (준비된 진술) 은 나쁜 계획을 "고착" 할 수 있습니다. 문제 쿼리에 대해서는 플랜 캐시 가드 레일 (PG: 'plan _ cash _ mode = force _ 맞춤형 _ plan') 또는 "전달" 상수를 사용하십시오.

6) 가입 및 정렬 최적화

6. 1 전략

Nested Loop: 내부의 작은 외부, 빠른 인덱스.
해시 조인: 큰 세트, 해시 테이블을위한 충분한 메모리.
합병: 이미 사용 가능한 순서대로 유리한 정렬 된 항목.

6. 가입중인 인덱스 2 개

'A JOIN B ON B.a _ id = A.id' 의 경우 → 색인은 'B (a _ id)' 입니다.
결합 후 필터의 경우-내부 테이블 필터 열의 인덱스.

6. 3 심사

해당 인덱스없이 '주문' 을 피하십시오. 메모리/디스크로 큰 세트를 정렬하는 것은 비용이 많이 듭

7) 쿼리 재 작성

서브 쿼리의 "눈송이" 를 제거하십시오. JOIN에서 확장.
CTE 인라인을 사용하십시오 (PG 소 12 CTE 기본 인라인이지만 'MATERIALIZED' 는 필요한 경우 중간 결과를 범할 수 있습니다).
'SELECT' → 필드를 제거하십시오 (IO/네트워크 절약).
'WHERE' 에서 색인 된 형식 (사전 계산 된 열) 으로 계산을 전송합니다.
집계: 점진적인 업데이트가있는 예비 요약 테이블/구체화 된보기.

8) 버칭, 제한 및 페이지 매김

배치 삽입/업데이트: 하나가 아닌 500-5000 배치.
깊은 'OFFSET' 대신 '(sort _ key, id)' 로 페이지 매김을 찾으십시오.
분류/조인 (푸시 다운 'LIMIT') 전에 다이얼링을 제한합니다.

9) 캐싱 및 비정규화

응용 프로그램 수준 쿼리 캐시 (key = SQL + bind-vars + rights 버전).
무거운 골재에 대한 재료보기; 회전/참조 계획.
비정규화-저장은 계산 된 필드 (할인을 포함한 가격) 를 자주 읽지 만 일관성을 위해 트리거/배경 작업을 사용합니다.
핫 키의 경우 L2로 Redis (TTL 및 이벤트 장애 포함).

10) 인기있는 엔진의 세부 사항

10. 1 PostgreSQL

지정: B-Tree, Hash, GIN/GiST, BRIN, 부분, 기능, INCLUDE.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

히스토그램 통계 ('ANALYZE TABLE... HISTOGRAM을 업데이트하십시오. 0).

10. 3 클릭 하우스

기본 키 = 정렬; '주문 확인 (테넌트 _ id, ts, id)'.

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 몽골 DB

복합/만화: 순서가 중요합니다. 필터 및 정렬은 색인과 일치해야합니다

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

진단에 '힌트 ()' 를 사용하고 '포함 된 쿼리' 를보십시오.

10. 5 ElasticSearch/OpenSearch

키워드 대 텍스트 필드; 정렬/집계에 대한 doc _ 값.
난방 세분화: 집계-무거운; '크기' 및 '복합' 집계 (페이징) 를 제한합니다.
정확한 비교가 필요한 분석기는 포함하지 마십시오.

11) 경쟁력, 인터록 및 MVCC

짧은 거래; 'REPEATABLE READ' 에서 "long" 읽기를 불필요하게 피하십시오.
인덱스 작업도 잠금 해제됩니다 (쓰기 처리량 감소).
온라인 색인 계획: 'CREATE INDEX CONCRETELY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
인덱스의 1 시간/ID → "핫 페이지" 동안 꼬리에 삽입; 키 (UUIDv7/소금) 를 배포하십시오.

12) 관찰 및 SLO

• 쿼리 이름으로 'db _ query _ latency _ ms' (P50/P95/P99).
• 'rows _ insured', 'rows _ return', 'buffer _ hit _ ratio'.
• '교착 상태', 'lock _ wait _ ms', 'temply _ sort _ disk _ usage'.
• 'Seq Scan' 과 계획 공유 'Index Scan' 이 예상되었습니다.
• DBMS의 버전/매개 변수를 변경할 때 회귀 경고가 표시됩니다.

• 임계 값으로 느린 쿼리 로그를 사용하십시오 (예: 200ms).
• 쿼리와 스팬의 상관 관계 (trace _ id).
• 문제 쿼리 계획을 제거하고 회고를 위해 객체 스토리지로 저장하십시오.

• 'LIMIT <= 50' 및 핫 테넌트로 P95 '<= 150 ms' 를 읽으십시오.
• P95는 최대 1000 줄의 배치로 '<= 200 ms' 를 기록합니다.

13) 안전 및 다중 임대

액세스 제어 필드의 색인 ('tenant _ id', 'owner _ id') 이 필요합니다.
정책 (RLS/ABAC) 은 사전 필터 여야합니다. 그렇지 않으면 최적화 프로그램이 잘못 계획됩니다.
명확한 텍스트에 민감한 필드를 색인화하지 마십 해시/토큰을 사용하십시오.

14) 반 패턴

찾기 커서 대안이없는 깊은 'OFFSET'.
"모두를위한 하나의 인덱스" -메모리 과부하 및 쓰기 경로.
'SELECT' 는 중요한 길입니다.
함수 색인없이 'WHERE' 열 위의 함수.
오래된 통계로 인한 불안정한 계획.
안정적인 주문을 기다리는 동안 '주문' 이 누락되었습니다.
인덱스를 위해 색인: 값 비싼 쓰기/지원으로 인한 ROI <0.

15) 구현 점검표

1. QPS의 상위 N 요청 및 시간 → 3-5 명의 후보자를 선택하십시오.
2. 'EXPLAIN ANALYZE' 계획을 제거하고 실제 대 카디널리티를 확인하십시오.
3. 디자인 색인: 현장 순서, INCLUDE/부분/기능.
4. 큰 테이블 (임시/테넌트 키) 에 대한 파티션을 구현합니다.
5. 폴더 쿼리: 인라인 간단한 CTE 인 'SELECT' 를 제거하면 세트가 제한됩니다.

6. 버칭을 활성화하고 페이지 매김을 찾으십시

7. 캐시 설정: L1/L2, 이벤트 별 장애.
8. 계획 모니터링 및 느린 로그, 회귀 경고를 소개합니다.

9. 실제 데이터 분포로드 테스트를 수행하십시

10. 개발 지침 업데이트 (ORM 힌트, 색인, 한계).

16) 예 전/후

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) ORM 및 API 프로토콜

N + 1 피하기: 탐욕스러운 샘플 ('포함', 'JOIN FETCH', '프리로드').
커서별로 표시되는 명시 적 필드 투영.
gRPC/REST: '페이지 _ 크기' 를 제한하고 '정렬' 을 수정하고 불투명 한 토큰을 사용하십시오.
계획 캐시: 매개 변수화 사용; 호출 당 "고유 한" SQL을 생성하지 않습니다.

18) 이주 및 운영

온라인으로 색인을 추가하고 INVISIBLE/CONCurrENT, 테스트 계획으로 표시 한 다음 전환하십시오.
색인 개정-정기적 인 위생 청소: 오래된 기능에 대한 복제, 사용되지 않은 "죽은".
파티 교체 계획 (오래된 드롭) 및 'VACUUM/OPTIMIZE' 일정.

19) 요약

쿼리 최적화는 올바른 키 및 색인, 깔끔한 계획, 신중한 파티션 및 샤딩, 쿼리 및 ORM 분야, 캐싱 및 관찰 가능성 등 시스템 엔지니어링입니다. 설명 된 패턴을 따르면 데이터 성장 및로드에 강한 빠르고 예측 가능하며 경제적 인 시스템을 얻을 수 있습니다.