Index Selectivity นั้นสำคัญไฉน?

Index selectivity คืออะไร?

Balance tree index ช่วยทำให้ประสิทธิภาพของคิวรี่ที่เรียกข้อมูลปริมาณน้อยๆ จากตารางดีขึ้น ดังนั้นเราจึงควรสร้าง อินเด็กซ์บนตารางที่ถูกคิวรี่ในจำนวนบรรทัดต่ำกว่า 15% บ่อยๆ ซึ่งค่านี้สามารถปรับเปลี่ยนได้ตามความเหมาะสมตามสถานการณ์ที่ว่า จำนวนข้อมูลแค่ไหนที่อินเด็กซ์สามารถเรียกได้หรือคอลัมน์ใดๆ บนอินเด็กซ์ที่ต้องการใช้ในการ Join กับตารางอื่นๆ

ค่าเรโชของค่าเฉพาะบน column (Distinct value) ต่อจำนวนข้อมูลบนตารางจะถูกนำมาใช้ในการคำนวนหา Selectivity ของอินเด็กซ์นั้นๆ ซึ่ง Selectivity ในอุดมคติคือเท่ากับ 1 เทียบได้กับการใช้อินเด็กซ์แบบ Unique บน NOT NULL column นั่นเอง

ตัวอย่างของ Index Selectivity

มีตารางจำนวน 100,000 บรรทัดและอินเด็กซ์หนึ่งบนตารางมีค่าเฉพาะ 88,000 บรรทัด ดังนั้นค่า Selectivity ตัวนี้จะเท่ากับ 88,000/100,000 = 0.88

บน Oracle นั้นเมื่อใดก็ตามที่เราทำการสร้างตารางที่มีคอลัมน์แบบ Unique และ Primary key เมื่อนั้น Oracle จะทำการสร้างอินเด็กซ์ขึ้นให้โดยอัตโนมัติ อินเด็กซ์เหล่านี้เป็นอินเด็กซ์ทีมีค่า Selectivity ที่ดีและมีผลต่อ Optimizer ค่อนข้างดีมาก เป็นเพราะว่าค่า Distinct value จะมีค่าเท่ากับจำนวนของข้อมูลบนตารางนั้นๆ

ตัวอย่างของ Index Selectivity ที่แย่

ถ้าอินเด็กซ์บนตารางหนึ่งซึ่งมีข้อมูลจำนวน 100,000 บรรทัด มีค่าเฉพาะแค่ 500 บรรทัด เมื่อนั้นค่า Selectivity จะเท่ากับ 500/100,000 = 0.005 และในตัวอย่างนี้ คิวรี่ที่มีการเรียกข้อมูลเฉพาะบนอินเด็กซ์ตัวนี้จะได้ผลลัพธ์เท่ากับ 100,000/500 = 200 บรรทัดต่อแต่ละค่าเฉพาะ ในกรณีนี้ Full table scan อาจจะให้ประสิทธิภาพที่ดีกว่าการใช้อินเด็กซ์ซึ่งนั่นหมายถึง I/O scan ที่มากกว่าในการคิวรี่ข้อมูล

เราจะคำนวนค่า Index Selectivity อย่างไร?

เราสามารถหาค่าได้โดยการนำค่า Distinct value บนอินเด็กซ์มาหารด้วยจำนวนบรรทัดบนตาราง

สมมุติตารางชื่อ EMP มีจำนวนข้อมูลทั้งหมด 14 บรรทัด และเราทำการสร้างอินเด็กซ์ขึ้นบนคอลัมน์ชื่อ job

create index idx_emp_job on emp(job);

analyze table emp compute statistics;

select distinct_keys from user_indexes
where table_name = 'EMP'
and index_name = 'IDX_EMP_JOB';

DISTINCT_KEYS
-------------
5

select num_rows from user_tables
where table_name = 'EMP';

NUM_ROWS
---------
14

ดังนั้นค่า Selectivity = DISTINCT_KEYS / NUM_ROWS = 0.35

การหาค่า Distinct value ของแต่ละคอลัมน์

สมมุติว่าตารางใดๆ นั้นได้ถูกทำการ analyze แล้ว เราสามารถคิวรี่ดูได้จาก *_TAB_COLUMNS เพื่อนำไปใช้ในการหาค่า Selectivity ของแต่ละคอลัมน์ต่อไป

select column_name, num_distinct
from user_tab_columns
where table_name = 'EMP';

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
EMPNO                          14
ENAME                          14
JOB                            5
MGR                            2
HIREDATE                       13
SAL                            12
COMM                           4
DEPTNO                         3

สรุป

ค่า Index Selectivity ช่วยทำให้สามารถกำหนดทิศทางในการสร้างอินเด็กซ์และการ Tuning performance ของระบบได้ โดยเฉพาะบนฐานข้อมูลแบบ OLTP นั้นการคิวรี่ข้อมูลโดยอินเด็กซ์นั้นสำคัญมากๆ นอกจากทำให้การคิวรี่ข้อมูลมีประสิทธิภาพสูงสุดแล้ว ยังทำให้ปริมาณของ User call หรือ Throughput ในช่วง Business hours ดีขึ้นอีกด้วย แบบนี้คุณลูกค้าคงจะปลื้มไม่ใช่น้อยนะครับ 🙂

ขอบคุณครับ

ข้อมูลจาก  How to measure Index Selectivity

Author: MrNobita Namsutto

Advertisements

ใส่ความเห็น

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 /  เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out /  เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out /  เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out /  เปลี่ยนแปลง )

Connecting to %s