Интеграция PostgreSQL с другими СУБД: делаем запросы в MySQL
Нередко бывает так, что в большом проекте в силу тех или иных причин — зачастую исторических, хотя бывает по-всякому — его части могут использовать различные СУБД для хранения и поиска критически важных данных. В числе прочего, этому разнообразию способствует конкуренция и развитие технологий, но, так или иначе, взаимодействие между СУБД описывает стандарт SQL/MED 2003 (Management of External Data), который вводит определение Foreign Data Wrappers (FDW) и Datalink.
Первая часть стандарта предлагает средства для чтения данных как набора реляционных таблиц под управлением одного или нескольких внешних источников; FDW также может представлять возможность использовать SQL-интерфейс для доступа к не SQL данным, таким, как файлы или, например, список писем в почтовом ящике.
Вторая часть, Datalink, позволяет управлять удаленным SQL-сервером.
Эти две части были реализованы еще в PostgreSQL 9.1 и называются FDW и dblink соответственно. FDW в PostgreSQL сделан максимально гибко, что позволяет разрабатывать wrapper’ы для большого количества внешних источников. В настоящее время мне известны такие FDW, как PostgreSQL, Oracle, SQL Server, MySQL, Cassandra, Redis, RethinkDB, Ldap, а также FDW к файлам типа CSV, JSON, XML и т.п.
В нашей статье мы поговорим о том, как настроить подключение PostgreSQL к MySQL и эффективно выполнять получающиеся запросы.
Для начала собираем и устанавливаем mysql_fdw:
git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd mysql_fdw
# во всех rhel-like дистрибутивов pg_config не попадает в PATH, он лежит в /usr/pgsql-9.5/bin:
PATH=$PATH:/usr/pgsql-9.5/bin USE_PGXS=1 make installУстаналиваем extension на базу, чтобы загрузились необходимые библиотеки:
CREATE EXTENSION mysql_fdw ;Создаем сервер:
CREATE SERVER mysql_server_data FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');И mapping текущего пользователя в PostgreSQL в пользователя MySQL:
CREATE USER MAPPING FOR user SERVER mysql_server_data
OPTIONS (username 'data', password 'datapass');После этого мы имеем возможность подключить таблицу MySQL в PostgreSQL:
CREATE FOREIGN TABLE
orders_2014 (
id int,
customer_id int,
order_date timestamp)
SERVER mysql_server_data
OPTIONS (dbname 'data', table_name 'orders');Допустим, мы храним справочник customers в PostgreSQL:
CREATE TABLE customers (id serial, name text);Попробуем выбрать 5 самых активных покупателей в январе 2014 года:
explain (analyze,verbose)
select
count(o2014.id),
c.name
from orders_2014 o2014
inner join customers c on c.id = o2014.customer_id
where
extract('month' from o2014.order_date) = 1 and
extract('year' from o2014.order_date) = 2014
group by 2 order by 1 desc limit 5; Limit (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.276..0.276 rows=5 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.275..0.275 rows=5 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.270..0.271 rows=5 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.255..0.264 rows=8 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.240..0.241 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.065..0.233 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Filter: ((date_part('month'::text, o2014.order_date) = '1'::double precision) AND (date_part('year'::text, o2014.order_date) = '2014'::double precision))
Rows Removed by Filter: 58
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id`, `order_date` FROM `data`.`orders`
-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=0.011..0.011 rows=9 loops=1)
Output: c.name, c.id
Sort Key: c.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.005 rows=12 loops=1)
Output: c.name, c.idКак мы видим, запрос неэффективный, так как со стороны MySQL было получено содержимой всей таблицы: SELECT id, customer_id, order_date FROM data.orders. Сервер, в силу естественных ограничений драйвера MySQL, не в состоянии трансформировать запрос таким образом, чтобы для получения корректного результата этот запрос было бы возможно выполнить на стороне MySQL, и поэтому сначала получает таблицу целиком, а потом уже осуществляет фильтрацию. Однако при изменении запроса можно добиться того, чтобы фильтрация по дате осуществлялась на стороне MySQL:
explain (analyze,verbose)
select
count(o2014.id),
c.name
from orders_2014 o2014
inner join customers c on c.id = o2014.customer_id
where
o2014.order_date between ('2014-01-01') and ('2014-02-01'::timestamptz - '1 sec'::interval)
group by 2 order by 1 desc limit 5;Сравнение order_date с ('2014-02-01'::timestamp - '1 sec'::interval) неправильно, так как timestamptz хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:
Limit (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.130..0.130 rows=0 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.129..0.129 rows=0 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.114..0.114 rows=0 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.111..0.111 rows=0 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.110..0.110 rows=0 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.093..0.093 rows=0 loops=1)
Output: o2014.id, o2014.customer_id
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - '00:00:01')))
-> Sort (cost=88.17..91.35 rows=1270 width=36) (never executed)
Output: c.name, c.id
Sort Key: c.id
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (never executed)
Output: c.name, c.idТут нас поджидает проблема, из-за которой стоит использовать mysql_fdw с большой осторожностью:
SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - '00:00:01')))Как мы видим, between, представляя из себя синтаксический сахар, был развернут в два условия, одно из которых не вычислено на стороне PostgreSQL: ('2014-02-01'::timestamp - '1 sec'::interval) и преобразовано в разницу двух строк (а не даты и интервала):
mysql> select '2014-01-02 00:00:00+00' - '00:00:01';
+---------------------------------------+
| '2014-01-02 00:00:00+00' - '00:00:01' |
+---------------------------------------+
| 2014 |
+---------------------------------------+
1 row in set, 2 warnings (0.00 sec)В итоге запрос возвращает неправильный результат.
С подобной проблемой столкнулся один из наших клиентов. Проблема была исправлена в форке PostgresPro, https://github.com/postgrespro/mysql_fdw и создан pull-реквест в основной репозиторий EnterpriseDB. Устанавливаем исправленную версию:
git clone https://github.com/postgrespro/mysql_fdw.git mysql_fdw_pgpro
cd mysql_fdw_pgpro
PATH=$PATH:/usr/pgsql-9.5/bin USE_PGXS=1 make installТеперь план запроса выглядит так:
Limit (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.219..0.219 rows=5 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.218..0.218 rows=5 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.199..0.201 rows=5 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.183..0.185 rows=8 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.151..0.151 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.116..0.120 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - INTERVAL 1 SECOND)))
-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=0.030..0.030 rows=9 loops=1)
Output: c.name, c.id
Sort Key: c.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (actual time=0.018..0.020 rows=12 loops=1)
Output: c.name, c.idЗапрос стал быстрее по сравнению с первым, так как с MySQL мы возвращаем значение более точечного запроса:
SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - INTERVAL 1 SECOND)))Операция фильтрации выполняется теперь на стороне MySQL. При определенных условиях будет использоваться индекс по order_date, если он создан.
Таким образом мы ускорили выполнение запроса. На простом примере мы почуствовали силу Open Source и мощь PostgreSQL в расширяемости.
Спасибо за внимание!
Подробнее про SQL-MED можно прочитать тут: http://cs.unibo.it/~montesi/CBD/Articoli/SQL-MED.pdf
Скачать исправленную версию mysql_fdw можно отсюда: https://github.com/PostgreSQLpro/mysql_fdw
