मैसकल प्रदर्शन

इस लेख का लेखन इस त्रयी से प्रेरित है: एक , दो , तीन । मैं ट्रिक्स और फीचर्स के उपयोग पर अपना $ 0.02 जोड़ना चाहता था।

मेरे विचारों में भ्रमित न होने के लिए, मेरी पहली पोस्ट में, हम एक ओपन-सोर्स उत्पाद से अनुरोधों के उदाहरण लेंगे। "ट्रिक्स" का इस्तेमाल पोस्टग्रेसीक्यूएल, ओरेकल, SQLite, DB2 पर भी किया जाता है और यह MySQL द्वारा निर्देशित नहीं होते हैं, हालांकि अनुकूलन मुख्य रूप से MySQL InnoDB पर लक्षित है:

SELECT h.hostid,hg.groupid,h.name
FROM hosts h,hosts_groups hg
WHERE (hg.groupid IN ( '4' ))
AND hg.hostid=h.hostid
AND hg.groupid BETWEEN 000000000000000 AND 099999999999999
AND h.status IN (0,1)
AND EXISTS (
SELECT hh.hostid
FROM hosts hh, hosts_groups hgg, rights r, users_groups ug
WHERE hh.hostid=h.hostid
AND hh.hostid=hgg.hostid
AND r.id=hgg.groupid
AND r.groupid=ug.usrgrpid
AND ug.userid=3
AND r.permission>=3
AND NOT EXISTS (
SELECT hggg.groupid
FROM hosts_groups hggg, rights rr, users_groups gg
WHERE hggg.hostid=hgg.hostid
AND rr.id=hggg.groupid
AND rr.groupid=gg.usrgrpid
AND gg.userid=3
AND rr.permission<3
))
ORDER BY h.name ASC
LIMIT 1001

* This source code was highlighted with Source Code Highlighter .

स्थिति: एक अधिकृत उपयोगकर्ता है, उपयोगकर्ता एक उपयोगकर्ता समूह से संबंधित है। मेजबान समूह से संबंधित मेजबान हैं।
निचला रेखा: उन होस्ट को बाहर खींचता है जिनके पास उपयोगकर्ता की पहुंच है। होस्ट समूहों पर उपयोगकर्ता समूहों तक पहुंच दी गई है।

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

यदि क्वेरी एक तालिका पर होती है, तो कभी-कभी आप इन तरकीबों का उपयोग कर सकते हैं:

CREATE TABLE events (
eventid bigint unsigned NOT NULL ,
source integer DEFAULT '0' NOT NULL ,
object integer DEFAULT '0' NOT NULL ,
objectid bigint unsigned DEFAULT '0' NOT NULL ,
clock integer DEFAULT '0' NOT NULL ,
value integer DEFAULT '0' NOT NULL ,
acknowledged integer DEFAULT '0' NOT NULL ,
ns integer DEFAULT '0' NOT NULL ,
value_changed integer DEFAULT '0' NOT NULL ,
PRIMARY KEY (eventid)
) ENGINE=InnoDB;
CREATE INDEX events_1 ON events ( object ,objectid,eventid);
CREATE INDEX events_2 ON events (clock,eventid);

* This source code was highlighted with Source Code Highlighter .

EXPLAIN
SELECT eventid,clock, value
FROM events
WHERE objectid=17131
AND object =0
AND clock>=1325635327
ORDER BY clock DESC , eventid DESC ;

* This source code was highlighted with Source Code Highlighter .

+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | events | ref | events_1,events_2 | events_1 | 12 | const,const | 113056 | Using where; Using filesort |
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+

उदाहरण के लिए, MySQL ने माना कि इवेंट 1 इंडेक्स में WHERE का उपयोग करना अधिक लाभदायक है, यह समझ में आता है, क्योंकि WHERE दो प्रमुख फ़ील्ड का उपयोग करता है, लेकिन यह ध्यान में नहीं आया कि परिणाम में 100k पंक्तियाँ हैं और उन्हें क्रमबद्ध किया जाना चाहिए।
इस स्थिति में, क्वेरी में चयनित MySQL सूचकांक के पहले क्षेत्र के मापदंड को बदलना:

EXPLAIN
SELECT eventid,clock, value
FROM events
WHERE objectid=17131
AND object +0=0
AND clock>=1325635327
ORDER BY clock DESC , eventid DESC ;

* This source code was highlighted with Source Code Highlighter .

+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | events | range | events_2 | events_2 | 4 | NULL | 113056 | Using where |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+

MySQL संशोधित फ़ील्ड मानों पर अनुक्रमित का उपयोग नहीं कर सकता है, इस मामले में, अंकगणित असाइनमेंट का उपयोग करके, हम ईवेंट 2 इंडेक्स के उपयोग को मजबूर करते हैं, जो डेटा चयन और सॉर्टिंग दोनों के लिए उपयुक्त है, जैसा कि EXPLAIN से देखा गया है।

कृपया ध्यान दें कि MySQL इंडेक्स द्वारा सॉर्ट नहीं किया जा सकता है यदि उपयोग किए गए फ़ील्ड एक अलग क्रम में सॉर्ट किए जाते हैं:

EXPLAIN
SELECT eventid,clock, value
FROM events
WHERE objectid=17131
AND object +0=0
AND clock>=1325635327
ORDER BY clock ASC , eventid DESC ;

* This source code was highlighted with Source Code Highlighter .

+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | events | range | events_2 | events_2 | 4 | NULL | 113056 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+

