• 목록

검색 성능을 개선하는 방법은 데이터베이스 최적화와 쿼리 효율성을 높이는 것입니다.
다음은 검색 속도를 개선할 수 있는 몇 가지 방법입니다.

1. 정규화된 검색어를 미리 저장하고 인덱스 생성하기
현재 쿼리는 REPLACE 함수로 특수문자를 제거하고 공백을 없애서 검색을 수행하고 있습니다. 대신, 특수문자와 공백이 제거된 정규화된 검색어를 데이터베이스에 미리 저장하고, 이 컬럼에 인덱스를 설정하여 검색 속도를 개선할 수 있습니다.

예시: TABLE mp3 sub(제목컬럼)
테이블에 새로운 컬럼 sub_normalized 추가
ALTER TABLE mp3 ADD COLUMN sub_normalized VARCHAR(255);

모든 행에 대해 정규화된 제목을 추가
UPDATE mp3 SET sub_normalized = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(sub, ' ', ''), '-', ''), '_', ''), '!', ''), '@', '');

인덱스를 추가하여 검색을 빠르게
CREATE INDEX idx_sub_normalized ON mp3(sub_normalized);

쿼리를 변경하여 sub_normalized 컬럼에서 검색
$search_condition = "WHERE sub_normalized LIKE '%$search_normalized%'";

이렇게 하면 검색 시 매번 REPLACE 함수로 문자열을 변환할 필요가 없으며, 인덱스 덕분에 검색 속도가 훨씬 빨라질 수 있습니다.

2. Full-Text 인덱스 사용하기

만약 검색 조건이 더 복잡하거나 전체 텍스트 검색이 필요하다면, MySQL의 FULLTEXT 인덱스를 사용할 수 있습니다. LIKE 조건보다는 FULLTEXT 인덱스를 활용한 검색이 훨씬 빠를 수 있습니다.

테이블에 FULLTEXT 인덱스를 추가
ALTER TABLE mp3 ADD FULLTEXT(sub);

쿼리를 LIKE 대신 MATCH를 사용해 변경
$search_condition = "WHERE MATCH(sub) AGAINST('$search_normalized' IN BOOLEAN MODE)";

이 방법을 사용하면 대량의 텍스트 데이터를 빠르게 검색할 수 있습니다.
다만, FULLTEXT 인덱스는 매우 짧은 단어나 특수문자 검색에는 적합하지 않으므로 검색 요구 사항에 맞게 조정이 필요할 수 있습니다.

FULLTEXT 인덱스에서 2글자까지 검색할 수 있도록 설정하려면, MySQL 또는 MariaDB의 설정 파일 (my.cnf 또는 my.ini)에 다음 옵션을 추가해야 합니다.

ft_min_word_len (MySQL) 또는 innodb_ft_min_token_size (InnoDB에서 사용하는 경우)
ft_min_word_len: MyISAM 엔진에서 사용하는 최소 검색 단어 길이를 설정합니다.
innodb_ft_min_token_size: InnoDB 엔진에서 사용하는 최소 검색 토큰 크기를 설정합니다.
먼저 my.ini 파일에 아래와 같은 설정을 추가하세요.
[mysqld]
ft_min_word_len=2
innodb_ft_min_token_size=2
설정을 변경한 후에는 MySQL/MariaDB 서버를 재시작해야 합니다.
sudo service mysql restart # 리눅스
# 또는
net stop mysql
net start mysql # 윈도우
그리고 나서, 기존 FULLTEXT 인덱스를 재생성해야 합니다. 이미 인덱스를 추가한 경우, 인덱스를 드롭하고 다시 생성하세요.
ALTER TABLE mp3 DROP INDEX sub;
ALTER TABLE mp3 ADD FULLTEXT(sub);

이제 2글자 단어도 검색이 가능합니다.

3. LIMIT과 OFFSET 최적화

