mysql ft指的是什么

mysql ft指的是FullText，即全文索引；全文索引是为了解决需要基于相似度的查询，而不是精确数值比较；全文索引在大量的数据面前，能比like快N倍，速度不是一个数量级。

MySQL 全文索引 (FullText)

一、简介

基本概念

全文索引是为了解决需要基于相似度的查询，而不是精确数值比较。

虽然使用 like + % 也可以实现模糊匹配，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like 快 N 倍，速度不是一个数量级。

版本支持

1. MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引

2. MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引

3. MySQL 5.7.6 中，提供了支持中文、日文和韩文（CJK）的内置全文 ngram 解析器，以及用于日文的可安装 MeCab 全文解析器插件

4. 全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建

5. 对于大型数据集，span style="" quot="quot" dashed="""dashed""" yellow="""yellow"""> 将数据加载到没有全文索引的表中然后创建索引要比将数据加载到具有现有全文索引的表中快得多

6. RDS MySQL 5.6 虽然也支持中文全文检索，但存在BUG

限制与缺点

• 导致磁盘资源的大量占用。全文索引本身就是一个利用磁盘空间换取性能的方法。全文索引大的原因是，按照某种语言来进行分词

• 全文索引创建速度慢，而且对有全文索引的各种数据修改操作也慢

• 使用全文索引并不是对应用透明的。如果要想利用全文索引，必须修改查询语句。原有的查询语句是不可能利用全文索引的，需要改成全文索引规定的语法

• 不区分大小写

• 分区表不支持全文搜索

• 由多列组合而成的全文检索的索引必须使用相同的字符集与排序规则

• 全文索引可能存在精度问题，即全文索引找到的数据，可能和like到的不一致

• MATCH()函数中的列必须与FULLTEXT索引中定义的列完全一致，除非是在MyISAM表中使用IN BOOLEAN MODE模式的全文搜索（可在没有建立索引的列执行搜索，但速度很慢）

• 单列分别建立全文索引时，多列模糊查询时不生效

• 不同表的全文索引不能放在一起查询，可以两个语句中加上OR

二、操作全文索引

2.1 配置最小搜索长度

我们可以通过 SQL 命令查看当前配置的最小搜索长度（分词长度）：

SHOWVARIABLESLIKE'ft%';
    

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容：

[mysqld]
innodb_ft_min_token_size=1
#最短的索引字符串，默认值为4
ft_min_word_len=1

配置完后重启 MySQL 服务器，并修复或重建全文索引方可生效。
可使用下面的命令修复：

repairtabletestquick;
    

2.2 创建索引

• 建表时创建全文索引

