نمایه سازی MySQL برای عملکرد بالا: یک مرور کلی سطح بالا

برخی از مهندسان MySQL ممکن است آنها را سنگ بنای بهبود عملکرد در MySQL بنامیم: ایندکس ها سازه های داده ای هستند که اغلب برای یافتن سریع ردیف های مطابق با یک پرس و جو خاص استفاده می شوند.

یک سال پیش • 6 دقیقه بخوانید

اگر تا به حال با MySQL ، Percona Server یا MariaDB کار کرده اید ، احتمالاً تعجب کرده اید که چگونه می توانید عملکرد نمونه های پایگاه داده خود را بهبود بخشید. اگر در مورد این موضوع به دنبال مشاوره بوده اید ، احتمالاً در مورد فهرست ها شنیده اید.

شاخص ها در MySQL را می توان به چند نوع طبقه بندی کرد:

1. Balanced tree (B-Tree) indexes - the most frequently used type of index. This index type can be used together with search queries that use the =,>,>=,
2. شاخص های فضایی ( R-Tree) - می توان همراه با انواع داده های هندسی MySQL برای شاخص های اشیاء جغرافیایی استفاده کرد.
3. شاخص های هش - معمولاً فقط با نمایش داده شدگان استفاده می شود که از اپراتورهای = یا جستجو استفاده می کنند. خیلی سریع اما فقط در هنگام استفاده از موتور ذخیره سازی حافظه قابل استفاده است.
4. پوشش شاخص ها - شاخص هایی که تمام ستون های مورد نیاز برای پرس و جو را پوشش می دهند.
5. شاخص های خوشه ای - چنین ایندکس ها داده های ردیف را ذخیره می کنند. معمولاً کلید اصلی S یا اگر وجود نداشته باشد ، شاخص های منحصر به فرد.
6. شاخص های چند رنگ (کامپوزیت) - شاخص هایی که در ستون های مختلف ایجاد می شوند.
7. شاخص های پیشوند - چنین ایندکس ها فقط به شما امکان می دهد پیشوند یک ستون را فهرست کنید. از آنجا که این شاخص ها مقدار کامل یک ستون را نشان نمی دهند ، آنها اغلب برای صرفه جویی در فضا استفاده می شوند.

شاخص های درخت B و MySQL

در اینجا چند نمونه از نمایش داده شد که ممکن است از چنین شاخصی استفاده کنند:

