การปรับประสิทธิภาพของฐานข้อมูล SQL สำหรับนักพัฒนา

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

ในบริษัทขนาดกลางและขนาดใหญ่ การปรับประสิทธิภาพ SQL ส่วนใหญ่จะได้รับการจัดการโดยผู้ดูแลฐานข้อมูล (DBA) แต่เชื่อฉันเถอะ มีนักพัฒนาจำนวนมากที่ต้องทำงานเหมือน DBA นอกจากนี้ ในหลายบริษัทที่ ฉัน เห็นว่ามี DBA พวกเขามักจะพยายามทำงานได้ดีกับนักพัฒนา ตำแหน่งเพียงแค่ต้องใช้รูปแบบการแก้ปัญหาที่แตกต่างกัน ซึ่งอาจนำไปสู่ความขัดแย้งในหมู่เพื่อนร่วมงาน

ยิ่งไปกว่านั้น โครงสร้างองค์กรก็มีบทบาทเช่นกัน สมมติว่าทีม DBA อยู่ที่ชั้น 10 พร้อมฐานข้อมูลทั้งหมด ในขณะที่ผู้พัฒนาอยู่บนชั้น 15 หรือแม้แต่ในอาคารอื่นภายใต้โครงสร้างการรายงานที่แยกจากกันโดยสิ้นเชิง เป็นการยากที่จะทำงานร่วมกันได้อย่างราบรื่นภายใต้เงื่อนไขเหล่านี้

ในบทความนี้ ฉันต้องการทำสองสิ่งให้สำเร็จ:

1. จัดเตรียมเทคนิคการปรับแต่งประสิทธิภาพ SQL ด้านนักพัฒนาให้กับนักพัฒนา
2. อธิบายว่านักพัฒนาและ DBA สามารถทำงานร่วมกันอย่างมีประสิทธิภาพได้อย่างไร

การปรับแต่งประสิทธิภาพของ SQL (ใน Codebase): Indexes

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

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

หากคุณเพิ่งเริ่มใช้ดัชนี เราขอแนะนำให้ใช้ไดอะแกรมนี้เมื่อจัดโครงสร้างการสืบค้นข้อมูลของคุณ:

โดยพื้นฐานแล้ว เป้าหมายคือการจัดทำดัชนีคอลัมน์การค้นหาและการจัดลำดับที่สำคัญ

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

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

การปรับแต่ง SQL: แผนการดำเนินการใน SQL Server

โดยวิธีการ: เครื่องมือ Execution Plan ใน SQL Server อาจมีประโยชน์สำหรับการสร้างดัชนี

หน้าที่หลักของมันคือการแสดงวิธีการดึงข้อมูลแบบกราฟิกที่เลือกโดยเครื่องมือเพิ่มประสิทธิภาพการสืบค้น SQL Server หากคุณไม่เคยเห็นมาก่อนมีคำแนะนำแบบละเอียด

ในการดึงแผนการดำเนินการ (ใน SQL Server Management Studio) เพียงคลิก "รวมแผนการดำเนินการตามจริง" (CTRL + M) ก่อนเรียกใช้แบบสอบถามของคุณ

หลังจากนั้น แท็บที่สามชื่อ “แผนปฏิบัติการ” จะปรากฏขึ้น คุณอาจเห็นดัชนีที่ตรวจพบที่หายไป หากต้องการสร้าง ให้คลิกขวาในแผนการดำเนินการแล้วเลือก “รายละเอียดดัชนีที่ขาดหายไป…” มันง่ายอย่างนั้น!

( คลิกเพื่อซูม )

การปรับ SQL: หลีกเลี่ยงการเข้ารหัสลูป

ลองนึกภาพสถานการณ์ที่การค้นหา 1,000 ครั้งใช้ฐานข้อมูลของคุณตามลำดับ สิ่งที่ต้องการ:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

คุณควรหลีกเลี่ยงลูปดังกล่าวในโค้ดของคุณ ตัวอย่างเช่น เราสามารถแปลงข้อมูลโค้ดด้านบนโดยใช้คำสั่ง INSERT หรือ UPDATE ที่ไม่ซ้ำกับหลายแถวและค่า:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

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

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

การปรับแต่ง SQL: หลีกเลี่ยงแบบสอบถามย่อย SQL ที่สัมพันธ์กัน

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

ต่อไปนี้คือตัวอย่างของแบบสอบถามย่อยที่สัมพันธ์กัน:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

โดยเฉพาะอย่างยิ่ง ปัญหาคือคิวรีภายใน ( SELECT CompanyName… ) ถูกเรียกใช้สำหรับ แต่ละ แถวที่ส่งคืนโดยเคียวรีภายนอก ( SELECT c.Name… ) แต่ทำไมต้องมองข้าม Company ครั้งแล้วครั้งเล่าสำหรับทุกแถวที่ประมวลผลโดยแบบสอบถามภายนอก?

เทคนิคการปรับแต่งประสิทธิภาพ SQL ที่มีประสิทธิภาพมากขึ้นคือการปรับโครงสร้างแบบสอบถามย่อยที่สัมพันธ์กันเป็นการรวม:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

ในกรณีนี้ เราจะดูตาราง Company เพียงครั้งเดียว ในตอนเริ่มต้น และเข้าร่วมกับตาราง Customer จากนั้น เราสามารถเลือกค่าที่เราต้องการ ( co.CompanyName ) ได้อย่างมีประสิทธิภาพมากขึ้น

การปรับแต่ง SQL: เลือกเท่าที่จำเป็น

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

ตัวอย่างเช่น:

 SELECT * FROM Employees

เทียบกับ

 SELECT FirstName, City, Country FROM Employees

หากคุณต้องการทุกคอลัมน์จริงๆ ให้ระบุทุกคอลัมน์อย่างชัดเจน นี่ไม่ใช่กฎมากนัก แต่เป็นวิธีการป้องกันข้อผิดพลาดของระบบในอนาคตและการปรับประสิทธิภาพของ SQL เพิ่มเติม ตัวอย่างเช่น หากคุณใช้ INSERT... SELECT... และตารางต้นทางมีการเปลี่ยนแปลงโดยการเพิ่มคอลัมน์ใหม่ คุณอาจพบปัญหาแม้ว่าตารางปลายทางจะไม่ต้องการคอลัมน์นั้นก็ตาม เช่น:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

เพื่อหลีกเลี่ยงข้อผิดพลาดประเภทนี้จาก SQL Server คุณควรประกาศแต่ละคอลัมน์แยกกัน:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

อย่างไรก็ตาม โปรดทราบว่ามีบางสถานการณ์ที่การใช้ SELECT * อาจมีความเหมาะสม ตัวอย่างเช่น กับตารางชั่วคราว—ซึ่งนำเราไปสู่หัวข้อถัดไป

การปรับแต่ง SQL: การใช้ตารางชั่วคราวอย่างชาญฉลาด (#Temp)

ตารางชั่วคราวมักจะเพิ่มความซับซ้อนของคิวรี หากโค้ดของคุณสามารถเขียนในลักษณะที่เรียบง่าย ตรงไปตรงมา เราขอแนะนำให้คุณหลีกเลี่ยงตารางชั่วคราว

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

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

การตัดสินใจไม่ได้ตรงไปตรงมาเสมอไป แต่ตัวอย่างนี้จะช่วยให้คุณเข้าใจสถานการณ์ที่คุณอาจต้องการใช้ตารางชั่วคราว:

ลองนึกภาพตารางลูกค้าที่มีระเบียนนับล้าน คุณต้องทำการเข้าร่วมในภูมิภาคเฉพาะ คุณสามารถทำได้โดยใช้คำสั่ง SELECT INTO จากนั้นเข้าร่วมกับตาราง temp:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( หมายเหตุ: นักพัฒนา SQL บางคนยังหลีกเลี่ยงการใช้ SELECT INTO เพื่อสร้างตารางชั่วคราว โดยบอกว่าคำสั่งนี้ล็อคฐานข้อมูล tempdb ไม่อนุญาตให้ผู้ใช้รายอื่นสร้างตารางชั่วคราว โชคดีที่สิ่งนี้ได้รับการแก้ไขใน 7.0 และใหม่กว่า )

เป็นทางเลือกแทนตารางชั่วคราว คุณอาจพิจารณาใช้แบบสอบถามย่อยเป็นตาราง:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

แต่เดี๋ยวก่อน! มีปัญหากับข้อความค้นหาที่สองนี้ ตามที่อธิบายไว้ข้างต้น เราควรรวมเฉพาะคอลัมน์ที่เราต้องการในแบบสอบถามย่อยของเรา (เช่น ไม่ใช้ SELECT * ) โดยคำนึงถึงสิ่งนั้น:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

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

สุดท้าย เมื่อคุณใช้ตาราง temp เสร็จแล้ว ให้ลบออกเพื่อล้างทรัพยากร tempdb แทนที่จะรอให้ถูกลบโดยอัตโนมัติ (เช่นเมื่อการเชื่อมต่อกับฐานข้อมูลของคุณสิ้นสุดลง):

 DROP TABLE #temp

การปรับแต่ง SQL: “มีบันทึกของฉันหรือไม่”

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

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

เทียบกับ

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

การปรับประสิทธิภาพของ SQL ด้วย SQL Server 2016

เนื่องจาก DBA ที่ทำงานกับ SQL Server 2016 มักจะรับรู้ เวอร์ชันดังกล่าวจึงเป็นการเปลี่ยนแปลงที่สำคัญในค่าเริ่มต้นและการจัดการความเข้ากันได้ แน่นอนว่าในเวอร์ชันหลักนั้นมาพร้อมกับการเพิ่มประสิทธิภาพการสืบค้นใหม่ แต่ควบคุมว่าขณะนี้มีการใช้หรือไม่ จะได้รับการปรับปรุงให้ดีขึ้นผ่าน sys.databases.compatibility_level

การปรับประสิทธิภาพของ SQL (ใน Office)

ผู้ดูแลระบบฐานข้อมูล SQL (DBA) และนักพัฒนามักขัดแย้งกันในเรื่องข้อมูลและปัญหาที่ไม่เกี่ยวกับข้อมูล จากประสบการณ์ของผม นี่คือเคล็ดลับบางประการ (สำหรับทั้งสองฝ่าย) เกี่ยวกับวิธีการทำงานร่วมกันอย่างมีประสิทธิภาพ

ทวีต

การเพิ่มประสิทธิภาพฐานข้อมูลสำหรับนักพัฒนา:

1. หากแอปพลิเคชันของคุณหยุดทำงานกะทันหัน อาจไม่ใช่ปัญหาฐานข้อมูล ตัวอย่างเช่น คุณอาจมีปัญหาเกี่ยวกับเครือข่าย ตรวจสอบสักนิดก่อนกล่าวหา DBA!

2. แม้ว่าคุณจะเป็นนักสร้างแบบจำลองข้อมูล SQL ของนินจา ขอให้ DBA ช่วยคุณเกี่ยวกับไดอะแกรมเชิงสัมพันธ์ของคุณ พวกเขามีจำนวนมากที่จะแบ่งปันและนำเสนอ

3. DBA ไม่ชอบการเปลี่ยนแปลงอย่างรวดเร็ว นี่เป็นเรื่องปกติ: พวกเขาจำเป็นต้องวิเคราะห์ฐานข้อมูลโดยรวมและตรวจสอบผลกระทบของการเปลี่ยนแปลงจากทุกมุม การเปลี่ยนแปลงง่ายๆ ในคอลัมน์อาจใช้เวลาหนึ่งสัปดาห์ในการดำเนินการ—แต่นั่นเป็นเพราะข้อผิดพลาดอาจกลายเป็นความสูญเสียครั้งใหญ่สำหรับบริษัท อดทนไว้!

4. อย่าขอให้ SQL DBA ทำการเปลี่ยนแปลงข้อมูลในสภาพแวดล้อมที่ใช้งานจริง หากคุณต้องการเข้าถึงฐานข้อมูลที่ใช้งานจริง คุณต้องรับผิดชอบต่อการเปลี่ยนแปลงทั้งหมดของคุณเอง

การเพิ่มประสิทธิภาพฐานข้อมูลสำหรับ SQL Server DBA:

1. หากคุณไม่ชอบให้ใครมาถามคุณเกี่ยวกับฐานข้อมูล ให้สร้างแผงสถานะแบบเรียลไทม์ให้พวกเขา นักพัฒนามักสงสัยสถานะของฐานข้อมูล และแผงดังกล่าวสามารถช่วยประหยัดเวลาและพลังงานของทุกคนได้

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

3. นักพัฒนาใช้เวลาทั้งวันกับระบบที่มีตรรกะทางธุรกิจที่เปลี่ยนแปลงบ่อย พยายามทำความเข้าใจว่าโลกนี้มีความยืดหยุ่นมากขึ้น และสามารถแหกกฎบางอย่างในช่วงเวลาที่สำคัญได้

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