LIMIT과 OFFSET을 사용한 페이징은 기본적으로 성능에 영향을 미칠 수 있습니다. 특히 OFFSET 값이 클수록 성능 저하가 심해질 수 있습니다. 이를 개선하려면 인덱스 기반 페이징을 사용하는 방법이 있습니다.

예를 들어, id 컬럼을 기준으로 페이징을 수행할 수 있습니다
$last_id = isset($_GET['last_id']) ? intval($_GET['last_id']) : 0;
if ($last_id > 0) {
$sql = "SELECT id, sub, suburl FROM mp3 $search_condition AND id < $last_id ORDER BY id DESC LIMIT $items_per_page";
} else {
$sql = "SELECT id, sub, suburl FROM mp3 $search_condition ORDER BY id DESC LIMIT $items_per_page";
}
이 방법은 페이지당 항목을 불러올 때 OFFSET 대신 마지막으로 불러온 id를 기준으로 검색하므로 성능이 개선될 수 있습니다.

4. 캐싱 도입

검색어가 자주 반복되는 경우, 검색 결과를 캐시하여 동일한 검색어에 대해 데이터베이스에 다시 쿼리하지 않도록 할 수 있습니다.
예를 들어, Redis나 Memcached와 같은 인메모리 캐시 시스템을 사용하여 검색 결과를 일정 시간 동안 저장하고, 동일한 검색어에 대해 캐시된 결과를 반환하는 방식으로 성능을 크게 개선할 수 있습니다.

5. 데이터베이스 서버 튜닝

데이터베이스 자체의 성능을 향상시키기 위해 MySQL/MariaDB 서버 설정을 최적화하는 방법도 있습니다. 데이터베이스 튜닝은 innodb_buffer_pool_size, query_cache_size, tmp_table_size 등의 설정을 조정하여 대규모 데이터에 대한 쿼리 성능을 개선할 수 있습니다.

요약
1.정규화된 검색어를 미리 저장하고 인덱스를 추가.
2.FULLTEXT 인덱스를 사용해 빠른 검색 수행.
3.OFFSET 대신 인덱스 기반 페이징을 사용하여 성능 향상.
4.검색 결과 캐시를 도입해 반복적인 검색 요청에 대해 응답 속도를 높이기.
5.데이터베이스 서버 튜닝을 통해 전체적인 성능 최적화.
이러한 방법들을 적용하면 100만 건 이상의 데이터를 검색할 때 성능을 크게 개선할 수 있습니다.

정규화된 검색어와 인덱스와 FULLTEXT 인덱스를 동시에 적용할 수 있습니다.
하지만 FULLTEXT 인덱스를 사용할 경우,
이미 1번에서 제안한 대로 특수문자와 공백을 제거한 sub_normalized 컬럼을 대상으로 FULLTEXT 인덱스를 추가할 수 없습니다.
대신, 원본 텍스트인 sub 컬럼에 대해 FULLTEXT 인덱스를 적용해야 합니다.

아래에서 정규화된 검색어 방식과 FULLTEXT 검색을 함께 적용하는 방법을 설명하겠습니다.

1. 정규화된 검색어와 FULLTEXT 인덱스 적용 방법

1.1 FULLTEXT 인덱스 추가
sub 컬럼에 FULLTEXT 인덱스를 추가하려면 아래와 같은 SQL 명령어를 사용합니다
ALTER TABLE mp3 ADD FULLTEXT(sub);

1.2 FULLTEXT 검색을 사용한 쿼리
정규화된 검색어를 처리하면서 FULLTEXT 검색을 사용하는 방법입니다. FULLTEXT 인덱스는 공백이나 특수문자를 무시하는 기능이 있어, 정규화 과정 없이도 충분히 성능을 높일 수 있습니다.

쿼리를 다음과 같이 변경할 수 있습니다.
if ($search) {
$search_condition = "WHERE MATCH(sub) AGAINST('$search_normalized' IN BOOLEAN MODE)";
}

