MySQL अनुक्रमणिका: बनाएं, जोड़ें और हटाएं ट्यूटोरियल

क्या है एक MySQL अनुक्रमणिका?

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

इंडेक्सिंग के कुछ नुकसान भी हैं — हर INSERT या UPDATE ऑपरेशन को इंडेक्स को बनाए रखना पड़ता है, इसलिए किसी ऐसी टेबल पर बहुत सारे इंडेक्स जोड़ने से, जिस पर बहुत ज्यादा डेटा लिखा जाता है, समग्र प्रदर्शन पर बुरा असर पड़ सकता है। एक सामान्य नियम के तौर पर, उन कॉलम को इंडेक्स करें जो WHERE, JOIN और ORDER BY क्लॉज़ में आते हैं और जिन्हें लिखने की तुलना में पढ़ने की आवृत्ति अधिक होती है।

इंडेक्स का उपयोग क्यों करें?

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

धीमी प्रतिक्रिया समय आमतौर पर डिस्क पर पंक्तियों के भौतिक क्रम में संग्रहीत होने के कारण होता है। बिना इंडेक्स के, MySQL किसी प्रेडिकेट से मेल खाने वाली पंक्तियों को खोजने के लिए प्रत्येक पंक्ति को स्कैन करना आवश्यक है — इसे "पूर्ण तालिका स्कैन" कहते हैं। इंडेक्स की मदद से MySQL सीधे मिलान करने वाली पंक्तियों पर जाएं, जो बी-ट्री लुकअप के लिए क्वेरी प्लान को O(n) से लगभग O(log n) में बदल देता है।

वाक्यविन्यास: इंडेक्स बनाएं

एक इंडेक्स को दो स्थानों पर परिभाषित किया जा सकता है:

1. टेबल बनाते समय।
2. टेबल पहले से मौजूद होने के बाद।

उदाहरण: CREATE TABLE के साथ एक इंडेक्स बनाएं

के लिए myflixdb डेटाबेस में, हमें पूर्ण-नाम कॉलम पर बहुत सारी खोजों की उम्मीद है। नीचे दिया गया स्क्रिप्ट एक नया कॉलम बनाता है। members_indexed तालिका जिसमें सूचकांक है full_names स्तंभ.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

स्क्रिप्ट को निष्पादित करें MySQL वर्कबेंच के विरुद्ध myflixdb डेटाबेस।

ताज़ा करना myflixdb नया देखने के लिए members_indexed मेज़। full_names अब कॉलम नीचे दिखाई देता है अनुक्रमित नोड।

सदस्यता बढ़ने के साथ-साथ खोज प्रश्नों में भी वृद्धि होती है। members_indexed जो WHERE और ORDER BY का उपयोग करते हैं full_names ये मूल सर्वर पर समान प्रश्नों की तुलना में कहीं अधिक तेज़ हैं। members बिना इंडेक्स वाली तालिका।

टेबल पहले से मौजूद होने के बाद इंडेक्स जोड़ें

आपको अक्सर यह पता चलेगा कि किसी मौजूदा टेबल को इंडेक्स की आवश्यकता है — सर्च क्वेरी धीमी होती हैं और EXPLAIN प्लान WHERE में दिखाई देने वाले कॉलम पर पूर्ण टेबल स्कैन दिखाता है। CREATE INDEX यह कथन तालिका को पुनः निर्मित किए बिना एक सूचकांक जोड़ता है।

CREATE INDEX `id_index` ON `table_name` (`column_name`);

ठोस उदाहरण — खोजों की गति बढ़ाना title का कॉलम movies तालिका:

CREATE INDEX `title_index` ON `movies` (`title`);

प्रत्येक क्वेरी जो फ़िल्टर करती है movies.title अब यह नए इंडेक्स द्वारा समर्थित है। अन्य कॉलम पर फ़िल्टर करने वाली क्वेरीज़ अभी भी टेबल को स्कैन करती हैं, जब तक कि उनका अपना इंडेक्स न हो।

नोट: जब आपकी क्वेरी हमेशा एक ही संयोजन पर फ़िल्टर या सॉर्ट करती है, तो आप कई कॉलमों पर एक मिश्रित इंडेक्स बना सकते हैं। क्रम महत्वपूर्ण है - पहला कॉलम यह निर्धारित करता है कि इंडेक्स का उपयोग किया जा सकता है या नहीं।

किसी तालिका पर अनुक्रमणिकाएँ सूचीबद्ध करें

