5 วิธีเพิ่มประสิทธิภาพและความเร็วให้กับ MySQL

1. index column ที่ถูกใช้ใน where, order by และ group by

การทำ index ช่วยให้การดึงข้อมูลได้เร็วขึ้น และเป็นประโยชน์กับการ sorting ด้วย แต่ก็ต้องแลกกับการใช้พื้นที่ที่มากขึ้นและลดประสิทธิภาพการ insert, update และ delete แต่มันก็คุ้มกับการที่คุณต้อง Query ข้อมูลที่มีปริมาณมากๆ

แล้วปริมาณแค่มูลเท่าไหร่ถึงจะเรียกว่ามาก จนต้องทำ index กันล่ะ คำตอบคือ ทดสอบ Query ด้วย Worst case scenario เช่น Query ที่มีความซับซ้อนมากๆ แล้วผล Query มันอืดมากเกินที่คุณจะรับไหว นั่นเป็นสัญญาณบอกว่าใช้ index เถอะครับ

Case Study: ทดสอบ Query ตารางชื่อว่า customer ที่มี 599 แถว

SELECT customer_id, last_name FROM customer WHERE first_name = 'MARSHALL'

จาก Query นี้ MySQL Server จะ scan ทุกแถวเพื่อให้ได้ผลลัพธ์ออกมา แต่ยังดีที่มีคำสั่งดีๆ อย่าง EXPLAIN มาช่วยชีวิตเราไว้ ซึ่งมันเอาไว้อธิบาย Query ว่าออกมาเป็นยังไง (คำสั่งนี้ใช้ได้ทั้ง INSERT, DELETE, REPLACE และ UPDATE ก็ได้เด้อ)

EXPLAIN SELECT customer_id, last_name FROM customer WHERE first_name = 'MARSHALL'

ผลลัพธ์ที่ได้คือ

จากคำอธิบายด้วยคำสั่ง EXPLAIN มีสิ่งสำคัญอยู่ 2 อย่างที่ช่วยเราปรับปรุงตารางคือ

1. คอลัมน์ key มีค่าเป็น NULL แสดงให้เห็นว่าจะมีการ scan ทุกแถว
2. คอลัมน์ rows มีค่าเท่ากับ 599 คือ MySQL Server scan 599 แถวในการค้นหา

วิธีการเพิ่มประสิทธิภาพ คือ เพิ่ม index เข้าไป ด้วยคำสั่ง

CREATE INDEX firstname ON customer (first_name)

• firstname คือ ชื่อ key index
• first_name คือ คอลัมน์ที่ต้องการทำ index (มีมากกว่า 1 คอลัมน์ก็ได้)
• ลองใช้คำสั่ง EXPLAIN ดูใหม่ครับ

ถ้าต้องการดูว่าตารางมี index อะไรบ้างสามารถใช้คำสั่ง

SHOW INDEXE FROM customer

2. หลีกเลี่ยงการใช้ LIKE นำหน้าด้วย Wildcards

MySQL ไม่สามารถใช้ประโยชน์จาก index ได้ ถ้าใช้ LIKE และนำหน้าด้วย Wildcard หรือ % เช่น

SELECT customer_id, last_name FROM customer WHERE first_name LIKE '%MARSHALL'

ถ้าลองใช้ EXPLAIN ทดสอบดูผลลัพธ์สิครับ

3. เพิ่มประสิทธิภาพ LIKE ด้วย UNION Clause

ถ้ามีการใช้ LIKE ร่วมกับ OR อาจทำให้ผลลัพธ์ไม่ถูกต้องและมีประสิทธิภาพต่ำกว่าการใช้ LIKE ร่วมกับ UNION ที่ทำ index เช่น

SELECT * FROM customer WHERE first_name LIKE 'MA%' OR last_name LIKE 'MA%'

เขียนแบบใช้ UNION ร่วมด้วย

SELECT * FROM customer WHERE first_name LIKE 'MA%'
UNION 
SELECT * FROM customer WHERE last_name LIKE 'MA%'

4. ปรับ Database Schema

ปรับ Data Types ให้เหมาะสม

เมื่อคุณออกแบบโครงสร้างตาราง โปรดรู้ไว้ว่า “สั้นๆ ดีกว่าเสมอ” เช่น ถ้าคุณต้องการออกแบบตาราง users ที่มีจำนวนไม่เกิน 100 records คุณควรใช้ TINYINT ที่รองรับค่าตั้งแต่ -128 ถึง 128

ถ้าหากข้อมูลของคุณเป็นตัวเลข คุณก็ไม่ควรใช้ Data Type เป็น VARCHAR เพราะเมื่อมีการคำนวณชนิดตัวแปรที่เป็นตัวเลขย่อมมีประสิทธิภาพมากกว่า

หลีกเลี่ยงจำนวน Column ที่มากเกินไป

การมีจำนวน Column ที่มากเกินไปทำให้เวลาในการ process ของ CPU ก็มากขึ้นด้วย ฉะนั้นแทนที่จะใช้ column มากๆ อาจจะแบ่งข้อมูลเก็บไว้อีกตารางนึง เช่น ถ้า user สามารถมี address ได้มากกว่า 1 ก็สร้างตารางเก็บ address ไว้ต่างหากเลย

JOIN มากไปก็ไม่ดี

ปกติการ JOIN ตารางจะแปลผันตรงกับประสิทธิภาพการ Query นั่นคือ ยิ่ง JOIN ตารางมากๆ ประสิทธิก็ยิ่งต่ำลงตามไปด้วย

5. Balance 4 ทรัพยากรหลัก

4 ทรัพยากรหลัก คือ Storage, Processor, Memory และ Network ถ้าส่วนใดส่วนหนึ่งอ่อนแอก็จะทำให้ประสิทธิภาพโดยรวมก็แย่ลงไปเช่นกัน ลองมาดูรายละเอียดแต่ละส่วนกันครับ

Storage

ถ้าคุณใช้ hard disk drives (HDD) คุณสามารถ upgrade มาใช้ solid-state drives (SSD) เพื่อประสิทธิภาพที่ดียิ่งขึ้น อาจจะใช้ tool ในการ Monitor disk input/output rate อย่างเช่น iotop หรือ sar เพื่อตรวจสอบ หาก disk ทำงานหนักเกินไปก็ควร upgrade ครับ

Processor

process เป็นส่วนมาตรวัดที่บอกว่าระบบของคุณเร็วแค่ไหน ถ้าเป็น Linux ลองใช้คำสั่ง top เพื่อดูการกินทรัพยากรในส่วนต่างๆ ของ server แล้วลองดูว่า MySQL process กินทรัพยากรของ CPU ในอัตรส่วนที่มากหรือไม่ ถ้าหากมากเกินไป ถึงแม้ค่าใช้จ่ายจะสูงในส่วนนี้ ก็มีความจำเป็นในการ upgrade

Memory

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

Network

ตรวจสอบ network bandwidth ระบบของคุณเพื่อรองรับ database traffic เพราะสื่อกลางในการส่งต่อข้อมูลหากมีคุณภาพ traffic ของ database ก็ได้รับอานิสงส์ด้วยเช่นกัน

ขอบคุณแหล่งข้อมูล : dzone.com, phoenixnap.com