MySQL प्रदर्शन अनुकूलन: डेटाटाइम फ़ील्ड द्वारा ऑर्डर




performance select (2)

MySQL में दो फाइलोर्ट एल्गोरिदम हैं: एक पुरानी फाइलोर्ट जो डिस्क पर रिकॉर्ड टाइप करती है, और एक नया संस्करण जो स्मृति में काम करता है।

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

MySQL is_active कॉलम का उपयोग करने का चयन कर रहा है, जाहिर है क्योंकि ऐसा लगता है कि कॉलम अन्य जोड़ों और कहां स्थितियों के साथ जारी रखने से पहले पंक्तियों को खत्म करने में सबसे चुनिंदा है। पहली बात यह है कि मैं सुझाव दूंगा कि post_date, feed_id, और कहां स्थितियों में कॉलम के साथ समग्र इंडेक्स बनाने का प्रयास करना होगा, उदाहरण के लिए (is_active, user_offtopic_count, post_date, feed_id)।

मेरे पास लगभग 100,000 ब्लॉग पोस्टिंग वाली एक टेबल है, जो एक फ़ील्ड से जुड़ी है जिसमें 50 फ़ीड्स के साथ 1: n रिलेशनशिप है। जब मैं एक चयन कथन के साथ दोनों टेबलों को क्वेरी करता हूं, तो पोस्टिंग टेबल के डेटाटाइम फ़ील्ड द्वारा आदेश दिया जाता है, MySQL हमेशा फाइलोर्ट का उपयोग करता है, जिसके परिणामस्वरूप बहुत धीमी क्वेरी समय (> 1 सेकंड) होता है। postings टेबल (सरलीकृत) की स्कीमा यहां दी गई है:

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| feed_id             | int(11)      | NO   | MUL | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| is_active           | tinyint(1)   | NO   | MUL | 0       |                |
| link                | varchar(255) | NO   | MUL | NULL    |                |
| author              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| excerpt             | text         | NO   |     | NULL    |                |
| long_excerpt        | text         | NO   |     | NULL    |                |
| user_offtopic_count | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

और यहां feed टेबल है:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| type        | int(11)      | NO   | MUL | 0       |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| website     | varchar(255) | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

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

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    (`postings`)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15  

इस क्वेरी पर explain extended कमांड के परिणाम से पता चलता है कि MySQL फाइलोर्ट का उपयोग कर रहा है:

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
| id | select_type | table    | type   | possible_keys                         | key       | key_len | ref                      | rows  | Extra                       |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
|  1 | SIMPLE      | postings | ref    | feed_id,is_active,user_offtopic_count | is_active | 1       | const                    | 30996 | Using where; Using filesort |
|  1 | SIMPLE      | feeds    | eq_ref | PRIMARY,type                          | PRIMARY   | 4       | feedian.postings.feed_id |     1 | Using where                 |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

जब मैं भाग से order by हटा देता हूं, तो MySQL फाइलॉर्ट का उपयोग बंद कर देता है। अगर आपको MySQL को सॉर्ट करने और इंडेक्स का उपयोग करके डेटा का चयन करने के लिए इस क्वेरी को अनुकूलित करने के बारे में कोई विचार है, तो कृपया मुझे बताएं। मैंने पहले से ही कुछ चीजों की कोशिश की है जैसे कि कुछ ब्लॉग पोस्टिंग द्वारा सुझाए गए अनुसार, फ़ील्ड द्वारा सभी ऑर्डर / ऑर्डर पर संयुक्त इंडेक्स बनाना, लेकिन यह काम नहीं करता है।


साथ ही, यह याद रखना महत्वपूर्ण है कि MySQL एक अनुक्रमणिका का उपयोग नहीं करेगा यदि आपके द्वारा ऑर्डर किए गए कॉलम पर एक फ़ंक्शन लागू होता है।

आपको postings.post_date को कुछ और के रूप में अलियासिंग करने का भी प्रयास करना चाहिए। यह MySQL को अनलर्टेड कॉलम द्वारा ऑर्डर करने के लिए बताएगा, और आप अभी भी यूनिक्स टाइमस्टैम्प का चयन करेंगे।







select