mysql ft は FullText、つまりフルテキスト インデックスを指します。フルテキスト インデックスは、正確な数値比較ではなく、類似性に基づいてクエリを解決するためのものです。フルテキスト インデックスは N 回作成できます。大量のデータに直面したときよりも速く、速度は桁違いではありません。
このチュートリアルの動作環境: Windows 10 システム、mysql8 バージョン、Dell G3 コンピューター。
mysql ft とは何ですか?
つまり、フルテキスト インデックス (FullText) です。
MySQL フルテキスト インデックス (FullText)
フルテキスト インデックスは次のことを解決します。正確な数値比較ではなく、類似性クエリに基づく必要があります。
あいまい一致はlike %
を使用して実現することもできますが、大量のテキスト データの検索では考えられません。大量のデータに直面した場合、全文インデックス作成はlike
よりも N 倍高速になる可能性がありますが、その速度は桁違いではありません。
MySQL 5.6
以前のバージョンでは、MyISAM
ストレージ エンジンのみがフルテキスト インデックスをサポートしていましたMySQL 5.6
以降のバージョン、MyISAM
およびInnoDB
ストレージ エンジンはフルテキスト インデックスをサポートしますMySQL 5.7.6
、中国語、日本語、および韓国語 (CJK) をサポートする組み込みのフルテキストngram パーサー
と、日本語用のインストール可能なMeCab
フルテキスト パーサー プラグインを提供します。InnoDB
またはMyISAM
テーブルにのみ使用でき、CHAR
、に対してのみ作成できます# VARCHAR
、TEXT
列中国語の全文検索もサポートされていますが、バグがあります
のように
Inconsistency現在設定されている最小検索長は、次の方法で確認できます。 SQL コマンド (単語セグメントの長さ):
SHOW VARIABLES LIKE 'ft%';
Value | |||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
-><()~*:""&| | |||||||||||||||||||||||||||||||||||||||||||||
84 | |||||||||||||||||||||||||||||||||||||||||||||
1 | |||||||||||||||||||||||||||||||||||||||||||||
20 | |||||||||||||||||||||||||||||||||||||||||||||
(built-で )############ 全文索引的相关参数都无法进行动态修改,必须通过修改 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;
ログイン後にコピー
ALTER TABLE fulltext_test ADD FULLTEXT INDEX content_fulltext(content) with parser ngram;
ログイン後にコピー
2.3 删除索引
DROP INDEX content_fulltext ON fulltext_test;
ログイン後にコピー
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 布尔全文检索在布尔搜索中,我们可以在查询中自定义某个被搜索的词语的相关性,当编写一个布尔搜索查询时,可以通过一些前缀修饰符来定制搜索。
+aaa +(>bbb 测试结果,总的来说很魔幻。 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;
ログイン後にコピー
执行时间约 改成使用全文索引方式: 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;
ログイン後にコピー
执行时间约 最魔幻的地方来了,如果将上面的SQL语句中( 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改成使用全文索引 -- 测试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))))
ログイン後にコピー
执行时间无限长(跑了半天没成功)? -- 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,故简单描述升级相关问题。
【相关推荐:mysql视频教程】 |
以上がmysqlフィートとは何ですかの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。