MySQL 8.0.24で予定されているCAST()の拡張はGIS機能を大きく進化させる

 最近、MySQLのリファレンスマニュアルの差分を追いかけているのですが、本日の更新で、Spatial(GIS)機能に注目している人には非常に大きな機能に関する記述が追加されました。CAST() 関数と CONVERT() 関数の spatial への拡張です。

MySQL :: MySQL 8.0 Reference Manual :: 12.11 Cast Functions and Operators

 あくまでも CAST() 関数ですので、複数行のデータを1つにまとめたり、1つの(たとえばLINESTRINGの)データを複数の(例えばPOINT)データにするものではないと考えられますが、CAST() 関数の 8.0.24で予定されている拡張によって、Spatialの各型どうしの変換がやりやすくなることは間違いないでしょう。強調しておきますが、数日前にリリースされた MySQL 8.0.23 ではなく、(通常どおりなら)2021年4月中旬にリリースされる予定の MySQL 8.0.24 でのお話です。

 今回のマニュアルへの追加記述によると、変換できるのは以下のもののようです。

singleな型からの変換:
f:id:sakaik:20210123171904p:plain

MULTIな型からの変換:
f:id:sakaik:20210123171933p:plain

一枚の図にあらわしたもの:
f:id:sakaik:20210123171943p:plain


当然、変換可能なもののみの動作するものと思いますが(たとえば MULTIPOINT から POINT への変換は「MULTI POINT型の中にただひとつの点だけがある場合」などの条件)、色々とドキュメントにも細かい事項が書かれているようですので、後ほどここに追記したいと思います。

続・MySQL 8.0.23 の INVISIBLE COLUMN :その用途

昨日の日記で、MySQL 8.0.23 で開放された INVISIBLE COLUMN(不可視列)について書きました。
sakaik.hateblo.jp

用途がよくわからない、といいつつ、最後に、「PKを不可視列にすれば、ちょっとだけINSERTが便利!」ということを発見したのですが、偶然ながらも、当たらずとも唐辛子といったところだったようです。

 lefred さんのブログに説明がありました。

lefred.be

 INVISIBLE COLUMN の話について書くと、「今まで、PKがなかったテーブルにAUTO_INCREMENT なPK列を追加する際に使うといいよ」ということでした。

 PKのないテーブルが存在すること自体、あまり褒められたものではありませんが、まぁ色々事情はあるのでしょう。このテーブルに id列(integer primary key)を追加する場合、通常は先頭に列を追加したいでしょうから、そのテーブルの列位置が変わってしまいます。まぁこれで影響が出ちゃうクエリもあまり褒められたものじゃないとは思うのですが、理想より現実。とにかく問題が起きたら困るので、列位置が変わらない方法があるなら、それに超したことはありません。
 こんな時に、追加する id列を不可視にすれば、影響なしなし無問題。

ALTER TABLE t1 
  ADD COLUMN id INT UNSIGNED 
        AUTO_INCREMENT PRIMARY KEY INVISIBLE FIRST;

 べつに、このid列を使ってデータアクセスしたいわけではないので、不可視でいいのです。


 lefredさんのブログには、明示的なPKが必要な理由や、PKがAUTO_INCREMENT であることをお勧めする理由が書かれていますので、興味ある方は読んでみるといいと思います。
 私の理解の範囲で簡単にまとめると:

  • PK がないテーブルは、MySQLが勝手に6バイトの内部的なPK列を作る
  • しかし内部的なPK列は、触れないし見えない、つまりコントローラブルじゃない
  • 内部的なPK列は全テーブル通しての連番(?)になるので、イケてない
  • ランダムな文字列をPKにするのはお勧めしない。INSERTのたびに内部の配置換えが起こるから(クラスタド・インデックスなので)
  • UUID() を UUID_TO_BIN してPKとして使うアイデアも、↑と同じ
  • つまりは、intやbigintのAUTO_INCREMENT 最高!

 私の勘違いや読み間違いをしている点があったら教えてください。


 PKがないテーブルに対して、内部で勝手につけるPKが現在の仕様ではなく、INVISIBLE なAUTO_INCREMENT になってくれれば、それで良い気もしますが、今回はそこへ向けての伏線なのかもしれませんね。

MySQL 8.0.23 の INVISIBLE COLUMN を試してみる

 MySQL 8.0.23 では、INVISIBLE COLUMN の仕組みが解放されました。
