สุ่ม rand ข้อมูล ใน MySQL อย่างมีประสิทธิภาพ Optimize

หน้าแรก PHP MySQL เกร็ดความรู้ สุ่ม rand ข้อมูล ใน MySQL อย่างมีประสิทธิภาพ Optimize

---

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

วันนี้เรามาดูการ optimization การสุ่ม record ให้มีประสิทธิภาพกันโดยเฉพาะให้มีการใช้งาน Index ได้อย่างเหมาะสม โดยตารางข้อมูลที่ทำอยู่มีข้อมูลประมาณ 30k records โดยยังคง Index ต่างๆไว้เหมือนเดิมโดยมี houseid เป็น Primary Key เป้าหมายต้องการสุ่ม record เหล่านั้นขึ้นมาแสดงผลจำนวน 4 records เรามาดูการใช้การสุ่มแบบง่ายกัน

Code

SELECT houseid FROM mod_houses ORDER BY RAND() LIMIT 4;

จากการใช้คำสั่งนี้ใช้เวลา 6.84 วินาทีและ 3.68 วินาทีหลังจากการ warm แล้ว เราเริ่มจากการสุ่มจาก record ทั้งหมด

Code

SELECT CEIL(RAND() * MAX(houseid)) FROM mod_houses;

คำสั่งนี้ใช้เวลา 0.01 วินาทีและ 0.0026 วินาทีหลังจากการ warm


จากนั้นเอามาใช้งานด้วย IN

Code

SELECT houseid FROM mod_houses WHERE IN (SELECT CEIL(RAND() * MAX(houseid)) FROM mod_houses) LIMIT 4;

แต่วิธีนี้ไม่ดีแน่เนื่องจาก subquery จะถูกเรียกใช้งานเมื่อ SELECT ด้านนอกทำการอ่านข้อมูลทำให้เกิดการ query จำนวนมหาศาล เราต้องเปลี่ยนมาเป็นการ JOIN

Code

SELECT h1.houseid FROM mod_houses h1 JOIN (SELECT (RAND() * (SELECT MAX(houseid) FROM mod_houses)) AS houseid) AS h2 ON h1.houseid>=h2.houseid ORDER BY h1.houseid ASC LIMIT 4;

คำสั่งนี้ทำให้เราได้ข้อมูลที่ต้องการออกมาโดยใช้เวลาเพียง 0.0012 วินาทีและ 0.0004 วินาทีหลังจากการ warm ที่เราต้องใช้ >= เนื่องจากบางครั้ง houseid อาจจะไม่มีจำนวนที่ตรงกับการสุ่ม ก็ให้ไปใช้ houseid ลำดับถัดไปแทน


จากการ optimize ครั้งนี้จะเห็นว่าความเร็วของการสุ่มเร็วขึ้นถึง 9,200 เท่าซึ่งจะช่วยให้ application โดยรวมทำงานได้เร็วขึ้นมาก โดยเฉพาะในระบบที่มีจำนวน records มากๆและมีการเรียกใช้งานเยอะๆ

ส่วนที่ 2

เนื่องจากการ Optimize ไม่มีสูตรตายตัว เพราะรายละเอียดต่างๆ ไม่เหมือนกันดังนั้นการ Optimize มีหลายแบบเพื่อนำไปใช้ให้เหมาะสมกับงานของเรา มาต่อในส่วนที่พอจะนึกออก

- พยายามใช้ Engine ให้เหมาะสมกับงาน ใน MySQL มี Engine ที่สำคัญที่เป็นที่นิยมอยู่ 2 ตัวคือ MyISAM และ InnoDB ซึ่งทั้ง 2 ตัวก็มีจุดเด่นต่างกัน MyISAM นั้นเหมาะกับงานที่มีการอ่านมากๆ เพราะถูกออกแบบให้สามารถอ่านได้เร็วมากๆ ส่วน InnoDB นั้นก็กลับกันคือเขียนเร็วมาก ดังนั้นอยู่ที่งานของเราว่ามีการใช้งานในส่วนใดมากกว่ากัน ส่วน InnoDB ยังมีความสามารถอื่นๆ เช่น Stored Procedure, Transaction และ Row-Level Lock อ่านเพิ่มได้ที่ ทำความรู้จักกับ INNODB Engine

- สร้าง Index ให้เหมาะสมโดยสร้างในฟิลด์ที่คุณใช้เป็น Clause ใน WHERE Clause, GROUP BY, ORDER BY และ JOIN เพื่อไม่ต้องให้เกิดการอ่านทั้งตารางเพื่อหาข้อมูลตรงตามเงื่อนไข

- ไม่ใช้ AUTO_INCREMENT โดยไม่จำเป็น ในการออกแบบตารางในบางครั้งไม่จำเป็นต้องมี PRIMARY KEY หรือ AUTO_INCREMENT ฟิลด์เสมอไปเช่นการออกแบบตารางเป็น many to many

- ใช้คำสั่ง Built-in ให้มากที่สุด เนื่องจากคำสั่งต่างๆ ถูก compile เป็น core engine ของ DBMS ทำให้ทำงานเร็วกว่าการคำนวนจาก Application ของคุณแน่นอน เช่นการนำข้อมูลจากตารางหนึ่งไปใส่อีกตารางก็สามารถใช้ INSERT ... SELECT แทนได้

- สุดท้ายจะรู้ความสามารถของสินค้านั้นๆ ต้องอ่านคู่มือในเว็บของผู้ผลิต แน่นอน http://dev.mysql.com (เขาไม่ได้ใช้ภาษายากเหมือนในนวนิยาย หรือราชาศัพท์หรอก)


ขอบคุณ: http://articles.modoeye.com/Design_and_Development/SQL/MySQL/Optimize_%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%AA%E0%B8%B8%E0%B9%88%E0%B8%A1_record_%E0%B9%83%E0%B8%99_MySQL.html
http://articles.modoeye.com/Design_and_Development/SQL/MySQL/Optimize_MySQL_%E0%B8%A0%E0%B8%B2%E0%B8%84_2.html

ขึ้นไปด้านบน