ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

เกี่ยวกับ ปัญหาการใช้งาน การติดตั้ง ฐานข้อมูล MySql Oracle MSSQL ect...
การเขียน คำสั่ง SQL เพื่อดึกข้อมูล บอร์ดนี้ควรระบุโครงสร้างตารางของท่านในคำถามด้วยนะ

Moderator: mindphp

ผ่านมา
PHP Newbie
PHP Newbie
Posts: 7
Joined: 27/04/2012 2:27 pm

ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by ผ่านมา »

/* ไปตั้งไว้ที่ http://www.pantip.com/tech/developer/topic/DD3063128/DD3063128.html ด้วยนะครับ */

นี่เป็น 1 ใน 2 query ที่ DBA แจ้งมาว่ามีปัญหาครับ (อีกอันกำลองลองดูๆอยู่ น่าจะแก้ได้เองครับ)

(MySQL นะครับ)

Code: Select all

SELECT DISTINCT record_date FROM inputs WHERE userID  = "A114124"  AND  deleted_on = "0000-00-00"  AND record_date  BETWEEN "2012-01-01"  AND  "2012-03-31" ORDER BY record_date
query นี้เป็น query ที่ใช้ดึงเอาวันที่ที่มี transaction ทั้งหมด ของ user คนนั้นออกมา highlight วันที่มีการทำงานของ user นั้นๆ ใน GUI ปฏิทิน 3 เดือนครับ

inputs เป็น table ที่เก็บ transaction ของ user แต่ละคน ในแต่ละวันจะมีจำนวน 1 - 2 record maximum เท่าที่เจอคือประมาณวันละ 100 records / 1 user
แต่ละวันมีจำนวน user ที่เข้ามาใช้ระบบไม่แน่นอน แต่อยู่ราวๆ 10 - 200 คน เฉลี่ยมี record เพิ่มขึ้นประมาณ 5000 record / วัน ตอนนี้เบ็ดเสร็จอยู่ที่ราวๆ 650,000 record ครับ
table มีแนวโน้มจะโตขึ้นเรื่อยๆ และแนวโน้มจะมากกว่าเดิมขึ้นเรื่อยๆครับ
(table structure มี field id เป็น PK ตัวเดียว ไม่มี index อื่นครับ)

คิดว่า ที่ DBA บ่นว่าช้า น่าจะมาจาก query นี้ ต้อง table scan เพื่อหาผลลัพธ์ออกมาเลย แต่ผมก็ไม่รู้ว่าจะแก้ query ให้มันเร็วกว่านี้ได้ยังไงแล้ว -_-''

วิธีแก้เท่าที่นึกออกตอนนี้ ก็คือ
A.) ทำ summary table ออกมา (ใน table input จะมี field ที่ต้อง summary เป็นยอดรวมที่ใช้ในส่วนงานอื่นอีกที)
เพื่อเปลี่ยนจากการทำ table scan ขนาดใหญ่ มาสู่ table scan ที่เล็กกว่า แต่ต้องไปตามแก้ script ที่จัดการข้อมูลของ table inputs ให้มาคอย update summary table ด้วย
กับ B.) เนื่องจากต้องมีการแก้ไข script ซึ่งมี cost พอสมควร ก็เลยจะเอา cost ตรงนี้ไปเสนอให้ upgrade hardware ก่อนเลย (แล้วตัว script จะตามมาทีหลัง)


คำถามนะครับ
1.) มีอะไรที่ผมพอจะทำได้เกี่ยวกับ table inputs นี้เพื่อเพิ่มความเร็วให้ script อีกได้ไหมครับ
2.) แนวคิด A.) ของผมถูกต้องไหมครับ มีอะไรพอจะเสริมให้มันเร็วขึ้นได้อีกไหมครับ


ขอบคุณครับ
User avatar
mindphp
ผู้ดูแลระบบ MindPHP
ผู้ดูแลระบบ MindPHP
Posts: 27160
Joined: 22/09/2008 6:18 pm
Contact:

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by mindphp »

record_date, userID ใช้ค้นหา น่าจะทำมันเป็น index นะครับ
และอีกอย่าง ที่ำให้ เสียเวลาคือ ORDER BY กรณีนี้เข้าใจ ว่า record_date มันสัมพันธ์ กับ id อยู่แล้ว ให้มันเรียงตาม id ก็ได้ครับ

