Chapter10 Practical Database Design And Tuning

10-1

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

บทที่ 10* การออกแบบและการปรับจูนฐานขอมูลใชงานจริง Practical Database Design and Tuning วัตถุประสงค 1. 2. 3. 4. 5.

10.1

เพื่อใหมีความเขาใจในการออกแบบฐานขอมูลเพื่อการใชงานจริง สามารถระบุแฟคเตอรที่เกี่ยวของและสงผลตอการออกแบบฐานขอมูลเชิงกายภาพได สามารถตัดสินใจออกแบบ Index ได เพื่อใหมีความเขาใจและสามารถทําการดีนอรมัลไรซเซชั่นของฐานขอมูลได เพื่อใหมีความเขาใจในการปรับจูน Query

การออกแบบฐานขอมูลเพื่อการใชงานจริง ในบทนี้จะเปนการยายจากแนวคิดในการออกแบบจากเพียงทฤษฏีใหสามารถนําไปสูการออกแบบเพื่อใชงานจริงได โดยในปจจุบันมีการนําฐานขอมูลไปใชอยางแพรหลาย มีผูใชงานเกี่ยวของกับฐานขอมูลหนึ่งๆ เปนจําหนวนมากและ อาจจะปรับเปลี่ยนความตองการจากเพียงฐานขอมูลขนาดเล็กเปนฐานขอมูลขนาดใหญ และมีความซับซอนมากขึ้น ดังนั้นในการออกแบบนั้นจะตองคํานึงถึงการนําไปใชงานจริงดวยเชนกัน ในบทนี้จึงจะมีการกลาวถึง การออกแบบ ฐานขอมูลทางกายภาพในฐานขอมูลเชิงกายภาพหรือ Physical Database Design รวมไปถึงลักษณะทั่วไปของ การปรับจูนฐานขอมูลในระบบเชิงสัมพันธ (Tuning) เพื่อใหสามารถนําไปใชงานจริงไดอยางมีประสิทธิภาพมาก ยิ่งขึ้น

10.2

แฟคเตอรที่มีอิทธิพลตอการออกแบบฐานขอมูลเชิงกายภาพ แฟคเตอรที่มีอิทธิพลตอการออกแบบฐานขอมูลเชิงกายภาพ อาจประกอบดวยกันหลายสวน สามารถที่จะสรุปได ดังนี้ แฟคเตอร ที่ 1 การวิเคราะการสืบคนฐานขอมูล (Database Query) และ รายการ (Transaction) • ในการสืบคนขอมูล (Query) มีขอมูลดังตอไปนี้ที่มีความจําเปนที่จะตองคํานึงถึงในการทําการสืบคน หรือ เรียกใช Query ในแตละครั้ง - ไฟลที่เขาไปสอบถาม - แอทตริบิวตซึ่งกําหนดเงื่อนไขการเลือกเพื่อสอบถาม - แอทตริบิวตซึ่งกําหนดเงื่อนไขรวม หรือเงื่อนไขที่ลิงคไปยังหลายตารางหรือออปเจ็คเพื่อสอบถาม - แอทตริบิวตซึ่ง Query จะทําการเรียกคา หมายเหตุ: แอทตริบิวตในขอที่ 2 และ3 เปนตัวเลือกสําหรับเปนบทนิยามของการเขาถึงโครงสราง • ในการเขาไปอัพเดท (Update) รายการ (Transaction) หรือทําการ (Operation) ใด ๆ ที่มีความจําเปนที่ จะตองคํานึงถึงขอมูลดังตอไปนี้ - ไฟลที่จะอัพเดท - ชนิดของการทํางานของแตละไฟล (แทรก, อัพเดท หรือลบ) - แอทตริบิวตของเงื่อนไขการเลือกเพื่อการลบ หรืออัพเดทที่กําหนด - แอทตริบิวตซึ่งคาจะถูกเปลี่ยน ดวยการอัพเดท

* อางอิงจากบทที่ 16 ของเอกสารอางอิง [1]

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

10-2

