Таблица Температура против представления в SQL: сохраненный ответ или сохраненный вопрос

1. Что представляет собой каждый из них

Оба созданы из SELECT и оба затем запрашиваются как таблица, и именно поэтому они путаются.

CREATE VIEW v_region AS
  SELECT region, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY region;

CREATE TEMP TABLE t_region AS
  SELECT region, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY region;

Разница в том, что было сохранено. Представление хранило текст запроса и ничего больше; он не содержит строк и не занимает места. Временная таблица выполнила запрос один раз и сохранила полученные строки.

Прочтите любой из них, и вы получите те же четыре строки:

East  | 100000 | 86499255
North | 100000 | 33500090
South | 100000 | 86999300
West  | 100000 | 68000200

Идентичный вывод, и с этого момента все в них отличается.

2. Измерение

Перед пояснением: чтение по четыре ряда из каждого. Скажите, ожидаете ли вы разницы, о которой стоит беспокоиться.

SELECT * FROM v_region      467.6 ms
SELECT * FROM t_region        0.0 ms

Четыре ряда в обе стороны. Просмотр занял почти полсекунды, потому что прочитать его — значит запустить GROUP BY более 400 000 строк каждый раз. Создание временной таблицы не заняло никакого измеримого времени, поскольку четыре строки уже находятся там.

Этот разрыв увеличивается по мере использования. Читая каждое из них десять раз подряд:

view        4688.8 ms
temp table     0.2 ms

Взгляд проделал всю совокупность десять раз. Это конкретная причина, по которой панель мониторинга, построенная на стеке представлений, может работать медленно, что выглядит загадочно: каждая панель считывает представление, каждое представление выполняет свой запрос, а представление, построенное на другом представлении, выполняет и то, и другое.

Скажите вслух, за что вы на самом деле платите, когда принимаете эту стоимость. Вы платите за то, чтобы цифры были актуальными на момент чтения, о чем идет речь в следующем разделе.

3. Показана торговля свежестью

В базовую таблицу добавляется сто новых заказов Севера. Ни представление, ни временная таблица не затрагиваются. Затем оба читаются снова.

the view       North | 100100 | 33600090
the temp table North | 100000 | 33500090

Взгляд заметил. Временная таблица этого не сделала и никогда не сделает, потому что она является копией ответа, который был на момент его создания.

Оба поведения правильны, и оба являются тем, чего кто-то хочет. Мнение является правильным, когда ответ должен отражать данные сейчас: операционную панель мониторинга, определение, на котором основываются другие люди, все, что устаревшее является провалом. Временная таблица подойдет, когда ответ не должен уходить под вас: многоэтапный анализ, где на каждом этапе должны быть одни и те же цифры, отчет, который должен согласовывать десять запросов, все, что вас попросят объяснить завтра.

Второй случай недооценен. Если вы запустите четыре запроса к актуальным данным в течение часа, они могут законно не согласовываться друг с другом, и вы потратите весь день на поиск ошибки, которая на самом деле является поступлением данных. Замораживание входных данных в начале делает весь анализ внутренне последовательным, и это скорее особенность, чем устаревший подход.

4. Обновление временной таблицы

Заморозка является проблемой только в том случае, если вы не можете легко ее разморозить, а можете. Два утверждения, и оно снова актуально:

DELETE FROM t_region;
INSERT INTO t_region
  SELECT region, COUNT(*), SUM(units*unit_price) FROM orders GROUP BY region;

the temp table after the refresh    North | 100100 | 33600090

Теперь это соответствует виду. Это дает вам шаблон, который снимает большую часть напряжения на этой странице: обновите сознательно, в выбранный вами момент и узнайте, когда это произошло. Временная таблица, обновленная в верхней части вашего скрипта, представляет собой снимок с отметкой времени, которую вы можете указать, и это гораздо лучше передать кому-то, чем «она была активной в любой момент, когда выполнялся каждый запрос».

Храните определяющий запрос в одном месте, а не вводите его дважды. На практике это означает однократную запись агрегата в виде представления и загрузку временной таблицы из представления: представление — это определение, а временная таблица — его сегодняшняя копия. Эта комбинация стоит больше, чем любой из них по отдельности.

5. Индексироваться может только один из них

Перед пояснением: оба ведут себя как таблицы в запросе. Скажите, можно ли поставить индекс каждому.

CREATE INDEX ix_t ON t_region(region);      index created
CREATE INDEX ix_v ON v_region(region);      views may not be indexed

Сообщение об ошибке является объяснением. Индекс — это отсортированная структура по хранимым строкам, а в представлении нет сохраненных строк для сортировки. Нечего строить.

Это имеет большее значение, чем кажется, когда промежуточный результат велик и вы собираетесь его к чему-то соединить. Временная таблица из миллиона строк с индексом ключа соединения ведет себя как любая другая таблица; тот же результат, что и представление, создается заново при каждой попытке соединения и вообще не может быть проиндексирован. Если вы делаете что-то повторяющееся с промежуточным результатом, это само по себе часто является решающим аргументом, а остальная часть того, что делают индексы, находится в процессе выполнения. когда индексировать таблицу.

