mysql ft是什么

本文小編為大家詳細介紹“mysql ft是什么”，內容詳細，步驟清晰，細節處理妥當，希望這篇“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 命令查看當前配置的最小搜索長度（分詞長度）：

SHOW VARIABLES LIKE 'ft%';

全文索引的相關參數都無法進行動態修改，必須通過修改 MySQL 的配置文件來完成。修改最小搜索長度的值為 1，首先打開 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下內容：

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

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

repair table test quick;

2.2 創建索引

• 建表時創建全文索引

CREATE TABLE fulltext_test (
  id int(11) NOT NULL AUTO_INCREMENT,
	content TEXT NOT NULL,
	tag VARCHAR(255),
	PRIMARY KEY (id),
	FULLTEXT KEY content_tag_fulltext(content, tag) WITH PARSER ngram
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE FULLTEXT INDEX content_fulltext ON fulltext_test(content) with parser ngram;

• 通過 SQL 語句 ALTER TABLE 創建全文索引

ALTER TABLE fulltext_test ADD FULLTEXT INDEX content_fulltext(content) with parser ngram;

2.3 刪除索引

• 使用 DROP INDEX 刪除全文索引

DROP INDEX content_fulltext ON fulltext_test;

• 通過 SQL 語句 ALTER TABLE 刪除全文索引

ALTER TABLE fulltext_test DROP INDEX content_fulltext;

三、檢索數據

3.1 自然語言的全文檢索

默認情況下，或者使用 in natural language mode 修飾符時，match() 函數對文本集合執行自然語言搜索。

SELECT * FROM 表名 WHERE Match(列名1,列名2) Against (檢索內容1 檢索內容2);

檢索內容不需要用逗號隔開！

自然語言搜索引擎將計算每一個文檔對象和查詢的相關度。這里，相關度是基于匹配的關鍵詞的個數，以及關鍵詞在文檔中出現的次數。在整個索引中出現次數越少的詞語，匹配時的相關度就越高。相反，非常常見的單詞將不會被搜索，如果一個詞語的在超過 50% 的記錄中都出現了，那么自然語言的搜索將不會搜索這類詞語。

3.2 布爾全文檢索

在布爾搜索中，我們可以在查詢中自定義某個被搜索的詞語的相關性，當編寫一個布爾搜索查詢時，可以通過一些前綴修飾符來定制搜索。

• 空(也就是默認狀況)，表示可選的，包含該詞的順序較高

• + 表示必須包含

• - 表示必須排除

• “>” 表示出現該單詞時增加相關性，查詢的結果靠前

• “<” 表示出現該單詞時降低相關性，查詢的結果靠后

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

• ~ 允許出現該單詞，但是出現時相關性為負，表示擁有該字會下降相關性，但不像「-」將之排除，只是排在較后面

• "" 雙引號表示短語，表示要徹底相符，不可拆字效果，類同于 like '%keyword%'

• () 經過括號來使用字條件:

+aaa +(>bbb <ccc) 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">李秀琴 <練習冊 <不是人>是個鬼' in boolean mode);

四、測試結果

測試環境：本機4核16G Windows10，MySQL 8.0
測試數據量：salebilldetail 表 1276萬行，salebill 表 269 萬行, customer 表 30 萬行, goods 表 75 萬行。

爭對測試用的SQL語句，增加了以下全文索引：

CREATE FULLTEXT INDEX billno_fulltext ON salebill(billno) WITH PARSER ngram;
CREATE FULLTEXT INDEX remarks_fulltext ON salebill(remarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX remarks_fulltext ON salebilldetail(remarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX goodsremarks_fulltext ON salebilldetail(goodsremarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX remarks_goodsremarks_fulltext ON salebilldetail(remarks, goodsremarks) WITH PARSER ngram;
CREATE FULLTEXT INDEX custname_fulltext ON customer(custname) WITH PARSER ngram;
CREATE FULLTEXT INDEX goodsname_fulltext ON goods(goodsname) WITH PARSER ngram;
CREATE FULLTEXT INDEX goodscode_fulltext ON goods(goodscode) WITH PARSER ngram;

測試結果，總的來說很魔幻。
為什么魔幻，看下面幾個語句：

test_1

-- 測試1，原始 like 查詢方式，用時 0.765s
select 1 from salebilldetail d where d.tid=260434 and ((d.remarks like concat('%','葡萄','%')) or (d.goodsremarks like concat('%','葡萄','%')));

test_2

-- 測試2，使用全文索引 remarks_fulltext、goodsremarks_fulltext, 用時 0.834s
select 1 from salebilldetail d where d.tid=260434 and ((match(d.remarks) Against(concat('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  in boolean mode)));

test_3

-- 測試3，使用全文索引 remarks_goodsremarks_fulltext, 用時 0.242s
select 1 from salebilldetail d where d.tid=260434 and ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode)));

test_4

-- 測試4，原始 like 查詢方式，不過濾 tid ，用時 22.654s
select t from salebilldetail d where ((d.remarks like concat('%','葡萄','%')) or (d.goodsremarks like concat('%','葡萄','%')));

test_5

-- 測試5，使用全文索引 remarks_fulltext、goodsremarks_fulltext,  不過濾 tid ，用時 24.855s
select 1 from salebilldetail d where ((match(d.remarks) Against(concat('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  in boolean mode)));

test_6

-- 測試6，使用全文索引 remarks_goodsremarks_fulltext, 不過濾 tid ，用時 0.213s
select 1 from salebilldetail d where ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode)));

test_7

-- 測試7，使用全文索引 remarks_goodsremarks_fulltext, 用時 0.22s
select count(1) from salebilldetail d where d.tid=260434 and  ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode)));

test_8

-- 測試8，使用全文索引 remarks_goodsremarks_fulltext, 不過濾 tid ，用時 0.007s
select count(1) from salebilldetail d where ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode)));