उपयोग SHOW INDEXES किसी टेबल पर परिभाषित सभी इंडेक्स देखने के लिए।

SHOW INDEXES FROM `table_name`;

उदाहरण — सूची अनुक्रमणिकाएँ movies तालिका:

SHOW INDEXES FROM `movies`;

स्टेटमेंट को चलाएँ MySQL कार्यक्षेत्र के खिलाफ myflixdb मौजूदा इंडेक्स और उनमें शामिल कॉलम देखने के लिए।

नोट: प्राथमिक और विदेशी कुंजियों को स्वचालित रूप से अनुक्रमित किया जाता है MySQLप्रत्येक इंडेक्स का एक अद्वितीय नाम होता है और उसमें उन कॉलमों की सूची होती है जिन्हें वह कवर करता है।

सिंटैक्स: ड्रॉप इंडेक्स

उपयोग DROP INDEX किसी टेबल से मौजूदा इंडेक्स को हटाने के लिए। यह तब उपयोगी होता है जब किसी ऐसी टेबल की गति धीमी हो रही हो जिस पर बहुत अधिक डेटा लिखा जाता है, और वह इंडेक्स पढ़ने के मामले में अपना महत्व खो रहा हो।

DROP INDEX `index_id` ON `table_name`;

ठोस उदाहरण — इसे छोड़ दें full_names अनुक्रमणिका से members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

के प्रकार MySQL अनुक्रमित

MySQL यह कई प्रकार के इंडेक्स का समर्थन करता है, जिनमें से प्रत्येक अलग-अलग कार्यभार के लिए उपयुक्त है।

प्रकार	उद्देश्य
प्राथमिक कुंजी	अद्वितीय पंक्ति पहचानकर्ता; InnoDB में तालिका डेटा के साथ क्लस्टर किया गया।
अद्वितीय	यह एक इंडेक्स के रूप में कार्य करते हुए विशिष्टता को लागू करता है।
अनुक्रमणिका (बी-ट्री)	रेंज क्वेरी और समानता लुकअप के लिए उपयोग किया जाने वाला डिफ़ॉल्ट सेकेंडरी इंडेक्स।
पूर्ण पाठ	MATCH … AGAINST सुविधा के साथ प्राकृतिक भाषा पाठ खोज के लिए अनुकूलित।
स्थानिक	POINT और POLYGON जैसे GIS डेटा प्रकारों के लिए R-ट्री इंडेक्स।
HASH	स्थिर समय समानता खोज; इसका उपयोग मेमोरी स्टोरेज इंजन द्वारा किया जाता है।
मिश्रित (बहु-स्तंभ)	कई कॉलमों को एक ही इंडेक्स में संयोजित करता है; सबसे बाईं ओर वाले उपसर्ग नियम का पालन करता है।

के लिए सर्वोत्तम अभ्यास MySQL अनुक्रमित

नीचे दी गई आदतें इंडेक्स को उपयोगी बनाए रखती हैं और उन्हें निरर्थक होने से रोकती हैं।

• क्वेरी पैटर्न के लिए इंडेक्स, कॉलम नाम के लिए नहीं: ऐसे इंडेक्स जोड़ें जो वास्तविक WHERE, JOIN और ORDER BY क्लॉज़ से मेल खाते हों, न कि "हर उस कॉलम से जो महत्वपूर्ण लगता हो"।
• समग्र सूचकांक क्रम देखें: इंडेक्स का उपयोग करने के लिए क्वेरी में अग्रणी कॉलम का होना आवश्यक है।
• डुप्लिकेट इंडेक्स से बचें: एक मिश्रित अनुक्रमणिका का अग्रणी उपसर्ग पहले से ही उस उपसर्ग पर एकल-स्तंभ खोजों को कवर करता है।
• EXPLAIN के साथ निरीक्षण करें: यह सुनिश्चित करें कि प्लानर वास्तव में नया इंडेक्स चुन रहा है।
• अप्रयुक्त इंडेक्स हटाएँ: उपयोग sys.schema_unused_indexes in MySQL 5.7+ उन इंडेक्स को खोजने के लिए जिन्हें कोई नहीं पढ़ता है।
• डेटा प्रकारों का मिलान करें: यदि WHERE क्लॉज़ किसी VARCHAR कॉलम की तुलना किसी संख्या से करता है, तो अंतर्निहित कास्ट के कारण इंडेक्स का उपयोग नहीं किया जा सकता है।

अक्सर पूछे जाने वाले प्रश्न