6. Область действия: кто еще может ее видеть

Представление — это постоянный объект в базе данных. Он появляется в каталоге, другие люди могут его запрашивать, и он сохраняется при отключении. Временная таблица является частной для вашего соединения и исчезает при отключении.

Открытие второго подключения к той же базе данных и запрос каждой из них:

sees the view       : 4 rows
sees the temp table : no such table: t_region

Обе половины этого полезны. Общее представление — это точка зрения: это то, как определение последовательно используется несколькими людьми вместо того, чтобы несколько раз перепечатываться в каждом из их запросов. То, что временная таблица является частной, также является важным моментом: вы можете свободно создавать, удалять и воссоздавать ее, не затрагивая никого, и вы не можете оставить после себя беспорядок, потому что она очищается сама собой при отключении.

Следствие застает людей врасплох. Если ваш инструмент создания отчетов открывает новое соединение для каждого запроса, временная таблица, созданная в одном запросе, не будет существовать в следующем. В этом случае ответом обычно является реальная таблица в чистой схеме, а не временная.

7. CTE, временная таблица, представление: что когда

Есть третья вещь, которая выполняет похожую работу, и выбирать между всеми тремя проще, чем кажется, потому что они живут в разных временных масштабах.

Следующее практическое правило: используйте CTE когда промежуточный результат необходим один раз, внутри одного оператора, что происходит в большинстве случаев и что именованные шаги предназначены для. Используйте Таблица температур когда нескольким операторам нужен один и тот же промежуточный результат, или когда это дорого и вы будете читать его повторно, или когда вам нужно, чтобы он оставался неподвижным. Используйте Вид когда другим людям или другим запросам требуется то же определение, и они всегда должны получать текущий ответ.

Стоит упомянуть один случай, поскольку он является распространенным и медленным. CTE, на который ссылаются трижды в одном запросе, может быть вычислено трижды, в зависимости от механизма. Если это дорого и используется более одного раза, это временная таблица, а не CTE.

8. Материализованные представления, которые одновременно

Материализованное представление — это представление, хранящее свои строки: определенный запрос, хранящийся в каталоге как представление, с результатом, записанным в виде таблицы. Его можно индексировать, он доступен для общего доступа и устарел до тех пор, пока его не обновят.

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_region AS SELECT ... ;
REFRESH MATERIALIZED VIEW mv_region;

Это именно та комбинация, которая нужна большинству настроек отчетности: определение живет в одном месте, все читают один и тот же объект, чтение происходит быстро, а запланированное обновление решает, насколько свежим означает «свежий». Цена состоит в том, что кто-то должен владеть обновлением, а материализованное представление, которое никто не обновляет, является худшим из всех миров, потому что оно выглядит как живой объект, но таковым не является.

Поддержка варьируется. PostgreSQL и Оракул имеют их под этим именем. SQL Server имеет индексированные представления, которые обновляются автоматически и имеют ограничения на то, что может содержать запрос. MySQL и SQLite не имеют ни того, ни другого, и стандартный обходной путь — это именно тот шаблон, который описан в четвертом разделе: реальная таблица плюс запланированное задание, которое ее перезагружает.

Представьте себе самую медленную панель управления, на которую вам приходится смотреть. Сколько его панелей считывают представление, которое пересчитывает агрегат по всей таблице, и насколько актуальной должна быть каждая из них?

Пограничные случаи, которые стоит знать

Шесть, которые появятся.

Представления, построенные на представлениях. Каждый уровень запускается повторно, поэтому стек с тремя уровнями выполняет три запроса за каждое чтение. Это самая распространенная причина необъяснимо медленного уровня отчетности, и она невидима, пока вы не прочтете определения.

Временная таблица, которая изжила себя. Это длится сессию, а долгоживущее соединение в записной книжке может длиться сутками. Если цифры перестанут соответствовать производственным, проверьте, когда был сделан снимок, прежде чем проверять что-либо еще.

Пул соединений. Инструменты, которые выдают разные соединения для каждого запроса, потеряют вашу временную таблицу между операторами, и ошибка будет выглядеть так, как будто таблица никогда не создавалась.

Конфликты имен временных таблиц. Они предназначены для каждого сеанса, поэтому каждый из двух человек может иметь t_region и ни одно не влияет на другое. Это удобно и означает, что имя в общем скрипте ничего не говорит вам о том, чьи данные в нем находятся.

Просмотры скрывают их стоимость. Запрос, соединяющий два представления, — это запрос, объединяющий два агрегата по полным таблицам, и его план покажет это, что является еще одной причиной читать планы, а не судить запросы по их длине.

Диск и память. Временная таблица — это реальные данные где-то в памяти или во временном файле, поэтому снимок из ста миллионов строк не является бесплатным. Если промежуточный результат настолько велик, сначала отфильтруйте его; остальная часть этого аргумента находится в Обработка больших наборов данных.