หมายเหตุ: แอทตริบิวตในขอที่ 3 เปนตัวเลือกสําหรับเปนบทนิยามของการเขาถึงโครงสราง สวนแอทตริ บิวตในขอที่ 4 เปนตัวเลือกสําหรับหลีกเลี่ยงการเขาถึงโครงสราง แฟคเตอร ที่ 2 การวิเคราะห ความถี่คาดหมาย (Expected Frequency) ของการเรียกใช (Invocation) การสืบคน (Query) และ รายการ หรือทรานแซคชัน (Transaction) • ขอมูลความถี่คาดหมาย ตามดวยขอมูลแอทตริบิวตที่รวบรวม ในแตละ Query และ Transaction ที่ใชเพื่อ คํานวณรายการสะสมของความถี่คาดหมาย ที่ใชในการ Query Transaction ทั้งหมด • แสดงเปนความถี่คาดหมายในการใชแอทตริบิวตแตละตัวในแตละไฟลเปนการเลือกแอทตริบิวต หรือแอทตริ บิวตรวม ในการ Query และ Transaction • กฎ 80-20 ประมาณ 80% ของการดําเนินการเปนการทํารายการใหเพียงแค 20% ของ Query และ Transaction

แฟคเตอร ที่ 3 การวิเคราะหเวลาบังคับของ Query และ Transaction ขอจํากัดดาน Performance หรือ ประสิทธิภาพ สงผลตอการจัดลําดับความสําคัญ หรือ Priority ของแอท ตริบิวต ซึ่งเปนตัวเลือกสําหรับการเขาถึงเสนทาง • การเลือกแอทตริบิวตที่ใชโดย Query และ Transaction และขอจํากัดดานเวลา หรือ Time Constraints กลายเปนตัวเลือกที่มีลําดับความสําคัญสูงขึ้นในการเปนตัวเลือกในการเขาถึงโครงสรางขั้นตน •

แฟคเตอร ที่ 4 การวิเคราะหความถี่คาดหมายของการอัพเดท (update operation) • ควรกําหนดจํานวนครั้งนอยสุดในการเขาถึงเสนทางสําหรับไฟลที่อัพเดทบอย แฟคเตอร ที่ 5 การวิเคราะหขอจํากัดที่มีความเปนเอกลักษณ (Uniqueness constraints) ของแอทตริบิวต • เสนทางที่จะใชในการเขาถึง (Access path) จะตองถูกกําหนดไวสําหรับแอทตริบิวตทุกตัวที่เปนตัวเลือกที่ สําคัญ หรือ เซตของแอทตริบิวต ที่เปนพื้นฐานสําคัญ หรือที่เปน primary key หรือ ขอจํากัดที่จะทําใหเปน เอกลักษณ (Unique)

10.3

การตัดสินใจในการออกแบบฐานขอมูลเชิงกายภาพ 10.3.1 การตัดสินใจในการออกแบบเกี่ยวกับอินเด็กซ (Index) ในการตัดสินใจเกี่ยวกับการจัดทํา indexing จะมีขอควรพิจารณาดังตอไปนี้ • แอทตริบิวตใดจะทําหนาที่เปนดัชนี หรือ Index - Index สามารถที่จะสรางจากแอททริบิวตมากกวาหนึ่งตัวได • จําเปนหรือไมที่จะตองสราง Clusters index - ในหนึ่ง table จะมีไดอยางมากเพียง index หนึ่งตัวที่จะสามารถทําหนาที่เปน primary index หรือ เปน Clustering Index - ถาแอทตริบิวตทําหนาที่เปน key ตองทําการสราง primary index ดวย - ถาแอทตริบิวตไมใช key จะตองทําการสราง Clustering Index ดวยเชนกัน • จําเปนหรือไมที่จะตองใช Hash index แทน Tree index • จําเปนหรือไมที่จะใช Dynamic hashing สําหรับไฟล - จะใชสําหรับไฟลที่มีความเปลี่ยนแปลงบอย (Volatile) หรือ มีการเปลี่ยนแปลงหรือลดขนาดลงอยาง ตอเนื่อง

10.4

ดีนอรมาไลเซชัน (Denormalization) การดีนอมอลไรซ ถือเปนการตัดสินใจการออกแบบเพื่อเพิ่มความเร็วในการสืบคน (Query) โตยที่การทํานอรมาไล เซชันนั้นมีวัตถุประสงคหลักคือการจัดแยกแอทตริบิวตที่เกี่ยวของกันในเชิงตรรกะ (logically related) ใหอยูในรูป ของตาราง เพื่อลดการซ้ําซอน (Redundancy) และหลีกเลี่ยงการอัพเดทอะนอรมาลี ซึ่งเปนสาเหตุใหเกิดการ ทํางานที่เปน overhead เพื่อรักษาความถูกตองของฐานขอมูล สวนวัตถุประสงคของการทําดีนอรมาไลเซชันนั้น คือ

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