แต่ถ้า รd ไม่ใช่ auto เราตั้ง ORDER BY ในส่วนของการสร้างตารางไว้ที่ record_date ก็น่าจะช่วยได้

Record รับ 10 ล้าน ใน mysql ทำ index ดีๆ เขียน script ดีๆ จูน my.ini ให้เข้าที่เข้าทาง ก็เอาอยู่ครับ

ส่วนของ ทำตารางเก็บสถิติ ก็ช่วยได้เยอะ ครับถ้าจะทำ ลองแก้ดู จริงผม ว่าแก้ไม่เยอะหรอกครับ ถ้าเขียนดี ยิ่งถ้าเป็น MVC อยู่แล้ว ดูที่ Model เพิ่ม method ไปอีกตัวก็ได้แล้วครับ
สบายๆ


เสริมเรื่อง query อีกนิด คิดว่าจะทำให้เร็วและง่ายที่สุดในตอนนี้คือ ลองเก็บไว้ ว่า แต่ละวัน (record_date) มี record id ของ PK อยู่ที่เท่าไร เก็บใส่ ตารางไว้ เขียนตารางเพื่อเก็บข้อมูลชุดนี้ขึ้นมาก็ได้
เช่น
2012-01-01 5000
2012-01-02 10000
...


เวลาเขียนเงื่อนไข query ก็หาก่อนจากตารางนั้นว่า เค้า เลือกวัน ที่อยู่ใน ช่วงไหน ก็จะได้
id เช่น 5000 ออกมา เราก็ใช้ เงื่อนไขนี้เพื่อกรองข้อมูลให้น้อยลง ก่อน เข้า query ที่ยก มา
เป็น

Code: Select all

SELECT DISTINCT record_date FROM inputs 
WHERE 
id > *** AND 
userID  = "A114124"  AND  deleted_on = "0000-00-00"  AND record_date  BETWEEN "2012-01-01"  AND  "2012-03-31" 
ORDER BY record_date
ติดตาม VDO: http://www.youtube.com/c/MindphpVideoman
ติดตาม FB: https://www.facebook.com/pages/MindphpC ... 9517401606
หมวดแชร์ความรู้: https://www.mindphp.com/forums/viewforum.php?f=29
รับอบรม และพัฒนาระบบ: https://www.mindphp.com/forums/viewtopic.php?f=6&t=2042
ผ่านมา
PHP Newbie
PHP Newbie
Posts: 7
Joined: 27/04/2012 2:27 pm

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by ผ่านมา »

ขอบคุณครับ คุณ mindphp

ประเด็นคือ record_date มันย้อนหลังได้เนี่ยสิครับ -_-''
ข้อมูลใน database จะเป็น record_date (data type= date , วันที่ที่ user ระบุว่าจะเก็บข้อมูล)
กับ created_on (data type = datetime ,วันที่สร้าง record จริง) ครับ
concept ที่จะเก็บ record_date เป็นช่วงๆ ตามวัน หรือเรียงตาม id แทนนี่คงไม่ได้แน่ครับ

field userID นี่เดี๋ยวคงทำ index แน่นอนครับ แต่ record_date ที่ data type เป็น date นี่ควรจะทำ index ด้วยหรือครับ
(ใน script ที่ใช้ มีการเปรียบเทียบวันที่เยอะที่เดียว แต่ผมเองไม่แน่ใจว่า date มันควรใช้เป็น index หรือเปล่า
หรือแม้แต่งานแบบสอบถามที่แยกเพศของผู้ถูกสำรวจ ที่มีแค่ 2 ค่า ชาย/หญิง เนี่ย
ถ้ามีการใช้งาน field นี้เยอะ (แต่เนื่องจากมันมีเพียง 2 ค่า) ก็เลยไม่แน่ใจว่าควรจะทำ index ใน field นี้ดีหรือเปล่า)

คุณ mindphp พอจะมีหลักการณ์ หรือ link อะไรดีๆ เกี่ยวกับว่าเมื่อไหร่ควรใช้ เมื่อไหร่ไม่ควรใช้ index แนะนำบ้างไหมครับ
User avatar
mindphp
ผู้ดูแลระบบ MindPHP
ผู้ดูแลระบบ MindPHP
Posts: 27160
Joined: 22/09/2008 6:18 pm
Contact:

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by mindphp »

