ฉันจำได้ครั้งแรกที่ฉันเห็น ActiveRecord ของราง มันเป็นการเปิดเผย ย้อนกลับไปในปี 2548 และฉันกำลังเขียนโค้ดคำสั่ง SQL ด้วยมือสำหรับแอป PHP ทันใดนั้น การใช้ฐานข้อมูลเปลี่ยนจากงานน่าเบื่อ เป็นงานง่ายและ - ฉันกล้าพูด - สนุก
...จากนั้น ผมก็เริ่มสังเกตเห็นปัญหาด้านประสิทธิภาพ
ActiveRecord ไม่ได้ช้า ฉันเพิ่งหยุดให้ความสนใจกับข้อความค้นหาที่กำลังดำเนินการอยู่ และปรากฎว่าการสืบค้นฐานข้อมูลที่มีสำนวนส่วนใหญ่ที่ใช้ในแอพ Rails CRUD เป็นค่าเริ่มต้นที่ค่อนข้างแย่ในการปรับขนาดเป็นชุดข้อมูลที่ใหญ่ขึ้น
ในบทความนี้เราจะพูดถึงสามผู้กระทำผิดที่ใหญ่ที่สุด แต่ก่อนอื่น มาพูดถึงวิธีที่คุณสามารถบอกได้ว่าการสืบค้นฐานข้อมูลของคุณจะปรับขนาดได้ดีหรือไม่
การวัดประสิทธิภาพ
ทุกการสืบค้นฐานข้อมูลจะมีประสิทธิภาพหากคุณมีชุดข้อมูลขนาดเล็กเพียงพอ ดังนั้น เพื่อให้เข้าใจถึงประสิทธิภาพได้อย่างแท้จริง เราจำเป็นต้องเปรียบเทียบกับฐานข้อมูลขนาดจริง ในตัวอย่างของเรา เราจะใช้ตารางชื่อ faults มีประมาณ 22,000 รายการ
เราใช้ postgres ใน postgres วิธีที่คุณวัดประสิทธิภาพคือการใช้ explain . ตัวอย่างเช่น:
# explain (analyze) select * from faults where id = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Index Scan using faults_pkey on faults (cost=0.29..8.30 rows=1 width=1855) (actual time=0.556..0.556 rows=0 loops=1)
Index Cond: (id = 1)
Total runtime: 0.626 ms
ซึ่งแสดงทั้งค่าใช้จ่ายโดยประมาณในการดำเนินการค้นหา (cost=0.29..8.30 rows=1 width=1855) และเวลาจริงที่ใช้ในการดำเนินการ (actual time=0.556..0.556 rows=0 loops=1)
หากคุณต้องการรูปแบบที่อ่านง่ายขึ้น คุณสามารถขอให้ postgres พิมพ์ผลลัพธ์ใน YAML
# explain (analyze, format yaml) select * from faults where id = 1;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Forward" +
Index Name: "faults_pkey" +
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.29 +
Total Cost: 8.30 +
Plan Rows: 1 +
Plan Width: 1855 +
Actual Startup Time: 0.008 +
Actual Total Time: 0.008 +
Actual Rows: 0 +
Actual Loops: 1 +
Index Cond: "(id = 1)" +
Rows Removed by Index Recheck: 0+
Triggers: +
Total Runtime: 0.036
(1 row)
สำหรับตอนนี้ เราจะเน้นเฉพาะ "แถวแผน" และ "แถวจริง" เท่านั้น
- วางแผนแถว ในกรณีที่เลวร้ายที่สุด ฐานข้อมูลจะต้องวนซ้ำกี่แถวเพื่อตอบคำถามของคุณ
- แถวจริง เมื่อดำเนินการสืบค้น DB วนซ้ำจริงกี่แถว
หาก "Plan Rows" เป็น 1 เช่นเดียวกับด้านบน แสดงว่าคิวรีมีแนวโน้มที่ดี หาก "Plan Rows" เท่ากับจำนวนแถวในฐานข้อมูล แสดงว่าคิวรีกำลัง "สแกนตารางแบบเต็ม" และไม่สามารถปรับขนาดได้ดี
ตอนนี้คุณรู้วิธีวัดประสิทธิภาพการสืบค้นแล้ว มาดูสำนวนเกี่ยวกับคอมมอนเรลกันและดูว่ามันซ้อนกันอย่างไร
นับ
เป็นเรื่องปกติที่จะเห็นโค้ดแบบนี้ในมุมมอง Rails:
Total Faults <%= Fault.count %>
ส่งผลให้ SQL มีลักษณะดังนี้:
select count(*) from faults;
มาเสียบเพื่อ explain และดูว่าเกิดอะไรขึ้น
# explain (analyze, format yaml) select count(*) from faults;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Aggregate" +
Strategy: "Plain" +
Startup Cost: 1840.31 +
Total Cost: 1840.32 +
Plan Rows: 1 +
Plan Width: 0 +
Actual Startup Time: 24.477 +
Actual Total Time: 24.477 +
Actual Rows: 1 +
Actual Loops: 1 +
Plans: +
- Node Type: "Seq Scan" +
Parent Relationship: "Outer"+
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.00 +
Total Cost: 1784.65 +
Plan Rows: 22265 +
Plan Width: 0 +
Actual Startup Time: 0.311 +
Actual Total Time: 22.839 +
Actual Rows: 22265 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 24.555
(1 row)
ว้าว! แบบสอบถามการนับอย่างง่ายของเรากำลังวนซ้ำมากกว่า 22,265 แถว — ทั้งตาราง! ใน postgres การนับจะวนซ้ำตลอดทั้งชุดระเบียน
คุณลดขนาดของชุดระเบียนได้โดยเพิ่ม where เงื่อนไขในการสอบถาม คุณอาจได้ขนาดที่ต่ำพอสำหรับประสิทธิภาพที่ยอมรับได้ ทั้งนี้ขึ้นอยู่กับความต้องการของคุณ
วิธีอื่นในการแก้ไขปัญหานี้คือแคชค่าการนับของคุณ Rails สามารถทำเช่นนี้ได้หากคุณตั้งค่า:
belongs_to :project, :counter_cache => true
มีทางเลือกอื่นให้เลือกเมื่อตรวจสอบเพื่อดูว่าแบบสอบถามส่งกลับระเบียนใดๆ หรือไม่ แทนที่จะเป็น Users.count > 0 , ลอง Users.exists? . แบบสอบถามที่เป็นผลลัพธ์มีประสิทธิภาพมากขึ้น (ขอบคุณผู้อ่าน Gerry Shaw ที่ชี้สิ่งนี้ให้ฉันฟัง)
การเรียงลำดับ
หน้าดัชนี เกือบทุกแอปมีอย่างน้อยหนึ่งแอป คุณดึงระเบียนล่าสุด 20 รายการจากฐานข้อมูลและแสดงข้อมูลเหล่านั้น อะไรจะง่ายกว่านี้?
รหัสสำหรับโหลดบันทึกอาจมีลักษณะดังนี้:
@faults = Fault.order(created_at: :desc)
sql ที่มีลักษณะดังนี้:
select * from faults order by created_at desc;
มาวิเคราะห์กัน:
# explain (analyze, format yaml) select * from faults order by created_at desc;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Sort" +
Startup Cost: 39162.46 +
Total Cost: 39218.12 +
Plan Rows: 22265 +
Plan Width: 1855 +
Actual Startup Time: 75.928 +
Actual Total Time: 86.460 +
Actual Rows: 22265 +
Actual Loops: 1 +
Sort Key: +
- "created_at" +
Sort Method: "external merge" +
Sort Space Used: 10752 +
Sort Space Type: "Disk" +
Plans: +
- Node Type: "Seq Scan" +
Parent Relationship: "Outer"+
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.00 +
Total Cost: 1784.65 +
Plan Rows: 22265 +
Plan Width: 1855 +
Actual Startup Time: 0.004 +
Actual Total Time: 4.653 +
Actual Rows: 22265 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 102.288
(1 row)
ที่นี่เราเห็นว่าฐานข้อมูลกำลังเรียงลำดับทั้งหมด 22,265 แถวทุกครั้งที่คุณทำแบบสอบถามนี้ ไม่มีบัวโน!
ตามค่าเริ่มต้น ทุกส่วนคำสั่ง "เรียงตาม" ใน SQL ของคุณจะทำให้ชุดระเบียนถูกจัดเรียงตามเวลาจริง ไม่มีการแคช ไม่มีเวทย์มนตร์ที่จะช่วยคุณ
วิธีแก้ไขคือการใช้ดัชนี สำหรับกรณีง่ายๆ เช่น กรณีนี้ การเพิ่มดัชนีที่จัดเรียงลงในคอลัมน์ created_at จะทำให้การสืบค้นเร็วขึ้นเล็กน้อย
ในการย้ายข้อมูล Rails ของคุณ คุณสามารถใส่:
class AddIndexToFaultCreatedAt < ActiveRecord::Migration
def change
add_index(:faults, :created_at)
end
end
ซึ่งรัน SQL ต่อไปนี้:
CREATE INDEX index_faults_on_created_at ON faults USING btree (created_at);
ในตอนท้าย (created_at) ระบุลำดับการจัดเรียง โดยค่าเริ่มต้นจะเป็นจากน้อยไปมาก
ตอนนี้ หากเราเรียกใช้การสืบค้นข้อมูลอีกครั้ง เราจะเห็นว่าไม่มีขั้นตอนการเรียงลำดับอีกต่อไป มันเพียงแค่อ่านข้อมูลที่จัดเรียงไว้ล่วงหน้าจากดัชนี
# explain (analyze, format yaml) select * from faults order by created_at desc;
QUERY PLAN
----------------------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Backward" +
Index Name: "index_faults_on_created_at"+
Relation Name: "faults" +
Alias: "faults" +
Startup Cost: 0.29 +
Total Cost: 5288.04 +
Plan Rows: 22265 +
Plan Width: 1855 +
Actual Startup Time: 0.023 +
Actual Total Time: 8.778 +
Actual Rows: 22265 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 10.080
(1 row)
หากคุณกำลังจัดเรียงตามคอลัมน์หลายคอลัมน์ คุณจะต้องสร้างดัชนีที่จัดเรียงตามคอลัมน์หลายคอลัมน์ด้วย นี่คือสิ่งที่ดูเหมือนในการโยกย้าย Rails:
add_index(:faults, [:priority, :created_at], order: {priority: :asc, created_at: :desc)
เมื่อคุณเริ่มทำการค้นหาที่ซับซ้อนมากขึ้น จะเป็นความคิดที่ดีที่จะเรียกใช้ผ่าน explain . ทำเร็วและบ่อยครั้ง คุณอาจพบว่าการเปลี่ยนแปลงอย่างง่ายในแบบสอบถามทำให้ postgres ใช้ดัชนีสำหรับการเรียงลำดับไม่ได้
ขีดจำกัดและออฟเซ็ต
ในหน้าดัชนีของเรา เราแทบไม่เคยรวมทุกรายการในฐานข้อมูล แต่เราแบ่งหน้า โดยแสดงรายการเพียง 10 หรือ 30 หรือ 50 รายการในแต่ละครั้ง วิธีที่ใช้บ่อยที่สุดคือการใช้ limit และ offset ด้วยกัน. ใน Rails จะมีลักษณะดังนี้:
Fault.limit(10).offset(100)
ที่สร้าง SQL ซึ่งมีลักษณะดังนี้:
select * from faults limit 10 offset 100;
ทีนี้ถ้าเราอธิบายไป เราจะเห็นอะไรแปลกๆ จำนวนแถวที่สแกนคือ 110 เท่ากับขีดจำกัดบวกออฟเซ็ต
# explain (analyze, format yaml) select * from faults limit 10 offset 100;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Limit" +
...
Plans: +
- Node Type: "Seq Scan" +
Actual Rows: 110 +
...
หากคุณเปลี่ยนออฟเซ็ตเป็น 10,000 คุณจะเห็นว่าจำนวนแถวที่สแกนเพิ่มขึ้นเป็น 10010 และการสืบค้นช้าลง 64 เท่า
# explain (analyze, format yaml) select * from faults limit 10 offset 10000;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Limit" +
...
Plans: +
- Node Type: "Seq Scan" +
Actual Rows: 10010 +
...
สิ่งนี้นำไปสู่ข้อสรุปที่น่ารำคาญ:เมื่อแบ่งหน้า หน้าหลัง ๆ จะโหลดช้ากว่าหน้าก่อนหน้า หากเราถือว่า 100 รายการต่อหน้าในตัวอย่างข้างต้น หน้า 100 จะช้ากว่าหน้าที่ 1 ถึง 13 เท่า
แล้วคุณจะทำอย่างไร?
ตรงไปตรงมา ฉันยังไม่สามารถหาวิธีแก้ปัญหาที่สมบูรณ์แบบได้ ฉันจะเริ่มต้นด้วยการดูว่าฉันสามารถลดขนาดของชุดข้อมูลได้หรือไม่ ดังนั้นฉันจึงไม่ต้องมีหน้า 100 หรือ 1,000 หน้าเพื่อเริ่มต้น
หากคุณไม่สามารถลดชุดบันทึกได้ คุณควรเปลี่ยนออฟเซ็ต/จำกัดด้วยคำสั่ง where
# You could use a date range
Fault.where("created_at > ? and created_at < ?", 100.days.ago, 101.days.ago)
# ...or even an id range
Fault.where("id > ? and id < ?", 100, 200)
บทสรุป
ฉันหวังว่าบทความนี้จะทำให้คุณเชื่อมั่นว่าคุณควรใช้ประโยชน์จากฟังก์ชันอธิบายของ postgres เพื่อค้นหาปัญหาด้านประสิทธิภาพที่เป็นไปได้กับการสืบค้นฐานข้อมูลของคุณ แม้แต่คำค้นหาที่ง่ายที่สุดก็อาจทำให้เกิดปัญหาด้านประสิทธิภาพที่สำคัญได้ ดังนั้นจึงต้องจ่ายเงินเพื่อตรวจสอบ :)
