shipment reference что это
Tracking Support
Get helpful information on your package’s whereabouts, options for changing your delivery, filing a claim and more.
Need a username? Sign up.
Prefer to track as a guest? Track a package.
Where’s My Package?
We have answers for all your questions. Let’s check off all the boxes to find out where your shipment is.
My Driver Left a UPS Delivery Notice. Now What?
Your driver left a delivery notice because we missed you the first time around. Here’s what to do next.
Learn About Your UPS Delivery Notice
What Does My Tracking Status Mean?
We put together a list of common tracking statuses to help you understand better where your package is in its journey.
Tracking Statuses Explained
Prevent More Missed Deliveries
Delivery Changes Happen
See what options you have to reroute, hold or cancel your delivery so we can make sure we’re getting it to you when and where you need it.
Can’t Find What You’re Looking For?
We’re here to help. Use our virtual assistant, or find the customer service route best suited for you.
I Have an Issue With My Delivery
If you can’t find your package or it’s damaged, we’ll investigate. If we can’t find it, we’ll issue a claim.
Tracking your package on UPS.com will give you the most up-to-date information about the status of your shipment. Check back periodically for shipment progress updates.
UPS Ground packages are generally delivered anytime Monday through Friday between 9 a.m. and 9 p.m. (and sometimes later) to residences, and to business addresses during their normal business hours.
While ground packages can’t be scheduled to arrive at a specific time, UPS express shipments have guaranteed delivery times that vary by service.
Generally, our drivers can deliver as late as 9 p.m. During the holiday season, our drivers may deliver even later.
If we are unable to deliver a package that shows a status of «out for delivery,» we’ll try another delivery on the next business day.
Your driver will try to leave your package out of plain sight to protect it. Check exterior doors or other places your package could’ve been placed, like the porch, back patio or garage. Also check with anyone who may have picked up your package, like a neighbor. If you still can’t find it, contact the seller to start a claim.
UPS tracking numbers appear in the following formats:
UPS will also track the following types of tracking numbers, which contain from seven to 20 characters:
Delivery Order/Sub-PRO Number: Less-Than-Truckload (LTL)/and Truckload (TL) child shipment tracking numbers
House Air Waybill: Used for air freight movement
House Bill of Lading: Used for ocean freight movement
PRO Number: Used for surface freight movement
UPS InfoNotice: A bar-coded notice, which includes a 12-digit reference number, given on first delivery attempt in select countries
UPS Service Notice: A notice given on first delivery attempt in select countries
UPS Mail Innovations Tracking Numbers
You can track your UPS Mail Innovations piece on both ups.com and upsmi.com. UPS Mail Innovations tracking numbers differ from a small package reference number in length and terminology.
UPS Mail Innovations tracking numbers appear in the following formats:
United States Postal Service Delivery Confirmation Number: a 22-34 numerical identifier assigned by the U.S. Postal Service when Delivery Confirmation is requested
Sequence Number (MMS/MMI Number): an 18-digit number assigned internally by UPS Mail Innovations or UPS Worldship
Mail Innovations Compliant Package ID: a barcode-type identifier assigned by the shipper to each mail piece, using the structure below:
MIXXXXXXNNNNNNNNNNNNNNNNNNNNNN
This indicator is made up of the following three components:
Почему не SQL?
Без преувеличения можно сказать, что SQL — один из самых распространенных в мире языков. Информационные системы могут быть написаны на Java, Python, JavaScript, C#, PHP и десятке других языков, но SQL база в том или ином виде будет в абсолютном большинстве таких систем. Среди бизнес-приложений процент систем, использующих SQL, вообще стремится к 100%.
При этом большинство существующих материалов о SQL на хабре и других ресурсах сводятся к простым вопросам, вроде: «какие типы соединений бывают», «чем левое соединение отличается от правого», «что такое триггеры» и так далее. Более того, в этих материалах практически ничего не говорится о проблемах SQL (и его реализациях), которых на самом деле очень и очень немало. Поэтому мы решили восполнить оба этих пробела: с одной стороны рассказать, как многие вещи в SQL работают изнутри, а с другой стороны — почему они работают не так как нужно / хотелось бы.
При этом речь в статье пойдет не о «вкусах и цветах фломастеров». Все затрагиваемые проблемы носят фундаментальный характер: присутствуют при разработке практически любой информационной системы и не ограничиваются «красотой кода», а в той или иной степени приводят либо к критическому падению производительности, либо к существенному росту порога вхождения, либо к значительным трудозатратам со стороны разработчика.
Статья получилась достаточно объемной, и далеко не все осилят ее за один раз. Поэтому, чтобы было удобнее в ней ориентироваться, а также иметь возможность оценить «масштабы бедствия», начнем с оглавления со списком всех затрагиваемых проблем:
Перед тем как начать, создадим базу с товарами, контрагентами и документами приходов и отгрузок. Наполним ее данными, причем большим количеством, так как дальше будет много примеров с демонстрацией проблем оптимизации запросов / производительности на больших объемах данных.
Уже заполненные базы данных, на которых проводилось тестирование, и параметры подключения к ним можно найти здесь:
Представления (View)
Первоначально SQL задумывался как набор команд для непосредственного общения пользователя с системой. Эта идея достаточно быстро и вполне предсказуемо провалилась, после чего SQL превратился в инструмент разработки, а значит, как и у любого другого инструмента разработки, у него в первую очередь появилась потребность в абстрагировании. Так на свет появились представления (View).
Представления дали SQL возможность отделять объявление функционала от его реализации. Так, например, разработчик может создать представление остатки:
И дальше обращаться к этому представлению как к обычной таблице.
При этом, если ему понадобится изменить логику вычисления остатков, он может легко это сделать, просто поменяв само представление остатков, после чего все запросы, которые использовали это представление, будут работать уже с новой его реализацией.
Как и в структурном программировании, абстрагирование дало SQL две очень важных возможности: упрощение и повторное использование. Казалось бы — вот оно счастье, но дальше, как говорится, что-то пошло не так.
View: Материализация представлений поддерживается в очень частных случаях
Если вы начнете использовать описанное выше представление остатков в реальной базе данных, вы, скорее всего, очень быстро обнаружите деградацию производительности. Дело в том, что остатки обычно используются очень часто, а значит, вычислять остатки при каждом обращении к ним будет очень накладно. Соответственно, чтобы избежать этих расходов, логично такое представление «материализовать», то есть сохранить в таблицу и автоматически обновлять ее при изменении данных, которые это представление использует. Это чуть замедлит запись, но очень сильно ускорит чтение. Казалось бы, что может быть проще. Но проблема в том, что поддерживать такую материализацию, мягко говоря, не так легко, как кажется. Во всяком случае, чтобы это работало эффективно на больших объемах. В вышеприведенным представлении остатков, например, при изменении склада в документе нужно от остатков по старому складу для всех товаров в документе отнять количество в этом документе, после чего добавить это количество к остаткам по новому складу. А при изменении количества для строки в документе нужно к остаткам по складу добавить разницу между старым и новым количеством. Если же, к примеру, в логику остатков добавить внутренние перемещения, все совсем запутается.
Посмотрим, что в этом плане умеют современные СУБД.
У PostgreSQL все просто, материализованные представления он поддерживает, но никаких инкрементальных обновлений, тем более выполняемых сразу при применении транзакции он не поддерживает.
В MS SQL есть так называемые индексированные представления (по сути те же материализованные представления), которые умеют инкрементально обновляться, но список ограничений там впечатляет. Собственно, поддерживаются только SUM GROUP BY и INNER JOIN, то есть даже вышеупомянутый элементарный пример с остатками в MS SQL работать не будет. Плюс в MS SQL нельзя ссылаться на другие представления, поэтому можно смело считать, что материализация представлений в MS SQL не поддерживается вообще.
За инкрементальную обновляемость представлений отвечает опция FAST REFRESH.
У механизма FAST REFRESH в Oracle список ограничений еще длиннее чем у MS SQL, но фактически он сводится к следующему:
Но даже если вы прорветесь через все эти ограничения, проблемы с производительностью, абстрагированием и избыточным потреблением ресурсов и все таки материализуете нужное представление, вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.
То есть, если у вас есть код:
то, если представление balance не материализовано, вы получите актуальный остаток (с учетом изменения строки документа), а если материализовано, вы получите остаток на начало транзакции. Как следствие:
Таким образом, в Oracle поддержка материализаций вроде как лучше, чем в MSSQL, но все равно очень далека от жизнеспособности. Поэтому большинство разработчиков, когда их спрашиваешь про материализованные представления, отвечают, что они вроде и слышали о такой возможности, но реально на практике никогда ей не пользовались. С другой стороны, если вы спросите этих же разработчиков, как именно надо решать задачу материализации тех же остатков, скорее всего услышите что-то про триггеры, общие точки изменения остатков, неправильную физическую модель и так далее. А на уточняющие вопросы — триггеры на что, как именно организовывать эти общие точки и физическую модель, скорее всего, услышите ответ: «я стратег, а не тактик» и вообще «доктор, откуда у вас такие картинки», я с такой задачей никогда не сталкивался. Хотя в то, что человек, который разрабатывает ИС, никогда не сталкивался с задачей хранения и обновления остатков (ну или задолженностей и других похожих показателей), как-то не очень верится.
На самом деле задачи материализации обычно решаются именно что «как-то». Логика вычисления и обновления представления дублируются. Часть сценариев обновления запрещается (например, полностью запрещают изменять документ). Еще иногда используют такой трюк как «перепроведение»: сначала эмулируют полное удаление старых данных (например всего документа), а потом добавление новых данных (то есть создание документа заново). В таком случае достаточно реализовать только логику удаления и добавления, что значительно проще, но куда менее производительно (например, в случаях, когда изменяется только одна строка документа).
View: Для представлений не поддерживаются ограничения и триггеры
Допустим, вы создали представление остатков как в примере выше, используете его для различных вычислений, и тут у вас появляется новое бизнес-требование: остаток должен быть больше 0. Если бы эти данные были первичными и хранились в таблице, вы могли бы легко решить эту проблему, создав соответствующее ограничение на нужное поле, но это представление, а для представлений возможность создавать ограничения ни одна из существующих современных СУБД не поддерживает.
Единственное, что могут предложить в этом плане некоторые коммерческие СУБД (MS SQL и Oracle) — это материализовать представление и создать ограничение для него. Но здесь мы опять-таки возвращаемся к огромному количеству ограничений материализованных представлений. Тут, правда, стоит отметить, что проблема не обновления материализованных представлений в транзакции для ограничений не актуальна. Зато есть другая проблема: ограничения проверяются в самом конце транзакции. Это, в свою очередь, означает, что сначала выполняется вся бизнес-логика системы (а это может быть достаточно большой объем работы), и, если в самом конце вдруг нарушится какое-то ограничение, то вся выполненная работа будет отменена, а значит ресурсы сервера и время пользователя будут потрачены впустую.
Вообще тему ограничений на материализованные представления (и материализованные представления вообще) относительно подробно разбирал один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг. И, в общем-то, пришел к тому же выводу, что и я:
This is pointless from a practical perspective, however.
Sooner or later incremental refresh limitations will be lifted. For the purpose of further constraint study in this book, let’s continue pretending as if it already happened.
получился у него весьма ироничным c учетом того, что книга писалась в 2005 году, а сейчас уже 2019 год, и за последние 14 лет список ограничений практически не изменился.
Но если для ограничений такой workaround с материализованными представлениями еще как-то может работать, то с триггерами вообще забавно:
If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE.
То есть триггеры создавать можно, но делать в них ничего нельзя, так как для материализованных представлений они ведут себя очень непредсказуемо. Такой кот Шредингера, вроде триггер есть, а вроде его и нет. Тут можно найти чуть более подробный разбор этой темы на Ask Tom.
View: В параметризованные представления во FROM можно передавать только константы
Теперь представим, что нам нужно получить не просто остаток, а остаток на дату. Если бы у нас была таблица со «всеми» датами (например dates), то мы могли бы создать следующее представление:
Но такой таблицы в SQL не существует, соответственно, для решения этой задачи придется использовать что-то другое.
В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса:
В свою очередь, во FROM этим функциям можно передавать аргументы, но только не в ON, а в скобках:
В остальном же эти функции ведут себя точно так же, как и представления (часто их и называют параметризованными представлениями).
Даже если оставить в стороне тот факт, что для работы с параметрами примитивных типов в SQL нужна отдельная абстракция, у этого механизма есть одно очень неприятное ограничение, которое делает его применение весьма ограниченным. Так, при использовании во FROM этим функциям нельзя передавать в качестве аргументов колонки других таблиц из FROM. То есть, если попытаться выполнить следующий запрос:
SQL сервер выдаст ошибку, что таблица shipment не найдена. Тут, конечно, можно подумать, что так и надо, потому как использование таблицы из FROM в аргументах параметризованного представления нарушает идеологию свободной перестановки JOIN (то есть, что JOIN’ы можно переставить в любом порядке, в том числе соединять shipment после balance) и поэтому такой возможности в SQL не может быть теоретически. Но на самом деле многие SQL сервера умеют использовать в подзапросах поля из уже соединенных таблиц (мы увидим это в следующем разделе про Join Predicate Push Down), поэтому они вполне могли бы разрешить это делать разработчику, просто убирая при перестановке JOIN’ов варианты, когда параметризованное представление (или вообще любой подзапрос) соединяется до таблицы, поля которой оно используют. И почему ни один производитель СУБД так не сделал, если честно, для меня загадка.
UPD: Пришла подсказка из зала, что такую возможность производители СУБД все же реализовали в виде специальной конструкции APPLY (или опции LATERAL в JOIN). Даже если опять-таки отбросить факт необходимости использования еще одной абстракции для реализации по сути одного частного случая, у подхода с APPLY есть два недостатка (один из которых весьма существенный):
В любом случае, факт остается фактом, и сейчас единственный выход для разработчика в таких случаях это самостоятельно делать pushdown верхнего контекста внутрь представления:
а значит, неоднократно повторять логику вычисления этого представления в различных запросах, и, тем самым, нарушать один из ключевых принципов программирования — Don’t repeat yourself.
Подытоживая все вышесказанное касательно представлений, можно сделать вывод, что представления, хоть и выглядят как таблицы, но по факту не умеют и четверти того, что умеют таблицы, поэтому, к сожалению, в состоянии обеспечить в SQL лишь самый базовый уровень абстрагирования (а точнее, очень низкий, по сравнению с тем, который мог бы быть, если бы все вышеописанные возможности поддерживались в общем случае)
Join Predicate Push Down (JPPD)
Проталкивание предикатов внутрь подзапросов является одной из самых важных возможностей оптимизатора SQL сервера. Этот механизм позволяет вычислять результаты подзапросов не для всех данных в базе (тем самым приводя порой к катастрофической деградации производительности), а только для тех данных, которые необходимы верхнему запросу.
Сначала рассмотрим простой пример, когда у нас есть условие, что значение поля из подзапроса должно быть равно некоторой константе (то есть случай просто predicate push down, без join):
В этом случае SQL сервер видит, что снаружи есть условие на то, что поле product подзапроса должно быть равно 345, и автоматически переносит это условие туда. Заодно, так как в этом подзапросе есть группировка по полю product, SQL сервер автоматически убирает это поле из BY (так как оно всегда равно одному значению), а так как других BY в подзапросе нет, то и весь GROUP BY целиком. Итого получается следующий запрос:
Далее SQL сервер видит такую же ситуацию с UNION подзапросами и автоматически переносит этот предикат внутрь каждого из UNION:
В итоге при наличии индекса по product в shipmentDetail и в receiptDetail такой запрос выполнится ну очень быстро.
Теперь рассмотрим более сложный случай:
Как и при выполнении любого другого запроса SQL сервер начинает перебор порядков выполнения join. Допустим он уже выбрал, что первым join’ом будет product и что доставать записи оттуда он будет при помощи индекса по group (предположим, что он есть). Далее он пытается присоединить к результату подзапрос balance, например при помощи nested loop join (то есть пробегом по уже имеющемуся результату, в нашем случае — таблице product, отфильтрованной по полю group). В этот момент SQL сервер видит, что у него есть предикат balance.product = product.id, где product.id — константа, то есть, точь-в-точь предикат из примера выше, а значит можно запустить соответствующую технику predicate push, что он собственно и делает.
Среднее время выполнения: 128мс
Среднее время выполнения: 80мс
Тем самым подзапрос balance рассчитывается только для товаров с группой 54, а не для всех товаров в базе (правда, нужно понимать, что в этом случае подзапрос рассчитывается несколько раз, для каждого товара с группой 54).
Тут, конечно, может показаться, что техника JPPD может работать только для nested loop join, но это не так, SQL сервера умеют проталкивать и hash join. В этом случае проталкивается не предикат balance.product = значение, а «виртуальный» предикат hash(balance.product) = значение («виртуальный», потому как у этого предиката нет синтаксического эквивалента в SQL, но тем не менее для выполнения он используется).
Вообще у механизма JPPD есть другое, более «декларативное» объяснение — через переписывание запроса. Так, верхний запрос можно переписать в виде:
И именно в таком ключе (с переписыванием запроса) механизм JPPD описан в патенте Oracle. Однако, на мой взгляд, такое описание не совсем корректно, потому как ни в одном SQL сервере (в том числе Oracle) нельзя обращаться к полям таблиц из верхнего запроса (на самом деле, непонятно почему, но на этом вопросе мы уже останавливались, когда говорили о параметризованных представлениях), а значит, понять, как именно работает JPPD, из такого описания очень сложно.
Справедливости ради, надо сказать, что SQL сервера не всегда делают описанные выше оптимизации автоматически. На самом деле, они только пытаются сделать такой predicate push down, то есть строят соответствующий план, затем рассчитывают стоимость его выполнения и сравнивают его со стоимостью выполнения запроса без проталкивания внутрь предикатов. И только если стоимость плана без проталкивания выше, выбирают план с проталкиванием.
Отметим, что механизм JPPD особенно важен для описанного выше механизма представлений. И если бы JPPD не существовало, то и в механизме представлений тоже было бы очень мало смысла, потому как представления рассчитывались бы для всей (!) базы при каждом обращении к ним, а значит производительность представлений была бы просто ужасная.
Итак, как мы видим, механизм JPPD в СУБД реализуется относительно просто, но у этой простоты есть и обратная сторона медали.
JPPD: Не работает с оконными функциями и рекурсивными CTE
Допустим, мы хотим получить порядковый номер строки в документе и напишем следующий запрос:
Среднее время выполнения: 1.2с
Среднее время выполнения: 14с
Как можно увидеть из плана, SQL сервер рассчитывает номера строк для всех документов в базе и, как следствие, запрос выполняется целую секунду (вместо нескольких миллисекунд).
JPPD: Низкая эффективность при работе с денормализованными данными
Допустим, мы хотим получим получить все отгрузки с даты по дату вместе с общими суммами отгрузки клиентам (немного надуманный случай, тут правильнее было бы говорить о, скажем, сумме задолженности клиента, но базовый пример очень простой, а за его пределы выходить не хочется, поэтому будем использовать то, что есть):
В этом случае SQL сервер будет рассчитывать сумму не для всех различных клиентов, по которым были отгрузки за эти даты, а для всех отгрузок за эти даты, то есть сумма по каждому клиенту может рассчитываться несколько раз.
Правда, эта проблема проявляется в основном, когда есть корреляция между условием отбора и условием соединения (то есть в этом примере между датой отгрузки и клиентом). На практике такое бывает очень часто (например, многим клиентам отгрузка идет только в течении какого-то периода), но конкретно в тестовой базе этой статьи мы генерировали равномерно распределенные данные, поэтому продемонстрировать описанную проблему на этой базе, к сожалению, не получится.
JPPD: Поддерживается только в коммерческих СУБД
Несмотря на простоту реализации, JPPD не поддерживается в PostgreSQL. Даже в самом примитивном случае:
По какой причине так получилось, и почему разработчики PostgreSQL заняты чем угодно, но не решением проблемы, из-за который те же представления использовать практически невозможно, если честно, для меня загадка.
Возможно проблема в патенте на JPPD, который у Oracle действует аж до 2028 года (они его постоянно продлевают, внося небольшие изменения). Однако, как мы видели выше, Microsoft’у существование такого патента абсолютно не мешает.
Как видим, все описанные проблемы JPPD хоть и не смертельные, но очень неприятные. Решить их можно, используя более общий механизм JPPD: собирать предикаты из внешнего запроса, группировать их по условиям соединения и полученный запрос добавлять при помощи JOIN внутрь подзапроса.
Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом:
Среднее время выполнения: 60мс
Среднее время выполнения: 30мс
Predicate Information (identified by operation id):
—
1 — filter(«T».«ID»=6770436)
5 — access(«T».«ID»=6770436)
7 — access(«T».«SHIPMENT»=«SHIPMENT»)
Переписанный запрос для работы с денормализованными данными:
Ну и наконец, эту технику можно использовать для оптимизации запросов в PostgreSQL. Так, пример в описании JPPD можно переписать в:
Конечно, в таком случае JOIN с product будет выполнен три раза, но это куда меньшее из зол, чем расчет подзапроса для всей базы.
Разделение логики условий на типы JOIN и WHERE
Немногие это замечают, но логика, влияющая на то, какие записи окажутся в результирующей таблице в SQL, разделена на 2 части:
Хуже всего в этом разделении на самом деле дела обстоят именно с FULL JOIN, так как для результирующих колонок приходится использовать COALESCE, что сразу убивает возможность PPD оптимизаций. Поэтому на практике вместо FULL JOIN чаще используют UNION, то есть вместо:
Правда, если нам необходимо рассчитать сумму двух колонок из A и B, все становится заметно хуже (приходится делать несколько LEFT JOIN). Так, запрос:
С UNION выглядит как-то так:
А если нам надо из таблиц A, B, C, D выбрать записи по условию (A.f=1 OR B.f=2) AND (C.f=3 OR D.f=4), то все становится совсем печально. Так, с FULL JOIN мы опять-таки несколькими COALESCE убьем все возможные оптимизации, а с UNION разработчику придется самому приводить условие к ДНФ и писать что-то вроде такого:
При этом, если нам надо будет еще вычислить какую то формулу от полей из A, B, C и D, то из-за LEFT JOIN’ов запрос вырастет еще в два раза.
Тут, конечно, многие скажут: «что это у вас за такая странная физическая модель», но, во-первых, очень часто физическую модель не выбирают (то есть она достается как есть), а во-вторых, как мы увидим в следующем разделе, даже если все эти поля будут в одной таблице, проблемы все равно будут (правда, немного по другой причине).
В любом случае, если бы тип JOIN задавался логическим предикатом IN JOIN, а особенно, если бы он мог выводится из условия (скажем, из A.f = 1 следует IN JOIN A), то запрос можно было писать как:
И дальше SQL сервер уже сам мог бы решить, как именно разбить этот запрос на UNION / FULL JOIN и какие типы JOIN подставить в полученных подзапросах. Сейчас же эту работу приходится выполнять непосредственно разработчику.
Плохая оптимизация OR
Вернемся к примеру из предыдущего раздела. Допустим, у нас не четыре разные таблицы A, B, C, D, а одна таблица mytable и четыре поля A, B, C, D:
Соответственно запрос в этом случает будет выглядеть следующим образом:
Чтобы этот запрос выполнялся быстро, при создании таблиц мы добавили индексы по различным комбинациям этих колонок: AC, BC, AD, BD.
Среднее время выполнения: 320мс
Как видим, SQL сервер даже не попытался поработать с условием, а просто выполнил то, что увидел. То есть взял первую скобку, увидел в ней OR, разбил его на индексы, а вторую скобку просто применил сверху.
Больше всех, конечно, отличился MS SQL:
Среднее время выполнения: 1.8с
Он решил вообще не использовать индексы. Но я проверил, если разновидностей колонок больше, MS SQL умеет строить план с индексами аналогичный Oracle и PostgreSQL, так что спишем это на особенности его настройки.
При этом даже если раскрыть вторую скобку:
Это не помогает — планы остаются такими же.
Единственный вариант, когда этот запрос выполнится нормально, — это преобразовать исходное условие к ДНФ:
Среднее время выполнения: 30мс
Среднее время выполнения: 60мс
Как можно увидеть из этих примеров, SQL сервер особо даже не пытается оптимизировать логические выражения. И это, в общем-то, понятно, так как такая оптимизация — NP-полная задача и возится с ней создателям SQL серверов, видимо, не очень хотелось. Поэтому, как и с типами JOIN, они просто решили переложить эту задачу на разработчика.
Плохая оптимизация при работе с разреженными данными
Теперь представим такую ситуацию. У нас есть большая таблица (shipmentdetail) и мы решили добавить туда новую колонку (sid) и индекс по ней (shipment_sd). Эта колонка по умолчанию null и заполнена для очень незначительного процента данных. Нам необходимо найти все дубликаты sid, для этого делаем следующий запрос:
Среднее время выполнения: 3.5с
Как видим, MS SQL, когда пытается бежать по индексу shipmentdetail_sd, не догадывается, что нужно ставить фильтр на IS NOT NULL, как следствие, производительность этого запроса очень низкая.
Если же добавить явные условия на то, что s1.sd и s2.sd IS NOT NULL картина резко улучшается:
Среднее время выполнения: 100мс
С PostgreSQL все сложнее, базовый запрос у него работает и работает хорошо, но не потому что он добавляет фильтр, а потому что использует merge join и, видимо, умеет пропускать в нем NULL значения (тут странно, что если MS SQL добавить соответствующие хинты, это не помогает):
Хотя, если посмотреть на estimate и cost, то видно, что PostgreSQL реально планирует пробежать по всем записям таблицы. Поэтому, если чуть-чуть изменить запрос и добавить, скажем, условие — найти дубликаты с количеством > 7
То получим ту же проблему, что и в MS SQL:
которая лечится все тем же добавлением явных условий на то, что s1.sd и s2.sd IS NOT NULL:
Oracle — единственный, кто не сплоховал в данном случае и догадался добавить такие предикаты сам:
Среднее время выполнения: 30мс
Плохая оптимизация при работе с последними значениями
Самые часто используемые агрегирующие функции в группирующих запросах в OLTP бизнес-приложениях — это сумма и последнее значение (например, в 1С это регистры накопления и сведений соответственно). С суммой все более-менее понятно, а вот с последним значением есть много вопросов, как в плане поддержки в SQL серверах такого функционала вообще, так и его дальнейшей оптимизации.
Итак, допустим, мы хотим получить для товара последнюю его отгрузку. Для начала будем считать, что последнюю отгрузку надо искать по внутреннему номеру этой отгрузки (а не, скажем, по дате). Первое, что нам предложит поиск по stackoverflow — это запрос:
Чтобы быстро выполнить верхний запрос, очевидно напрашивается индекс по product, shipment, и действительно, если мы его построим и выполним наш запрос, получим достаточно красивый и эффективный план выполнения (что удивительно, даже в PostgreSQL):
Среднее время выполнения: 70мс
Среднее время выполнения: 30мс
В частности, в этом плане все SQL сервера догадываются вставить проверку, что достаточно считать ровно один ряд (FIRST ROW — Oracle, LIMIT — MS SQL и PostgreSQL).
А теперь попробуем чуть усложнить запрос и выполнить его не для одного товара, а для множества товаров, например, всех товаров, начинающихся на Product 86 (PostgreSQL сразу выбывает из гонки, так как JPPD не поддерживает в принципе).
Среднее время выполнения: 60мс
Запрос по прежнему выполняется быстро, но настораживает тот факт, что проверка на один ряд исчезла, а это очень важно, когда записей станет много.
Ну а теперь выполним запрос для всех товаров.
Среднее время выполнения: 2.6с
Как видим, Oracle, не догадавшись вставить проверку на один ряд, вообще переходит на Hash Group By без JPPD (что, впрочем, логично без проверки на один ряд) и выполняет этот запрос уже несколько секунд. В то же время, если переписать этот запрос на subquery expressions с ORDER BY и rownum=1 (то есть по сути явно указав Oracle, что нужно выбирать одну запись), план становится значительно лучше:
Среднее время выполнения: 300мс
И запрос выполняется за несколько сотен миллисекунд, то есть в 10 раз быстрее. Почему Oracle по разному оптимизируют запросы, когда у него в предикате равенства константа задана явно и когда она приходит из JPPD — загадка. Но на практике разработчику для нормальной производительности придется вставлять вот такие вот костыли как в запросе выше. Причем, если в базе нет нужного индекса или база «пустая» (то есть когда отгрузок еще нет, а товары есть и их много), запрос с такими костылями будет выполняться гораздо хуже, по сравнению с базовым запросом и тем, как его выполняет Oracle. То есть по хорошему такая оптимизация должна быть решением самой СУБД, а не разработчика, который может не знать ни статистики, ни того, какие индексы есть в системе.
Что касается MS SQL, то если в прошлом разделе у MS SQL была проблема, а у Oracle нет, то здесь все наоборот. MS SQL догадывается вставлять Top 1 в план:
Среднее время выполнения: 300мс
И выполняет этот запрос так как надо. Правда, если заменить JOIN с LEFT на INNER, то Top 1 магическим образом пропадает (хотя непонятно в чем разница, и зачем SQL серверу может понадобится больше чем одна запись), и мы имеем ту же проблему, что и в Oracle:
Среднее время выполнения: 2.8с
То есть фактически в MS SQL все INNER JOIN подзапросов с MAX необходимо преобразовывать в LEFT JOIN. Но это все же меньшее из зол по сравнению с Oracle (где, как мы видели, все JOIN подзапросов с MAX надо преобразовывать в subquery expression с rownum=1).
В любом случае, несмотря на описанные выше оптимизации, у подхода с MAX / MIN есть два очень существенных недостатка. А именно, непонятно что делать:
Проблема N+1
Есть распространенное мнение, что если система написана на SQL (а точнее его расширениях PL/SQL, T-SQL и т.п.), то она автоматически не имеет проблемы с многократным выполнением одних и тех же запросов, но с разными параметрами. А это, скажем так, не совсем верно. По большому счету в расширениях SQL проблема N+1 не сильно отличается от аналогичной проблемы в тех же ORM-фреймворках.
Например, у нас есть хранимая процедура:
А теперь нам нужно выполнить эту процедуру для 1000 записей. И тут у нас два варианта:
Однако если с хранимыми процедурами еще есть какой-то workaround, то с триггерами во многих SQL серверах все еще хуже. Здесь мы не знаем, когда триггер будет вызван и, соответственно, переписывать нечего. Теоретически для решения проблемы N+1 в SQL серверах есть триггеры per statement (в противовес per row), но:
UPD: Еще одна подсказка из зала, в PostgreSQL начиная с 10 версии есть так называемые transition таблицы (new_table и old_table), функционал которых аналогичен inserted и deleted в MS SQL.
Высокая цена ошибки при проектировании БД
Если показать большинство описанных выше проблем разработчику БД, первое, что вы, скорее всего, услышите в ответ будет: «да у вас неправильная модель БД». Причем будет предполагаться, что эта «неправильная модель» одновременно и причина, и средство решения всех ваших проблем.
Если посмотреть на «неправильную модель» как на причину всех проблем, то тут вообще непонятно использование термина «неправильная». Обычно при создании системы невозможно предугадать не то что, какая там будет статистика, а какой в принципе будет функционал этой системы через пять-десять лет. Постоянно меняться — одна из ключевых особенностей любого бизнеса (особенно на конкурентных рынках), а вместе с самим бизнесом необходимо изменять и его ИТ-системы (как зеркало этого бизнеса). И то, что было правильно сначала, может быстро стать неправильным потом. Так что, если кто-то вас упрекнет в том, что вы неправильно спроектировали БД, можете смело кидать в этого человека камень. Уверен, что при изменении требований к его БД нужным образом (а это неизбежно) его модель тоже будет неправильной.
С «неправильной моделью» как средством решения проблем все еще сложнее. Как мы увидели в первом разделе, нормализация / денормализация БД в современных SQL-серверах — не такой уж простой и прозрачный процесс. От слова совсем. Но даже если вам надо просто переместить несколько полей из разных таблиц в одну таблицу или наоборот разложить некоторые поля одной таблицы по разным таблицам, вы, скорее всего, столкнетесь с не меньшими трудностями и вам, возможно, придется переписать довольно значительное число запросов, как записи, так и чтения. Тут, конечно, вам на помощь, скорее всего, придут все те же представления, но, как мы видели в остальных разделах, представления поддерживают далеко не все то, что поддерживают таблицы (в частности, ограничения, триггеры и индексы), плюс имеют проблемы с производительностью при использовании FULL JOIN и т.п. Поэтому так просто взять и заменить таблицу на представление, скорее всего, не получится.
Так что, как говорится, работа разработчика БД и опасна и трудна, и требует не только хорошее аналитическое мышление, но и развитые экстрасенсорные способности, так как любая ошибка в проектировании БД может впоследствии очень дорого стоить.
Непредсказуемая оптимизация при работе с большим количеством JOIN
В современных SQL серверах для построения планов запросов (в частности, определения порядков JOIN) используется так называемый Cost-Based Optimizer (CBO). Часто его преподносят как очень сложный и умный механизм, и он действительно внутри учитывает огромное количество информации, от индексов по функциям до партиционирования таблиц и индексов, но именно это количество информации и играет с ним злую шутку — алгоритмически это не более чем обычный перебор. А значит, как и в любом переборе, сложность работы CBO растет экспоненциально от количества join’ов. Более того, так как это перебор перестановок, а не подмножеств, сложность у этого перебора вообще космическая — O(n!). То есть даже для двенадцати join’ов вариантов их перестановок будет около 48 миллионов. Понятно, что у алгоритма поиска планов есть отсечения, но с таким количеством вариантов даже они не помогут. Поэтому почти все SQL сервера при большом количестве join’ов переходят на различные эвристики. И вот тут у некоторых SQL серверов начинаются проблемы. Так, в PostgreSQL GEQO алгоритм умудряется пропускать чересчур очевидные варианты. К примеру, в запросе может быть одна единственная маленькая таблица, которая находится в запросе на расстоянии больше 8 join’ов от таблицы, с которой у нее общее условие, и при этом в результирующем плане она будет соединяться в самом конце.
Вообще, опыт показал, что при работе с большим количеством join эффективнее всего следующий подход: жадняком с минимальным lookahead’ом определить самый очевидный порядок join, после чего перебор в cost-based оптимизации начинать именно с этого порядка join (а не порядка join, заданного в запросе). Возможно, MS SQL и Oracle так и делают, но найти какую-то конкретную информацию по используемым ими эвристикам очень тяжело (в отличии от PostgreSQL). Везде написано что-то в стиле:
This heuristic uses sophisticated methods for instantaneously finding particular
plans in the search space which are likely to be nearly optimal or, at least, very
good execution plans.
Вживую, к сожалению, протестировать эти сценарии в MS SQL и Oracle пока не удалось (так как эти сценарии требуют одновременно и большое количество данных, и сложную логику), но в будущем, я надеюсь, это все-таки удастся сделать и тогда я дополню статью этой информацией.
Отсутствие наследования и полиморфизма
Наследование и полиморфизм (здесь и далее речь пойдет о subtype полиморфизме) появились задолго до появления SQL и, за счет возможности эффективно декомпозировать задачи, а также не наращивать технический долг по мере роста сложности системы, совершили небольшую революцию в программировании, позволив создавать системы значительно более сложные, чем существовавшие ранее (такую же революцию в программировании, кстати, в свое время совершило абстрагирование, дав миру, в частности, структурное программирование).
И если в простом проекте польза этих двух механизмов не настолько очевидна, то по мере роста этого проекта, именно наследование и полиморфизм являются одними из основных барьеров от превращения его кода в один большой «спагетти-код».
Посмотрим, что в этом плане умеют современные SQL сервера.
У MS SQL все просто — они даже не пытались поддержать ни наследование, ни тем более полиморфизм.
В PostgreSQL формально наследование таблиц есть, но не более того. А учитывая, что смысла в наследовании без полиморфизма нет практически никакого, зачем в PostgreSQL наследование вообще добавляли — неясно.
Вообще, если провести аналогию со структурным программированием, полиморфизм в SQL, по идее, должен был выглядеть как возможность создания абстрактного представления, в который можно добавлять различные UNION’ы в качестве реализации, то есть что-то вроде:
Но такой возможности ни в PostgreSQL, ни в других SQL серверах нет.
В Oracle решили скрестить «ежа с ужом»:
Избыточно низкий уровень абстрагирования
Как известно, SQL — язык реляционной алгебры, то есть язык работы с таблицами. При этом абсолютное большинство таблиц в БД, как правило, находятся во второй нормальной форме (имеют ключи), а значит, любую таблицу можно рассматривать как множество функций, то есть отображений ключей (параметров функции) на колонки (значения функции). При этом все операции над таблицами все равно выполняются в реляционной алгебре, то есть при помощи операций соединения и объединения, создавая тем самым дополнительную ненужную сложность. К примеру, операция соединения — это декартово произведение двух таблиц, что, скажем так, не слишком очевидно для обычного человека (во всяком случае, в жизни люди с такой операцией практически не сталкиваются). И я много раз пытался объяснять людям, не имевшим опыта работы с SQL, эту операцию формально, но безуспешно. В итоге все сводилось к «обезьянному методу» обучения: надо получить такую информацию — используй вот такой запрос, другую информацию — другой запрос и так далее. Впрочем, справедливости ради, даже если меня спросить что будет, если сделать LEFT JOIN таблицы и указать в условии соединения не все ключи, мне придется серьезно поломать мозг. То есть построить таблицу я смогу, но логически для меня это будут бессмысленные данные. Как говорилось в одном культовом сериале: «She understands, she doesn’t comprehend». А у меня опыт работы с SQL, скажем так, не самый маленький, мне приходилось работать с настолько сложными запросами, что они приводили к багам в PostgreSQL, причем таким, что меня даже лично упоминали в Release Notes (потому как запросы были просто из join’ов и =, то есть баг центральнее некуда)
Альтернативой, позволяющей решить проблему избыточной сложности, было бы использование в SQL не реляционной алгебры — с таблицами, соединениями, а функциональной — с функциями и композициями. Впрочем, эта тема уже подробно разбиралась в отдельной статье, поэтому здесь я приведу лишь пример задачи с 2* (для наглядной демонстрации различия этих двух подходов)
А уже читатель сам может решить, какой из этих подходов проще.
Адаптивная оптимизация(AO)
Одним из ключевых условий построения эффективных планов выполнения является правильная оценка статистики результатов соединения таблиц / подзапросов. Так, если в обеих соединяемых таблицах мало записей, или их мало в одной из таблиц, а во второй есть индекс по условию соединения, для соединения этих таблиц можно (и нужно) использовать цикл (nested loop join), в остальных случаях, как правило, эффективнее использовать hash или merge join. Но что будет, если планировщик думал, что записей мало, а их на самом деле оказалось много? В общем-то, ничего хорошего. Причем самые разрушительные последствия будут в случае, если планировщик думал что в обеих таблицах мало записей, а оказалось что в каждой из них, к примеру по 10к записей. В этом случае в процессе выполнения запроса SQL серверу придется пробежаться по 100млн записей (или даже построить такую таблицу), что создаст большую нагрузку как на процессор (особенно если включен параллелизм), так и на память (в том числе постоянную, то есть СХД). А ошибаться со статистикой SQL сервера могут во многих случаях, самыми распространенными из которых являются неравномерная статистика и корреляция данных. Конечно с этими явлениями SQL сервера борются как могут (так, к примеру, они хранят наиболее часто встречающиеся значения, cross-column статистику, всяческие гистограммы и т.п.), но даже со всеми этими техниками при наличии в запросе большого количества таблиц вероятность ошибки в статистике все равно достаточно высока.
Чтобы сделать сервер более устойчивым к таким ошибкам, SQL сервера используют следующую технику: если прогнозируемое количество записей меньше некоторого порога, и, по идее, должен был бы использоваться nested loop join, они вставляют в план альтернативную ветку (так называемый adaptive join), которая активируется, если реальное количество записей значительно превысит прогнозируемое. В частности, такая техника позволяет исключить упомянутый выше самый «разрушительный» сценарий — соединения двух огромных таблиц при помощи nested loop join. Но, как и у JPPD, у AO есть ряд недостатков.
AO: Поддерживаются только в коммерческих СУБД
Этот механизм не поддерживается в PostgreSQL. Собственно, даже в MSSQL его поддержка появилась всего 2 года назад, поэтому ожидать его появление в PostgreSQL в ближайшее время не приходится. Усугубляет ситуацию еще то, что PostgreSQL редкостный оптимист. Так, если PostgreSQL не знает selectivity, то считает ее равной 0.3 (там реально в коде такая константа захардкожена), плюс, если он ничего не знает про два условия соединения, то считает их некоррелированными и просто перемножает selectivity. Как следствие, он очень часто думает, что в промежуточной таблице будет одна запись, и если, например, запрос содержит несколько подзапросов (информация о которых как раз часто попадает в класс «неизвестной»), стреляет себе в ногу с завидной регулярностью.
С другой стороны, с учетом отсутствия JPPD и непредсказуемостью GEQO, возможно изначально и не планировалось, что PostgreSQL должен эффективно работать со сложными запросами. Так что отсутствие AO, как и излишний оптимизм отлично вписываются в эту парадигму.
AO: Вероятность ошибки все равно достаточно высока
This means that a threshold depends on the estimates, so accurate estimates are still important.
То есть если оптимизатор ошибется не в нижнюю, а в верхнюю сторону, то адаптивный join не создастся, а значит SQL сервер, к примеру, может не использовать индекс, когда это было нужно.
Также при такой схеме возможна ситуация, когда join с ошибочно маленькой статистикой будет неправильно поставлен в начало, и тем самым воспрепятствует попаданию в начало «правильного» join. При этом, когда SQL сервер обнаружит ошибку, будет уже поздно:
Adaptive plans do not allow the join order to be altered, so even if a better join method or parallel distribution method is used, the execution plan may still be sub-optimal.
Для хотя бы частичного решения этих проблем в Oracle помимо adaptive join также поддерживается так называемая адаптивная статистика (adaptive statistics). Она позволяет собирать для выполняемого запроса реальную статистику каждого join, а затем использовать ее при следующем выполнении этого же запроса, при необходимости перепланировав его. Впрочем, у этого механизма тоже есть ряд проблем:
The reasoning for this is many of these optimizations are more appropriate for data warehousing, where there optimization time is is a small proportion of the query runtime. In OLTP environments, where SQL runtime is typically smaller, the additional optimization time may become a significant part of the elapsed time, for little extra benefit.
В то же время есть более простая и во многом даже более эффективная техника адаптивной оптимизации. Ее смысл заключается в том, чтобы определить общее предполагаемое время выполнения запроса (с определенным запасом), и если оно будет превышено, отменить запрос, материализовать в нем часть подзапросов (скорингом определив наиболее подходящие), а затем попытаться выполнить этот запрос еще раз. Так как подзапросы будут сохранены во временные таблицы и по ним будет достаточно подробная статистика, оптимизатору будет гораздо проще построить эффективный план по сравнению с базовым запросом. Естественно, всю информацию о новом времени выполнения, материализация каких подзапросов помогла быстро выполнить запрос и т.п. необходимо кэшировать и использовать при последующих выполнениях.
Тут, правда, надо сказать, что такой подход требует выполнения нескольких дополнительных условий:
Заключение
Подводя итог, коммерческие SQL сервера поддерживают достаточно много сложного функционала и оптимизаций, но практически всегда это делают в таком виде, что использовать эти возможности на практике — себе дороже. Выстрелить в ногу можно практически на каждом шагу, а использование некоторых оптимизаций напоминает анекдот про установку вируса под Linux — там где эти оптимизации должны подстраховывать разработчика, нужно наоборот заставлять их работать.
PostgreSQL же не поддерживает даже базовые возможности по оптимизации (в частности JPPD), поэтому использовать его «как есть» для разработки основной части бизнес-логики, как это часто делают с MS SQL и Oracle, затея, скажем прямо, весьма трудоемкая и рискованная. Еще более забавно, когда кто-то заявляет о кроссплатформенности по СУБД (включая туда PostgreSQL), потому как тут два варианта:
Здесь конечно может возникнуть вопрос, к чему вообще эта статья, критиковать может любой дурак. И, если честно, я не писал бы эту статью, если бы не знал, что все описанные проблемы можно решить. Да, непросто, я бы даже сказал, очень и очень непросто, но ведь за коммерческими СУБД стоят одни из крупнейших в мире корпораций. И у них вроде как работают «лучшие в мире инженеры». Поэтому, когда приходится решать проблемы за них, невольно ловишь себя на мысли, что «что-то здесь не так». Но оставим это на их совести.
А теперь, как говорится, минутка рекламы. Все вышеизложенные проблемы удалось решить в lsFusion, причем сделать это в общем случае. Прозрачная материализация (денормализация) данных, ограничения и триггеры на любые вычисляемые данные, JPPD в общем случае, динамическая физическая модель, компактный и одновременно быстрый оптимизатор булевой логики, компиляция циклов в запросы и наоборот, оптимизация работы с разреженными данными и последними значениями, множественные наследование и полиморфизм — и это только вершина айсберга. Помимо всего этого есть еще логика представлений и множество других чисто языковых возможностей, но это все к SQL напрямую не относится и будет рассмотрено в следующей статье.
Нас, кстати, постоянно упрекают, почему мы lsFusion сравниваем в том числе с DBMS, и, я надеюсь, эта статья дала хотя бы часть ответов на этот вопрос. Да, последнюю милю (определение типов выполнения JOIN — loop/hash/merge, поддержку ACID и т.п.) lsFusion не закрывает и использует для этого RDBMS, но по большому счету это особенности реализации lsFusion, разработчик этого не видит (если не хочет, конечно). Плюс, важным моментом является то, что, так как ответственность за оптимизацию всех запросов лежит на lsFusion, в качестве RDBMS можно использовать самую примитивную из них — PostgreSQL. И в этом смысле lsFusion можно рассматривать как своеобразный костюм железного человека для PostgreSQL, дающий ему «суперспособности» MS SQL и Oracle, во всяком случае, в плане оптимизации запросов. При этом, если последние в нормальных редакциях стоят по 14 тысяч долларов за ядро, то связка lsFusion + PostgreSQL абсолютно бесплатна. Впрочем, у этого факта есть и обратная сторона, мы так и не убедили ни одного заказчика поставить себе MS SQL или Oracle в промышленную эксплуатацию, поэтому многие вещи в коммерческих СУБД проверялись исключительно на тестовых стендах, а значит, вполне возможно, в статье могут быть некоторые ошибки. Так что если кто-нибудь где-нибудь такую ошибку найдет, просьба написать про нее в комментариях, и мы ее обязательно исправим.