10-3

การปรับปรุงประสิทธิภาพของ query และ transaction ที่เกิดขึ้นเปนประจํา (โดยทั่วไป นักออกแบบจะรวมเขาไป ในตารางแอทตริบิวต ที่จําเปนตองใชในการตอบ Query หรือการสรางรายงาน ดังนั้นจึงควรหลีกเลี่ยงการรวมเขา กับตารางอื่น) หรืออาจมองไดวาการดีนอรมาไลเซชันเปนการ Trade off ระหวางประสิทธิภาพของการอัพเดท และ การทํา Query ตัวอยาง TEACH(STUDENT, COURSE, INSTRUCTOR) {{STUDENT, COURSE} --> INSTRUCTOR, INSTRUCTOR --> COURSE} • •

10.5

ดีตอมโพส แบบ Lossless TEACH ใหเปน T1(STUDENT, INSTRUCTOR) และ T2(INSTRUCTOR, COURSE) ซึ่งอยูในรูปของ BCNF เราตองเก็บ T1, T2, และ TEACH (3NF) เพื่อที่จะสามารถหาคําตอบวามี COURSE ใดที่ STUDENT ลงเรียนจาก INSTRUCTOR ใด โดยไมตอง join T1กับ T2 กอน

การปรับจูนฐานขอมูลของฐานขอมูลเชิงสัมพันธ 10.5.1

การปรับจูน (Tuning)

การปรับจูน คือกระบวนการในการแกไข/ปรับแตงการออกแบบฐานขอมูลทางกายภาพอยางตอเนื่อง โดยเฝา ติดตามการใชทรัพยากร (Resource Utilization) เชนเดียวกับกระบวนการภายใน DMBS เพื่อคนหาคอขวด (Bottleneck)เชน การแยงขอมูล หรืออุปกรณเดียวกัน โดยมีเปาหมายเพื่อใหแอพลิเคชั่นทํางานเร็วขึ้น เพื่อลด เวลาตอบสนองใน Query และ Transaction และเพื่อปรับปรุงปริมาณงาน Throughput โดยรวมของ Transaction

โดยใน DBMS นั้น จะมีการจัดเก็บสถิติ เชน • ขนาดของตาราง • จํานวนของ Distinct Value ในแตละคอลัมน • จํานวนครั้งที่แตละ Query หรือ Transaction ถูกเรียกใชภายในระยะเวลาแตละ Interval • จํานวนครั้งสําหรับเฟสที่แตกตางกันของ Query หรือ Transaction Processing นอกเหนือจากนั้น ยังมีสถิติที่ไดจากการติดตาม หรือ Monitoring • สถิติของการจัดเก็บ (Storage statistics) • สถิติของประสิทธิภาพอินพุตเอาทพุตและ อุปกรณ (I/O and device performance Statistics) • สถิติในการดําเนินการ Query และ Transaction (Query/transaction processing statistics) • สถิติเกี่ยวกับการ Locking และ บันทึกรายการ (Logging) ที่เกี่ยวของ (Locking/logging related •

statistics) สถิติดัชนี (Index statistics)

10.5.2 ปญหาที่ควรพิจารณาในการปรับจูน ปญหาที่พบบอยไดแก จะหลีกเลี่ยงปญหาของ Excessive lock contention อยางไร จะลด Overhead ของการบันทึกรายการ (Logging) และ การกําจัด (Dumping) ขอมูลที่ไมจําเปนอยางไร จะจัดขนาดของบัฟเฟอร (Buffer) และตารางเวลาของการประมวลผลใหเหมาะสมที่สุดอยางไร จะจัดสรรทรัพยากร เชน ดิสก แรม และโปรเซสเซอร เพื่อใหมีประสิทธิผลอยางไร

• • • •

10.5.3 การปรับจูนดัชนี ในการพิจารณาปรับจูนดัชนี หรือ Tuning indexes นั้น ควรคํานึงถึงสิ่งตางๆ ดังนี้

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย •

•

10-4

