Optimizing Query Syntax

Optimizing Query Syntax คือ การปรับแต่งคำสั่ง Query เพื่อให้ได้คำสั่งที่สามารถเรียกข้อมูลออกมาได้เร็วที่สุด หากมีการเขียนคำสั่งที่ไม่ดี อาจจะทำให้มีการใช้ระยะเวลานานในการ Query ผลลัพธ์ที่ต้องการออกมา ซึ่งมีการเขียนได้หลายแบบตามตรรกะของผู้ใช้งาน

ตัวอย่าง

แบบ A :                select  f1,f2,f3  from my_table

แบบ B :                select * from my_table

การเลือก Column เฉพาะที่ต้องการใช้งานออกมาย่อมเร็วกว่าการเลือกทุก Column ออกมาใช้งาน

Database Design

การออกแบบระบบฐานข้อมูลเป็นอีกจุดสำคัญหนึ่งที่ไม่ควรมองข้าม หากมีการออกแบบฐานข้อมูลที่ดี ก็เป็นเรื่องง่ายในการทำการค้นหาข้อมูล หรือเพิ่ม แก้ไข หรือลบ เนื่องจากรูปแบบของฐานข้อมูลมีหลายรูปแบบ โดยส่วนใหญ่แล้วมักนิยมแบบ Relational Database แต่อาจมีการพิจารณาฐานข้อมูลรูปแบบอื่นเข้าไปตามความเหมาะสมของข้อมูลของเราได้เช่นกัน

การออกแบบฐานข้อมูลมี 4 แบบ

1. Relational       เป็นตารางฐานข้อมูลซึ่งมีความเกี่ยวข้องสัมพันธ์กัน แทนที่จะจัดเก็บข้อมูลทั้งหมดทุกเรื่องไว้ในตารางเดียว (ซึ่งเรียกกันว่า แบบ Flat Database) ให้จัดแบ่งข้อมูลออกเป็นตารางใหม่หลายตารางแยกออกจากกัน โดยมีกำหนดให้แต่ละตารางมี Field ซึ่งเกี่ยวข้องสัมพันธ์กันใช้เป็นตัวอ้างอิงหรือที่เรียกว่า Primary key และ Foreign key นั่นเอง ส่วนใหญ่จะใช้หลักการ Normalization เพื่อสะดวกในการ insert update and delete

 

2. Hierarchical     เป็นฐานข้อมูลที่นำเสนอข้อมูลและความสัมพันธ์ระหว่างข้อมูลในรูปแบบของโครงสร้างต้นไม้ (tree structure) เป็นโครงสร้างลักษณะคล้ายต้นไม้เป็นลำดับชั้น ซึ่งแตกออกเป็นกิ่งก้านสาขา เพื่อต้องการให้เป็นฐานข้อมูลที่สมารถกำจัดความซ้ำซ้อน (Data Redundancy) โดยใช้แนวความคิดของโปรแกรมที่ชื่อว่า Generalized Update Access Method (GUAM) ซึ่งข้อดีของการออกแบบฐานข้อมูลแบบ Hierarchical คือ มีระบบโครงสร้างซับซ้อนน้อยที่สุด และเข้าใจง่าย เหมาะสำหรับงานที่ต้องการค้นข้อมูลแบบมีเงื่อนไข หรือเรียงลำดับต่อเนื่อง และยังสามารถป้องกันข้อมูลได้ดี เพราะต้องอ่านจาก ข้อมูล ที่root ก่อน

3. Object Oriented DB    ฐานข้อมูลเชิงวัตถุ จัดเก็บทั้งข้อมูลและชุดคำสั่งไว้ด้วยกัน จึงสามารถใช้งานร่วมกันได้โดยอัตโนมัติ ทำให้ฐานข้อมูลชนิดนี้มีประสิทธิภาพในการจัดเก็บและจัดการ แต่มีการนำมาใช้งานน้อยกว่าฐานข้อมูลเชิงสัมพันธ์ เนื่องจากมีความยุ่งยากซับซ้อนมากกว่า

 

