🤾🏽 👧 👠 क्या प्रश्न QUERY CACHE से टकराते हैं 🛀🏻 🏴󠁧󠁢󠁳󠁣󠁴󠁿 📙

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

यह पता लगाने का कोई तरीका नहीं है कि अब प्रश्न क्या हैं

यानी यह विधि मौजूद नहीं है। केवल कुछ समय के बाद मुझे एहसास हुआ कि यह बिल्कुल भी नहीं है, और स्रोत कोड को पैच किए बिना इस डेटाबेस संरचना की सामग्री प्राप्त करने के लिए पूरी तरह से कानूनी तरीका है। यह तरीका INFORMATION_SCHEMA लिए एक छोटा सा प्लगइन लिखना है।

सामान्य क्वेरी कैश जानकारी

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

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

Query_cache_query

 struct Query_cache_query { ulonglong limit_found_rows; ... inline uchar* query() ... };

- SQL क्वेरी और इसके निष्पादन के परिणामस्वरूप प्राप्त रिकॉर्ड की संख्या

यह केवल वहाँ से बाहर खींचने के लिए बनी हुई है।

प्लगइन्स के बारे में सामान्य जानकारी

जैसा कि मैंने कहा: इस जानकारी को प्राप्त करने के लिए, हम आधिकारिक तौर पर उपलब्ध MySQL फ्रेमवर्क - INFORMATION_SCHEMA Plugins उपयोग करेंगे। QUERY CACHE ढांचे के साथ काम करने का पहला विवरण Roland Bouman ने अपने ब्लॉग पर बनाया था। उनका लेख, काफी पुराना, दुर्भाग्य से इस बिंदु तक नहीं बचा है। उन लोगों के लिए जो प्लगइन्स लिखने के लिए नए हैं, मैं हब पर इस पोस्ट को पढ़ने की सलाह देता हूं, MySQL के प्रत्यक्ष डेवलपर से पेट्रोपावेल या एक ही लेखक की भागीदारी के साथ लिखी गई पुस्तक MySQL 5.1 Plugin Development । इस लेख में मैं MySQL 5.5.29 के तहत इस विशेष प्लगइन के कार्यान्वयन के विवरण पर अधिक ध्यान देने की कोशिश करूंगा - फिलहाल सर्वर का नवीनतम संस्करण। INFORMATION_SCHEMA लिए न्यूनतम प्लगइन में शामिल होना चाहिए:
- ST_FIELD_INFO - तालिका संरचना का वर्णन
- mysql_declare_plugin - प्लगइन लाइब्रेरी डिस्क्रिप्टर मैक्रो प्रकार के साथ MYSQL_INFORMATION_SCHEMA_PLUGIN और परिणाम तालिका का नाम निर्दिष्ट करना
- st_mysql_information_schema - स्वयं प्लगइन का वर्णनकर्ता संरचना
- प्लगइन इनिशियलाइज़ेशन फ़ंक्शन, जो परिणामी तालिका भरने की प्रक्रिया को इंगित करता है
- और डी-आरंभीकरण समारोह।
सुविधा के लिए, हम एक प्लगइन लिखेंगे जो दो तालिकाओं को भरता है: कैश में प्रश्नों की एक सूची और उनके द्वारा कब्जा की गई QUERY_CACHE_RESULTS मेमोरी, और डेटाबेस तालिकाओं की एक सूची जिसके द्वारा QUERY_CACHE_TABLES प्रश्नों को QUERY_CACHE_TABLES । कोड को सरल बनाने के लिए, हम प्लगइन से सत्र चर के प्रबंधन को बाहर करते हैं, विधेय और अन्य उपयोगी को धक्का देते हैं, लेकिन हमारे लिए बहुत महत्वपूर्ण विशेषताएं नहीं हैं जिन्हें बाद में जोड़ा जा सकता है।

हम अनुरोध कैश कनेक्ट करते हैं

QUERY CACHE MySQL सर्वर का कार्यान्वयन Query_cache::query_cache का एक उदाहरण है। यह ऑब्जेक्ट वैश्विक है: इसका कार्यान्वयन sql_cache.cc । इस कैश को MySQL के बॉल्स में वर्णित किया गया है, इसलिए इस संरचना में हमारे प्लगइन को जोड़ने की संभावना के लिए, हमें निर्देश के साथ संकलन करना होगा

 #define MYSQL_SERVER

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

निजी वर्ग चर तक पहुंचने के लिए कुछ नए तरीके जोड़ें

 class MySQL_IS_Query_Cache : private Query_cache { public: HASH *get_queries_hash() { return &this->queries; } HASH *get_tables_hash() { return &this->tables; } };

- अब हम सीधे अपने प्लगइन में इन हैश टेबल के साथ काम कर सकते हैं।

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

QUERY_CACHE_TABLES

यह दृश्य उन सभी तालिकाओं की एक सूची प्रदर्शित करेगा जिनके क्वेरी परिणाम क्रमशः कैश किए गए हैं, इसमें केवल दो कॉलम होंगे:

