軽量日記 by adokoy

MySQL

以前の職場ではPostgreSQLを使っていたが、今の職場ではMySQLを使っている。 一応、今進めているプロジェクトでどんなDBMSを使うかは一任されていたが、会社としてメインで使っているのがMySQLだったので選択。

以下、気が付いたことを記す。

MySQLには、FULL OUTER JOINが無い

これはちょっと驚いた。というか、SQL標準とは一体・・・。

一応、LEFT OUTER JOIN を2回(または読みやすくLEFT,RIGHT一回ずつ等)とUNIONで実現できる。

SELECT lf1,rf2 FROM lt LEFT OUTER JOIN rt ON lt.lf1 = rt.rf1

UNION

SELECT lf1,rf2 FROM lt RIGHT OUTER JOIN rt ON lt.lf1 = rt.rf1

MySQLの結合アルゴリズムはNested Loop(と、その派生アルゴリズム)のみ

数百万レコードを対象にして複数のフィールドでGROUP BYすると厳しい。この点、PostgreSQLはメモリを犠牲にしてHASH JOINしてくれるのでありがたいですね(場合によってはNested Loopの数百倍以上高速)

MySQLにはgenerate_series的なものが無い

一応、ユーザー変数を使ってそれっぽいことはできる。が、生成したいレコード数分が存在しているテーブル(仮想テーブルでもOK)を利用する必要があるので、information_schemaを使うかunionとcross joinで仮想テーブルを爆発させるかしなければならない。

歯抜けデータ補完で適用されることが多いのは日付関連だとは思うが、いっそのこと実テーブルを作っておくべきではある。

以下に、私がお勧めするカレンダーテーブル作成文を記す。

CREATE TABLE date_calendar( date date );


INSERT INTO date_calendar(date)

SELECT
  @date:='1500-01-01' as date
UNION ALL
SELECT
  @date:=DATE_ADD(@date, INTERVAL 1 DAY)
FROM
  (
(
  (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
  (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
) AS cj1
  CROSS JOIN
(
  (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
  (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
) AS cj2
  CROSS JOIN
(
  (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
  (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
) AS cj3
  CROSS JOIN
(
  (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
  (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
) AS cj4
  CROSS JOIN
(
  (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
  (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
) AS cj5
  CROSS JOIN
(
  (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
  (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
) AS cj6
  )
WHERE
  @date < '2500-01-01';

とりあえずこれだけの範囲をカバーしていれば問題ないだろうし、レコード数も数十万程度(=数十万日)なのでパフォーマンス上も大した問題にならないだろう。

created by
Toshiaki Yokoda
created at
last modified by
Toshiaki Yokoda
last modified at
2019-10-08 10:59