4. Network DB   ข้อมูลในฐานข้อมูลแบบนี้สามารถมีความสัมพันธ์กันแบบใดก็ได้ ไม่ว่าจะเป็นแบบหนึ่งต่อหนึ่ง หนึ่งต่อกลุ่มหรือกลุ่มต่อกลุ่มโดยโครงสร้างของฐานข้อมูลแบบเครือข่ายก็เป็น Tree เช่นเดียวกับฐานข้อมูลแบบลำดับชั้น แต่จะเป็น Tree ที่ดูซับซ้อนมากขึ้นเพื่อรองรับความสัมพันธ์แบบกลุ่มต่อกลุ่ม

 

 

การทำ Performance Tuning

ขั้นตอนต่างๆในการทำ Performance Tuning

Performance Baseline Measurement

ก่อนการทำ Tuning จะต้องมีการวัดค่าพื้นฐานต่างๆของระบบก่อน ไม่ว่าจะเป็น cpu percent processing, disk queue, query time, no. of page scan หรือค่าอื่นๆที่เกี่ยวข้อง เพื่อที่จะใช้เป็นตัวเปรียบเทียบหลังจากที่ได้ทำการTuning ไปแล้วว่า ได้ผลมากน้อยเพียงใด

เครื่องมือที่ใช้ปกติก็จะมี Performance Monitor กับ SQL Profiler

รูปที่ 1 ตัวอย่างหน้าตาเครื่องมือ Performance Monitor ของ Windows 7

Performance Monitor

Performance Monitor เป็นเครื่องมือที่มีประสิทธิภาพสูง แต่ฟรี ที่มีอยู่ใน windows อยู่แล้ว โดยส่วนใหญ่แล้วเรามักจะใช้ในการตรวจสอบ CPU, Disk, Memory, Network และอื่นๆ สำหรับโปรแกรมเมอร์บางท่านอาจจะยังไม่เคยใช้ เพราะว่าเครื่องมือตัวนี้ Admin หรือ DBA มักจะเข้ามาใช้เพื่อ Monitor ระบบอยู่แล้ว

ในตอนนี้ผมจะลองแสดงให้เห็นวิธีการใช้งานเพื่อหา Bottle Neck อย่างง่ายๆ ดูนะ

เริ่มจากเปิดโปรแกรม Performance Monitor ขึ้นก่อน โดยโปรแกรมนี้จะอยู่ใน Administrative Tools ซึ่งปกติจะมีอยู่ใน Windows ทุกเวอร์ชั่น

รูปที่ 2 ภายในเมนู Control Panel

รูปที่ 3 ภายในหัวข้อ Administrator Tool

รูปที่ 4 แสดง Performance Monitor

                จากรูปที่ 4 จะแสดง count ของ % Processor Time ซึ่งเป็น counter ที่ใช้วัดการทำงานของ CPU ว่ามีการทำงานเป็นอย่างไร โดยปกติแล้วเราจะดูที่ค่า Average ถ้าค่า average ไม่เกิน 80% ก็ถือว่าปลอดภัย แต่ถ้าเกินกว่านี้ แสดงว่า CPU ทำงานไม่ทัน อาจจะแก้ปัญหาโดยการเพิ่ม CPU ให้มาหรือเปลี่ยน CPU

                นอกจากนั้น เราสามารถที่จะเพิ่ม counter ได้อีกโดยการคลิกขวาเพื่อเลือกเมนู Add Counter ดังรูปที่ 5

รูปที่ 5 แสดงการเลือกเมนู Add Counter

