במאמר הקודם למדנו על נורמליזציה של מסד נתונים. אחרי שנירמלנו את מסד הנתונים שלנו, עלינו לחשוב על מיקום האינדקסים. למרות שאינדקסים אינם חלק מהמודל של מסד הנתונים היחסי, חשוב מאד להבין אותם לעומק כיוון שהבנה מעמיקה שלהם יכולה להשפיע באופן דרסטי על מהירות מסד הנתונים.
אינדקס כשמו כן הוא – בדיוק כמו אינדקס שמופיע באנציקלופדיה או בספר עיוני. אם אני רוצה למצוא ערך מסוים באנציקלופדיה, אני יכול לסרוק את כל הספרים (הם אפילו מסודרים לפי א’-ב’) או לשלוף את כרך האינדקס, לדפדף במהירות (כרך האינדקס קטן הרבה יותר מהאנציקלופדיה), למצוא את הערך הנכון ולראות באיזה כרך ועמוד הוא נמצא ואז לשלוף את הכרך הנכון. בדרך כלל פעולה כזו לוקחת הרבה פחות זמן מסריקת האנציקלופדיה כולה וכל מי שיצא לו לחפש באנציקלופדיה אמיתית מנייר יודע את זה.
אינדקסים במסד נתונים הם עותק של חלק קטן מהטבלה, עדיף שדה קצר. כאשר אנו יוצרים אינדקס, אנו בעצם יוצרים עותק של כל הטור במקום פיזי נפרד בדיסק. כאשר אנו ניגשים לטבלה, תהליך בשם query optimizer מחליט אם לגשת לטבלה או להשתמש באינדקס שיצרנו. אם השימוש באינדקס יהיה מהיר יותר, ה-query optimizer ישתמש בו.
על מנת שהאינדקס יהיה יעיל, אסור להפוך יותר מדי טורים בטבלה לאינדקס ואסור שהאינדקס יכלול שדות ארוכים. כך למשל, אם נבחר שדות שהם מסוג LONGTEXT כאינדקס שלנו, האינדקס יהיה לא יעיל וה-query optimizer לא יבחר בו או שהוא כן יבחר בו והתוצאה תהיה בעייתית עבורנו.
סוגי אינדקס
ב-MySQL ישנם מספר סוגי אינדקס שניתן להשתמש בהם, תלוי במנוע שמריץ את הטבלה.
Btree
Btree הוא ראשי תיבות של Binary Tree – עץ שיכולים להיות לו שני nodes ובסופו של דבר שני leafs. מדובר בפתרון מצויין עבור Database שמתעדכן באופן תדיר. מדובר בפורמט של אינדקס שהוא דיפולטיבי ב-MySQL וכל המנועים השונים משתמשים בו. הוא יעיל במיוחד כאשר מבצעים בו השוואה באמצעות האופרטורים < , <=, =, >=, >, <>, !=, ו-BETWEEN.
Rtree
דומה ל-Binary Tree רק שהוא הרבה יותר טוב במציאת מידע מרחבי. מידע מרחבי הוא סוג של מידע של MySQL שלא הוסבר במדריך זה וכולל מידע הקשור ל-Geographic Information Systems.
Hash
Hash לוקח את הטור ומעביר אותו דרך פונקצית Hash. כך למשל טקסט יכול לעבור דרך פונקציה שתמיר אותו למספר. Hash יהיה מאד מהיר כאשר אנו מחפשים ערך ספציפי או ערך שגדול או שווה לערך הספציפי. אך הביצועים שלו יפלו מ-Btree כאשר מדובר על מציאת טווח.
בדרך כלל מקובל להשתמש ב-BTREE בשני המנועים הפופולריים של MySQL.
יצירה ושינוי אינדקסים ב-MySQL
כיצד יוצרים אינדקס?
לאחר יצירת הטבלה, אנחנו צריכים להחליט על 3 דברים – על איזה טור להפעיל את האינדקס, מה שם האינדקס, מה הסוג של האינדקס. לצורך הדוגמא, כך יוצרים אינדקס לטבלת my_costumer לשדה costumer_id. סוג האינדקס הוא BTREE והוא UNIQUE.
כל אינדקס יכול להיות UNIQUE – ועדיף שיהיה כיוון שאינדקס שכל הערכים שלו ייחודיים מקל על השליפה.
CREATE UNIQUE INDEX costumer_id_index USING BTREE on my_costumer(costumer_id);
יצירת האינדקס אורכת לעתים זמן ונועלת את הטבלה לקריאה ולכתיבה – לשיקולכם כאשר מוסיפים אינדקס לשרת פרודקשן.
בדיקת אינדקסים שקיימת בטבלה קיימת
לאחר שיצרנו את האינדקס, אנחנו בטח נרצה לראות אותו (ואולי גם אינדקסים אחרים שנוצרו). פקודת SHOW INDEX FROM ושם הטבלה יסייעו לנו לראות את האינדקסים שיש על טבלה מסוימת.
SHOW INDEX FROM my_costumer;
תתקבל טבלה מסודרת עם כל האינדקסים הקיימים.
כיצד מוחקים אינדקס מטבלה קיימת?
מחיקת אינדקס היא פשוטה ביותר, באמצעות SHOW INDEX FROM אנו יכולים לראות את שם האינדקס ולמחוק אותו באמצעות DROP INDEX ON עם שם הטבלה.
למשל:
DROP INDEX costumer_id_index ON my_costumer;
במאמר הבא אנו נלמד על סוגי מנועים שונים ב-MySQL ואז נדע גם מתי להשתמש ב-BTREE או ב-HASH.