1. SELECT * FROM demo_table WHERE column [ = |> |>= | <|
2. * را از demo_table انتخاب کنید که در آن ستون_ بین 100 تا 200 ؛
3. * را از demo_table انتخاب کنید که در آن ستون مانند "مقدار ٪" ؛

شاخص های مکانی و mysql

از شاخص های مکانی (که گاهی اوقات به عنوان شاخص های R-Tree نیز گفته می شود) برای دسترسی به اشیاء مکانی (جغرافیایی) استفاده می شود - برای استفاده از چنین شاخص هایی ، شما باید از توابع GIS ارائه شده توسط MySQL استفاده کنید: برخی از این توابع شامل mbrcontains ، mbrcovers یا یا mbrcovers یا یاmbrequals. چنین توابعی می تواند نشان دهد که آیا حداقل مستطیل های محدود کننده یک پارامتر حاوی ، پوشش یا برابر با حداقل مستطیل های محدود کننده پارامتر دیگر است.

برای افزودن چنین شاخصی به یک جدول ، از کلمه کلیدی مکانی مانند استفاده کنید:

1. ایجاد شاخص مکانی فضایی_idx در demo_table (demo_column) ؛یا
2. جدول DEMO_TABLE را اضافه کنید شاخص مکانی (فضایی_IDX) ؛

در اینجا چند نمونه از نمایش داده شد که ممکن است از چنین نوع شاخصی استفاده کنند:

1. mbrcontains ( @متغیر_1 ، @متغیر_2) را انتخاب کنید.
2. mbrwithin را انتخاب کنید ( @متغیر_2 ، @متغیر_1) ؛

به خاطر داشته باشید که قبل از استفاده از متغیرهایی مانند متغیر_1 یا متغیر_2 در هر ظرفیتی ، ابتدا باید آنها را تعریف کنید (پارامتر مقدار WKT نشان دهنده یک مقدار شناخته شده با متن است که نشان دهنده اشیاء هندسه است):

set @متغیر_1 = st_geomfromtext ('مقدار wkt') ؛

شاخص های هش و mysql

شاخص های هش در MySQL چند مزیت و مضرات مشخص دارند: اصلی ترین صعود شاخص های هش این است که آنها بسیار سریع هستند ، اما نکته منفی این است که آنها فقط برای مقایسه برابری استفاده می شوند و فقط روی موتور ذخیره سازی کار می کنند. همچنین از جستجوهای دامنه پشتیبانی نمی کند. برای ایجاد چنین شاخصی روی یک جدول ، از گزینه استفاده هش در انتهای پرس و جو خود استفاده کنید:

ایجاد index idx_name در demo_table (demo_column) با استفاده از هش ؛

دلیل سریع بودن شاخص های هش این است که شاخص های هش معمولاً بسیار جمع و جور هستند و کلید اصلی آنها فقط به عناصر در جدول هش دسترسی دارد. با این حال ، این بدان معنی است که شما نمی توانید از جستجوهای محدوده استفاده کنید - از شاخص های هش فقط باید با = یا اپراتورها استفاده شود.

پوشش شاخص ها در MySQL

هنگام کار با MySQL ، گاهی اوقات ممکن است با برخی از موارد گوشه ای روبرو شوید که ممکن است نیاز به استفاده از یک شاخص به روشی متفاوت داشته باشد - در اینجا جایی است که فهرست ها را وارد می کنند.

پوشش شاخص ها ، به عبارت ساده ، فهرست هایی هستند که تمام زمینه های مورد نیاز برای یک پرس و جو را برای اجرای موفقیت آمیز پوشش می دهند. بنابراین ، هنگامی که یک شاخص پوشش در حال استفاده است ، یک پرس و جو می تواند به جای دسترسی به دیسک ذخیره کننده دیسک I/O ، نتایج حاصل از خود شاخص را بازیابی کند. به عنوان مثال ، بگویید که شما یک پرس و جو مانند چنین دارید:

ستون_1 ، ستون_2 را از DEMO_TABL که در آن ستون_3 = 'مقدار' انتخاب کنید ، انتخاب کنید.

اگر از شاخصی استفاده می کنید که از قبل حاوی مقادیر ستون هایی است که می خواهید از طریق آن جستجو کنید ، MySQL به دیسک دسترسی نخواهد داشت - در عوض ، نتایج را مستقیماً از فهرست ارائه می دهد.

شاخص های خوشه ای در mysql

شاخص های خوشه ای به خودی خود یک نوع شاخص MySQL جداگانه نیستند - این یک رویکرد متفاوت برای ذخیره داده ها است. چنین شاخص ها داده های ردیف را ذخیره می کنند: اگر جدول شما دارای کلید اصلی است ، کلید اصلی شاخص خوشه ای است. اگر جدول شما کلید اصلی ندارد ، شاخص خوشه ای اولین شاخص منحصر به فرد با تمام ستون های کلیدی آن است که به عنوان تهی تعریف نشده است.

هنگامی که چنین شاخصی در حال استفاده است ، تمام ردیف های جدول بر اساس مقادیر کلید فهرست ذخیره و مرتب می شوند. از آنجا که ردیف ها فقط می توانند یک مرتبه مرتب داشته باشند ، جداول شما نمی توانند بیش از یک شاخص خوشه ای داشته باشند.

شاخص های خوشه ای به راحتی قابل تشخیص هستند - در MySQL ، کلیدهای طلایی معمولاً چنین شاخص هایی را نشان می دهند. در مقابل ، شاخص های درخت متعادل توسط کلیدهای ذکر شده در نقره نشان داده می شوند (در این حالت ، ستونی با "کلید طلایی" ستون با یک شاخص خوشه ای است در حالی که ستونی با "کلید نقره" ستون با شاخص درخت متعادل است):

یک شاخص خوشه ای و یک شاخص درخت متعادل

برای تعریف یک شاخص خوشه ای هنگام ایجاد یک جدول ، یک Auto_increment را به ستون خود اضافه کرده و آن را به عنوان کلید اصلی تعریف کنید. برای تعریف یک شاخص درخت متعادل هنگام ایجاد یک جدول ، یک فهرست به ستون خود اضافه کنید و ستونی را که می خواهید فهرست بندی کنید مشخص کنید (در صورت تمایل می توانید نام این فهرست را نیز مشخص کنید):

ایجاد جدول Arctype (Clustered_Index int (255) کلید اصلی NULL AUTO_INCREMENT ، DEMO_INDEX VARCHAR (255) نه تهی ، index idx_name (demo_index)) ؛

یک شاخص منحصر به فرد با تمام ستون های کلیدی خود که به عنوان تهی تعریف نشده است نیز می تواند یک شاخص خوشه ای باشد - همانطور که از نام آن پیداست ، در این حالت ، تمام مقادیر موجود در یک ستون با یک شاخص منحصر به فرد منحصر به فرد خواهند بود (یعنی هیچ نسخه ای وجود نخواهد داشت).

شاخص های کامپوزیت در mysql

شاخص کامپوزیت شاخصی است که دارای چندین ستون است. این کاملاً توضیحی است: اگر در چندین ستون شاخص داشته باشیم ، یک شاخص کامپوزیت داریم. در اینجا چگونگی افزودن چنین شاخصی به یک جدول ممکن است به نظر برسد (در این حالت ، C1 ، C2 و C3 نام ستون هستند):

ایجاد COMPOSITE_IDX در DEMO_TABLE (C1 ، C2 ، C3) ؛

شاخص های کامپوزیت در صورت برخورد با نمایش داده هایی که ممکن است از زمینه های ایندکس شده برای انتخاب ، پیوستن یا عملیات فیلتر استفاده کنند ، می توانند سودمند باشند. به عنوان مثال ، اگر می خواهید یک پرس و جو را مانند آن برآورده کنید ، یک شاخص کامپوزیت در ستون های C1 و C2 ممکن است مفید باشد:

* را از demo_table که در آن c1 = 5 و c2 = 10 ؛

در MySQL ، شاخص های کامپوزیت می توانند از 16 ستون تشکیل شوند ، اگرچه در نظر داشته باشید که MySQL از فهرست های چپ به راست استفاده می کند و برعکس نیست.

شاخص های پیشوند در mysql

بعضی اوقات ، ممکن است با موقعیت هایی روبرو شوید که ممکن است نیاز به فهرست کردن ستون های طولانی داشته باشید ، و شاخص های خود را بسیار بزرگ می کند. در این موارد ، شما همچنین می توانید به جای فهرست بندی کل مقدار ، چند شخصیت اول یک ستون را فهرست کنید - چنین شاخص هایی به شاخص های پیشوند گفته می شود.

برای ایجاد شاخص پیشوند روی یک جدول در MySQL ، از یک پرس و جو مانند SO استفاده کنید:

ایجاد PREFIX_IDX در DEMO_TABLE (COLLM_NAME (طول)) ؛

در این حالت ، prefix_idx نام فهرست است ، demo_table نام جدول است ، column_name نام ستون است و طول آن طول شاخص است.

هنگام استفاده از شاخص های پیشوند ، بسیار مهم است که به یاد داشته باشید که باید ستون ها را به گونه ای فهرست کنید که انتخاب مناسبی را به شما بدهد و در همان زمان فضا را ذخیره کند. بنابراین بیایید سعی کنیم این کار را انجام دهیم.

اول ، ما باید انتخاب ستون را پیدا کنیم - می توانیم یک پرس و جو مانند SO (مقادیر متمایز) را در ستون تقسیم می کنیم توسط همه مقادیر موجود در جدول - یک انتخاب شاخص ایده آل مقدار 1 است):