รูปที่ 6 แสดงหน้าจอการเลือก Counter

                จากรูปที่ 6 สามารถอธิบายหน้าจอได้ดังนี้

         1.   Select counters from computer – ระบุชื่อ computer name ที่ต้องการจะตรวจสอบ

         2.   หมวดหมู่ของ counter และเมื่อกดจะแสดงรายชื่อ counter ของหมวดนั้นๆ

         3.   instance of selected object  – instance ของ counter ที่เลือก ตัวอย่างเช่น ถ้าเราเลือก 

               %C1 Time  ในช่อง select instance of selected object จะแสดงจำนวน  CPU ทั้งหมดที่อยู่ในเครื่อง

                      จากรูปด้านบน มี CPU 0,1 แสดงว่ามี  CPU 2 ตัว แต่ถ้าเครื่องนี้มีหลายตัวก็อาจจะแสดงเป็น 0, 1, 2, 3

                      ก็แสดงว่าในเครื่องนี้มี CPU ทั้งหมด 4 ตัว โดยเราอาจจะขอดูเฉพาะตัวใดตัวหนึ่งหรือดูทั้งหมดก็ได้

Performance Tuning

                ในการทำงานเกี่ยวกับฐานข้อมูลไม่ว่าที่ใดก็ตามย่อมต้องการความรวดเร็วในการตอบสนองมากที่สุด ยิ่งฐานข้อมูลมีจำนวนข้อมูลมาก การที่จะทำงานกับข้อมูลนั้นก็จะใช้เวลานานมากขึ้นทั้งในเรื่องการดึงและการจัดการข้อมูล จึงเป็นสาเหตุหลักที่ทำให้มีวิธีการปรับแต่งฐานข้อมูลเพื่อที่จะแก้ไขปัญหาเหล่านี้ โดยใช้ต้นทุนน้อยที่สุดแต่ะเกิดผลลัพธ์มากที่สุด

ทำไมต้องมีการทำ Performance Tuning

โดยส่วนใหญ่แล้วจะมีอยู่ 3 กรณี ได้แก่

o   เมื่อต้องการให้ระบบมีความเร็วในการ Access ข้อมูลภายใน Database เพิ่มขึ้น

o   เมื่อต้องการให้ระบบสามารถรับ Load ของ Request ที่เข้ามาได้มากขึ้น ตลอดจนถึง Connection ด้วย

o   เมื่อต้องการให้ระบบมีความเสถียรภาพที่ดีขึ้น ไม่ Hang กรณีเจอ Peak load

สาเหตุที่เกิดปัญหา

o   Design Database ไม่ดี หรือดีเกินไป

      ในการออกแบบฐานข้อมูล จะมีการ Normalize Table แตกตารางเป็นหลายตาราง ให้มีความซ้ำซ้อนน้อยที่สุด เพื่อให้สะดวกต่อการ Insert, Update, Delete แต่เมื่อเราต้องการดึงข้อมูล (Select) จากตารางต่าง ๆ ขึ้นมาใช้ ทำให้ต้อง Join, Subquery หรือแม้กระทั่ง Union จากหลายตารางมากเกินไป เพื่อให้ได้ผลลัพท์ที่ต้องการ ซึ่งทำให้ช้าเกินกว่าที่รับได้ ถ้าข้อมูลมีจำนวน Record มากขึ้นเกินระดับหนึ่ง

o   Application Tuning

      สาเหตุอันดับหนึ่งของความช้า เนื่องมากจากการเขียน SQL ไม่ถูกต้อง ทำให้เกิดการ Full Table Scan (ไล่ดูข้อมูลทั้งตารางทีละ Record ตั้งแต่ 1 ไปถึง 100), การจัดเรียงข้อมูล (Sorting) เพื่อ Matching หรือแสดงผลทุกครั้งที่ Query, Join หลาย Table เกินไป, ดึงข้อมูลมากเกินกว่าที่จะแสดงผลให้ User ดูได้ในคราวเดียว, Caching, การใช้ Like, การใช้ OR, Union, Order by, Distinct, Group by, Implicit Cast ที่ไม่เหมาะสม, ไม่ใช้ Bind Variable

