행이 늘자 목록이 느려졌다

목록 화면이 느려졌다. 처음에는 빨랐는데 자료가 쌓이면서 느려졌다.

SELECT COUNT(*) FROM products;

수만 건이었다.

Open Table of contents

증상 — 스무 건만 필요한데 전부 훑었다
하나로 묶은 인덱스
안 쓰는 컬럼까지 읽고 있었다
개수 조회를 없앴다
정리

증상 — 스무 건만 필요한데 전부 훑었다

목록 쿼리는 이랬다.

SELECT * FROM products
 WHERE status = 1 AND deleted = 0
 ORDER BY created_at DESC
 LIMIT 20;

스무 건만 가져오는데 왜 느린지 이해가 안 됐다. 찾아보니 어떻게 도는지 보는 방법이 있었다.

EXPLAIN SELECT ...;

결과가 이랬다.

type    ALL
rows    수만
Extra   Using filesort

type 이 ALL 이고 Extra 에 Using filesort 가 있다. 전부 훑고 정렬한다는 뜻이다.

정렬을 하려면 어느 것이 앞인지 알아야 하고 그것을 알려면 전부 봐야 한다. LIMIT 은 읽는 양을 줄여 주지 않는다.

다만 덧붙일 것이 있다. LIMIT N 이 붙으면 filesort 는 우선순위 큐를 써서 N 개만 들고 간다. 정렬 메모리는 그만큼 줄지만 훑는 행 수는 그대로다. 이 최적화가 걸렸는지는 EXPLAIN 에 안 나오고 optimizer trace 의 filesort_priority_queue_optimization 을 봐야 한다.

하나로 묶은 인덱스

인덱스를 봤다.

SHOW INDEX FROM products;

기본 키만 있었다. 조건은 status 와 deleted 이고 정렬은 created_at 이라 처음엔 각각 걸었다.

ALTER TABLE products ADD INDEX (status);
ALTER TABLE products ADD INDEX (created_at);

나아졌는데 key 에 하나만 잡히고 정렬은 여전히 남았다. 찾아보니 여러 컬럼을 하나로 묶을 수 있었다.

ALTER TABLE products ADD INDEX (status, deleted, created_at);

다시 계획을 보니 type 이 ref 가 되고 Using filesort 가 사라졌다. 인덱스가 이미 그 순서로 정렬돼 있어서 앞에서 스무 건만 읽고 멈춘다.

묶을 때 순서가 중요했다. 컬럼 순서를 (created_at, status, deleted) 로 바꿔 보니 정렬은 없어져도 조건이 안 좁혀졌다.

조건에 쓰는 컬럼이 앞이고 정렬에 쓰는 컬럼이 뒤여야 한다. 다만 앞 컬럼이 등호로 고정될 때만 성립한다. 매뉴얼이 드는 예도 WHERE key_part1 = constant ORDER BY key_part2 인데 앞이 상수라 뒤 값이 인덱스 순서 그대로이기 때문이다. 앞에 범위 조건을 걸면 뒤 컬럼은 구간마다 흩어져 Using filesort 가 돌아온다.

안 쓰는 컬럼까지 읽고 있었다

또 하나 걸린 것이 있었다.

SELECT *

모든 컬럼을 가져오는데 목록 화면에서 쓰는 것은 몇 개뿐이었다. 특히 상품 설명이 TEXT 라 컸다.

SELECT id, name, price, created_at

필요한 것만 적으니 읽는 양이 줄었다. 스무 건이어도 그 스무 건의 긴 글을 다 읽는 비용이 없어졌다.

인덱스에 있는 컬럼만 쓰면 표를 안 읽고 그때 Extra 에 Using index 가 뜬다. InnoDB 는 기본 키가 보조 인덱스에 이미 들어 있어서 목록에 쓸 것이 기본 키와 인덱스 컬럼뿐이면 그것으로 끝난다.

개수 조회를 없앴다

페이지 번호를 만들려고 전체 개수도 세고 있었다.

SELECT COUNT(*) FROM products WHERE ...;

이건 조건에 맞는 행을 전부 세야 하니 인덱스가 있어도 그 수만큼 본다. SQL_CALC_FOUND_ROWS 로 묶는 것도 해 봤는데 어차피 전부 세는 것은 같아서 빨라지지 않았다.

정확한 개수가 필요한지 물어봤다. 목록 화면에서는 다음 페이지가 있는지만 알면 됐다.

LIMIT 21

스물한 건을 가져와서 21건이 오면 다음이 있고 20건 이하면 없다. 개수 세기가 없어졌다.

다만 관리자 화면은 총 건수가 필요해서 그대로 뒀다. 필요한 곳만 센다.

고친 뒤 EXPLAIN 을 다시 떠서 만든 인덱스가 key 에 잡히는지 봤다. 만들었는데 안 쓰이는 경우가 있고 앞의 컬럼 순서가 그랬다.

정리

LIMIT 20 이어도 정렬 때문에 전부 훑는다
LIMIT 은 정렬 메모리를 줄일 뿐 읽는 행 수는 안 줄인다
그 최적화는 EXPLAIN 이 아니라 optimizer trace 에 나온다
Extra 에 Using filesort 가 있는지로 가른다
조건과 정렬을 하나로 묶은 인덱스가 Using filesort 를 없앤다
조건이 앞이고 정렬이 뒤여야 하며 앞 컬럼이 등호로 고정될 때만 성립한다
SELECT * 는 목록에 안 쓰는 TEXT 까지 읽는다
인덱스만으로 끝나면 Extra 에 Using index 가 뜬다
COUNT(*) 는 인덱스가 있어도 그 수만큼 센다
LIMIT 21 로 다음 페이지 유무를 안다
걸고 나서 다시 확인한다. 만들어도 안 쓰이는 경우가 있다