ตอบแบบกรณีนีไม่ได้ไปเปลี่ยนโครงสร้างใดๆ ของตาราง
สร้างตารางเพิ่ม เก็บข้อมูล
add_date max_id
2012-01-01 5000
2012-01-02 10000
...
2012-01-29 88888
2012-01-30 99999

หมา่ยถึงให้ทำตารางเพิ่ม ครับแล้วเก็บ id ของแต่ละวัน ว่าไปจบที่เท่าไร เช่นถ้า user ค้นมาว่า
ตั้งแต่ วันที่ 2012-01-02 ถึง 2012-01-29 เราก็ไปค้นหาจากตารางนั้นมาก่อนว่า id สุดท้ายก่อน วันก่อหน้านั้นเท่า ไร มันก็จะได้ใช้ index PK ที่มีอยู่แล้ว ในการกรองข้อมูลให้น้อยลงก่อน
เป็นเทคนิคในการเขียนแบบหนึ่ง นอกจากใช้ ทบ. ต่างๆที่จะ เพื่อทำให้มันเร็วขึ้น

ส่วนอีกประเด็นคือ และ id ถ้ามัน auto สั่งมัน ORDER BY id DESC แทน การ record_date อันนี้ทำได้อยู่แล้ว ครับ

ส่วน เพศ เอามาทำ index ไม่ได้ครับ เพราะการกระจายของข้อมูลมันไม่มีเลย

เครื่องมือช่วย ในการเขียน query คือ

Code: Select all

Explain ตามด้วยคำสั่ง sql
ที่เราใช้ query อยู่ครับ จะได้รู้ว่ามัน ค้นหาจากอะไรบ้างมีประโยชน์มาก สำหรับการค้นจาก หลายๆ ตาราง และเราไม่ได้ออกแบบฐานข้อมูลไว้เอง
ติดตาม VDO: http://www.youtube.com/c/MindphpVideoman
ติดตาม FB: https://www.facebook.com/pages/MindphpC ... 9517401606
หมวดแชร์ความรู้: https://www.mindphp.com/forums/viewforum.php?f=29
รับอบรม และพัฒนาระบบ: https://www.mindphp.com/forums/viewtopic.php?f=6&t=2042
tohkai_php
PHP Sr. Member
PHP Sr. Member
Posts: 95
Joined: 10/04/2012 9:09 am

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by tohkai_php »

AND record_date > "2012-01-01" AND record_date < "2012-03-31"

ช่วยได้หน่อย แหะๆ
User avatar
mindphp
ผู้ดูแลระบบ MindPHP
ผู้ดูแลระบบ MindPHP
Posts: 27160
Joined: 22/09/2008 6:18 pm
Contact:

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by mindphp »

tohkai_php wrote:AND record_date > "2012-01-01" AND record_date < "2012-03-31"

ช่วยได้หน่อย แหะๆ
ได้อีกนิดหน่อย จริงครับ :-D
ติดตาม VDO: http://www.youtube.com/c/MindphpVideoman
ติดตาม FB: https://www.facebook.com/pages/MindphpC ... 9517401606
หมวดแชร์ความรู้: https://www.mindphp.com/forums/viewforum.php?f=29
รับอบรม และพัฒนาระบบ: https://www.mindphp.com/forums/viewtopic.php?f=6&t=2042
ผ่านมา
PHP Newbie
PHP Newbie
Posts: 7
Joined: 27/04/2012 2:27 pm

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by ผ่านมา »

อ้ะ .. คุณ mindphp ผมพอจะเข้าใจ concept ของคุณครับ เพียงแต่ว่า table design ที่ทำไว้ record_date ไม่ได้ไปทางเดียวกันกับ id ครับ

field record_date ย้อนหลังได้ เพราะต้อง support ให้ user ที่ไม่ได้มาทำงานวันเสาร์ - อาทิตย์ หรือช่วงลา
ดังนั้น field record_date จะไม่เรียงตาม id ครับ field ที่เรียงตาม id จริงๆ คือ created_on ที่บอกไว้เบื้องต้นครับ
แต่ เงื่อนไขที่ใช้ค้นหา ก็ไม่ใช่ created_on อยู่ดี เวลา query ข้างบน ระบบสนใจแต่วันที่ที่ขายจริง (record_date) เท่านั้นครับ


