हैलो, हेब्रैचैटेल!
InnoDB पूर्ण-पाठ डेटा खनन कई MySQL / InnoDB डेवलपर्स के लिए एक प्रसिद्ध सिरदर्द है। जो लोग अप टू डेट नहीं हैं, मैं उन्हें समझाऊंगा। MyISAM तालिका प्रकार में एक पूर्ण-पाठ डेटा खोज होती है, हालाँकि, तालिका में स्वयं ऐतिहासिक रूप से सीमाएँ होती हैं जो व्यक्तिगत परियोजनाओं में मौलिक होती हैं। अधिक उन्नत InnoDB तालिका प्रकार में पूर्ण-पाठ खोज नहीं है। इसलिए गरीब डेवलपर्स को MyISAM की सीमाओं के साथ या InnoDB में खोज की कमी के साथ रखना पड़ता है। मैं इस बारे में बात करना चाहता हूं कि जादू के बिना और विशेष रूप से नियमित साधनों का उपयोग करके इनोबीडी में पूर्ण-खोज को व्यवस्थित करने के तरीके क्या हैं। प्रत्येक विधि की गति विशेषताओं की तुलना करना भी दिलचस्प होगा।

उदाहरण के लिए, 10,000 प्रविष्टियों के साथ एक छोटी तालिका लें।

बनाएँ उपयोगकर्ताओं ()
आईडी INT ( 11 ) नहीं पूर्ण AUTO_INCREMENT ,
लॉग इन VARCHAR ( 255 ) DEFAULT NULL ,
` पासवर्ड` वारचेयर ( 255 ) डिफॉल्ट नाल ,
VARCHAR ( 255 ) DEFAULT NULL का नाम
उपनाम VARCHAR ( 255 ) DEFAULT NULL ,
ईमेल VARCHAR ( 255 ) पूर्ण नहीं ,
देश वरच्र ( 255 ) DEFAULT NULL ,
शहर वरचेर ( 255 ) डेफॉल्ट नाल ,
प्राथमिक कुंजी ( आईडी )
)
इंजन = INNODB

इस तालिका में, हम साइट के उपयोगकर्ताओं का डेटा संग्रहीत करते हैं। साइट पर स्वयं एक उपयोगकर्ता खोज फ़ॉर्म है जिसमें आप "टॉल्स्टॉय यास्नाया पॉल्यूशन" फॉर्म की एक मनमानी क्वेरी दर्ज कर सकते हैं। इस तरह के अनुरोध को संसाधित करने के लिए, एक बार में कई क्षेत्रों में खोज की जानी चाहिए। हमें फ़ील्ड लॉगिन , नाम , उपनाम , शहर , देश के लिए एक खोज की आवश्यकता है। अनुरोध या तो एक शब्द (नाम या शहर) या एक स्थान द्वारा अलग किए गए शब्दों के एक सेट के रूप में हो सकता है। समस्या यह है कि हमें एक साथ कई क्षेत्रों में शब्दों के इस सेट को खोजना होगा, जो अतिरिक्त कार्यों का उपयोग किए बिना इनोबीडी में करना मुश्किल है।

InnoDB में पूर्ण-पाठ डेटा खोजने के लिए कई अपेक्षाकृत सरल तरीके हैं:

1. MyISAM में मिरर टेबल का उपयोग करना
2. कैश्ड डेटा के साथ MyISAM में एक मिरर टेबल का उपयोग करना
3. MyISAM में एक कीवर्ड टेबल का उपयोग करना
4. InnoDB में क्वेरी पार्सिंग और प्रत्यक्ष खोज
5. तृतीय-पक्ष समाधान का उपयोग करना

आइए उनमें से प्रत्येक पर अधिक विस्तार से विचार करें।

MyISAM में मिरर टेबल का उपयोग करना

