Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

SELECT ...

WHERE PARENT COUNTRY = 'AU' OR 1=1

Использование интуиции

Получение "картины" соединения не является точной наукой, в процессе работы вы получите интуитивное понимание создания спецификаций соединений. Однако с этой техникой борются многие люди. Для всех спецификаций запросов, особенно для запросов к множеству таблиц, секрет заключается в добросовестном их проектировании. Если у вас мало опыта в SQL, не полагайтесь на инструменты CASE или утилиты конструирования запросов, чтобы научиться их создавать. Это классическая ситуация: пока вы не приобретете опыт, вы не сможете оценить, насколько глупы данные инструменты. Если вы всегда полагаетесь на тупые инструменты, вы никогда не приобретете интуицию.

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

Пора дальше

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

ГЛАВА 23. Упорядоченные и агрегатные наборы.

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

Наборы, заданные оператором SET, по умолчанию читаются в произвольном порядке. Часто, особенно при отображении данных для пользователя или при печати отчетов, вам нужен определенный вид сортировки. Ясно, что список телефонов будет более полезным, если фамилии будут находиться в алфавитном порядке. Группы чисел по продажам или тестовые результаты более понятны, если они упорядочены, сгруппированы и просуммированы. В SQL существует два предложения по заданию формирования выходных наборов.

Предложение ORDER BY используется для сортировки наборов в возрастающем или убывающем порядке по одному или большему количеству столбцов. Предложение GROUP BY может разделять набор на вложенные группы, или уровни, в соответствии со столбцами из списка SELECT и (по желанию) выполняя агрегатные вычисления на множестве числовых столбцов в границах группы.

Обсуждение сортировки

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

Задание порядка в предложениях сортировки

Следующая упрощенная структура синтаксиса оператора SELECT показывает позицию предложений ORDER BY и GROUP BY В спецификациях упорядочения или группирования. Оба предложения являются необязательными и оба могут присутствовать в операторе:

SELECT [FIRST m] [SKIP N] | [DISTINCT | ALL ]

{<список-столбцов>}

FROM <спецификация-таблицы>

[WHERE <условие-поиска>]

[GROUP BY <элемент-группировки> [COLLATE последовательность-сортировки]

[, <элемент-группировки> [COLLATE последовательность-сортировки ]...]

[HAVING <условие-поиска>]

[UNION [ALL] <выражение-выбора>]

[PLAN <выражение-плана>]

[ORDER BY <список-сортируемых-элементов>]

[FOR UPDATE [OF столбец [,столбец ...]] [WITH LOCK]];

Временное пространство сортировки

Запросы с предложениями ORDRE BY и GROUP BY "паркуют" промежуточные наборы для дальнейших операций сортировки во временном хранилище. Рекомендуется иметь доступную память, приблизительно в 2.5 раза превышающую размер самой большой таблицы, которую вы будет сортировать. Версии Firebird 1.5 и выше могут конфигурировать память для сортировки в RAM; всем версиям нужно иметь временное дисковое пространство для использования в этих операциях.

Оперативная память для сортировки

В версии 1.5 и выше по умолчанию устанавливается размер блока памяти сортировки в 1 Мбайт. Это размер каждого участка памяти RAM, который выделяется сервером вплоть до максимального значения по умолчанию 64 Мбайт для Суперсервера и 8 Мбайт для Классического сервера. Оба эти значения могут быть сконфигурированы с помощью параметров конфигурации sortMemBiocksize и sortMemupperLimit, соответственно, в файле firebird.conf.

! ! !

ВНИМАНИЕ! Не слишком увеличивайте ресурсы памяти для кэша сортировки на машине с Классическим сервером. Поскольку Классический сервер порождает отдельный серверный процесс для каждого соединения, слишком большое значение верхней границы приведет к огромному размеру потребляемой памяти RAM в сильно загруженной системе.

. ! .

Место на диске для сортировки

Если на диске не выделена память для сортировки, то сервер будет сохранять файлы сортировки в каталоге /tmp файловой системы POSIX или в каталоге, указанном переменными окружения TMP и/или TEMP в Windows.

Вы можете явно сконфигурировать пространство для сортировки двумя способами. Первый заключается в установлении каталога с использованием переменной окружения FIREBIRD_TMP (INTERBASE_TMP для версии 1.0.x). Второй способ- сконфигурировать каталоги с использованием параметра конфигурации TempDirectories в firebird.config для версии 1.5 и выше или при добавлении одной или более записей temp directory в файл isc config (POSIX) или ibconfig (Windows) для версии 1.0.x.

Инсталляция по умолчанию не выполняет никакого явного конфигурирования размера памяти сортировки на диске. Подробности и синтаксис таких установок см. в разд. "Параметры для конфигурирования памяти сортировки" главы 36.

Индексирование

Упорядоченные наборы являются дорогостоящими для ресурсов сервера и вообще для производительности. При оценке запроса и определении плана оптимизатор может выбирать между тремя методами доступа к наборам данных (называемым потоками), которые располагаются в указанных таблицах: NATURAL (поиск без какого-либо порядка), INDEX (использование индекса для управления поиском) и MERGE (создание двоичных образов двух потоков и их слияние по принципу один к одному).

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

При методе MERGE (также называемый SORT) каждая строка (а следовательно, и каждая страница) читается только один раз, а чтение выполняется в порядке хранения. В Firebird 1.5, который с большой вероятностью будет использовать оперативную память для сортировки, метод MERGE является наиболее быстрым в большинстве случаев.

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

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

Предложение ORDER BY

Предложение ORDER BY используется для сортировки выходного набора запроса SELECT, оно также допустимо для любого оператора SELECT, который способен отыскивать множество строк для вывода. Оно помещается после всех других предложений, за исключением предложения FOR UPDATE (если присутствует) или предложения INTO (в хранимой процедуре).

Общая форма синтаксиса предложения ORDER BY:

. . .

ORDER BY <список-элементов-сортировки>

<список-элементов-сортировки> = <элемент-сортировки>

[, <элемент-сортировки>]

<элемент-сортировки> = <столбец> | <выражение> | <номер-позиции>

[COLLATE <порядок-сортировки>]

ASC[ENDING] | DESC[ENDING]

[NULLS LAST [ NULLS FIRST]

Элементы сортировки

Разделенный запятыми <список-элементов-сортировки> определяет вертикальный порядок сортировки строк. Порядок сортировки по каждому элементу сортировки может быть возрастающим (ASC) или убывающим (DESC). Возрастающий порядок является порядком по умолчанию и не обязательно должен быть указан.