👩🏾‍✈️ 👨‍❤️‍💋‍👨 🖐🏼 अनुक्रमणिका का उपयोग करते हुए SQL सर्वर प्रदर्शन का अनुकूलन ♟️ ⏩ ➗

परिचय

जैसा कि आप जानते हैं, अनुक्रमित एक cngie में सामग्री या सूचकांक की तालिका के समान उत्पादकता बढ़ाते हैं। इंटरनेट पर कई लेखों और पुस्तकों के कुछ अध्यायों को पढ़ने के बाद, मैं यह जानना चाहूंगा कि कैसे सूचकांक SQL सर्वर से डेटा पुनर्प्राप्ति की गति को बढ़ाने में मदद करते हैं। एक उदाहरण पर विचार करें।
इसके लिए हमें एक विदेशी कुंजी द्वारा जुड़ी दो तालिकाओं की आवश्यकता है। मुख्य तालिका में 10,000 पंक्तियाँ होंगी, दूसरे में 1,000,000 लिखा होगा। पहली तालिका में एक प्राथमिक कुंजी और पाठ के साथ 2 कॉलम होंगे, दूसरी तालिका में एक प्राथमिक कुंजी, 2 पाठ फ़ील्ड, 2 संख्यात्मक फ़ील्ड, एक दिनांक, परिकलित स्तंभ और एक विदेशी कुंजी होगी।

डेटाबेस संरचना

DB संरचना

CREATE TABLE "maintable"( "ID" INT NOT NULL IDENTITY (1, 1) PRIMARY KEY, "name" NVARCHAR (50) NULL, "description" NVARCHAR (100) NULL ) CREATE TABLE "secondtable" ( "ID" INT NOT NULL IDENTITY (1, 1) PRIMARY KEY, "sname" NVARCHAR (50) NULL, "sdescr" NVARCHAR (100) NULL, "somefirstnumber" INT DEFAULT 0, "somesecondnumber" INT DEFAULT 1, "somedatetime" DATETIME NOT NULL, "howmanyhours" INT NULL DEFAULT 0, "newdate" AS DATEADD(HOUR, "howmanyhours", "somedatetime"), "mainID" INT NOT NULL CONSTRAINT FK_SecondToMain FOREIGN KEY ("mainID") REFERENCES "maintable" ("ID") ); --  CREATE PROCEDURE "insertintomain" @Name NVARCHAR (50), @Descr NVARCHAR (100) AS INSERT INTO "SimpleIndex"."dbo"."maintable" ("name", "description") VALUES (@Name, @Descr); GO CREATE PROCEDURE "insertintosecond" @Sname NVARCHAR(50), @Sdescr NVARCHAR(100), @Firstnumber INT, @Secondnumber INT, @SomeDT DATETIME, @Hours INT, @MainID INT AS INSERT INTO "SimpleIndex"."dbo"."secondtable" ( "sname", "sdescr", "somefirstnumber", "somesecondnumber", "somedatetime", "howmanyhours", "mainID") VALUES ( @Sname, @Sdescr,@Firstnumber, @Secondnumber, @SomeDT, @Hours, @MainID) GO

डेटा प्रविष्टि

यह स्पष्ट है कि आप केवल एक लाख लाइनें नहीं डालेंगे, हम C # का उपयोग करेंगे।

