🍼 🔈 👨‍👩‍👦‍👦 MySQL (JSON) में लचीला डेटा संग्रहण 👨🏾‍🏭 👳🏿 👩🏻‍⚕️

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

प्रागितिहास

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

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

प्रस्तुति के दौरान, कई उदाहरण चलाए गए और लाइव खेले गए। पाठ की तुलना में अधिक संपूर्ण चित्र चाहते हैं, वीडियो देखने के लिए स्विच करें।

इसलिए, एक नोटबुक में मौसम टिप्पणियों को रिकॉर्ड नहीं करने के लिए, इंटरनेट चीजों के लिए बड़ी संख्या में डिवाइस हैं - रास्पबेरी पाई, नए रास्पबेरी पाई, अरुडिनो - हजारों विभिन्न प्लेटफार्मों। लेकिन मैंने इस प्रोजेक्ट के लिए पार्टिकल फोटॉन नामक एक उपकरण चुना। आधिकारिक वेबसाइट पर $ 19 की लागत का उपयोग करना बहुत आसान है।

कण फोटॉन के बारे में अच्छी बात यह है:

100% बादल समाधान;
किसी भी सेंसर उपयुक्त हैं, उदाहरण के लिए, Arduino के लिए। इन सबकी कीमत एक डॉलर से भी कम है।

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

इसके अलावा, मुझे न केवल रोशनी और इतने पर माप करने और उन्हें फोन पर स्थानांतरित करने की आवश्यकता है (जो वास्तव में अच्छा है - मैं वास्तविक समय में देख सकता हूं कि मेरे पास किस तरह की रोशनी है), लेकिन डेटा स्टोर करने के लिए भी। इसके लिए, स्वाभाविक रूप से, एक MySQL अनुभवी के रूप में, मैंने MySQL को चुना।

हम MySQL में डेटा कैसे लिखते हैं

मैंने एक जटिल योजना चुनी:

मुझे कण कंसोल से डेटा मिलता है;
मैं उन्हें MySQL में लिखने के लिए Node.js का उपयोग करता हूं।

मैं कण जेएस एपीआई का उपयोग कर रहा हूं, जिसे कण वेबसाइट से डाउनलोड किया जा सकता है। मैं MySQL के साथ एक संबंध स्थापित करता हूं और लिखता हूं, यानी मैं सिर्फ INSERT INTO वैल्यू करता हूं। ऐसी पाइपलाइन।

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

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

अब आइए MySQL और JSON के बारे में बात करते हैं, जो JSON के साथ MySQL 5.7 से MySQL 8 में काम कर रहा है। फिर मैं एक डेमो दिखाऊंगा जिसके लिए मैं MySQL 8 का उपयोग करता हूं (रिपोर्ट के समय यह संस्करण अभी तक उत्पादन के लिए तैयार नहीं था, एक स्थिर रिलीज पहले ही जारी की जा चुकी है)।

MySQL डेटा स्टोरेज

जब हम सेंसर से प्राप्त डेटा को संग्रहीत करने का प्रयास करते हैं, तो हमारा पहला विचार MySQL में एक तालिका बनाना है :

