상품 목록이 느렸다. 한 페이지에 50개를 보여 주는데 8초쯤 걸린다. 쿼리 로그를 켜고 한 번 열어 보니 102줄이 찍혔다.
Table of contents
Open Table of contents
행마다 조회하고 있었다
목록 코드는 이랬다.
$products = $this->db->query("SELECT * FROM product WHERE use_yn='Y' LIMIT 50")->result();
foreach ($products as $p) {
$p->brand = $this->db->query(
"SELECT name FROM brand WHERE brand_no = {$p->brand_no}")->row();
$p->stock = $this->db->query(
"SELECT SUM(qty) AS q FROM stock WHERE product_no = {$p->product_no}")->row();
}목록 1번 그리고 행마다 2번이라 50행이면 101번이다.
한 번 한 번은 빠르다. 밀리초 단위인데 왕복 비용이 붙어 100번이면 그것만으로 몇 초가 된다. 느린 쿼리를 찾는 것으로는 원인이 안 나오는 종류였다.
한 번에 가져오게 묶었다
브랜드는 조인으로 붙였다.
SELECT p.*, b.name AS brand_name
FROM product p
LEFT JOIN brand b ON b.brand_no = p.brand_no
WHERE p.use_yn = 'Y'
LIMIT 50재고는 집계라 조인에 넣으면 행이 부풀 수 있어서 따로 한 번 더 조회했다.
$nos = array_column($products, 'product_no');
$in = implode(',', array_map('intval', $nos));
$stocks = $this->db->query(
"SELECT product_no, SUM(qty) AS q FROM stock
WHERE product_no IN ({$in}) GROUP BY product_no")->result();돌려받은 것을 키로 바꿔서 붙인다.
$map = [];
foreach ($stocks as $s) $map[$s->product_no] = $s->q;
foreach ($products as $p) $p->stock = isset($map[$p->product_no]) ? $map[$p->product_no] : 0;101번이 2번이 됐다.
그래도 3초쯤 걸렸다. 재고 조회가 오래 걸리는 것이었다. 묶고 나니 한 번의 무게가 드러났다.
실행 계획으로 본 남은 무게
EXPLAIN 을 붙여 봤다.
+----+-------+------+---------------+------+---------+------+--------+-------------+
| id | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | stock | ALL | NULL | NULL | NULL | NULL | 184320 | Using where |
+----+-------+------+---------------+------+---------+------+--------+-------------+type 이 ALL 이라 전체를 훑는다. possible_keys 가 비었으니 쓸 인덱스 자체가 없다.
SHOW INDEX FROM stock;기본 키만 있고 product_no 에 인덱스가 없었다.
ALTER TABLE stock ADD INDEX idx_product (product_no);다시 EXPLAIN 을 봤다.
| id | table | type | key | rows |
| 1 | stock | range | idx_product | 138 |range 로 바뀌고 rows 가 18만에서 138로 줄었다.
rows 는 추정치라 실제 매칭 수를 따로 세어 봤다.
SELECT COUNT(*) FROM stock WHERE product_no IN (...);126행이었다. 추정과 비슷했고 전체 응답은 0.4초가 됐다.
검증 — 순서가 중요했다
느린 목록에는 대개 반복 조회와 인덱스 문제가 둘 다 있었다.
행마다 조회가 나가면 그 조회 하나하나가 빨라서 인덱스가 없어도 티가 안 난다. 100번을 2번으로 줄이면 그제야 한 번의 무게가 드러난다.
1. 쿼리 로그를 켜서 몇 번 나가는지 센다
2. 반복 조회를 묶는다
3. 남은 쿼리에 EXPLAIN 을 걸어 인덱스를 본다
4. 인덱스를 넣고 다시 잰다반대로 하면 인덱스를 아무리 넣어도 100번은 100번이다. 빨라진 시간에 100을 곱한 만큼이 여전히 남는다.
페이지 번호를 그리려고 전체 개수를 세는 쿼리도 봤다.
SELECT COUNT(*) FROM product WHERE use_yn='Y'이건 조건에 인덱스가 걸려 있어 빨랐다. 다만 use_yn 이 Y 인 행이 대부분이라 인덱스를 타도 거의 전부를 센다.
당장은 감당이 됐지만 행이 늘면 여기가 다음 병목이 된다는 것을 적어 뒀다.
정리
- 목록을 그리며 행마다 조회하면 쿼리 수가 행 수를 따라간다
- 한 번은 빠르지만 왕복 비용이 붙는다. 100번이면 그것만으로 몇 초다
- 느린 쿼리만 봐서는 원인이 안 나온다. 쿼리 로그로 횟수를 센다
- 단순 참조는 조인으로 붙인다
- 집계는 식별자를 모아
IN으로 한 번에 조회하고 키로 붙인다 - 묶고 나면 한 번의 무게가 드러난다.
EXPLAIN의type과key를 본다 rows는 추정치다. 실제 매칭 수를COUNT(*)로 따로 센다- 순서는 쿼리 수와 묶기가 먼저고
EXPLAIN과 인덱스가 나중이다