चलो एक सरल कंसोल एप्लिकेशन बनाएं

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; namespace SimpleAddingRandomRecordsToDatabase { class Program { static string alphabet() { //      string alphabet = "abcdefgh ijklmnopqrst uvwxyz    "; //    Encoding enc = Encoding.UTF8; var bytes = enc.GetBytes(alphabet); alphabet = enc.GetString(bytes); return alphabet; } static int alphlen = alphabet().Length; static string getRandomString(int lenth, int rndparam, bool allowspacebars) { string str = ""; Random rand = new Random(rndparam); for (int i = 0; i < lenth; i++) { int num = rand.Next(alphlen); str = str + alphabet()[num]; } if (!allowspacebars) { str = str.Replace(" ", ""); } return str; } static void Main(string[] args) { string result; Console.WriteLine(" 1   "); result = Console.ReadLine(); if (result.Equals("1")) { //   SqlConnection cn = new SqlConnection(); SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder(); cs.IntegratedSecurity = true; cs.DataSource = @"KONSTANTIN\KONSTANTINSQL"; cs.InitialCatalog = "SimpleIndex"; cn.ConnectionString = cs.ToString(); cn.Open(); //  10 000     int i2 = 0; for (int i = 0; i < 10000; i++) { SqlCommand cm = cn.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "insertintomain"; i2++; cm.Parameters.Add(new SqlParameter("Name", getRandomString(50, i2, false))); i2++; cm.Parameters.Add(new SqlParameter("Descr", getRandomString(100, i2, true))); cm.ExecuteNonQuery(); } // 1 000 000 ,     i2 = 0; for (int i = 0; i < 1000000; i++) { Random rand = new Random(); DateTime dt = DateTime.Now; dt = dt.AddHours((-1) * rand.Next(2000)); SqlCommand cm = cn.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "insertintosecond"; i2++; cm.Parameters.Add(new SqlParameter("Sname", getRandomString(50, i2, false))); i2++; cm.Parameters.Add(new SqlParameter("Sdescr", getRandomString(100, i2, true))); i2++; Random rand2 = new Random(i2); cm.Parameters.Add(new SqlParameter("Firstnumber", rand2.Next(10000))); i2++; rand2 = new Random(i2); cm.Parameters.Add(new SqlParameter("Secondnumber", rand2.Next(100))); cm.Parameters.Add(new SqlParameter("SomeDT", dt)); i2++; rand = new Random(i2); cm.Parameters.Add(new SqlParameter("Hours", rand.Next(30))); i2++; rand = new Random(i2); cm.Parameters.Add(new SqlParameter("MainID", rand.Next(9999)+1)); cm.ExecuteNonQuery(); } cn.Close(); Console.WriteLine(" ."); } } } }

हम कंसोल एप्लिकेशन लॉन्च करते हैं, "1" दबाएं और प्रतीक्षा करें। मुझे डालने में लगभग 15-20 मिनट लगे।

परिणामों का विवरण

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

प्राथमिक कुंजी खोज

प्राथमिक कुंजी द्वारा पंक्ति खोज करें, दस हजार की एक विशिष्ट पंक्ति संख्या का संकेत:

 SELECT "name", "description" FROM "maintable" WHERE "ID" = 3823;

खोज का समय: 1.523 सेकंड ।
दूसरे अनुरोध में 0.9 सेकंड और तीसरे में 0.1 सेकंड लगे ।

हम एक ही तालिका से महान चयनात्मकता के साथ दूसरी क्वेरी निष्पादित करते हैं:

 SELECT "name", "description" FROM "maintable" WHERE "ID" BETWEEN 5000 AND 6000;

पहला अनुरोध 0.3 सेकंड , दूसरा 0.26 सेकंड , तीसरा 0.25 सेकंड लिया गया ।

अब हम एक लाख रिकॉर्ड वाले टेबल के समान प्रश्नों का निष्पादन करते हैं। एक विशिष्ट पंक्ति संख्या के लिए:

 SELECT * FROM "secondtable" WHERE "ID" = 728472;

खोज समय: 1.4 सेकंड , दूसरी क्वेरी 1.36 सेकंड दिखाई गई । । अगला, औसत क्वेरी समय दिखाया जाएगा।

प्राथमिक कुंजी कॉलम में पहले से ही एक इंडेक्स है, इसलिए प्रदर्शन में सुधार करना सबसे अधिक असंभव है। परिणामों के आधार पर, यह कहा जा सकता है कि समान मापदंडों के साथ दोहराया गया प्रश्न खोज की गति को बढ़ाता है।

पैटर्न खोज

अनुक्रमित और गैर-अनुक्रमित कॉलम के साथ टेम्पलेट द्वारा पाठ खोज पर विचार करें:

 SELECT * FROM "maintable" WHERE "name" LIKE '%';

सूचकांक के बिना, इस तरह की क्वेरी को 10,000 पंक्तियों में से 0.172 सेकंड में निष्पादित किया जाता है। सूचकांक के साथ

 CREATE NONCLUSTERED INDEX iName ON "maintable" ("name")

यह 0.112 सेकंड में किया जाता है, और "नाम" और "विवरण" फ़ील्ड वाले एक समग्र सूचकांक के साथ, अनुरोध ने 0.09 सेकंड लिया।

जांच

 SELECT * FROM "maintable" WHERE "name" LIKE '%dc%';

एक इंडेक्स के बिना 0.172 सेकंड और एक इंडेक्स के साथ 0.112 सेकंड में प्रदर्शन किया।

एक तालिका के लिए एक खाका खोज करें जिसमें एक लाख रिकॉर्ड हो।

 SELECT * FROM "secondtable" WHERE "sname" LIKE '%';

इंडेक्सिंग के बिना यह क्वेरी औसतन 1.7 सेकंड लेगी, जबकि 0.15 सेकंड कॉलम इंडेक्स के साथ खर्च किए जाएंगे।

दिनांक खोज

शुरुआत के लिए, हम एक कम चयनात्मकता अनुरोध करेंगे

 SELECT * FROM "secondtable" WHERE "somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-26-11 11:32:00'

इंडेक्स के बिना, औसतन 0.7 सेकंड , इंडेक्सेड - 0.22 सेकंड लगेगा।

उच्च चयनात्मकता क्वेरी (420,600 पंक्तियाँ)

 SELECT * FROM "secondtable" WHERE "somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00'

इंडेक्स के बिना - 19.219 सेकंड , इंडेक्स केवल दिनांक फ़ील्ड - 9.9 सेकंड ।

गणना किए गए कॉलम के लिए तिथि द्वारा क्वेरी

 SELECT * FROM "secondtable" WHERE "newdate" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00'

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

टेबल ज्वाइन की

तालिकाओं में शामिल होने पर, एक कॉलम को इंडेक्स करना हमेशा उचित होता है, जो एक विदेशी कुंजी है। निम्नलिखित उदाहरण में, हम देखेंगे कि यह वास्तव में मामला है।

 CREATE NONCLUSTERED INDEX iFK ON "secondtable" ("mainID"); SELECT "name", "sname", "somefirstnumber" FROM "maintable" M INNER JOIN "secondtable" S ON M."ID" = S."mainID" WHERE M."ID" = 8271

ऐसी क्वेरी 101 लाइनों को वापस कर देगी। एक सूचकांक के बिना - 0.422 सेकंड , और एक सूचकांक के साथ - 0.122 सेकंड । जैसा कि हम देख सकते हैं, क्वेरी की गति तीन गुना से अधिक है। संघ से संबंधित शेष प्रश्नों पर विचार करें।

लेकिन सूचकांक हमेशा प्रदर्शन में वृद्धि नहीं करता है। उदाहरण के लिए, एक क्वेरी (420,000 पंक्तियाँ)

 SELECT "name", "sname", "somefirstnumber" FROM "maintable" M INNER JOIN "secondtable" S ON M."ID" = S."mainID" WHERE S."somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00';

एक इंडेक्स के बिना एक ही परिणाम दिखाया, केवल विदेशी कुंजी, विदेशी कुंजी और दिनांक के सूचकांक के साथ, एक समग्र सूचकांक के साथ जिसमें ये दोनों कॉलम शामिल हैं - लगभग 6.8 सेकंड ।

पैटर्न से मेल खाते हुए मिलाएं

चलो एक लाख रिकॉर्ड के बीच टेम्पलेट खोज के साथ एक संयोजन करते हैं:

 SELECT "name", "sname", "somefirstnumber" FROM "maintable" M INNER JOIN "secondtable" S ON M."ID" = S."mainID" WHERE "sname" LIKE '%';

अनुक्रमित के बिना, क्वेरी 0.766 सेकंड लेती है और 5 पंक्तियाँ लौटाती है।
केवल एक विदेशी कुंजी के सूचकांक के साथ - 0,4 सेकंड। ;
केवल एक पाठ क्षेत्र के सूचकांक के साथ - 0.125 सेकंड। ;
विदेशी कुंजी सूचकांक और पाठ क्षेत्र सूचकांक के साथ - 0.109 सेकंड। ;
एक विदेशी कुंजी और एक पाठ क्षेत्र युक्त एक समग्र सूचकांक के साथ - 0.35 सेकंड। ।

परिणामों की तुलना

ग्राफ़ के रूप में परिणामों पर विचार करें। ऊर्ध्वाधर अक्ष बीता हुआ समय ( गति नहीं! ) दिखाता है।
1. प्राथमिक कुंजी द्वारा नमूनाकरण। चूंकि पहले से ही एक संकुल सूचकांक है, एक मेज से एक बड़े और एक छोटे से चयनात्मक नमूने पर विचार करें:

इस ग्राफ से यह स्पष्ट है कि बड़ी मात्रा में डेटा का नमूनाकरण तेजी से संसाधित होता है।

2. तालिका में शामिल होने के साथ प्राथमिक कुंजी द्वारा खोजें। निम्नलिखित ग्राफ से पता चलता है कि विदेशी कुंजी को अनुक्रमित करने से तीन बार से अधिक सर्च ऑपरेशन में तेजी आती है:

3. टेक्स्ट टेम्पलेट द्वारा खोजें। ग्राफ़ दिखाता है कि यदि आप बड़ी मात्रा में डेटा के साथ अनुक्रमणिका का उपयोग करते हैं तो उत्पादकता कई बार बढ़ती है:

4. दिनांक तक खोजें। सूचकांक एक छोटे से नमूने और एक ही तालिका के भीतर एक बड़े दोनों के लिए प्रदर्शन में काफी वृद्धि करते हैं:

तालिकाओं के संयोजन और तिथि के आधार पर खोज के साथ और बिना अनुक्रमणिका के समान परिणाम दिखाई दिए।

5. गणना किए गए कॉलम द्वारा खोजें। सूचकांक खोज समय को भी कम करता है।

6. संघ के साथ टेम्पलेट द्वारा खोजें। टेक्स्ट फ़ील्ड इंडेक्स और दोनों इंडेक्स प्रदर्शन को काफी बढ़ाते हैं:

संक्षेप में देना

परिणामों का विश्लेषण करने के बाद, हम कह सकते हैं कि:

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

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

अनुक्रमणिका का उपयोग करते हुए SQL सर्वर प्रदर्शन का अनुकूलन