स्कीमा नाम और तालिका नाम

 #define MAX_SCHEMA_NAME_LENGTH 127 #define MAX_TABLE_NAME_LENGTH 127 #define COLUMN_SCHEMA_NAME 0 #define COLUMN_TABLE_NAME 1 ST_FIELD_INFO query_cache_table_fields[]= { {"SCHEMA_NAME", MAX_SCHEMA_NAME_LENGTH, MYSQL_TYPE_STRING, 0, 0, "Schema Name"}, {"TABLE_NAME", MAX_TABLE_NAME_LENGTH, MYSQL_TYPE_STRING, 0, 0, "Table Name"}, {0,0, MYSQL_TYPE_STRING, 0, 0, 0} };

- डिफ़ॉल्ट रूप से, हम इन तत्वों की अधिकतम लंबाई 127 वर्णों से अधिक नहीं रखेंगे, मुझे लगता है कि यह उचित है

QUERY_CACHE_RESULTS

SQL क्वेरीज़ के साथ-साथ उनके निष्पादन के परिणाम की जानकारी वाली तालिका में, हम पाँच कॉलम जोड़ते हैं:

सीधे एसक्यूएल, प्रतिक्रिया में लाइनों की संख्या, साथ ही प्रतिक्रिया द्वारा उपयोग की जाने वाली मेमोरी के बारे में जानकारी

 #define MAX_STATEMENT_TEXT_LENGTH 1024 #define COLUMN_STATEMENT_TEXT 0 #define COLUMN_FOUND_ROWS 1 #define COLUMN_RESULT_BLOCKS_COUNT 2 #define COLUMN_RESULT_BLOCKS_SIZE 3 #define COLUMN_RESULT_BLOCKS_SIZE_USED 4 ST_FIELD_INFO query_cache_result_fields[]= { {"STATEMENT_TEXT", MAX_STATEMENT_TEXT_LENGTH,MYSQL_TYPE_STRING, 0, 0, "Cached statement text"}, {"FOUND_ROWS", 21, MYSQL_TYPE_LONGLONG, 0, 0, "Result row count"}, {"RESULT_BLOCKS_COUNT", 21, MYSQL_TYPE_LONG, 0, 0, "Result Blocks count"}, {"RESULT_BLOCKS_SIZE", 21, MYSQL_TYPE_LONGLONG, 0, 0,"Result Blocks size"}, {"RESULT_BLOCKS_SIZE_USED", 21, MYSQL_TYPE_LONGLONG, 0, 0,"Result Blocks used size"}, {0,0, MYSQL_TYPE_STRING, 0, 0, 0} };

- क्वेरी टेक्स्ट कृत्रिम रूप से 1024 वर्णों तक सीमित है, यदि आवश्यक हो तो आप इस पैरामीटर को बढ़ा सकते हैं

डेटा सहेजें

उसके बाद, हम डेटा के साथ प्राप्त तालिका के प्रत्यक्ष भरने के लिए आगे बढ़ते हैं। जहां भी हम स्ट्रिंग मान संग्रहीत करते हैं, एक एन्कोडिंग की आवश्यकता होती है।

  // character set information to store varchar values CHARSET_INFO *cs = system_charset_info;

Query_cache::query_cache तक पहुंचने के लिए Query_cache::query_cache आपको होना चाहिए

उपयुक्त लॉक सेट करें

  // query_cache defined in sql_cache.h is MySQL Query Cache implementation; MySQL_IS_Query_Cache *qc = (MySQL_IS_Query_Cache *)&query_cache; HASH *h_queries; query_cache.lock(); h_queries = qc->get_queries_hash();

- वह वस्तु जिसे हम खोज रहे हैं, उसे पुन: प्राप्त करता है, जिसे हम QUERY CACHE से सभी प्रश्नों तक पहुँच सकते हैं (चित्र HASH queries )

HASH tables डेटा संरचना तक पहुंच प्राप्त की जा सकती है

इसी तरह से

  // query_cache defined in sql_cache.h is MySQL Query Cache implementation; MySQL_IS_Query_Cache *qc = (MySQL_IS_Query_Cache *)&query_cache; HASH *h_tables; query_cache.lock(); h_tables = qc->get_tables_hash();

इसके अलावा HASH tables साथ सब कुछ सरल है। परिणामी हैश तालिका और Iterate