इसके अलावा, यदि इंडेक्स की तुलना में एक अलग क्रम में फ़ील्ड्स पर सॉर्टिंग होती है, तो इंडेक्स का उपयोग नहीं किया जाता है। सामान्य तौर पर, MySQL एक बी-ट्री के रूप में अनुक्रमित करता है, इसलिए सूचकांक के मध्य या अंत में फ़ील्ड का उपयोग करना विफल हो जाएगा।

यदि चयन और छंटाई के लिए एक सूचकांक का उपयोग करना असंभव है, तो ऐसा हुआ कि MySQL ने सही मूल्यांकन नहीं किया और छंटाई के लिए अनुक्रमित को चुना। ऐसा तब होता है जब तालिका में एक निश्चित संख्या में रिकॉर्ड से गुजरते हुए, जब यह MySQL के लिए चयन के लिए सूचकांक का उपयोग करने के लिए अधिक लाभदायक हो जाता है, और छंटाई या इसके विपरीत के लिए नहीं। इस तरह के क्षणों का पूर्वाभास करना मुश्किल होता है, और वे अलग-अलग संख्याओं के रिकॉर्ड पर समान प्रश्नों की जांच करके प्रकाश में आते हैं।

पहले अनुरोध पर वापस। मेरी राय में, निष्पादन योग्य स्क्रिप्ट के माध्यम से छांटना बेहतर है, निश्चित रूप से, यदि चयन हजारों लाइनों के भीतर है।
सबसे पहले, इस मामले में MySQL को तालिका से पूर्ण चयन करने की आवश्यकता नहीं है, लेकिन LIMIT तक पहुंचने पर रुक जाता है;
दूसरे, शायद ही कभी जब आपको वास्तव में एक ही समय में अधिक जानकारी प्रदर्शित करने की आवश्यकता होती है, जब तक कि यह रिपोर्ट न हो;
तीसरा, बहुत सारी जानकारी वाले पृष्ठ धीमे होंगे, भले ही यह 1000-विकल्प के साथ सिर्फ एक ड्रॉप-डाउन सूची हो, यह अब उपयोग करने योग्य नहीं है;
चौथा, MySQL में कोई प्राकृतिक छँटाई नहीं है;

लेकिन वास्तविक उदाहरणों में, यह शायद ही कभी संभव है, क्योंकि आपको उपयोगकर्ताओं को सॉर्ट करने की क्षमता देनी होगी, और यहां तक ​​कि MySQL के सॉर्टिंग को निर्दिष्ट किए बिना समान चयन की गारंटी नहीं है। वैकल्पिक रूप से, आप पहले फ़ील्ड को इंडेक्स में डाल सकते हैं, जिसके द्वारा छंटनी अधिक बार होती है, जिससे MySQL खोज और सॉर्टिंग दोनों के लिए एक इंडेक्स का उपयोग करेगा।

COUNT, बहुत से लोग पेजिंग के लिए दूसरा अनुरोध करते हैं, लेकिन वही Google, हालांकि यह कहता है कि लाखों + मैच पाए गए थे, वास्तव में आपको पहले हजार के बारे में बताएंगे और वह यह है। इसके अलावा, अंतिम पृष्ठ पर यह सूचित करेगा कि वास्तव में कम पाया गया है। इसलिए, 1001 लाइन का चयन करने के बाद, हम बस उपयोगकर्ता को बताते हैं कि 1000+ मैच पाए जाते हैं और इस स्तर पर अधिक चुनने की आवश्यकता नहीं है। जब उपयोगकर्ता अधिक कोड का अनुरोध करता है, तो हम पहले पृष्ठ पर चरण दर चरण चुनेंगे। आवश्यकता से अधिक 1 लाइन की जाँच करना।

सूचकांकों। तालिकाओं के प्रश्नों के रूप में, इन तालिकाओं के सूचक स्मृति में कैश किए जाते हैं और तब तक बने रहते हैं जब तक कि स्मृति समाप्त नहीं हो जाती है और फिर उन्हें बाहर निकलने के लिए कहा जाता है। इसलिए, यदि आपके पास गीगाबाइट की जानकारी है, तो उन अनुक्रमितों की संख्या के आधार पर, अनुक्रमित percent 40% प्रतिशत स्थान पर कब्जा कर लेंगे। उदाहरण के लिए, आपके पास MySQL के लिए आवंटित 16GB RAM वाला एक मामूली सर्वर है। जब 10GB + के वजन वाले सूचकांक के साथ एक तालिका को क्वेरी करते हैं, तो MySQL के लिए आवंटित सभी मेमोरी को मुक्त कर दिया जाएगा और इस सूचकांक के साथ भर दिया जाएगा, और पिछले सभी कैश्ड इंडेक्स गुमनामी में फेंक दिए जाएंगे। इस प्रकार, एक भारी अनुरोध करते हुए, आप सर्वर के संपूर्ण प्रदर्शन को मार सकते हैं। क्या करें? कई विकल्प हैं, लेकिन मैं यह नहीं कहूंगा कि वे सरल हैं और अलग-अलग संरक्षकों में बड़े> 10 मिलियन + तालिकाओं को संग्रहीत करने के लिए अभिन्न हैं, उदाहरण के लिए BigTable , NoSQL या MySQL के लिए NoSQL भी।

अभी के लिए बस इतना ही। मुझे आपके फैसले और उपरोक्त सलाह को सुनकर खुशी होगी।