Сегодня я хотел бы поговорить о том, как реализовать материализованные представления в PostgreSQL, используя их в приложении Ruby on Rails.
PostgreSQL , известный как Postgres, является многоплатформенной системой баз данных SQL с открытым исходным кодом. Это одна из самых популярных систем баз данных и вторая в мире база данных с открытым исходным кодом в мире. Разработка для Postgres осуществляется в рамках группы глобального развития PostgreSQL. Если вы никогда не использовали Postgres, я бы порекомендовал попробовать его в вашем следующем проекте, это действительно круто.
Учебник разделен на три части следующим образом:
- Что такое просмотр базы данных
- Что такое материализованное представление
- Как добавить материализованное представление в приложение Rails.
Что такое просмотр базы данных?
Представление — это в основном хранимый набор запросов к базовым таблицам. Всякий раз, когда вызывается представление, оно выполняет серию предварительно сохраненных запросов к базовым данным, возвращая результирующие данные. В отличие от таблицы, она не требует физического пространства, но вместо этого схема представления сохраняется и доступна из памяти.
Когда базовые данные, на которых построено представление, изменяются, они отражаются в последующих вызовах представления.
Некоторые из преимуществ, предоставляемых представлением:
1. Абстракция данных — представление может использоваться для абстрагирования набора данных из базовых таблиц.
2. Оптимизация — представление, как и любой запрос, проходит через оптимизатор, который максимально улучшает его, основываясь на операции, выполняемой с результатом представления.
3. Простота. Как уже упоминалось выше, представление может абстрагировать данные, одновременно упрощая запросы к нескольким таблицам. Представление часто состоит из одного или нескольких объединений между таблицами, поэтому данные этих объединений выглядят как простая таблица.
Почти все основные поставщики баз данных имеют функцию просмотра, и это единственный способ запрашивать определенные базы данных NoSQL.
Что такое материализованное представление?
Материализованное представление, также называемое «matview», — это форма представления базы данных, впервые представленная в Oracle. С тех пор он был адаптирован многими основными базами данных.
Из Википедии,
Материализованное представление — это объект базы данных, который содержит результаты запроса.
По существу, материализованное представление похоже на представление базы данных, за исключением того, что оно физически хранится на диске и обновляется вручную. Matview хранит результаты запроса в собственной табличной структуре, из которой можно запрашивать данные. Невозможно добавлять или удалять строки, но в остальное время он ведет себя как настоящая таблица.
Кроме того, когда данные за представлением материализации изменяются, matview необходимо вручную обновить, прежде чем новые данные будут включены в сохраненный matview. Это как положительный, так и отрицательный. Хранение результатов запроса, который делает материализованное представление, позволяет ему работать как таблица. Запросы выполняются быстрее, и сам matview может быть проиндексирован. Однако, поскольку данные, которые содержат результаты, в основном являются снимками, они могут быть устаревшими, и вам необходимо знать, когда обновлять данные.
На момент написания этой статьи matviews изначально были доступны в Oracle DB , PostgreSQL , Sybase , IBM DB2 и Microsoft SQL Server . К сожалению, MySQL не обеспечивает встроенной поддержки matviews, но есть альтернативы с открытым исходным кодом.
Matviews в PostgreSQL
Материализованные представления были введены в Postgres версии 9.3. В версии 9.4 была введена опция одновременного обновления matview (то есть без блокировки представления).
Синтаксис создания matview в Postgres аналогичен созданию таблицы:
CREATE MATERIALIZED VIEW ex_matview AS SELECT col1, col2 FROM mytable WHERE col3 = condition; Добавление материализованного представления в приложение Rails
Для нашего простого примера я создал приложение Rails и сгенерировал 1M строк в базе данных, используемой приложением. Код доступен на Github . В приложении есть три таблицы: channel , item и sales которые имитируют данные о продажах для продавца. Схема для примера приложения:
Допустим, у нас есть страница панели инструментов, которая предоставляет информацию о продажах за прошедший год, а также следующие данные:
1) Объем продаж, совершенных каналом для каждого элемента, мы можем запросить его следующим образом:
Sale.joins(:channel).joins(:item).where('channels.name = ?', 'web').group('items.name').sum(:amount)
2) Объем продаж, совершенных каждым каналом в целом:
Sale.joins(:channel).group('channels.name').sum(:amount)
3) Количество продаж в день:
Sale.group("DATE_TRUNC('day', sold_date)").sum(:amount)
Эти запросы не сложны, но они дороги, даже после многих оптимизаций. Кроме того, данные, вероятно, будут расти все больше и больше. Это хороший кандидат для материализованного представления, так как данные не должны быть в реальном времени, и мы абстрагируем только часть данных.
Для начала давайте создадим миграцию:
rails g migration CreateSalesMatview
Добавьте следующие строки в файл миграции:
| class CreateSalesMatview < ActiveRecord::Migration | |
| def change | |
| execute <<-SQL | |
| CREATE MATERIALIZED VIEW sales_matview AS | |
| SELECT DATE_TRUNC(‘day’, sold_date) AS date, | |
| i.name AS item, | |
| SUM(amount) AS amount, | |
| SUM(quantity) AS quantity, | |
| c.name AS channel | |
| FROM sales s | |
| INNER JOIN items i ON i.id = s.item_id | |
| INNER JOIN channels c ON c.id = s.channel_id | |
| GROUP BY DATE_TRUNC(‘day’, sold_date), item, channel | |
| SQL | |
| end | |
| end |
by Я просто собираю необходимые данные, которые необходимы для отчетов.
После сохранения файла запустите bin/rake db:migrate и вуаля! Matview создан. Кстати, изменения для этой миграции не будут доступны в файле схемы, поскольку это не таблица.
Создание модели
Одна удивительная вещь, используя материализованное представление с Rails / ActiveRecord, заключается в том, что мы можем создавать модели для него, как и любую другую таблицу в схеме. Модель в нашем случае будет в файле с именем sales_matview.rb . Таким образом, модель называется SalesMatview . Вы можете использовать любое имя, которое хотите, но рекомендуется следовать соглашениям об именах Rails.
Давайте создадим файл app / models / sales_matview.rb и добавим в него следующие строки:
| class SalesMatview < ActiveRecord::Base | |
| self.table_name = ‘sales_matview’ | |
| def readonly? | |
| true | |
| end | |
| def self.refresh | |
| ActiveRecord::Base.connection.execute(‘REFRESH MATERIALIZED VIEW sales_matview’) | |
| end | |
| end |
Вы заметите, что модель привязана к основному matview, используя self.table_name . Также мы создали два метода:
-
readonly?дляreadonly?который блокирует модель от записи данных, это таблица только для чтения. -
self.refreshкоторый будет вызываться для обновления материализованного представления при необходимости.
Мы готовы использовать только что созданное материализованное представление. Вы можете запросить его точно так же, как в обычном запросе ActiveRecord.
Теперь давайте посмотрим на наши предыдущие запросы:
-
Объем продаж, совершенных каналом в расчете на единицу товара:
SalesMatview.where('channel = ?', 'web').group(:item).sum(:amount) -
Сумма продаж каждого канала в целом:
SalesMatview.group(:channel).sum(:amount) -
Количество продаж в день:
SalesMatview.group(:date).sum(:amount)
Запросы похожи, за исключением тех объединений, но вы заметите большую разницу в том, как они выполняются:
Channel per item user system total real AR (251.5ms) 0.040000 0.070000 0.110000 ( 0.412950) MV (2.3ms) 0.000000 0.010000 0.010000 ( 0.012010) Channel overall user system total real AR (374.4ms) 0.000000 0.000000 0.000000 ( 0.376352) MV (3.3ms) 0.000000 0.000000 0.000000 ( 0.006813) Sales per day user system total real AR (976.4ms) 0.020000 0.000000 0.020000 ( 0.990258) MV (6.2ms) 0.020000 0.000000 0.020000 ( 0.026783)
Это ОГРОМНАЯ экономия, если не сказать больше! Более 100 раз улучшено! Попробуйте и убедитесь сами.
Обновление материализованных представлений
Теперь нам нужно заняться обновлением материализованного представления. Мы можем создать задачу rake, чтобы обновить matview и регулярно обновлять данные. Добавьте строку ниже в файл lib / tasks / sales.rb, и все готово.
desc 'Update the Sales materialized view' task :update_sales_view => :environment do SalesMatview.refresh end
Для больших наборов данных, где обновления могут занимать слишком много времени, в Postgres есть возможность выполнять обновление одновременно. Просто измените оператор в методе refresh на:
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_matview
Имейте в виду, что для одновременного обновления необходимо использовать уникальный ключ в matview.
Кроме того, поскольку материализованное представление доступно только для чтения, если вы хотите изменить схему, на которой оно построено, вы должны отбросить matview и воссоздать его. Просто добавьте следующую строку в вашу миграцию, прежде чем все остальное, чтобы отбросить matview:
DROP MATERIALIZED VIEW IF EXISTS sales_matview
Вывод
Весь код, который используется в примерах, доступен здесь — Github . Он также имеет начальный файл для генерации 1 миллиона строк для упомянутой схемы.
Я надеюсь, что это было полезно и информативно для вас, и я благодарю вас за чтение. Дайте мне знать ваши предложения в комментариях.
Кредиты для:
http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html
http://en.wikipedia.org/wiki/Materialized_view