उन तत्वों को सहेजें जिनकी हमें आवश्यकता है

  for(uint i = 0; i < h_tables->records; i++) { query_cache_block_hash = my_hash_element(h_tables, i); query_cache_block_current = (Query_cache_block*)query_cache_block_hash; Query_cache_table* query_cache_table = query_cache_block_current->table(); // get tables data const char *schema_name = (const char*)query_cache_table->db(); size_t schema_name_length = strlen(schema_name)>MAX_SCHEMA_NAME_LENGTH?MAX_SCHEMA_NAME_LENGTH:strlen(schema_name); is_query_cache_tables->field[COLUMN_SCHEMA_NAME]->store((char*)schema_name, schema_name_length, cs); ... }

बचत के दौरान त्रुटियों के मामले में, साथ ही प्रक्रिया के नियमित समापन के मामले में, query_cache.unlock() लॉक को हटाना न भूलें। यह लॉक पूरे डेटाबेस के उदाहरण के लिए वैश्विक है; जब इसे स्थापित किया जाता है, तो QUERY CACHE तक पहुंचने वाले अन्य सभी प्रश्न आपके प्लगइन के काम करने का इंतजार करेंगे।
एसक्यूएल प्रश्नों का पाठ प्राप्त करना थोड़ा अधिक जटिल है, और अतिरिक्त जोड़तोड़ की आवश्यकता है। हैश तालिका के पुनरावृत्ति के परिणामस्वरूप प्राप्त ब्लॉक में वह जानकारी नहीं है जो हमें चाहिए। इसमें केवल Query_cache_query प्रकार की संरचनाओं का लिंक होता है, जिस तक पहुंचने पर हमें केवल आवश्यक जानकारी का एक हिस्सा मिलेगा,

परिणाम सेट में क्वेरी पाठ और रिकॉर्ड की संख्या

  // get statement data statement_text = (const char*)query_cache_query->query(); ulonglong found_rows = query_cache_query->found_rows();

इस क्वेरी के परिणामों को कैश करने के लिए उपयोग की जाने वाली मेमोरी की जानकारी, क्वेरी चर के लिए result चर के माध्यम से उपलब्ध है। यह एक लिंक होगा

पहला मेमोरी क्षेत्र

  Query_cache_block *first_result_block = query_cache_query->result();

इसके परिणाम को संग्रहीत करने के लिए आवंटित किया गया। सभी आवंटित मेमोरी अनुभागों के बारे में जानकारी प्राप्त करने के लिए, आपको लिंक की गई सूची से गुजरना होगा, जो चक्रीय है और अंततः हमें पहले Query_cache_block block में ले Query_cache_block । यह भी विचार करने योग्य है कि इस कॉल के परिणामस्वरूप प्राप्त ब्लॉक की स्थिति असंबंधित Query_cache_block::RES_INCOMPLETE हो सकती है।

क्रॉल परिणाम

  // loop all query result blocks for current query while( (result_block= result_block->next) != first_result_block && result_block->type != Query_cache_block::RES_INCOMPLETE) /* This type of block can be not lincked yet (in multithread environment)*/ { result_blocks_count++; result_blocks_size += result_block->length; result_blocks_size_used += result_block->used; }

- वास्तव में, क्या हम इस तरह से एक असंबद्ध ब्लॉक में जा सकते हैं या नहीं, मुझे नहीं पता, लेकिन यह सुरक्षित खेलना बेहतर है

हम सर्वर को इकट्ठा करते हैं और परिणाम का आनंद लेते हैं

 install plugin QUERY_CACHE_RESULTS soname 'query_cache_results.so'; install plugin QUERY_CACHE_TABLES soname 'query_cache_tables.so'; show plugins; +--------------------------+--------+--------------------+------------------------+---------+ | Name | Status | Type | Library | License | +--------------------------+--------+--------------------+------------------------+---------+ ... | QUERY_CACHE_RESULTS | ACTIVE | INFORMATION SCHEMA | query_cache_results.so | GPL | | QUERY_CACHE_TABLES | ACTIVE | INFORMATION SCHEMA | query_cache_tables.so | GPL | +--------------------------+--------+--------------------+------------------------+---------+ 24 rows in set (0.00 sec) select * from pivot limit 10; ... 10 rows in set (0.00 sec) select * from pivot p1, pivot p2 limit 1000; ... 1000 rows in set (0.00 sec) select * from information_schema.query_cache_tables; +-------------+------------+ | SCHEMA_NAME | TABLE_NAME | +-------------+------------+ | test | pivot | +-------------+------------+ 1 row in set (0.00 sec) select * from information_schema.query_cache_results \G *************************** 1. row *************************** STATEMENT_TEXT: select * from pivot p1, pivot p2 limit 1000 FOUND_ROWS: 1000 RESULT_BLOCKS_COUNT: 1 RESULT_BLOCKS_SIZE: 9992 RESULT_BLOCKS_SIZE_USED: 9985 *************************** 2. row *************************** STATEMENT_TEXT: select * from pivot limit 10 FOUND_ROWS: 10 RESULT_BLOCKS_COUNT: 1 RESULT_BLOCKS_SIZE: 512 RESULT_BLOCKS_SIZE_USED: 208 2 rows in set (0.00 sec)

PS चूंकि मैं एक डेटाबेस डेवलपर हूं, और C ++ में राइटिंग कोड भी मेरी साइड विशेषता नहीं है, इसलिए वर्णित समाधान काफी टेढ़ा और गैर-इष्टतम हो सकता है, इस कारण से मुझे पीएम में कोड पर आपकी टिप्पणी सुनने में खुशी होगी।

PPS स्व-असेंबली और प्रयोग के लिए, मैं github पर परिणामी प्लगइन के स्रोत कोड को टिप्पणी में उपयोगकर्ताओं की सलाह पर पोस्ट करता हूं: github.com/mcshadow/mysql_query_cache