ขอคำแนะนำการ set index,primaryให้ database mysql มีประสิทธิภาพมากที่สุด

[Alexander]

สวัสดีครับ,

รบกวนขอคำแนะนำการ set index,primaryให้ database mysql มีประสิทธิภาพมากที่สุด ไม่ทราบการ set index มากไปมีผลต่อเว็บโหลดช้าขึ้นมั้ยครับ
พอดีได้อ่านข้อมูลแนะนำการให้ detabase โหลดไว มีประสิทธิภาพ ต้องจัดการ index fild ที่โหลดบ่อย  แต่ยังไม่ค่อยเข้าใจ จึงรบกวนถามเพื่อนๆหน่อยครับ ว่าวิธีที่ถูกต้องจริงควรทำอย่างไร


ขอบคุณมากครับ

[Extra Cash]

เอาคร่าวๆละกันนะครับ

การเลือกฟิลด์ที่จะสร้าง index ให้เลือกฟิลด์ที่มีข้อมูลอยู่ในทุก where criteria
เช่น select * from abc where name='xx' and group='yy'
index ที่ต้องสร้างคือ name กับ group
แต่ถ้า group มีข้อมูลให้เลือกแค่ สัตว์บก, สัตว์น้ำ, สัตว์ปีก 3 อย่าง
อย่างนี้ไม่จำเป็นต้องสร้าง index ให้ group เพราะไม่ช่วยเพิ่มประสิทธิภาพเท่าใหร่
index ควรสร้างเฉพาะฟิลด์ที่มีข้อมูลแตกต่างกันมากพอควรจะมีประโยชน์กว่า
ถ้ามี select statment อื่นที่มี where criteria ของฟิลด์อื่น ก็ควรสร้าง index ให้ฟิลด์นั้นด้วย

index หลายๆตัวจะเปลืองพื้นที่เก็บ index และมีผลกับการ insert, update ช้าลงบ้าง แต่จะทำให้ select ไวขึ้น

primary key ควรเลือกตัวที่มีขนาดเล็กที่สุด และต้องมีค่าในแต่ละ row ไม่ซ้ำกันเลย

[mrtomson]

เสริมอีกหน่อยแล้วกันครับ primary key ควรจะ้เป็น field ที่เก็บค่าที่เป็นเอกลักษณ์ของ row ซึ่งแต่ละ row จะต้องไม่เหมือนกัน
ซึ่งเป็นตัวเลขจะดีที่สุด

--- ยกตัวอย่างเช่น ---
วิธีการเลือก primary
เช่น ประเทศไทย มีคนชื่อ-นามสกุล เหมือนกันหลายคน เราจะรู้ได้ว่า ใครเป็นใคร ด้วย หมายเลขประจำตัวประชาชนซึ่งไม่ซ้ำกันแน่นอน
primary คือหมายเลขประจำตัวประชาชน
--- จบตัวอย่าง ---

ฉะนั้นเวลาสร้างฐานข้อมูลควรจะมีฟิลด์ ที่เป็น identifier ด้วยสัก 1 ตัว โดยเปรียบจากตัวอย่างด้านบนแล้ว ฟิลด์นี้ก็เหมือนหมายเลขบัตรประชาชน
ฉะนั้นเวลาเราสร้างฐานข้อมูลก็ควรจะมีฟิลด์ id สักหน่อย เพื่ออ้างอิงข้อมูลได้ กรณีที่มี 2 ข้อมูลขึ้นไปที่มีค่าเหมือนกัน เราจะใช้ id เพื่ออ้างอิงได้ว่าเราจะเลือกอันไหน
คุณอาจเกิดคำถามว่าแล้วค่าของ field id นี้จะเอามาจากไหน อันนี้ส่วนใหญ่ที่นิยมจะใช้เป็น A_I (Auto Increment) ซึ่งค่าของฟิลด์นั้น mysql จะกำหนดเองอัตโนมัติ
(การจะกำหนดให้ใช้ A_I ต้องเลือกใน option ของ field ตอนที่จะสร้าง field ครับ)

ส่วนเรื่อง index ถ้าจะเปรียบง่ายๆก็เหมือน mysql เป็นหนังสือ 1 เล่ม หากเราผู้เขียน ใส่สารบัญเยอะเกินไป จะเกิดข้อเสียเช่น (เปรียบเป็นสารบัญ เพราะบางคนไม่รู้จักว่าดัชนีแปลว่าอะไร)
ประเภทว่า มีสารบัญบอกว่าหน้านี้บรรทัดที่เท่านี้เขียนว่าอะไร ลองคิดดูว่า สารบัญมันจะมากกว่า ข้อมูลเสียอีก
mysql ก็เช่นเดียวกัน หากมี index=สารบัญ มากเกินไป ก็จะทำให้ผู้อ่านหนังสือเล่มนั้นหาข้อมูลที่ต้องการอ่านได้ช้าลง

ฉะนั้น index (ในเชิงทั่วไป)เราควรจะเลือกเฉพาะ field ที่เป็นเงื่อนไขของการค้นหาเท่านั้นจะดีที่สุด

[Tee++;]

เรื่อง Index ต้องถือว่าเป็น ศาสตร์ และ ศิลป์ ลึกลับ หาอ่านยากมากๆ 5555+

Index เอาเป็นว่าของ MySQL ก่อนละกัน

ถือเป็นดัชนีของข้อมูล โดยการจัดเก็บ Index นั้นจะถูกเรียงข้อมูลไว้อยู่แล้ว ดังนั้นเวลาค้นหาจาก Index มันจะมีกระบวนการค้นหาตาม ดัชนี โดยที่ไม่ต้องไปไล่ข้อมูลทั้งหมด สมมุติว่าเรามี Index 1-100 ถ้าเราต้องการหาข้อมูล ที่ 88 กระบวนการจะไม่ได้เริ่มค้นหาตั้งแต่ 1 แต่จะจิ้มเข้าไปที่ 88 เลยและจะหยุดทำงานทันทีที่เจอ

