چگونه فهرست ها واقعاً کار می کنند – جامعه dev

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

به جدول پایگاه داده خود فکر کنید ، شاید Users جدول ، به عنوان مجموعه گسترده ای از سوابق (ردیف های SQL ، اسناد در NOSQL). این سوابق فقط در حافظه شناور نیستند. آنها باید سریال شوند و به طور مداوم روی دیسک (HDD یا SSD) ذخیره شوند.

The Slow Lane: زندگی بدون شاخص (اسکن کامل جدول)

بیایید نحوه برخورد داده ها به دیسک را تجسم کنیم. ما را تصور کنید Users جدول:

CREATE TABLE Users (
    ID INT PRIMARY KEY, -- Let's say 4 bytes
    Name VARCHAR(60),   -- 60 bytes
    Age INT,            -- 4 bytes
    Bio TEXT,           -- 128 bytes (can be large!)
    TotalBlogs INT      -- 4 bytes
);

بر اساس این اندازه ها ، هر سابقه کاربر می گیرد 4 + 60 + 4 + 128 + 4 = 200 bytes هنگام ذخیره

اکنون ، دیسک ها داده های بایت را نمی خوانند. آنها می خوانند بلوک (یا صفحات). اندازه بلوک مشترک 4KB است ، اما برای سادگی ، بیایید از مثال متن استفاده کنیم 600 بایت در هر بلوکبشر

اگر هر رکورد 200 بایت باشد و یک بلوک 600 بایت باشد ، می توانیم متناسب باشیم 600 / 200 = 3 کاربر را در یک بلوک دیسک واحد ثبت می کند.

بیایید بگوییم ما Users جدول دارای 100 ردیف است. برای ذخیره این موارد ، ما نیاز داریم:
100 rows / 3 rows/block = 33.33 blocks
از آنجا که نمی توانیم کسری از بلوک داشته باشیم ، به آن نیاز داریم 34 بلوک برای ذخیره کل جدول به صورت متوالی.

اکنون ، بخش بحرانی: دیسک I/O کند است! خواندن بلوک از دیسک سفارشات بزرگی نسبت به خواندن از حافظه است.

این پرس و جو را در نظر بگیرید:

SELECT * FROM Users WHERE Age = 23;

بدون شاخص در Age، چگونه پایگاه داده این کاربران را پیدا می کند؟ چاره ای جز انجام یک اسکن جدول کامل:

1. بلوک 1 را بخوانید (حاوی ردیف های 1-3) به حافظه.
2. بررسی کردن Age برای هر یک از این 3 ردیف. مسابقات را حفظ کنید. بلوک را دور بیندازید (از نظر مفهومی).
3. بلوک 2 را بخوانید (حاوی ردیف های 4-6) به حافظه.
4. بررسی کردن Age برای ردیف های 4-6. مسابقات را حفظ کنید. بلوک را دور بیندازید.
5. … برای همه تکرار کنید 34 بلوکبشر

حتی اگر فقط دو کاربر داشته باشند Age = 23، پایگاه داده هنوز مجبور به خواندن و پردازش شد همه 34 بلوک فقط برای یافتن آنها اگر خواندن یک بلوک 1 ثانیه فرضیه را می گیرد (طبق تصویر متن – در واقعیت بسیار سریعتر است ، اما اصل آن را حفظ می کند) ، این پرس و جو طول می کشد 34 ثانیهبشر این دردناک است

The Fast Lane: معرفی شاخص ها

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

بیایید یک فهرست در ما ایجاد کنیم Age ستون:

CREATE INDEX idx_users_age ON Users(Age);

این idx_users_age شاخص ممکن است چیزی شبیه به این مفهومی باشد (اغلب در ساختارهای پیچیده تری مانند درختان B ذخیره می شود ، اما بیایید ساده شود):

خصوصیات کلیدی:

1. کوچکتر: هر ورودی فهرست فقط حاوی مقدار ایندکس شده است (Age، 4 بایت) و اشاره گر (ID، 4 بایت) = 8 بایتبشر بسیار کوچکتر از رکورد کامل 200 بایت!
2. مرتب شده: این شاخص توسط مقدار فهرست بندی شده طبقه بندی می شود (Age). این برای جستجوی سریع بسیار مهم است.

حال ، بیایید اندازه این شاخص را برای جدول 100 ردیف ما محاسبه کنیم:
100 entries * 8 bytes/entry = 800 bytesبشر

با استفاده از بلوک های 600 بایت ما ، چند بلوک انجام می دهد فهرست نیاز؟
800 bytes / 600 bytes/block = 1.33 blocks -> 2 بلوکبشر