o   Memory Tuning

      ระบบจัดการฐานข้อมูล (Database Management System : DBMS) ต้องการหน่วยความจำ (Memory) ที่เพียงพอ เพื่อใช้งานการทำงานต่าง ๆ เช่น shared_pool, buffer cache, log buffer ตัวอย่างเช่นเราสามารถดูอัตรา Buffer Hit Ratio ถ้าค่ายิ่งมากยิ่งเร็ว แสดงว่าข้อมูลที่เราต้องการถูก Load อยู่บน Memory แล้วไม่ต้องไปหาจากใน Harddisk ใหม่ แต่ถ้าค่าน้อยแสดงว่า Memory น้อยเกินไป หรือมีปัญหาเรื่องการ Design อื่น ๆ ทำให้ DBMS ไม่สามารถใช้ข้อมูลที่อยู่บน Memory ได้ต้องไปดึงจาก Harddisk มาใหม่

o   I/O Tuning

     ระบบฐานข้อมูลไม่สามารถทำงานได้เร็ว ถ้าข้อมูลอยู่บน Disk ที่ช้า หรือ Network ที่ช้า และควรมีการบริหารจัดการ Data File, Log File, Temp File และ Block Size ที่เหมาะสม

o   Eliminate Database Contention:

     ใช้ Tools ตรวจหา Lock, Latch และ Wait ภายใน Database และกำหนด Schedule Batch Job ให้ไม่กระทบกับงานขณะ Peak Working Hours.

o   OS Tuning

      ตรวจสอบ และ Tune OS, CPU, I/O, Memory Utilization โดยประสานงานกับผู้ดูแลระบบ และศึกษาเกี่ยวกับ OS Platform ที่ใช้อยู่ เช่น Windows, Linux, Unix, Solaris

ประโยชน์จากการทำ Performance Tuning ทำให้มีการเรียกใช้ทรัพยากรของระบบได้สูงสุดและมีประสิทธิภาพมากยิ่งขึ้น

                สามารถเข้าถึงข้อมูลได้อย่างรวดเร็วมากยิ่งขึ้น

                หากมีการ tuning ที่ดีแล้ว อาจไม่จำเป็นที่จะต้องปรับเปลี่ยน Hardware ภายนอกเลย ทำให้ลดต้นทุนค่าใช้จ่ายได้อีกด้วย

 

    แนวทางการปรับแต่ง

                การ tuning database นั้นมี 2 แนวทางใหญ่ๆเลยคือ

    Hardware Tuning

การปรับแต่งทางด้านอุปกรณ์ Hardware ของระบบ เช่น การเปลี่ยน CPU หรือ RAM เป็นต้น เป็นวิธีที่ง่ายที่สุดและเป็นวิธีที่ต้องเสียเงินเยอะกว่าวิธีอื่นเช่นกัน ถ้าหากมีการออกแบบระบบฐานข้อมูลมาไม่ดี การปรับเปลี่ยน Hardware ใหม่นอกจากจะสิ้นเปลืองค่าใช้จ่ายแล้วอาจไม่สามารถช่วยอะไรเลยได้เช่นกัน

    Software Tuning

คือการปรับแต่งภายในระบบเซิฟเวอร์ที่มีการติดตั้งฐานข้อมูลนั้นๆไว้ ทั้งการปรับแต่งค่าพารามิเตอร์ต่างๆของตัวฐานข้อมูลและการปรับแต่งโค้ดภายในอีกมากมาย ใช้ระยะเวลาในการดำเนินการค่อนข้างนาน แต่ทำให้ไม่ต้องสูญเสียค่าใช้จ่ายโดยไม่จำเป็น เป็นการลดต้นทุนในระดับหนึ่ง โดยรายงานฉบับนี้จะเน้นไปทางด้าน Software Tuning เท่านั้น

http://www.mediafire.com/?5viq1mk0cczzzfu <-- PPT