從上面的測試語句可以看出，數據量越多，查詢越簡單，全文索引的效果越好。

再來看看我們的業務測試SQL:

test_9

-- 測試9
select 
    i.billid
    ,if(0,0,i.qty) as qty  
    ,if(0,0,i.goodstotal) as total          
    ,if(0,0,i.chktotal) as selfchktotal   
    ,if(0,0,i.distotal) as distotal 
    ,if(0,0,i.otherpay) as feetotal  
    ,if(0,0,ifnull(d.costtotal,0)) as costtotal  
    ,if(0,0,ifnull(d.maoli,0)) as maoli         
    ,i.billno
    ,from_unixtime(i.billdate,'%Y-%m-%d') as billdate /*單據日期*/
    ,from_unixtime(i.createdate,'%Y-%m-%d %H:%i:%s') as createdate /*制單日期*/
    ,if(i.sdate=0,'',from_unixtime(i.sdate,'%Y-%m-%d  %H:%i:%s')) as sdate /*過賬日期*/
    ,from_unixtime(i.udate,'%Y-%m-%d %H:%i:%s') as udate /*最后修改時間*/
    ,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 /* 打印次數 */
    ,(case  when i.rednew=1 then 1  when i.redold=1 then 2  when i.settle=1 then 3  when i.effect=1 then 4  else 9 end) as state /*單據狀態*/
    ,(case  when i.rednew=1 then '紅沖單'  when i.redold=1 then '已紅沖'  when i.settle=1 then '已結算'  when i.effect=1 then '已過賬'  else '草稿' end) as statetext
    ,'' as susername /* 操作人 */
    ,'' as accname /* 科目 */
from salebill i
left join coursecentersale d on d.tid=i.tid and d.billid=i.billid
left join customer c on c.tid=i.tid and c.custid=i.custid
left join store k on k.tid=i.tid and k.storeid=i.storeid
left join employee e on e.tid=i.tid and e.empid=i.empid
left join user u on u.tid=i.tid and u.userid=i.userid
where i.tid=260434 and (i.billtype = 5 or i.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.billno like concat('%','葡萄','%'))
        or (i.remarks like concat('%','葡萄','%'))
        or exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((d.remarks like concat('%','葡萄','%')) or (d.goodsremarks like concat('%','葡萄','%'))))
        or exists(select 1 from customer c where c.tid=260434 and c.custid=i.custid and (c.custname like concat('%','葡萄','%')))
        or exists(select 1 from goods g join salebilldetail d on d.tid=g.tid and d.goodsid=g.goodsid where d.tid=260434 and d.billid=i.billid and ((g.goodsname like concat('%','葡萄','%')) or (g.goodscode like concat('%','葡萄','%'))))
    )
    and i.rednew=0 /*單據列表不含紅沖單*/ 
    and i.billid not in (select billid from coursecenter_del t where t.tid=260434)
    and ((i.settle=1 and i.effect=1 and i.redold=0 and i.rednew=0)) /*已結算*/
order by udate desc,billno desc
limit 0,100;

執行時間約 1.6 秒，使用的是 like 方式。

改成使用全文索引方式：

test_10