पहला प्रस्तावित तरीका MyISAM में एक अतिरिक्त तालिका बनाना है। जैसा कि आप जानते हैं, MyISAM बहुत अच्छी तरह से पूर्ण-पाठ खोज का समर्थन करता है और इसका उपयोग किया जा सकता है। मुख्य तालिका ( उपयोगकर्ता ) के सभी डेटा को इस अतिरिक्त तालिका में कॉपी किया जाएगा। ट्रिगर द्वारा सिंक्रनाइज़ेशन प्रदान किया जाएगा। नई तालिका में, फ़ील्ड लॉगिन , नाम , उपनाम , शहर , देश जोड़ें । इस प्रकार, हम मुख्य तालिका का एक "दर्पण" बनाएंगे, और हम इसके साथ काम करेंगे। पूर्ण-पाठ खोज को सक्षम करने के लिए, सभी 5 क्षेत्रों में एक साथ FULLTEXT सूचकांक जोड़ें:

सृजन संबंधी खोज (
आईडी INT ( 11 ) DEFAULT NULL ,
लॉग इन VARCHAR ( 255 ) DEFAULT NULL ,
VARCHAR ( 255 ) DEFAULT NULL का नाम
उपनाम VARCHAR ( 255 ) DEFAULT NULL ,
देश वरच्र ( 255 ) DEFAULT NULL ,
शहर वरचेर ( 255 ) डेफॉल्ट नाल ,
FULLTEXT INDEX IX_search ( शहर , देश , लॉगिन , नाम , उपनाम )
)
इंजन = MYISAM

मुख्य तालिका और दर्पण तालिका के बीच डेटा को सिंक्रनाइज़ करने के लिए, हम उपयोगकर्ताओं पर लिखने, बदलने और पढ़ने के लिए ट्रिगर सेट करते हैं:

रिकॉर्डिंग ट्रिगर:

बनाएँ
TRIGGER `डालें`
INSERT के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
INSERT INTO सर्च ( `id` , ` login` , `name` , ` surname` , `country` , ` city` ) VALUES (
नई। `Id`,
नई। `लॉगिन करें ,
नई। `नाम` ,
नई। `उपनाम ' ,
नई। `देश` ,
नई। `city`
) ;
अंत

ट्रिगर बदलें:

बनाएँ
TRIGGER `अपडेट`
अद्यतन के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
DELETE से `खोज` कहाँ` आईडी` = नया । `आईडी` ;
INSERT INTO `खोज` ( ` आईडी` , `लॉगिन` , ` नाम` , `उपनाम` , ` देश` , `शहर` ) VALUES (
नई। `Id`,
नई। `लॉगिन करें ,
नई। `नाम` ,
नई। `उपनाम ' ,
नई। `देश` ,
नई। `city`
) ;
अंत

और एक सरल हटाने ट्रिगर:

बनाएँ
TRIGGER `हटाओ`
DELETE के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
DELETE से `खोज` कहाँ` आईडी` = पुराने । `आईडी` ;
अंत

खोज निम्नलिखित क्वेरी का उपयोग करके की जाती है:

'Users` का चयन करें । * FROM` उपयोगकर्ता`
INNER JOIN `search`
`खोज` पर । `आईडी` = ` उपयोगकर्ता` । `id`
कहां
MATCH ( `खोज` । शहर , ` खोज` देश , `खोज` । लॉगिन , ` खोज` । नाम , `खोज` । उपनाम ) AGAINST ( 'व्लादिमीर टुपिन सेंट पीटर्सबर्ग' BOOLEAN MODE में ) : 0
ORDER BY MATCH ( `search` । City , ` search` । देश , `खोज` । Login , ` search` । Name , `search` । Surname ) AGAINST ( 'व्लादिमीर टुपिन सेंट पीटर्सबर्ग' BOOLEANEE DESC ) DESC।

यहां, डेटा को खोज तालिका में खोजा जाता है, परिणाम प्रासंगिकता द्वारा सॉर्ट किया जाता है, और आउटपुट पर हमें उपयोगकर्ता तालिका से संबंधित प्रविष्टियां मिलती हैं।

