سلام.

یک سری مطالعه و تست‌ها در مورد ایندکس‌ها روی دیتابیس انجام دادم و میخوام در موردشون توضیح بدم.

چرا روی فیلدها ایندکس میذاریم؟

برای اینکه پرفورمنس و سرعت response time کوئری ها، زمان خواندن اطلاعات رو بالا ببریم.

ایندکس‌ها باعث میشن که دیتابیس شما، درختی مرتب شده بر اساس اون فیلد مورد نظرو تولید کنه و زمانی که شما عملیات سلکت رو روی اون فیلد انجام میدهید، عملیات سلکت روی درخت مرتب شده صورت میگیرد و نتیجه با سرعت بالایی برمیگردد.

برای مثال، فرض کنیم جدولی داریم با یک میلیون رکورد و هیچ ایندکسی روی این جدول وجود نداره.

حالا کوئری میزنم به جدول که یوزر bidak رو سلکت کنه:

SELECT * FROM `users` WHERE `username` = 'bidak'

نتیجه کوئری بالا در زمان اندکی واسم نمایش داده میشه.

اما میخوام با دستور EXPLAIN ببینم که دقیقا چه تعداد رکوردی قراره پیمایش بشه:

EXPLAIN SELECT * FROM `users` WHERE `username` = 'bidak'

نتیجه:

mysql> EXPLAIN SELECT * FROM `users` WHERE `username` = 'bidak'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000000
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

همونور که میبینید possible_keys و key مقدار NULL دارند.

possible_keys ایندکس‌های موجود در کوئری رو نشون میده که کوئری میتونه ازشون استفاده کنه.

key ایندکسی رو نشون میده که کوئری ازش استفاده کرده.(یعنی برای جستجو از درختی استفاده کرده که برای اون ایندکس، مرتب و ساخته شده.)

اما قسمت rows که برابر ۱,۰۰۰,۰۰۰ میباشد. یعنی Mysql تخمین زده که یک میلیون ردیف(ستون username) در این جستجو باید بررسی بشه.

اما قسمت filtered درصدی رو نشون میده که جدول فیلتر میشه.(براساس شروط داخل کوئری)

بالاترین عدد ۱۰۰ درصد است که در این قسمت ۱۰% هست و همه چیز نشان از پرفورمنس بسیار پایین این جدول دارد.

حالا میام و یک ایندکس روی username میگذارم.(میتونید ایندکس بذارید یا میتونید unique بذارید):

mysql> EXPLAIN SELECT * FROM `users` WHERE `username` = 'bidak'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: username
          key: username
      key_len: 258
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

قسمت key برابر username هست و rows نشون میده برابر ۱ است و مقدار filtered هم برابر ۱۰۰%

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

با این مثال‌ها فهمیدیم که اهمیت ایندکس گذاری چیه و فهمیدیم اصلا ایندکس گذاری چه کاربردی دارد.

بنابراین اگر در پروژه، عملیات خواندن(select) زیادی روی یک فیلد داشتید، اون فیلد رو ایندکس گذاری کنید.

ایندکس‌ها عمدتا روی WHERE و ORDER BY در کوئری هاتون اتفاق میوفته.

نکته: بد نیست همیشه روی کوئریتون مثل الان از EXPLAIN استفاده کنید تا متوجه بشید که از یک ایندکس استفاده میشه یا نه. این مثالو ببینید:

mysql> EXPLAIN SELECT * FROM `users` ORDER BY username\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000000
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

میبینید که از ORDER BY استفاده کردم ولی از هیچ ایندکسی استفاده نشده، در صورتی که username ایندکس شده.

حالا به کوئری LIMIT رو اضافه میکنم:

mysql> EXPLAIN SELECT * FROM `users` ORDER BY username LIMIT 1;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: index
possible_keys: NULL
          key: username
      key_len: 258
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

* اگر LIMIT رو بدون ORDER BY username که ایندکس شده بیارید، مقدار rows برابر ۱ میلیون میشه.

نکات:

** ایندکس ها، سرعت اینسرت و آپدیت رو کند میکنن . سربار اضافی ایجاد میشه، بنابراین اگر وجود ایندکس ضروری نبود، ایجاد نکنید.

تست:

جدولی دارم با یک میلیون رکورد. در تستی که پایین اوردم، تمام داده‌های یک ستون رو آپدیت کردم.(روی ۱ میلیون رکورد)

