涓€銆丮ySQL鍏ㄦ枃绱㈠紩鍩虹姒傚康
MySQL鍏ㄦ枃绱㈠紩鏄竴绉嶇壒娈婄被鍨嬬殑绱㈠紩锛屼笓闂ㄧ敤浜庡鏂囨湰鍐呭杩涜鍏ㄦ枃鎼滅储锛屽叾鏍稿績鍘熺悊鏄€氳繃瀵规枃鏈暟鎹殑鍒嗚瘝澶勭悊寤虹珛鍊掓帓绱㈠紩缁撴瀯銆備笌浼犵粺绱㈠紩鐩告瘮锛屽叏鏂囩储寮曟敮鎸佽嚜鐒惰瑷€鎼滅储鍜屽竷灏旀ā寮忔悳绱紝鑳藉蹇€熷畾浣嶅寘鍚壒瀹氳瘝姹囨垨鐭鐨勮褰曡銆?/p>
閽堝涓枃鏂囨湰鐨勭壒鎬э紝MySQL 8.0鍙婁互涓婄増鏈彁渚涗簡ngram鍏ㄦ枃瑙ｆ瀽鍣紝鏈夋晥瑙ｅ喅浜嗕腑鏂囧瓧绗﹁繛缁功鍐欍€佹棤澶╃劧鍒嗛殧绗﹀甫鏉ョ殑鍒嗚瘝鎸戞垬銆傝瑙ｆ瀽鍣ㄩ噰鐢ㄦ寜瀛楀垏鍓叉満鍒讹紝鏈夋晥鎻愬崌浜嗕腑鏂囨绱㈢殑鍑嗙‘鎬у拰瑕嗙洊鑼冨洿銆?/p>
浜屻€丮ySQL鐜鍑嗗涓庡畨瑁?/h2>
2.1 绯荤粺瑕佹眰涓庡畨瑁?/h3>
鍦ㄥ紑濮嬮厤缃叏鏂囩储寮曞墠锛岄渶纭繚MySQL鐜姝ｇ‘瀹夎锛?/p>
- 鐗堟湰瑕佹眰锛氭帹鑽愪娇鐢∕ySQL 8.0.26鎴栨洿楂樼増鏈紝浠ヨ幏寰楁渶浣崇殑鍏ㄦ枃绱㈠紩鍔熻兘鏀寔
- 绯荤粺閰嶇疆锛氬缓璁?鏍窩PU/4GB鍐呭瓨璧凤紝鐢熶骇鐜鎺ㄨ崘8鏍?6GB閰嶇疆
- 瀹夎姝ラ锛?
- 閫氳繃瀹樻柟缃戠珯鎴栧彲闈犳笭閬撲笅杞藉畨瑁呭寘
- 鎸夌収瀹夎鍚戝瀹屾垚鍩虹閰嶇疆锛岀壒鍒敞鎰忚缃己瀵嗙爜骞跺Ε鍠勪繚绠?/li>
- 楠岃瘉瀹夎鎴愬姛锛氬湪缁堢杈撳叆
mysql --version鏄剧ず鐗堟湰淇℃伅鍗充负瀹夎鎴愬姛
2.2 閰嶇疆鏂囦欢浼樺寲
缂栬緫MySQL閰嶇疆鏂囦欢my.ini锛圵indows锛夋垨f锛圠inux锛夛紝纭繚浠ヤ笅鍏抽敭鍙傛暟閰嶇疆姝ｇ‘锛?/p>
- 璁剧疆姝ｇ‘鐨勫畨瑁呯洰褰曞拰鏁版嵁瀛樻斁鐩綍
- 閰嶇疆瀛楃闆嗕负utf8mb4锛屽叏闈㈡敮鎸佷腑鏂囧瓨鍌?/li>
- 璋冩暣
max_connections鍙傛暟锛屾牴鎹疄闄呰礋杞介渶姹傝缃繛鎺ユ暟
涓夈€佸叏鏂囩储寮曢厤缃缁嗘楠?/h2>
3.1 鍒涘缓鏀寔鍏ㄦ枃绱㈠紩鐨勮〃
鍦ㄨ璁℃暟鎹〃鏃讹紝闇€鏄庣‘鍝簺瀛楁闇€瑕佸叏鏂囩储寮曟敮鎸侊細
sql
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FULLTEXT idx_title (title) WITH PARSER ngram,
FULLTEXT idx_content (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.2 涓虹幇鏈夎〃娣诲姞鍏ㄦ枃绱㈠紩
濡傛灉宸插瓨鍦ㄦ暟鎹〃锛屽彲閫氳繃ALTER TABLE璇彞娣诲姞鍏ㄦ枃绱㈠紩锛?/p>
sql
涓簍itle瀛楁娣诲姞鍏ㄦ枃绱㈠紩
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
涓篶ontent瀛楁娣诲姞鍏ㄦ枃绱㈠紩
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram;
涓哄涓瓧娈靛垱寤鸿仈鍚堝叏鏂囩储寮?
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram;
3.3 閰嶇疆ngram浠ょ墝澶у皬
ngram瑙ｆ瀽鍣ㄧ殑鏍稿績鍙傛暟鏄护鐗屽ぇ灏忥紝瀹冨喅瀹氫簡鍒嗚瘝鐨勬渶灏忓崟鍏冿細
sql
璁剧疆ngram浠ょ墝澶у皬涓?锛堟帹鑽愪腑鏂囦娇鐢級
SET GLOBAL ngram_token_size = 2;
鎴栧湪閰嶇疆鏂囦欢涓案涔呰缃?
[mysqld]
ngram_token_size=2
浠ょ墝澶у皬閫夋嫨寤鸿锛?/p>
- 鍊间负1锛氬崟瀛楃储寮曪紝鍙洖鐜囬珮浣嗙簿纭害杈冧綆
- 鍊间负2锛氬弻瀛楃储寮曪紝骞宠 鍙洖鐜囦笌绮剧‘搴︼紝閫傚悎澶у鏁颁腑鏂囧満鏅?/li>
- 鍊间负3锛氫笁瀛楃储寮曪紝绮剧‘搴﹂珮浣嗗彲鑳介仐婕忛儴鍒嗙粨鏋?/li>
鍥涖€佷腑鏂囧叏鏂囨绱紭鍖栫瓥鐣?/h2>
4.1 鏌ヨ璇硶娣卞害瑙ｆ瀽
鍏呭垎鍒╃敤MySQL鍏ㄦ枃妫€绱㈢殑澶氱鏌ヨ妯″紡锛屾弧瓒充笉鍚屽満鏅渶姹傦細
- 鑷劧璇█妯″紡锛氭渶甯哥敤鐨勬悳绱㈡ā寮忥紝鎸夌浉鍏虫€ф帓搴忕粨鏋?
sql
SELECT id, title,
MATCH(title, content) AGAINST(‘鏁版嵁搴撲紭鍖? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST(‘鏁版嵁搴撲紭鍖? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC; - 甯冨皵妯″紡锛氭敮鎸侀珮绾ф悳绱㈡搷浣滅
sql
鍖呭惈”MySQL”浣嗕笉鍖呭惈”瀹夎
SELECT * FROM articles
WHERE MATCH(content) AGAINST(‘+MySQL -瀹夎’ IN BOOLEAN MODE);
鍖呭惈瀹屾暣鐭”涓枃浼樺寲
SELECT * FROM articles
WHERE MATCH(content) AGAINST(‘”涓枃浼樺寲”‘ IN BOOLEAN MODE);
4.2 涓枃鍒嗚瘝浼樺寲鎶€宸?/h3>
閽堝涓枃璇█鐗圭偣锛屽疄鏂戒互涓嬩紭鍖栨柟妗堬細
- 鑷畾涔夎瘝鍏告墿灞?/strong>锛氶€氳繃缁存姢涓撲笟鏈琛ㄦ彁鍗囩壒瀹氶鍩熸绱㈢簿搴?/li>
- 鍋滅敤璇嶇鐞?/strong>锛氳繃婊ゅ父瑙佽櫄璇嶅拰鍣０璇嶆眹锛屾彁楂樼储寮曟晥鐜?/li>
- 鍚屼箟璇嶅鐞?/strong>锛氬缓绔嬪悓涔夎瘝鏄犲皠鍏崇郴锛屾墿澶ф绱㈣鐩栬寖鍥?/li>
- 鍋滅敤璇嶇鐞?/strong>锛氳繃婊ゅ父瑙佽櫄璇嶅拰鍣０璇嶆眹锛屾彁楂樼储寮曟晥鐜?/li>
4.3 鎬ц兘璋冧紭鏂规
纭繚鍏ㄦ枃绱㈠紩鍦ㄩ珮鏁堢姸鎬佷笅杩愯锛?/p>
- 绱㈠紩閲嶅缓绛栫暐锛氬畾鏈熶紭鍖栬〃绌洪棿锛屾秷闄ょ储寮曠鐗?
sql
OPTIMIZE TABLE articles; - 鏌ヨ缂撳瓨鍒╃敤锛氬悎鐞嗛厤缃煡璇㈢紦瀛橈紝鍑忓皯閲嶅鍒嗚瘝璁＄畻
- 璐熻浇鍧囪 閮ㄧ讲锛氬浜庨珮骞跺彂鍦烘櫙锛岃€冭檻璇诲啓鍒嗙鏋舵瀯
浜斻€佸疄鎴樺簲鐢ㄤ笌鏁呴殰鎺掗櫎
5.1 鍏稿瀷搴旂敤鍦烘櫙
- 鏂伴椈缃戠珯锛氬疄鐜板鏂囩珷鏍囬鍜屽唴瀹圭殑蹇€熸绱?/li>
- 鐢靛晢骞冲彴锛氭敮鎸佸晢鍝佹弿杩板拰鐢ㄦ埛璇勪环鐨勫叧閿瘝鎼滅储
- 鍗氬绯荤粺锛氭彁渚涚簿鍑嗙殑鍐呭妫€绱㈠姛鑳?/li>
- 鏂囨。绠＄悊绯荤粺锛氬疄鐜板鏂囨。鍐呭鐨勮仈鍚堟悳绱?/li>
5.2 甯歌闂瑙ｅ喅鏂规
- 绱㈠紩涓嶇敓鏁?/strong>锛氭鏌ヨ〃寮曟搸鏄惁涓篒nnoDB鎴朚yISAM锛岄獙璇乶gram瑙ｆ瀽鍣ㄩ厤缃?/li>
- 涓枃鍒嗚瘝涓嶅噯纭?/strong>锛氳皟鏁磏gram_token_size鍙傛暟锛屼紭鍖栧仠鐢ㄨ瘝鍒楄〃
- 鏌ヨ鎬ц兘涓嬮檷锛氬垎鏋愭煡璇㈣鍒掞紝鑰冭檻娣诲姞瑕嗙洊绱㈠紩鎴栬皟鏁存煡璇㈣鍙?/li>
5.3 鐩戞帶涓庣淮鎶?/h3>
寤虹珛瀹屾暣鐨勭洃鎺т綋绯伙紝纭繚鍏ㄦ枃绱㈠紩闀挎湡绋冲畾杩愯锛?/p>
- 瀹氭湡妫€鏌ョ储寮曠姸鎬佷笌澶у皬
- 鐩戞帶鏌ヨ鍝嶅簲鏃堕棿鍙樺寲瓒嬪娍
- 寤虹珛瀹氭湡浼樺寲涓庨噸寤虹储寮曠殑缁存姢娴佺▼
鍏€佷簯骞冲彴閮ㄧ讲寤鸿
鍦ㄤ簯鐜閮ㄧ讲MySQL鍏ㄦ枃绱㈠紩鏃讹紝闃块噷浜戠瓑骞冲彴鎻愪緵浜嗘洿澶氫紭鍖栭€夐」鍜岀鐞嗗伐鍏枫€傚湪姝ｅ紡璐拱浜戜骇鍝佸墠锛屽缓璁€氳繃浜戝皬绔欏钩鍙伴鍙栨弧鍑忎唬閲戝埜锛屽彲鏈夋晥闄嶄綆涓婁簯鎴愭湰锛屼韩鍙楀悓绛夋€ц兘鐨勬暟鎹簱鏈嶅姟銆?/p>
浠ヤ笂鍐呭璇︾粏闃愯堪浜?025骞碝ySQL鍏ㄦ枃绱㈠紩鐨勪腑鏂囦紭鍖栧叏娴佺▼锛屼粠鍩虹姒傚康鍒板疄鎴樺簲鐢ㄥ潎鎻愪緵浜嗗彲鎿嶄綔鐨勮В鍐虫柟妗堛€傞€氳繃鍚堢悊閰嶇疆鍜屾寔缁紭鍖栵紝鍙樉钁楁彁鍗囦腑鏂囨枃鏈绱㈢殑鏁堢巼鍜屽噯纭€э紝涓哄悇绫诲簲鐢ㄦ彁渚涘己澶х殑鎼滅储鑳藉姏鏀拺锛圓I鐢熸垚锛?
内容均以整理官方公开资料，价格可能随活动调整，请以购买页面显示为准，如涉侵权，请联系客服处理。
本文由星速云发布。发布者：星速云。禁止采集与转载行为，违者必究。出处：https://www.67wa.com/16575.html