इस दृष्टिकोण का मुख्य लाभ अतिरिक्त अनुक्रमित जोड़कर खोज का लचीलापन है और नए खोज संयोजनों (देश + शहर या लॉगिन + नाम + उपनाम) को संकलित करना है। इस प्रकार, हम खोज और प्रासंगिक नियमों के लिए नए सेट बनाने के लिए स्वतंत्र हैं।
इस पद्धति के नुकसान (साथ ही "दर्पण" के निर्माण के सभी तरीके) डेटा के अत्यधिक भंडारण हैं। इसलिए, हमारे उदाहरण के रूप में छोटी मात्रा में डेटा के साथ इसका उपयोग करना उचित है।

कैश्ड डेटा के साथ MyISAM में एक मिरर टेबल का उपयोग करना

दूसरा तरीका डेटा मिरर बनाना भी है, हालांकि यहां हम केवल एक फ़ील्ड में डेटा स्टोर करेंगे। इस कार्य में, खोज को खेतों के एक समूह द्वारा तुरंत किया जाता है और हम उन्हें रिक्त स्थान द्वारा अलग किए गए एक पाठ क्षेत्र में संयोजित करने का प्रयास करेंगे। इस प्रकार, एक एकल डेटासेट उपयोगकर्ता तालिका में सेट किए गए संपूर्ण डेटा के अनुरूप होगा। दो फ़ील्ड आईडी और टेक्स्ट के साथ एक खोज तालिका बनाएं। Id - मुख्य तालिका ( उपयोगकर्ताओं ) की आईडी के अनुरूप होगी, पाठ हमारा "कैश्ड" डेटा है।

सृजन संबंधी खोज (
आईडी INT ( 11 ) DEFAULT NULL ,
`पाठ` पाठ दोष समाप्त ,
पूर्ण INDEX IX_search_text ( `पाठ` )
)
इंजन = MYISAM

ट्रिगर्स का उपयोग करके सिंक्रोनाइज़ेशन भी किया जाता है:

जोड़ना:

बनाएँ
TRIGGER `डालें`
INSERT के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
INSERT INTO सर्च ( `id` , ` text` ) VALUES ( NEW । `Id` )
लोअर (
CONCAT_WS ( '' ,
नई। `नाम` ,
नई। `उपनाम ' ,
नई। `लॉगिन करें ,
नई। `देश` ,
नई। `city`
)
)
) ;
अंत

बदलने के लिए:

बनाएँ
TRIGGER `अपडेट`
अद्यतन के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
खोज से कहाँ से `आईडी` = नया । `आईडी` ;
INSERT INTO सर्च ( `id` , ` text` ) VALUES ( NEW । `Id` )
लोअर (
CONCAT_WS ( '' ,
नई। `नाम` ,
नई। `उपनाम ' ,
नई। `लॉगिन करें ,
नई। `देश` ,
नई। `city`
)
)
) ;
END बनाएं

हटाने:

TRIGGER `हटाओ`
DELETE के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
DELETE FROM सर्च कहां से करें 'id` = OLD `आईडी` ;
अंत

खोज क्वेरी इस तरह दिखाई देती है:

