ระดับความรู้ที่จำเป็นในการทำความเข้าใจบทความ:ความเข้าใจทั่วไปเกี่ยวกับฐานข้อมูลและ SQL ประสบการณ์เชิงปฏิบัติบางอย่างกับ DBMS
สิ่งที่สำคัญที่สุดที่คุณสามารถเรียนรู้ในการเขียนคำสั่ง SQL ที่มีประสิทธิภาพได้คือการจัดทำดัชนี อย่างไรก็ตาม อันดับที่สอง ซึ่งตามหลังมากคือความรู้ที่ว่าไคลเอ็นต์ SQL จำนวนมากต้องการให้ฐานข้อมูลทำงาน"งานที่ไม่จำเป็น แต่จำเป็น " จำนวน มาก พูดตามฉัน:
“งานที่ไม่จำเป็นแต่เป็นภาระผูกพัน” คืออะไร? ดังที่ Captain Obvious บอกเรา เธอ:
ไม่มีอะไรผิดปกติ เรากำลังทำงานร่วมกับฐานข้อมูลภาพยนตร์ (เช่นฐานข้อมูล Sakila ) และต้องการแสดงชื่อและเรตติ้งของภาพยนตร์ทั้งหมดแก่ผู้ใช้ แบบสอบถามต่อไปนี้สามารถให้ผลลัพธ์ที่เราต้องการ:
ทางด้านขวาคุณจะเห็น JSON ที่ซับซ้อนที่กำลังโหลดอยู่:
แน่นอนว่าเรากำลังสิ้นเปลืองหน่วยความจำ ฯลฯ ลองพิจารณาแบบสอบถามนี้เป็นทางเลือก:
โปรดทราบว่าตารางนักแสดงมีเพียง 4 คอลัมน์ ดังนั้นความแตกต่างด้านประสิทธิภาพระหว่างคำสั่ง 1 และ 2 จึงไม่ใหญ่มากนัก แต่ก็ยังมีนัยสำคัญ ฉันจะทราบด้วยว่าฉันใช้คำแนะนำของเครื่องมือเพิ่มประสิทธิภาพของ Oracle เพื่อให้เครื่องมือเพิ่มประสิทธิภาพเลือกดัชนีเฉพาะอย่างใดอย่างหนึ่งสำหรับแบบสอบถาม เจ้าหน้าที่คนที่ 3 เป็นผู้ชนะการแข่งขันของเราอย่างไม่มีปัญหา ประสิทธิภาพดีขึ้นมาก และเรากำลังพูดถึงแบบสอบถามที่ง่ายมาก อีกครั้ง เมื่อเราเขียน SELECT * เราจะสร้างงานที่ไม่จำเป็นแต่เป็นงานบังคับ สำหรับฐานข้อมูล ที่ไม่สามารถปรับให้เหมาะสมได้ เธอจะไม่เลือกดัชนีที่ครอบคลุมเนื่องจากมีค่าใช้จ่ายสูงกว่าดัชนี LAST_NAME ที่เธอเลือกเล็กน้อย และเหนือสิ่งอื่นใด เธอยังคงต้องเข้าถึงตารางเพื่อดึงคอลัมน์ LAST_UPDATE ที่ไม่มีประโยชน์ เป็นต้น แต่ยิ่งเราวิเคราะห์ SELECT * อย่างลึกซึ้งเท่าไหร่ก็ยิ่งแย่ลงเท่านั้น มาพูดถึง...
อะไรจริงจังใช่ไหม? ตอนนี้คุณเริ่มเข้าใจสิ่งที่ฉันกำลังพูดถึงแล้ว แต่ลองจินตนาการว่าเราได้เรียนรู้บางอย่างจากความผิดพลาดในอดีต และดำเนินการตามคำค้นหาที่เหมาะสมที่สุด:
แต่สิ่งนี้จะไม่เกิดขึ้น เครื่องมือเพิ่มประสิทธิภาพ (หรือแม้แต่ parser) สามารถมั่นใจได้ว่าไม่มีองค์ประกอบรายการที่เลือกในเพรดิเคต EXISTS (SELECT ..) จะเปลี่ยนผลลัพธ์ของการสืบค้น ดังนั้นจึงไม่จำเป็นต้องดำเนินการ แบบนี้!
งานที่ไม่จำเป็นแต่จำเป็น |
ไม่จำเป็น
ให้แอปพลิเคชันไคลเอนต์ของเราต้องการข้อมูลต่อไปนี้:SELECT title, rating
FROM film
อย่างไรก็ตาม แอปพลิเคชันของเรา (หรือ ORM) จะดำเนินการค้นหานี้แทน:
SELECT *
FROM film
เราได้อะไรตามมา? เดา. เราได้รับข้อมูลที่ไร้ประโยชน์มากมาย:
- จากดิสก์
- เพื่อแคช
- โดยสาย
- ในความทรงจำของลูกค้า
- และโยนทิ้งไปในที่สุด [โดยไม่จำเป็น]
บังคับ
และตอนนี้ - ส่วนที่แย่ที่สุด แม้ว่าตอนนี้เครื่องมือเพิ่มประสิทธิภาพสามารถทำงานได้หลายอย่าง แต่การกระทำเหล่านี้จำเป็นสำหรับฐานข้อมูล ฐานข้อมูลไม่มีทางรู้ได้ว่าแอปพลิเคชันไคลเอ็นต์ไม่ต้องการข้อมูลนี้ถึง 95% และนี่เป็นเพียงตัวอย่างที่ง่ายที่สุด ลองนึกภาพการเชื่อมต่อหลายตาราง... แล้วคุณล่ะว่าอย่างไร แต่ฐานข้อมูลนั้นเร็ว? ฉันขอให้ความกระจ่างแก่คุณเกี่ยวกับบางสิ่งที่คุณอาจไม่ได้นึกถึง แน่นอนว่าเวลาในการดำเนินการของคำขอแต่ละรายการไม่ส่งผลกระทบใดๆ เลย โอเค มันวิ่งช้าลง 1.5 เท่า แต่เราจะผ่านมันไปได้ ใช่ไหม? เพื่อความสะดวก? บางครั้งนี่เป็นเรื่องจริง แต่ถ้าคุณมัก จะเสียสละประสิทธิภาพเพื่อความสะดวก สิ่งเล็กๆ น้อยๆ เหล่านี้ก็จะเริ่มเพิ่มมากขึ้น เราจะไม่พูดถึงประสิทธิภาพอีกต่อไป (ความเร็วของการดำเนินการตามคำขอแต่ละรายการ) แต่เกี่ยวกับปริมาณงาน (เวลาตอบสนองของระบบ) จากนั้นปัญหาร้ายแรงจะเริ่มขึ้นซึ่งไม่ใช่เรื่องง่ายที่จะแก้ไข นั่นคือเมื่อคุณสูญเสียความสามารถในการขยายขนาด มาดูแผนการดำเนินการในกรณีนี้คือ Oracle DBMS:--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 166K|
| 1 | TABLE ACCESS FULL| FILM | 1000 | 166K|
--------------------------------------------------
เมื่อเทียบกับ:
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 20000 |
| 1 | TABLE ACCESS FULL| FILM | 1000 | 20000 |
--------------------------------------------------
การเรียกใช้แบบสอบถาม SELECT * แทนชื่อ SELECT การให้คะแนนจะใช้หน่วยความจำมากกว่า 8 เท่าในฐานข้อมูล ไม่มีอะไรที่ไม่คาดคิดใช่ไหม? เรารู้ว่าสิ่งนี้จะเกิดขึ้น แต่เรายังคงเห็นด้วยกับสิ่งนี้สำหรับคำขอหลายรายการของเราซึ่งเราไม่ต้องการข้อมูลทั้งหมดนี้ เราสร้าง งานที่ไม่จำเป็นแต่เป็นงานบังคับสำหรับฐานข้อมูลซึ่งจะสะสมและกองพะเนินเทินทึกอยู่เสมอ เราใช้หน่วยความจำมากกว่าที่จำเป็นถึง 8 เท่า (ตัวคูณจะเปลี่ยนแน่นอน) ในขณะเดียวกัน ในขั้นตอนอื่นๆ ทั้งหมด (ดิสก์ I/O การถ่ายโอนข้อมูลผ่านเครือข่าย การใช้หน่วยความจำโดยไคลเอนต์) ปัญหาจะเหมือนกันทุกประการ แต่ฉันจะข้ามมันไปและดูที่...
การใช้ดัชนี
ฐานข้อมูลส่วนใหญ่ในปัจจุบันชื่นชมแนวคิดของการครอบคลุมดัชนีแล้ว ดัชนีที่ครอบคลุมไม่ใช่ดัชนีชนิดพิเศษ แต่อาจกลายเป็น "ดัชนีพิเศษ" สำหรับข้อความค้นหาบางคำ ไม่ว่าจะเป็น "โดยบังเอิญ" หรือเพราะตั้งใจให้เป็นเช่นนั้น พิจารณาคำถามต่อไปนี้:SELECT *
FROM actor
WHERE last_name LIKE 'A%'
ไม่มีอะไรที่ไม่คาดคิดในแง่ของการนำไปปฏิบัติ นี่เป็นคำของ่ายๆ ดูช่วงตามดัชนี เข้าถึงตาราง - เพียงเท่านี้คุณก็เสร็จแล้ว:
-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 8 |
|* 2 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 8 |
-------------------------------------------------------------------
แผนดีไม่ใช่เหรอ? ถ้าเราต้องการสิ่งนี้จริงๆ ก็ไม่:
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
แผนการของเขาคือ:
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
|* 1 | INDEX RANGE SCAN| IDX_ACTOR_NAMES | 8 |
----------------------------------------------------
เราสามารถกำจัดการเข้าถึงตารางได้อย่างสมบูรณ์ เนื่องจากมีดัชนีที่ตอบสนองทุกความต้องการในการสืบค้นของเรา... ดัชนีที่ครอบคลุม มันสำคัญหรือ? แล้วยังไง! วิธีการนี้ช่วยให้คุณเร่งความเร็วการค้นหาบางรายการตามลำดับความสำคัญ (หรือลดความเร็วลงตามลำดับความสำคัญเมื่อดัชนีไม่ครอบคลุมอีกต่อไปหลังจากการเปลี่ยนแปลงบางอย่าง) ไม่สามารถใช้ดัชนีที่ครอบคลุมได้เสมอไป คุณต้องจ่ายค่าดัชนีและไม่ควรเพิ่มดัชนีมากเกินไป แต่ในกรณีนี้ทุกอย่างชัดเจน มาประเมินประสิทธิภาพกัน:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER := 100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Улучшенный request, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Оптимальный request: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/
เป็นผลให้เราได้รับ: ตัวดำเนินการ 1: +000000000 00:00:02.479000000 ตัวดำเนินการ 2: +000000000 00:00:02.261000000 ตัวดำเนินการ 3: +000000000 00:00:01.857000000 |
การแปลง SQL
เครื่องมือเพิ่มประสิทธิภาพทำงานได้ดีมากเพราะพวกเขาแปลงการสืบค้น SQL ( ฉันได้พูดคุยเกี่ยวกับวิธีการทำงานในการพูดคุยล่าสุดของฉันที่ Voxxed Days ในซูริก ) ตัวอย่างเช่น มีการแปลง "ข้อยกเว้น JOIN" ที่ทรงพลังอย่างยิ่ง พิจารณามุมมองตัวช่วยต่อไปนี้ที่เราต้องสร้างเพื่อหลีกเลี่ยงการเข้าร่วมตารางเหล่านี้ทั้งหมดด้วยตนเองทุกครั้ง:CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
มุมมองนี้จะรวม "...-ต่อ-หนึ่ง" ทั้งหมดระหว่างตารางลูกค้าลูกค้าและตารางต่างๆ สำหรับบางส่วนของที่อยู่ ขอบคุณการทำให้เป็นมาตรฐาน ลองนึกภาพว่าหลังจากทำงานกับมุมมองนี้เพียงเล็กน้อย เราก็คุ้นเคยกับมันและลืมเกี่ยวกับตารางที่ซ่อนอยู่ไป และตอนนี้เราดำเนินการค้นหาต่อไปนี้:
SELECT *
FROM v_customer
เป็นผลให้เราได้รับแผนการที่น่าประทับใจมาก:
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 47920 | 14 |
|* 1 | HASH JOIN | | 599 | 47920 | 14 |
| 2 | TABLE ACCESS FULL | COUNTRY | 109 | 1526 | 2 |
|* 3 | HASH JOIN | | 599 | 39534 | 11 |
| 4 | TABLE ACCESS FULL | CITY | 600 | 10800 | 3 |
|* 5 | HASH JOIN | | 599 | 28752 | 8 |
| 6 | TABLE ACCESS FULL| CUSTOMER | 599 | 11381 | 4 |
| 7 | TABLE ACCESS FULL| ADDRESS | 603 | 17487 | 3 |
----------------------------------------------------------------
แน่นอน ฐานข้อมูลกำลังดำเนินการรวมและสแกนตารางทั้งหมดเหล่านี้ เนื่องจากนั่นคือสิ่งที่เราบอกให้ทำ นั่นคือดึงข้อมูลทั้งหมดนี้ ลองจินตนาการอีกครั้งว่าสิ่งที่เราต้องการจริงๆ ก็คือ:
SELECT first_name, last_name
FROM v_customer
มาดูกันว่าเกิดอะไรขึ้น!
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 16173 | 4 |
| 1 | NESTED LOOPS | | 599 | 16173 | 4 |
| 2 | TABLE ACCESS FULL| CUSTOMER | 599 | 11381 | 4 |
|* 3 | INDEX UNIQUE SCAN| SYS_C007120 | 1 | 8 | 0 |
------------------------------------------------------------------
การเปลี่ยนแปลง ครั้งใหญ่ให้ดีขึ้นในแง่ของการดำเนินการ การรวมได้ถูกกำจัดออกไปแล้ว เนื่องจากตอนนี้เครื่องมือเพิ่มประสิทธิภาพสามารถเห็นว่ามันไม่มีประโยชน์และหากสามารถเห็นสิ่งนั้นได้ (และคุณไม่ได้บังคับงานนั้นโดยการเลือก *) ก็แสดงว่าไม่สามารถทำงานทั้งหมดนั้นได้ เหตุใดจึงเป็นเช่นนี้ในกรณีนี้? คีย์ต่างประเทศที่ Customers.ADDRESS_ID ไปยังคีย์หลัก ADDRESS.ADDRESS_ID รับประกันค่าหนึ่งค่าของค่าหลัง ซึ่งหมายความว่าการดำเนินการ JOIN จะเป็นการรวม "...-ต่อ-หนึ่ง" ที่ไม่เพิ่มหรือลดจำนวนแถว . และเนื่องจากเราไม่ได้เลือกหรือขอแถวใดๆ เลย จึงไม่มีประโยชน์ที่จะโหลดแถวเหล่านั้นเลย การลบ JOIN อาจไม่ส่งผลต่อผลลัพธ์ของการสืบค้นเลย ฐานข้อมูลทำเช่นนี้ตลอดเวลา คุณสามารถเรียกใช้แบบสอบถามต่อไปนี้ในเกือบทุกฐานข้อมูล:
-- Oracle
SELECT CASE WHEN EXISTS (
SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
ในกรณีนี้ คุณอาจคาดหวังว่าจะมีข้อยกเว้นทางคณิตศาสตร์เกิดขึ้น เช่นเดียวกับเมื่อดำเนินการแบบสอบถามต่อไปนี้:
SELECT 1 / 0 FROM dual
เกิดขึ้น: ORA-01476: ตัวหารเท่ากับศูนย์ |
ในขณะเดียวกัน...
ปัญหาที่น่ารำคาญที่สุดประการหนึ่งของ ORM คือการเขียนคำสั่ง SELECT * ได้ง่ายมาก ในความเป็นจริง ตัวอย่างเช่น ใน HQL / JPQL โดยทั่วไปจะใช้เป็นค่าเริ่มต้น เราสามารถละเว้นส่วนคำสั่ง SELECT ได้เลย เพราะเราจะดึงข้อมูลเอนทิตีทั้งหมดใช่ไหม ตัวอย่างเช่น:FROM v_customer
ตัวอย่างเช่นVlad Mihalcea ผู้เชี่ยวชาญและผู้สนับสนุนการพัฒนาด้วย Hibernateแนะนำให้ใช้คำค้นหา [ที่ผ่านการรับรอง] เกือบทุกครั้งเมื่อคุณแน่ใจว่าไม่ต้องการบันทึกการเปลี่ยนแปลงใดๆ หลังจากชำระเงิน ORM ช่วยแก้ปัญหาความคงอยู่ของกราฟวัตถุได้อย่างมาก หมายเหตุ: ความคงอยู่. งานในการแก้ไขกราฟวัตถุและการบันทึกการเปลี่ยนแปลงนั้นเชื่อมโยงกันอย่างแยกไม่ออก แต่ถ้าคุณไม่ทำอย่างนั้น เหตุใดจึงต้องสกัดสารสกัดออกมาด้วย? ทำไมไม่เขียนคำขอ [ปรับปรุง] ล่ะ? ขอให้ชัดเจน: จากจุดยืนด้านประสิทธิภาพ การเขียนแบบสอบถามที่ปรับแต่งให้เหมาะกับกรณีการใช้งานเฉพาะของคุณโดยเฉพาะนั้นดีกว่าตัวเลือกอื่นๆ อย่างเห็นได้ชัด คุณอาจไม่สนใจเนื่องจากชุดข้อมูลของคุณมีขนาดเล็กและไม่สำคัญ ยอดเยี่ยม. แต่เมื่อคุณต้องการความสามารถในการปรับขนาดในที่สุด การออกแบบแอปพลิเคชันของคุณใหม่เพื่อใช้การสืบค้นแทนการข้ามผ่านกราฟเอนทิตีตามความจำเป็นจะค่อนข้างท้าทาย และคุณจะมีบางอย่างที่ต้องทำโดยปราศจากมัน
การนับแถวเพื่อดูว่ามีอะไรอยู่หรือไม่
การสิ้นเปลืองทรัพยากรที่เลวร้ายที่สุดประการหนึ่งคือการเรียกใช้คำสั่ง COUNT(*) เพียงเพื่อดูว่ามีอะไรอยู่ในฐานข้อมูลหรือไม่ ตัวอย่างเช่น เราจำเป็นต้องค้นหาว่าผู้ใช้รายหนึ่งมีคำสั่งซื้อเลยหรือไม่ และเราดำเนินการตามคำขอ:SELECT count(*)
FROM orders
WHERE user_id = :user_id
ประถมศึกษา. หาก COUNT = 0 แสดงว่าไม่มีคำสั่งซื้อ มิฉะนั้นใช่ ประสิทธิภาพจะไม่แย่ขนาดนั้นเนื่องจากเราอาจมีดัชนีอยู่ในคอลัมน์ ORDERS.USER_ID แต่คุณคิดว่าประสิทธิภาพของแบบสอบถามข้างต้นจะถูกเปรียบเทียบกับตัวเลือกต่อไปนี้:
-- Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
)
ไม่จำเป็นต้องใช้นักวิทยาศาสตร์ด้านจรวดในการพิจารณาว่าภาคแสดงการดำรงอยู่ที่แท้จริงจะหยุดค้นหาสตริงเพิ่มเติมทันทีที่พบภาคแรก ดังนั้นหากผลลัพธ์ออกมาเป็น “ไม่มีคำสั่ง” ความเร็วก็จะเทียบเคียงได้ อย่างไรก็ตาม หากผลเป็น “ใช่ มีคำสั่งซื้อ” แล้วในกรณีที่ไม่จำเป็นต้องนับจำนวนที่แน่นอนก็จะได้รับคำตอบเร็วขึ้นมาก ท้ายที่สุดแล้วเราไม่สนใจจำนวนที่แน่นอน อย่างไรก็ตาม เราบอกให้ฐานข้อมูลคำนวณ ( งานที่ไม่จำเป็น ) และฐานข้อมูลไม่รู้ว่าเราเพิกเฉยต่อผลลัพธ์ทั้งหมดที่มากกว่า 1 ( งานที่จำเป็นต้องทำ ) แน่นอนว่ามันคงจะแย่กว่านั้นมากหากเราเรียก list.size() บนคอลเลกชั่นที่ได้รับการสนับสนุนจาก JPA เพื่อให้ได้ผลลัพธ์เดียวกัน ฉันได้เขียนเกี่ยวกับเรื่องนี้ในบล็อกของฉันก่อนหน้านี้ และได้ทำการทดสอบเปรียบเทียบของทั้งสองตัวเลือกใน...
GO TO FULL VERSION