Основно съдържание
Програмиране
Курс: Програмиране > Раздел 3
Урок 3: Релационни заявки в SQL- Разделяне на данни в свързани таблици
- Съединяване на свързани таблици
- Предизвикателство: Боби има хоби
- Свързване на свързани таблици с външно ляво свързване (left outer join)
- Предизвикателство: Поръчки от клиенти
- Съединяване на таблици към самите себе си със себе-съединяване
- Предизвикателство: Филми в SQL
- Комбиниране на няколко съединения
- Предизвикателство: КнигаПриятели
- Проект: Известни хора
- По-ефективен SQL чрез планиране на заявки и оптимизация
© 2023 Khan AcademyУсловия за ползванеДекларация за поверителностПолитика за Бисквитки
По-ефективен SQL чрез планиране на заявки и оптимизация
След като научи много начини за селектиране на данни и започваш да правиш
SELECT
заявки между няколко таблици, моментът е подходящ да поговорим за ефективността на SQL заявките – колко бързо се изпълняват и могат ли да се изпълняват по-бързо?SQL е декларативен език – всяка заявка декларира какво искаме да направи SQL енджинът, но не казва как да го направи. Оказва се, че "планът" как да го направи е това, което определя ефективността на заявките и затова е толкова важно.
Защо SQL заявките се нуждаят от план?
Например да кажем, че имаме тази проста заявка:
SELECT * FROM books WHERE author = "Дж. К. Роулинг";
Има 2 различни начина да намерим резултатите с SQL в тази заявка:
- Направи "пълно сканиране на таблицата": прегледай всеки ред в таблицата и върни редовете, които отговарят.
- Създай "индекс": Направи копие на таблицата, сортирана по автор, а след това изпълни двоично търсене, за да намериш реда, в който е авторът "Дж. К. Роулинг", намери съответните ID-та, а след това изпълни двоично търсене върху оригиналната таблица, което връща съответните редове, които отговарят на това ID.
Кой начин е по-бърз? Зависи от данните и от това колко често ще се изпълнява заявката. Ако таблицата има само 10 реда, тогава пълното сканиране на таблицата изисква преглеждането само на 10 реда и първият подход ще работи съвсем добре.
Ако таблицата има 10 милиона реда, тогава пълното сканиране ще изисква преглеждането на 10 милиона реда. Ще бъде по-бързо да се изпълни двоично търсене върху сортирана таблица – така ще ни трябват само 23 преглеждания, за да намерим стойност в 10 милиона реда. Създаването на сортирана таблица, обаче, също отнема време (около 230 милиона операции в зависимост от еджина). Ако изпълняваме тази заявка много пъти (повече от 23 пъти) или вече имаме таблицата сортирана, вторият подход е по-подходящ.
Как SQL енджинът решава кой план да избере? Това е важна стъпка, за която все още не сме говорили, защото се бяхме фокусирали върху синтаксиса на нашите заявки, а не върху тяхната имплементация. Колкото повече напредваш с използването на SQL върху големи бази данни, толкова по-важна става стъпката на планиране.
Жизненият цикъл на SQL заявката
Можем да приемем, че SQL енджинът преминава през всяка от тези стъпки за всяка заявка, която му подадем:
- Анализаторът на заявки отговаря за това заявките да са правилни синтактично (например запетаите да са си на мястото) и семантично (например таблиците да съществуват) и връща грешка, ако това не е така. Ако всичко е правилно, тогава превръща заявката в алгебричен израз и го подава на следващата стъпка.
- Планиращият заявките оптимизатор върши тежката работа. Първо изпълнява ясни оптимизации (подобрения, които винаги водят до по-добро изпълнение, като опростяването на 5*10 до 50). След това разглежда различни "планове за заявки", които могат да имат различни оптимизации, оценява цената (процесор и време) на всеки план според броя редове в съответните таблици, а след това избира оптимален план и го подава на следващата стъпка.
- Изпълнителят на заявки приема плана и го превръща в операции към базата данни и ни връща резултат, ако има такъв.
Къде е мястото на хората?
Планирането и оптимизацията на заявки се изпълнява за всяка заявка и ти можеш цял живот да отправяш SQL заявки, без да го разбереш. Когато веднъж започнеш да работиш с големи бази данни, обаче, ще започнеш да обръщаш повече внимание на своите заявки и може да се зачудиш дали има начин да подобриш изпълнението им.
Често, особено при сложни заявки, наистина има начини, с които да помогнеш с оптимизацията на заявката и това се нарича "настройване на заявката.
Първата стъпка е да идентифицираш кои заявки искаш да настроиш, което можеш да разбереш, като видиш кои от извикванията към базата данни отнемат най-много време или използват най-много ресурси, например като използваш програма за профилиране на SQL. Понякога може да откриеш заявка с лошо изпълнение едва когато ѝ отнеме толкова време, че цялата ти база данни падне. Но се надявам, че ще я намериш по-рано.
Следващата стъпка е да разбереш как конкретният SQL енджин изпълнява една заявка и как всички SQL системи имат начин, по който да задават въпроси на двигателя. В SQLite можеш да поставиш
EXPLAIN QUERY PLAN
пред всяка SQL заявка, за да видиш какво се случва зад кулисите. Ако използваш това, приготви се за дълбоко гмуркане в документацията за EXPLAIN QUERY PLAN
, защото "обяснението" е доста подробно и специфично за имплементацията. Ако използваш друг SQL енджин, можеш да потърсиш "как да намеря плана за изпълнение на X".И сега идва трудната част: ръчна оптимизация, за да подобриш плана за изпълнение. Тази част често зависи от конкретния SQL енджин, който използваш, и особеностите на данните.
Например, спомняш ли си заявката, която обсъдихме в началото? Ако знаем предварително, че ще имаме стотици заявки, които ограничават с
WHERE
колоната на авторите, можем да създадем индекс, като използваме командата CREATE INDEX
. След това SQL енджинът ще може да използва този индекс, за да намира ефективно съвпадащите редове. Можеш да прочетеш това ръководство за планиране на заявки в SQLite, за да разбереш кога са полезни индексите.Създаването на индекси често прави повтарящите се заявки по-ефективни. Но има и много други подходи. За SQLite можеш да разбереш повече в техния преглед на планиране на заявки и да обърнеш специално внимание върху секциите с "ръчна" обработка.
Тук не можем да покрием всички сложни детайли на оптимизацията на заявки и настройването на заявки, затова препоръчвам да научиш повече за тях, когато ти се наложи.
(Ето някои примери за задълбочаване в планирането на SQL заявки, които според мен са интересни: SQL Server Query Optimizer, Oracle SQL Tuning, MSSQL Execution Plan Basics)
Искаш ли да се присъединиш към разговора?
Все още няма публикации.