درک شاخص های Mariadb: راهنمای مهندس آزمایش شده توسط نبرد

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

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

فهرست ها واقعاً چیست؟

به یک فهرست مانند کاتالوگ کارت در یک کتابخانه قدیمی فکر کنید (اگر به اندازه کافی جوان هستید که هرگز آن را ندیده اید ، یک سیستم نشانه گذاری بسیار سازمان یافته را تصور کنید). به جای اسکن هر کتاب برای یافتن “JavaScript: The Good Parts” ، کاتالوگ را بررسی می کنید ، مکان دقیق را دریافت می کنید و مستقیم به سمت آن می روید.

در MariaDB ، یک فهرست یک ساختار داده جداگانه است که نشانگرها را به ردیف های جدول واقعی ، که توسط مقادیر ستون فهرست بندی شده طبقه بندی می شود ، حفظ می کند. وقتی پرس و جو می کنید WHERE user_id = 12345، پایگاه داده می تواند به جای اسکن میلیون ها رکورد ، مستقیماً به ردیف های مربوطه پرش کند.

طعم های مختلف ایندکس

شاخص های B-Tree (The Workhorses)

شاخص های درخت B پیش فرض و رایج ترین نوع در Mariadb هستند. آنها درختان متعادل هستند که داده ها به ترتیب مرتب شده ذخیره می شوند و سریعاً نمایش داده شد.

CREATE INDEX idx_created_at ON orders (created_at);

وقتی می درخشند:

• جستجوهای برابری: WHERE id = 123
• نمایش داده شدگان: WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'
• سفارش توسط عملیات
• گروه بر اساس عملیات

GOTCHA در دنیای واقعی: من یک بار ساعت ها صرف بهینه کردن یک پرس و جو که به طور غیر قابل توضیح آهسته بود ، فقط برای کشف کسی که یک شاخص را ایجاد کرده بود LOWER(email) اما پرس و جو استفاده می کرد email مستقیم. این شاخص در آنجا بود ، فقط برای آن پرس و جو بی فایده بود.

شاخص های هش (شیاطین سرعت)

شاخص های هش از یک تابع هش برای ترسیم مقادیر به مکان های سطل استفاده می کنند. آنها برای مسابقات دقیق بسیار سریع هستند اما برای هر چیز دیگری بی فایده هستند.

CREATE INDEX idx_hash_user_id ON sessions (user_id) USING HASH;

ایده آل برای:

• برابری دقیق: WHERE user_id = 123
• ستونهای مربوط به سایت بالا با جستجوهای بیشتر برابری

برای:

• نمایش داده شدگان (آنها نادیده گرفته می شوند)
• تطبیق الگوی
• عملیات مرتب سازی

شاخص های کامپوزیت (چند ابزار)

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

CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

این شاخص می تواند به طور مؤثر کنترل کند:

• WHERE user_id = 123
• WHERE user_id = 123 AND status="completed"
• WHERE user_id = 123 AND status="completed" AND created_at > '2023-01-01'

اما برای:

• WHERE status="completed" (فقط ستون دوم)
• WHERE created_at > '2023-01-01' (فقط ستون سوم)

چگونه Mariadb از فهرست های داخلی استفاده می کند

روند تصمیم گیری بهینه ساز پرس و جو

هنگامی که شما یک پرس و جو را اجرا می کنید ، بهینه ساز Mariadb آنچه را که دوست دارم آن را “ارزیابی شاخص عالی” بنامم ، طی می کند. نگاه می کند:

1. فهرست های موجود در جداول پرسیده شده
2. کاردینال (مقادیر چقدر بی نظیر هستند)
3. آمار درباره توزیع داده ها
4. انتخاب پرس و جو (چند ردیف انتظار بازگشت دارد)

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

EXPLAIN SELECT * FROM users WHERE age = 25 AND city = 'New York';

بهینه ساز ممکن است بین:

• اسکن جدول کامل
• فهرست age
• فهرست city
• شاخص کامپوزیت در (city, age)
• ادغام فهرست با استفاده از هر دو شاخص تک ستونی

اسکن فهرست در مقابل اسکن جدول

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

نقطه اوج؟ معمولاً حدود 10-30 ٪ جدول. اگر پرس و جو شما به 40 ٪ از ردیف ها احتیاج دارد ، Mariadb ممکن است این شاخص را کاملاً پرش کند زیرا خواندن کل جدول به طور متوالی سریعتر از تندرست بین فهرست و جدول برای میلیون ها ردیف است.

الگوهای عملکرد شاخص در دنیای واقعی