ドキュメントによると「カラムを追加しても、SELECT * の動作に影響を与えないところがいいんだよねー」と書いてあるのですが、うーん、それって何処の人のニーズなの?という感じで、もにょもにょします。(そんなニーズないだろう、という思いと、もしあるとしたら私の思いもつかない使い方なので、話を聞いて感動してみたい思いとが共存している感じです)

MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.10 Invisible Columns


 ということで、試すのも簡単そうなので、実際に動かしてみました。
だらだらと、やります。

テーブルの作成と確認

 himitu カラムを INVISIBLE として、テーブルt1を作ってみます。

CREATE TABLE t1 (
  id         integer,
  name       varchar(10),
  himitu     varchar(10) INVISIBLE,
  created_at datetime);

 作れたので、descと show create table それぞれで確認してみます。

desc:

mysql> desc t1;
+------------+-------------+------+-----+---------+-----------+
| Field      | Type        | Null | Key | Default | Extra     |
+------------+-------------+------+-----+---------+-----------+
| id         | int         | YES  |     | NULL    |           |
| name       | varchar(10) | YES  |     | NULL    |           |
| himitu     | varchar(10) | YES  |     | NULL    | INVISIBLE |
| created_at | datetime    | YES  |     | NULL    |           |
+------------+-------------+------+-----+---------+-----------+
4 rows in set (0.01 sec)

 出力を見ると、普通に4カラムのテーブルが作成されて、付加情報として「INVISIBLE」という属性がついている、というイメージですね。


show create table:

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `himitu` varchar(10) DEFAULT NULL /*!80023 INVISIBLE */,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

 8.0.23 以降の場合のみに「INVISIBLE」がつくようになっています。つまり 8.0.23 で作成/ダンプしたテーブルを、それ以前の(たとえばお隣のサーバの)MySQL に入れると、INVISIBLE なカラムはVISIBLE になるということでもあり、この機能のウリである「SELECT * の結果が変わらない」という観点からは、異なる動作が発生すると言えます。まぁそもそも、個人的には SELECT * の使用はお勧めしていないので、このことで問題になってしまうようなシステムは私の周りにはないと思うのですが、、

データを入れる

 INSERT文でカラム明示しない場合は、ないものとして扱われる(以下の例では3カラムの値のみを与えている)。INVISIBLE カラムに値を入れたい場合は INSERT文でカラムを明示する必要がある。

INSERT INTO t1 VALUES (1, "watashi", now());
INSERT INTO t1 (id, name, himitu, created_at) VALUES (2, "oreore", "naisyo", now());


内容確認。SELECT * には出てこない。

mysql> SELECT * FROM t1;
+------+---------+---------------------+
| id   | name    | created_at          |
+------+---------+---------------------+
|    1 | watashi | 2021-01-21 14:47:02 |
|    2 | oreore  | 2021-01-21 14:47:41 |
+------+---------+---------------------+
2 rows in set (0.00 sec)

 カラム明示をすれば、もちろん確認可能。

mysql> SELECT id, name, himitu, created_at FROM t1;
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 14:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 14:47:41 |
+------+---------+--------+---------------------+
2 rows in set (0.00 sec)

INVISIBLEをVISIBLEに

 秘密カラムを隠す必要がなくなったので(というシナリオで) VISIBLE 化してみる。

mysql> ALTER TABLE t1 MODIFY COLUMN himitu VARCHAR(10) VISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


 desc で見ても、Extraのところにあった「INVISIBLE」の表示はなくなっているし、Oracleみたいに、VISIBLE化した時に一番最後のカラムに移動したりはしない。

mysql> desc t1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| name       | varchar(10) | YES  |     | NULL    |       |
| himitu     | varchar(10) | YES  |     | NULL    |       |
| created_at | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


SELECT * でももちろん表示されるようになった。