CREATETABLEfulltext_test(
idint(11)NOTNULLAUTO_INCREMENT,
	contentTEXTNOTNULL,
	tagVARCHAR(255),
	PRIMARYKEY(id),
	FULLTEXTKEYcontent_tag_fulltext(content,tag)WITHPARSERngram
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
    

• 在已存在的表上创建全文索引

CREATEFULLTEXTINDEXcontent_fulltextONfulltext_test(content)withparserngram;
    

• 通过 SQL 语句 ALTER TABLE 创建全文索引

ALTERTABLEfulltext_testADDFULLTEXTINDEXcontent_fulltext(content)withparserngram;
    

2.3 删除索引

• 使用 DROP INDEX 删除全文索引

DROPINDEXcontent_fulltextONfulltext_test;
    

• 通过 SQL 语句 ALTER TABLE 删除全文索引

ALTERTABLEfulltext_testDROPINDEXcontent_fulltext;
    

三、检索数据

3.1 自然语言的全文检索

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索。

SELECT*FROM表名WHEREMatch(列名1,列名2)Against(检索内容1检索内容2);
    

检索内容不需要用逗号隔开！

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

3.2 布尔全文检索

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

• 空(也就是默认状况)，表示可选的，包含该词的顺序较高

• + 表示必须包含

• - 表示必须排除

• “> ” 表示出现该单词时增加相关性，查询的结果靠前

• “” 表示出现该单词时降低相关性，查询的结果靠后

• * 表示通配符，只能接在词后面

• ~ 允许出现该单词，但是出现时相关性为负，表示拥有该字会下降相关性，但不像「-」将之排除，只是排在较后面

• "" 双引号表示短语，表示要彻底相符，不可拆字效果，类同于 like '%keyword%'

• () 经过括号来使用字条件:

+aaa+(>
    bbbccc)aaa="aaa"sql="sql"select="select"from="from"test="test"where="where"match="match"against="against"in="in"boolean="boolean"mode="mode"select="select"from="from"tommy="tommy"where="where"match="match"against="against"in="in"boolean="boolean"mode="mode"select="select"from="from"tommy="tommy"where="where"match="match"against="against">
    李秀琴练习册不是人>
    是个鬼'inbooleanmode);
    

四、测试结果

测试环境：本机4核16G Windows10，MySQL 8.0
测试数据量：salebilldetail 表 1276万行，salebill 表 269 万行, customer 表 30 万行, goods 表 75 万行。

争对测试用的SQL语句，增加了以下全文索引：

CREATEFULLTEXTINDEXbillno_fulltextONsalebill(billno)WITHPARSERngram;
    
CREATEFULLTEXTINDEXremarks_fulltextONsalebill(remarks)WITHPARSERngram;
    
CREATEFULLTEXTINDEXremarks_fulltextONsalebilldetail(remarks)WITHPARSERngram;
    
CREATEFULLTEXTINDEXgoodsremarks_fulltextONsalebilldetail(goodsremarks)WITHPARSERngram;
    
CREATEFULLTEXTINDEXremarks_goodsremarks_fulltextONsalebilldetail(remarks,goodsremarks)WITHPARSERngram;
    
CREATEFULLTEXTINDEXcustname_fulltextONcustomer(custname)WITHPARSERngram;
    
CREATEFULLTEXTINDEXgoodsname_fulltextONgoods(goodsname)WITHPARSERngram;
    
CREATEFULLTEXTINDEXgoodscode_fulltextONgoods(goodscode)WITHPARSERngram;
    

测试结果，总的来说很魔幻。
为什么魔幻，看下面几个语句：

test_1

--测试1，原始like查询方式，用时0.765s
select1fromsalebilldetaildwhered.tid=260434and((d.remarkslikeconcat('%','葡萄','%'))or(d.goodsremarkslikeconcat('%','葡萄','%')));
    

test_2

--测试2，使用全文索引remarks_fulltext、goodsremarks_fulltext,用时0.834s
select1fromsalebilldetaildwhered.tid=260434and((match(d.remarks)Against(concat('"','葡萄','"')inbooleanmode))or(match(d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode)));
    

test_3

--测试3，使用全文索引remarks_goodsremarks_fulltext,用时0.242s
select1fromsalebilldetaildwhered.tid=260434and((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode)));
    

test_4

--测试4，原始like查询方式，不过滤tid，用时22.654s
selecttfromsalebilldetaildwhere((d.remarkslikeconcat('%','葡萄','%'))or(d.goodsremarkslikeconcat('%','葡萄','%')));
    

test_5

--测试5，使用全文索引remarks_fulltext、goodsremarks_fulltext,不过滤tid，用时24.855s
select1fromsalebilldetaildwhere((match(d.remarks)Against(concat('"','葡萄','"')inbooleanmode))or(match(d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode)));
    

test_6

--测试6，使用全文索引remarks_goodsremarks_fulltext,不过滤tid，用时0.213s
select1fromsalebilldetaildwhere((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode)));
    

test_7

--测试7，使用全文索引remarks_goodsremarks_fulltext,用时0.22s
selectcount(1)fromsalebilldetaildwhered.tid=260434and((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode)));
    

test_8

--测试8，使用全文索引remarks_goodsremarks_fulltext,不过滤tid，用时0.007s
selectcount(1)fromsalebilldetaildwhere((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode)));
    

从上面的测试语句可以看出，数据量越多，查询越简单，全文索引的效果越好。

再来看看我们的业务测试SQL:

test_9