'Users` का चयन करें । * FROM` उपयोगकर्ता`
INNER JOIN `search`
`खोज` पर । `आईडी` = ` उपयोगकर्ता` । `id`
कहां
MATCH ( `search`.` टेक्स्ट` ) AGAINST ( 'व्लादिमीर तुपिन सेंट पीटर्सबर्ग' BOOLEAN MODE ' में ) 0
MATCH द्वारा आदेश ( `खोज` 'पाठ ' ) AGAINST ( 'व्लादिमीर टुपिन सेंट पीटर्सबर्ग' BOOLEAN MODE ) DESC में

यह विधि पहले की तरह लचीली नहीं है, हालांकि, जैसा कि हम बाद में देखेंगे, यह बड़ी संख्या में विभिन्न अनुरोधों के साथ गति में जीतता है।

MyISAM में एक कीवर्ड टेबल का उपयोग करना

तीसरी विधि "कीवर्ड" - खोज टैग की एक सूची बनाने पर आधारित है। उपयोगकर्ता तालिका में फ़ील्ड फ़ील्ड हैं। उदाहरण के लिए, फ़ील्ड (id=2144; login= leo; name=;surname=;city=' ';country=;email=leo@tolstoy.ru;password=;) कीवर्ड होंगे («leo»; «»; «»; « »; «») । हम इन सभी शब्दों को एक अलग MyISAM तालिका में लिखेंगे, जिसमें दो फ़ील्ड आईडी और टेक्स्ट होंगे । आईडी मुख्य तालिका ( उपयोगकर्ताओं ) की आईडी से मेल खाती है। और टेक्स्ट एक फ़ील्ड है जिसमें कीवर्ड टैग लिखे जाएंगे। उपयोगकर्ता तालिका के प्रत्येक उपयोगकर्ता की नई खोज तालिका में 5 प्रविष्टियां होंगी। इस प्रकार, हमें प्रत्येक उपयोगकर्ता के लिए टैग की एक तालिका मिली।

सृजन संबंधी खोज (
आईडी INT ( 11 ) DEFAULT NULL ,
`पाठ` वर्ण ( 255 ) दोष पूर्ण
पूर्ण INDEX IX_search_text ( `पाठ` )
)
इंजन = MYISAM

डेटा सिंक्रनाइज़ेशन भी ट्रिगर द्वारा किया जाता है:

निर्माण:

बनाएँ
TRIGGER `डालें`
INSERT के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
INSERT INTO सर्च ( `id` , ` text` ) VALUES
(( `` `Id` , NEW.` Login` ) ,
(( `` `Id` , NEW.` Name` ) ,
(( `` `आईडी` , नया.` उपनाम` ) ,
(( `` `Id` , NEW.` देश` ) ,
(( `` `आईडी` , न्यू.` सिटी` ) ;
अंत

बदलने के लिए:

बनाएँ
TRIGGER `अपडेट`
अद्यतन के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
खोज से कहाँ से `आईडी` = नया । `आईडी` ;
INSERT INTO सर्च ( `id` , ` text` ) VALUES
(( `` `Id` , NEW.` Login` ) ,
(( `` `Id` , NEW.` Name` ) ,
(( `` `आईडी` , नया.` उपनाम` ) ,
(( `` `Id` , NEW.` देश` ) ,
(( `` `आईडी` , न्यू.` सिटी` ) ;
अंत

हटाने:

बनाएँ
TRIGGER `हटाओ`
DELETE के बाद
उपयोगकर्ताओं पर
हर एक के लिए
शुरू
DELETE FROM सर्च कहां से करें 'id` = OLD `आईडी` ;
अंत

खोज क्वेरी:

'Users` का चयन करें । * FROM` उपयोगकर्ता`
INNER JOIN `search`
`खोज` पर । `आईडी` = ` उपयोगकर्ता` । `id`
कहां
MATCH ( `search`.` टेक्स्ट` ) AGAINST ( 'व्लादिमीर तुपिन सेंट पीटर्सबर्ग' BOOLEAN MODE ' में ) 0
ग्रुप बाय `सर्च` । `id`
COUNT ( * ) DESDER द्वारा आदेश

कृपया ध्यान दें कि यदि पहले प्रासंगिकता MyISAM द्वारा निर्मित खोज इंजन में निर्धारित की गई थी, तो इस मामले में हम इसे स्वयं निर्धारित करते हैं। खोज के परिणामस्वरूप, हमें केवल वे टैग प्राप्त हुए जो अनुरोध से मेल खाते हैं। और एक उपयोगकर्ता के पास जितने अधिक टैग होते हैं, वे नमूना में उच्च होते हैं।
दिए गए उदाहरण में एक खामी है: समान संख्या में टैग के साथ, कई रिकॉर्ड में एक प्राकृतिक छंटाई होती है, जो प्रासंगिकता के दृष्टिकोण से हमेशा सच नहीं होती है।
हालांकि, इस पद्धति में आगे विकास के लिए एक उच्च क्षमता है। सबसे पहले, हम MATCH AGAINST से प्रासंगिकता रेटिंग के योग को ORDER BY से जोड़ सकते हैं। इस प्रकार, उपरोक्त दोष समाप्त हो जाएगा। दूसरे, हम इस तालिका में वजन टैग का एक अतिरिक्त वजन क्षेत्र जोड़ सकते हैं, और मुख्य तालिका के प्रत्येक क्षेत्र के लिए इस वजन का मान निर्दिष्ट कर सकते हैं। इस प्रकार, हम व्यक्तिगत क्षेत्रों के महत्व (वजन) को ध्यान में रखते हुए छँटाई जोड़ सकते हैं। यह हमें खोज की गुणवत्ता से समझौता किए बिना कुछ क्षेत्रों पर ध्यान केंद्रित करने का अवसर देता है।

InnoDB में क्वेरी पार्सिंग और प्रत्यक्ष खोज

चौथा तरीका कठोर है और पिछले वाले के रूप में MyISAM का उपयोग नहीं करता है। इसमें कोई अतिरिक्त टेबल और ट्रिगर भी नहीं है। हम अभी मौजूदा तालिका खोजेंगे। पहले हमें उन सभी क्षेत्रों को अनुक्रमित करने की आवश्यकता है जिनमें खोज की जाएगी।

बनाएँ उपयोगकर्ताओं ()
आईडी INT ( 11 ) पूर्ण नहीं ,
लॉग इन VARCHAR ( 255 ) DEFAULT NULL ,
` पासवर्ड` वारचेयर ( 255 ) डिफॉल्ट नाल ,
VARCHAR ( 255 ) DEFAULT NULL का नाम
उपनाम VARCHAR ( 255 ) DEFAULT NULL ,
ईमेल VARCHAR ( 255 ) पूर्ण नहीं ,
देश वरच्र ( 255 ) DEFAULT NULL ,
शहर वरचेर ( 255 ) डेफॉल्ट नाल ,
प्राथमिक कुंजी ( आईडी ) ,
INDEX शहर ( शहर ) ,
INDEX देश ( देश ) ,
INDEX ईमेल ( ईमेल ) ,
INDEX लॉगिन ( लॉगिन ) ,
INDEX नाम ( नाम ) ,
INDEX पासवर्ड ( पासवर्ड ) ,
INDEX उपनाम ( उपनाम )
)
इंजन = INNODB

InnoDB में, हम केवल LIKE ऑपरेटर का उपयोग करके खोज कर सकते हैं, लेकिन इसके प्रभावी संचालन के लिए क्वेरी को शब्दों में विभाजित करना आवश्यक है, अन्यथा कई शब्दों से युक्त प्रश्न बिना परिणाम के रहेंगे। शब्दों में विभाजित करने और क्वेरी बनाने के लिए, हम एक फ़ंक्शन लिखते हैं:

बनाएँ
समारोह खोज ( str VARCHAR ( 255 ) )
रिटर्न्स वेरचर ( 255 ) CHARSET cp1251
शुरू
DECLARE आउटपुट VARCHAR ( 255 ) DEFAULT '' ;
DECLARE temp_str VARCHAR ( 255 ) ;
DECLARE first_part VARCHAR ( 255 ) DEFAULT "CONCAT_WS ('',` name`, `surname`,` login`, `country`,` city`) LIKE '% " ;
DECLARE last_part VARCHAR ( 255 ) DEFAULT "% '" ;

LENGTH ( str ) ! = 0 डीओ
SET temp_str = SUBSTRING_INDEX ( str , '' , 1 ) ;
इफ टेम्पर_स्ट्र = स्ट्रैट
तब
सेट स्ट्रेट = '' ;
वरना
SET str = SUBSTRING ( str , LENGTH ( temp_str ) + 2 ) ;
END IF ;

अगर उत्पादन! = ''
तब
सेट आउटपुट = CONCAT ( आउटपुट , 'OR' ) ;
END IF ;

सेट आउटपुट = CONCAT ( आउटपुट , first_part , temp_str , last_part ) ;

END WHILE;
RETURN आउटपुट;
अंत

फ़ंक्शन उत्पन्न खोज क्वेरी का एक टुकड़ा लौटाता है जिसे आपको केवल स्थानापन्न और निष्पादित करने की आवश्यकता होती है:

SET @ WHERE = CONCAT ( 'SELECT * FROM` यूजर्स WHERE' , सर्च ( 'हबरा ख़बरोविच' ) ) ;
PREPARE ने @ WHROM से तैयार किया;
तैयार तैयार;

आप अस्थायी तालिकाओं का उपयोग भी कर सकते हैं, क्वेरी परिणाम संसाधित करते समय वे ठोस सुविधा देंगे।

तृतीय-पक्ष समाधान का उपयोग करना

कई तृतीय-पक्ष पूर्ण-पाठ खोज समाधान हैं। सबसे लोकप्रिय मंच स्फिंक्स और अपाचे ल्यूसिन आधारित परियोजनाएं हैं। उनका उपयोग कम मात्रा में डेटा (जैसे हमारे उदाहरण में) के साथ अर्थहीन है, और कभी-कभी प्रतिबंध (होस्टर, दुष्ट व्यवस्थापक, कुटिल हाथ, आदि) के कारण यह असंभव है।

तुलना

पूर्ण-पाठ खोज के दिखाए गए तरीकों की तुलना करें (तृतीय-पक्ष समाधानों को छोड़कर) सामान्य प्रश्नों की गति के लिए। हम उदाहरण के रूप में बदलती जटिलता के 50 प्रश्नों का उपयोग करेंगे। ऐसा करने के लिए, हम एक PHP स्क्रिप्ट लिखते हैं जो उपर्युक्त तरीकों में से प्रत्येक द्वारा खोज की औसत गति की निष्पक्ष रूप से गणना करेगा। माप को वास्तविक स्थितियों के करीब लाने के लिए, हम दूसरा नियंत्रण माप करेंगे, जिसमें समान खोज क्वेरी का उपयोग किया जाएगा। यहां यह मूल्यांकन करना संभव होगा कि प्रत्येक विधि में MySQL कैशिंग तंत्र का कितना अच्छा उपयोग किया जाता है।

विभिन्न तरीकों से InnoDB तालिका में MySQL डेटाबेस में खोज प्रश्नों की गति की तुलना:



अधिक जानकारी:

विधि	एक अनुरोध की औसत गति (सेकंड)।	एक दोहराया अनुरोध के निष्पादन की औसत गति (सेकंड)
MyISAM में मिरर टेबल का उपयोग करना	0.029738	0.011974
कैश्ड डेटा के साथ MyISAM में एक मिरर टेबल का उपयोग करना	0.025652	0.012027
MyISAM में एक कीवर्ड टेबल का उपयोग करना	0.027876	0.008866
InnoDB में क्वेरी पार्सिंग और प्रत्यक्ष खोज	0.136091	.०९,५४१

जैसा कि अपेक्षित था, InnoDB पर प्रत्यक्ष LIKE खोजों को सबसे धीमा और बाकी सभी के लिए महत्वपूर्ण रूप से खो देता है। बेशक, इस पद्धति को अभी भी अनुकूलित किया जा सकता है, लेकिन यह गति में एक महत्वपूर्ण लाभ देने की संभावना नहीं है।
शेष बची हुई तीन खोजी विधियों ने लगभग समान स्तर पर खुद को दिखाया। जैसा कि अभ्यास ने दिखाया है, बड़ी संख्या में समान अनुरोधों के साथ, MyISAM में कीवर्ड (टैग) का उपयोग एक मूर्त लाभ देता है। बड़ी संख्या में विभिन्न खोज क्वेरी के साथ, जीतने का दूसरा तरीका है - कैश्ड दर्पण बनाना। यदि कुछ फ़ील्ड दूसरों से आकार में बहुत भिन्न हैं (लेख सामग्री, समाचार पाठ), तो पहली विधि अधिक प्रभावी है - दर्पण तालिका बनाना।

MyISAM मिरर बनाना छोटे तालिकाओं (तालिका में 10-50 हजार रिकॉर्ड) के लिए उपयोग किया जाना चाहिए, अगर तालिका में अधिक रिकॉर्ड हैं, और तकनीकी क्षमताएं तृतीय-पक्ष तंत्र (Sphinx, Apache Lucene) का उपयोग करने की अनुमति देती हैं।