เหตุผลในทํา Tuning indexes - Query อาจจะใชเวลาในการรันนานหากไมมี index - Query อาจจะทําใหเกิด overhead เปนจํานวนมาก เนื่องจาก index อาจจะชี้ไปยังแอททริบิวตที่มี การเปลี่ยนแปลงของความถี่แบบ undergoes frequent ทางเลือกในการปรับจูนดัชนี - ทิ้ง หรือ/และสรางใหม - เปลี่ยนดัชนีที่ไมเปนกลุมไปเปนดัชนีกลุม(และอื่นๆ) - ปรับสรางใหม (Rebuild)

10.5.4 การปรับจูนการออกแบบฐานขอมูล ในการปรับจูนการออกแบบฐานขอมูล จําเปนตองระบุขอกําหนดกระบวนการเปลี่ยนอยางตอเนื่อง เปลี่ยนแปลงใน แผนผังแนวคิดหากจําเปน และสะทอนการเปลี่ยนแปลงเหลานั้นเขาไปใน conceptual schema และการออกแบบ ทางกายภาพ (Physical design) การเปลี่ยนแปลงที่เปนไปไดตอการออกแบบฐานขอมูลไดแก • ตารางปจจุบันอาจรวมเขาดวยกันโดยการทําดีนอรมาไลเซชั่น หากมีแอทตริบิวตจากสองตารางหรือมากกวาที่ ตองใชพรอมกันบอย และสําหรับตาราง (TABLE) ที่ไดมานั้น อาจจะมีทางเลือกในการออกแบบแบบอื่นๆ ที่ ทุกตารางนั้นอยูในรูปของ 3NF หรือ BCNF จึงสามารถปรับเปลี่ยนอันใดอันหนึ่ง หรือสลับ TABLE ได • ความสัมพันธในรูป R(K, A, B, C, D, …) โดยที่ K เปนชุดของแอทตริบิวตสําคัญ ที่อยูในรูปของ BCNF สามารถแบงเก็บอยูในหลายตารางที่อยูในรูปของ BCNF ดวย โดยสําเนา K ที่สําคัญซ้ําลงในแตละตาราง ตัวอยางเชน แยก R ออกเปน ตาราง R1(K, A, B) R2(K, C, D), R3(K, …). เรียกวา การพารธิชัน แนวตั้ง (Vertical Partitioning) • แอทตริบิวตจากตารางหนึ่งอาจถูกจัดเก็บในตารางอื่นได ถึงจะทําใหเกิดปญหาการซ้ําซอน (Redundancy) หรือเกิดความผิดปกติ (Anomalies) • เลือกใข Horizontal Partitioning เมื่อจําเปน

10.5.5 การปรับจูน Query สิ่งแสดงถึงวาตองการปรับจูน Query สามารถแสดงไดดังตอไปนี้ Query สงผลใหมีการเรียกใช หรือ เขาถึง (access) ดิสกมากเกินไป แผนการ Query แสดงถึง Index ที่เกี่ยวของที่ไมไดถูกใช

• •

กรณีทั่วไปที่ตองการการปรับจูน Query นั้น มีดังตอไปนี้ • Query optimizers หลายๆ ตัวไมไดใช index หากมีสมการทางคณิตศาสตร การเปรียบเทียบเชิงตัวเลข (numerical comparisons) ของแอททริบิวตที่ตางขนาดกัน หรือการเปรียบเทียบคานัล (NULL comparisons) และการเปรียบเทียบซับสตริง (Sub-string comparisons) อยูดวย • Index ที่มักจะไมคอยถูกใชสําหรับ nested queries ที่ใชคําสั่ง IN; ตัวอยางเชน

• •

•

SELECT SSN FROM EMPLOYEE WHERE DO IN (SELECT DNUMBER FROM DEPARTMENT WHERE MGRSSN = ‘333445555’); ในการใชคําสั้ง DISTINCT นั้น อาจเกิดปญหาความซ้ําซอน (Redundancy) ซึ่งเปนสิ่งที่เราควรเลี่ยง โดย

ที่ผลลัพธจะไมเปลี่ยนแปลง ควรหลีกเลี่ยงการใชงานตารางผลชั่วคราว (temporary result table) ที่ไมจําเปน โดยการยุบรวม Query เชาดวยกันถึง ยกเวนแตวาความสัมพันธแบบชั่วคราว (temporary relation) นั้นๆ จําเปนสําหรับการ ประมวลผลในลําดับถัดไป (Intermediate processing) หากเปนไปไดวามีหลายทางเลือกในการ join ใหเลือกใช clustering index และหลีกเลี่ยงการเปรียบเทียบ string