شمارش (ستون مجزا) / تعداد (*) را از demo_table انتخاب کنید.

حال باید سعی کنیم انتخاب شاخص پیشوند را تا حد امکان به آن مقدار نزدیک کنیم. برای انجام این کار ، ما می توانیم یک پرس و جو مانند این را صادر کنیم:

COUNT (سمت چپ مجزا (ستون ، 5)) / COUNT (*) را به عنوان Selectivity_5 انتخاب کنید.

مقداری که نمایش داده می شود ، انتخاب شاخصی است که پیشوند 5 کاراکتر را دارد (برای ارزیابی طول شخصیت های مختلف ، تنظیم پرس و جو).

خلاصه

در این پست وبلاگ ، ما برخی از موارد استفاده مربوط به شاخص های خاص در MySQL را پشت سر گذاشته ایم. انتخاب یک شاخص خوب برای مورد استفاده شما ممکن است در واقع یک دردسر باشد - از این گذشته ، شما گزینه های زیادی برای انتخاب دارید - اما هنگامی که نیازهای پروژه خود را درک کردید و از طرفین و پایین آمدن استفاده از گزینه های خاص شاخص در MySQL ، انتخابتناسب راست آسان تر می شود.

از این گذشته ، امیدوارم ، این پست وبلاگ بینش ارزشمندی را در مورد شاخص های MySQL برای شما فراهم کرده است - برای محتوای بیشتر از دنیای MySQL به شما بپیوندید.