--测试9
select
i.billid
,if(0,0,i.qty)asqty
,if(0,0,i.goodstotal)astotal
,if(0,0,i.chktotal)asselfchktotal
,if(0,0,i.distotal)asdistotal
,if(0,0,i.otherpay)asfeetotal
,if(0,0,ifnull(d.costtotal,0))ascosttotal
,if(0,0,ifnull(d.maoli,0))asmaoli
,i.billno
,from_unixtime(i.billdate,'%Y-%m-%d')asbilldate/*单据日期*/
,from_unixtime(i.createdate,'%Y-%m-%d%H:%i:%s')ascreatedate/*制单日期*/
,if(i.sdate=0,'',from_unixtime(i.sdate,'%Y-%m-%d%H:%i:%s'))assdate/*过账日期*/
,from_unixtime(i.udate,'%Y-%m-%d%H:%i:%s')asudate/*最后修改时间*/
,i.custid,c.custname
,i.storeid,k.storename
,i.empid,e.empname
,i.userid,u.username
,i.remarks/*单据备注*/
,i.effect,i.settle,i.redold,i.rednew/*单据状态*/
,i.printtimes/*打印次数*/
,(casewheni.rednew=1then1wheni.redold=1then2wheni.settle=1then3wheni.effect=1then4else9end)asstate/*单据状态*/
,(casewheni.rednew=1then'红冲单'wheni.redold=1then'已红冲'wheni.settle=1then'已结算'wheni.effect=1then'已过账'else'草稿'end)asstatetext
,''assusername/*操作人*/
,''asaccname/*科目*/
fromsalebilli
leftjoincoursecentersaledond.tid=i.tidandd.billid=i.billid
leftjoincustomerconc.tid=i.tidandc.custid=i.custid
leftjoinstorekonk.tid=i.tidandk.storeid=i.storeid
leftjoinemployeeeone.tid=i.tidande.empid=i.empid
leftjoinuseruonu.tid=i.tidandu.userid=i.userid
wherei.tid=260434and(i.billtype=5ori.effect=1)
and('_billdate_f_'!='')
and('_billdate_t_'!='')
and('_sdate_f_'!='')
and('_sdate_t_'!='')
and('_udate_f_'!='')
and('_udate_t_'!='')
and('_cdate_f_'!='')
and('_cdate_t_'!='')
and('_billid_'!='')/*单据id*/
and('_custid_'!='')/*客户ID*/
and('_storeid_'!='')/*店仓ID*/
and('_empid_'!='')/*业务员ID*/
and('_custstop_'!='')/*客户是否停用*/
and(
(i.billnolikeconcat('%','葡萄','%'))
or(i.remarkslikeconcat('%','葡萄','%'))
orexists(select1fromsalebilldetaildwhered.tid=260434andd.billid=i.billidand((d.remarkslikeconcat('%','葡萄','%'))or(d.goodsremarkslikeconcat('%','葡萄','%'))))
orexists(select1fromcustomercwherec.tid=260434andc.custid=i.custidand(c.custnamelikeconcat('%','葡萄','%')))
orexists(select1fromgoodsgjoinsalebilldetaildond.tid=g.tidandd.goodsid=g.goodsidwhered.tid=260434andd.billid=i.billidand((g.goodsnamelikeconcat('%','葡萄','%'))or(g.goodscodelikeconcat('%','葡萄','%'))))
)
andi.rednew=0/*单据列表不含红冲单*/
andi.billidnotin(selectbillidfromcoursecenter_deltwheret.tid=260434)
and((i.settle=1andi.effect=1andi.redold=0andi.rednew=0))/*已结算*/
orderbyudatedesc,billnodesc
limit0,100;
    

执行时间约 1.6 秒，使用的是 like 方式。

改成使用全文索引方式：

test_10

