نمایه سازی شیرجه عمیق: شاخص های کامپوزیت و آزمایش های پرس و جو واقعی

ما با شاخص های کامپوزیت و نمایش داده های صفحه بندی ، فهرست بندی های MySQL را تحت فشار قرار دادیم. این چیزی است که ما کشف کردیم.
برچسب ها: [MySQL, Indexing, Database Performance, Experiments, Composite Index]

🧠 recap سریع (برای خوانندگان جدید)

در آخرین وبلاگ ما ، ما نشان دادیم که چگونه اضافه کردن یک شاخص ساده در یک ستون ، یک پرس و جو اساسی را در 80،000 ردیف از 420ms تا 8msبشر

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

🧪 آزمایش آزمایش

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

🔧 ساختار جدول (مانند گذشته):

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  age INT,
  city VARCHAR(255),
  created_at DATETIME
);

🧪 آزمایشات و نتایج

🧪 آزمایش 1: شاخص کامپوزیت روشن (cityبا age)

پرس و جو:

SELECT * FROM users WHERE city = 'Mumbai' AND age = 30;

بدون فهرست:

• زمان اعدام: 430 میلیون پوند
• ردیف های مورد بررسی: 80،000 پوند (اسکن کامل جدول)

با فهرست:

CREATE INDEX idx_city_age ON users(city, age);

• زمان اعدام: 10 میلیون پوند
• ردیف های مورد بررسی: 400 پوند

✅ نتیجه: بهبود شدید هنگام فیلتر کردن توسط هر دو قسمت به همان ترتیب شاخص.

🧪 آزمایش 2: سفارش معکوس در پرس و جو (هنوز هم با استفاده از همان شاخص)

پرس و جو:

SELECT * FROM users WHERE age = 30 AND city = 'Mumbai';

• زمان اعدام: 200 میلیون پوند
• mysql انجام داد نه از شاخص کامپوزیت به طور کامل استفاده کنید
• ردیف های مورد بررسی: 14000 پوند

⚠ بینش: شاخص های کامپوزیت فقط به طور کارآمد کار می کنند چپ به راستبشر

🧪 آزمایش 3: مرتب سازی با فهرست

پرس و جو:

SELECT * FROM users WHERE city = 'Mumbai' ORDER BY age;

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

✅ شاخص در صورت گنجاندن ستون مرتب سازی به فیلتر و سفارش کمک می کند به ترتیب فهرستبشر

🧪 آزمایش 4: پرس و جو صفحه بندی

پرس و جو:

SELECT * FROM users WHERE city = 'Mumbai' ORDER BY created_at LIMIT 50 OFFSET 10000;

• بدون فهرست: 500 میلیون پوند
• با فهرست روشن (cityبا created_at): 20 میلیون پوند

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

📊 جدول خلاصه

💡 یادگیری

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

🔮 آینده

در پست بعدی ، ما بررسی خواهیم کرد:

• پوشش شاخص ها
• انتخاب چند شاخص
• استراتژی های ادغام شاخص

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

action سؤالات نمایه سازی یا موارد لبه ای که می خواهید آزمایش کنید؟ یک نظر را رها کنید!