Огляд плану виконання
Щоб мати можливість виконувати запити, SQL Server ядро СУБД повинен проаналізувати інструкцію, щоб визначити ефективний спосіб доступу до необхідних даних і обробити його. Цей аналіз обробляється компонентом, який називається оптимізатором запитів. Вхідні дані оптимізатора запитів включають сам запит, схему бази даних (визначення таблиць та індексів) та статистику бази даних. Оптимізатор запитів створює один чи кілька планів виконання запитів, іноді називаються планами виконання запитів або планами виконання. Оптимізатор запитів вибирає план запиту за допомогою набору евристики для балансування часу компіляції та оптимальності плану пошуку хорошого плану запиту.
Додаткові відомості про плани обробки запитів та виконання запитів див. у розділах , присвячених оптимізації інструкцій SELECT та кешування планів виконання та повторного використання посібника з архітектури обробки запитів.
Відомості про перегляд планів виконання в SQL Server Management Studio та Azure Data Studio див. у статті "Відображення та збереження планів виконання".
План виконання запиту – це визначення:
- Послідовності, де відбувається звернення до вихідним таблицям. Як правило, існує багато послідовностей, у яких сервер бази даних може звертатися до базових таблиць для побудови результуючого набору. Наприклад, якщо інструкція SELECT посилається на три таблиці, сервер бази даних спочатку може звернутися до TableA , використовувати дані з TableA для отримання відповідних рядків з TableB , а потім використовувати дані з TableB для вилучення даних з TableC . Інші послідовності, в яких сервер бази даних може звертатися до таблиць:
TableC, TableB, TableA або
TableB, TableA, TableC або
TableB, TableC, TableA або
TableC , , TableA TableB - Методи, які використовуються для отримання даних з кожної таблиці. Існують різні методи для звернення до даних у кожній таблиці. Якщо потрібно лише кілька рядків з певними ключовими значеннями, сервер бази даних може використовувати індекс. Якщо необхідні всі рядки в таблиці, сервер бази даних може пропустити індекси і виконати перегляд таблиці. Якщо всі рядки таблиці необхідні, але є індекс, ключові стовпці якого знаходяться в елементі ORDER BY, виконання перевірки індексу замість сканування таблиці може зберегти окремий результуючий набір. Якщо таблиця невелика, сканування таблиць може бути найефективнішим методом майже всього доступу до таблиці.
- Методи, що використовуються для обчислень, а також фільтрації, статистичної обробки та сортування даних з кожної таблиці. У міру доступу до даних таблиць можна різними способами виконувати обчислення над даними (наприклад, обчислення скалярних значень), а також статистичну обробку та сортування даних, як визначено в тексті запиту (наприклад, при використанні пропозиції GROUP BY або ORDER BY ) та їх фільтрацію (наприклад, при використанні пропозиції WHERE або HAVING).
Пов'язаний контент
- Спостереження та налаштування продуктивності
- Засоби моніторингу продуктивності та налаштування
- Посібник з архітектури обробки запитів
- Динамічна статистика запитів
- Монітор активності
- Моніторинг продуктивності з використанням сховища запитів
- sys.dm_exec_query_statistics_xml
- sys.dm_exec_query_profiles
- DBCC TRACEON - прапори трасування (Transact-SQL)
- Довідник по оператору логічного та фізичного шоуплану
- Інфраструктура профілювання запитів
- Відображення та збереження планів виконання
- Порівняння та аналіз планів виконання
- Керівництва планів
SQL-Ex blog
SQL Server зберігає найчастіше використовувані плани виконання в кеші, тому їх не потрібно перекомпілювати при кожному виконанні запиту. Як ми можемо скористатися цим, щоб знайти потенційні проблеми продуктивності в планах виконання? планів SQL Server.
Динамічне адміністративне подання (DMV) sys.dm_db_index_usage_stats створює звіт про використання індексів, і ми можемо використовувати це DMV для виявлення індексів, які можуть викликати проблеми DMV можуть надати підсумки деяких видів використання індексів, наприклад, кількість сканувань, пошуків і переходів по закладках , на підставі чого ми можемо виявити не тільки індекси, а й бази даних, які вимагають уваги.
- Scans: Сканування зазвичай найгірший варіант з точки зору продуктивності, оскільки в пошуках потрібної інформації проглядається весь індекс.
- Seeks: Пошук на відміну від сканування - найкращий варіант використання індексу, тому ми можемо порівняти відношення пошуку до сканування, щоб виявити ті індекси, які частіше скануються, ніж використовується пошук, що може бути потенційним джерелом проблем.
- Lookups: Пошук закладки відбувається тоді, коли операції, що виконується на некластеризованому індексі, потрібні додаткові стовпці для запиту, який зазвичай використовує кластеризований індекс. звичайно, це дасть кількість сканувань, а не пошуку закладок.Тому число пошуку закладок включається лише тоді, коли сканування стає занадто дорогим, що також погано для продуктивності.
DMV sys.dm_db_index_usage_stats включає інформацію про дії користувачів та системи, тобто. user_scans та system_scans, але ми можемо ігнорувати системну інформацію.
Першим кроком є виявлення бази даних, яка найбільшою мірою схильна до цих проблем. Нам потрібен запит для проблем кожного типу, сканування або пошук закладки (пошук - seek - не є проблемою, проте відношення пошуку до сканування є тією інформацією, яка допоможе нам ідентифікувати проблеми, викликані скануванням).
Ідентифікація проблем сканування
Ось запит для пошуку баз даних, які мають найбільші проблеми сканування:
select db_name(database_id),max(user_scans) bigger,
avg(user_scans) average
from sys.dm_db_index_usage_stats
group by db_name(database_id)
order by average desc
Підсумовування user_scans для різних індексів великого сенсу не має, тому ми обчислюємо максимум і середнє значення сканування користувача, щоб знайти бази даних, що вимагають нашої уваги.
Ось результат виконання запиту на моєму SQL Server:
Бачимо, що найбільше операцій сканування відбувається в базі даних adventureworks2012.
Тільки після вибору конкретної бази даних ми можемо продовжити і отримати імена індексів, які можуть викликати проблеми. Щоб це зробити, потрібно з'єднати цю інформацію з DMV з інформацією з sys.indexes та отримати ім'я індексу.
Наступний запит необхідно запустити на вибраній базі даних (звісно, ви зміните ім'я бази в запиті):
Use adventureworks2012 /* select object_name(c.object_id) as [table],
c.name як [index],user_scans,user_seeks,
case a.index_id
коли 1 then 'CLUSTERED'
else 'NONCLUSTERED'
end as type
from sys.dm_db_index_usage_stats a
inner join sys.indexes c
on c.object_id=a.object_id and c.index_id=a.index_id
and database_id=DB_ID('AdventureWorks2012') /* order by user_scans desc
Ці приклади були створені за допомогою бази даних Adventureworks2012 , яку можна завантажити з https://msftdbprodsamples.codeplex.com/releases/view/55330. При цьому таблиці 'bigproduct' and 'bigtransactionhistory' були створені Адамом Мачаником, і ви можете знайти їхній скрипт на http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx. Активність сканування генерувалася за допомогою інструмента SQL Query Stress, також розробленого Адамом, і ви можете взяти його звідси: http://dataeducation.com/sqlquerystress-the-source-code/.
Зауважу, що я також використовував index_id для ідентифікації індексу кластеризованого або некластеризованого. Я також включив інформацію про пошук, щоб ми могли порівняти відношення числа сканувань до пошуків та вирішити, який індекс потребує пильної уваги.
В результаті ми можемо з'ясувати, що таблиця bigproduct та її кластеризований індекс мають безліч сканувань, тому ми маємо зосередитись на них.
Обидва запити, які я використав досі, були створені для того, щоб вивчати проблеми сканування, однак ви можете використовувати ці ж запити і для проблем пошуку закладок: вам просто потрібно змінити поле user_scans на полі user_looups.
Пошук планів запитів, що викликають сканування
За допомогою розглянутих запитів ми знаходимо, які бази даних та індекси вимагають уваги, але як знайти ті плани запитів, за допомогою яких ми знайдемо самі запити, які викликають ці проблеми?
На другому кроці ми можемо використовувати SQL для виявлення проблемних планів запитів у кеші з наступним переходом до запитів, які потребують оптимізації.
Використовуючи DMV sys.dm_exec_query_stats, ми можемо вибрати всі запити у кеші та ідентифікувати проблемні плани.
Це DMV має дескриптор, який ми можемо використовувати для отримання плану запиту, та дескриптор, який ми можемо використовувати для отримання тексту запиту.Для їх отримання ми будемо використовувати динамічні адміністративні функції (DMF) sys.dm_exec_query_plan та sys.dm_exec_sql_text відповідно. Нам знадобиться CROSS APPLY.
select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
Поле query_plan представлений у вигляді XML, однак, якщо ви спостерігаєте результат у сітці (result to grid), вікно запиту в SSMS розпізнає схему і показує графічний план при натисканні на посилання. Це зручно вивчення окремих планів, але з систематичного пошуку за безліччю планів, відповідальних конктерному критерію. Якщо ми не зможемо фільтрувати результати на основі XML, нам доведеться переглядати їх по одному. Тому найкращим варіантом є використання Xquery.
Якщо клацнути по полю query_plan, ми побачимо графічний план запиту
Для того, щоб використовувати XQuery не наосліп для пошуку в XML, нам знадобиться інформація про використовувану XML-схему.
Схема цього документа XML опублікована на http://schemas.microsoft.com/sqlserver/2004/07/showplan
Існує безліч можливостей виявлення проблем виконання за допомогою цього методу, але я зможу показати лише кілька. А для виконання різних досліджень, вам потрібно буде вивчити схему.
Переглядаючи схему, ми виявимо, що є елемент RelOp з атрибутом LogicalOp, який ми можемо використовувати для пошуку всіх планів, які включають сканування індексу або сканування таблиці. Давайте розглянемо запит:
select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:RelOp[@LogicalOp="Index Scan"
or @LogicalOp="Clustered Index Scan"
or @LogicalOp="Table Scan"]')=1
XML план запиту є типізованим, тому нам потрібно визначити простір імен для того, щоб використовувати Xquery. Схема є дещо складною, і вам потрібно бути уважними при написанні власних запитів. Наприклад, можливі помилки при запиті елемента IndexScan, оскільки елемент IndexScan використовується у всіх операціях з індексами, включаючи пошук та пошук закладок.
Наступний крок – фільтрація результатів за вказаним індексом. Ми вже знайшли індекси з найбільшою кількістю сканувань, тепер ми можемо з'ясувати, які плани їх породжують. Наслідуючи схему, ім'я індексу є атрибутом елемента Object всередині елемента IndexScan, який знаходиться всередині елемента RelOp.
Тому запит буде таким:
select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:RelOp/qplan:IndexScan/qplan:Object[@Index="[pk_bigProduct]"]')=1
Якщо запит видає занадто багато планів, ми можемо використовувати інші поля sys.dm_exec_query_Stats, щоб знайти плани, які потребують оптимізації. Наприклад, ми можемо використовувати поле total_worker_time, щоб упорядкувати результати за часом CPU, наприклад:
select qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:RelOp[@LogicalOp="Index Scan"
or @LogicalOp="Clustered Index Scan"
or @LogicalOp="Table Scan"]/qplan:IndexScan/qplan:Object[@Index="[pk_bigProduct]"]')=1
order by total_worker_time desc
У моїй ситуації результатом є лише один план запиту. Ми маємо текст відповідного запиту та можемо подивитися графічний план у SSMS.SSMS показує навіть інформацію про "відсутні індекси", дозволяючи легко вирішити проблему, створивши їх.
Ми знайшли той самий запит, який породжує проблему та.
.Ми можемо побачити графічний план і припущення про відсутній індекс.
Ідентифікація проблем пошуку закладок
Іншим прикладом використання DMV є пошук планів з пошуком закладок в індексі.
select db_name(database_id),max(user_lookups) bigger,
avg(user_lookups) average
from sys.dm_db_index_usage_stats
group by db_name(database_id)
order by average desc
Спробуємо знайти індекс, який їх породжує для подальшого дослідження:
use adventureworks2012 /* select object_name(c.object_id) as [table],
c.name як [index],user_lookups,
case a.index_id
коли 1 then 'CLUSTERED'
else 'NONCLUSTERED'
end as type
from sys.dm_db_index_usage_stats a
inner join sys.indexes c
on c.object_id=a.object_id and c.index_id=a.index_id
and database_id=DB_ID('AdventureWorks2012') /* order by user_lookups desc
Adventureworks2012 також має проблеми із закладками
Нарешті, щоб знайти плани запитів із закладками, нам потрібно виконати фільтрацію за атрибутом lookup в елементі IndexScan. Новий запит:
select qp.query_plan,qt.text, plan_handle,query_plan_hash from
sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//AWMI:IndexScan[@Lookup]/AWMI:Object[@Index="[PK_TransactionHistory_TransactionID]"]')=1
У цьому прикладі ми виявимо, що якщо видалити два поля - quantity і actualcost з запиту, то lookup зникне. Звичайно, це неможливо, і в даному випадку нам довелося б шукати інше рішення, але це тема цієї статті.
Ми бачимо причину закладки
Налаштування дослідницьких запитів на практичні цілі
Чудово, що ми можемо знаходити плани запитів у кеші для пошуку проблем, проте непрактично використовувати такі запити у процесі щоденної оптимізації. Як зробити це більш практичним? Просто: ми створюємо функцію, і нам більше не потрібно турбуватися про складний синтаксис запитів. Отже, ми можемо створити запити для кожної з головних проблем щодо запитів, а потім створити одну функцію для кожного запиту.
Метод XQuery exists() приймає лише константи як параметри. Тому для доступу до змінної всередині виразу xquery ми можемо лише перетворивши ім'я індексу на змінну за допомогою виразу sql:variable.
Нижче наведено отриману таким чином функцію:
Create FUNCTION [dbo].[FindScans]
(
-- Додати параметри функції
@Index varchar(50)
)
RETURNS TABLE
AS
RETURN
(
select qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:RelOp[@LogicalOp="Index Scan"
or @LogicalOp="Clustered Index Scan"
or @LogicalOp="Table Scan"]/qplan:IndexScan/qplan:Object[fn:lower-case(@Index)=fn:lower-
case(sql:variable("@Index"))]')=1
)
GO
Зверніть увагу, що я увімкнув функцію fn:lower-case, інакше функція стала б чутлива до регістру, як і XML.
Тепер для пошуку планів у кеші, які використовують сканування конкретного індексу, достатньо написати простий запит:
select * from dbo.FindScans('[pk_bigProduct]')
Аналогічно для проблем із закладками:
CREATE FUNCTION FindLookups
(
-- Додати параметри функції
@Index varchar(50)
)
RETURNS TABLE
AS
RETURN
(
select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//AWMI:IndexScan[@Lookup]/AWMI:Object[fn:lower-case(@Index)=fn:lower-
case(sql:variable("@Index"))]')=1
)
GO
select * from dbo.FindLookups('[PK_TransactionHistory_TransactionID]')
І останній штрих: оскільки ми створюємо функції, що повторно виконуються, буде корисно виводити більше полів з sys.dm_exec_query_stats, щоб функції стали більш гнучкими.
Ось остаточний вид функцій:
Create FUNCTION [dbo].[FindScans]
(
-- Додати параметри функції
@Index varchar(50)
)
RETURNS TABLE
AS
RETURN
(
select qp.query_plan,qt.text,
statement_start_offset, statement_end_offset,
creation_time, last_execution_time,
execution_count, total_worker_time,
last_worker_time, min_worker_time,
max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads,
max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes,
max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads,
max_logical_reads, total_elapsed_time,
last_elapsed_time, min_elapsed_time,
max_elapsed_time, total_rows,
last_rows, min_rows,
max_rows from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:RelOp[@LogicalOp="Index Scan"
or @LogicalOp="Clustered Index Scan"
or @LogicalOp="Table Scan"]/qplan:IndexScan/qplan:Object[fn:lower-
case(@Index)=fn:lower-case(sql:variable("@Index"))]')=1
)
GO
Create FUNCTION FindLookups
(
-- Додати параметри функції
@Index varchar(50)
)
RETURNS TABLE
AS
RETURN
(
select qp.query_plan,qt.text,
statement_start_offset, statement_end_offset,
creation_time, last_execution_time,
execution_count, total_worker_time,
last_worker_time, min_worker_time,
max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads,
max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes,
max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads,
max_logical_reads, total_elapsed_time,
last_elapsed_time, min_elapsed_time,
max_elapsed_time, total_rows,
last_rows, min_rows,
max_rows from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//AWMI:IndexScan[@Lookup]/AWMI:Object[fn:lower-case(@Index)=fn:lower-
case(sql:variable("@Index"))]')=1
)
GO
Висновок
Ми можемо почати з побудови безлічі запитів, які застосовують XQuery для пошуку планів виконання, що зберігаються в кеші, щоб намітити шляхи оптимізації запитів і усунення потенційних проблем. Потім можна обернути ці запити до функцій для їх повторного використання. При створенні нових функцій дуже корисно вивчити схему плану виконання та випробувати їх на предмет продуктивності, перш ніж використовувати на робочому сервері. Хоча вони дуже зручні при відслідковуванні проблем продуктивності, самі запити не є швидкими.
SQL-Ex blog
Розуміння плану запиту PostgreSQL є критичним навичкою для розробників і адміністраторів баз даних.Це, ймовірно, перше, на що ми повинні звернути увагу на початку оптимізації запиту, а також перша річ для перевірки того, чи ваш оптимізований запит оптимізований так, як ви очікували.
Життєвий цикл запиту у базі даних PostgreSQL
- Навіщо нам взагалі потрібний план запиту?
- Що точно представлено у плані?
- PostgreSQL недостатньо розумний, щоб оптимізувати мої запити автоматично? Чому я мушу турбуватися про планувальника?
- Планувальник – це єдине, куди я мушу дивитися?
Діаграма життєвого циклу запиту у PostgreSQL
Перша фаза - це підключення до бази даних через ODBC/JDBC (API, створений Microsoft і Oracle, відповідно, для взаємодії з базами даних), або іншими засобами типу PSQL (інтерфейс терміналу для Postgres).
Друга фаза призначена для трансляції запиту у проміжний формат, відомий як дерево розбору. Обговорення внутрішнього пристрою дерева розбирання виходить за рамки цієї статті, але ви можете уявити це як скомпіловану форму запиту SQL.
Третя фаза це те, що ми називаємо системою перезапису системи/правил. Вона бере дерево аналізу, згенероване на другому етапі, і переписує його у вигляді, з яким може почати працювати планувальник/оптимізатор.
Четверта фаза є найважливішою, це серце бази даних. Без планувальника виконавчий механізм вирішуватиме наосліп, як виконати запит, які індекси використовувати, чи варто сканувати невеликі таблиці для виключення вже непотрібних рядків тощо. Ця стадія якраз обговорюватиметься у цій статті.
П'ята та остання фаза - це виконавець, який фактично виконує план та повертає результат.Майже всі системи баз даних наслідують процес, який більш-менш відповідає викладеному вище.
Встановлення даних
Давайте встановимо якусь фіктивну таблицю з тестовими даними для виконання експериментів.
Потім наповнимо цю таблицю даними. Я використовував скрипт Python, наведений нижче, для створення випадкових рядків.
Скрипт використовує бібліотеку Faker для створення тестових даних. Він генерує csv файл на кореневому рівні, який може бути імпортований як звичайний csv у PostgreSQL за допомогою наступної команди.
Оскільки id є serial, він автоматично заповнюватиметься самим PostgreSQL.
Тепер таблиця містить 1119284 записи.
Більшість наступних прикладів будуть ґрунтуватися на цій таблиці. Вона навмисно зроблена простою, щоб сфокусуватися на процесі, а не на складності таблиці/даних.
Переходимо до етапу планування
PostgreSQL та багато інших систем баз даних дозволяють користувачам бачити те, що фактично відбувається під капотом на етапі планування. Ми можемо це зробити, виконуючи те, що називається командою EXPLAIN (пояснити).
ПОЯСНЕННЯ запиту в PostgreSQL
При використанні EXPLAIN ви можете переглянути плани запитів до їх фактичного виконання базою даних. Ми перейдемо до розуміння кожного з них нижче, але спочатку поглянемо на іншу розширену версію EXPLAIN, яка називається EXPLAIN ANALYSE.
Пояснення та аналіз разом
Додавання аргументу ANALYZE до запитів виводить інформацію про час.
На відміну від EXPLAIN, EXPLAIN ANALYSE фактично виконує запит у базі даних. Цей варіант дуже корисний розуміння ситуації, коли планувальник грає свою партію неправильно; тобто. чи є велика різниця у плані, що генерується між EXPLAIN і EXPLAIN ANALYSE.
Що таке буфери та кеші в базах даних?
Давайте перейдемо до цікавішої метрики, яка називається BUFFERS. Вона пояснює, як багато даних приходить з кешу PostgreSQL і як багато зчитується з диска.
Увімкнення BUFFERS як аргумент показує, скільки сторінок потрапляє у запит.
Buffers : shared hit=5 означає, що п'ять сторінок було вилучено з кешу PostgreSQL. Давайте налаштуємо усунення в запиті для отримання відмінних рядків.
Зміна OFFSET призводить до різного числа запитів сторінок.
Buffers: shared hit=7 read=5 показує, що 5 сторінок бралося з диска. Частина read - це змінна, яка показує, як багато сторінок читалися з диска, а hit, як уже пояснювалося, беруться з кешу. Якщо ми виконаємо той самий запит знову (пам'ятаємо, що ANALYSE виконує запит), то тепер усі дані будуть у кеші.
Повторне виконання запиту показує, що кеш тепер надає всі результати.
PostgreSQL використовує механізм кешу, званий LRU (Least Recently Used - найрідше використовувані останнім часом), для зберігання в пам'яті даних, що часто використовуються. Розуміння того, як працює кеш, і його важливість це матеріал для іншої статті, але зараз ми повинні усвідомити, що PostgreSQL має надійний механізм кешування, і ми можемо побачити його роботу, використовуючи команду EXPLAIN (ANALYSE, BUFFERS).
Аргумент VERBOSE
Verbose - ще один аргумент команди, який надає додаткову інформацію.
Аргумент команди VERBOSE надасть ще більше інформації для складного запиту.
Зауважимо, що Output: id, name, sentence, company є додатковим. У плані складного запиту буде багато іншої інформації, яка буде надрукована.За замовчуванням опції COSTS і TIMING встановлені в TRUE, і немає необхідності вказувати їх явно, якщо ви не захочете відключити їх (FALSE).
FORMAT у поясненні PostgreSQL
PostgreSQL може представляти план у чудовому форматі, такому як JSON, тому ці плани можуть інтерпретуватися незалежним від мови способом.
Цей запит друкує план запиту у форматі JSON. Ви можете переглянути цей формат у Arctype, скопіювавши його висновок та вставивши його в іншу таблицю, як показано нижче у GIF.
Вставте виведення EXPLAIN JSON в таблицю, а також використовувати перегляд JSON для перевірки.
- Text (за замовчуванням)
- JSON (у прикладі вище)
- XML
- YAML
- EXPLAIN - тип плану, з якого зазвичай починаєте, і використовуваний, переважно, у промислових системах.
- EXPLAIN ANALYSE використовується для виконання запиту, поряд із отриманням плану запиту. Так ви отримуєте розбивку в плані часу планування та часу виконання, порівняння за вартістю та фактичний час виконаного запиту.
- EXPLAIN (ANALYSE, BUFFERS) використовується поверх аналізу для отримання числа рядків, що читаються з кеша та диска, і того, як поводиться кеш.
- EXPLAIN (ANALYSE, BUFFERS, VERBOSE) для отримання докладної та додаткової інформації щодо запиту.
- EXPLAIN(ANALYSE,BUFFERS,VERBOSE,FORMAT JSON) - який формат виведення ви хочете отримати; у разі JSON.
Елементи плану запиту
Будь-який план запиту незалежно від складності має деяку фундаментальну структуру. У цьому розділі ми сфокусуємо увагу на цих структурах, які допоможуть зрозуміти план запиту в абстрактній манері.
Вузли запиту
План запиту складається з вузлів:
Вузли – ключова частина виконання запиту
Вузол можна уявляти як етап виконання запиту базою даних. Вузли найчастіше є вкладеними, як показано вище; Seq Scan виконується раніше, після чого застосовується пропозиція Limit. Давайте додамо пропозицію Where, щоб зрозуміти подальше вкладення.
- Фільтрування рядків на ім'я Sandra Smith.
- Виконує послідовне сканування при застосуванні вищенаведеного фільтра.
- Застосування пропозиції limit нагорі.
Вартість у планувальнику запитів
Ціни є вирішальною частиною плану запиту до бази даних, і вони можуть бути неправильно зрозумілі через те, в якому вигляді вони представлені. Давайте подивимося на простий план з вартістю.
Вартість представлена усередині виводу EXPLAIN.
- Початкова вартість пропозиції LIMIT не дорівнює нулю. Це тому, що вартість виконання підсумовується знизу вгору, і те, що ви бачите, є вартість вузлів, розташованих нижче.
- Повна вартість є довільним заходом і більш актуальна для планувальника, ніж для користувача. У будь-якому випадку ви ніколи не отримаєте на практиці дані всієї таблиці одночасно.
- Послідовне сканування має свідомо погані оцінки, оскільки база даних немає варіантів оптимізувати його. Індекси можуть різко збільшити швидкість запитів із пропозицією WHERE.
- Width (ширина) важлива, оскільки, чим ширший рядок, тим більше даних доводиться читати з диска. Ось чому дуже важливо дотримуватись правил нормалізації таблиць бази даних.
Планування та виконання базою даних
Час планування та виконання – це метрики, які виводяться лише з опцією EXPLAIN ANALYSE.
Планування та виконання - це дві різні фази виконання запиту.
Планувальник (Planning Time – час планування) вирішує, як запит повинен виконуватися на основі різних параметрів, а виконавець (Execution Time) виконує запит. Ці наведені вище параметри є абстрактними і застосовуються до будь-якого типу запитів. Час виконання представлений у мілісекундах. У багатьох випадках час планування та час виконання можуть відрізнятися і, як у прикладі вище, планувальнику може знадобитися більше часу для планування запиту, а виконавцю потрібно менше часу, що зазвичай не так. Їм не обов'язково необхідно відповідати один одному, але якщо вони сильно розходяться, тоді настав час задуматися про те, чому це сталося.
У типових OLTP системах, до яких належить і PostgreSQL, будь-яка комбінація планування та виконання має бути менше 50мс, якщо це не аналітичний запит/величезні записи/відомі винятки. Нагадаємо, що OLTP – це оперативна обробка транзакцій. У типовому бізнесі виконується від сотень до мільйонів транзакцій. Завжди слід дуже уважно стежити за часом виконання, оскільки запити з невисокою вартістю можуть додати високі накладні витрати.
Куди рухатись далі
Ми пройшли шлях від розгляду життєвого циклу запиту до того, як планувальник ухвалює свої рішення. Я навмисно не стосувався таких питань, як типи вузлів (сканування, сортування, з'єднання), оскільки кожен із них потребує окремої статті. Метою цієї статті є дати загальне розуміння того, як працює планувальник, що впливає на його рішення та які інструменти надає PostgreSQL для кращого розуміння планувальника.
Повернімося до питань, які ми ставили вище.
Q: Навіщо нам взагалі потрібний план?
A: "Дурень із планом краще, ніж геній без плану!" - Стара приказка. План абсолютно необхідний, щоб вирішити, який шлях обрати, особливо коли рішення ухвалюється на основі статистики.
Q: Що точно знаходиться у плані?
A: План складається з вузлів, цін, часу планування та часу виконання. Вузли – це фундаментні блоки побудови запиту. Вартість – це основний атрибут вузла. Час планування та виконання показує фактичний час.
Q: Хіба PostgreSQL недостатньо розумний, щоб оптимізувати мої запити автоматично? Чому я мушу турбуватися про планувальника?
A: PostgreSQL дійсно розумний настільки, наскільки це можливо. Планувальник стає краще і краще з кожним релізом, але немає такого повністю автоматизованого/досконалого планувальника. Насправді це непрактично, т.к. оптимізація може бути гарною для одного запиту, але поганою для іншого. Планувальник повинен десь прокреслити лінію та забезпечити узгоджену поведінку та продуктивність. Велика відповідальність лежить на розробниках/адміністраторах баз даних, щоб писати оптимізовані запити та краще розуміти поведінку бази даних.
Q: Чи достатньо мені дивитися лише на планувальника?
A: Звісно, ні. Є багато інших речей, таких як предметна експертиза програми, проектування таблиць, архітектури бази даних і т.д., які дуже важливі. Але вам, як розробнику/адміністратору баз даних, розуміння та покращення цих абстрактних навичок надзвичайно важливе для кар'єрного зростання.
З цими фундаментальними знаннями ми можемо впевнено читати будь-який план і розуміти, що відбувається високому рівні. Оптимізація запитів дуже велика тема, яка вимагає знання безлічі речей, що відбуваються всередині бази даних. У наступних статтях ми побачимо, як плануються та виконуються запити та їх вузли, які фактори впливають на поведінку планувальника та як ми можемо оптимізувати їх.