mysql> SELECT * FROM t1;
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 14:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 14:47:41 |
+------+---------+--------+---------------------+
2 rows in set (0.00 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `himitu` varchar(10) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

ぜんぶ隠しちゃうことはできない

すべてのカラムを秘密にしてみようと思ったのだけど、許してもらえなかった。

mysql> CREATE TABLE t2 (
    ->   id         integer     INVISIBLE,
    ->   name       varchar(10) INVISIBLE,
    ->   himitu     varchar(10) INVISIBLE,
    ->   created_at datetime    INVISIBLE);
ERROR 4028 (HY000): A table must have at least one visible column.


 ということで、とりあえず1つだけVISIBLEなカラムを作った状態で、

CREATE TABLE t2 (
  id         integer     INVISIBLE,
  name       varchar(10) ,
  himitu     varchar(10) INVISIBLE,
  created_at datetime    INVISIBLE);


そのカラムを INVISIBLE にしてみようとしたが、、、

mysql> ALTER TABLE t2 MODIFY COLUMN name VARCHAR(10) INVISIBLE;
ERROR 4028 (HY000): A table must have at least one visible column.

 やはり同じエラー。最終的に1つもVISIBLEなカラムが存在しな状態が許されないことが確認できました。
これ、今回の機能のために新たに付け加えられたエラーメッセージなんですよね。お会いできて、なんだか嬉しい。

INVISIBLEなカラムに値必須オプションを付けると面倒

 id 列を PRIMARY KEY にして INVISIBLE にしてみます。なんと、これ、テーブル作成できます。
(ここまでのテーブルを元にしたので himituという名前の列がありますが、もはや秘密ではありません)

mysql> CREATE TABLE t3 (
    ->   id         integer     PRIMARY KEY INVISIBLE,
    ->   name       varchar(10) ,
    ->   himitu     varchar(10) ,
    ->   created_at datetime    );
Query OK, 0 rows affected (0.01 sec)

でも、列の明示なしでのINSERTを試みると、エラー。id列の値を指定していないのだから当然ですね。

mysql> INSERT INTO t3 VALUES ("watashi", "naisyo", now());
ERROR 1364 (HY000): Field 'id' doesn't have a default value

INVISIBLE カラムの使い方を見つけた!!!

 先ほど INVISIBLE にした PRIMARY KEY の id列に、AUTO_INCREMENTを付けてみます。

 CREATE TABLE t4 (
   id         integer     AUTO_INCREMENT PRIMARY KEY INVISIBLE,
   name       varchar(10) ,
   himitu     varchar(10) ,
   created_at datetime    );


ここに3件のデータを入れて、内容確認してみます。

INSERT INTO t4 VALUES ("watashi", "naisyo", now());
INSERT INTO t4 VALUES ("oreore", "nainai", now());
INSERT INTO t4 VALUES ("kare", "syosyo", now());
mysql> SELECT id, name, himitu, created_at FROM t4;
+----+---------+--------+---------------------+
| id | name    | himitu | created_at          |
+----+---------+--------+---------------------+
|  1 | watashi | naisyo | 2021-01-21 14:58:35 |
|  2 | oreore  | nainai | 2021-01-21 14:59:04 |
|  3 | kare    | syosyo | 2021-01-21 14:59:04 |
+----+---------+--------+---------------------+
3 rows in set (0.01 sec)

 うん!!これだ!!!
INSERT時に、AUTO_INCREMENT やデフォルト値に任せて、普段は値を明示しないような場合、ちょっとだけ便利だぞ、これ!
今までは、AUTO_INCREMENT を持つテーブルに手作業で値を入れる場合に、id列を除いた列名を羅列するか、あるいは 値として*1NULLを明示していたのが、これで不要になるのです。きっと便利だ!

 ということで本日時点の結論。

  • INVISIBLE カラムは、AUTO_INCREMENT の列に指定すると INSERTがちょっとラク

(笑)

追記:秘密カラムの追加

 いちばん本筋であった「カラムを追加しても SELECT * の結果が変わらないのがいいんだよー」というシナリオを書いていませんでした。

 以下のようにして、追加できます。

mysql> ALTER TABLE t4 ADD COLUMN himi2 varchar(5) INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

 descで確認すると、ちゃんと追加されています。

mysql> desc t4;
+------------+-------------+------+-----+---------+--------------------------+
| Field      | Type        | Null | Key | Default | Extra                    |
+------------+-------------+------+-----+---------+--------------------------+
| id         | int         | NO   | PRI | NULL    | auto_increment INVISIBLE |
| name       | varchar(10) | YES  |     | NULL    |                          |
| himitu     | varchar(10) | YES  |     | NULL    |                          |
| created_at | datetime    | YES  |     | NULL    |                          |
| himi2      | varchar(5)  | YES  |     | NULL    | INVISIBLE                |
+------------+-------------+------+-----+---------+--------------------------+
5 rows in set (0.00 sec)

*1:本当は「値」ではないのだけど便宜上

MySQL 8.0 マニュアル(Rev.68436)

2021-01-06
revision: 68430 to 68436

はじめに

 開始して3回目の日記ですが、うん、これは毎回の変更を追うのは、無理だ(笑)。
ということで、更新のたびに(日記を)更新、というのは断念し、次回以降は、目についた面白そうな更新があるときだけ紹介するようにしたいと思います。

全体の更新の傾向

 数日間見た程度の範囲ですが、以下のような更新の傾向を感じました。

  • 機能やオプション、変数らの追加に対する説明の追加
  • リファレンスマニュアルの索引への情報追加
  • 本文へのちょっとした(もうひとこと)説明の追加
  • ドキュメントソース(今回はhtmlで比較確認)改行位置の変更
  • 冠詞の変更、言い回しの変更などの、内容を大きくは変えないがニュアンスに影響する変更

 この中で、本質的な内容の追加・変更ではない部分を目視で判断していくところが、結構大変だなーというのが現時点での感想です。

今回(r68436) での変更点

 引き続き、AUTOEXTEND_SIZE に関する説明の追加や書換えが目立ちます。INFORMATION_SCHEMA.INNODB_TABLESPACES からの extend size 情報の取得SQL例など。

 その他、レプリケーションについて、こんな記述が追加:

From MySQL 8.0.23, you can improve the performance of semisynchronous replication by enabling the system variables replication_sender_observe_commit_only, which limits callbacks, and replication_optimize_for_static_plugin_config, which adds shared locks and avoids necessary lock acquisitions. These settings help as the number of replicas increases, because contention for locks can slow down performance. Semisynchronous replication source servers can also get performance benefits from enabling these system variables, because they use the same locking mechanisms as the replicas.

MySQL 8.0.23 からは、システム変数 replication_sender_observe_commit_only を有効にしてコールバックを制限し、 replication_optimize_for_static_plugin_config を有効にして、共有ロックを追加して不要なロック取得を回避することで、準同期レプリケーションのパフォーマンスを向上させることができます。これらの設定は、レプリカの数が増えるにつれて役立ちます。
なぜなら、ロックの競合はパフォーマンスを低下させる可能性があるからです。準同期レプリケーションのソースサーバは、これらのシステム変数を有効にすることでもパフォーマンスを向上させることができます。


では次回はまた気が向いたときに:^)