سندرم “همه چیز آهسته است”

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

علائم:

• نمایش داده شدی که با ردیف های 10K سریع بود با 1 متر+ ردیف خزنده است
• استفاده بالای CPU در ساعات کاری
• کاربران از “برنامه کند بودن برنامه” شکایت می کنند

رویکرد راه حل:

1. استفاده کردن SHOW PROCESSLIST برای یافتن نمایش داده های در حال اجرا
2. EXPLAIN آهسته
3. به دنبال اسکن میز روی میزهای بزرگ باشید
4. فهرست های مناسب را اضافه کنید

مشکل بیش از حد شاخص

شاخص های بیشتر همیشه به معنای عملکرد بهتر نیست. من پایگاه داده ها را با 15+ شاخص در یک جدول واحد که فقط 3 مورد استفاده شده است ، حسابرسی کرده ام.

هزینه های پنهان:

• هر درج/به روز رسانی/حذف همه فهرست ها را حفظ می کند
• فضای ذخیره سازی بیشتر
• زمان پشتیبان گیری طولانی تر
• تعمیر و نگهداری شاخص بالای سر

قانون شست من: اگر یک شاخص توسط هر پرس و جو در ورود به سیستم Querd Query در طی یک ماه استفاده نمی شود ، در نظر بگیرید که آن را رها کنید.

نکات بهینه سازی شاخص عملی

1. استفاده را به نفع خود توضیح دهید

EXPLAIN FORMAT=JSON SELECT * FROM orders 
WHERE customer_id = 123 AND status = 'pending'
ORDER BY created_at;

به دنبال:

• "type": "ALL" (اسکن جدول – معمولاً بد)
• "Extra": "Using filesort" (مرتب سازی بدون فهرست)
• "Extra": "Using temporary" (ایجاد جدول دما)

2. ترفند فهرست پوشش

یک فهرست پوشش شامل تمام ستون های مورد نیاز یک پرس و جو است:

-- Query only needs id, customer_id, status, total
CREATE INDEX idx_covering ON orders (customer_id, status, id, total);

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

3. شاخص های پیشوند برای ستونهای رشته ای

برای ستون های رشته ای طولانی ، شما اغلب نیازی به فهرست بندی کل مقدار ندارید:

-- Index only the first 10 characters
CREATE INDEX idx_email_prefix ON users (email(10));

این به خوبی برای دامنه های ایمیل ، URL ها یا هر رشته ای که پیشوند از انتخاب خوبی برخوردار باشد ، کار می کند.

اشتباهات فهرست بندی مشترک (و نحوه جلوگیری از آنها)

1. ستون پیچیده شده

-- Bad: Index on created_at won't be used
WHERE YEAR(created_at) = 2023

-- Good: Rewrite to use the index
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'

2. پیشرو در کارتهای وحشی مانند

-- Index won't help
WHERE email LIKE '%@gmail.com'

-- Index can help
WHERE email LIKE 'john%'

3. انواع داده های ناسازگار

-- If user_id is INT, this won't use the index
WHERE user_id = '123'

-- This will
WHERE user_id = 123

سلامت شاخص نظارت

Meriadb مدرن ابزارهای عالی برای تجزیه و تحلیل شاخص فراهم می کند:

-- Find unused indexes
SELECT * FROM information_schema.INDEX_STATISTICS 
WHERE CARDINALITY = 0;

-- Check index cardinality
SHOW INDEX FROM your_table;

-- Analyze index usage
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';

خط پایین

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

1. ساده شروع کنید – اضافه کردن شاخص ها بر اساس الگوهای پرس و جو واقعی ، نه فرضیه های فرضی
2. نظارت فعال – از سیاهههای مربوط به پرس و جو و طرح عملکرد استفاده کنید
3. کاملاً آزمون – تغییرات شاخص می تواند اثرات غیر منتظره ای داشته باشد
4. به طور مرتب خانه تمیز – فهرست های بلااستفاده را به صورت دوره ای حذف کنید

به یاد داشته باشید ، بهترین استراتژی شاخص موضوعی است که با برنامه شما تکامل می یابد. آنچه برای ردیف های 100K کار می کند ممکن است برای 100 متر ردیف کار نکند ، و با پیچیده تر شدن سؤالات شما ، ممکن است نیاز به تجدید نظر داشته باشد.

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

نمایه سازی مبارک ، و ممکن است سؤالات شما همیشه در میلی ثانیه بازگردد!

آیا با چالش های جالب توجه در MariaDB روبرو شده اید؟ داستانهای جنگ خود را در نظرات زیر به اشتراک بگذارید.