-- 測試10
select 
    i.billid
    ,if(0,0,i.qty) as qty         
    ,if(0,0,i.goodstotal) as total   
    ,if(0,0,i.chktotal) as selfchktotal  
    ,if(0,0,i.distotal) as distotal 
    ,if(0,0,i.otherpay) as feetotal  
    ,if(0,0,ifnull(d.costtotal,0)) as costtotal 
    ,if(0,0,ifnull(d.maoli,0)) as maoli  
    ,i.billno
    ,from_unixtime(i.billdate,'%Y-%m-%d') as billdate /*單據日期*/
    ,from_unixtime(i.createdate,'%Y-%m-%d %H:%i:%s') as createdate /*制單日期*/
    ,if(i.sdate=0,'',from_unixtime(i.sdate,'%Y-%m-%d  %H:%i:%s')) as sdate /*過賬日期*/
    ,from_unixtime(i.udate,'%Y-%m-%d %H:%i:%s') as udate /*最后修改時間*/
    ,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 /* 打印次數 */
    ,(case  when i.rednew=1 then 1  when i.redold=1 then 2  when i.settle=1 then 3  when i.effect=1 then 4  else 9 end) as state /*單據狀態*/
    ,(case  when i.rednew=1 then '紅沖單'  when i.redold=1 then '已紅沖'  when i.settle=1 then '已結算'  when i.effect=1 then '已過賬'  else '草稿' end) as statetext
    ,'' as susername /* 操作人 */
    ,'' as accname /* 科目 */
from salebill i
left join coursecentersale d on d.tid=i.tid and d.billid=i.billid
left join customer c on c.tid=i.tid and c.custid=i.custid
left join store k on k.tid=i.tid and k.storeid=i.storeid
left join employee e on e.tid=i.tid and e.empid=i.empid
left join user u on u.tid=i.tid and u.userid=i.userid
where i.tid=260434 and (i.billtype = 5 or i.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('"','葡萄','"') in boolean mode))
        or (match(i.remarks) against(concat('"','葡萄','"') in boolean mode))
        or exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks) Against(concat('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  in boolean mode))))
        or exists(select 1 from customer c where c.tid=260434 and c.custid=i.custid and (match(c.custname) Against(concat('"','葡萄','"') in boolean mode)))
        or exists(select 1 from goods g join salebilldetail d on d.tid=g.tid and d.goodsid=g.goodsid where d.tid=260434 and d.billid=i.billid 
     and ((match(g.goodsname) Against(concat('"','葡萄','"') in boolean mode))
     or (match(g.goodscode) Against(concat('"','葡萄','"') in boolean mode))))
    )
    and i.rednew=0 /*單據列表不含紅沖單*/ 
    and i.billid not in (select billid from coursecenter_del t where t.tid=260434)
    and ((i.settle=1 and i.effect=1 and i.redold=0 and i.rednew=0)) /*已結算*/
order by udate desc,billno desc
limit 0,100;

執行時間約 1.6 秒，與使用的是 like 方式差不多。

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

exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks) Against(concat('"','葡萄','"') in boolean mode)) or (match(d.goodsremarks) Against(concat('"','葡萄','"')  in boolean mode))))

test_11

改成使用全文索引 remarks_goodsremarks_fulltext

-- 測試11
exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode))))

執行時間無限長（跑了半天沒成功）？
經分析，在 where 子句中，一個條件子句中包含一個以上 match 時會出現這樣的情況。即：

-- and 中只有一個全文檢索時正常， 用時0.2秒
select xxx from xxx
...
and (
	exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode))))
)
...

-- 下面這樣就異常了，會慢成百上千倍，用時 160 秒， 如果有更多的 match ，會更夸張的慢下去
select xxx from xxx
...
and (
	exists(select 1 from salebilldetail d where d.tid=260434 and d.billid=i.billid and ((match(d.remarks,d.goodsremarks) Against(concat('"','葡萄','"') in boolean mode))))
	or match(i.billno) against(concat('"','葡萄','"') in boolean mode)
)
...

測試結果匯總：

五、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

  
  

  -- 修改加密規則 
  ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
  -- 更新用戶密碼
  ALTER USER '賬號'@'%' IDENTIFIED WITH mysql_native_password BY '密碼';
  -- 刷新權限
  FLUSH PRIVILEGES;

  
  

• 方式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}?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
  // useSSL  如果不配置false 項目可以正常啟動但是會提示ssl問題
  // serverTimezone=UTC 必須配置【時區設置成自己對應的時區】否則項目會報錯

  
  

  show variables like '%time_zone%'；
  set global time_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是什么”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。