מנועי מסדי נתונים של MySQL

הסבר מפורט על מנועי הטבלאות שיש ב-MySQL

במאמר הקודם למדנו על אינדקסים במסד נתונים מסוג MySQL. שם ציינתי שסוג האינדקס בדרך כלל תלוי ביכולת של המנוע שעומד מאחורי הטבלה. ועכשיו נשאלת השאלה – מה זה מנוע?

מנוע של טבלה הוא המנגנון שבאמצעותו נשמרים ומאוחזרים הנתונים מטבלה ספציפית במסד הנתונים שלנו. אחת היכולות הגדולות של MySQL הוא האפשרות לבחור את המנוע המתאים לנו עבור כל טבלה וטבלה. MySQL מגיע עם כמה מנועים מותקנים עליו וניתן להתקין עליו מנועים נוספים (מגרסה 5.1 ומעלה). לכל מנוע ומנוע יש את היכולות שלו ואת העוצמות והחולשות שלו. ההבדלים בין המנועים השונים הם בתחומים הבאים:

סוגי מידע

למרות שכל סוגי המנועים תומכים בסוגי המידע הבסיסיים שלמדנו ובמיוחד בטקסט ובמספרים, ישנם בחלק מהמנועים הגבלות על חלק מסוגי המידע. חשוב לבדוק לפני שמחליטים על מנוע מסוים את ההגבלות שלו.

נעילה (locking)

נעילה היא מניעה של תהליך (process) לבצע שינויים במסד הנתונים מכל מיני סיבות. הסיבות הפופולריות הן עדכון של המידע שהתהליך מנסה לגשת אליו. נעילה היא מאד חשובה לא רק עבור שלמות מסד הנתונים אלא גם עבור מהירות התגובה ועבור מספר האפליקציות שיכולות להשתמש במסד הנתונים בו זמנית. ישנן 3 סוגי נעילה:
נעילת טבלה (כל הטבלה ננעלת כאשר יש עדכון מסוים בתא באותה טבלה).
נעילת עמוד – נעילת כל המידע בטווח של 8Kb מהשורה שאותה אנו רוצים לעדכן/לשנות.
נעילת שורה – נעילת השורה שבה עורכים את השינויים בלבד.

טרנזקציה

יכולת הטרנזקציה היא חשובה עבור מהימנות המידע. אם אני מבצע העברה של מידע משורה לשורה, או הכנסה של מידע לשורה ספציפית, התהליך יסתיים בהצלחה רק לאחר שירוץ במלואו עם כל הפעולות שבו. אם משהו בתהליך נתקע/נעלם במהלך הפעולה, הפעולה לא תתבצע. התוצאה היא מסד נתונים מאד אמין.

על מנת לראות אילו מנועים מותקנים ב-MySQL, צריך להקליד את הפקודה הבאה:


show engines;

ומייד תוצג רשימת המנועים שמותקנים כרגע ב-MySQL. בדרך כלל תראו את המנועים הבאים:


+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking        |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine        |
| ARCHIVE    | YES     | Archive storage engine        |
| CSV        | NO      | CSV storage engine        |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables        |
| FEDERATED  | YES     | Federated MySQL storage engine        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables        |
| ISAM       | NO      | Obsolete storage engine        |
+------------+---------+----------------------------------------------------------------+

המנועים השונים ב-MySQL

MyISAM

מגבלות לסוגי מידע: אין.
מגבלות על האינדקסים: 64 אינדקסים לטבלה. אפשר להשתמש ב-BTREE, RTREE.
טרנזקציה: אין.
נעילה: טבלה.
זה מנוע ברירת המחדל של MySQL. רוב האפליקציות יחיו איתו בנוחות. המנוע הזה מעולה בקריאה של נתונים אך בכתיבת הרבה נתונים בקצב גבוה, עדיף להשתמש במנוע אחר או לחלופין לזכור שהוא מוגבל בכל הקשור להרבה פעולות כתיבה שנעשות בפרק זמן נמוך.

MEMORY (לשעבר HEAP)

מגבלות לסוגי מידע: אין BLOB או LONGTEXT.
מגבלות על האינדקסים: אין. משתמשים ב-BTREE, HASH.
טרנזקציה: אין.
נעילה: אין.

מדובר במנוע שיוצר טבלאות שנמצאות רק בזכרון ה-RAM של ה-MySQL. ברגע שה-MySQL עובר restart מסיבה מסוימת אז כל המידע שיש בטבלה נמחק (אך המבנה נשמר). כיוון שהטבלה נמצאת ב-RAM, הביצועים שלה מצויינים. מומלץ מאד להשתמש במנוע זה עבור מידע זמני שלא חשוב שהוא יישאר במסד הנתונים במשך זמן רב.

MERGE

מגבלות לסוגי מידע: כל הטבלאות שנכנסות אליו חייבות להיות ממנוע ISAM.
מגבלות על האינדקסים: לא רלוונטי.
טרנזקציה: אין.
נעילה: טבלה.

Merge הוא מנוע מיוחד שמאפשר יצירה של טבלה אחת המאחדת כמה טבלאות בעלות מבנה זהה (המבוססות על מנוע ISAM) ואחזור הנתונים מהן כאילו מדובר בטבלה אחת. מדובר במנוע שימושי מאד אם יש לכם מסיבה כלשהי טבלאות זהות שצריך לאחזר מכולן מידע. כך למשל אם יש לי 12 טבלאות זהות של רכישות, אחת לכל חודש ולעתים אני צריך לאחזר מהם

מידע – אז השימוש בטבלת merge שתאחד את כל 12 הטבלאות ותאפשר לי לאחזר מכולן מידע הוא אידיאלי יותר מ-12 שאילתות לכל טבלה ואז בדיקה נוספת של התוצאות.