여기서 AGAINST는 MySQL의 FULLTEXT 검색 메서드이며, IN BOOLEAN MODE 옵션을 사용하여 복잡한 검색을 가능하게 합니다. 이 옵션은 다음과 같은 고급 기능을 제공합니다:

+: 반드시 포함되어야 하는 단어.
-: 포함되어서는 안 되는 단어.
*: 와일드카드로 접두사 검색.
예를 들어, +hello -world는 "hello"를 반드시 포함하고, "world"를 포함하지 않는 결과를 찾습니다.

2. FULLTEXT와 LIKE 검색의 조합

특정 상황에서는 FULLTEXT 인덱스를 사용하되, 특수문자와 공백을 처리한 정규화된 검색이 필요할 수 있습니다. 이때 FULLTEXT 검색과 정규화된 검색을 조합하는 것도 가능합니다.

예를 들어, 아래와 같은 방식으로 조합할 수 있습니다.
if ($search) {
$search_condition = "WHERE MATCH(sub) AGAINST('$search_normalized' IN BOOLEAN MODE)
OR sub_normalized LIKE '%$search_normalized%'";
}

이 방식은 FULLTEXT 검색이 빠르게 동작하되, 정규화된 검색도 보조적으로 동작하도록 합니다.

3. 주의 사항
FULLTEXT 인덱스는 MyISAM 및 InnoDB 엔진 모두에서 사용할 수 있지만,
InnoDB에서 사용 시 MySQL 버전에 따라 기능이 조금 다를 수 있습니다.
따라서 사용하는 MySQL 버전에서 FULLTEXT 검색이 어떻게 동작하는지 확인해야 합니다.
FULLTEXT 검색은 매우 유연한 반면, 아주 짧은 단어나 일반적인 단어에 대해서는 잘 검색되지 않을 수 있습니다. 이 점은 미리 고려해야 합니다.

최종 예시 코드
$conn = new mysqli($servername, $username, $password, $dbname);

// 연결 확인
if ($conn->connect_error) {
die("연결 실패: " . $conn->connect_error);
}

$items_per_page = 50; // 페이지당 항목 수
$pages_to_show = 5; // 표시할 페이지 번호 갯수

// 검색어 가져오기 및 정규화
$search = isset($_GET['search']) ? $_GET['search'] : '';
$search_normalized = preg_replace('/[^\p{L}\p{N}]/u', '', $search); // 특수문자 및 공백 제거

// 페이지 설정
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$offset = ($page - 1) * $items_per_page; // OFFSET 계산

// FULLTEXT와 LIKE 쿼리 결합
$search_condition = '';
if ($search) {
$search_condition = "WHERE MATCH(sub) AGAINST('$search_normalized' IN BOOLEAN MODE)
OR sub_normalized LIKE '%$search_normalized%'";
}

// 총 데이터 수 조회
$total_sql = "SELECT COUNT(*) AS total FROM mp3 $search_condition";
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_assoc();
$total_items = $total_row['total'];
$total_pages = ceil($total_items / $items_per_page); // 총 페이지 수

// 페이지별 데이터 조회 (역순으로 정렬)
$sql = "SELECT id, sub, suburl FROM mp3 $search_condition ORDER BY id DESC LIMIT $items_per_page OFFSET $offset";
$result = $conn->query($sql);

// 페이지 번호 범위 설정
$start_page = max(1, $page - floor($pages_to_show / 2));
$end_page = min($total_pages, $start_page + $pages_to_show - 1);

결론
정규화된 검색어를 위한 별도의 sub_normalized 컬럼을 사용할 수 있으며,
이 컬럼에 대해 LIKE 검색을 수행합니다.
동시에, 원본 sub 컬럼에 대해 FULLTEXT 인덱스를 생성하여,
성능을 개선하면서도 고급 검색 기능을 제공합니다.
이 두 방법을 함께 적용하면, 검색 속도가 크게 개선되면서도 유연한 검색 기능을 유지할 수 있습니다.