--测试10
select
i.billid
,if(0,0,i.qty)asqty
,if(0,0,i.goodstotal)astotal
,if(0,0,i.chktotal)asselfchktotal
,if(0,0,i.distotal)asdistotal
,if(0,0,i.otherpay)asfeetotal
,if(0,0,ifnull(d.costtotal,0))ascosttotal
,if(0,0,ifnull(d.maoli,0))asmaoli
,i.billno
,from_unixtime(i.billdate,'%Y-%m-%d')asbilldate/*单据日期*/
,from_unixtime(i.createdate,'%Y-%m-%d%H:%i:%s')ascreatedate/*制单日期*/
,if(i.sdate=0,'',from_unixtime(i.sdate,'%Y-%m-%d%H:%i:%s'))assdate/*过账日期*/
,from_unixtime(i.udate,'%Y-%m-%d%H:%i:%s')asudate/*最后修改时间*/
,i.custid,c.custname
,i.storeid,k.storename
,i.empid,e.empname
,i.userid,u.username
,i.remarks/*单据备注*/
,i.effect,i.settle,i.redold,i.rednew/*单据状态*/
,i.printtimes/*打印次数*/
,(casewheni.rednew=1then1wheni.redold=1then2wheni.settle=1then3wheni.effect=1then4else9end)asstate/*单据状态*/
,(casewheni.rednew=1then'红冲单'wheni.redold=1then'已红冲'wheni.settle=1then'已结算'wheni.effect=1then'已过账'else'草稿'end)asstatetext
,''assusername/*操作人*/
,''asaccname/*科目*/
fromsalebilli
leftjoincoursecentersaledond.tid=i.tidandd.billid=i.billid
leftjoincustomerconc.tid=i.tidandc.custid=i.custid
leftjoinstorekonk.tid=i.tidandk.storeid=i.storeid
leftjoinemployeeeone.tid=i.tidande.empid=i.empid
leftjoinuseruonu.tid=i.tidandu.userid=i.userid
wherei.tid=260434and(i.billtype=5ori.effect=1)
and('_billdate_f_'!='')
and('_billdate_t_'!='')
and('_sdate_f_'!='')
and('_sdate_t_'!='')
and('_udate_f_'!='')
and('_udate_t_'!='')
and('_cdate_f_'!='')
and('_cdate_t_'!='')
and('_billid_'!='')/*单据id*/
and('_custid_'!='')/*客户ID*/
and('_storeid_'!='')/*店仓ID*/
and('_empid_'!='')/*业务员ID*/
and('_custstop_'!='')/*客户是否停用*/
and(
(match(i.billno)against(concat('"','葡萄','"')inbooleanmode))
or(match(i.remarks)against(concat('"','葡萄','"')inbooleanmode))
orexists(select1fromsalebilldetaildwhered.tid=260434andd.billid=i.billidand((match(d.remarks)Against(concat('"','葡萄','"')inbooleanmode))or(match(d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode))))
orexists(select1fromcustomercwherec.tid=260434andc.custid=i.custidand(match(c.custname)Against(concat('"','葡萄','"')inbooleanmode)))
orexists(select1fromgoodsgjoinsalebilldetaildond.tid=g.tidandd.goodsid=g.goodsidwhered.tid=260434andd.billid=i.billid
and((match(g.goodsname)Against(concat('"','葡萄','"')inbooleanmode))
or(match(g.goodscode)Against(concat('"','葡萄','"')inbooleanmode))))
)
andi.rednew=0/*单据列表不含红冲单*/
andi.billidnotin(selectbillidfromcoursecenter_deltwheret.tid=260434)
and((i.settle=1andi.effect=1andi.redold=0andi.rednew=0))/*已结算*/
orderbyudatedesc,billnodesc
limit0,100;
    

执行时间约 1.6 秒，与使用的是 like 方式差不多。

最魔幻的地方来了，如果将上面的SQL语句中（salebilldetail表使用全文索引 remarks_fulltext、goodsremarks_fulltext的地方）

exists(select1fromsalebilldetaildwhered.tid=260434andd.billid=i.billidand((match(d.remarks)Against(concat('"','葡萄','"')inbooleanmode))or(match(d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode))))

test_11

改成使用全文索引 remarks_goodsremarks_fulltext

--测试11
exists(select1fromsalebilldetaildwhered.tid=260434andd.billid=i.billidand((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode))))

执行时间无限长（跑了半天没成功）？
经分析，在 where 子句中，一个条件子句中包含一个以上 match 时会出现这样的情况。即：

--and中只有一个全文检索时正常，用时0.2秒
selectxxxfromxxx
...
and(
	exists(select1fromsalebilldetaildwhered.tid=260434andd.billid=i.billidand((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode))))
)
...