•

อยางไรก็ตาม ในบางกรณี การใช correlated queries และ temporaries ก็อาจดีก็ได ตัวอยางเชน SELECT SSN

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

10-5

FROM EMPLOYEE E WHERE SALARY = SELECT MAX (SALARY) FROM EMPLOYEE AS M WHERE M.DNO = E.DNO

แยกออกเปน 2 Query ============================================ SELECT MAX (SALARY) AS HIGHSALARY, DNO INTO TEMP FROM EMPLOYEE GROUP BY DNO ============================================ SELECT SSN FROM EMPLOYEE, TEMP WHERE SALARY = HIGHSALARY AND EMPLOYEE.DNO = TEMP.DNO • • •

ลําดับของตารางจากคําสั่ง FROM อาจจะกระทบตอกระบวนการ Join Query optimizer บางตัว เมื่อทํางานในลักษณะ Nested Quesires นั้น อาจมีประสิทธิภานดอยกวาการ ทํา Un-nested queries มีแอพลิเคชันจํานวนมากที่ใชการสรางมุมมอง (VIEW) เพื่อกําหนดขอบเขคของขอมูลที่สนใจสําหรับแอพลิเค ชันนั้นๆ แด บางครั้งมุมมองเหลานี้กลายเปนผลไมดี หรือ overkill ตัวเอง

10.5.6 แนวทางการปรับจูน Query เพิ่มเติม •

การ Query ดวยเงื่อนไขการเลือกหลายทางเลือก (Multiple selection) ที่เชื่อมดวย OR อาจจะไมเรียก Query Optimizer ขึ้นมาเพื่อใช Index ดังนั้น Query แบบนั้น ควรถูกแบงออกมา และ แสดงในรูปแบบ Union Query โดยแตละตัวจะมีเงื่อนไขในแอทตริบิวตที่จะทํานําไปสู Index ที่จะใชงาน ตัวอยางเขน SELECT FNAME, LNAME, SALARY, AGE FROM EMPLOYEE WHERE AGE > 45 OR SALARY < 50000 SELECT FNAME, LNAME, SALARY, AGE FROM EMPLOYEE WHERE AGE > 45 UNION SELECT FNAME, LNAME, SALARY, AGE FROM EMPLOYEE WHERE SALARY < 50000

• • • •

เงื่อนไข NOT อาจจะเปลี่ยนใหอยูในรูปของเชิงบวก Embedded SELECT block อาจจะแทนดวย join หากมีการใข Equality Join ระหวางสองตาราง จะไดวา ขอบเขตหรือ range ที่กําหนดใหกับแอทตริบิวตที่ใช oin ในตารางหนึ่ง อาจซ้ํากับอีกตารางหนึ่งก็ได การใขเงื่อนไข WHERE ที่ดี ควรจะใชเพื่อเรียกใข Index จากหลายคอลัมน ดังตัวอยาง SELECT REGION#, PROD_TYPE, MONTH, SALES FROM SALES_STATISTICS WHERE REGION# = 3 AND ((PRODUCT_TYPE BETWEEN 1 AND 3) OR (PRODUCT_TYPE BETWEEN 8 AND 10) SELECT REGION#, PROD_TYPE, MONTH, SALES FROM SALES_STATISTICS WHERE (REGION# = 3 AND ((PRODUCT_TYPE BETWEEN 1 AND 3)) OR (REGION# = 3 AND (PRODUCT_TYPE BETWEEN 8 AND 10)

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

10-6

แบบฝกหัด 1. 2. 3. 4.

จากแฟคเตอรที่สงผลในการออกแบบฐานขอมูล จงทําการวิเคราะหแฟคเตอรในแตละตัวที่ไดระบุไวในบทนี้ อธิบายความสําคัญของ Clustering Index ในการปรับจูน ในการตัดสินใจออกแบบเกี่ยวกับ index มีขอควรระวังเกี่ยวกับแอททริบิวตอยางไรบาง อธิบายสถิติที่เกี่ยวของในการปรับจูนฐานขอมูล