การปรับแต่งประสิทธิภาพของ SQL อาจเป็นงานที่ยากอย่างไม่น่าเชื่อโดยเฉพาะอย่างยิ่งเมื่อทำงานกับข้อมูลขนาดใหญ่ซึ่งแม้แต่การเปลี่ยนแปลงเล็กน้อยที่สุดก็อาจส่งผลกระทบอย่างมาก (เชิงบวกหรือเชิงลบ) ต่อประสิทธิภาพ
ใน บริษัท ขนาดกลางและขนาดใหญ่การปรับแต่งประสิทธิภาพ SQL ส่วนใหญ่จะได้รับการจัดการโดย Database Administrator (DBA) แต่เชื่อฉันมี นักพัฒนามากมาย ใครที่ต้องทำงานเหมือน DBA นอกจากนี้ในหลาย ๆ บริษัท ฉันได้เห็นเช่นนั้น ทำ มี DBA พวกเขามักจะดิ้นรนเพื่อทำงานร่วมกับนักพัฒนาได้ดีตำแหน่งงานนั้นต้องการรูปแบบการแก้ปัญหาที่แตกต่างกันซึ่งอาจนำไปสู่ความไม่ลงรอยกันในหมู่เพื่อนร่วมงาน
เมื่อทำงานกับข้อมูลขนาดใหญ่แม้แต่การเปลี่ยนแปลงเล็กน้อยที่สุดก็สามารถส่งผลกระทบอย่างมากต่อประสิทธิภาพการทำงานยิ่งไปกว่านั้นโครงสร้างองค์กรยังสามารถมีบทบาท สมมติว่าทีม DBA อยู่ที่ชั้น 10 พร้อมฐานข้อมูลทั้งหมดในขณะที่นักพัฒนาซอฟต์แวร์อยู่ที่ชั้น 15 หรือแม้กระทั่งในอาคารอื่นภายใต้โครงสร้างการรายงานที่แยกจากกันโดยสิ้นเชิงแน่นอนว่ายากที่จะทำงานร่วมกันอย่างราบรื่นภายใต้เงื่อนไขเหล่านี้
ในบทความนี้ฉันต้องการทำสองสิ่งให้สำเร็จ:
หากคุณเป็นผู้มาใหม่ในฐานข้อมูลและถามตัวเองว่า“ SQL คืออะไร การปรับแต่งประสิทธิภาพ ?” คุณควรทราบว่าการจัดทำดัชนีเป็นวิธีที่มีประสิทธิภาพในการปรับแต่งฐานข้อมูล SQL ของคุณซึ่งมักถูกละเลยในระหว่างการพัฒนา ในแง่พื้นฐาน an ดัชนี เป็นโครงสร้างข้อมูลที่ปรับปรุงความเร็วของการดำเนินการดึงข้อมูลบนตารางฐานข้อมูลโดยจัดเตรียมการค้นหาแบบสุ่มอย่างรวดเร็วและการเข้าถึงเรกคอร์ดที่สั่งอย่างมีประสิทธิภาพ ซึ่งหมายความว่าเมื่อคุณสร้างดัชนีแล้วคุณสามารถเลือกหรือจัดเรียงแถวได้เร็วขึ้นกว่าเดิม
ดัชนียังใช้เพื่อกำหนดคีย์หลักหรือดัชนีเฉพาะซึ่งจะรับประกันได้ว่าไม่มีคอลัมน์อื่นที่มีค่าเหมือนกัน แน่นอนว่าการจัดทำดัชนีฐานข้อมูลเป็นหัวข้อที่น่าสนใจมากมายซึ่งฉันไม่สามารถใช้คำอธิบายสั้น ๆ นี้ได้ (แต่ นี่คือการเขียนรายละเอียดเพิ่มเติม ).
หากคุณไม่คุ้นเคยกับการจัดทำดัชนีขอแนะนำให้ใช้แผนภาพนี้เมื่อจัดโครงสร้างคำค้นหาของคุณ:
โดยทั่วไปเป้าหมายคือการจัดทำดัชนีคอลัมน์การค้นหาและลำดับที่สำคัญ
โปรดทราบว่าหากตารางของคุณถูกตอกโดย INSERT
, UPDATE
และ DELETE
อย่างต่อเนื่องคุณควรระมัดระวังในการจัดทำดัชนี - คุณอาจจบลง ประสิทธิภาพลดลง เนื่องจากดัชนีทั้งหมดต้องได้รับการแก้ไขหลังจากการดำเนินการเหล่านี้
นอกจากนี้ DBA มักจะทิ้งดัชนี SQL ของพวกเขาก่อนที่จะดำเนินการแทรกแบตช์ของแถวล้านบวกไปที่ เร่งกระบวนการแทรก . หลังจากใส่แบตช์แล้วพวกเขาจะสร้างดัชนีขึ้นมาใหม่ อย่างไรก็ตามโปรดจำไว้ว่าดัชนีที่ลดลงจะส่งผลต่อทุกแบบสอบถามที่ทำงานในตารางนั้น ดังนั้นจึงแนะนำให้ใช้วิธีนี้เมื่อทำงานกับการแทรกขนาดใหญ่เพียงครั้งเดียว
โดยวิธีการ: เครื่องมือ Execution Plan ใน SQL Server มีประโยชน์สำหรับการสร้างดัชนี
หน้าที่หลักคือการแสดงวิธีการดึงข้อมูลแบบกราฟิกที่เลือกโดยเครื่องมือเพิ่มประสิทธิภาพการสืบค้น SQL Server หากคุณไม่เคยเห็นมาก่อนก็มี คำแนะนำโดยละเอียด .
ในการดึงแผนการดำเนินการ (ใน SQL Server Management Studio) เพียงคลิก“ รวมแผนการดำเนินการจริง” (CTRL + M) ก่อนเรียกใช้แบบสอบถามของคุณ
หลังจากนั้นแท็บที่สามชื่อ“ แผนการดำเนินการ” จะปรากฏขึ้น คุณอาจเห็นดัชนีที่ขาดหายไปที่ตรวจพบ ในการสร้างเพียงคลิกขวาในแผนการดำเนินการแล้วเลือก“ ไม่มีรายละเอียดดัชนี…” ง่ายๆแค่นั้นเอง!
( คลิกเพื่อซูม )
ลองนึกภาพสถานการณ์ที่แบบสอบถาม 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 รายใหม่มักถูกจับได้ว่าจัดโครงสร้างคำค้นหาด้วยวิธีนี้เพราะโดยปกติแล้วจะเป็นเส้นทางที่ง่าย
นี่คือตัวอย่างของการสืบค้นย่อยที่สัมพันธ์กัน:
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 ที่มีประสิทธิภาพมากขึ้นคือการ refactor เคียวรีย่อยที่สัมพันธ์กันเป็นการรวม:
SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID
ในกรณีนี้เราจะพูดถึง Company
เพียงครั้งเดียวในตอนเริ่มต้นและเข้าร่วมด้วย Customer
โต๊ะ. จากนั้นเราสามารถเลือกค่าที่เราต้องการ (co.CompanyName
) ได้อย่างมีประสิทธิภาพมากขึ้น
หนึ่งในเคล็ดลับการเพิ่มประสิทธิภาพ 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 *
อาจจะเหมาะสม ตัวอย่างเช่นด้วยตารางชั่วคราวซึ่งจะนำเราไปสู่หัวข้อถัดไป
ตารางชั่วคราว มักจะเพิ่มความซับซ้อนของข้อความค้นหา หากโค้ดของคุณสามารถเขียนได้อย่างเรียบง่ายตรงไปตรงมาฉันขอแนะนำให้หลีกเลี่ยงตารางชั่วคราว
แต่ถ้าคุณมีขั้นตอนการจัดเก็บที่มีการจัดการข้อมูลบางอย่าง ไม่ได้ จัดการด้วยแบบสอบถามเดียวคุณสามารถใช้ตารางชั่วคราวเป็นตัวกลางเพื่อช่วยคุณสร้างผลลัพธ์สุดท้าย
เมื่อคุณต้องเข้าร่วมตารางขนาดใหญ่และมีเงื่อนไขในตารางดังกล่าวคุณสามารถเพิ่มประสิทธิภาพของฐานข้อมูลได้โดยการถ่ายโอนข้อมูลของคุณในตารางชั่วคราวจากนั้นทำการเข้าร่วม ที่ . ตารางชั่วคราวของคุณจะมีแถวน้อยกว่าตารางเดิม (ใหญ่) ดังนั้นการเข้าร่วมจะเสร็จเร็วขึ้น!
การตัดสินใจไม่ได้ตรงไปตรงมาเสมอไป แต่ตัวอย่างนี้จะช่วยให้คุณเข้าใจสถานการณ์ที่คุณอาจต้องการใช้ตารางชั่วคราว:
ลองนึกภาพโต๊ะลูกค้าที่มีประวัตินับล้านรายการ คุณต้องทำการเข้าร่วมในภูมิภาคเฉพาะ คุณสามารถทำได้โดยใช้ SELECT INTO
จากนั้นเข้าร่วมกับตารางชั่วคราว:
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 ทั้งหมดเหล่านี้จะส่งคืนข้อมูลเดียวกัน แต่ด้วยตารางชั่วคราวเราสามารถสร้างดัชนีในตารางอุณหภูมิเพื่อปรับปรุงประสิทธิภาพได้ มีการอภิปรายที่ดี ที่นี่ เกี่ยวกับความแตกต่างระหว่างตารางชั่วคราวและแบบสอบถามย่อย
สุดท้ายเมื่อคุณทำตารางชั่วคราวเสร็จแล้วให้ลบออกเพื่อล้างทรัพยากร tempdb แทนที่จะรอให้มันถูกลบโดยอัตโนมัติ (เช่นเดียวกับเมื่อคุณสิ้นสุดการเชื่อมต่อกับฐานข้อมูล):
DROP TABLE #temp
เทคนิคการเพิ่มประสิทธิภาพ 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'
เนื่องจาก DBA ที่ทำงานกับ SQL Server 2016 น่าจะทราบแล้วเวอร์ชันนี้จึงมีการเปลี่ยนแปลงที่สำคัญใน ค่าเริ่มต้นและการจัดการความเข้ากันได้ . แน่นอนว่าในเวอร์ชันหลักมาพร้อมกับการเพิ่มประสิทธิภาพการสืบค้นใหม่ แต่ตอนนี้การควบคุมว่าจะใช้หรือไม่ผ่าน sys.databases.compatibility_level
ผู้ดูแลระบบฐานข้อมูล SQL (DBA) และนักพัฒนามักขัดแย้งกันในเรื่องข้อมูลและปัญหาที่ไม่เกี่ยวข้องกับข้อมูล จากประสบการณ์ของฉันต่อไปนี้เป็นเคล็ดลับ (สำหรับทั้งสองฝ่าย) ในการเข้ากันและทำงานร่วมกันอย่างมีประสิทธิภาพ
หากแอปพลิเคชันของคุณหยุดทำงานกะทันหันอาจไม่ใช่ปัญหาฐานข้อมูล ตัวอย่างเช่นคุณอาจมีปัญหาเครือข่าย ตรวจสอบสักหน่อยก่อนที่คุณจะกล่าวหา DBA!
แม้ว่าคุณจะเป็นผู้สร้างโมเดลข้อมูล SQL แบบนินจา แต่ขอให้ DBA ช่วยคุณในการสร้างแผนภาพเชิงสัมพันธ์ พวกเขามีจำนวนมากที่จะแบ่งปันและนำเสนอ
DBA ไม่ชอบการเปลี่ยนแปลงอย่างรวดเร็ว นี่เป็นเรื่องธรรมดาพวกเขาจำเป็นต้องวิเคราะห์ฐานข้อมูลโดยรวมและตรวจสอบผลกระทบของการเปลี่ยนแปลงจากทุกมุม การเปลี่ยนแปลงง่ายๆในคอลัมน์อาจใช้เวลาหนึ่งสัปดาห์ในการดำเนินการ แต่นั่นเป็นเพราะข้อผิดพลาดอาจกลายเป็นความสูญเสียครั้งใหญ่สำหรับ บริษัท อดใจรอ!
อย่าขอให้ SQL DBA ทำการเปลี่ยนแปลงข้อมูลในสภาพแวดล้อมการใช้งานจริง หากคุณต้องการเข้าถึงฐานข้อมูลการผลิตคุณต้องรับผิดชอบต่อการเปลี่ยนแปลงทั้งหมดของคุณเอง
หากคุณไม่ชอบให้คนอื่นถามคุณเกี่ยวกับฐานข้อมูลให้แสดงแผงสถานะแบบเรียลไทม์ นักพัฒนา มักจะสงสัยในสถานะของฐานข้อมูลและแผงควบคุมดังกล่าวสามารถช่วยประหยัดเวลาและพลังงานของทุกคนได้
ช่วยเหลือนักพัฒนาในสภาพแวดล้อมการทดสอบ / การประกันคุณภาพ ทำให้ง่ายต่อการจำลองเซิร์ฟเวอร์ที่ใช้งานจริงด้วยการทดสอบอย่างง่าย ๆ กับข้อมูลจริง นี่จะช่วยประหยัดเวลาได้อย่างมีนัยสำคัญสำหรับผู้อื่นและตัวคุณเอง
นักพัฒนาใช้เวลาทั้งวันกับระบบด้วยตรรกะทางธุรกิจที่เปลี่ยนแปลงบ่อย พยายามทำความเข้าใจว่าโลกนี้มีความยืดหยุ่นมากขึ้นและสามารถทำลายกฎเกณฑ์บางอย่างได้ในช่วงเวลาสำคัญ
ฐานข้อมูล SQL มีวิวัฒนาการ วันนั้นจะมาถึงเมื่อคุณต้องย้ายข้อมูลของคุณไปยังเวอร์ชันใหม่ นักพัฒนาเชื่อมั่นในฟังก์ชันใหม่ ๆ ที่สำคัญในเวอร์ชันใหม่แต่ละเวอร์ชัน แทนที่จะปฏิเสธที่จะยอมรับการเปลี่ยนแปลงให้วางแผนล่วงหน้าและเตรียมพร้อมสำหรับการย้ายถิ่นฐาน
ระบบการจัดการฐานข้อมูลเช่น SQL Server ต้องแปลแบบสอบถาม SQL ที่คุณให้ไว้เป็นคำแนะนำจริงที่ต้องดำเนินการเพื่ออ่านหรือเปลี่ยนแปลงข้อมูลในฐานข้อมูล หลังจากการประมวลผลเอ็นจินฐานข้อมูลจะพยายามปรับแบบสอบถามให้เหมาะสมโดยอัตโนมัติหากเป็นไปได้
การเพิ่มประสิทธิภาพการสืบค้นคือเมื่อนักพัฒนาหรือเอ็นจินฐานข้อมูลเปลี่ยนแปลงคิวรีในลักษณะที่ SQL Server สามารถส่งคืนผลลัพธ์เดียวกันได้อย่างมีประสิทธิภาพมากขึ้น บางครั้งมันง่ายเหมือนการใช้ EXISTS () แทน COUNT () แต่ในบางครั้งการสืบค้นจะต้องเขียนใหม่ด้วยวิธีการอื่น
การปรับแต่งประสิทธิภาพรวมถึงการเพิ่มประสิทธิภาพการสืบค้นการเพิ่มประสิทธิภาพโค้ดไคลเอ็นต์ SQL การจัดการดัชนีฐานข้อมูลและในอีกแง่หนึ่งคือการประสานงานที่ดีขึ้นระหว่างนักพัฒนาและ DBA
ดัชนีติดตามชุดย่อยที่เป็นเป้าหมายของข้อมูลในตารางเพื่อให้การเลือกและการสั่งซื้อทำได้เร็วขึ้นมากโดยที่เซิร์ฟเวอร์ไม่ต้องดูข้อมูลทุกบิตสุดท้ายของตารางนั้น
EXISTS () หยุดประมวลผลทันทีที่พบแถวที่ตรงกันในขณะที่ COUNT () ต้องนับทุกแถวโดยไม่คำนึงว่าคุณต้องการรายละเอียดนั้นจริงหรือไม่ในตอนท้าย