INSERT ... आपातकालीन स्थिति में DUPLICATE प्रमुख अद्यतन व्यवहार

कुछ हफ़्ते पहले, मैं एक क्लाइंट समस्या पर काम कर रहा था, जिसमें डेटाबेस प्रदर्शन और यहां तक ​​कि इसकी विफलताओं का भी सामना करना पड़ा, जो लगभग हर 4 सप्ताह में हुआ। पर्यावरण में कुछ खास नहीं, हार्डवेयर या पूछताछ में। संक्षेप में, अधिकांश डेटाबेस एक एकल तालिका थी जिसमें, अन्य चीजों के अलावा, INT AUTO_INCREMENT PRIMARY KEY और UNIQUE KEY मौजूद थीं।

इस तालिका के साथ काम करने वाले प्रश्न लगभग सभी प्रकार के INSERT ... ON DUPLICATE KEY UPDATE (बाद INSERT ODKU ), जहाँ INSERT में सूचीबद्ध कॉलम UNIQUE KEY साथ कॉलम के अनुरूप थे। और उन्हें प्रति दिन लगभग 24 घंटे लगातार 1500-2000 अनुरोधों की आवृत्ति के साथ निष्पादित किया गया था। यदि आप गणित में अच्छे हैं, तो आप शायद पहले से ही अनुमान लगा चुके हैं कि मामला क्या था।

चर्चा के उद्देश्यों के लिए, हम निम्नलिखित तालिका का उपयोग स्थिति के चित्रण के रूप में करेंगे:

 CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; 

अब घटनाओं के निम्नलिखित अनुक्रम की कल्पना करें:

 (root@localhost) [test]> INSERT INTO update_test (username, host_id, last_modified) VALUES ('foo',3,NOW()); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> select * from update_test; +----+----------+---------+---------------------+ | id | username | host_id | last_modified | +----+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:36:30 | +----+----------+---------+---------------------+ 

सामान्य से बाहर कुछ भी नहीं, है ना? हमने एक पंक्ति को एक खाली तालिका में डाला और यदि हम SHOW CREATE TABLE , तो हम देखेंगे कि AUTO_INCREMENT काउंटर का मान अभी 2 । यदि हम इस तालिका में एक INSERT ODKU करते हैं, तो हम निम्नलिखित देखेंगे:

 (root@localhost) [test]> insert into update_test (username,host_id) values ('foo',1) on duplicate key update last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) (root@localhost) [test]> select * from update_test; +----+----------+---------+---------------------+ | id | username | host_id | last_modified | +----+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | +----+----------+---------+---------------------+ 1 row in set (0.00 sec) 

और अब, भले ही हमने नई पंक्ति न AUTO_INCREMENT हो, हमारा AUTO_INCREMENT काउंटर 3 हो गया है। यह वास्तव में, अपेक्षित व्यवहार है। InnoDB उन बाधाओं की जाँच करता है जिसमें वे परिभाषित किए गए थे, और PRIMARY KEY हमेशा पहले जाती है। इसलिए, MySQL हमारे INSERT जाँच करता है, देखता है कि अगला AUTO_INCREMENT मूल्य उपलब्ध है और इसका उपयोग करता है, लेकिन फिर यह UNIQUE KEY जाँच करता है और उल्लंघन पाता है, इसलिए INSERT बजाय यह UPDATE करता है। यदि हम handler status काउंटरों को देखते हैं, तो हम देख सकते हैं कि एक डालने का अनुरोध विफल हो गया था, और एक अद्यतन अनुरोध सफल हुआ (यह बताता है कि 2 पंक्तियों को क्यों बदला गया था, 1 नहीं)।

 (root@localhost) [test]> show status like 'handler%'; *** some rows omitted *** +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_rollback | 0 | | Handler_update | 1 | | Handler_write | 1 | +----------------------------+-------+ 

