👩🏿‍🎤 🤵🏽 🙌🏻 MySQL में बहुत बड़ी तालिकाएँ 🙅🏽 🤟🏻 🔕

यदि आपके प्रोजेक्ट में टेबल हैं, जिनका आकार गीगाबाइट में गणना किया गया है, और ऐसी तालिका की संरचना को बदलने के लिए, आपको कई घंटों के लिए सभी सेवाओं को रोकना होगा - यह लेख आपके लिए होगा।

दिया गया: डेटा के कई दस गीगाबाइट्स को मापने वाली एक तालिका। कार्य तालिका की संरचना को बदलना है।

मैं सीधे आगे चला जाऊंगा, यह विधि केवल ट्रांसेक्शनल टेबल पर काम करेगी। यदि आपके पास दसियों गीगाबाइट्स MyISAM तालिका है, तो यहां मज़ाक है - "अपनी समस्याओं से स्वयं निपटें"। एक InnoDB तालिका के लिए एक उदाहरण दिया जाएगा।

मान लीजिए कि हमारी तालिका की संरचना इस प्रकार है:

CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT '', `password_hash` char(32) NOT NULL DEFAULT '', `registration_date` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

हम इस तालिका में last_login फ़ील्ड जोड़ना चाहते हैं।

हमारे पास क्या विकल्प हैं?

माथा

 ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;

विकल्प छोटी परियोजनाओं पर बहुत अच्छा काम करता है जहां तालिकाओं का आकार शायद ही कभी 50,000 रिकॉर्ड से अधिक होता है। विकल्प हमें शोभा नहीं देता। ALTER को बहुत लंबे समय तक क्रियान्वित किया जाएगा और इस बार तालिका को लिखने और पढ़ने दोनों के लिए बंद कर दिया जाएगा। तदनुसार, इस समय के लिए सेवा को रोकना होगा।

दिमाग चालू करो

आप उस मामले के लिए तालिका को बिल्कुल भी नहीं छू सकते हैं, लेकिन एक अलग `users_lastvisits` बनाएं:

 CREATE TABLE `users_lastvisits` ( `user_id` int(11) NOT NULL, `last_login` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

अब यह उन सभी प्रश्नों में संभव है, जहां last_login को last_login तालिका के साथ JOIN करने की आवश्यकता है। यह काम करेगा, ज़ाहिर है, अधिक धीरे-धीरे, और जोइन को जोड़ने के अनुरोधों में भी बहुत समय है, लेकिन सामान्य तौर पर यह कभी-कभी पर्याप्त होता है और आप इस बिंदु पर रुक सकते हैं।

और फिर भी - आपको एक फ़ील्ड जोड़ने की आवश्यकता है

आप मास्टर-दास प्रतिकृति बढ़ा सकते हैं, गुलाम सर्वर पर अलर्ट बना सकते हैं, और फिर उन्हें स्वैप कर सकते हैं। ईमानदार होने के लिए, मैंने ऐसा कभी नहीं किया है, यह अगली विधि की तुलना में आसान हो सकता है, लेकिन प्रतिकृति बढ़ाना हमेशा संभव नहीं होता है।

मेरा तरीका इस प्रकार है

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

तो, हम 2 टेबल तैयार कर रहे हैं - पहला वांछित संरचना के साथ, दूसरा लॉगिंग परिवर्तन के लिए।

 CREATE TABLE `_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT '', `password_hash` char(32) NOT NULL DEFAULT '', `registration_date` int(11) NOT NULL DEFAULT '0', `lastvisit` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` ( `id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT '0', `action` enum('updated','deleted') NOT NULL DEFAULT 'updated', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ट्रिगर रखो:

 DELIMITER ;; CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted'); END;; CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated'); END;; CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF (OLD.id != NEW.id) THEN INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted'); END IF; INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated'); END;; DELIMITER ;

अब हम अतिप्रवाह शुरू करते हैं। ऐसा करने के लिए, डेटाबेस के लिए 2 कनेक्शन खोलें। एक में, आधान पर्याप्त रूप से चला जाएगा, दूसरे में लेखन के लिए तालिका को अस्थायी रूप से लॉक करना आवश्यक होगा।

 mysql> LOCK TABLES users WRITE; Query OK, 0 rows affected (0.00 sec) mysql> --      mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.17 sec) mysql> --      mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users; mysql> --     mysql> UNLOCK TABLES;

यह अब है, जबकि तालिका अतिप्रवाह कर रही है, हमारे पास यह सोचने का समय है कि हम उस डेटा में कैसे डालेंगे जो अतिप्रवाह की शुरुआत के बाद से बदल गया है। इसमें कुछ भी जटिल नहीं है - मैं स्क्रिप्ट नहीं दूंगा, आपको बस यूजर्स से एक रिकॉर्ड लेने की जरूरत है_अपडेट किया गया तालिका जिसमें वे जोड़े गए थे (प्राथमिक कुंजी द्वारा सॉर्ट) और इसे _users तालिका में अपडेट करें या हटाएं;

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

 mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.16 sec) mysql> RENAME TABLE users TO __users, _users TO users; Query OK, 0 rows affected (0.11 sec)

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

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

 mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;

mysqldump के माध्यम से अतिप्रवाह करने की आवश्यकता:

 $ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2

इस तरह, मैं सेवाओं को किसी अन्य सर्वर पर रोक के बिना लगभग 12 घंटे में 60Gb और 400 मिलियन पंक्तियों की एक तालिका स्थानांतरित करने में सक्षम था।

वैसे, साइकिल का आविष्कार पहले ही फेसबुक द्वारा किया जा चुका है और इसे MySQL के लिए ऑनलाइन स्कीमा चेंज कहा जाता है।

MySQL में बहुत बड़ी तालिकाएँ

माथा

दिमाग चालू करो

और फिर भी - आपको एक फ़ील्ड जोड़ने की आवश्यकता है

मेरा तरीका इस प्रकार है

More articles: