ทำไมการใช้ index จึงทำให้ query ข้อมูลได้ไวขึ้น?

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

ในฐานข้อมูลการทำ index ก็จะทำให้กับ field หรือคอลัมน์ที่เรามีเงื่อนไขในการ query บ่อยๆ การดึงข้อมูลก็จะไปดูที่ index แล้วกระโดดไปยังตำแหน่งที่เก็บข้อมูลเลยโดยไม่ต้องค้นหาทุกแถวในตาราง

ตัวอย่างเช่น การทำ index ให้กับตาราง employees ที่คอลัมน์ emp_id

1	rowid
2	rowid
3	rowid
.	.
.	.
.	.

เมื่อมีการ query

SELECT *
FROM employees
WHERE emp_id = 3;

Database ก็จะวิ่งไปดึงข้อมูลยังตำแหน่งที่เก็บข้อมูลของ emp003 มาแสดง โดยที่ไม่ต้องวิ่งไปหาทุกๆ แถวในตาราง employees

Oracle Database มี Index อยู่หลายประเภทแต่ที่ถูกใช้กันทั่วไปคือ B-Tree Indexes

(รูปภาพจาก www.oracle.com)

จากรูปจะเห็นว่า B-tree Index นั้นมี block อยู่สองประเภทด้วยกันคือ Branch blocks ไว้สำหรับการค้นหา และ leaf blocks สำหรับเก็บค่า ในการค้นหาก็จะแบ่งเป็นช่วงๆ ตามขอบเขตแต่ละ block ทำให้การค้นหานั้นมีประสิทธิภาพ

ซึ่ง การใช้ index นั้น ยังมีรายละเอียดเสริมเพื่อเพิ่มความเข้าใจอีก โดย คุณ Siamnobita ได้อธิบายเกี่ยวกับ index ด้วยกัน 5 ข้อดังต่อไปนี้

1. สิ่งที่ทำให้เราสามารถค้นหาใน index ได้เร็วนั้นเนื่องจากมีการ sort ตามค่าในคอลัมน์ด้วย เคยมีคนตั้งคำถามใน narisa ประมาณว่าทำไม oracle ถึงไม่เรียงลำดับข้อมูลในตารางซะเลย จะได้ไม่ต้องใช้ index คำตอบก็คือการจัดเก็บแบบเรียงลำดับนั้นทำได้ยากกว่าและเกิดต้นทุนตามมาเช่น เวลาที่ใช้เมื่อมีการเพิ่ม record ใหม่ พื้นที่ว่างเมื่อเกิดการ split block เป็นต้น อย่างไรก็ดีหากเราไม่มีปัญหากับต้นทุนเหล่านี้ เราก็สามารถจัดเก็บข้อมูลในตารางแบบเรียงลำดับได้เลย นั่นคือใช้ index-organized table (IOT) ซึ่งถือเป็นวิธีที่เร็วที่สุดในการค้นหาข้อมูลตาม primary key

2. ปกติเวลา oracle อ่านข้อมูลจะอ่านทีละ block ไม่ใช่ทีละแถว ดังนั้นจะดูว่าเร็วหรือช้า จะนับจากว่า oracle ต้อง access ข้อมูลจำนวนกี่ block เช่น

ถ้าดูจากรูปด้านบน index มี 3 level
เมื่อเรา select * from ... where index_column = ??
oracle จะอ่านข้อมูลทั้งสิ้น 4 block คือ root block ตัวบนสุด, branch block ตัวกลาง, leaf block ตัวล่างสุด, table block ซึ่งรู้ได้ทันทีเมื่อรู้ค่า rowid จำนวน level ที่น้อยที่สุดที่เป็นไปได้คือ 1 level คือเก็บ rowid ไว้ใน root block เลย ซึ่งจะเกิดในกรณีที่ข้อมูลมีจำนวนไม่มาก ( โดย default ขนาดของ block ประมาณ 8K )

3. คำถามคือ หากข้อมูลมีขนาดเล็ก ๆ เช่น ตารางมีขนาดแค่ block เดียว การใช้ index ยังมีประโยชน์อยู่หรือไม่ เดิมผมเคยเข้าใจว่าไม่มีประโยชน์เหมือนกัน แต่เมื่อได้อ่าน blog ของคุณ richard foote ซึ่งทำการทดสอบให้เห็นชัด ๆ ไปเลย พบว่า index ก็ยังมีประโยชน์อยู่ดี เนื่องจาก

3.1 ในการ full table scan นอกจากตัว block ที่เก็บ data แล้วยังต้องอ่าน header block ด้วยจึงมี cost ที่เกิดขึ้นไม่ใช่แค่ 1 I/O เมื่อเทียบกับ index ที่ใช้ 2 I/O (root block + data block) ก็พอ ๆ กัน

3.2 ในการ full table scan จะเก็บ data ที่อ่านมาบน buffer cache ด้าน LRU ซึ่งจะอยู่ใน memory ได้ไม่นาน ขณะที่ index scan จะวางไว้ด้าน MRU ทำให้มีโอกาสใช้ประโยชน์จากการอ่านจาก memory โดยตรงได้มากกว่า โดยเฉพาะเมื่อมีการเรียกใช้ข้อมูลจาก table บ่อย ๆ

4. ในข้อ 3 เขาทดสอบเฉพาะกรณี index unique scan เช่นค้นตาม primary key เท่านั้น หากเป็น index range scan จะเป็นอีกกรณีหนึ่ง ซึ่งทุก rowid ที่เจอใน index ก็จะต้องมีการ access table block หนึ่งครั้ง แม้ว่า block นั้นจะอยู่บน memory แล้วก็ยังเป็น cost อยู่ดี ดังนั้น full table scan ก็อาจจะคุ้มกว่าขึ้นกับจำนวนแถวที่ต้องการ

5. ประโยชน์ของ index อีกข้อ คือโดยปกติจำนวน column ใน index จะน้อยกว่า column ใน table มาก ๆ ดังนั้นขนาดของ index ก็จะเล็กกว่า table มาก ๆ ด้วย หากเราต้องการดูเฉพาะข้อมูลที่อยู่ใน index อยู่แล้ว เราก็อาจ full scan ที่ index แทน table ไปเลยก็ได้ ซึ่งกรณีนี้จะเป็นการ fast full scan ซึ่งอ่าน index แบบ multi block เหมือน table scan ( full scan ใน index มี 2 แบบ full scan เฉย ๆ กับ fast full scan แบบแรกอ่านทีละ block ซึ่งช้ากว่า แต่ข้อดีคือ ผลลัพธ์มีการ sort ตาม index แบบหลังจะไม่มีการเรียงลำดับ

ปล. ต้องขอบคุณคำอธิบายดี ๆ จาก คุณ Siamnobita มาก ๆ ครับ

Credit by Paley (@ratipong)

อ้างอิง

• Indexes and Index-Organized Tables
  

ข้อเขียนนี้ช่วยฉัน:

ค่า Null ใน Oracle

หลายคนที่เคยใช้งานฐานข้อมูลมาระยะหนึ่ง จะต้องรู้จักกับค่าว่าง หรือค่า Null ซึ่งมีความพิเศษไม่เหมือนใครอยู่หลายอย่าง และเป็นหลุมพรางที่ก่อให้เกิด bug ใน application มาแล้วนับไม่ถ้วน วันนี้ ผมจึงอยากชวนให้ผู้อ่านมาทำความรู้จักกับเจ้าค่าว่างนี้ กันให้มากขึ้น ซึ่งน่าจะเป็นประโยชน์ทั้งกับผู้ที่เพิ่งเริ่มต้นหัดเขียน SQL ใหม่ ๆ และนักพัฒนาที่มีประสบการณ์แล้วทุกท่าน

ความหมายและการใช้งาน

ความหมายของค่า Null ซึ่งถูกกำหนดขึ้นโดย E.F.Codd ผู้ให้กำเนิด Relational Model ซึ่งเป็นที่มา RDBMS ทั้งหลาย คือ “missing information and inapplicable information” แปลว่า ข้อมูลที่ไม่ถูกระบุหรือไม่สามารถระบุได้ โดยทั่วไปเราจะใช้ในความหมายว่า ข้อมูลที่ไม่ทราบค่า (unknown value)

ตาม Oracle Database SQL Language Reference Oracle แนะนำให้ระบุค่า Null กรณีต่อไปนี้

1. when the actual value is not known เราไม่ทราบค่าที่แท้จริงของข้อมูลนั้น หรือ
2. when a value would not be meaningful ค่าที่ใส่ให้กับข้อมูลนั้นไม่สื่อความหมายที่แท้จริง

กรณีที่ 1 นั้นตรงไปตรงมา ถ้าเราไม่รู้ค่าของข้อมูล เราก็ไม่ต้องใส่อะไรลงไป ฐานข้อมูลก็จะ assign ค่า Null ให้กับข้อมูลนั้น ส่วนกรณีที่ 2 ผมอยากจะเข้าใจว่า Oracle หมายถึงกรณีที่เราใส่ค่าพิเศษเฉพาะบางอย่างลงไปเพื่อสื่อความหมายพิเศษบางอย่าง เช่น ใส่ end date เป็น 31/12/9999 เพื่อแทนความหมายว่าไม่มีวันที่สิ้นสุด จะเห็นได้ว่าวันที่ 31/12/9999 ที่เราใส่ลงไปนั้น ไม่ได้ตั้งใจจะสื่อถึงวันที่ดังกล่าวอย่างแท้จริง แต่สื่อถึงวันที่เท่าไรก็ได้ที่ไกลออกไปมาก ๆ เท่านั้น ในกรณีดังกล่าวเราอาจใช้ค่า Null แทนได้ (และผมเคยได้รับคำแนะนำมาว่าใช้ค่า Null ดีกว่าในแง่ของ Optimizer statistics ซึ่งมีผลโดยตรงต่อ performance)

คุณสมบัติ

โดยปกติแล้ว ค่า Null เป็นค่าที่มีความหมายพิเศษ และจะไม่สามารถเปรียบเทียบกับใครได้เลยแม้กระทั่งตัวมันเอง ตรงจุดนี้ ผู้ที่เริ่มต้นเรียน SQL ใหม่ ๆ มักเกิดความสับสน ขอให้คิดง่าย ๆ ว่า Null คือ unknown หมายความว่ามันสามารถจะมีค่าเป็นอะไรก็ได้ทุกค่า แต่ปัจจุบันเรายังไม่รู้ค่าของมัน ดังนั้น ค่า Null เท่ากับ Null จึงไม่ถูกต้อง เพราะตัวหนึ่งอาจจะกลายเป็น a ส่วนอีกตัวอาจจะกลายเป็น b ไปก็ได้ และในขณะเดียวกัน Null ไม่เท่ากับ Null ก็ไม่ถูกต้องเช่นเดียวกัน เพราะมันอาจจะหมายถึง a ทั้งคู่ก็ได้

ตามทฤษฎีแล้ว Null มีความหมายไม่เหมือน 0 (Zero) เพราะ 0 มีค่า และ Null ต้องมีความหมายไม่เหมือนกัน empty string (string ที่มีขนาดเป็น 0 หรือใน SQL เราจะพิมพ์เครื่องหมายคำพูดสองตัวติดกัน ไม่เว้นวรรค) ด้วย ผมขอยกตัวอย่างง่าย ๆ สมมติผมเก็บ ที่อยู่ แยกคอลัมน์เป็น บ้านเลขที่ ซอย ถนน เขต แขวง จังหวัด ถ้าบ้านผมอยู่ติดถนนใหญ่ ผมก็ไม่มีชื่อซอย ในกรณีนี้ ค่าในคอลัมน์ซอย ควรเป็น empty string เพราะไม่ใช่ผมไม่รู้ชื่อซอย หรือไม่อยากระบุชื่อซอย แต่มันไม่มีชื่อซอยต่างหาก

แต่ทฤษฎีส่วนทฤษฎี ปัจจุบัน Oracle จัดการกับ Null และ empty String โดยมองว่ามันเป็นสิ่งเดียวกันเลย แม้จะมีการระบุว่าอนาคตอาจมีการเปลี่ยนแปลงกติกานี้ได้ และแนะนำให้เราอย่าใส่ empty string แทน null แต่ผมเห็นเขาระบุแบบนี้มาตั้งแต่เวอร์ชั่น 7 จนวันนี้เวอร์ชั่น 11 ก็ยังระบุข้อความเหมือนเดิม (แต่รู้สึกจะเน้นข้อความมากขึ้นอีกนิด) และก็นึกภาพไม่ออกจริง ๆ ว่าหากเกิดเปลี่ยนขึ้นมาเมื่อไร โลกของ application ที่สร้างบน oracle จะวุ่นวายสับสนขนาดไหน งานเข้าแน่ ๆ เอาเป็นว่า เราทำตามเขาว่าไปก่อน ก็แล้วกัน empty string ก็ส่วน empty string null ก็ส่วน null อย่าเอามาใช้ปนกัน

การคำนวณ

ตามกติกาแล้ว การคำนวณใด ๆ ที่ดำเนินการกับค่า Null ไม่ว่าจะเป็นการบวก ลบ คูณ หาร ผลลัพธ์ที่ได้จะต้องมีค่าเป็น Null ด้วย เพราะเรามองว่าค่า null จะหมายถึงค่าจริงอะไรก็ได้ ผลลัพธ์ที่ออกมาจะกลายเป็นอะไรก็ได้เหมือนกัน ดังนั้นมันก็ต้องเป็น Null อย่างไรก็ดี เนื่องจาก Oracle มอง Null เป็น empty String ทำให้การจับ null มาต่อกับ string อื่น (concatenation) ได้ค่าเท่ากับ string นั้น ๆ การ concatenation เป็น operator ยกเว้นตัวเดียวที่ทำงานกับ null แล้วไม่จำเป็นต้องได้ค่า null

เรื่องของการคำนวณนี้เองที่มักกลายเป็นข้อผิดพลาดของโปรแกรมเสมอ หากผู้พัฒนาลืมที่จะพิจารณาว่าค่าที่เอามาคำนวณมีโอกาสที่จะเป็น null หรือไม่ หลายคนที่มีประสบการณ์กับข้อผิดพลาดแบบนี้ ก็เลยถือเป็นกฏไปเลยก็มีว่าต้องใส่ NVL ครอบตลอด แต่ผมเองก็ไม่แนะนำการใช้เป็นกฏตายตัวแบบนี้ ทางที่ดี ต้องพิจารณาความเป็นไปได้ของข้อมูลก่อนถึงค่อยใส่ เพราะบางครั้งการได้ผลลัพธ์เป็น null อาจเป็นสิ่งที่ถูกต้อง เพราะจะเตือนเราว่าข้อมูลของเราเกิดข้อผิดพลาดขึ้น และสามารถกลับไปแก้ไขที่ข้อมูลต้นทางได้ ดีกว่าที่จะให้ข้อผิดพลาดนั้นถูกซ่อนไว้ภายใต้ function NVL

เรื่องของ Null ยังมีหัวข้อที่น่าสนใจอีกไม่น้อย ผมคิดว่าเราจะมาต่อกันครั้งหน้า ในเรื่องของการเปรียบเทียบ การจัดกลุ่ม และการเรียงลำดับ ครับ

References:
http://en.wikipedia.org/wiki/Null_(SQL
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements005.htm#i59110

ข้อเขียนนี้ช่วยฉัน: