สรุปฐานข้อมูล

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

เนื่องจากผมทำงานในบริษัทเล็กๆ ผมจึงทำทุกอย่าง ฉันคิดว่าบางครั้งการรักษาตามอาการยังดีกว่าการรักษาที่ต้นเหตุ! มีหลายประเด็นที่ต้องใส่ใจ:

1. การออกแบบตารางฐานข้อมูลต้องมีความสมเหตุสมผล โดยเฉพาะการออกแบบคีย์หลัก หากปริมาณข้อมูลในตารางมีขนาดใหญ่มาก การออกแบบคีย์หลักก็ไม่ควรมีความหมาย เช่นเดียวกับ ROWID เช่น GUID ของ SQL Server, UUID ของ Hibernate ฯลฯ แน่นอนว่า ตารางพจนานุกรมข้อมูลบางตารางสามารถประมวลผลได้อย่างยืดหยุ่น และไม่จำเป็นต้องพิจารณาว่าตารางเหล่านั้นต้องเป็นคีย์หลักจริง ในการออกแบบคีย์หลัก โดยทั่วไปจะไม่ใช้คีย์หลักแบบผสม

2. การจัดทำดัชนีที่สมเหตุสมผล Index เป็นเครื่องมืออันทรงพลังและเป็นวิธีที่ดีในการเร่งการสืบค้นข้อมูลของเรา แต่อย่าเพิ่มทุกช่อง หลักการของการจัดทำดัชนีก็เหมือนกับสารบัญของหนังสือ หากสารบัญในหนังสือของคุณมีชื่อเดียวกันเกือบทั้งหมด คุณสามารถจินตนาการได้ว่าจะค้นหาเนื้อหาเฉพาะตามสารบัญได้เร็วแค่ไหน ? ดัชนีไม่จำเป็นต้องไม่ซ้ำกัน แต่ก็ไม่ควรมีบันทึกที่เหมือนกันมากเกินไป นอกจากนี้ หากมีการเพิ่มดัชนีเพิ่มเติม พื้นที่ตาราง TEMP จะเพิ่มขึ้น เมื่อส่งออกตารางและนำเข้าไปยังฐานข้อมูลอื่น ดัชนีจะลดประสิทธิภาพของการนำเข้าด้วย ในเวลานี้ คุณจะพบว่าพื้นที่ตาราง UNDOTBS01 มีขนาดใหญ่ผิดปกติ ดังนั้นดัชนีจึงเป็นดาบสองคมและต้องใช้อย่างสมเหตุสมผล

3. ฉันเคยเห็นบทความระดับมืออาชีพเกี่ยวกับการเพิ่มประสิทธิภาพ SQL บนอินเทอร์เน็ต แต่ฉันรู้สึกว่าไม่สามารถใช้บทความเหล่านี้ในโครงการของฉันได้ แต่ฉันยังคงทดลองและค้นพบหลักการพื้นฐานบางอย่างในระหว่างโครงการแทน โดยส่วนตัวแล้วฉันคิดว่าการเพิ่มประสิทธิภาพ SQL มีเพียงหลักการเดียวเท่านั้น ซึ่งก็คือการจำกัดขอบเขตการสืบค้นให้แคบลงให้มากที่สุด ซึ่งจะช่วยปรับปรุงประสิทธิภาพได้อย่างแน่นอน และ Oracle เองก็สามารถเพิ่มประสิทธิภาพ SQL ที่เราเขียนได้ ดังนั้นสิ่งที่เราต้องทำก็คือ จำกัดขอบเขตการสืบค้นให้แคบลงมากที่สุด เมื่อพูดถึงสิ่งนี้ ฉันคิดว่าทุกคนคงคิดว่าการจัดทำดัชนีเป็นเครื่องมือที่มีประสิทธิภาพในการปรับปรุงความเร็วการสืบค้น .

SQL ส่วนใหญ่ที่ต้องได้รับการปรับให้เหมาะสมคือคิวรีการรวมหลายตาราง และการรวมหลายตารางยังรวมถึงการรวมแนวนอนและการรวมแนวตั้งที่เราใช้มากที่สุดคือการรวมแนวตั้ง โดยทั่วไปการเชื่อมต่อในแนวนอนหมายความว่าโครงสร้างเขตข้อมูลของสองตารางโดยพื้นฐานจะเหมือนกัน และบันทึกข้อมูลบางส่วนของตารางหนึ่งควรเปลี่ยนเป็นบันทึกของอีกตารางหนึ่ง นั่นคือ แถว+แถว การเชื่อมต่อในแนวตั้งหมายความว่าเราใช้บางฟิลด์ที่จะสอบถามจากตาราง A และบางฟิลด์ที่จะสอบถามจากตาราง B จากนั้นเชื่อมต่อตารางที่นำออกจากตาราง A และ B ในแนวตั้งโดยใช้ส่วนทั่วไป นั่นคือ คอลัมน์+คอลัมน์

คำสั่งการรวมแนวนอน: เลือก a.column1,a.column2 จาก tableA สหภาพทั้งหมด เลือก b.column1,b.column2 จาก tableB b