ข้อเสียของ Index มีแค่ สิ้นเปลืองเนื้อที่ กับ ทำให้ Opretion Insert, Update, Delete ทำงานช้าลง ซึ่งก็ไมีต้อง ซีเรียส เพราะพวกนี้มีแค่ 10% ในเว็บ ธรรมดา (แต่ว่าถ้าเป็น FB หรือ TW มันน่าจะ 50:50 ได้เลย -*-)

แล้วก็ Index มีประเภทที่แตกต่างกัน 3-4 ชนิด ต้องเลือกใช้งานให้ถูก แล้วก็ยังทีส่วนที่เป็นข้อยกเว้นอีกหลายๆอย่างเช่น

1. การสร้าง Index ที่มีข้อมูลซ้ำกันเกิน 50% เช่น Yes, No แบบนี้ Index จะไม่ทำงาน
2. แล้วก็การใช้ condition != < > พวกนี้ Index ไม่ทำงาน
3. แล้วก็การสร้าง index แบบ multi-columns อาทิเช่น (id, created_at, status)แบบนี้ถ้าเรา where ด้วย 3 ตัวพร้อมเพีรงกันจะเร็วมาก แต่ว่าต้องทำจาก ซ้ายไป ขวา ยังไง?
id อย่างเดียว ทำงาน
id created ทำงาน
created ไม่ทำงาน
created status ไม่ทำงาน

มันจะต้องจาก ซ้าย ไป ขวา เท่านั้น ไม่ก็ต้องสร้าง Index มาอีกชุดนึง

แล้วก็มี Index อีกประเภท ที่ต้องไปหาอ่านเพิ่มเติม เพรามัน มี condition ที่ต่่างไปอีก คือแบบ Full text

ลองหาอ่านใน web ของ mysql ดูครับ index ดีๆ นี่ลด resources ไปได้อย่าง มหาศาล จากล้าน เหลือ 1 เลยสู้ๆ

[Alexander]

ขอบคุณมากครับ เพื่อนๆทุกท่าน ที่ให้ข้อมูลแบบแน่นมากครับ  ได้ประโยชน์มากเลยครับ
ที่ผมกังวลคือกลัว hostว่าใช้ทรัยากรเกิน ถ้า index มากไป จะไปปรับปรุงครับ
+1

[watch_011]

ได้ความรู้เพียบเลย
ขอบคุณครับ

[saparee]

ขออนุญาตขุดนะครับ


กรณีถ้าต้องการใช้ A LEFT JOIN B ON A.x = B.y

แบบนี้ คือต้องทำ index ในทั้ง x และ y ผมเข้าใจถูกต้องหรือเปล่าครับ?

[dekmv]

กำลังหาอยู่เลยครับ อ่านๆแล้ว มีหลายเรื่องที่ไม่รู้ครับ 

[ohmohm]

ขออนุญาตขุดนะครับ


กรณีถ้าต้องการใช้ A LEFT JOIN B ON A.x = B.y

แบบนี้ คือต้องทำ index ในทั้ง x และ y ผมเข้าใจถูกต้องหรือเปล่าครับ?

LEFT JOIN มันจำเป็นต้องอ่านทุก rows ของ A (หรืออาจจะบาง rows ถ้ามี WHERE) เพื่อมาเทียบกับ B ถ้าไม่เจอ มันจะให้ null ออกมา
index อาจจะมีประโยชน์ กับ column ของ A ที่อยู่หลัง WHERE (ถ้ามี) หรือ column B.y นะครับ

แนะนำอ่าน
http://technet.microsoft.com/en-us/library/ms191426%28v=sql.105%29.aspx
http://www.sqlserverblogforum.com/2011/10/merge-join-vs-hash-join-vs-nested-loop-join/
http://blogs.msdn.com/b/bartd/archive/2011/01/25/query_5f00_tuning_5f00_key_5f00_terms.aspx

updated ขอเพิ่มครับ bookmark ไว้หน่อย
http://blogs.msdn.com/b/mssqlisv/archive/2008/09/26/designing-composite-indexes.aspx
http://technet.microsoft.com/en-us/library/ms172984.aspx

http://www.codeproject.com/Articles/234399/Database-performance-optimization-part-Indexing

[saparee]

ขออนุญาตขุดนะครับ


กรณีถ้าต้องการใช้ A LEFT JOIN B ON A.x = B.y

แบบนี้ คือต้องทำ index ในทั้ง x และ y ผมเข้าใจถูกต้องหรือเปล่าครับ?

LEFT JOIN มันจำเป็นต้องอ่านทุก rows ของ A (หรืออาจจะบาง rows ถ้ามี WHERE) เพื่อมาเทียบกับ B ถ้าไม่เจอ มันจะให้ null ออกมา
index อาจจะมีประโยชน์ กับ column ของ A ที่อยู่หลัง WHERE (ถ้ามี) หรือ column B.y นะครับ

แนะนำอ่าน
http://technet.microsoft.com/en-us/library/ms191426%28v=sql.105%29.aspx
http://www.sqlserverblogforum.com/2011/10/merge-join-vs-hash-join-vs-nested-loop-join/
http://blogs.msdn.com/b/bartd/archive/2011/01/25/query_5f00_tuning_5f00_key_5f00_terms.aspx

ขอบคุณครับที่แนะนำ 

[amzaa1]

มาเก็บความรู้ครับ