Экран-реестр

Материал из Eludia
Перейти к: навигация, поиск

Содержание

Реестр (журнал, лог) — это экран, для которого content-часть содержит SQL-запрос с выражением LIMIT, а presentation — таблицу с элементом pager по соответствующей выборке. Как правило, открываемые по умолчанию экраны являются именно реестрами, поэтому обращения к ним весьма часты, так что здесь следует уделять оптимизации особое внимание.

Использование индексов

Существует 2 принципиально разных варианта исполнения запроса с ORDER BY ... LIMIT:

буферный
сервер СУБД сначала достаёт полную выборку во временную таблицу (если памяти хватает, то в памяти, иначе — на диске), потом полностью сортирует её, после чего выдаёт на клиент запрошенный фрагмент.
индексный
сервер сразу проходит по требуемому интервалу значений в индексе, для каждого найденного элемента достаёт связанную запись из основной таблицы и дополнительных справочников и добавляет полученную запись к результату.

Оба варианта дают правильный результат, но очевидно, что с ростом объёма корневой таблицы буферный режим приводит к огромному и бессмысленному перерасходу вычислительных ресурсов (и, соответственно, заметному замедлению работы). Тем не менее, по умолчанию используется именно он.

Чтобы воспользоваться индексом, его необходимо правильно спроектировать. При этом следует учесть, что один и тот же ключ одновременно должен использоваться для фильтрации (WHERE) и сортировки (ORDER BY). Если поля фильтрации и сортировки совпадают, вам очень повезло, однако так не бывает (поле fake: фильтр — всегда, сортировка — никогда). Впрочем, и без этого больших проблем обычно не возникает.

Рассмотрим типичнейший случай: WHERE fake = 0 ORDER BY label. В этой ситуации подойдёт ключ по (fake, label). Вообще если у вас есть несколько фильтров типа 'равенство', то ключ по последовательности этих полей и полей сортировки будет использоваться в плане запроса. На случай разных порядков сортировки никто не мешает завести побольше дополнительных индексов.

Органичения

Однако стоит отметить, что (по крайней мере) для MySQL упорядочение по всем полям должно быть однонаправленным (все ASC или все DESC), причём (упакованный, по умолчанию) ключ по VARCHAR-полю не будет работать в случае DESC-сортировки.

Функции от полей

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

Но если ваша СУБД поддерживает индексы по выражениям, тем не менее стоит при малейшей возможности избегать использования функций в условиях фильтрации. Особенно часто соблазн отфильтровать выборку не по полю, а по выражению возникает при работе с датами. Типичный случай: ограничение типа равенства на год

YEAR(dt) = 2008

Даже если вы можете построить индекс по YEAR(dt) (хотя наверняка забудете это сделать), условие

dt BETWEEN '2008-01-01 00:00:00' AND '2008-12-31 23:59:59'

будет отработано не менее эффективно. При этом вы получите более переносимый код, а индекс по dt, вероятно, ещё пригодится.

Чужие поля

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

Использование полей разных таблиц в фильтрах одного запроса — это намёк на то, что вы не совсем правы с его составлением.

Ограничения на поля таблиц, подключаемых через JOIN к корневой таблице запроса, как правило, приводятся к ограничениям типа "равенство" или "множество" (IN (...)) на поля-ссылки одной таблицы.

Если JOIN не простой, а каскадный (организация -> город -> область -> страна), имеет смысл задублировать ссылку на "далёкий" справочник в корневой таблице (организация -> страна). Да, это приводит к избыточности данных и необходимости дополнительно поддерживать целостность, но такие затраты окупаются эффективностью фильтрации.

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

Дальнейшее развитие этой идеи быстро приводит к материализованным view. Это снимает практически все вопросы с оптимизацией выборок, однако несколько усложняет поддержку БД, если она вообще подразумевает такую функцию.

UNION

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

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

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

Подзапросы в SQL

рассмотрены отдельно.

Недостатки sql_select_all_cnt

Функция sql_select_all_cnt входит в стандартный шаблон экрана-реестра, использовать её удобно, однако с ростом количества записей за этот комфорт приходтся платить всё большей потерей эффективности. Детали зависят от выбора СУБД, однако в любом случае, по крайней мере на стороне сервера, полная выборка проходится от начала до конца.

Чем это плохо? Тем, что для каждой записи, не попадающей в LIMIT, выполняются все JOIN-операции и (если таковые имеются) вычисления из раздела SELECT. Даже если вы идеально настроили индекс и можете получить свою выборку за один RANGE SCAN, всё равно вам приходится терпеть проход всей "слепой зоны" с доставанием как минимум записи основной таблицы, а обычно ещё 3-4 справочников лишь затем, чтобы посчитать пройденные шаги. Обидно. Но узнать-то полное количество записей надо: иначе pager не отрисовать.