追記

 ちょっとしたことは、ここに追記していくことにしよう。

  • 2021-01-08 (revision: 68447) は、主にレプリケーション用語の修正。 MASTER→REPLICATION SOURCE, SLAVE→REPLICA。そうか、CHANGE MASTER が CHANGE REPLICATION SOURCE になるのか。長いなぁ。

MySQL 8.0 マニュアル(Rev.68430)

2021-01-04 → 2021-01-05
revision: 68413 to 68430


基本的には、auto-extend に関するあれこれ追加。

マニュアルのインデックスへの追加や
13.1.10 ALTER TABLESPACE Statement に AUTOEXTEND_SIZE に関する情報をより詳細に記述追加


5.1.4 Server Option, System Variable, and Status Variable Reference
5.1.5 Server System Variable Reference
5.1.9.2 Dynamic System Variables
の一覧表に

  • replication_optimize_for_static_plugin_config
  • replication_sender_observe_commit_only

を追加

インデックスの追加の例は、
https://dev.mysql.com/doc/refman/8.0/en/dynindex-sysvar.html

  • Section 17.1.6.3, “Replica Server Options and Variables”
  • Section 17.1.6.5, “Global Transaction ID System Variables”

を追加 など

MySQL 8.0 マニュアル(Rev.68413)

日々更新されている MySQL リファレンスマニュアル。どれくらい、どんな事がどんな粒度で更新されているのかを知りたくなったので、少しおいかけてみています。気が向いた範囲でここでも披露できればと思います。
 もう既に、こりゃ大変だ、、と気づいてしまい、やめる気満々なのですが、ちょっとだけ。



2021-01-04時点
revision: 68409 から revision: 68413 の変更内容(主観による要約)。

Section 15.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”追加

表領域のサイズがエクステント未満の場合は、 一度に 1 ページずつ拡張される。
表空間が 1 エクステントより大きくてもサイズが 32 エクステントより小さい場合は、1 度に 1 エクステントずつ拡張される。
表領域のサイズが 32 エクステントを超えている場合は、一度に 4 エクステントずつ拡張される。
エクステントについて詳しい情報は Section 15.11.2, “File Space Management"

MySQL 8.0.23 からは、file-per-table や 一般のテーブルスペースを拡張する量は、 AUTOEXTEND_SIZE オプションで設定可能。大きめの拡張サイズを設定することで、フラグメンテーションを回避して大きなデータを扱いやすくなる。

https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-autoextend-size.html

MySQLの地理情報データをQGISで表示する方法

この日記は、 RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2020 の14日目ぶんとして後から書いているものです。

この日記は

 QGISという GISの専用ツールがあります。 QGIS が何かについては私も語るほど整理できた情報を持っていないので、とにかく地理情報(緯度経度等の情報)のデータを、表示したり色々したりできるツールです(雑な説明)。
この、QGISからMySQLにアクセスし、MySQLのデータを表示する試みを紹介します。本日記ではとりあえず、Windows上で MySQLに接続して、テーブルまるごと表示するところまでです。
f:id:sakaik:20201224230835p:plain

概要

 実は非常に苦労して、あれこれトライしていたのですが(MySQLに接続されるものの、データが表示されなかった)、QGIS を 3.10 から 3.16 に上げたら、あっさりと表示されるようになりました。なので、この日記で紹介するものは、非常にシンプルです。 なんだったんだ、この数日の苦労は。。でも嬉しい。


 以下、手順を示します。

QGIS のインストール

 QGISWindows 10 にインストールします。今回成功したバージョンは 3.16 です。

MySQL にデータを用意

 以下の日記を参考にするなどして、適当なデータをMySQLに登録しておきます。今回動作しているのは MySQL 8.0.22 です。
sakaik.hateblo.jp
 私は、上の日記を書いたあとで、全国の境界値データと、全国の湖沼データを登録しておきました。

QGIS から MySQL への接続情報の登録

 レイヤ - データソースマネージャ を開きます。
左ペインで「ベクタ」を選択し、
 ソースタイプ:データベース
を選択。

データベース は「新規」を押して、以下の画面のとおりに設定。名前はお好きな名前で。
 タイプ:MySQL
 ホスト:localhost
 データベース:(データの入っているデータベース(スキーマ)名)ここでは shptest
 ポート番号:特に設定変更していなければ 3306
 認証はベーシックにして ユーザ名とパスワードを設定。保存にチェックを入れておくと、毎回入れなくて済むのでラク。「構成に変換」を押して変換しておくのが吉(そうでない場合はパスワードも平文で保存/表示されます)
f:id:sakaik:20201224225734p:plain

 「接続テスト」ボタン押下して接続が問題なければ、OK。
ここまでで、MySQLへの接続情報の登録が完了しました。
この画面を開いたままで、次の作業へと進みます。
閉じちゃった場合、次回以降は レイヤ - レイヤを追加 - ベクタレイヤを追加 で同じ画面が開かれます。接続情報は記憶されているので、上記DB接続設定作業を繰り返す必要はありません。
f:id:sakaik:20201224225244p:plain 

QGISMySQLデータを読み込む

 とりあえず今回は、テーブルを指定して、そのテーブルの空間データをすべて表示する、ということが目標です。
データソースマネージャのベクタの画面を開き(この日記の手順通りにやってきた場合は、ひらきっぱなしになっている画面のことです)、「追加」ボタンを押下します。(ベクタレイヤを追加する、という指示になります)
 設定されたデータベース(スキーマ)の全テーブルが一覧表示されるので、QGISで参照したいテーブルを指定してOKを押します。データのサイズによっては少し時間がかかるので、処理が終わるのをおとなしく待ちましょう。
f:id:sakaik:20201224230643p:plain

 そして表示されるのが、冒頭の日本地図です。関東地方を拡大してみたのが以下の図。
f:id:sakaik:20201224231133p:plain

 たったこれだけです。

おまけ:表示の変更、調整など

 ここからは単純に QGIS の操作の話になります。もうMySQL関係ない。
左側にある「レイヤ」は、取り込んだテーブル1つがひとつのレイヤとなっています。チェックマークを付け外しすることで、表示オンオフを切り替えられます。レイヤの右端にハテナ(?)がついている場合は、そのレイヤに適切な測地系の指定が行われていません。クリックして、一応ちゃんと設定しておいたほうが良いでしょう。
 表示の塗りつぶしや線の色・太さを変えるには、レイヤのひとつを選択して、右クリックーシンボロジ(上から3番目)です。以下にキャプチャした画面で塗りつぶしの色や塗りつぶしパターンを、同画面上部の、ここでは「シンプル塗りつぶし」になっている部分をクリックすると、線の色や太さの設定ができます。
f:id:sakaik:20201224231604p:plain

まとめ

 MySQL(データつき) と QGIS が動作する環境で、QGISからMySQLへの接続情報の設定方法から表示までを説明しました。今回使った日本全土の境界ポリゴンは、構成するポイントが12万ポイント近くあります。なので、マシンスペックによっては表示に結構時間がかかる(北から順にじわじわと表示されていく感じ)と思います。今のところ私も「そういうものだ」と思っていますが、本気で今後触っていくにはポイント数の削減等を含め、もう少しサクサク動くようにしておきたいところですね。今後の課題といたします。