چگونه پرس و جو اجرا می شود با فهرست

بیایید دوباره همان پرس و جو را اجرا کنیم:

SELECT * FROM Users WHERE Age = 23;

این بار ، برنامه ریز پرس و جو بانک اطلاعاتی فهرست را می بیند Age و از آن استفاده می کند:

1. فهرست را اسکن کنید: به جای اسکن جدول اصلی ، اسکن می کند کوچک فهرست از آنجا که این شاخص طبقه بندی شده است ، می تواند به سرعت تمام ورودی ها را پیدا کند Age = 23بشر در بدترین حالت (با استفاده از مدل اسکن ساده متن) ، کل فهرست را می خواند. چند بلوک است؟ عادل 2 بلوکبشر
   • (توجه به بهینه سازی: بانکهای اطلاعاتی واقعی با استفاده از درختان B حتی باهوش تر هستند و اغلب نیازی به خواندن همه بلوک های شاخص ندارند ، اما بیایید برای مقایسه به مدل ساده بچسبیم.)
2. شناسه های ردیف مربوطه را شناسایی کنید: از اسکن شاخص ، بانک اطلاعاتی نشانگرهایی را برای ردیف ها پیدا می کند Age = 23بشر در مثال ما ، این می شود Row ID 1 وت Row ID 4بشر
3. ردیف های واقعی: اکنون ، با داشتن شناسه های ردیف خاص ، پایگاه داده می رود مستقیما به بلوک های جدول اصلی حاوی آن ردیف ها.
   • ردیف شناسه 1 در بلوک 1 است. بلوک 1 را بخوانید (1 بلوک بخوانید).
   • Row ID 4 در بلوک 2 است. بلوک 2 را بخوانید (1 بلوک بخوانید).
4. نتایج بازگشت: داده های کامل را برای ردیف های 1 و 4 جمع آوری کرده و آنها را برگردانید.

کل بلوک های دیسک خوانده شده در این پرس و جو فهرست بندی شده:
2 blocks (for index scan) + 1 block (for row 1) + 1 block (for row 4) = **4 blocks**بشر

بازپرداخت: مقایسه عملکرد

• بدون فهرست: 34 بلوک خوانده شده (34 ثانیه فرضی)
• با فهرست: 4 بلوک می خواند (4 ثانیه فرضی)

این یک است بهبود عملکرد 8x در این مثال ساده ، فقط با اضافه کردن یک فهرست! در سناریوهای دنیای واقعی با جداول بزرگتر و نمایش داده های پیچیده تر ، تفاوت می تواند حتی چشمگیرتر باشد (100x ، 1000x یا بیشتر!).

چرا این برای شما مهم است؟

1. کاهش دیسک I/O: این پیروزی اصلی است. شاخص ها به طور چشمگیری تعداد بلوک های خوانده شده از ذخیره دیسک آهسته را کاهش می دهند.
2. Targeted Reads: به جای اسکن کورکورانه همه چیز ، پایگاه داده از این فهرست برای مشخص کردن استفاده می کند دقیقاً کدام داده ها به آن نیاز دارند.
3. برنامه ریزی پرس و جو: درک کنید که برنامه ریز پرس و جو پایگاه داده شما کوشش کردن برای استفاده از شاخص ها برای ستون های خود WHEREبا JOIN ONبا ORDER BY، و گاهی GROUP BY بندها
4. انتخاب فهرست: ایندکس ها در ستون هایی که مقادیر متنوع هستند (انتخاب بالا) به بهترین وجه کار می کنند. نمایه سازی gender ستون با “M” ، “F” ، “دیگر” ممکن است کمتر از نمایه سازی یک مؤثر باشد email ستون.
5. تجارت: ایندکس ها رایگان نیستند! آنها فضای دیسک را مصرف می کنند و کمی کند می شوند INSERTبا UPDATEوت DELETE عملیات به دلیل اینکه شاخص نیز باید به روز شود. فهرست های خود را با عاقلانه بر اساس الگوهای خوانده شده خود انتخاب کنید.

پایان

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

دفعه بعد که با یک پرس و جو آهسته روبرو می شوید ، فقط سخت افزار بیشتری را در آن قرار ندهید. تجزیه و تحلیل برنامه پرس و جو (EXPLAIN ANALYZE) ، خود را بررسی کنید WHERE وت JOIN بندها ، و اطمینان حاصل کنید که از فهرست های مناسب در ستون های سمت راست برخوردار هستید. کاربران شما (و سرورهای شما) از شما تشکر می کنند.
اعتبار: Arpit Bhayani