多くの場合、大規模なプロジェクトでは、さまざまな理由で（多くの場合、歴史的ですが、どのような場合でも発生します）、重要なデータの保存と検索にさまざまなDBMSを使用できます。 とりわけ、この多様性は競争と技術開発によって促進されますが、何らかの方法で、DBMS間の相互作用はSQL / MED 2003（外部データの管理）標準によって記述され、外部データラッパー（FDW）とデータリンクの定義を導入します。
標準の最初の部分は、1つ以上の外部ソースの制御下でデータを一連のリレーショナルテーブルとして読み取るための手段を提供します。 FDWは、SQLインターフェイスを使用して、ファイルやメールボックス内の文字のリストなどの非SQLデータにアクセスする機能も提供します。 2番目の部分であるDatalinkを使用すると、リモートSQLサーバーを管理できます。
これらの2つの部分はPostgreSQL 9.1に実装され、それぞれFDWおよびdblinkと呼ばれます。 PostgreSQLのFDWは可能な限り柔軟に作成されているため、多数の外部ソースのラッパーを開発できます。 現在、PostgreSQL、Oracle、SQL Server、MySQL、Cassandra、Redis、RethinkDB、LdapなどのFDW、およびCSV、JSON、XMLなどのファイルのFDWを知っています。
この記事では、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
必要なライブラリがロードされるように、拡張機能をベースにインストールします。
CREATE EXTENSION mysql_fdw ;
サーバーを作成します。
CREATE SERVER mysql_server_data FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
そして、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');
顧客ディレクトリをPostgreSQLに保存するとします。
CREATE TABLE customers (id serial, name text);
2014年1月に最もアクティブな5人の購入者を選択してみましょう。
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;
('2014-02-01'::timestamp - '1 sec'::interval)
よりも正確に格納されているため、order_dateと('2014-02-01'::timestamp - '1 sec'::interval)
比較するの('2014-02-01'::timestamp - '1 sec'::interval)
正しくありませんが、この値は偶然に選択されたものではありません。
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（構文糖）は2つの条件で拡張され、そのうちの1つはPostgreSQL側で計算されませんでした：（ ('2014-02-01'::timestamp - '1 sec'::interval)
2行の差（日付と間隔ではない）：
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)
その結果、クエリは誤った結果を返します。
顧客の1人が同様の問題に遭遇しました。 この問題は、PostgresProのフォーク（ https://github.com/postgrespro/mysql_fdw）で修正され、メインの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のインデックスが使用されます（作成されている場合）。
したがって、リクエストの実行を加速しました。 簡単な例を使用して、オープンソースの力と拡張性におけるPostgreSQLの力を感じました。
ご清聴ありがとうございました！
» SQL-MEDの詳細
» mysql_fdwの修正バージョンをダウンロードする