소개
이 랩에서는 MySQL 의 전문 검색 (full-text search) 기능을 탐색합니다. id, title, content 열을 가진 articles라는 데이터베이스와 테이블을 생성하고 샘플 데이터로 채우는 것부터 시작합니다.
다음으로, ALTER TABLE 문을 사용하여 articles 테이블에 title 및 content 열에 대한 article_index라는 전문 검색 인덱스를 추가합니다. 마지막으로 SHOW INDEXES 명령을 사용하여 인덱스 생성을 확인하고 Index_type이 FULLTEXT인지 확인합니다. 이 설정은 MySQL 데이터베이스 내에서 효율적인 텍스트 검색을 준비합니다.
이 랩 전체에서 MySQL 셸 내에서 작업하게 됩니다. MySQL 셸에는 시작 시 한 번만 들어가고 끝날 때 나오면 됩니다. 다음 단계의 모든 SQL 명령은 동일한 MySQL 세션 내에서 실행해야 합니다.
데이터베이스 및 테이블 생성
이 단계에서는 기사를 저장할 MySQL 데이터베이스와 테이블을 생성합니다. 이 테이블은 전문 검색 기능을 시연하는 데 사용됩니다.
먼저 터미널을 열고 root 사용자로 MySQL 서버에 연결합니다. 다음 명령을 사용하여 연결할 수 있습니다.
sudo mysql -u root
이 명령은 root 사용자를 사용하여 MySQL 서버에 연결합니다. sudo를 사용하므로 비밀번호를 묻는 메시지가 표시되지 않습니다.
MySQL 셸에 연결되면 mysql> 프롬프트가 표시됩니다. 랩이 끝날 때까지 후속 단계에서는 모두 MySQL 셸에 머물러 있어야 합니다.
이제 search_db라는 데이터베이스를 생성합니다. 이 데이터베이스는 articles 테이블을 보유합니다. 다음 SQL 명령을 실행합니다.
CREATE DATABASE IF NOT EXISTS search_db;
IF NOT EXISTS 절은 데이터베이스가 아직 존재하지 않는 경우에만 생성되도록 합니다.
다음으로 USE 명령을 사용하여 새로 생성된 데이터베이스로 전환합니다.
USE search_db;
데이터베이스가 변경되었음을 나타내는 메시지가 표시되어야 합니다.
이제 articles 테이블을 생성합니다. 이 테이블에는 id, title, content 세 개의 열이 있습니다.
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT
);
이 명령을 자세히 살펴보겠습니다.
CREATE TABLE articles: 이 문은articles라는 새 테이블을 생성합니다.id INT AUTO_INCREMENT PRIMARY KEY: 각 새 행에 대해 자동으로 증가하고 테이블의 기본 키 역할을 하는id라는 정수 열을 정의합니다.title VARCHAR(255) NOT NULL: 최대 길이가 255 자인title이라는 문자열 열을 정의합니다.NOT NULL은 이 열이 비어 있을 수 없음을 의미합니다.content TEXT: 더 큰 문자열을 저장할 수 있는content라는 텍스트 열을 정의합니다.
이 명령을 실행한 후 search_db 데이터베이스 내에 articles 테이블이 생성됩니다.
현재 데이터베이스의 테이블을 나열하여 테이블 생성을 확인할 수 있습니다.
SHOW TABLES;
출력에서 articles가 나열되어야 합니다.
+-----------------------+
| Tables_in_search_db |
+-----------------------+
| articles |
+-----------------------+
1 row in set (0.00 sec)
데이터베이스와 테이블을 생성했으므로 다음 단계에서 샘플 데이터로 채울 준비가 되었습니다.
샘플 데이터 삽입 및 전문 검색 인덱스 추가
이 단계에서는 articles 테이블에 샘플 데이터를 삽입한 다음 효율적인 텍스트 검색을 활성화하기 위해 전문 검색 인덱스를 추가합니다.
MySQL 셸에 계속 있고 search_db 데이터베이스를 사용하고 있는지 확인합니다. 그렇지 않은 경우 데이터베이스를 선택합니다.
USE search_db;
이제 articles 테이블에 샘플 데이터를 삽입해 보겠습니다. 제목과 내용이 다른 세 개의 행을 추가합니다.
INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');
이 INSERT 문은 articles 테이블에 여러 행을 추가합니다. VALUES 뒤의 각 괄호 쌍은 새 행을 나타내며, 값은 title 및 content 열에 해당합니다.
테이블에서 모든 행을 선택하여 데이터가 삽입되었는지 확인할 수 있습니다.
SELECT * FROM articles;
출력에서 삽입된 세 개의 행이 표시되어야 합니다.
+----+--------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+--------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)
이제 테이블에 데이터가 삽입되었으므로 전문 검색 인덱스를 추가해 보겠습니다. 전문 검색 인덱스를 사용하면 MySQL 이 텍스트 데이터에 대해 빠르고 관련성 높은 검색을 수행할 수 있습니다. title 및 content 열 모두에 전문 검색 인덱스를 추가합니다.
ALTER TABLE 문을 사용하여 인덱스를 추가합니다.
ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);
이 명령을 자세히 살펴보겠습니다.
ALTER TABLE articles:articles테이블을 수정하고 있음을 나타냅니다.ADD FULLTEXT INDEX article_index:FULLTEXT유형의 새 인덱스를 추가하고article_index라는 이름을 지정합니다.(title, content): 전문 검색 인덱스에 포함될 열을 지정합니다.
이 명령을 실행한 후 MySQL 은 지정된 열에 전문 검색 인덱스를 빌드합니다.
전문 검색 인덱스가 성공적으로 생성되었는지 확인하려면 SHOW INDEXES 명령을 사용할 수 있습니다.
SHOW INDEXES FROM articles;
출력에는 articles 테이블의 모든 인덱스가 표시됩니다. Index_type 열에 FULLTEXT가 있는 article_index 항목이 표시되어야 합니다.
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| articles | 1 | article_index | 1 | title | NULL | 3 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
| articles | 1 | article_index | 2 | content | NULL | 3 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)
이제 테이블에 데이터를 성공적으로 삽입하고 전문 검색 인덱스를 추가했습니다. 다음 단계에서는 이 인덱스를 사용하여 전문 검색을 수행하는 방법을 배웁니다.
기본 전문 검색 수행
이 단계에서는 MATCH AGAINST 절을 사용하여 articles 테이블에서 기본 전문 검색을 수행합니다. 이는 생성한 전문 검색 인덱스를 활용하는 기본적인 방법입니다.
MySQL 셸에 계속 있고 search_db 데이터베이스를 사용하고 있는지 확인합니다. 그렇지 않은 경우 데이터베이스를 선택합니다.
USE search_db;
MATCH AGAINST 절은 SELECT 문의 WHERE 절 내에서 사용됩니다. 기본 구문은 다음과 같습니다.
SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');
여기서 column_list는 전문 검색 인덱스에 포함된 열의 쉼표로 구분된 목록 (이 경우 title 및 content) 이고, 'search_term'은 검색하려는 단어 또는 구문입니다.
"MySQL"이라는 단어를 포함하는 기사를 검색해 보겠습니다.
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');
이 쿼리는 title 또는 content 열이 "MySQL"이라는 용어와 일치하는 articles 테이블에서 id, title, content 열을 선택합니다.
다음과 같은 출력이 표시되어야 합니다.
+----+--------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+--------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)
세 기사 모두 "MySQL"이라는 단어를 포함하므로 모두 반환됩니다.
전문 검색은 각 행이 검색어와 얼마나 잘 일치하는지를 나타내는 관련성 점수도 제공합니다. 이 점수를 SELECT 문에 포함할 수 있습니다.
SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;
이 쿼리는 각 행의 점수를 보여주는 relevance라는 열을 추가합니다.
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title | content | relevance |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. | 1.34832763671875 |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)
결과를 관련성별로 정렬하여 최상의 일치를 먼저 볼 수 있습니다.
SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;
이 쿼리는 결과를 relevance 점수를 기준으로 내림차순으로 정렬합니다.
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title | content | relevance |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. | 1.34832763671875 |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)
이 단계에서는 MATCH AGAINST를 사용하여 기본 전문 검색을 수행하는 방법과 관련성별로 결과를 검색하고 정렬하는 방법을 배웠습니다. 다음 단계에서는 부울 모드를 사용하여 더 고급 검색 옵션을 탐색합니다.
불리언 모드를 사용한 고급 검색
이 단계에서는 MATCH AGAINST 절의 부울 모드를 사용하여 더 정확하고 유연한 전문 검색을 수행합니다. 부울 모드를 사용하면 연산자를 사용하여 결과에 포함되어야 하거나 포함되지 않아야 하는 단어를 제어할 수 있습니다.
MySQL 셸에 계속 있고 search_db 데이터베이스를 사용하고 있는지 확인합니다. 그렇지 않은 경우 데이터베이스를 선택합니다.
USE search_db;
부울 모드를 사용하려면 MATCH AGAINST 문에 IN BOOLEAN MODE 절을 추가합니다. 구문은 다음과 같습니다.
SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);
다음은 부울 모드에서 사용되는 일반적인 연산자입니다.
+: 결과에 단어가 반드시 포함되어야 함을 나타냅니다.-: 단어를 포함하는 행을 제외합니다."": 정확한 구문을 검색합니다.*: 와일드카드 연산자 (단어 끝에 사용).
"MySQL"이라는 단어를 반드시 포함하고 "optimizing"이라는 단어를 포함하지 않는 기사를 찾아보겠습니다.
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);
이 쿼리는 + 연산자를 사용하여 "MySQL"을 요구하고 - 연산자를 사용하여 "optimizing"을 제외합니다.
다음과 같은 출력이 표시되어야 합니다.
+----+------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
"Optimizing MySQL Queries"에 대한 기사는 "optimizing"을 포함하므로 제외됩니다.
이제 "full-text search"라는 정확한 구문을 검색해 보겠습니다.
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);
큰따옴표를 사용하면 단어를 연속된 구문으로 검색합니다.
출력은 다음과 같습니다.
+----+------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
"full-text search"라는 정확한 구문을 포함하는 첫 번째 기사만 반환됩니다.
와일드카드 연산자 *를 사용해 보겠습니다. "index"로 시작하는 단어를 포함하는 기사를 찾고 싶다고 가정해 보겠습니다.
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);
index* 용어는 "index" 및 "indexing"과 같은 단어와 일치합니다.
출력은 다음과 같습니다.
+----+------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+------------------------+---------------------------------------------------------------------+
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
이 단계에서는 MATCH AGAINST 절과 다양한 연산자를 사용하여 부울 모드를 사용하여 더 제어되고 구체적인 전문 검색을 수행하는 방법을 배웠습니다. 다음 단계에서는 이러한 기술을 결합하는 연습을 합니다.
검색 기법 결합하기
이 단계에서는 기본 MATCH AGAINST 검색과 부울 모드 연산자를 결합하여 더 복잡하고 정교한 전문 검색 쿼리를 수행합니다.
MySQL 셸에 계속 있고 search_db 데이터베이스를 사용하고 있는지 확인합니다. 그렇지 않은 경우 데이터베이스를 선택합니다.
USE search_db;
"MySQL"이라는 단어를 포함하고 "indexing" 또는 "optimizing"이라는 단어 중 하나를 포함하는 기사를 찾아보겠습니다. 부울 모드에서 선택적 용어를 그룹화하기 위해 괄호를 사용할 수 있습니다.
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);
이 쿼리는 "MySQL"(+MySQL) 을 요구하고 괄호 안의 용어 중 적어도 하나 (+(indexing optimizing)) 를 요구합니다.
출력은 다음과 같습니다.
+----+--------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+--------------------------+---------------------------------------------------------------------+
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
첫 번째 기사는 "MySQL"을 포함하지만 "indexing" 또는 "optimizing"을 포함하지 않으므로 제외됩니다.
부울 모드를 관련성 점수와 결합할 수도 있습니다. "MySQL"을 포함하는 기사를 찾고 > 연산자를 사용하여 "indexing"을 언급하는 기사의 관련성을 우선적으로 지정해 보겠습니다.
SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;
이 쿼리는 "MySQL"을 요구하고 "indexing"을 포함하는 기사의 관련성 점수를 높입니다. 그런 다음 계산된 관련성별로 결과를 정렬합니다.
출력은 다음과 같습니다.
+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title | content | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. | 1.6931 |
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. | 0.3068 |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.3068 |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)
"Indexing in MySQL" 기사가 >indexing 연산자로 인해 가장 높은 순위를 차지하는 것을 알 수 있습니다.
기본 검색과 부울 연산자 및 관련성 점수를 결합하여 특정 요구 사항에 맞는 강력하고 유연한 전문 검색 쿼리를 만들 수 있습니다.
이것으로 MySQL 전문 검색 기능에 대한 실습이 끝났습니다. 다음 명령을 입력하여 MySQL 셸을 종료할 수 있습니다.
exit
전문 검색 인덱스가 있는 테이블을 설정하고, 기본 검색을 수행하고, 더 고급 쿼리를 위해 부울 모드를 사용하는 방법을 배웠습니다.
요약
이 실습에서는 MySQL 의 전문 검색 기능에 대해 배웠습니다. search_db라는 데이터베이스와 텍스트 데이터를 저장할 articles 테이블을 생성하는 것으로 시작했습니다. 그런 다음 샘플 기사로 테이블을 채웠습니다.
핵심 단계는 ALTER TABLE 문을 사용하여 articles 테이블의 title 및 content 열에 article_index라는 전문 검색 인덱스를 추가하는 것이었습니다. 이 인덱스는 효율적인 전문 검색에 필수적입니다. SHOW INDEXES를 사용하여 인덱스 생성을 확인했습니다.
마지막으로 MATCH AGAINST 절을 사용하여 전문 검색을 수행하는 방법을 탐색했습니다. 기본 검색을 수행하고, 관련성 점수를 검색하고, 관련성별로 결과를 정렬하는 방법을 배웠습니다. 또한 +, -, "", *와 같은 연산자를 사용하여 더 정확하고 복잡한 검색 쿼리를 만드는 부울 모드를 살펴보았습니다. 이러한 기술을 결합하면 MySQL 데이터베이스에 저장된 텍스트 데이터에서 관련 정보를 효과적으로 검색하고 검색할 수 있습니다.
