Комментарии 38
Совершенно пустая и практически бесполезная статья. К тому же написанная ну просто безобразно и нечитаемо, особенно коды и планы. Как по мне, оригинал явно не заслуживает того, чтобы тратить время на перевод.
Да и перевод сделан совершенно безобразно. Несогласованные лица, падежи и прочее - чуть ли не через абзац... ну уж если автор озаботился переводом - надо же было после машинного перевода хотя бы прочитать, что получилось!
плагиат, статья переведена другим человеком в рамках разработки проектов НАНОФИТ и Тимбилдер
Автор статьи не в курсе, что SQL движок может варьировать план запроса в зависимости от объема данных? Один и тот же запрос на разных средах (с разными данными) может иметь радикально разные планы.
Поэтому все это имеет смысл делать на копии промсреды, но не на тестовых средах.
Если говорить про оптимизацию запросов, то, к примеру, на наше платформе (DB2 for i) есть такая штука как "интерактивный SQL" - средство где можно не только погонять запросы в интеактиве и посмотреть результаты (прежде чем вставлять его в код), но и режим "демонстрации" где показывается как запрос будет выполняться
Там еще и рекомендации оптимизатора можно посмотреть.
Далее, из личного опыта. Сильно тормозят те запросы, где используется сортировка (order by, особенно по неиндексированным полям) или агрегирование (с использованием group by). И тут чаще всего выгоднее сделать запрос "линейным" (пусть и избыточным), а сортировку и/или агрегирование выполнять уже в коде в процессе потоковой постобработки результата.
Есть еще ряд "финтов ушами", но, боюсь, это уже специфика нашей платформы - статические/динамические запросы (различаются тем, в какой момент строится план - один раз во время компиляции или каждый раз в рантайме), блочное чтение (fetch не по одной записи, а блоками по 1 000...10 000 записей в массив и т.п.
ключевым моментом в оптимизации медленных запросов является поиск последовательных сканов, которые выполняет БД, и добавление соответствующих индексов
...и ни слова про селективность. Дальше можно не читать.
К сожалению, не существует универсального способа оценить БД, чтобы определить, какие индексы надо добавить для повышения производительности.
Не все так плохо.
В SQL Server есть Dynamic Management Views (DMVs), можно получить рекомендации по индексам.
Execution Plan позволяет проанализировать и получить рекомендации по индексам.
В профайлере можно отловить запросы, занимающие время.
Можно добавить Extended Events для автоматического логирования тупящих запросов.
Вобщем, все движки сваливать в кучу и подводить черту - рисковое занятие.
Не все движки одинаковые.
Любую оптимизацию следует начинать с обновления статистик. Потому что план запроса зависит, в первую очередь, от них, а не от объемов данных и их селективности, о которых планировщик узнает именно из статистик.
Особенно это относится к временным и промежуточным таблицам.
При оптимизации запросов важно учитывать объемы данных, ведь от размера таблиц оптимизатор может выбрать разные сценарии, о чем было сказано в статье.
На больших таблицах поведение может измениться от разных значений фильтров. Из-за разной статистики (распределения значений).
Для OLAP нагрузок уже гораздо проще перейти на колоночные базы и индексы, работают быстро и без заморочек.
Для OLTP нагрузок надо измерять наносеки, нормализацию базы проводить. И еще у индексов есть стоимость обслуживания, которая добавляет свои наносеки.
В своей практике я пришел к простому выводу, все сложные запросы должны выбирать как можно меньше данных на каждом этапе. Иной раз лишний JOIN может увеличить время выполнения запроса в сотни раз (таблицы с миллионами записей).
Также не упоминается о временных таблицах. Если запрос «никак не хочет» выполняться быстро, можно его раскидать во временные таблицы, речь про OLAP нагрузку разумеется.
Вся эта тема с оптимизацией SQL дико долгая, чтоб я мог еще что-то добавить)
Лично я использую только хранимые процедуры. Тут и план запроса не выполняется каждый раз, а строится единожды при компиляции, и обработка данных в хранимых процедурах позволяет не таскать гигабайты трафика по сети клиенту туда сюда, и бизнес логика опять же в бд позволяет сильно упростить клиента, оставив ему только интерфейсные зависимости. И конечно, надо знать как оптимально обрабатывать данные на SQL без использования всяких тормозных fetch. Хоть у меня не web разработка, но через vpn и интернет мои приложения работают также быстро как и в локальной сети. Потому что трафик минимальный между клиентом и сервером. Потому что хранимые процедуры оптимизированы, созданы все необходимые индексы, периодически проверяется дефрагментация индексов и они перестраиваются и много ещё чего оптимизировано при разработке встроенных процедур. Конечно, у меня нишевая область использования моих систем, и я стараюсь избегать web подходов к разработке в нишевых областях, в которых она не нужна, а только добавляет ненужной сложности и замедляет работу приложений.
Примеры будут приведены для Postgres, MySQL и SQLite.
хранимые процедуры. Тут и план запроса не выполняется каждый раз, а строится единожды при компиляции
И о какой из этих БД речь?
Я работаю исключительно с sql server. Было приложение где пришлось реализовать одну и ту же функциональность в SQL Server и sqlite. Для совместимости в SQL server использовал те же запросы sqlite во встроенных процедурах sql server. Текст запросов переделывать не пришлось вообще. За исключением получения даты и количества записей. В общем разницы не нашел в быстродействии. Оптимизировал в SQL server.
Тогда какое отношение Ваш комментарий имеет к этой статье? Её хабы: MySQL PostgreSQL SQLite
А то что любая оптимизация будет хорошо работать. Если я оптимизировал в SQL server, то в Sqlite она тоже будет оптимальна. Даже запрос менять не надо. Методы оптимизации во всех базах одинаковые практически, только это и хотел сказать. Причина медленного запроса должна быть устранена, оптимизация медленного запроса уже признак неправильной архитектуры или плохого знания sql вкупе с игнорированием общеизвестных рекомендаций по построению запросов.
Запрос, оптимально работающий в MS SQL, легко может уйти в нирвану даже в PostgreSQL, не говоря уже об SQLite. И наоборот - тоже дело обычное.
И Вы так и не ответили на вопрос: "какое отношение Ваш комментарий имеет к этой статье? Её хабы: MySQL PostgreSQL SQLite"
Особенно с учетом того, что ни один из них не компилирует SQL запросы до их первого исполнения при запуске в хранимой процедуре. В последнем так вообще хранимых процедур нет и говорить об их компиляции бессмысленно.
И это, на практике, просто замечательно. На MS SQL очень часто приходится требовать это насильно через WITH RECOMPILE. Потому что между запусками хранимой процедуры могут меняться как статистики, так и селективность выборки в зависимости от параметров процедуры. И скомпилированный изначально план запроса может оказаться совершенно не эффективен при последующих вызовах.
Мне ни разу не понадобилось включать компиляцию процедур. Просто архитектура приложений делается так чтобы использовалась статистика + рекомендации по составлению запросов+оптимизация по индексам+ соединение малых таблиц к большим+ и т д. Это справедливо для любых бд.
Мне ни разу не понадобилось включать компиляцию процедур.
Я даже не знаю, как это можно сделать на MS SQL. Расскажите.
Я писал наоборот, о вреде компиляции хранимых процедур MS SQL по умолчанию, которую приходится запрещать указанием WITH RECOMPILE для всей процедуры или конкретных запросов в ней.
архитектура приложений делается
При чем тут архитектура, если, в зависимости от параметров процедуры, оптимальные планы запросов в ней могут быть совершенно разными?
соединение малых таблиц к большим
Не верно. Должно быть соединение выборок в зависимости от их селективности. А это совсем не обязательно связано с размерами таблиц.
Демонстрирую. Создаем две таблицы и хранимую процедуру:
DROP TABLE IF EXISTS tmp_tmp;
CREATE TABLE tmp_tmp (
ID int PRIMARY KEY,
SessionId int NOT NULL,
Val int NOT NULL
);
INSERT INTO tmp_tmp(ID, SessionId, Val)
SELECT N.number+T.number*1000 AS ID,
CASE WHEN T.number<500 THEN N.number
ELSE N.number+T.number*1000 END AS SessionId,
N.number AS Val
FROM master.dbo.spt_values T
JOIN master.dbo.spt_values N ON N.type='P'
AND N.number BETWEEN 1 AND 1000
WHERE T.type='P'
AND T.number BETWEEN 0 AND 999;
CREATE INDEX tmp_tmp_SessionId_Idx ON tmp_tmp (SessionId, Val);
CREATE INDEX tmp_tmp_Val_Idx ON tmp_tmp (Val, SessionId);
UPDATE STATISTICS tmp_tmp WITH FULLSCAN;
DROP TABLE IF EXISTS tmp_tmp2;
CREATE TABLE tmp_tmp2 (
ID int PRIMARY KEY,
Val int NOT NULL,
Gr int NOT NULL
);
INSERT INTO tmp_tmp2(ID, Val, Gr)
SELECT N.number+T.number*1000 AS ID,
CASE WHEN T.number<500 THEN T.number
ELSE N.number END AS Val,
CASE WHEN T.number<500 THEN N.number
ELSE N.number+T.number*1000 END AS Gr
FROM master.dbo.spt_values T
JOIN master.dbo.spt_values N ON N.type='P'
AND N.number BETWEEN 1 AND 1000
WHERE T.type='P'
AND T.number BETWEEN 0 AND 999;
CREATE INDEX tmp_tmp2_Val_Idx ON tmp_tmp2 (Val, Gr);
CREATE INDEX tmp_tmp2_Gr_Idx ON tmp_tmp2 (Gr, Val);
UPDATE STATISTICS tmp_tmp2 WITH FULLSCAN;
CREATE OR ALTER PROC tmp_proc (@gr int, @sessionid int)
AS
SELECT *
FROM tmp_tmp H
JOIN tmp_tmp2 D ON D.Val=H.Val
WHERE D.Gr=@gr AND H.SessionId=@sessionid
Запускаем с разными параметрами. Так:
EXEC tmp_proc 100, 600100;
Table 'tmp_tmp2'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_tmp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.И так:
EXEC tmp_proc 600100, 100;
Table 'tmp_tmp2'. Scan count 1000, logical reads 3000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_tmp'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 2 ms, elapsed time = 2 ms.
Теперь меняем процедуру, добавив принудительную перекомпиляцию запроса:
CREATE OR ALTER PROC tmp_proc (@gr int, @sessionid int)
AS
SELECT *
FROM tmp_tmp H
JOIN tmp_tmp2 D ON D.Val=H.Val
WHERE D.Gr=@gr AND H.SessionId=@sessionid
OPTION (RECOMPILE)Повторяем те же самые вызовы:
EXEC tmp_proc 100, 600100;
Table 'tmp_tmp2'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_tmp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
EXEC tmp_proc 600100, 100;
Table 'tmp_tmp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_tmp2'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Теперь планировщик смог понять, что в этих запросах выгодней выбирать данные из таблиц в разном порядке и план запроса стал оптимален в обеих случаях.
И если речь не о примитивных запросах в пет-проектах, а о реальных приложениях, то при малейших сомнениях ставится RECOMPILE на всю процедуру или на запросы, в которых прямо или косвенно участвуют параметры процедуры.
Спрасибо, не знал. Все и так работает неплохо. Уже 4 года . Лог в БД накапливается за год до 3 млн. записей, использую его в мониторинге, где встроенная процедура запускается 1 раз в секунду. В процедуре выборка из лога идет подзапросом. Но систему это не грузит совершенно. добавлены необходимые индексы. В моей публикации есть картинка программы мониторинга, если интересно.
Даже на выборке из одной таблицы, но по множеству полей, селективность может заметно различаться, в зависимости от параметров. Просто на столь крошечных объемах Вы это не замечаете. Были бы миллиарды записей, как у меня, то сразу бы это ощутили.
встроенная процедура запускается 1 раз в секунду
В моей публикации есть картинка программы мониторинга, если интересно.
Не интересно. Это задача ElasticSearch, ClickHouse или InfluxDB, получающих логи, например, через Kafka. Но уж никак не MS SQL.
на моих небольших объемах БД использовать это не нужно. Сервер справляется замечательно сам, с моей помощью в оптимизации индексов. А если все хорошо, то не зачем использовать что-то еще. Чем проще тем лучше.
Но статью и комментарии читают далеко не только те, у кого небольшие объемы БД. А Вы не указали, даже неявно, что Ваши рекомендации подходят только для небольших объемов, где время на рекомпиляцию планов запросов сравнимо с временем выполнения самих запросов.
С индексами тоже не все так просто. На больших таблицах с высокой плотностью изменений данных любой лишний индекс дает увеличение нагрузки на всю систему в целом. Поэтому всегда приходится балансировать.
RECOMPILE тоже занимает определенное время и дает нагрузку на сервер. Если у вас таких RECOMPILE будет несколько десятков тысяч (работающих параллельно) и они будут регулярно вызываться сотни миллионов раз в сутки каждый - вы это неизбежно почувствуете. Палка о двух концах.
Вот такое вот как-то получил от сопровождения:
Из PEX статистики работы ****** видно, что 33% времени и 36% ресурсов CPU тратится на выполнение QSQRPARS в программе *******, т.е. парсинг статических выражений при подготовке SQL запроса,
Поскольку ****** один из наиболее активно используемых сервис модулей, необоснованное повышенное ресурсопотребление является малодопустимым. Просьба инициировать доработку *******.
Здесь речь идет как раз о "динамическом" SQL - когда план запроса строится при каждом его вызове.
С рекомпиляцией процедура работает на 2 порядка медленней, сейчас попробовал. Время на компиляцию съедает весь профит.
RECOMPILE тоже занимает определенное время и дает нагрузку на сервер.
Время на RECOMPILE, обычно, на порядки меньше, чем время выполнения запросов.
Если у вас таких RECOMPILE будет несколько десятков тысяч (работающих параллельно)
Если среди этих десятков тысяч найдется хотя бы сотня запросов, выбравших без RECOMPILE на три порядка (как в моем примере - 3000 logical reads вместо 3) менее оптимальный план запросов, то уже окупится.
Здесь речь идет как раз о "динамическом" SQL - когда план запроса строится при каждом его вызове.
А при чем тут динамический SQL, который требует не только компиляции плана запроса, но еще и парсинга с запросами метаданных? RECOMPILE вызывает лишь sp_cache_remove, query_post_compilation_showplan и sp_cache_insert.
И я не призывал лепить RECOMPILE на все процедуры. Делать это следует ровно как я писал: "при малейших сомнениях ставится RECOMPILE на всю процедуру или на запросы, в которых прямо или косвенно участвуют параметры процедуры"
У меня не такой большой объем данных на этой задаче, но он компенсировался сложностью разработки идеи программируемых тестов и расчета и показа метрологических параметров в реальном времени в мониторинге. Да и динамический SQL пришлось тоже применять для расчета по формулам в зависимости от многих параметров.
бизнес логика опять же в бд позволяет сильно упростить клиента, оставив ему только интерфейсные зависимости
Сложность переносится в БД. Но в коде всех популярных языков бороться со сложностью гораздо проще, чем в T-SQL.
Смотря о каких сложностях идёт речь. И каких задачах. Если говорить об обработке наборов данных, то на SQL и быстрее и проще, не надо гонять данные на клиент. Конечно что нибудь специфические., типа расчёт коэффициентов полиномов лучще делать на клиенте, но данные готовить лучше в SQL, например, усредннение наборов данных, формирование и транспонирование матриц для расчёта и передача на клиент. Но здесь проблема в том что надо хорошо знать sql и что возможно и как на нем обрабатывать и как делать правильно архитектуру приложения, набор таблиц и т д. Чтобы потом не было мучительно больно. В своей статье на хабре я описываю создание такой системы с программируемым логикой в бд. И жизненный цикл этого приложения показывает, что решение было правильным. Сложная система обладает феноменальной устойчивость ю и ее просто сопровождать без изменения клиента. Требовалось к примеру изменять некоторые фичи в форирлваниии протоколов и обработке данных, и это делалось в SQL быстро и не затрагивало клиента. Разработка на SQL сложна, но окупается в дальнейшем очень хорошо одно из приложений, которое было переписано . Было написано на С# и в качестве БД использовались xml файлы. Мы переписали его на labview, c логикой в бд для двух вариантов - под sql server и sqlite, сделали встроенный редактор. Раньше приходилось пользователям редактировать xml, чтобы получить новый функционал, теперь это обычный интерфейс м кучей удобств копирования и редактирования строк. Часть функционала приложения мы вынесли в бд и сделами простоц табличной процессор, вычисления редактируются пользователем.
Если говорить об обработке наборов данных, то на SQL и быстрее и проще
Далеко не всегда. Часто быстрее обработать данные в Spark, Redis, ClickHouse и т.п.
не надо гонять данные на клиент
Не все, что запрашивается из БД пересылается на клиента. Многое остается только на сервере приложений и там кешируется.
И не следует забывать, что горизонтально масштабировать сервера приложений намного проще, чем СУБД. Поэтому, нередко, лучше погонять больше данных по сети, но не нагружать РСУБД.
Чем сложнее логика и тяжелее вычисления и чем меньше объем данных, необходимый для этих вычислений, тем меньше смысла производить эти вычисления на РСУБД.
Смотря о каких сложностях идёт речь.
О сложностях бизнес-логики.
И каких задачах.
Стандартное бизнес-приложение. Например, система управления чем-нибудь (от содержимого сайта до документооборота предприятия).
Но здесь проблема в том что надо хорошо знать sql и что возможно и как на нем обрабатывать и как делать правильно архитектуру приложения, набор таблиц и т д.
И как на T-SQL сделать правильно архитектуру?
Например, как организовать юнит-тестирование? Как использовать одинаковое условие для разных запросов? Как найти все использования некого свойства некой сущности (поля в таблице)?
В общем это несложно, протестировать запрос или процедуру. Можно написать sql скрипты тестирования. В общем коде всех процедур найти использование поля можно просто создать общий скрипт бд и поиском в файле найти то что нужно. Для хранения истории изменений процедур и вообще всего dml я использую триггер в бд, который обрабатывает событие в бд и пишет в таблицу истории изменённый скрипт. Если нужно в процессе разработки добавлять сущности в таблицу, чтобы не править много процедур,, связанных с ней, как вариант, я делаю связанную таблицу по ключевому полю. Для того чтобы процедуры не ломались в таком варианте, параметры процедур указываю всегда null, insert использую с values, а не из select. Тестирование северного кода делаю в процессе разработки и в общем достаточно редко его приходится потом править и правки как правило минимальные. Опыт - сын ошибок трудных. Заранее делаю несколько вариантов архитектуры бд приложения и выбираю такую, какая лучше ложится на мой метод. И в общем это работает, потому что основное время уходит на разработку интерфейса клиентской части и всех его зависимостей. Последнее приложение было достаточно сложное, это в общем такой процесс перевода бумажного паспорта прибора в электронный вид и прохождение его по сложным стадиям обработки а разных отделах компании со статусами, правами доступа, комментариями, историями изменений, формированием различных документов в excell и т п. Версия MVP была сделана за полгода и потом 1 месяц тестирования в рабочей обстановке с паралельным ведением бумажного и электронного варианта. Затем устранение мелких недочётов, в основном клиентской части, северная почти не потребовала никаких изменений, так как была спооектирована правильно. Если бы бизнес логика была на клиенте, было бы гораздо сложнее так быстро все разработать и запустить. Это работа одного человека, без команды. Приложение десктопное.
Можно написать sql скрипты тестирования.
Перед запуском тестов нужно поднимать специальную версию БД с заданными данными? Я предпочитаю юнит-тесты, которые не используют соединение с БД.
В общем коде всех процедур найти использование поля можно просто создать общий скрипт бд и поиском в файле найти то что нужно.
Текстовый поиск выдаёт много лишних результатов из-за того, что поля в разных таблицах называются одинаково. Даже когда есть специальный БД-проект в Visual Studio, который содержит скрипты для всех таблиц, поиск текста сильно уступает списку ссылок на метод/свойство. Но, хотя бы можно в в 2 клика открыть код (скрипт) нужной процедуры/таблицы.
Тестирование северного кода делаю в процессе разработки и в общем достаточно редко его приходится потом править и правки как правило минимальные.
В больших и долгих (десятилетия) проектах бизнес-требования регулярно меняются. Одну фичу переделать, другую добавить...
И в общем это работает,
Мне приходилось работать с продуктами, где логика в БД, и с продуктами, где логика в коде. Второй вариант намного легче разрабатывать и поддерживать. ИМХО.
Мой опыт показывает что с логикой на клиенте, особенно сложной, разработка длится гораздо дольше, и требуется серьёзная отладка, команда. А у нас два человека команда, и мы делаем сложные приложения очень быстро. Я думаю что бизнес логика на клиенте для web боле удобна, просто потому что мало кто из web разработчиков хорошо знает sql и возможности обработки данных на нем. И дело даже наверно не в этом, а в способе мышления при разработке архитектуры. Идут проторенной дороге, читая умные книжки про антипаттерн и пр. У нас есть одно приложение, довольно простое, которое делали люди, которые из компании давно ушли. Там применили postgress, линукс, рнр, питон, и это для обычного приложения типа crud. Оно периодически тормозит, глючит, и в общем никто ничего не может сделать, ибо разработчиков нет, поддержка от них минимальная. Они просто тренировались, изучая всю эту технологию и питон, которых там вообще не нужен. Моя система, разработанная 4 года назад работает и почти не требует никакой поддержки. Сейчас встала задача масштабирования и северная часть вообще не требует доработки, только клиентская по мнтерфейсу и работе с новым оборудованием. Так как на бизнес логику клиент не завязан, то доработка клиента достаточно проста.
Обычно я делаю ещё лог в бд и его просмотр. Для этого в Архитектуре предусматривается специальная таблица, которая хранит список операций и список коннектов к бд. Он используется как на клиенте так и сервере, при записи лога, что делал пользователь. Все это включено в общую систему мониторинга всех моих приложений, где можно посмотреть по этому приложению лог по каждому пользователю. Очень удобно. Внутри приложения я добавил мессенджер, чтобы можно было посылать сообщение конкретному пользователю, группе или всем сразу и контроль чтения. В основном это нужно админу и разработчику для информирования или подсказок пользователю, когда у них есть проблемы.
С логированием проблем нет. Лог в таблице даже удобней, чем файлах, так как со всех серверов в одном месте. Хотя из кода тоже можно в таблицу писать, а ещё лучше использовать, например, Graylog.
Я клиентское приложение пишу на Delphi. Cейчас эта система позволяет делать программы и под Web, андроид, линукс, любые бд. Web в десктопных приложениях имхо не особо нужен, только дабавляет сложности. Главное что интерфейс делается очень быстро, так как очень мощная библиотека визуальных компонентов. В этом плане все события привязываться к событиям в компонентах и код приложения легко рефакторить, он не превращается в спагетти, так как бизнес логика выведена из клиентской части в северную. Ну и фишка с разбиением бизнес логики на операции и хранение в отдельной таблице позволяет сильно упростить разработку и клиента и северную часть. Транзакции все обрабатываются только в северной части, так как код обработки, записи лога и корректировки данных находится в хранимых процедурах.
Как оптимизировать медленные SQL запросы?