ตัวอย่างสถานการณ์
เช่น user "A03421" เข้าสู่ระบบมาเมื่อวาน(created_on = 2012-04-26) แต่กรอกข้อมูลรายการขายของวันที่ 21 เม.ย. 55 (record date = 2012-04-21)
หลังจากนั้นก็ค่อยกรอกข้อมูลการขายของเมื่อวาน (created_on = 2012-04-26 , record_date = 2012-04-26)

หลังจากนั้น user "A34421" ก็เข้าสู่ระบบมาวันนี้ เพื่อกรอกข้อมูลของวันที่ 22 ( created_on = 2012-04-27 ,record_date = 2012-04-22) และ 27 (created_on = 2012-04-27 , record_date = 2012-04-27)
data ใน inputs ก็จะเป็น
id , userID , record_date , created_on , ...
1 , A03421 , 2012-04-21 , 2012-04-26, ...
2 , A03421 , 2012-04-26 , 2012-04-26, ...
3 , A34421 , 2012-04-22 , 2012-04-27, ...
4 , A34421 , 2012-04-27 , 2012-04-27, ...
จะเห็นว่า record_date ไม่ได้เรียงตาม id ครับ ดังนั้นถ้าจะเก็บ max record_id ของแต่ละวัน ก็ต้อง update table ใหม่แบบ real time เลยสิครับ
แถมการค้นก็จะได้ข้อมูลที่ไม่เกี่ยวข้องมาส่วนหนึ่งอีก ถ้าวันหนึ่งมี record เพิ่ม 5000 record ก็ต้องมีการ update table ใหม่ 5000 ครั้ง หรือครับ?





คุณ tohkai_php

ผมเข้าใจว่ามาตลอดว่า between เร็วกว่า compare 2 รอบนะครับ ผมเข้าใจผิดหรือเนี่ย -*-
User avatar
mindphp
ผู้ดูแลระบบ MindPHP
ผู้ดูแลระบบ MindPHP
Posts: 27160
Joined: 22/09/2008 6:18 pm
Contact:

Re: ปรึกษาหน่อยครับ DBA บ่นมาว่า query ทำให้ระบบช้าครับ

Post by mindphp »

ข้อมูลไม่ครบ หลอกให้คิดตั้งนาน :oops:

ส่วนเรื่อง สร้างตารางที่เพิ่ม รันวันละครั้งก็พอครับ
ไม่ต้องไปอัพแดดตอนที่เพิ่มข้อมูลด้วย
ทำ script แยกออกมา เพื่อทำงานกับข้อมูลตารางนั้นตารางเดียว สมุมติเราตั้งให้มันทำงาน ตอนเทียงคืนของทุกวัน มันก็จะได้ว่า เมื่อวาน max id ที่เท่าไร มา ตารางนี้ไม่เกี่ยวข้องกับงานเลย มีหน้าที่ช่วย ลดช่วงข้อมูลที่จะไปค้นด้วย เงือนไขอื่นๆ ต่อ แต่ตอนนี้ มีประเด็น record_date เข้ามาก็ยุ่งเข้าอีกหน่อย คิดต่อล่ะกันนะครับ :-D

ถ้าตารางนี้ไม่ได้ใช้ ที่ script อื่น ลองตั้งให้ค่ามาตรฐานในการเรียงของตารางเป็น record_date ไว้ก่อนซิครับ
แล้วยก ORDER BY record_date ออกช่วยให้เร็วขึ้นได้อีกครับ
ติดตาม VDO: http://www.youtube.com/c/MindphpVideoman
ติดตาม FB: https://www.facebook.com/pages/MindphpC ... 9517401606
หมวดแชร์ความรู้: https://www.mindphp.com/forums/viewforum.php?f=29
รับอบรม และพัฒนาระบบ: https://www.mindphp.com/forums/viewtopic.php?f=6&t=2042
Post Reply
  • Similar Topics
    Replies
    Views
    Last post

Return to “SQL - Database”

Who is online

Users browsing this forum: No registered users and 9 guests