โปรดทราบว่าเมื่อเชื่อมต่อในแนวนอน จำนวนคอลัมน์จะต้องเท่ากัน และประเภทข้อมูลของคอลัมน์ฟิลด์ที่เกี่ยวข้องจะต้องเท่ากัน ในความเป็นจริง คุณสามารถนึกถึงตารางที่จะรวมเข้าด้วยกันเป็นสำเนาเดียวของอีกตารางหนึ่งได้เหมือนกันทุกประการ บางคนอาจถามว่าคอลัมน์ที่ฉันต้องการรวมมีคอลัมน์ต่างกันหรือไม่มีคอลัมน์เลย คุณสามารถใช้วิธีต่อไปนี้

เลือก d.dname,d.loc จาก dept1 d union ทั้งหมด เลือก '' dname, e.loc จาก dept e ดูที่ "'' dname" เราจะหาได้ง่ายๆ ว่าคุณสามารถหาสิ่งทดแทนได้ ใช้สตริงว่างแทน นั่น ไม่มีฟิลด์ จึงสามารถรวมเข้าด้วยกันได้

คำสั่งการรวมแนวตั้ง: เลือก a.column1,a.column2 จาก tableA การรวมภายนอกแบบเต็ม เลือก b.column3,b.column4 จาก tableB b บน a.aid=b.bid โดยที่... นี่คือรูปแบบการรวมภายนอกแบบเต็ม ความเร็วนี้เร็วมากจริงๆ แต่คุณอาจไม่ชอบข้อความค้นหา เนื่องจากมีแถวผลลัพธ์บางแถวที่คุณอาจไม่ต้องการเห็นเลย ภายใต้สถานการณ์ปกติ เราใช้การรวมภายนอกด้านซ้ายและการรวมภายนอกด้านขวามากกว่า ความแตกต่างระหว่างทั้งสองคือการรวมภายนอกด้านซ้ายนั้นส่วนใหญ่จะขึ้นอยู่กับตารางที่สอดคล้องกับฟิลด์การรวมทางด้านซ้ายหลังจากนั้น และการรวมภายนอกด้านขวาจะอยู่ตรงข้ามกัน แน่นอนคุณสามารถใช้การรวมทางซ้ายหรือทางขวาได้ ระหว่างใช้งานฉันยังพบว่าการเชื่อมต่อภายนอกค่อนข้างเร็วกว่า

เพื่อเร่งความเร็วประสิทธิภาพของการสืบค้นการเชื่อมต่อแนวตั้ง วิธีคือการซ้อนการสืบค้น ต่อไปนี้เป็นตัวอย่างจริงจากโครงการ:

เลือก c.customerid,c.receivedmoney,c.tollcollector,c.receiveddate,c.yearmonth,c.receivedlatefee,

c.receivedfee,c.receivedappend,c.jmman,c.jmmoney,c.name,d.chargeint จาก

(เลือก a.รหัสลูกค้า,a.receivedmoney,a.tollcollector,a.receiveddate,a.yearmonth,a.receivedlatefee,

ก.รับค่าธรรมเนียม,ก.รับเพิ่ม,ก.จมมาน,ก.จมเงิน,ข.ชื่อจาก

(เลือก rf.customerid,rf.receivedmoney,rf.tollcollector,rf.receiveddate,rf.yearmonth,rf.receivedlatefee,

rf.receivedfee, rf.receivedfee ผนวก, rf.jmman, rf.jmmoney จาก sf_receivedfee rf โดยที่

rf.electriccompanyid='1000000001' และ rf.dealsign=0 และ rf.yearเดือนใน(200811,200901,200903,200804,200805,200806,200807)

และ rf.customerid=1000052545) การรวมภายนอกด้านซ้าย (เลือก xe.employeeid,xe.name จาก xt_employee xe) b บน a.tollcollector=b.employeeid)

c การรวมด้านนอกด้านซ้าย (เลือก cp.chargeint,cp.customerid จาก sf_chargeprotocol cp โดยที่ cp.customerid=1000052545) d

บน c.customerid=d.customerid

คุณจะเห็นว่าในตัวอย่างนี้ ขั้นแรกเรากรองระเบียนที่เราต้องการจากแต่ละตารางโดยใช้เงื่อนไขเดียวกันเกือบทั้งหมด จากนั้นจึงรวมระเบียน ในการใช้งานจริง ฉันพบว่าการดำเนินการนี้เร็วกว่าการสืบค้นลิงก์โดยตรงเกือบ 60 เท่า แม้ว่าจะดูน่าเกลียดและอ่านยาก แต่ก็ช่วยแก้ปัญหาประสิทธิภาพของ SQL ได้ หลักการที่ใช้ยังคงจำกัดขอบเขตให้แคบลงก่อน จากนั้นจึงดำเนินการค้นหาการเชื่อมต่อ หากเราเชื่อมต่อแล้วกรอง จะเทียบเท่ากับการรวมสองตารางเข้าด้วยกัน จากนั้นจึงดึงข้อมูลตามเงื่อนไข ความเร็วสามารถจินตนาการได้