[Node.js & MySQL] 도전과제: 검색 - 색인기능 살펴보기

자주 검색하는 컬럼이라면 색인을 넣어주자. 검색 속도가 빨라진다.

아래처럼 인덱스를 만들어주고, 기존대로 WHERE 컬럼명 = ?으로 검색하면 된다.

다만 데이터가 꽤 많아야(테스트했을 때는 몇십만건 정도) 유의미한 차이를 느낄 수 있다.

CREATE INDEX index_category ON 테이블명(컬럼명)

---

 

생활코딩 Node.js - MySQL 수업은 이고잉님이 추가 도전과제를 던져주는 것으로 끝난다.

검색, 정렬(저자 이름 순, 시간 순), 페이지 기능을 구현해보면 좋겠다. 그리고 팁을 몇 가지 준다.

 

1. 검색

<form>의 get 방식을 이용해서 검색 요청을 처리하고,

db단에서 SQL구문 SELECT * FROM topic WHERE title = "keyword"로 데이터를 찾는다.

이때 데이터가 많으면 느려질 수 있으니 index 기능을 사용하는 것이 좋다.

 

2. 정렬

SELECT * FROM topic ORDER BY id DESC

 

3. 페이지

SELECT * FROM topic LIMIT 0 OFFSET 20

 

순서대로 하나씩 해볼 예정인데, 먼저 그전에 검색에서 색인 기능이 왜 필요한지를 알아보고 가기로 했다.

 

 

색인이 내가 아는 그 id 컬럼을 말하는 건 아닐 것 같아서, 검색을 좀 해본 결과

[database] DB 인덱스(INDEX)란? 라는 고마운 글을 찾았다.

책 뒤의 찾아보기 페이지에 빗대어서 db의 색인이 어떻게 구현하는지 설명해준 다음, 쿼리를 알려준다.

 

CREATE INDEX index_category ON 테이블명(컬럼명)

사용시에는 기존과 똑같이 기존 컬럼명을 사용하면 된다.

SELECT * FROM 테이블명 WHERE 컬럼명 = '?'

효과가 어느 정도인지 궁금해져서 속도 테스트를 해보기로 했다.

 

 

INDEX ON 속도 테스트

속도 테스트를 하려면 데이터가 꽤 많아야 하고, 그걸 직접 작성할 수는 없으니 dummydata를 구해야 한다.

자바스크립트도 연습할 겸 직접 만들어보기로 했다.

 

id, alphar, number

alpha: A부터 Z까지 알파벳을 조합해서 3글자 길이의 문자열을 만든다. AAA ~ BBC ~ ZZZ.

26**3이면 17576개의 데이터를 만들 수 있다!

number: 알파벳의 자릿수를 더한다. A면 0, Z면 0. 0 ~ 112 ~ 252525.

 

일단 DB와 테이블을 만들고

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.09 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE dummy(
      id INT(11) NOT NULL AUTO_INCREMENT,
      alpha CHAR(5) NOT NULL,
      numbers BIGINT NOT NULL,
      PRIMARY KEY(id));
Query OK, 0 rows affected (0.22 sec)

데이터를 만들어본다.

var mysql      = require('mysql');
// 비밀번호는 별도의 파일로 분리해서 버전관리에 포함시키지 않아야 합니다. 
var db = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'password',
  database : 'test'
});
  
db.connect();
module.exports = db;

var letter = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

for (var i in letter) {
	for (var j in letter) {	
		for (var k in letter) {
			var a = `${letter[i]}${letter[j]}${letter[k]}`;
			var n = i + j + k;
			db.query('INSERT INTO dummy (alpha, numbers) VALUES (?, ?)', [a, n], function(err, result){
				if (err) throw err;
			});
	    };
	};
};

그렇게 오래 걸리지는 않았다. 1~2분 정도? 중간중간 진행상황을 확인했다.

mysql> SELECT COUNT(*) FROM dummy;

 

그리고 비교용으로 dummy2를 만들고, dummy에만 인덱스를 추가해준다.

mysql> CREATE INDEX index_alpha ON dummy(alpha);

그리고 목록 제일 끝에 있는 ZZZ를 찾아본다.

 

17,576건(26**3)일 때

mysql> SELECT * FROM dummy WHERE alpha = 'ZZZ';
+-------+-------+--------+
| id    | alpha | number |
+-------+-------+--------+
| 17576 | ZZZ   | 252525 |
+-------+-------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM dummy2 WHERE alpha = 'ZZZ';
+-------+-------+--------+
| id    | alpha | number |
+-------+-------+--------+
| 17576 | ZZZ   | 252525 |
+-------+-------+--------+
1 row in set (0.01 sec)

