در پایگاه داده های رابطه ای مانند MySQL ، شاخص ها پایه و اساس بازیابی داده های کارآمد هستند. در میان استراتژی های مختلف نمایه سازی ، شاخص های کامپوزیت – آنهایی که چندین ستون را در بر می گیرند – هنگام برخورد با نمایش داده های پیچیده ، مزایای عملکرد قابل توجهی را ارائه می دهند.
این مقاله به ساختار شاخص های کامپوزیت در MySQL ، رفتار جستجوی آنها و دلیل منطقی در پشت سر می رود قانون پیشوند چپبشر
همانطور که قبلاً بحث کرده ایم ، بیایید اکنون به نمونه ای از پرسش و پاسخ که قبلاً ذکر شد برای کشف موضوع امروز اشاره کنیم: ساختار ذخیره سازی شاخص های کامپوزیتبشر
در یک سؤال که از طریق کاربر ارسال شده است در مورد ساختار ذخیره شاخص کامپوزیت، کسی جواب زیر را داد:
Table T1: (a int primary key, b int, c int, d int, e varchar(20))
create index idx_t1_bcd on t1(b,c,d);
یک شاخص کامپوزیت در
*b, c, d*به نظر می رسد این در درخت شاخص است. در طول مقایسه ،*b*ابتدا بررسی می شود و پس از آن*c*، و سپس*d*بشر
از آنجا که پاسخ فقط شامل یک تصویر واحد و یک جمله کوتاه است ، ممکن است درک آن در یک نگاه کمی سخت باشد.
بنابراین ، بیایید این توضیحات قبلی را بنا کنیم و از آن مثال استفاده کنیم عمیق تر به نحوه ذخیره شاخص های کامپوزیت در یک درخت B+بشر
بیایید با میز شروع کنیم T1، که ستون ها را دارد aبا bبا cبا dوت eبشر
در اینجا a کلید اصلی است جز e، که از نوع است VARCHARستون های دیگر از نوع هستند INTبشر یک فهرست کامپوزیت idx_t1_bcd(b, c, d) در این جدول ایجاد شده است.
از آنجا که تصویری که قبلاً نشان داده شده بود فقط از دو سطح درخت استفاده شده است ، که درک آن دشوار است ، اکنون از مقداری استفاده خواهیم کرد داده های جدول فرضی و نشان دادن الف تصویر تصفیه شده ساختار شاخص کامپوزیت در یک درخت B+.
توجه: این مثال بر اساس موتور ذخیره سازی InnoDB ساخته شده است.
فرض کنید T1 جدول شامل داده های زیر است:
سپس ، بر اساس شاخص کامپوزیت (b, c, d)، درخت B تقریباً مانند نمودار زیر به نظر می رسد. (به عنوان مثال ، اولین ورودی گره ریشه را بگیرید: 1 1 4، که مطابق با b = 1با c = 1با d = 4.)
از طریق این دو نمودار ، اکنون باید درک سختی در ذهن خود از ساختار ذخیره سازی شاخص های کامپوزیت بر روی یک درخت B داشته باشیم.
بیایید ابتدا به جدول نگاه کنیم T1بشر برای کلید اصلی آن a، بیایید به طور موقت فرض کنیم که این یک عدد صحیح و به صورت خودکار است (PS: که چرا این یک عدد صحیح و به صورت خودکار است ، دو مقاله قبلی توضیحات مفصلی دارند ، بنابراین ما آنها را در اینجا تکرار نمی کنیم). رفیق از شاخص کلید اصلی برای حفظ شاخص و پرونده داده از طریق درخت B استفاده می کند. سپس ، هنگامی که ما یک شاخص کامپوزیت ایجاد می کنیم (b, c, d)، همچنین یک درخت شاخص ایجاد می کند ، که به همین ترتیب یک ساختار درخت B+ نیز هست. با این حال ، بخشی از داده های گره های برگ آن مقدار اصلی کلید را ذخیره می کند از سطحی که شاخص کامپوزیت در آن ساکن است (همانطور که در نمودار با پس زمینه بنفش در گره های برگ نشان داده شده است). در مورد اینکه چرا بخش داده های یک فهرست ثانویه مقدار اصلی کلیدی را ذخیره می کند ، در مقاله قبلی نیز مورد بحث قرار گرفت – اگر علاقه دارید یا هنوز آن را نخوانده اید ، احساس راحتی کنید.
خوب ، اکنون که وضعیت کلی را پوشش داده ایم ، بیایید از این دو نمودار استفاده کنیم تا چند نکته را توضیح دهیم.
در مقایسه با یک شاخص تک ستونی ، یک شاخص کامپوزیت فقط شامل چندین ستون دیگر است ، و همه آن ستون های ایندکس شده در درخت فهرست ظاهر می شوندبشر برای یک شاخص کامپوزیت ، موتور ذخیره سازی ابتدا بر اساس اولین ستون فهرست بندی شده مرتب می شود. همانطور که در نمودار نشان داده شده است ، بیایید به آخرین لایه درخت B+ نگاه کنیم – اگر فقط به اولین ستون فهرست بندی شده در گره های برگ (یعنی ردیف اول) نگاه کنیم ، مقادیر هستند 1, 1, 5, 12, 13, 13, 13، که به وضوح به ترتیب صعودی هستند. یعنی: اگر مقادیر ستون اول برابر باشد ، مرتب سازی توسط ستون دوم انجام می شود، و غیره – اینگونه است که درخت شاخص نشان داده شده در بالا ساخته شده است.
علاوه بر این ، اگر ما به ردیف های دوم و سوم، که نمایانگر c وت d ستون های شاخص کامپوزیت ، به ترتیب مقادیر آنها هستند:1, 5, 3, 14, 12, 16, 16 (برای c)4, 4, 6, 3, 4, 1, 5 (برای d).
در اینجا ، می بینید که این ردیف ها دیگر در حال افزایش استبشر با این حال ، وقتی مقادیر در **b** ستون برابر است، مقادیر موجود در c ستون تمایل به افزایش – به عنوان مثال ، چه زمانی b = 1، ما داریم 1, 5؛ کی b = 13، ما داریم 12, 16, 16بشر به همین ترتیب ، وقتی مقادیر در **c** ستون برابر است، d مقادیر ستون تمایل به افزایش خواهد داشت. این دقیقاً به همین دلیل است که ما باید از اصل پیشوند سمت چپ پیروی کنیمبشر
سازمان کلیدی چند ستونی مبتنی بر درخت B+
یک شاخص کامپوزیت چندین زمینه را در یک مقدار کلید واحد ترکیب می کند و یک درخت B+ را مطابق ترتیب تعریف شده در قسمت ها می سازد. به عنوان مثال ، برای یک شاخص کامپوزیت (b, c, d)، مقادیر کلیدی هر گره به ترتیب ترتیب داده می شود b → c → dبشر
-
گره های غیر برگ: مقادیر کلیدی کامپوزیت را ذخیره کنید (مانند ترکیبات
b, c, d) و نشانگر گره های کودک. -
گره های برگ: مقادیر کلیدهای جامع کامپوزیت را ذخیره کنید (
b, c, d) و مربوطه مقدار اصلی کلیدی (برای جستجوی جدول استفاده می شود).
مرتب سازی قوانین
-
نظم جهانی: همه گره ها توسط ستون اول (سمت چپ) سفارش داده می شوند. اگر مقادیر ستون اول برابر باشد ، از ستون دوم استفاده می شود و غیره. به عنوان مثال ،
(b=1, c=2)قبل می آید(b=1, c=3)بشر - دستور محلی: در همان سطح ، مقادیر کلیدی ذخیره شده در هر گره سفارش داده می شود ، که از کارآمد پشتیبانی می کند نمایش داده شدبشر
بهینه سازی ذخیره فیزیکی
- گره های غیر برگ در یک درخت B+ داده های واقعی را ذخیره نمی کنند. آنها فقط مقادیر کلیدی و نشانگرها را ذخیره می کنند. این اجازه می دهد تا هر صفحه دیسک (به عنوان مثال 16 کیلوبایت) مقادیر کلیدی بیشتری را در خود نگه دارد و ارتفاع درخت را کاهش می دهد (معمولاً 3-4 سطح برای پشتیبانی از ده ها میلیون ردیف کافی است).
- گره های برگ از طریق متصل می شوند لیست های دو برابر پیوند، ساخت اسکن کارآمد
در قلمرو بهینه سازی بانک اطلاعاتی ، ابزارهایی مانند Chat2DB در حال ظهور هستند تا به توسعه دهندگان در پالایش پرس و جوهای SQL خود کمک کنند. Chat2DB از AI برای تجزیه و تحلیل بیانیه های SQL و پیشنهاد پیشرفت هایی مانند استفاده از شاخص بهینه یا بازسازی پرس و جو استفاده می کند. در حالی که این جایگزینی برای دانش عمیق در مورد داخلی های پایگاه داده نیست ، اما به عنوان یک کمک ارزشمند در شناسایی پیشرفت های بالقوه عملکردی عمل می کند.
به وب سایت chat2db بروید
🙋 به انجمن Chat2DB بپیوندید
🐦 ما را در x دنبال کنید
📝 ما را در مورد اختلاف پیدا کنید
