Оптимизация Rails для Oracle - Планы выполнения и индексы
ОГЛАВЛЕНИЕ
Планы выполнения и индексы
Чего не может сделать Rails
Хотя Rails и прошла большой путь в направлении сокращения бремени разработки, благодаря умному использованию соглашений и реализации шаблона проекта, есть ситуации, где у нее не хватает знаний, необходимых для принятия решения разработчиком. Таким образом, Rails-разработчик не должен забывать, что он (или она) строит приложение, которое будет поддерживаться в принципе любой базой данных.
Хотя большая часть программного кода является выполнимой независимо от того, в среде какой именно базы данных этот код будет выполняться, но если вы хотите добиться от приложений наилучшей производительности, полезно иметь хорошее понимание того, как работает база данных.
Знание того, как работает база данных, полезно и при разработке модели данных и при построении запросов, но это знание намного более важно при принятии решения о том, какие индексы необходимы для таблиц. Это относится к числу тех решений, которые инфраструктура не может принять самостоятельно, и даже не пытается сделать этого, поскольку они могут зависеть от:
- того, какая именно база данных используется (поскольку в ней могут иметься или не иметься некоторые возможности, когда заходит речь о создании и использовании индексов)
- того, какие данные присутствуют в таблицах (поскольку они влияют на план выполнения запроса и на то, какие индексы используются)
- конкретного приложения (поскольку фактически используемые запросы могут получать доступ к данным непредсказуемыми способами, основанными на моделях использования)
Кроме того, эти факторы влияют друг на друга, и любая оптимизация будет зависеть всех от их всех вместе. В своем сообщении в блог "Indexing for DB Performance (Индексирование для обеспечения производительности DB") http://weblog.jamisbuck.org/2006/10/23/indexing-for-db-performance, Джеймис Бак (Jamis Buck) дает некоторые подсказки о типах необходимых индексов:
- внешние ключи (has_one (один к одному), belongs_to (принадлежит к), has_many (один ко многим) и др. отношения)
- неиндексированные поля, к которым часто делаются запросы (например, имена входящих в систему)
- столбцы, по которым часто выполняются сортировки или делаются вычисления
- наборы столбцов, которые совместно используются как условия
Ведение каждого индекса связано с накладными расходами, поскольку индекс необходимо обновляется при вставке, [удалении] и модификании данных, поэтому не надо строить индексы для каждого столбца (или их комбинаций). Скорее, вы должны сделать обоснованный выбор, начав с некоторого разумного набора индексов, изменяя его по мере развития приложения с течением времени и принимая во внимание фактические данные и модели их использования.
План выполнения. В контексте Oracle это означает использование команды explain plan, чтобы понять, как выполняются запросы. В результате применения этой команды у разработчика появляется способ непосредственно оптимизировать запрос и, в конечном счете, добавлять индексы.
Всегда нужно сначала попробовать оптимизировать запрос, и только после этого пытаться добавлять индексы.
В Rails значительно облегчено использование пользовательских (custom) запросов вместо типовых (regular). Это достигается, благодаря использованию удобного метода ActiveRecord, который называется find_by_sql. Отметьте, однако, что при вызове find_by_sql можно просто передать текст запроса SQL, но вы должны действительно подтвердить, что используется форма замены параметра, передавая вместе с запросом массив и список параметров. Таким способом можно использовать заключенные в кавычки параметры, которые находятся в адаптере базы данных, и быть увереным, что можно использовать реальные переменные связывания в будущем, когда ActiveRecord сможет поддерживать их, без необходимости в дальнейшем изменения кода.
Подключаемая программа Query_Analyzer. При поиске узких мест в приложении важно знать, какие запросы выполняются в данный момент. Здесь могут оказаться действительно полезными журналы Rails, поскольку приложение, которое выполняется в режиме разработки, регистрирует все фактические запросы. Можно собирать и исследовать их один за другим, чтобы выделить запросы и генерировать планы выполнения. Это может превратиться в утомительную работу. Подключаемый программный модуль query_analyzer помогает уменьшить нагрузку, помещая выходные данные оператора explain plan в журнальный файл. По этой ссылке http://www.spazidigitali.com/wp-content/uploads/2006/12/query_analyzer.zip можно получить совместимый с Oracle выпуск; он был первоначально разработан Бобом Силвой (Bob Silva) для MySQL. Для его установки достаточно разархивировать содержимое архива в каталог vendor/plugins вашего проекта Rails.
Чтобы позволить подключаемому программному модулю работать в среде Oracle, пользователю базы данных должно быть предоставлено достаточно много привилегий для доступа к таблицам словаря данных, к которым делаются запросы при загрузке модели. Это можно сделать с помощью следующего оператора:
GRANT SELECT ANY DICTIONARY TO < database user >;
Вспомогательный плагин (the plugin monkey) корректирует адаптер базы данных, чтобы добавить план выполнения для каждого запроса с уровнем регистрации ниже INFO, который выполняется адаптером. План форматируется и печатается на используемом по умолчанию устройстве при работе в отладочном режиме. Эта установка по умолчанию гарантирует, что построение плана не будет выполняться при выполнении приложения в промышленной среде.
Имеются два параметра для изменения поведения по умолчанию; оба они могут быть изменены в файлах установки окружения. Параметр plan_table_name может быть использован для указания имени таблицы плана, и его значение по умолчанию - это стандартное PLAN_TABLE; параметр plan_details может использоваться для указания того, какие именно детали должны быть напечатаны при выполнении оператора explain plan. Возможные значения:
- BASIC: выводится минимум информации
- TYPICAL: отображается самая релевантная информация (значение по умолчанию)
- SERIAL: похож на TYPICAL, но без параллельной информации
- ALL: выводится вся информация
Если выполнить общий запрос:
Employee.find(101)
в файл development.log будут выгружены следующие данные:
Employee Load (0.000000) explain plan for SELECT * FROM employees WHERE (employees.id = 101)
Analyzing Employee Load
plan_table_output
-------------------------------------------------------------------------------------------
Plan hash value: 1171009080
-------------------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
-------------------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1 / 719 / 2 (0)/ 00:00:01 /
/ 1 / TABLE ACCESS BY INDEX ROWID/ EMPLOYEES / 1 / 719 / 2 (0)/ 00:00:01 /
/* 2 / INDEX UNIQUE SCAN / SYS_C004026 / 1 / / 1 (0)/ 00:00:01 /
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEES"."ID"=101)
Отметим, что в запросе используется уникальный индекс, связанный с первичным ключом таблицы; в этом случае не требуется делать ничего.
Если оставить плагин активным (установить уровень отладки на informations), это позволит при выполнении приложения собрать данные всех запросов в таблице плана.
Таблица плана выводит много информации о запросах. Вы можете быстро определить таблицы, которым, возможно, требуется новый индекс, если отыщете в журнальном файле строку 'TABLE ACCESS FULL".
Employee Load (0.010000) explain plan for SELECT * FROM employees WHERE (first_name = 'Stephen')
Analyzing Employee Load
plan_table_output
-------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
-------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1 / 719 / 3 (0)/ 00:00:01 /
/* 1 / TABLE ACCESS FULL/ EMPLOYEES / 1 / 719 / 3 (0)/ 00:00:01 /
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FIRST_NAME"='Stephen')
Note
-----
- dynamic sampling used for this statement
Здесь показан результат выполнения запроса, отыскивающего всех служащих, имеющих имя 'Stephen': Employee.find (:all,:conditions => ["first_name =?", "Stephen"]).
Отметим, что этот запрос должен просмотреть всю таблицу служащих (EMPLOYEES), чтобы получить только одну строку, используя фильтр по столбцу first_name. Если при выполнении приложения этот вид запроса встречается много раз, нужно рассмотреть вопрос о добавлении индекса. Информация предиката о более дорогостоящих операциях дает хорошую подсказку, какие индексы могут принести пользу для выполнения запроса - в рассматриваемом случае, очевидно, это мог бы быть индекс по столбцу first_name.
Rails Migrations. Вы можете также использовать Rails Migrations для управления индексами. (См. документацию по API для получения дополнительной информации о том, что такое Migrations, и как их использовать для управления схемой базы данных.), Чтобы добавить индекс, выполните:
add_index table_name, column_names, options = {} При этом к таблице "table_name" будет добавлен индекс для столбца "column_names". В этом предложении можно задать как отдельный столбец, так и их список:
add_index :departments, :manager_id add_index :locations, [:city, :postal_code]
В опциях параметра можно указать, должен ли быть индекс уникальным:
add_index :locations, [:city, :postal_code, :street_address], :unique => true
и/или с заданным именем. (Это полезно в среде Oracle, так как в имени индекса по умолчанию должна быть использована комбинация имени таблицы и имени первого столбца, которая может оказаться слишком длинной.)
add_index :locations, [:city, :postal_code], :name => :idx_city_zip
Для удаления индекса просто используйте метод remove_index:
remove_index :departments, :manager_id
remove_index :departments, :column => :manager_id
remove_index :locations, :column => [:city, :postal_code, :street_address]
remove_index :locations, :name => :idx_city_zip
Если нужно использовать более сложный SQL-оператор или SQL-оператор, определенный для конкретной базы данных, используйте команду execute из метода более высокого или низкого уровня, в которые вы передаете на выполнение строку SQL:
execute "CREATE INDEX idx_emp_first_name ON employees (first_name)"
В приведенном выше примере:
C:\Progetti\ArticoliROR\Oracle\project>ruby script/generate migration AddFirstNameIndex
exists db/migrate
create db/migrate/008_add_first_name_index.rb
Это - файл миграции:
class AddFirstNameIndex < ActiveRecord::Migration
def self.up
add_index :employees, :first_name, :name=>:idx_emp_first_name
end
def self.down
remove_index :employees, :name=>:idx_emp_first_names
end
end
Выполните миграцию, используя утилиту rake db:migrate.
C:\Progetti\ArticoliROR\Oracle\project>rake db:migrate
(in C:/Progetti/ArticoliROR/Oracle/project)
== AddFirstNameIndex: migrating ===============================================
-- add_index(:employees, :first_name, {:name=>:idx_emp_first_name})
-> 0.0100s
== AddFirstNameIndex: migrated (0.0100s) ======================================
Повторное выполнение запроса показывает, что этот индекс теперь используется:
Employee Load (0.010000) explain plan for SELECT * FROM employees WHERE (first_name = 'Stephen')
Analyzing Employee Load
plan_table_output
--------------------------------------------------------------------------------------------------
Plan hash value: 2736374945
--------------------------------------------------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
--------------------------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 1 / 719 / 2 (0)/ 00:00:01 /
/ 1 / TABLE ACCESS BY INDEX ROWID/ EMPLOYEES / 1 / 719 / 2 (0)/ 00:00:01 /
/* 2 / INDEX RANGE SCAN / IDX_EMP_FIRST_NAME / 1 / / 1 (0)/ 00:00:01 /
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FIRST_NAME"='Stephen')
Note
-----
- dynamic sampling used for this statement
Как вы видели, действительно, удобно использовать подключаемый программный модуль, чтобы можно было "засунуть свой нос" в планы выполнения, используемые выполняющейся прикладной программой, и вы можете даже оставить изменения, инсталлированными на промышленном сервере, и включать/отключать их в случае необходимости, просто изменяя уровень регистрации.
Заключение
В этой статье вы познакомились с тем, как в приложении Rails конфигурируются подключения к базе данных Oracle. Было показано, как в выпуске 1.2 для обеспечения большей эффективности была обновлена структура, в которой теперь используются параметры cursor_sharing и prefetch_rows (до тех пор, пока не появится ожидаемая настоящая реализация переменных связывания).
Кроме того, были исследованы команды миграции Rails, которые релевантны для настройки базы данных (особенно, команды создания/удаления индексов).
И, наконец, в показанр, что для получения высокой эффективности необходимо понимать, как Rails из написанного кода строит SQL, как база данных затем выполняет эти операторы.