CREATE TABLE 'sensor_wide' ( 'id' int (11) NOT NULL AUTO_INCREMENT, 'light' int (11) DEFAULT NULL, 'temp' double DEFAULT NULL, 'humidity' double DEFAULT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB

यहां प्रत्येक सेंसर के लिए और प्रत्येक डेटा प्रकार के लिए एक कॉलम है: प्रकाश, तापमान, आर्द्रता।

यह काफी तार्किक है, लेकिन एक समस्या है - यह लचीला नहीं है । मान लीजिए हम एक और सेंसर जोड़ना चाहते हैं और कुछ और मापना चाहते हैं। उदाहरण के लिए, कुछ लोग एक केग में शेष बीयर को मापते हैं। इस मामले में क्या करना है?

 alter table sensor_wide add water level double ...;

तालिका में कुछ जोड़ने के लिए कैसे बिगाड़ना है? आपको एक परिवर्तन तालिका बनाने की आवश्यकता है, लेकिन यदि आपने MySQL में परिवर्तन तालिका बनाई है, तो आप जानते हैं कि मैं किस बारे में बात कर रहा हूं - यह पूरी तरह से मुश्किल है। MySQL 8 और MariaDB में अल्टर टेबल बहुत सरल है, लेकिन ऐतिहासिक रूप से यह एक बड़ी समस्या है। इसलिए अगर हमें बीयर के नाम के साथ, उदाहरण के लिए, एक कॉलम जोड़ने की आवश्यकता है, तो यह इतना सरल नहीं होगा।

फिर से, सेंसर दिखाई देते हैं, गायब हो जाते हैं, हमें पुराने डेटा के साथ क्या करना चाहिए? उदाहरण के लिए, हम प्रकाश व्यवस्था के बारे में जानकारी प्राप्त करना बंद कर देते हैं। या हम एक नया कॉलम बना रहे हैं - कैसे स्टोर करें जो पहले नहीं था? मानक दृष्टिकोण शून्य है, लेकिन विश्लेषण के लिए यह बहुत सुविधाजनक नहीं होगा।

एक अन्य विकल्प एक कुंजी / मूल्य की दुकान है।

MySQL डेटा भंडारण: कुंजी / मूल्य

यह अधिक लचीला होगा : कुंजी / मूल्य में एक नाम होगा, उदाहरण के लिए, तापमान और, तदनुसार, डेटा।

 CREATE TABLE 'cloud_data' ( 'id' int (11) NOT NULL AUTO_INCREMENT, 'name' varchar(255) DEFAULT NULL, 'data' text DEFAULT NULL, 'updated_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY ('id') ) ENGINE=InnoDB

इस मामले में, एक और समस्या दिखाई देती है - कोई प्रकार नहीं हैं । हमें नहीं पता कि हम 'डेटा' फील्ड में क्या स्टोर कर रहे हैं। हमें इसे एक पाठ क्षेत्र के रूप में घोषित करना होगा। जब मैं अपना इंटरनेट ऑफ थिंग्स डिवाइस बनाता हूं, तो मुझे पता होता है कि वहां किस तरह का सेंसर है और उसी के अनुसार टाइप किया जाता है, लेकिन अगर आपको किसी अन्य व्यक्ति के डेटा को उसी तालिका में संग्रहीत करने की आवश्यकता है, तो मुझे नहीं पता होगा कि क्या डेटा एकत्र किया जा रहा है।

आप कई तालिकाओं को संग्रहीत कर सकते हैं, लेकिन प्रत्येक सेंसर के लिए एक पूरी नई तालिका बनाना बहुत अच्छा नहीं है।

क्या किया जा सकता है? - JSON का उपयोग करें।

MySQL डेटा स्टोरेज: JSON

अच्छी खबर यह है कि MySQL 5.7 में आप JSON को एक क्षेत्र के रूप में संग्रहीत कर सकते हैं।

 CREATE TABLE 'cloud_data_json' ( 'id' int (11) NOT NULL AUTO_INCREMENT, 'name' varchar(255) DEFAULT NULL, 'data' JSON, 'updated_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY ('id') ) ENGINE=InnoDB;

MySQL 5.7 दिखाई देने से पहले, लोगों ने JSON को संग्रहीत भी किया, लेकिन एक पाठ क्षेत्र के रूप में। MySQL में JSON फ़ील्ड आपको JSON को सबसे अधिक कुशलता से संग्रहीत करने की अनुमति देता है। इसके अलावा, JSON के आधार पर, आप उनके आधार पर वर्चुअल कॉलम और इंडेक्स बना सकते हैं।

एकमात्र मामूली समस्या यह है कि भंडारण के दौरान तालिका आकार में बढ़ेगी । लेकिन तब हमें बहुत अधिक लचीलापन मिलता है।

JSON फ़ील्ड टेक्स्ट फ़ील्ड की तुलना में JSON को संग्रहीत करने के लिए बेहतर है क्योंकि:

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

JSON में डेटा संग्रहीत करने के लिए, हम बस SQL का उपयोग कर सकते हैं: एक INSERT बनाएं, वहां 'डेटा' डालें और डिवाइस से डेटा प्राप्त करें।

 … stream.on('event', function(data) { var query = connection.query( 'INSERT INTO cloud_data_json (client_name, data) VALUES (?, ?)', ['particle', JSON.stringify(data)] ) … (demo)

डेमो

प्रदर्शित करने के लिए ( यहां वीडियो में इसकी शुरुआत) उदाहरण एक वर्चुअल मशीन का उपयोग करता है जिसमें एसक्यूएल होता है।

नीचे कार्यक्रम का एक टुकड़ा है।

मैं INSERT INTO cloud_data (name, data) हूं, मुझे डेटा पहले से ही JSON फॉर्मेट में मिलता है, और मैं इसे सीधे MySQL को लिख सकता हूं, जैसा कि अंदर है, इस बारे में सोचे बिना।

जैसा कि यह निकला, इस क्लाउड का उपयोग करके, आप न केवल मेरे डिवाइस के डेटा का उपयोग कर सकते हैं, बल्कि आमतौर पर वे सभी डेटा जो इस पार्टिकल का उपयोग करते हैं। यह अब तक का काम लगता है। पूरी दुनिया में पार्टिकल फोटॉन का उपयोग करने वाले लोग कुछ डेटा भेज रहे हैं: गेराज का दरवाजा खुला है, या बीयर के बाकी हिस्से ऐसे हैं, या कुछ और। यह ज्ञात नहीं है कि ये डिवाइस कहां स्थित हैं, लेकिन ऐसे डेटा प्राप्त किए जा सकते हैं। अंतर केवल इतना है कि जब मुझे अपना डेटा मिलता है, तो मैं कुछ लिखता हूं जैसे: deviceId: 'mine' ।

जब हम कोड चलाते हैं, तो हमें किसी और के डिवाइस से कुछ डेटा की एक धारा मिलती है जो कुछ कर रही होती है।

हम पूरी तरह से नहीं जानते हैं कि यह डेटा क्या है: TTL, publish_at, coreid, door status (दरवाजा खुला), रिले पर।

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

कृपया, सब कुछ दर्ज किया गया है।

दस्तावेज़ की दुकान

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

MySQL 5.7 में दिखाई देने वाली एक और संभावना है: एक अलग प्रोटोकॉल, एक अलग पोर्ट का उपयोग करें, और दूसरे ड्राइवर की भी आवश्यकता है। Node.js (वास्तव में, किसी भी प्रोग्रामिंग भाषा - PHP, जावा, आदि के लिए) के लिए, हम एक अलग प्रोटोकॉल का उपयोग करके MySQL से जुड़ते हैं और JSON प्रारूप में डेटा स्थानांतरित कर सकते हैं। फिर, मुझे नहीं पता कि मेरे पास इस JSON में क्या है - दरवाजों के बारे में जानकारी या कुछ और, मैं सिर्फ MySQL में डेटा डंप करता हूं, और फिर हम इसका पता लगा लेंगे।

 const mysqlx = require('@mysql/xdevapi*); // MySQL Connection var mySession = mysqlx.gctSession({ host: 'localhost', port: 33060, dbUser: 'photon* }); … session.getSchema("particle").getCollection("cloud_data_docstore") .add( data ) .execute(function (row) { }).catch(err => { console.log(err); }) .then( -Function (notices) { console.log("Wrote to MySQL") }); ...https://dev.mysql.com/doc/dev/connector-nodejs/

यदि आप इसके साथ प्रयोग करना चाहते हैं, तो आप MySQL 5.7 को कॉन्फ़िगर कर सकते हैं ताकि यह उचित पोर्ट डॉक्यूमेंट स्टोर या XI SII को समझे और सुने। मैंने कनेक्टर-नोडज का उपयोग किया।

यह एक उदाहरण है कि मैं वहां क्या लिखता हूं: बीयर आदि, मुझे बिल्कुल नहीं पता कि क्या है। अब हम इसे लिखते हैं और बाद में इसका विश्लेषण करते हैं।

हमारे कार्यक्रम का अगला बिंदु यह है कि कैसे देखा जाए?

MySQL डेटा स्टोरेज: JSON + indexes

JSON और MySQL 5.7 में एक शानदार विशेषता है जो JSON से फ़ील्ड को खींच सकता है। यह JSON_EXTRACT फ़ंक्शन पर इस तरह की सिंटैक्टिक चीनी है। मुझे लगता है कि यह बहुत सुविधाजनक है।

हमारे मामले में डेटा उस कॉलम का नाम है जिसमें JSON संग्रहीत है, और नाम हमारा फ़ील्ड है। नाम, डेटा, publish_at - यह हम सब इस तरह से बाहर निकाल सकते हैं।

 select data->>'$.name' as data_name, data->>'$.data' as data, data->>'$.published_at' as published from cloud_data_json order by data->'$.published_at' desc limit 10;

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

तार्किक तरीके से, MySQL इस मामले में किसी भी अनुक्रमित का उपयोग नहीं करेगा। हम JSON से डेटा खींचते हैं और कुछ प्रकार के फ़िल्टर और सॉर्टिंग लागू करने का प्रयास करते हैं। इस मामले में, हम फाइलशॉर्ट का उपयोग करते हैं।

 EXPLAIN select data->>'$.name' as data_name ... order by data->>'$.published_at' desc limit 10 select_type: SIMPLE table: cloud_data_json possible_keys: NULL key: NULL … rows: 101589 filtered: 100.00 Extra: Using filesort

फाइलसॉर्ट का उपयोग करना बहुत बुरा है, यह एक बाहरी प्रकार है।

अच्छी खबर यह है कि आप इसे गति देने के लिए 2 कदम उठा सकते हैं।

स्टेप 1. एक वर्चुअल कॉलम बनाएं

 mysql> ALTER TABLE cloud_data_json -> ADD published_at DATETIME(6) -> GENERATED ALWAYS AS (STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ")) VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

मैं EXTRACT करता हूं, यानी, मैं JSON से डेटा खींचता हूं और इसके आधार पर मैं एक वर्चुअल कॉलम बनाता हूं। वर्चुअल कॉलम MySQL 5.7 और MySQL 8 में संग्रहीत नहीं है - यह सिर्फ एक अलग कॉलम बनाने की क्षमता है।

आप पूछते हैं कि यह कैसा है, आपने कहा कि ALTER TABLE इतना लंबा ऑपरेशन है। लेकिन यहाँ यह इतना बुरा नहीं है। वर्चुअल कॉलम बनाना तेज़ है । वहाँ वहाँ कम है, लेकिन वास्तव में MySQL में सभी DDL परिचालनों पर एक ताला है। ALTER TABLE एक काफी तेज़ ऑपरेशन है, और यह पूरी तालिका का पुनर्निर्माण नहीं करता है।

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

मूल क्वेरी को अनुकूलित करने के लिए, आपको publish_at और नाम को खींचने की आवश्यकता है। Published_at पहले से मौजूद है, नाम आसान है - बस एक वर्चुअल कॉलम बनाएं।

 mysql> ALTER TABLE cloud_data_json -> ADD data_name VARCHAR(255) -> GENERATED ALWAYS AS (data->>'$.name') VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

चरण 2. एक सूचकांक बनाना

नीचे दिए गए कोड में, मैं publish_at पर एक इंडेक्स बनाता हूं और क्वेरी निष्पादित करता हूं:

 mysql> alter table cloud_data_json add key (published_at); Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G table: cloud_data_json type: index possible_keys: NULL key: published_at key_len: 9 rows: 10 filtered: 100.00 Extra: Backward index scan

आप देख सकते हैं कि MySQL वास्तव में सूचकांक का उपयोग करता है। यह आदेश द्वारा एक अनुकूलन है। इस उदाहरण में, डेटा और नाम अनुक्रमित नहीं हैं। MySQL डेटा द्वारा ऑर्डर का उपयोग करता है, और चूंकि हमारे पास publish_at पर एक इंडेक्स है, इसलिए यह इसका उपयोग करता है।

इसके अलावा, मैं एक ही वाक्यविन्यास चीनी STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ") उपयोग कर सकता था बजाय क्रम में प्रकाशित किए STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ") । MySQL अभी भी यह समझेगा कि इस कॉलम पर एक इंडेक्स है और इसका उपयोग शुरू करें।

इसके साथ वास्तव में एक छोटी सी समस्या है। मान लें कि मैं न केवल प्रकाशित_त द्वारा डेटा को सॉर्ट करना चाहता हूं, बल्कि नाम से भी।

 mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc, data_name asc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 101589 filtered: 100.00 Extra: Using filesort

यदि आपका डिवाइस प्रति सेकंड हज़ारों घटनाओं की दसियों प्रक्रियाओं को प्रकाशित करता है, तो publish_at एक अच्छा प्रकार नहीं देगा, क्योंकि डुप्लिकेट होंगे। इसलिए, हम data_name द्वारा एक और छँटाई जोड़ते हैं। यह केवल चीजों के इंटरनेट के लिए एक विशिष्ट क्वेरी नहीं है: मुझे अंतिम 10 ईवेंट दें, लेकिन उन्हें दिनांक के अनुसार क्रमबद्ध करें, और फिर, उदाहरण के लिए, आरोही क्रम में व्यक्ति के अंतिम नाम से। ऐसा करने के लिए, ऊपर दिए गए उदाहरण में, दो फ़ील्ड हैं और दो प्रकार की कुंजी निर्दिष्ट की गई है: अवरोही और आरोही।

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

MySQL 8.0 में नया

उतरना / चढ़ना

MySQL 5.7 में, ऐसी क्वेरी को अनुकूलित नहीं किया जा सकता है, यदि केवल अन्य चीजों की कीमत पर। MySQL 8 में, प्रत्येक क्षेत्र के लिए छँटाई निर्दिष्ट करने का एक वास्तविक अवसर था।

 mysql> alter table cloud_data_json add key published_at_data_name (published_at desc, data_name asc); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0

सबसे दिलचस्प बात यह है कि अनुक्रमणिका नाम लंबे समय से SQL में आने के बाद अवरोही / आरोही कुंजी है। यहां तक कि MySQL 3.23 के पहले संस्करण में, आप publish_at अवरोही या प्रकाशित_at आरोही निर्दिष्ट कर सकते हैं। MySQL ने इसे स्वीकार कर लिया, लेकिन कुछ भी नहीं किया , अर्थात यह हमेशा एक दिशा में क्रमबद्ध होता था।

MySQL 8 में, यह तय किया गया था और अब इस तरह की सुविधा है। आप अवरोही क्रम में और डिफ़ॉल्ट सॉर्ट के साथ एक फ़ील्ड बना सकते हैं।

आइए एक दूसरे पर वापस जाएं और चरण 2 से फिर से उदाहरण देखें।

यह काम क्यों करता है, अन्यथा यह नहीं करता है यह काम करता है क्योंकि MySQL इंडेक्स में यह एक बी-ट्री है, और बी-ट्री इंडेक्स को शुरुआत से और अंत से दोनों पढ़ा जा सकता है। इस मामले में, MySQL इंडेक्स को अंत से पढ़ता है और सब कुछ ठीक है। लेकिन अगर हम उतरते और चढ़ते हैं, तो आप पढ़ नहीं सकते। आप एक ही क्रम में पढ़ सकते हैं, लेकिन आप दो प्रकारों को जोड़ नहीं सकते हैं - आपको पुन: क्रमबद्ध करने की आवश्यकता है।

चूंकि हम एक बहुत ही विशिष्ट मामले का अनुकूलन कर रहे हैं, हम इसके लिए एक सूचकांक बना सकते हैं और एक विशिष्ट प्रकार निर्दिष्ट कर सकते हैं: यहाँ प्रकाशित_त उतर रहा है, data_name आरोही है। MySQL इस इंडेक्स का उपयोग करता है, और सब कुछ ठीक और तेज होगा।

 mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: index possible_keys: NULL key: published_at_data_name key_len: 267 ref: NULL rows: 10 filtered: 100.00 Extra: NULL

यह MySQL 8 की एक विशेषता है, जो अब प्रकाशन के समय, पहले से ही उपलब्ध है और उत्पादन में उपयोग के लिए तैयार है।

आउटपुट परिणाम

दो और दिलचस्प चीजें हैं जो मैं दिखाना चाहता हूं:

1. सुंदर प्रिंट, यानी स्क्रीन पर डेटा का एक सुंदर आउटपुट। सामान्य चयन के साथ, JSON फॉर्मेट नहीं किया जाएगा।

 mysql> select json_pretty(data) from cloud_data_json where data->>'$.data' like '%beer%' limit 1\G … json_pretty(data): { "ttl": 60, "data": "FvGav,tagkey=beer-store spFridge=7.00,pvFridge=7.44", "name": "LOG_DATA_DEBUG", "coreid": "3600....", "published_at": "2017-09-28T18:21:16.517Z" }

2. हम कह सकते हैं कि MySQL परिणाम को JSON सरणी या JSON ऑब्जेक्ट के रूप में आउटपुट करेगा, फ़ील्ड निर्दिष्ट करेगा, और फिर आउटपुट JSON के रूप में स्वरूपित किया जाएगा।

JSON दस्तावेजों के अंदर पूर्ण-पाठ खोज

यदि हम एक लचीली भंडारण प्रणाली का उपयोग करते हैं और यह नहीं जानते कि हमारे JSON के अंदर क्या है, तो पूर्ण-पाठ खोज का उपयोग करना तर्कसंगत होगा।

दुर्भाग्य से, पूर्ण-पाठ खोज की अपनी सीमाएँ हैं । मैंने जो पहली कोशिश की, वह थी फुल-टेक्स्ट की। मैंने ऐसा काम करने की कोशिश की:

 mysql> alter table cloud_data_json_indexes add fulltext key (data); ERROR 3152 (42000): JSON column 'data' supports indexing only via generated columns on a specified ISON path.

दुर्भाग्य से यह काम नहीं करता है। यहां तक कि MySQL 8 में, JSON क्षेत्र द्वारा बस एक पूर्ण-पाठ सूचकांक बनाना दुर्भाग्य से असंभव है। बेशक, मैं इस तरह के एक समारोह के लिए करना चाहते हैं - JSON कुंजी द्वारा कम से कम खोज करने की क्षमता बहुत उपयोगी होगी।

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

 mysql> ALTER TABLE cloud_data_json_indexes -> ADD data_data VARCHAR(255) -> GENERATED ALWAYS AS (data->>'$.data') VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table cloud_data_json_indexes add fulltext key ft_json(data_name, data_data); ERROR 3106 (HY000): 'Fulltext index on virtual generated column' is not supported for generated columns.

दुर्भाग्य से, यह या तो काम नहीं करता है - आप एक वर्चुअल कॉलम पर एक पूर्ण-पाठ इंडेक्स नहीं बना सकते हैं ।

यदि ऐसा है, तो एक संग्रहीत कॉलम बनाएँ। MySQL 5.7 आपको एक कॉलम को संग्रहीत फ़ील्ड के रूप में घोषित करने की अनुमति देता है।

 mysql> ALTER TABLE cloud_data_json_indexes -> ADD data_name VARCHAR(255) CHARACTER SET UTF8MB4 -> GENERATED ALWAYS AS (data->>'$.name') STORED; Query OK, 123518 rows affected (1.75 sec) Records: 123518 Duplicates: 0 Warnings: 0 mysql> alter table cloud_data_json_indexes add fulltext key ft_json(data_name); Query OK, 0 rows affected, 1 warning (3.78 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +------------+--------+---------------------------------------------------+ | Level | Code | Message | +------------+--------+---------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +------------+--------+---------------------------------------------------+

पिछले उदाहरणों में, हमने वर्चुअल कॉलम बनाए जो संग्रहीत नहीं हैं, लेकिन अनुक्रमित बनाए और संग्रहीत किए जाते हैं। इस मामले में, मुझे MySQL को बताना था कि यह एक STORED कॉलम है, अर्थात यह बनाया जाएगा और डेटा को इसके लिए कॉपी कर लिया जाएगा। उसके बाद, MySQL ने एक पूर्ण-पाठ सूचकांक बनाया, इसके लिए हमें तालिका को फिर से बनाना पड़ा। लेकिन यह सीमा वास्तव में InnoDB और InnoDB पूर्ण खोज है: आपको एक विशेष पूर्ण-पाठ खोज पहचानकर्ता जोड़ने के लिए तालिका को फिर से बनाना होगा।

दिलचस्प बात यह है कि MySQL 8 में इमोटिकॉन्स के लिए एक नया UTF8 MB4 एन्कोडिंग था। बेशक, उनके लिए काफी नहीं है, लेकिन क्योंकि UTF8MB3 में रूसी, चीनी, जापानी और अन्य भाषाओं के साथ कुछ समस्याएं हैं।

 mysql> ALTER TABLE cloud_data_json_indexes -> ADD data_data TEXT CHARACTER SET UTF8MB4 -> GENERATED ALWAYS AS ( CONVERT(data->>'$.data' USING UTF8MB4) ) STORED Query OK, 123518 rows affected (3.14 sec) Records: 123518 Duplicates: 0 Warnings: 0

तदनुसार, MySQL 8 को JSON डेटा को UTF8MB4 में संग्रहीत करना चाहिए। लेकिन क्या इस तथ्य के कारण कि Node.js डिवाइस क्लाउड से कनेक्ट होता है, और वहां कुछ गलत तरीके से लिखा गया है, या यह बीटा संस्करण बग है, ऐसा नहीं हुआ। इसलिए, मुझे उन्हें संग्रहीत कॉलम में लिखने से पहले डेटा को बदलना पड़ा।

 mysql> ALTER TABLE cloud_data_json_indexes DROP KEY ft_json, ADD FULLTEXT KEY ft_json(data_name, data_data); Query OK, 0 rows affected (1.85 sec) Records: 0 Duplicates: 0 Warnings: 0

उसके बाद, मैं दो क्षेत्रों पर एक पूर्ण-पाठ खोज बनाने में सक्षम था: JSON नाम पर और JSON डेटा पर।

न केवल IoT

JSON केवल चीजों का इंटरनेट नहीं है। इसका उपयोग अन्य रोचक चीजों के लिए किया जा सकता है:

कस्टम फ़ील्ड (CMS);
जटिल संरचनाएं, आदि;

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

यह एक ऐसा पेड़ है जिसे सफलतापूर्वक लगाया गया है। दुर्भाग्य से, कुछ साल बाद, हिरण ने इसे खाया, लेकिन यह पूरी तरह से अलग कहानी है।

यह रिपोर्ट एक उत्कृष्ट उदाहरण है कि एक बड़े सम्मेलन में एक विषय से एक पूरा खंड कैसे निकलता है, और फिर एक अलग घटना। इंटरनेट ऑफ थिंग्स के मामले में, हमें InoThings ++ मिला - इंटरनेट ऑफ थिंग्स मार्केट में पेशेवरों के लिए एक सम्मेलन, जो 4 अप्रैल को दूसरी बार आयोजित किया जाएगा।

सम्मेलन की केंद्रीय घटना, ऐसा लगता है, गोल मेज होगा "क्या हमें इंटरनेट ऑफ थिंग्स पर राष्ट्रीय मानकों की आवश्यकता है?", जिसे व्यवस्थित रूप से व्यापक लागू रिपोर्टों द्वारा पूरक किया जाएगा। यदि आपका भारी लोड सिस्टम सही ढंग से IIoT पर जा रहा है तो आओ।

MySQL (JSON) में लचीला डेटा संग्रहण