--下面这样就异常了，会慢成百上千倍，用时160秒，如果有更多的match，会更夸张的慢下去
selectxxxfromxxx
...
and(
	exists(select1fromsalebilldetaildwhered.tid=260434andd.billid=i.billidand((match(d.remarks,d.goodsremarks)Against(concat('"','葡萄','"')inbooleanmode))))
	ormatch(i.billno)against(concat('"','葡萄','"')inbooleanmode)
)
...

测试结果汇总：

五、MySQL 版本升级

因线上系统目前是 RDS MySQL 5.6，故简单描述升级相关问题。

• Group By: 在 MySQL 5.7 之后，默认使用增加了限制，一些在 MySQL 5.6 可执行的Group By语句，在 5.7 之后会报错，可以更改新版本 MySQL 的 sqlModel

  
  

  -- 查询 sql_mode
  select @@SESSION.sql_mode;
      
  -- 设置
  SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
      
  -- 或 设置 （修改于当前会 话，关闭当前会话后失效）
  SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
      
  -- 刷新
  flush PRIVILEGES;
      
  

  
  

• ONLY_FULL_GROUP_BY: 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

• NO_AUTO_VALUE_ON_ZERO: 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

• STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制

• NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零

• NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告

• ERROR_FOR_DIVISION_BY_ZERO：在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySql返回NULL

• NO_AUTO_CREATE_USER: 禁止GRANT创建密码为空的用户

• NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

• PIPES_AS_CONCAT：将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似

• ANSI_QUOTES：启用后，不能用双引号来引用字符串，因为它被解释为识别符
  
  

• 方式2：在配置文件中添加 sql_mode = '对应需要的模式'

• sql_mode 模式说明：

• 方式1：重启 MySQL 后失效

• MySQL8.0 修改了账号密码加密策略 (默认的认证插件由mysql_native_password更改为caching_sha2_password），导致一些可视化软件无法连接 mysql8.0 版本的数据库。如果需要，可以修改默认的策略或者账号密码的认证策略

  
  

  [mysqld]
  default_authentication_plugin = mysql_native_password
  

  --修改加密规则
  ALTERUSER'root'@'localhost'IDENTIFIEDBY'password'PASSWORDEXPIRENEVER;
      
  --更新用户密码
  ALTERUSER'账号'@'%'IDENTIFIEDWITHmysql_native_passwordBY'密码';
      
  --刷新权限
  FLUSHPRIVILEGES;
      
  

  
  

• 方式2：执行语句修改某账号密码验证策略

• 方式1：配置文件中添加, 让mysql使用原密码策略 (需重启mysql服务)

• MySQL8.0 授权用户账号语法变更，创建用户的操作已经不支持grant的同时创建用户方式，需要先创建用户再进行授权。

  
  

  -- 原来的流程：
  mysql>
       grant all on *.* to 'admin'@'%' identified by 'admin';
      
  -- 新的正确流程：
  mysql>
       create user 'admin'@'%' identified by 'admin';
      
  mysql>
       grant all on *.* to 'admin'@'%' ;
      
  mysql>
       flush privileges;
      
  

  
  

• 数据库连接区别

  
  

  jdbc:mysql://{
  ip}
  :{
  port}
  /{
  db}
      &
      #63;
      characterEncoding=utf8&
      useSSL=false&
      serverTimezone=UTC
  // useSSL 如果不配置false 项目可以正常启动但是会提示ssl问题
  // serverTimezone=UTC 必须配置【时区设置成自己对应的时区】否则项目会报错
  

  showvariableslike'%time_zone%'；
  setglobaltime_zone='+8:00';
      
  

  
  

• 如果时区问题还不能解决：

• JDBC 连接串修改如下（首先需要驱动使用8.0对应连接的驱动）：

• MySQL 5.7 原生支持JSON类型，并引入了众多JSON函数

• MySQL 8.0 JSON字段的部分更新（JSON Partial Updates）

• MySQL 8.0 默认字符集由latin1修改为utf8mb4

• MySQL 8.0 正则表达式的增强，新增了4个相关函数，REGEXP_INSTR()，REGEXP_LIKE()，REGEXP_REPLACE()，REGEXP_SUBSTR()

• MySQL 8.0 GROUP BY语句不再隐式排序 （忽略在Group By中的排序命令，如 desc, asc）

读到这里，这篇“mysql ft指的是什么”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注行业资讯频道。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！