거의 차이가 나지 않는다... 아무래도 데이터 양이 부족한 것 같다. 데이터를 늘려보자.

데이터가 몇 만개가 되니까 그냥 돌리면 타임아웃 에러가 떠서 BULK INSERT 방식으로 변경했다

var mysql      = require('mysql');
// 비밀번호는 별도의 파일로 분리해서 버전관리에 포함시키지 않아야 합니다. 
var db = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'password',
  database : 'test'
});
  
db.connect();
module.exports = db;

var letter = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];


var _promise = function (one, place){
	return new Promise(function (resolve, reject) {

	var text = '';
	for (var i in letter){
		for (var j in letter){
			for (var k in letter){
				text += `('${one}${letter[i]}${letter[j]}${letter[k]}', ${place+i+j+k}), `;
			};
		};
	};
	resolve(text);
	});
};

for (var i in letter){
	_promise(letter[i], i).then(function(text){
		query = 'INSERT INTO dummy (alpha, number) VALUES ' + text.slice(0, -2);
		query2 = 'INSERT INTO dummy2 (alpha, number) VALUES ' + text.slice(0, -2);
		db.query(query, function(err, result){
					if (err) throw err;
		});
		db.query(query2, function(err, result){
					if (err) throw err;
		});
	});
};

Promise를 적용해서 for문으로 작성한 데이터를 벌크로 입력했다. 충격적으로 빨리 진행되었다!!

26**4면 데이터가 45만개가 넘는데 30초가 채 안 걸렸다. 위에서 1.7만개에 2분 가까이 걸린게 얼마나 비효율적이었는지 알겠다. 앞으로는 꼭 벌크 인서트를 써야겠다.

 

데이터가 준비되었으니 다시 테스트를 해본다. 일단 dummy에만 인덱스를 추가해준다.

mysql> CREATE INDEX index_alpha ON dummy(alpha);
Query OK, 0 rows affected (4.55 sec)

4.55초가 걸렸다! 이번에는 차이가 좀 날 것 같다. 목록 마지막에 있는 'ZZZZ'를 검색해본다.

 

456,976건(26**4)일 때

mysql> SELECT * FROM dummy WHERE alpha = 'ZZZZ';
+--------+-------+----------+
| id     | alpha | number   |
+--------+-------+----------+
| 456976 | ZZZZ  | 25252525 |
+--------+-------+----------+
1 row in set (0.02 sec)

mysql> SELECT * FROM dummy2 WHERE alpha = 'ZZZZ';
+--------+-------+----------+
| id     | alpha | number   |
+--------+-------+----------+
| 456976 | ZZZZ  | 25252525 |
+--------+-------+----------+
1 row in set (0.35 sec)

0.2초대 0.35초로 인덱스의 압승이다! 과연 천만개가 넘어가면 어떻게 될까?

천만개는 데이터 만드는 것부터 어려웠다. 벌크로 넣어도 mysql 타임아웃이 나와서 connectTimeout 옵션을 늘려주었다. 총 소요시간은 5분 언저리였던 것 같다. 데이터 생성은 1분 내로 되는데 들어가는 게 오래 걸린다.

 

어쨌든 데이터를 확보했으니 다시 확인해본다. 이번에도 한쪽에만 인덱스를 추가한다.

mysql> CREATE INDEX index_alpha ON dummy5(alpha);
Query OK, 0 rows affected (1 min 16.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

1분 좀 더 걸린다. 각각 'ZZZZZ'를 검색해본다.

 

11,881,376건일 때

mysql> SELECT * FROM dummy5 WHERE alpha = 'ZZZZZ';
+----------+-------+------------+
| id       | alpha | numbers    |
+----------+-------+------------+
| 11881376 | ZZZZZ | 2525252525 |
+----------+-------+------------+
1 row in set (0.05 sec)


mysql> SELECT * FROM dummy6 WHERE alpha = 'ZZZZZ';
+----------+-------+------------+
| id       | alpha | numbers    |
+----------+-------+------------+
| 11881376 | ZZZZZ | 2525252525 |
+----------+-------+------------+
1 row in set (6.49 sec)

0.05초대 6.49초로 상당히 차이가 나기 시작했다. 천만개에서 이정도니까 1억개가 넘어가면 어마어마한 차이가 될 듯하다.

 

 

<결론>

INDEX ON의 중요성을 깨닫긴 했지만, 데이터 45만개까지는 그다지 큰 차이가 없는 듯해서 이번 검색 기능 구현에서는 생략하려고 한다.