Интеграция 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;

план PostgreSQL

 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 хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:

план PostgreSQL

 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

Теперь план запроса выглядит так:

план PostgreSQL

 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
» Скачать исправленную версию mysql_fdw