MySQL 實(shí)現(xiàn)一個(gè)簡(jiǎn)單版搜索引擎泻拦，真是絕了毙芜！
前言
只有Innodb和myisam存儲(chǔ)引擎能用全文索引（innodb支持全文索引是從mysql5.6開(kāi)始的）
char、varchar争拐、text類(lèi)型字段能創(chuàng)建全文索引（fulltext index type）
全文索引的基于關(guān)鍵詞的腋粥，如何區(qū)分不同的關(guān)鍵詞了，就要用到分詞（stopword）
英文單詞用空格架曹，逗號(hào)進(jìn)行分詞隘冲；中文分詞不方便（一個(gè)句子不知道怎樣區(qū)分不同的關(guān)鍵詞）
內(nèi)置分詞解析器ngram支持中文，日文绑雄，韓文（將句子分成固定數(shù)字的短語(yǔ)）
當(dāng)對(duì)表寫(xiě)入大量數(shù)據(jù)時(shí)展辞，寫(xiě)入數(shù)據(jù)后再創(chuàng)建全文索引的速度更快（減少了維護(hù)索引的開(kāi)銷(xiāo)）
全文索引的原理的倒排索引（一種數(shù)據(jù)結(jié)構(gòu)），一般利用關(guān)聯(lián)數(shù)組万牺，在輔助表中存儲(chǔ)單詞與文檔中所在位置的映射
使用
用MATCH() ... AGAINST 方式來(lái)進(jìn)行搜索match()表示搜索的是那個(gè)列罗珍，against表示要搜索的是那個(gè)字符串
查看默認(rèn)的分詞（以這些詞來(lái)區(qū)分不同的關(guān)鍵詞）；也可以自定義分詞脚粟，以這些詞來(lái)區(qū)分不同的關(guān)鍵詞
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;
如
+-------+
| value |
+-------+
| a ? ? |
| about |
| an ?? |
| are ? |
| as ?? |
| at ?? |
| be ?? |
| by ?? |
| com ? |
| de ?? |
| en ?? |
| for ? |
| from? |
三種類(lèi)型的全文搜索方式
natural language search（自然語(yǔ)言搜索）通過(guò)MATCH AGAINST 傳遞某個(gè)特定的字符串來(lái)進(jìn)行檢覆旱，默認(rèn)方式
boolean search（布爾搜索）為檢索的字符串增加操作符，如“+”表示必須包含珊楼，"-"不包含通殃，"*" 表示通配符度液，即使傳遞的字符串較小或出現(xiàn)在停詞中厕宗，也不會(huì)被過(guò)濾掉
query expansion search（查詢(xún)擴(kuò)展搜索）搜索字符串用于執(zhí)行自然語(yǔ)言搜索，然后堕担，搜索返回的最相關(guān)行的單詞被添加到搜索字符串已慢，并且再次進(jìn)行搜索，查詢(xún)將返回來(lái)自第二個(gè)搜索的行
相關(guān)參數(shù)
配置相關(guān)參數(shù)innodb_ft_min_token_size默認(rèn)3霹购，表示最小3個(gè)字符作為一個(gè)關(guān)鍵詞佑惠，增大該值可減少全文索引的大小
innodb_ft_max_token_size默認(rèn)84，表示最大84個(gè)字符作為一個(gè)關(guān)鍵詞齐疙，限制該值可減少全文索引的大小
ngram_token_size默認(rèn)2膜楷，表示2個(gè)字符作為內(nèi)置分詞解析器的一個(gè)關(guān)鍵詞，如對(duì)“abcd”建立全文索引贞奋，關(guān)鍵詞為'ab'赌厅，'bc'，'cd'轿塔，當(dāng)使用ngram分詞解析器時(shí)特愿，innodb_ft_min_token_size和innodb_ft_max_token_size 無(wú)效
注意：這三個(gè)參數(shù)均不可動(dòng)態(tài)修改仲墨，修改了這些參數(shù)，需重啟MySQL服務(wù)揍障，并重新建立全文索引目养。
這里有一份《 完整的 MySQL 開(kāi)發(fā)規(guī)范》進(jìn)大廠必看，推薦看下毒嫡。關(guān)注公眾號(hào)Java技術(shù)棸┮希回復(fù)mysql可以獲取更多教程。
測(cè)試innodb引擎使用全文索引
準(zhǔn)備
1审胚、目標(biāo)
查詢(xún)文章中是否含有某個(gè)關(guān)鍵詞匈勋；一系列文章出現(xiàn)某個(gè)關(guān)鍵詞的次數(shù)
查詢(xún)文章的標(biāo)題是否含有某個(gè)關(guān)鍵詞
2、設(shè)置以下參數(shù)減少磁盤(pán)IO壓力
SET GLOBAL sync_binlog=100;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
3膳叨、導(dǎo)入1kw 數(shù)據(jù)進(jìn)行測(cè)試全文索引該數(shù)據(jù)來(lái)源網(wǎng)上搜索提取碼：60l7
4洽洁、某個(gè)文章表 的結(jié)構(gòu)
CREATE TABLE `article` (
? `id` bigint(10) NOT NULL,
? `url` varchar(1024) CHARACTER SET latin1 NOT NULL DEFAULT '',
? `title` varchar(256) NOT NULL DEFAULT '',
? `source` varchar(32) DEFAULT '' COMMENT '真實(shí)來(lái)源',
? `keywords` varchar(32) DEFAULT NULL,
? `publish_time` timestamp NULL DEFAULT NULL,
? PRIMARY KEY (`id`),
? KEY `title_idx` (`title`)
) ENGINE=InnoDB
使用myloader 多線程導(dǎo)入測(cè)試數(shù)據(jù)，先把測(cè)試數(shù)據(jù)進(jìn)行解壓
tar -zxf mydumper_dump_article.tar.gz
time myloader -u $user -p $passwd -S $socket -t 32 -d /datas/dump_article -v 3
5菲嘴、導(dǎo)入數(shù)據(jù)后總數(shù)據(jù)量和數(shù)據(jù)文件饿自、索引文件大小
SELECT COUNT(*) FROM `article`;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (7.85 sec)
?
SELECT ? ? table_name, ? CONCAT(FORMAT(SUM(data_length) / 1024 / 1024,2),'M') AS dbdata_size, ? CONCAT(FORMAT(SUM(index_length) / 1024 / 1024,2),'M') AS dbindex_size, ? CONCAT(FORMAT(SUM(data_length + index_length) / 1024 / 1024 / 1024,2),'G') AS `db_size(G)`, ? AVG_ROW_LENGTH,table_rows,update_time FROM ? information_schema.tables WHERE table_schema = DATABASE() and table_name='article';
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| table_name | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | table_rows | update_time ? ? ? ? |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| article ?? | 3,710.00M ? | 1,003.00M ?? | 4.60G ? ?? | ? ? ? ? ?? 414 | ?? 9388739 | 2019-07-05 15:31:37 |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
使用默認(rèn)方式創(chuàng)建全文索引
1、該表已有關(guān)鍵詞字段（對(duì)文章內(nèi)容的簡(jiǎn)述）龄坪，并以“,”作為分詞符
select keywords from article limit 10;
+-------------------------------------------------+
| keywords ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
+-------------------------------------------------+
| NULL ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| NULL ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| ,婚姻,愛(ài)情 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| 發(fā)型,偏分,化妝,時(shí)尚 ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| 小A, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| ,服裝搭配,女性,時(shí)尚 ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| 漂亮,女性 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| 情人節(jié),東莞,女性 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| 皮膚,護(hù)膚,護(hù)膚,食品營(yíng)養(yǎng),美容,養(yǎng)生 ? ? ? ? ? ? ? |
| 三里屯,北京,時(shí)尚 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
+-------------------------------------------------+
2昭雌、不建全文索引時(shí)搜索某個(gè)關(guān)鍵詞需要進(jìn)行全表掃描
select count(*) from article where keywords like '%時(shí)尚%';
+----------+
| count(*) |
+----------+
| ? ?? 163 |
+----------+
1 row in set (7.56 sec)
3、對(duì)關(guān)鍵詞字段創(chuàng)建全文索引（以 , 作為分詞）
my.cnf配置文件中設(shè)置innodb_ft_min_token_size健田，并重啟MySQL服務(wù)（最小兩個(gè)字符作為一個(gè)關(guān)鍵詞烛卧，默認(rèn)三個(gè)字符作為一個(gè)關(guān)鍵詞）
[mysqld]
innodb_ft_min_token_size=2
3.1 設(shè)置自定義stopwords（即分詞）
USE mysql;
CREATE TABLE my_stopwords(VALUE VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopwords(VALUE) VALUE (',');
SET GLOBAL innodb_ft_server_stopword_table = 'mysql/my_stopwords';
SHOW GLOBAL? VARIABLES WHERE Variable_name IN('innodb_ft_min_token_size','innodb_ft_server_stopword_table');
+---------------------------------+--------------------+
| Variable_name ? ? ? ? ? ? ? ? ? | Value ? ? ? ? ? ?? |
+---------------------------------+--------------------+
| innodb_ft_min_token_size ? ? ?? | 2 ? ? ? ? ? ? ? ?? |
| innodb_ft_server_stopword_table | mysql/my_stopwords |
+---------------------------------+--------------------+
3.2 創(chuàng)建全文索引
alter table article add fulltext index idx_full_keyword(keywords);
* [ ] Query OK, 0 rows affected, 1 warning (1 min 27.92 sec)
* [ ] Records: 0? Duplicates: 0? Warnings: 1
3.3 剩余磁盤(pán)空間需足夠，原表4.6G妓局，剩余5.7G磁盤(pán)总放，添加全文索引也會(huì)失敗
df -h
Filesystem ? ? ? ? ?? Size? Used Avail Use% Mounted on
/dev/vda1 ? ? ? ? ? ? 7.8G? 6.3G? 1.2G? 85% /
tmpfs ? ? ? ? ? ? ? ? 1.9G ? ? 0? 1.9G ? 0% /dev/shm
/dev/mapper/vg_opt-lvol0
? ? ? ? ? ? ? ? ? ? ? 19G ? 12G? 5.7G? 68% /datas
?
會(huì)創(chuàng)建原表大小的臨時(shí)文件
8.6K Jul? 5 16:19 #sql-5250_3533.frm
4.4G Jul? 5 16:20 #sql-ib117-1768830977.ibd
?
?
alter table article add fulltext index idx_full_keyword(keywords);
ERROR 1114 (HY000): The table 'article' is full
3.4 利用創(chuàng)建的全文索引進(jìn)行查詢(xún)某個(gè)關(guān)鍵詞出現(xiàn)的次數(shù)查詢(xún)響應(yīng)時(shí)間有了很大的提升，只需0.05s好爬；使用where keywords like '%時(shí)尚%' 需要7.56s
select count(*) from article where match(keywords) against('%時(shí)尚%');
+----------+
| count(*) |
+----------+
| ? ?? 163 |
+----------+
1 row in set (0.05 sec)
3.5 如需同時(shí)完全匹配多個(gè)關(guān)鍵詞局雄，用布爾全文搜索
表示完全匹配 "三里屯,北京" 的記錄數(shù)：
select count(*) from article where match(keywords)? against('+三里屯,北京' in boolean mode);
+----------+
| count(*) |
+----------+
| ? ? ?? 1 |
+----------+
1 row in set (0.06 sec)
?
表示匹配“三里屯” 或者 “北京”的記錄數(shù)
select count(*) from article where match(keywords)? against('三里屯,北京');
+----------+
| count(*) |
+----------+
| ? ? ?? 8 |
+----------+
1 row in set (0.06 sec)
3.6 創(chuàng)建全文索引后，會(huì)創(chuàng)建一些其它文件
96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_1.ibd
96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_2.ibd
96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_3.ibd
96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_4.ibd
128K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_5.ibd
256K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_6.ibd
96K Jul 5 16:29 FTS_00000000000000a7_BEING_DELETED_CACHE.ibd
96K Jul 5 16:29 FTS_00000000000000a7_BEING_DELETED.ibd
96K Jul 5 16:30 FTS_00000000000000a7_CONFIG.ibd
96K Jul 5 16:29 FTS_00000000000000a7_DELETED_CACHE.ibd
96K Jul 5 16:29 FTS_00000000000000a7_DELETED.ibd
前6個(gè)表示倒排索引（輔助索引表）
第7存炮，8個(gè)表示包含已刪除文檔的文檔ID（DOC_ID）炬搭，其數(shù)據(jù)當(dāng)前正在從全文索引中刪除
第9個(gè)表示FULLTEXT索引內(nèi)部狀態(tài)的信息
第10，11個(gè)表示包含已刪除但尚未從全文索引中刪除其數(shù)據(jù)的文檔
使用ngram分詞解析器創(chuàng)建全文索引
1穆桂、對(duì)title字段建立全文索引（該字段沒(méi)有固定的stopwords 分詞宫盔，使用ngram分詞解析器）需先在my.cnf 配置文件中設(shè)置ngram_token_size（默認(rèn)為2，2個(gè)字符作為ngram 的關(guān)鍵詞）享完，并重啟mysql服務(wù)灼芭。
這里使用默認(rèn)的 2
select title from article limit 10;
+------------------------------------------------------------------------------+
| title ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
+------------------------------------------------------------------------------+
| worth IT ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
|Launchpad 江南皮革廠小show ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
|Raw 幕后罕見(jiàn)一刻 “瘋子”被抬回后臺(tái) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
|Raw:公子大罵老爸你就是個(gè)綠茶? 公子以一打四 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
|四組30平米精裝小戶(hù)型，海量圖片驼侠，附戶(hù)型圖 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
|夜店女王性感煙熏貓眼妝 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
|大秀哥重摔“巨石”強(qiáng)森 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
|少女時(shí)代 崔秀英 服飾科普 林允兒 黃美英 金泰妍 鄭秀晶 ? ? ? ? ? ? ? ? ? ? ?? | ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
|德陽(yáng)戶(hù)外踏青姿鸿，花田自助燒烤 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
+------------------------------------------------------------------------------+
2谆吴、對(duì)title字段創(chuàng)建全文索引
alter table article add fulltext index ft_index_title(title) with parser ngram;
Query OK, 0 rows affected (3 min 29.22 sec)
Records: 0? Duplicates: 0? Warnings: 0
3、會(huì)創(chuàng)建倒排索引（title字段越長(zhǎng)長(zhǎng)苛预，創(chuàng)建的倒排索引越大）
112M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_1.ibd
28M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_2.ibd
20M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_3.ibd
140M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_4.ibd
128M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_5.ibd
668M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_6.ibd
4句狼、不建立全文索引搜索title的某個(gè)關(guān)鍵詞
select count(*) from article where title like '%戶(hù)外%';
+----------+
| count(*) |
+----------+
| ?? 22058 |
+----------+
1 row in set (8.60 sec)
?
select count(*) from article where title like '%后臺(tái)%';
+----------+
| count(*) |
+----------+
| ? ? 1142 |
+----------+
5、使用全文索引搜索某個(gè)關(guān)鍵詞響應(yīng)時(shí)間有很大的提升
select count(*) from article where match(title)? against('戶(hù)外');
+----------+
| count(*) |
+----------+
| ?? 22058 |
+----------+
1 row in set (0.07 sec)
?
select count(*) from article where title like '%后臺(tái)%';
+----------+
| count(*) |
+----------+
| ? ? 1142 |
+----------+
1 row in set (8.31 sec)
6热某、注意當(dāng)搜索的關(guān)鍵詞字符數(shù)大于2 （ngram_token_size定義大心骞健）會(huì)出現(xiàn)不一致問(wèn)題
普通搜索，實(shí)際中出現(xiàn)該關(guān)鍵詞的記錄數(shù)為6
select count(*) from article where title like '%公子大%';
+----------+
| count(*) |
+----------+
| ? ? ?? 6 |
+----------+
1 row in set (8.40 sec)
?
全文搜索昔馋，出現(xiàn)關(guān)鍵字的記錄數(shù)為9443
select count(*) from article where match(title)? against('公子大');
+----------+
| count(*) |
+----------+
| ? ? 9443 |
+----------+
1 row in set (0.06 sec)
?
實(shí)際出現(xiàn)該關(guān)鍵字的記錄數(shù)為1
select count(*) from article where title like '%花田自助%';
+----------+
| count(*) |
+----------+
| ? ? ?? 1 |
+----------+
1 row in set (8.33 sec)
?
全文搜索出現(xiàn)該關(guān)鍵詞的記錄數(shù)為3202
select count(*) from article where match(title)? against('花田自助');
+----------+
| count(*) |
+----------+
| ? ? 3202 |
+----------+
1 row in set (0.06 sec)
結(jié)論
當(dāng)mysql 某字段中有固定的stopword 分詞（英文的空格符筹吐，中文的“,”"-"等），對(duì)該字段建立全文索引秘遏，能快速搜索出現(xiàn)某個(gè)關(guān)鍵詞的相關(guān)記錄信息丘薛，實(shí)現(xiàn)簡(jiǎn)單搜索引擎的效果**
當(dāng)mysql 某字段沒(méi)有固定的stopword 分詞，使用內(nèi)置解析器ngram 可將字段值分成固定數(shù)量（ngram_token_size定義大邪钗！）的關(guān)鍵詞快速進(jìn)行搜索洋侨；當(dāng)搜索的關(guān)鍵詞的字符數(shù)量不等于ngram_token_size定義大小時(shí)，會(huì)出現(xiàn)與實(shí)際情況不一致的問(wèn)題
全文索引能快速搜索倦蚪，也存在維護(hù)索引的開(kāi)銷(xiāo)希坚；字段長(zhǎng)度越大，創(chuàng)建的全文索引也越大陵且，會(huì)影響DML語(yǔ)句的吞吐量裁僧，可用專(zhuān)門(mén)的全文搜索引擎ES來(lái)做這件事