روی جدول ۱ ایندکس PK وجود داشت: ۳۰ ثانیه عمل آپدیت طول کشید.

روی جدول ۲ ایندکس دیگه اضافه کردم: ایندفعه ۹۰ ثانیه طول کشید.

روی جدول ۷ ایندکس وجود داشت: نزدیک ۴۰۰ ثانیه طول کشید.

البته این تایم‌ها روی سیستم من انجام شده و روی PHPMYAdmin و در خود کنسول MySql ثانیه‌ها پایینتر بود. مثلا با ۷ ایندکس چیزی حدود ۷۰ ثانیه طول کشید و همینطور روی سرور پایینتر خواهد امد. اما چیزی که ازین تست میشد فهمید اینه که بیهوده ایندکس ایجاد نکنیم چون روی پرفورمنس دیتابیس، تأثیر مستقیم میذاره.

** ازونجایی که برای هر ایندکسی که ایجاد میکنید یک درخت تشکیل میشه(Type=BTREE)، بنابراین اگر روی یک فیلد خاص، عملیات سلکت رو زیاد استفاده نمیکنید، اون فیلد رو ایندکس نکنید. چون این کار باعث میشه فضای بیشتری از دیسک اشغال بشه. برای مثال فرضاً جدول شما ۱۰۰ مگ اطلاعات داره ولی شما ۱۰ تا ایندکس ایجاد کردید و دیتابیس شما شده ۱ گیگ. یا مثلا هر خونه از فیلد username شما ۸ بایت فضا رو اشغال میکنه و شما ۱ میلیارد رکورد دارید. فیلد username رو ایندکس میکنید و در نتیجه ۱,۰۰۰,۰۰۰,۰۰۰ * ۸ بایت یعنی برای این ایندکس به میزان ۸ گیگابایت فضا روی هارد دیسک نیاز دارید.

** یوقتی هست یه سناریو مثل این دارید که در جدول firstname و lastname دارید و چون روی جفتشون سلکت انجام میدید، میاید firstname رو ایندکس میکنید و lastname رو هم ایندکس میکنید. این دوتا فیلدو هر کدومو جدا ایندکس کردید. نتیجه ی سلکت رو ببینید:

mysql> EXPLAIN SELECT * FROM `users` WHERE firstname = 'Mohsen' AND lastname = 'Movahed'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: firstname,lastname
          key: firstname
      key_len: 53
          ref: const
         rows: 1
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.10 sec)

Mysql در هر کوئری تنها از یک ایندکس استفاده میکند. وقتی در کوئری چندین ایندکس بیاد، Mysql خودش یک ایندکس رو بر اساس اینکه شرایط کدوم مناسبتره انتخاب میکنه. خود همین تحقیق Mysql، سربار اضافی ایجاد میکنه.

اما من میام از Composite Index استفاده میکنم و یک ایندکس روی دو فیلد قرار میدم:

ADD INDEX `composite_index` (`firstname`, `lastname`) USING BTREE;

نتیجه:

mysql> EXPLAIN SELECT * FROM `users` WHERE first_name = 'Mohsen' and last_name = 'Movahed'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: composite_index
          key: composite_index
      key_len: 106
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

** اگر ستون Varchar ای رو خواستید ایندکس کنید، در نظر داشته باشید که محدودیتی روی طول آن وجود دارد و فقط ۷۶۷ بایت ایندکس میشه.

ازونجایی Mysql برای هر کاراکتر UTF-۸ سه بایت رو در نظر میگیره، بنابراین:

Varchar(255)

انتخاب مناسبیه. چون ستون مورد نظر، حداکثر ۷۶۵ بایت فضا رو اشغال خواهد کرد.(۲۵۵ * ۳ = ۷۶۵)

البته کاراکتر ست ای هم داریم که ۴ بایت را ساپورت میکند: utf۸mb۴

** مورد آخر هم اینو بیارم که Constraint Key‌ها علاوه بر اینکه محدودیت هایی روی فیلد ایجاد میکنن، ایندکس هم هستن. برای مثال Unique Key = محدودیت جلوگیری از درج مقادیر تکراری + ایندکس، در نتیجه چه بگیم Unique Key و چه بگیم Unique Index تفاوتی نمیکند.