यह पोस्ट MySQL 8.0 के JSON functions का हाथों-हाथ walkthrough कवर करती है। JSON support MySQL में 5.7 से है, लेकिन 8.0 ने एक सार्थक सुधारों का सेट जोड़ा — बेहतर indexing strategies, नए functions, और multi-valued indexes — जो JSON data के साथ काम करना काफी अधिक व्यावहारिक बनाते हैं। निम्नलिखित सबसे आमतौर पर आवश्यक patterns को दस्तावेज करता है, जिसमें EXPLAIN output और performance observations शामिल हैं जो जानने लायक हैं।
यह "JSON vs. relational" बहस पोस्ट नहीं है। यदि आप MySQL में JSON स्टोर कर रहे हैं, तो आपके पास पहले से ही आपके कारण हैं। यहाँ का लक्ष्य यह सुनिश्चित करना है कि आप उपलब्ध tooling का प्रभावी ढंग से उपयोग कर रहे हैं।
परिवेश
mysql> SELECT @@version, @@version_comment\G
*************************** 1. row ***************************
@@version: 8.0.36
@@version_comment: MySQL Community Server - GPL
टेस्टिंग एक VM पर की गई जिसमें 8GB RAM था और innodb_buffer_pool_size को 4G पर सेट किया गया था। एक housekeeping नोट जो उल्लेख करने लायक है: query_cache_type 8.0 में अप्रासंगिक है क्योंकि query cache को पूरी तरह हटा दिया गया था। यदि आपने 5.7 instance को migrate किया है और आपके my.cnf में अभी भी वह variable है, तो इसे हटा दें — MySQL 8.0 startup error फेंकेगा।
टेस्ट टेबल सेटअप करना
टेस्ट टेबल एक काफी सामान्य pattern को simulate करती है — एक application जो user profile data और event metadata को JSON blobs के रूप में स्टोर करती है:
CREATE TABLE user_events (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
event_data JSON NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_user (user_id)
) ENGINE=InnoDB;
INSERT INTO user_events (user_id, event_data) VALUES
(1, '{"action":"login","ip":"192.168.1.10","tags":["mobile","vpn"],"score":88}'),
(1, '{"action":"purchase","ip":"192.168.1.10","tags":["desktop"],"score":72,"amount":49.99}'),
(2, '{"action":"login","ip":"10.0.0.5","tags":["mobile"],"score":91}'),
(3, '{"action":"logout","ip":"10.0.0.9","tags":["desktop","vpn"],"score":65}'),
(2, '{"action":"purchase","ip":"10.0.0.5","tags":["mobile"],"score":84,"amount":129.00}');
बेसिक एक्सट्रैक्शन: JSON_VALUE बनाम JSON_EXTRACT
JSON_VALUE() को MySQL 8.0.21 में पेश किया गया था और यह scalar values को निकालने का साफ़-सुथरा तरीका है जिसमें built-in type casting है। उसके पहले, आप JSON_EXTRACT() (या -> shorthand) का उपयोग कर रहे थे और manually casting कर रहे थे, जो काम करता है लेकिन आपकी queries में noise जोड़ता है।
-- Pre-8.0.21 approach
SELECT user_id,
JSON_EXTRACT(event_data, '$.action') AS action,
CAST(JSON_EXTRACT(event_data, '$.score') AS UNSIGNED) AS score
FROM user_events;
-- Cleaner 8.0.21+ approach
SELECT user_id,
JSON_VALUE(event_data, '$.action') AS action,
JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED) AS score
FROM user_events;
दूसरी query का आउटपुट:
+---------+----------+-------+
| user_id | action | score |
+---------+----------+-------+
| 1 | login | 88 |
| 1 | purchase | 72 |
| 2 | login | 91 |
| 3 | logout | 65 |
| 2 | purchase | 84 |
+---------+----------+-------+
5 rows in set (0.00 sec)
RETURNING clause वास्तव में उपयोगी है। यह awkward double-cast pattern को समाप्त करता है और बाद में query code पढ़ते समय intent को स्पष्ट बनाता है।
Multi-Valued Indexes: असली गेम चेंजर
यहीं 8.0 ने वास्तव में JSON workloads के लिए सुई को हिलाया। Multi-valued indexes, जो MySQL 8.0.17 से उपलब्ध हैं, आपको JSON column के अंदर array elements को सीधे index करने की अनुमति देते हैं। यह practice में कैसा दिखता है:
ALTER TABLE user_events
ADD INDEX idx_tags ((CAST(event_data->'$.tags' AS CHAR(64) ARRAY)));
यहाँ EXPLAIN tag value से filter करने वाली query पर before और after दिखाता है:
-- Without the multi-valued index:
EXPLAIN SELECT * FROM user_events
WHERE JSON_CONTAINS(event_data->'$.tags', '"vpn"')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_events
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 100.00
Extra: Using where
-- After adding the multi-valued index:
EXPLAIN SELECT * FROM user_events
WHERE JSON_CONTAINS(event_data->'$.tags', '"vpn"')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_events
partitions: NULL
type: range
possible_keys: idx_tags
key: idx_tags
key_len: 67
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
Full table scan से range scan तक। 5 rows पर यह trivial है, लेकिन लाखों rows वाली table और frequent tag-based filtering पर, वह अंतर महत्वपूर्ण है। सुधार table size और query frequency के साथ सीधे scale करता है।
एक महत्वपूर्ण gotcha: MEMBER OF() और JSON_OVERLAPS() भी multi-valued indexes से लाभान्वित होते हैं, लेकिन JSON_SEARCH() नहीं। यह design time पर query pattern चुनते समय मायने रखता है:
-- This WILL use the multi-valued index:
SELECT * FROM user_events
WHERE 'vpn' MEMBER OF (event_data->'$.tags');
-- This will NOT use it:
SELECT * FROM user_events
WHERE JSON_SEARCH(event_data->'$.tags', 'one', 'vpn') IS NOT NULL;
JSON को Aggregate और Transform करना
कुछ aggregation functions जो अच्छी तरह जानने लायक हैं:
``````html-- Build a JSON array of actions per user
SELECT user_id,
JSON_ARRAYAGG(JSON_VALUE(event_data, '$.action')) AS actions
FROM user_events
GROUP BY user_id;
+---------+----------------------+
| user_id | actions |
+---------+----------------------+
| 1 | ["login","purchase"] |
| 2 | ["login","purchase"] |
| 3 | ["logout"] |
+---------+----------------------+
3 rows in set (0.01 sec)
-- Summarize into a JSON object keyed by action
SELECT user_id,
JSON_OBJECTAGG(
JSON_VALUE(event_data, '$.action'),
JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED)
) AS score_by_action
FROM user_events
GROUP BY user_id;
+---------+--------------------------------+
| user_id | score_by_action |
+---------+--------------------------------+
| 1 | {"login": 88, "purchase": 72} |
| 2 | {"login": 91, "purchase": 84} |
| 3 | {"logout": 65} |
+---------+--------------------------------+
3 rows in set (0.00 sec)
JSON_OBJECTAGG() त्रुटि फेंकेगा यदि किसी समूह में डुप्लिकेट कुंजियाँ हों। यह जानना उपयोगी है इससे पहले कि आप इसे प्रोडक्शन ETL पाइपलाइन में सामना करें। उस स्थिति में, आपको अपस्ट्रीम में डुप्लिकेट हटाने होंगे या एप्लिकेशन लॉजिक में इसे हैंडल करना होगा इससे पहले कि डेटा इस एग्रीगेशन स्टेप तक पहुँचे।
JSON-हैवी क्वेरीज़ के बाद SHOW STATUS चेक करना
क्वेरी पैटर्न का मूल्यांकन करते समय, हैंडलर मेट्रिक्स चेक करना एक उपयोगी आदत है:
FLUSH STATUS;
SELECT * FROM user_events
WHERE JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED) > 80;
SHOW STATUS LIKE 'Handler_read%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 6 |
+----------------------------+-------+
7 rows in set (0.00 sec)
Handler_read_rnd_next मान पूर्ण स्कैन की पुष्टि करता है — आश्चर्य की कोई बात नहीं क्योंकि score मान पर कोई फंक्शनल इंडेक्स नहीं है। स्केल पर score-आधारित फिल्टरिंग के लिए, एक जेनरेटेड कॉलम जिसमें इंडेक्स हो, सही उत्तर है:
ALTER TABLE user_events
ADD COLUMN score_val TINYINT UNSIGNED
GENERATED ALWAYS AS (JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED)) VIRTUAL,
ADD INDEX idx_score (score_val);
उसे जोड़ने के बाद, वही क्वेरी एक उचित इंडेक्स रेंज स्कैन पर आ जाती है। JSON फील्ड्स पर जेनरेटेड कॉलम MySQL 8.0 और Percona Server 8.0 दोनों में उपलब्ध हैं, और वे किसी भी सार्थक स्केल पर स्केलर JSON फील्ड फिल्टरिंग के लिए सबसे विश्वसनीय मार्ग बने रहते हैं।
यदि आप Percona Server चला रहे हैं, तो Percona Toolkit से pt-query-digest अभी भी सबसे व्यावहारिक तरीका है जिससे आप यह पहचान सकें कि कौन सी JSON-हैवी क्वेरीज़ वास्तव में प्रोडक्शन में दर्द पैदा कर रही हैं इससे पहले कि आप सट्टेबाजी में इंडेक्स जोड़ना शुरू करें।
व्यावहारिक अवलोकन
- मल्टी-वैल्यूड इंडेक्स (8.0.17+) एक लंबे समय से लंबित सुधार हैं और अच्छा काम करते हैं जब आपके क्वेरी पैटर्न JSON_CONTAINS() या MEMBER OF() के साथ संरेखित हों
- JSON_VALUE() with RETURNING (8.0.21+) पुराने cast-after-extract पैटर्न से साफ-सुथरा है और इसे लगातार अपनाना चाहिए
- जेनरेटेड कॉलम प्लस इंडेक्स स्केल पर स्केलर JSON फील्ड फिल्टरिंग के लिए सबसे विश्वसनीय मार्ग बने रहते हैं
- ग्रुप्ड डेटा में JSON_OBJECTAGG() डुप्लिकेट कुंजी त्रुटियों पर नजर रखें — यह ETL पाइपलाइनों में कठोर त्रुटि के रूप में उभरता है और टेस्टिंग में आसानी से छूट सकता है यदि आपका सैंपल डेटा संयोग से साफ हो
- हमेशा EXPLAIN से इंडेक्स उपयोग की जाँच करें — ऑप्टिमाइज़र हमेशा जटिल WHERE क्लॉज़ में मल्टी-वैल्यूड इंडेक्स को नहीं चुनता, और मान लेने के बजाय पुष्टि करना उचित है
सारांश
MySQL 8.0 के JSON सुधार वास्तव में उपयोगी हैं, विशेष रूप से मल्टी-वैल्यूड इंडेक्स और JSON_VALUE() टाइप कास्टिंग के साथ। वे अच्छे स्कीमा डिज़ाइन की जगह नहीं लेते, लेकिन उन मामलों के लिए जहाँ JSON स्टोरेज उचित है या विरासत में मिला है, आपके पास अब वास्तविक टूल्स हैं काम करने के लिए बजाय सिर्फ आशा करने के कि ऑप्टिमाइज़र इसे समझ लेगा। विशेष रूप से जेनरेटेड कॉलम पैटर्न का मूल्यांकन जल्दी करना चाहिए यदि आपको पता है कि कुछ JSON फील्ड्स WHERE क्लॉज़ में नियमित रूप से उपयोग होंगे।
उपयोगी संदर्भ:
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें