Home > SQL Performance Tuning > การทำ Performance Tuning SQL Query เบื้องต้น (part 5 – Index Strategy)

การทำ Performance Tuning SQL Query เบื้องต้น (part 5 – Index Strategy)


System Requirement

  1. SQL Server 2005 or 2008
  2. Sample Database “AdventureWorks” (Install from DVD)

Index Strategy Overview

เทคนิคหนึ่งที่ถือว่านิยมกันมากที่สุดก็คือการทำ Index ครับ โดยการทำ Index นั้นมีหลักการที่ง่ายมากๆ ก็คือ ทำย้งไงให้ Query ตัวเดิมสามารถที่จะ Scan disk น้อยลง เพราะว่าโดยปกติแล้ว เวลาที่ SQL Server ทำงานหรือค้นหาข้อมูลเพื่อให้ได้ผลลัพธ์ตาม Query Syntax นั้น ทาง SQL Server จะใช้ทรัพยากรทั้งหมด (Resources) ไม่ว่าจะเป็น CPU, Disk, Memory, etc. แล้วจุดที่ถือว่าเป็นตัวร้ายที่สุด ก็คือ disk ครับ

จริงๆ แล้ว เราสามารถที่จะหาวิธีใช้ Memory ให้น้อยที่สุดก็ได้ หรือว่าหาวิธียังไงให้ใช้ CPU ให้น้อยที่สุดก็ได้ แต่มันจะเข้าหลักการที่ว่า “ได้ไม่คุ้มเสีย” เพราะโดยส่วนใหญ่แล้ว ปัญหาที่ทำให้ Query ช้านั้น จะอยู่ที่ Disk ซะ 80% แล้วครับ ดังนั้น ถ้าเราพุ่งเป้าไปที่ disk นั้น ก็จะคุ้มค่ากว่าพุ่งเป้าไปที่ CPU หรือ Memory ครับ

Index ใน SQL ก็เหมือน Index ในหนังสือนั่นแหล่ะครับ สมมุติว่าเราไปหยิบหนังสือขึ้นมา 1 เล่ม ที่มีจำนวนหน้า 1,000 หน้า การที่เราจะเปิดหนังสือไปหน้าที่มีข้อมุลที่เราต้องการ เราอาจจะต้องเปิดไปทีละหน้าไปเรื่อยๆ จนกว่าจะเจอหน้าที่มีข้อมูลที่เราต้องการใช่ไหมครับ ทีนี้สมมุติว่าเราเจอหน้านั้นแล้ว ก็มีคำถามต่อไปว่าเราจะหยุดเปิดหน้าที่เหลือหรือเปล่า? คำตอบก็คือเราก็คงจะไม่หยุดเปิดหน้าที่เหลือ เหตุผลหรือครับ? ก็เพราะว่าหน้าที่เหลือก็อาจจะมีข้อมูลที่เราต้องการอีกก็ได้นี่ครับ ดังนั้น เราก็เลยต้องเปิดไปเรื่อยๆ จนจบหนังสือทั้งเล่มนั่นเอง

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

ใน sql server ก็ทำการแบ่งหน้า (Page) ไว้เก็บข้อมูลเหมือนกัน โดย 1 Page จะมีขนาดเท่ากับ 8KB เสมอ ครับ และไม่สามารถแก้ไขได้ แต่ใน Oracle จะสามารถกำหนดขนาดของ Page ได้ครับ ทีนี้ใน 1 page จะเก็บข้อมูลได้กี่ Record ก็ขึ้นอยู่กับความยาวของ Record แล้วครับ ยกตัวอย่างเช่น ถ้า 1 record มีความยาวเท่ากับ 1 KB ก็แสดงว่าใน 1 page สามารถเก็บข้อมูลได้ 8 Records ครับ

เตรียมข้อมูลที่ใช้ในการทดลอง

ก่อนจะทำการทดลอง ผมจะ export table “person.contact” ออกมาก่อนโดยสร้างเป็น database ก้อนใหม่ชื่อ demo โดยมีขั้นตอนดังนี้ครับ

1) เลือกคำสั่ง Export Data

2) เลือก source เป็น Adventureworks ตามรูป

3) เลือก Destination โดยทำการสร้าง Database ใหม่โดยกดปุ่ม New

4) ตั้งชื่อฐานข้อมูลเป็น Demo

5) เลือก Table ที่เราต้องการ Export ในที่นี้ให้เลือก person.contact

หลังจากนั้น ก็ทำตามแนวที่เราถนัดครับ next ไปเรื่อยๆ จน export data สำเร็จ จนได้ database ก้อนใหม่ชื่ demo ก็เป็นอันเสร็จ พร้อมที่จะทำการทดลองได้แล้วครับ

เริ่มการทดลอง

คำสั่งต่อไปนี้เป็นคำสั่งที่ใช้ในการขอดูจำนวน page ทั้งหมดที่ใช้ใน Table ครับ ก่อนใช้ต้อง use demo ก่อนครับ

     dbcc checktable(‘person.contact’)

จะเกิดผลลัพธ์ดังรูป

ผลลัพธ์ที่ได้จะทำให้เราทราบว่า Table “person.contact” มีจำนวน page ทั้งหมดกี่ page ถ้าอยากจะทราบว่า table นี้มีขนาดเท่าไหร่ ก็เอา 8,192 คูณจำนวน page เข้าไปก็ทราบขนาดของ Table ครับ

ทีนี้เราลองดูตัวอย่างวิธีการ Query ตาม Syntax ด้านล่างครับ

    set statistics io on
    go
    select * from person.contact
    go

 

วิธีการ Query ข้างต้นนี้เป็นวิธีการขอดู records ทั้งหมดที่อยู่ใน Table ดังนั้นการ Scan page จึงมีขนาดเท่ากับจำนวน page ทั้งหมดของ Table ครับ

คราวนี้เราลองใส่ where เข้าไปตาม syntax ด้านล่าง แล้วลองรันดูครับ

จะเห็นว่าจำนวน scan page จะเท่ากันตามหลักการของหนังสือเหมือนกับที่ผมได้กล่าวเอาไว้ครับ

ทีนี้ลองสร้าง Index ดู เพื่อลดจำนวน scan page ตาม syntax ถัดไป โดยมีหลักการที่ว่าให้สร้าง index บน column ที่เราต้องการ where ในที่นี้คือ contactid ดังตัวอย่าง

ทีนี้ลอง ขอดูว่า index ที่เราสร้างไว้สำเร็จหรือไม่ โดยใช้คำสั่งและผลลัพธ์ดังรูปถัดไป

จะได้ผลลัพธ์ว่า Table นี้มี index ที่สร้างไว้แล้วอะไรบ้าง ถ้าได้หน้าจอประมาณด้านล่างก็แสดงว่าเราสร้าง index สำเร็จครับ

 

ทีนี้ลองรันคำสั่ง ที่มี where อีกครั้งนึง คราวนี้จะได้ผลลัพธ์ดังนี้ครับ

ถ้าเทียบกับตอนแรกที่ไม่มี index จะเห็นว่า ต้อง scan page ถึง 557 page แต่พอใส่ index เข้าไปแล้วรันคำสั่งเดิม จะเห็นว่าเหลือ scan page แค่ 3 page เองครับ สรุปว่า optimize เพิ่มขึ้นถึง 557/3 = 185 เท่าโดยประมาณครับ แต่นี่ก็ optimize เฉพาะ disk น่ะครับ แต่ก็ถือว่าคุ้มค่ามากครับ เพราะว่าถ้า disk ทำงานน้อยลง cpu และ memory ก็จะทำงานน้อยลงตามไปด้วยครับ

สรุปการใช้งาน Index

การสร้าง Index ถ้าจะให้ได้ผล ควรจะสร้างลงบน field ต่างๆ ดังต่อไปนี้

  1. Where
  2. Primary Key
  3. Foreign Key
  4. Join
  5. Group by

นี่เป็นเพียงส่วนหนึ่งเท่านั้น ในการใช้งานจริงๆ ยังมีรายละเอียดปลีกย่อยอีกมากครับ ลองติดตามตอนต่อไปนะครับ

หลักสูตรอบรม Tuning SQL Server (Click)

 

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: