前言只有Innodb和myisam存储引擎可以使用全文索引（innodb从mysql5.6开始支持全文索引）char、varchar、text类型的字段可以创建全文索引（fulltextindextype）full-text基于关键词的索引，如何区分不同的关键词，需要用到分词（stopword）英文单词用空格和逗号来分割；中文分词不方便（不知道如何区分句子中不同的关键词）内置分词解析器ngram支持中文、日文、韩文（句子分为固定个数的词组）数据量大时写入表，写入数据后创建全文索引速度更快（减少维护索引的开销）全文索引原理的倒排索引（一种数据结构），一般使用关联array，在副表中存储word与文档中位置的映射关系，使用MATCH()...AGAINST进行搜索match()表示搜索该列，against表示搜索搜索该字符串勾选默认分词（使用这些词来区分不同的关键词）；还可以自定义分词，使用这些词来区分不同的关键字SELECT*FROMinformation_schema.INNODB_FT_DEFAULT_STOPWORD；比如+-------+|value|+-------+|a||about||an||are||as||at||be||by||com||de||en||for||from|三种全文搜索方式自然语言搜索（naturallanguagesearch）将特定字符串通过MATCHAGAINST进行检查，默认方式布尔搜索（booleansearch）添加运算符检索到的字符串，例如“+”表示必须包含，“-”不包含，“*”表示通配符，即使传入的字符串很小或者出现在停用词中，也不会filteredoutqueryexpansionsearch（查询扩展搜索）搜索字符字符串用于执行自然语言搜索。然后，将搜索返回的最相关行的词添加到搜索字符串中，并再次执行搜索。查询将返回第二次搜索中的行。最少3个字符作为关键字，添加较大的值可以减小全文索引的大小。innodb_ft_max_token_size默认为84，表示最多可以使用84个字符作为关键字。限制此值可以减小全文索引的大小。ngram_token_size默认为2，表示使用2个字符作为内置分词解析器的关键字。例如，为“abcd”建立全文索引，关键字为'ab'、'bc'、'cd'，使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size无效。注意：这三个参数不能动态修改。修改完这些参数后，需要重启MySQL服务，重新建立全文索引。这是进入大型工厂时必看的《 完整的 MySQL 开发规范》的副本。推荐阅读。关注公众号Java技术栈，回复mysql获取更多教程。使用全文索引准备测试innodb引擎1.目标查询文章包含某个关键字；某个关键词在一系列文章中出现的次数查询文章标题是否包含某个关键词2.设置如下参数，降低磁盘IO压力SETGLOBALsync_binlog=100;SETGLOBALinnodb_flush_log_at_trx_commit=2;3.导入1kw数据测试全文索引。本资料来源网上搜索提取码：60l74。文章表的结构CREATETABLE`article`(`id`bigint(10)NOTNULL,`url`varchar(1024)CHARACTERSETlatin1NOTNULLDEFAULT'',`title`varchar(256)NOTNULDEFAULT'',`source`varchar(32)DEFAULT''COMMENT'truesource',`keywords`varchar(32)DEFAULTNULL,`publish_time`timestampNULLDEFAULTNULL,PRIMARYKEY(`id`),KEY`title_idx`(`title`))ENGINE=InnoDB使用myloader导入测试数据多线程，先解压测试数据tar-zxfmydumper_dump_article.tar.gztimemyloader-u$user-p$passwd-S$socket-t32-d/datas/dump_article-v35，数据导入后总数据量，数据文件大小，索引文件大小SELECTCOUNT(*)FROM`article`;+----------+|COUNT(*)|+--------+|10000000|+----------+1rowinset(7.85sec)SELECTtable_name,CONCAT(FORMAT(SUM(data_length)/1024/1024,2),'M')ASdbdata_size,CONCAT(FORMAT(SUM(index_length)/1024/1024,2),'M')ASdbindex_size,CONCAT(FORMAT(SUM(data_length+index_length)/1024/1024/1024,2),'G')AS`db_size(G)`,AVG_ROW_LENGTH,table_rows,update_timeFROMinformation_schema.tablesWHEREtable_schema=DATABASE()andtable_name='article';+------------+------------+------------+------------+----------------+------------+--------------------+|table_name|dbdata_size|dbindex_size|db_size(G)|AVG_ROW_LENGTH|table_rows|update_time|+------------+------------+--------------+------------+----------------+------------+------------------+|文章|3,710.00M|1,003.00M|4.60G|414|9388739|2019-07-0515:31:37|+-----------+------------+------------+------------+--------------+------------+-------------------+使用默认方式创建全文索引1.表中已有关键字字段（文章内容的简要说明），使用“,”作为分词符selectkeywordsfromarticlelimit10;+----------------------------------------------+|关键词|+----------------------------------------------+|NULL||NULL||,婚姻,爱情||发型,离别,妆容、时尚||小A、||、服装搭配、女性、时尚||美丽、女性||情人节、东莞、女性||皮肤、护肤、护肤、食品营养、美容、健康||北京三里屯时尚|+----------------------------------------------+2。不建立全文索引搜索关键词需要全表扫描selectcount(*)fromarticlewherekeywordslike'%fashion%';+---------+|count(*)|+----------+|163|+------------+1rowinset(7.56sec)3.为关键字字段创建全文索引（使用，作为分词）setinnodb_ft_min_token_sizeinthemy.cnf配置文件，并重启MySQL服务（最少两个字符作为关键字，默认三个字符作为关键字）[mysqld]innodb_ft_min_token_size=23.1设置自定义停用词（即分词）USEmysql;CREATETABLEmy_stopwords(VALUEVARCHAR(30))ENGINE=INNODB;INSERTINTTOmy_stopwords(VALUE)VALUE(',');SETGLOBALinnodb_ft_server_stopword_table='mysql/my_stopwords';SHOWGLOBALVARIABLESWHEREVariable_nameIN('innodb_ft_min_token_size','innodb_ft_server-stopword-table');+--------------------------+--------------------+|Variable_name|值|+--------------------------------+------------------+|innodb_ft_min_token_size|2||innodb_ft_server_stopword_table|mysql/my_stopwords|+------------------------------+-------------------+3.2创建全文索引altertablearticleaddfulltextindexidx_full_keyword(keywords);*[]QueryOK,0rowsaffected,1warning(1min27.92sec)*[]Records:0Duplicates:0Warnings:13.3剩余磁盘空间需要足够，原表4.6G，剩余5.7G磁盘，添加全文索引也会失败df-hFilesystemSizeUsedAvailUse%Mountedon/dev/vda17.8G6.3G1.2G85%/tmpfs1.9G01.9G0%/dev/shm/dev/mapper/vg_opt-lvol019G12G5.7G68%/datas会创建一个原始表大小为8.6KJul516:19#sql-的临时文件5250_3533.frm4.4GJul516:20#sql-ib117-1768830977.ibdaltertablearticleaddfulltextindexidx_full_keyword(keywords);ERROR1114(HY000):Thetable'article'isfull3.4使用创建的全文索引查询某个关键字出现的次数查询响应时间大幅提升，仅为0.05s；在像'%fashion%'这样的关键字需要7.56sselectcount(*)fromarticlewherematch(keywords)against('%fashion%');+----------+|count(*)|+----------+|163|+------------+1rowinset(0.05sec)3.5如果需要同时完全匹配多个关键词，用布尔全文搜索来表示完整匹配“北京三里屯”的记录条数：selectcount(*)fromarticlewherematch(keywords)against('+北京三里屯'inbooleanmode);+----------+|count(*)|+--------+|1|+----------+1rowinset(0.06sec)表示匹配“三里屯”或“北京”的记录数selectcount(*)fromarticlewherematch(keywords)against('北京三里屯');+----------+|count(*)|+---------+|8|+---------+1rowinset(0.06sec)3.6创建全文索引后，会创建一些其它文件96KJul516:30FTS_00000000000000a7_00000000000000c0_INDEX_1.ibd96KJul516:30FTS_00000000000000a7_00000000000000c0_INDEX_2.ibd96KJul516:30FTS_00000000000000a7_00000000000000c0_INDEX_3.ibd96KJul516:30FTS_00000000000000a7_00000000000000c0_INDEX_4.ibd128KJul516:30FTS_00000000000000a7_00000000000000c0_INDEX_5.ibd256KJul516:30FTS_00000000000000a7_00000000000000c0_INDEX_6.ibd96KJul516:29FTS_00000000000000a7_BEING_DELETED_CACHE.ibd96KJul516:29FTS_00000000000000a7_BEING_DELETED.ibd96KJul516:30FTS_00000000000000a7_CONFIG.ibd96KJul516:29FTS_00000000000000a7_DELETED_CACHE.ibd96KJul516:29FTS_00000000000000a7_DELETED.ibd前6个表示倒排索引（辅助索引表）第7，8个表示包含已删除文档的文档ID（DOC_ID），其数据当前正在从全文索引Deletethe9thinformationthatindicatestheinternalstateoftheFULLTEXTindex.The10thand11thindicatedocumentsthatcontaindeletedbuthavenotbeendeletedfromthefull-textindex.Createafull-textindexusingthengramwordsegmentationparser1.Createafull-textindexforthetitlefield(theThefielddoesnothaveafixedstopwordswordsegmentation,usethengramwordsegmentationparser)youneedtosetngram_token_sizeinthemy.cnfconfigurationfile(thedefaultis2,2charactersareusedasthekeywordofngram),andrestartthemysqlservice这里使用默认的2selecttitlefromarticlelimit10;+--------------------------------------------------------------------------+|title|+--------------------------------------------------------------------------+|worthIT||Launchpad江南制革小秀||Raw幕后难得的“疯狂”瞬间被带回后台||Raw：儿子骂老爸，你是绿茶小子，一打四||四组30平精装小户型，海量图片，带户型||夜店女王性感烟熏猫眼妆||大秀哥砸“巨石强森”||少女时代崔秀英古装科普林允儿黄美英金泰妍郑秀晶||德阳户外踏青，华天自助BBQ|+------------------------------------------------------------------------------+2，为创建全文索引thetitlefieldaltertablearticleaddfulltextindexft_index_title(title)withparserngram;QueryOK,0rowsaffected(3min29.22sec)Records:0Duplicates:0Warnings:03,createaninvertedindex(thelongerthetitlefield,thelargerthecreatedinvertedindex)112MJul521:46FTS_00000000000000a7_00000000000000cd_INDEX_1.ibd28MJul521:46FTS_00000000000000a7_00000000000000cd_INDEX_2.ibd20MJUL521：46FTS_000000000000A7_00000000000000000000CD_INDEX_3.IBD140MJUL521：46FTS_000000000000000000A7_0000000000000000000000000000000000000000000000000000000000000000000000000000000000来了.ibd668MJul521:46FTS_00000000000000a7_00000000000000cd_INDEX_6.ibd4。不建全文索引搜索标题中的某个关键词selectcount(*)fromarticlewheretitlelike'%outdoor%';+----------+|count(*)|+----------+|22058|+--------+1rowinset(8.60sec)selectcount(*)fromarticlewheretitlelike'%background%';+---------+|计数(*)|+----------+|1142|+----------+5。使用全文索引搜索关键词响应时间长boostselectcount(*)fromarticlewherematch(title)against('outdoor');+---------+|count(*)|+----------+|22058|+------------+1rowinset(0.07sec)selectcount(*)fromarticlewheretitlelike'%background%';+--------+|count(*)|+----------+|1142|+------------+1rowinset(8.31sec)6.注意当搜索到的关键字字符数大于2时（ngram_token_size定义大小），有会不一致搜索，这个关键词的实际记录数是6条selectcount(*)fromarticlewheretitlelike'%公子大%';+----------+|count(*)|+----------+|6|+------------+1rowinset(8.40sec)全文检索，带关键词的记录条数为9443selectcount(*)fromarticlewherematch(title)against('工字打');+------------+|count(*)|+----------+|9443|+----------+1rowinset(0.06sec)该关键字的实际记录数为1selectcount(*)fromarticlewheretitlelike'%华天自助%';+----------+|count(*)|+--------+|1|+--------+1rowinset(8.33sec)全文检索该关键词出现的记录数为3202selectcount(*)fromarticlewherematch(title)against('华天自助');+------------+|count(*)|+---------+|3202|+------------+1rowinset(0.06sec)结论当mysql字段有固定停用词分词时（英文空格字符、中文“、”、“-”等），对该字段建立全文索引，通过某个关键词快速搜索相关记录信息，达到简易搜索引擎的效果。**当一个mysql字段没有固定的停用词分词时，使用内置的解析器ngram将字段值分成固定数量的关键字（由ngram_token_size定义）进行快速搜索；当搜索到的关键字的字符数不等于ngram_token_size定义的大小时，会出现与实际情况不符的问题开销；字段长度越大，创建的全文索引越大，会影响DML语句的吞吐量，可以使用专用的全文搜索引擎ES来做
