MySQL / Full-text search

Full-text search

LIKE '%текст%' нь index ашиглахгүй, бүх мөрийг шалгадаг — том хүснэгтэд маш удаан. Full-text search (FTS) нь текст дотор хайхад зориулагдсан тусгай index ашиглан хурдан, ухаалаг хайлт хийнэ.

FULLTEXT index үүсгэх

sql

CREATE TABLE articles (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  title   VARCHAR(200) NOT NULL,
  body    TEXT,
  author  VARCHAR(100)
);

-- Хүснэгт үүсгэхдээ зэрэг
CREATE TABLE articles (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  body  TEXT,
  FULLTEXT INDEX ft_content (title, body)
);

-- Байгаа хүснэгтэд нэмэх
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);

-- Эсвэл
CREATE FULLTEXT INDEX ft_title ON articles (title);

Тест өгөгдөл

sql

INSERT INTO articles (title, body, author) VALUES
  ('MySQL суулгах заавар',
   'MySQL бол өргөн хэрэглэгддэг өгөгдлийн сангийн систем. Ubuntu дээр суулгахад apt ашиглана.',
   'Болд'),
  ('PostgreSQL vs MySQL харьцуулалт',
   'Хоёр өгөгдлийн сан хоёулаа SQL дэмждэг. PostgreSQL нарийн query-д илүү тохиромжтой.',
   'Оюун'),
  ('MySQL index оновчлол',
   'Index нь query хурдыг эрс сайжруулдаг. B-tree index хамгийн түгээмэл хэрэглэгддэг.',
   'Болд'),
  ('MongoDB болон MySQL ялгаа',
   'MongoDB нь document database, MySQL нь relational database. Хэрэглээнээс хамааран сонгоно.',
   'Дорж');

MATCH ... AGAINST — natural language mode

sql

-- Үндсэн хайлт (natural language mode)
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL');

код

id | title
---|------
1  | MySQL суулгах заавар
3  | MySQL index оновчлол
2  | PostgreSQL vs MySQL харьцуулалт
4  | MongoDB болон MySQL ялгаа

Natural language mode нь хамааралтай байдлаар (relevance score) дараалуулна — хамааралгүй үр дүн автоматаар хасагдаж болно.

Relevance score харах

sql

SELECT
  title,
  MATCH(title, body) AGAINST('MySQL index') AS хамаарал
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL index')
ORDER BY хамаарал DESC;

код

title                          | хамаарал
-------------------------------|--------
MySQL index оновчлол           | 2.847
MySQL суулгах заавар           | 0.923
PostgreSQL vs MySQL харьцуулалт | 0.641

Хамааралтай байдлыг тооцоход үгийн давтамж, баганын жин зэргийг харгалзана.

Boolean mode — нарийн хайлт

sql

SELECT title
FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);

Boolean горимын операторууд:

| Оператор | Утга | Жишээ | | -------- | ------------------ | ---------------------------- | | + | Заавал байх | +MySQL | | - | Заавал байхгүй | -PostgreSQL | | * | Эхлэл тохирох | index* → indexes, indexing | | "..." | Яг тэр хэллэг | "өгөгдлийн сан" | | > | Хамаарал нэмэх | >MySQL | | < | Хамаарал бууруулах | <PostgreSQL | | ( ) | Бүлэглэх | +(MySQL PostgreSQL) |

sql

-- MySQL эсвэл PostgreSQL агуулсан
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('MySQL PostgreSQL' IN BOOLEAN MODE);

-- Яг "өгөгдлийн сан" хэллэг
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('"өгөгдлийн сан"' IN BOOLEAN MODE);

-- index-ээр эхэлсэн бүх үг
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('index*' IN BOOLEAN MODE);

-- MySQL заавал байх, MongoDB байхгүй байх
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -MongoDB' IN BOOLEAN MODE);

Query expansion mode

sql

-- Хайлтын үгтэй холбоотой нэмэлт үг автоматаар нэмнэ
SELECT title
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL' WITH QUERY EXPANSION);

Query expansion нь эхлээд хайж, үр дүнгийн нийтлэг үгийг олж, тэр үгийг нэмж дахин хайна. Хайлтын хамрах хүрээг өргөтгөдөг боловч холбогдолгүй үр дүн нэмэгдэж болно.

FTS-ийн хязгаарлалт

Минимум үгийн урт: Анхдагчаар 3 тэмдэгтэөс богино үгийг index хийдэггүй:

sql

-- Минимум урт харах
SHOW VARIABLES LIKE 'ft_min_word_len';
-- Үр дүн: 3

-- 'DB' гэж хайхад үр дүнгүй болно (2 тэмдэгт)

ft_min_word_len тохируулгыг my.cnf-д өөрчилж болно — гэхдээ сервер дахин ачаалаад index дахин бүтээх шаардлагатай.

Stop words: 'the', 'is', 'a' зэрэг нийтлэг үгийг MySQL index хийдэггүй:

sql

-- Stop word жагсаалт
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

InnoDB шаардлага: FULLTEXT index нь InnoDB болон MyISAM хүснэгтэд ажилладаг.

JOIN дахь FTS: FULLTEXT index байгаа баганаар MATCH хийх хүснэгт FROM дотор шууд байх ёстой.

LIKE vs FULLTEXT харьцуулалт

| | LIKE '%текст%' | FULLTEXT | | ------------------ | -------------- | ------------------ | | Index ашиглах | ✗ | ✓ | | Хурд (том өгөгдөл) | Удаан | Хурдан | | Хамаарал тооцоолол | ✗ | ✓ | | Boolean оператор | ✗ | ✓ | | Тохиргоо | Тэр даруй | Index шаардлагатай | | Богино текст | Хангалттай | Хэт их |

Жижиг хүснэгт (10,000-аас доош мөр) эсвэл энгийн тохиолдолд LIKE хангалттай. Том контент хайлтад FULLTEXT index зайлшгүй.

Дараагийн хичээлд:

EXPLAIN ба query оновчлол — query яаж ажилладгийг шинжлэх, удаан query-г хурдасгах аргыг үзнэ.

← Өмнөх хичээлJSON өгөгдөл MySQL-д Дараах хичээл →EXPLAIN ба query оновчлол