इस बिंदु पर आप सोच सकते हैं, "तो क्या?" आइए अपने ग्राहक के पास वापस जाएं। 1,500 INSERT ODKU प्रति सेकंड, लगातार 24 घंटे। उनकी तालिका का PRIMARY KEY वही है जैसा मैंने डेमो तालिका में उपयोग किया है - INT UNSIGNED । हम गिनते हैं। INT UNSIGNED का अधिकतम मूल्य 4294967295 । हम इसे प्रति सेकंड 1500 अनुरोधों से विभाजित करते हैं और 86400 से विभाजित करते हैं, जो एक दिन में सेकंड की संख्या है, और हमें 33.1 दिन, या 4 सप्ताह से थोड़ा अधिक मिलता है। संयोग? मुझे ऐसा नहीं लगता। तो जब हम अर्थ से परे जाते हैं तो वास्तव में क्या होता है? कुछ व्यवहार आपको आश्चर्यचकित कर सकते हैं। आइए अपनी डेमो टेबल पर वापस जाएं और इसमें AUTO_INCREMENT कॉलम के लिए अधिकतम मान के साथ एक पंक्ति डालें, और फिर एक डालें।

 (root@localhost) [test]> insert into update_test (id,username,host_id) values (4294967295, 'bar', 10); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> flush status; (root@localhost) [test]> insert into update_test (username,host_id) values ('baz', 10); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY' (root@localhost) [test]> show status like 'handler%'; *** some rows omitted *** +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_rollback | 1 | | Handler_write | 1 | +----------------------------+-------+ 

इसलिए, हमने एक पंक्ति सम्मिलित करने का प्रयास किया और यह काम नहीं आया, क्योंकि AUTO_INCREMENT पहले से ही अधिकतम मूल्य था और अनुरोध विफल रहा। लेकिन, अगर हम INSERT ODKU करने की कोशिश करते हैं तो क्या होता है? सबसे पहले, आइए देखें कि हमारे पास तालिका में क्या है:

 (root@localhost) [test]> select * from update_test; +------------+----------+---------+---------------------+ | id | username | host_id | last_modified | +------------+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | | 4294967295 | bar | 10 | NULL | +------------+----------+---------+---------------------+ 2 rows in set (0.00 sec) (root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES ('foo', 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) 

ठीक लग रहा है, है ना? 2 पंक्तियों को बदल दिया गया है, यह स्पष्ट है कि एक श्रृंखला के लिए जो username = "foo" स्थिति से host_id , host_id और last_modified अपडेट किए गए, और हम आनन्दित हो सकते हैं। दुर्भाग्य से, यह मामला नहीं है:

 (root@localhost) [test]> select * from update_test; +------------+----------+---------+---------------------+ | id | username | host_id | last_modified | +------------+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | | 4294967295 | bar | 7 | 2012-10-05 23:24:49 | +------------+----------+---------+---------------------+ 2 rows in set (0.00 sec) 

ओह, अंतिम पंक्ति को अपडेट किया गया था, जिसकी id हमारे AUTO_INCREMENT के अधिकतम मूल्य के बराबर है, और username कॉलम पर UNIQUE KEY की अनदेखी की गई थी।

अब हम उस क्लाइंट की समस्या को आसानी से समझ सकते हैं जिसका डेटाबेस इस पद के लिए प्रेरणा का काम करता है। 1500 प्रति सेकंड अनुरोध, एक ही पंक्ति को ब्लॉक करने और अपडेट करने की कोशिश करने से कुछ भी अच्छा नहीं होगा। बेशक, एक सरल समाधान है - INT से BIGINT तक AUTO_INCREMENT कॉलम के डेटा प्रकार को बदलें।

यह पता चला है कि यह व्यवहार प्रलेखित है । मैनुअल कहता है कि कई अद्वितीय अनुक्रमित के साथ एक टेबल पर हमारा INSERT ODKU UPDATE update_test SET host_id = 7, last_modified = NOW() WHERE id = 4294967295 OR username = "foo" LIMIT 1 और निश्चित रूप से अनुकूलक PRIMARY बजाय PRIMARY का चयन करेगा। UNIQUE ।

हमने यहां क्या सीखा है?

• AUTO_INCREMENT बहुत आसान है जितना यह लगता है। वास्तविक ग्राहक तालिका में 500k से कम पंक्तियाँ थीं।
• AUTO_INCREMENT लिए AUTO_INCREMENT प्रकारों का उपयोग करना लगभग हमेशा एक बुरा विचार है। आप उपलब्ध मानों की आधी श्रृंखला खो देते हैं।
• भौतिकी के नियमों की तरह अंतर्ज्ञान, अक्सर चरम स्थितियों में विफल रहता है।