По-ефективен 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)

Искаш ли да се присъединиш към разговора?

Вписване в профила

Сортирай по:

Все още няма публикации.

Разбираш ли английски? Натисни тук, за да видиш още дискусии в английския сайт на Кан Академия.