Berkeley DB – BDB

מגבלות לסוגי מידע: אין.
מגבלות על האינדקסים: 31 אינדקסים לטבלה. מקסימום 1024 ביטים לשדה. אפשר להשתמש ב-HASH.
טרנזקציה: יש.
נעילה: עמוד.

BDB הוא מנוע המבוסס על hash והמפתחות למידע של ה-hash מאוחסנים באופן מאד יעיל ומאפשרים קבלת מידע מהירה באופן יוצא מן הרגיל. הטרנזקציה שלו מבוססת על נעילת עמוד ולפכיך יוצרת בעיה אם אני רוצה להכניס הרבה שורות. כאשר אני מכניס הרבה שורות, נעילת 8Kb מסביב לשורה שאני מכניס יוצרת מעצור רציני בהכנסת המידע. BDB מצטיין במיוחד באחזור מידע של מפתח ספציפי, דבר שעושה אותו אידיאלי עבור אחזור מידע שלא משתנה אבל צריך לקבל אותו במהירות – כמו למשל רשימת ערים או אפשרויות.

InnoDB

מגבלות לסוגי מידע: אין.
מגבלות על האינדקסים: אין. אפשר להשתמש ב-BTREE.
טרנזקציה: יש.
נעילה: שורה.

InnoDB הוא מנוע מוביל שמצטיין בקריאה ובכתיבה. רבים מעדיפים אותו על MyISAM אולי בעיקר בזכות היכולת שלו לבצע טרנזקציה והיכולת שלו לנעול שורה אחת בלבד בזמן כתיבה. דבר שמאפשר לו כתיבה מהירה הרבה יותר מזו של MyISAM.

מנוע Example הוא מנוע לימודי שלא משתמשים בו אלא על מנת לבנות מנוע משל עצמכם. Federated הוא מנוע שמאפשר לייבא טבלאות משרת MySQL מרוחק. המנוע נקבע לפי הטבלה המיובאת. CSV הוא לא ממש מנוע כיוון שהוא שומר את המידע כקובץ csv. המנוע Black hole הוא מנוע שמאפשר לנו לכתוב שאילתות שלא מתבצעות והוא קיים על מנת לשלוט על טבלאות אחרות שהן slaves שלו. כל אלו הם חשובים ומעניינים אך אינם ב-scope של המדריך הזה. יש לזכור שיש עוד המון מנועים שניתן להתקין לבד או אפילו לכתוב לבד,

יצירת טבלה עם מנוע מסוים שאינו מנוע של ברירת המחדל

כל מה שצריך לעשות זה להוסיף ENGINE= ואת שם המנוע לפקודת CREATE TABLE המוכרת לנו.


CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB

שינוי טבלה ממנוע אחד למנוע אחר

כאשר משנים טבלה קיימת יש לבדוק היטב את התאמת המנוע לטבלה ולאינדקסים הקיימים! אחרת מידע עלול ללכת לאיבוד. במידע ומנסים להגדיר מנוע שאינו מוגדר ב-MySQL,

המנוע שיוגדר יהיה מנוע ברירת המחדל של MySQL (שהוא MyISAM, כל עוד לא שינינו את ברירת המחדל).


ALTER TABLE mytable ENGINE = MyISAM

בדיקת המנוע של הטבלה

אם אנחנו רוצים לראות את המנוע של הטבלה, כל מה שעלינו לעשות זה להשתמש בפקודת SHOW TABLE STATUS WHERE NAME = שם הטבלה.


SHOW TABLE STATUS WHERE NAME = 'mytable';

התוצאה היא טבלה מפורטת שבה, בין היתר, יש פירוט של המנוע של הטבלה.

יצירת טבלה עם מנוע merge

ראשית ניצור שתי טבלאות MyISAM. אחת בשם t1 והשניה בשם t2. לשתי הטבלאות יש מבנה זהה.


CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20)) ENGINE=MyISAM;


CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20)) ENGINE=MyISAM;

אחרי שיצרנו את שתי הטבלאות אנו נכניס לתוכן נתונים:


INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

עכשיו למלאכת יצירת הטבלה עם מנוע ה-MERGE. כך נראית הפקודה שיוצרת את ה-MERGE:


CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

כפי שאנו רואים, ראשית אנו משתמשים בפקודת יצירת טבלה, בדיוק כמו כל טבלה רגילה. רק שאנו מקפידים על המבנה של הטבלאות שה-merge יאחד אותן. אחרי כן אנו מציינים את המנוע של הטבלה שהוא MERGE. ולאחר מכן פקודת UNION שמציינת את שמות הטבלאות שיאוחדו. INSERT_METHOD קובע מה באיזה מידע להשתמש במידה ויש כפילויות (MERGE לא מאפשר כפילויות) האם במידע של הטבלה הראשונה או בזה של הטבלה האחרונה.

אם נעשה SELECT לטבלה החדשה שיצרנו, נראה את הנתונים משתי הטבלאות האחרות. ועכשיו אפשר גם ליצור את האינדקסים שמתאימים לטבלה במנוע MERGE.

⚠️המדריכים כאן הם טעימה בלבד! יש ספר מקיף המלמד MySQL מרמת בסיס ועד רמה מתקדמת. הספר הדיגיטלי, שרלוונטי גם לכל מסדי הנתונים שעובדים עם SQL – זמין כאן כחלק מהפרויקט "ללמוד ג'אווהסקריפט בעברית" – זו הדרך הטובה ביותר ללמוד באופן מקיף MySQL.

עד כאן בנוגע לאינדקסים ומנועים. במאמר הבא אנו נלמד על מפתחות ראשיים ב-MySQL.