В такой ситуации остаётся только одно: заменить sql_select_all_cnt на sql_select_all и вычислить $cnt отдельным запросом вида SELECT COUNT(*) FROM ... WHERE .... Во-первых, тут нет выражения ORDER BY, а значит, большой буфер не грозит нам даже теоретически. Во-вторых, если вы привели все фильтры к условиям на одну таблицу, то JOIN'ы тоже исчезают. И, наконец, в таких обстоятельствах у вас появляется шанс узнать требуемое число, пройдясь по одному индексу и не заглядывая в таблицу.

Тут, конечно, возникает необходимость синхронизации фильтров в обоих запросах, но, если вы имеете привычку накапливать условия в переменной $filter, то особой проблемы это не представляет.

Хуже то, что в промежутке между 2 запросами (на выборку и на количество) содержимое БД может измениться, так что вы получите, вообще говоря, рассогласованные данные. Блокировать БД на запись или пойти на риск с открытыми глазами, особенно в контексте того, что по ходу отрисовки страницы в данных может поменяться ещё что-то — дело уже ваше.

GROUP BY и множественные справочники

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

Самое худшее, что тут можно сделать — это достать выборку, после чего запустить по ней цикл и вычислять требуемое для каждой строки по отдельности. При стандартном объёме в 50 строк это порождает миниму 50 дополнительных запросов к БД, что физически не может быть приемлемо по скорости, даже если каждый запрос оптимален.

Другой популярный, но ошибочный ход состоит в комбинировании конструкций GROUP BY и LIMIT. Формально это смотрится красиво: запрос всего один. Но беда в том, что всё, сказанное выше об избежании буферизации, становится неприменимым. GROUP BY — это практически гарантированная временная таблица и промежуточная сортировка полной выборки.

К тому же бывает так, что правильный запрос с GROUP BY построить невозможно в принципе. Пример: список договоров с суммами по актам (одна таблица многие-ко-многим) и платежам (другая аналогичная таблица). Если к одному договору относится 2 акта и 2 платежа, то параллельный LEFT JOIN даст 4 записи (декартово произведение) и после группировки получатся удвоенные суммы.

Эффективный путь решения задачи заключается в том, чтобы сначала извлечь выборку со скалярными полями, а потом навесить на её записи статистику дополнительными запросами: почти как в первом, наихудшем, варианте, только обрабатывать записи не по одной, а все разом.

Для этого часто бывает удобно воспользоваться функцией ids: для заданной выборки она формирует одновременно список id и индекс (хэш: id-запись). Дальше полученный список можно подставить в статистический запрос, результаты которого разместить в исходной выборке, воспользовавшись индексом.

$item -> {records} = sql_select_all ('SELECT * FROM ...');

my ($ids, $idx) = ids ($item -> {records});

sql_select_loop (
	"SELECT ... AS sum WHERE id_... IN ($ids) GROUP BY ...",
	sub {$idx -> {$i -> {id}} -> {sum} = $i -> {sum}},
);

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

Вторичные запросы не обязательно групповые, содержимое поля $idx -> {$i -> {id}} -> {...} можно накапливать за несколько шагов. Характерный пример: формирование списка строк через запятую в отсутствие групповой функции GROUP_CONCAT. Возможны и более сложные случаи, когда сначала накапливается промежуточная структура данных, а потом (за отдельный проход) по ней вычисляется скалярное значение, отображаемое в клетке таблицы.

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

Oracle

Допустим, вы работаете с БД Oracle (для определённости, 10g). У Вас есть таблица (скажем, users) с VARCHAR2-полем label, достаточно большая, чтобы не рассчитывать на её кэширование в памяти и сделать неприемлемо долгой операцию SORT. Например, 25 000 записей.

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

Чтобы не перепахивать каждый раз всю таблицу (TABLE ACCESS FULL), вы аккуратно строите индекс по полю label, делаете ANALYZE TABLE COMPUTE STATISTICS, рассчитывая на RANGE SCAN, и... чёрта с два получаете. Oracle упорно извлекает всю таблицу в буфер и сортирует её там многие сотни миллисекунд. Чтобы использовать индекс, нужно сделать ещё много чего.

Без фильтра — никак

В это трудно поверить (поправьте меня контрпримером), но Oracle в принципе отказывается использовать в плане RANGE SCAN, если в запросе нет фильтра по полям индекса. В смысле: просишь его

SELECT * FROM users ORDER BY label

и, вроде бы, что может быть очевиднее, чем пройтись по индексу, который построен именно на label? Ан нет. Не будет такого: получите TABLE ACCESS FULL, а потом SORT. Можно придумать обоснование типа того, что помимо чёса по индексу есть ещё и доступ к таблице и он стоит ненулевых ресурсов. Однако в тех случаях, когда при помощи различных фокусов удаётся добиваться RANGE SCAN с заведомо эквивалентным результатом, скорость реально возрастает в десятки раз. Логика "опитмизатора" запросов неисповедима.

В некоторых экспериментах помогал бессмысленный фильтр

label LIKE '%'

хотя в ряде случаев этого оказалось недостаточно. Фильтр

label >= ' '

оптимизатор уже не игнорирует, но он несколько наушает семантику запроса: строки, для которых label IS NULL (в Oracle таковыми считаются и те, для которых ставилось label = ""), пропадают из поля зрения.

Однако давайте вспомним: ведь в наших реестрах практически повсеместно используется фильтр по полю fake. Этим надо воспользоваться. Для фильтрации по fake и сортировки по label построим индекс по (fake, label). Тогда в запросе

WHERE
 fake = 0
ORDER BY
 label

такой индекс будет использован.

NULLS LAST и всё тут

Опция NULLS FIRST в ORDER BY отрубает RANGE SCAN напрочь и без вариантов. Это надо знать. По-видимому, B-tree в Oracle строятся так, что NULL всегда попадает в конец: никакими настройками CREATE INDEX это не перешибается. К сожалению, у нас в процедуре mysql_to_oracle исторически сложилось так, что при ORDER ... ASC (то есть по умолчанию) выставлялось NULLS FIRST. Оно как бы логично: раз уж пустые строки приравнены к NULL, по алфавиту они меньше всех. Но что ж тут поделаешь... В общем, я добавил опцию $conf -> {db_nulls_last} и сильно всем советую выставлять её в 1, если вы только пользуетесь mysql_to_oracle.

NLS_SORT = BINARY

Теперь — немного мистики. Попробуйте как-нибудь на досуге отдать команду

ALTER SESSION SET NLS_SORT = RUSSIAN

И после этого посмотреть на план запроса

SELECT * FROM users WHERE label BETWEEN 'А' AND 'ЯЯЯ' ORDER BY label

Первым ходом будет RANGE SCAN, однако на самом верху окажется... SORT! Конечно, если почитать учебник, то так оно как бы и должно быть. То есть, чтобы ORDER BY хоть когда-нибудь использовал индексы для сортировки, нужно говорить

ALTER SESSION SET NLS_SORT = BINARY

Да, коллеги, несмотря на уважение к многим национальным культурам, заложенное в комплект поставки СУБД, отслеживать соответствие буковок и чиселок надо именно вам. Конечно, если вас сколько-нибудь интересует производительность.

С этим можно смириться... Но одного я напрочь не понимаю: если уж оптимизатор не считает порядок индекса алфавитным для SORT — как он тогда может использовать его для SCAN? Как в Oracle предполагалось поступать с буквой "Ё" для CLMSWIN1251 и NLS_SORT = RUSSIAN: поставить между "Е" и "Ж", но предварительно отфильтровать, так, чтобы её не было в выборке?

Что бы там ни имелось в виду, для себя надо сделать 3 вывода:

  • менять все Ё на Е от греха подальше;
  • для русского языка использовать cp1251 или на крайняк UTF-8;
  • NLS_SORT = BINARY.

FIRST_ROWS

Все перечисленные выше условия — необходимы для использования RANGE SCAN, однако их список всё ещё неполон. В нём не хватает оптимизационного псевдокомментария /*+FIRST_ROWS*/. Теоретически он предусмотрен как бы для перекоса оптимизации в одну конкретную сторону: скорейшего получения первых строк, возможно, ценой замедления запроса в целом. Анекдот состоит в том, что для нашего случая без хинта FIRST_ROWS в план (хоть убейся, всё равно) вместо RANGE SCAN используется TABLE ACCESS FULL + SORT, который весьма ощутимо задерживает получение не только первой, но и всех остальных записей.

Хинт FIRST_ROWS автоматически включается в функции sql для диалекта Oracle. Однако в sql_select_all_cnt никакой такой специфической автоматики не предусмотрено.

Ещё о sql_select_all_cnt

Если предыдущие пункты в основном актуальны практически для любых приложений, использующих Oracle (аналог статусного поля fake используют многие, называют все по-разному), то здесь речь о специфике Eludia.pm.

Итак, функция sql_select_all_cnt. В версии для Oracle полное количество записей давно вычислялось отдельным запросом, который получается из исходного заменой списка полей на COUNT(*) и отбрасыванием выражения ORDER BY.

При этом, если в запрос входят LEFT JOIN по однозначным ссылкам на справочники, то в плане с COUNT(*) образуются бессмысленные, однако стоящие ресурсов NESTED LOOPS.

Так вот, функция sql формирует собственный COUNT-запрос, и те JOIN'ы, которые не могут повлиять на количество записей (то есть LEFT JOIN'ы по ссылкам на id) в него не входят изначально. К сожалению, реализовать такое на уровне sql_select_all_cnt представляется слишком сложным.

Итак, краткая сводка

  • Старайтесь никогда не употреблять sql_select_all_cnt в новых приложениях: либо используйте sql, либо работайте на уровне сырого DBI.
  • При построении индексов с прицелом на ORDER BY указывайте fake первым в списке полей.
  • Ставьте $conf -> {db_nulls_last}. Если это ломает логику — правьте приложение так, чтобы вместо NULL или пустой строки писалась, например, строка из единственного пробела.
  • Берегитесь букв "Ё" и "ё" (пока автоматики не предусмотрено).
  • Знайте, что NLS_SORT = BINARY (ядро позаботится).
Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Разработчику
Администратору
Инструменты