Percona Serverのマスキング試してみた
MySQLのマスキングをPerconaで使ってみる
MySQLのマスキングはEnterpriseEditionでしか使えないが、PerconaServerはそれを無償で使えるように！
やるじゃん、ぺるこな！
でも使えるのは8.0.17-18バージョンからなので、ごめんね5.7…。
とりあえず、この記事 をなぞりつつ、MySQLの公式ドキュメントを見ながら試してみた。
マスキング関数
- ビューでマスキング済の結果を返す
- ビューにマスキング関数を利用する
- 今のところは関数を見る限りあまり日本向けではない
マスキング関数の種類
マスキング関数の種類以下の3パターンに分かれている。
(ネーミングは公式の英語からこんな感じで勝手につけたので正しくないかも)
- データマスキング関数
- ランダムデータ生成関数
- ランダムデータディクショナリベース関数
１．データマスキング関数（Data Masking Functions＝DMF）
- 文字列引数に対してマスキング操作を実行し、マスクされた結果を返します。
２．ランダムデータ生成関数（Random Data Generation Functions＝RDGF）
- さまざまなタイプのデータに対してランダムな値を生成します。
- 生成された値には、可能であれば、正当なデータと間違われることを避けるために、デモンストレーションまたはテスト値用に予約された特性があります。
３．ランダムデータディクショナリベース関数（Random Data Dictionary-Based Functions＝RDDBF）
- 用語の辞書を操作し、それらに基づいて生成およびマスキング操作を実行します。
マスキング関数の一覧(MySQL8.0.19時点)
| 関数名 | 関数タイプ | 説明 | 構文 | 引数 |
|---|---|---|---|---|
| mask_inner | DMF | 文字列の内部部分をマスクし、両端をそのままにして、結果を返します。 オプションのマスキング文字を指定できます。 マスキング文字はシングルバイト文字でなければなりません。 マルチバイト文字を使用しようとすると、エラーが発生します |
mask_inner(str, margin1, margin2 [, mask_char]) | str：マスクする文字列。 margin1：マスクされないままにする文字列の左端の文字数を指定する負でない整数。値が0の場合、マスクされていない左端の文字はありません。 margin2：マスクされないままにする文字列の右端の文字数を指定する負でない整数。 値が0の場合、右端の文字はマスクされません。 mask_char：（オプション）マスキングに使用する単一の文字。もし mask_charが指定されていない場合 デフォルトは 'X'です。 |
| mask_outer | DMF | 文字列の左端と右端をマスクし、内部をマスクせずに、結果を返します。 オプションのマスキング文字を指定できます。 マスキング文字はシングルバイト文字でなければなりません。 マルチバイト文字を使用しようとすると、エラーが発生します |
mask_outer(str, margin1, margin2 [, mask_char]) | str：マスクする文字列。 margin1：マスクする文字列の左端の文字数を指定する非負の整数。 値が0の場合、左端の文字はマスクされません。 margin2：マスクする文字列の右端の文字数を指定する非負の整数。 値が0の場合、右端の文字はマスクされません。 mask_char：（オプション）マスキングに使用する単一の文字。 もし mask_charが指定されていない場合 デフォルトは 'X'です。 |
| mask_pan | DMF | 支払いカードのプライマリアカウント番号をマスクし、最後の4桁以外をすべて'X'文字で置き換えた番号を返します 。 | mask_pan(str) | str：マスクする文字列。 文字列はプライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。 |
| mask_pan_relaxed | DMF | 支払いカードのプライマリアカウント番号をマスクし、最初の6桁と最後の4桁を除くすべての'X'文字を文字に置き換えた番号を返します。 最初の6桁は、支払いカード発行者を示します。 |
mask_pan_relaxed(str) | str：マスクする文字列。文字列は、プライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。 |
| mask_ssn | DMF | 米国の社会保障番号をマスクし、最後の4桁以外のすべてを'X'文字に置き換えた番号を返します 。 | mask_ssn(str) | |
| gen_range | RDGF | 指定された範囲から選択された乱数を生成します。 | gen_range(lower, upper) | lower：範囲の下限を指定する整数。 upper：範囲の上限を指定する整数。下限より小さくてはいけません。 |
| gen_rnd_email | RDGF | example.comドメイン 内のランダムな電子メールアドレスを生成します。 | gen_rnd_email() | なし |
| gen_rnd_pan | RDGF | ランダムな支払いカードのプライマリアカウント番号を生成します。 (※割り当てられない可能性が0じゃないので公開向きではないと公式には記載されている) |
gen_rnd_pan(size) | size：（オプション）結果のサイズを指定する整数。指定しない場合デフォルトは16です。指定する場合12〜19の範囲の整数である必要があります。 |
| gen_rnd_ssn | RDGF | ランダムな米国社会保障番号をAAA-BB-CCCCAAABB形式で生成します。 | gen_rnd_ssn() | なし |
| gen_rnd_us_phone | RDGF | ランダムな米国の電話番号を1-555-AAA-BBBB形式で生成します。 | gen_rnd_us_phone() | なし |
| gen_blacklist | RDDBF | 1つの辞書に存在する用語を2番目の辞書の用語に置き換え、置換する用語を返します。 これにより、置換によって元の用語がマスクされます |
gen_blacklist(str, dictionary_name, replacement_dictionary_name) | str：置換する用語を示す文字列。 dictionary_name：置換する用語を含む辞書に名前を付ける文字列。 replacement_dictionary_name：置換語を選択する辞書を指定する文字列。 |
| gen_dictionary | RDDBF | 辞書からランダムな用語を返します。 | gen_dictionary(dictionary_name) | dictionary_name：用語を選択する辞書を指定する文字列。 |
| gen_dictionary_drop | RDDBF | 辞書を辞書レジストリから削除します。 | gen_dictionary_drop(dictionary_name) | dictionary_name：辞書レジストリから削除する辞書に名前を付ける文字列。 |
| gen_dictionary_load | RDDBF | ファイルを辞書レジストリにロードし、辞書名の引数を必要とする他の関数で使用される名前を辞書に割り当てます。 この機能にはSUPER特権が必要です。 辞書をリロードするには、まずgen_dictionary_drop()で辞書をドロップし 、次にgen_dictionary_load()で再度ロードします。 |
gen_dictionary_load(dictionary_path, dictionary_name) | dictionary_path：辞書ファイルのパス名を指定する文字列。 dictionary_name：辞書の名前を提供する文字列。 |
環境構築
では、遊んで見るために環境を構築してみるか！
Percona8 インストール in AWS EC2
Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS参照
今回は作業時のPercona Serverの最新Verである8.0.18-9 を入れました。
[ec2-user@ip-xxx ~]$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
percona-release-latest.noarch.rpm | 17 kB 00:00:00
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm を調べています: percona-release-1.0-13.noarch
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm をインストール済みとして設定しています
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:1.0-13 を インストール
--> 依存性解決を終了しました。
amzn2-core/2/x86_64 | 2.4 kB 00:00:00
amzn2extra-docker/2/x86_64 | 1.3 kB 00:00:00
依存性を解決しました
============================================================================================================================================================================================================
Package アーキテクチャー バージョン リポジトリー 容量
============================================================================================================================================================================================================
インストール中:
percona-release noarch 1.0-13 /percona-release-latest.noarch 20 k
トランザクションの要約
============================================================================================================================================================================================================
インストール 1 パッケージ
合計容量: 20 k
インストール容量: 20 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
インストール中 : percona-release-1.0-13.noarch 1/1
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
https://www.percona.com/doc/percona-repo-config/percona-release.html
検証中 : percona-release-1.0-13.noarch 1/1
インストール:
percona-release.noarch 0:1.0-13
完了しました!
[ec2-user@ip-xxx ~]$ sudo percona-release setup ps80
* Disabling all Percona Repositories
* Enabling the Percona Server 8.0 repository
* Enabling the Percona Tools repository
<*> All done!
[ec2-user@ip-xxx ~]$ sudo yum install percona-server-server
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
ps-80-release-noarch | 2.9 kB 00:00:00
ps-80-release-x86_64 | 2.9 kB 00:00:00
tools-release-noarch | 2.9 kB 00:00:00
tools-release-x86_64 | 2.9 kB 00:00:00
(1/4): tools-release-noarch/2/primary_db | 1.1 kB 00:00:00
(2/4): ps-80-release-noarch/2/primary_db | 1.1 kB 00:00:00
(3/4): ps-80-release-x86_64/2/primary_db | 63 kB 00:00:00
(4/4): tools-release-x86_64/2/primary_db | 54 kB 00:00:00
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-server.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: percona-server-client のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-client.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: libncurses.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: libtinfo.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
---> パッケージ percona-server-shared.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared-compat のパッケージ: percona-server-shared-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ mariadb-libs.x86_64 1:5.5.64-1.amzn2 を 不要
---> パッケージ ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3 を インストール
---> パッケージ percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7 を 非推奨
--> 依存性解決を終了しました。
依存性を解決しました
============================================================================================================================================================================================================
Package アーキテクチャー バージョン リポジトリー 容量
============================================================================================================================================================================================================
インストール中:
percona-server-server x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 53 M
percona-server-shared-compat x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 1.2 M
mariadb-libs.x86_64 1:5.5.64-1.amzn2 を入れ替えます
依存性関連でのインストールをします:
ncurses-compat-libs x86_64 6.0-8.20170212.amzn2.1.3 amzn2-core 308 k
percona-server-client x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 12 M
percona-server-shared x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 1.3 M
トランザクションの要約
============================================================================================================================================================================================================
インストール 2 パッケージ (+3 個の依存関係のパッケージ)
総ダウンロード容量: 68 M
Is this ok [y/d/N]: y
Downloading packages:
(1/5): ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64.rpm | 308 kB 00:00:00
warning: /var/cache/yum/x86_64/2/ps-80-release-x86_64/packages/percona-server-client-8.0.18-9.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY ] 3.6 MB/s | 24 MB 00:00:12 ETA
percona-server-client-8.0.18-9.1.el7.x86_64.rpm の公開鍵がインストールされていません
(2/5): percona-server-client-8.0.18-9.1.el7.x86_64.rpm | 12 MB 00:00:04
(3/5): percona-server-shared-8.0.18-9.1.el7.x86_64.rpm | 1.3 MB 00:00:00
(4/5): percona-server-shared-compat-8.0.18-9.1.el7.x86_64.rpm | 1.2 MB 00:00:01
(5/5): percona-server-server-8.0.18-9.1.el7.x86_64.rpm | 53 MB 00:00:18
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
合計 3.7 MB/s | 68 MB 00:00:18
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
Userid : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
Package : percona-release-1.0-13.noarch (installed)
From : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか？ [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
インストール中 : percona-server-shared-compat-8.0.18-9.1.el7.x86_64 1/6
インストール中 : percona-server-shared-8.0.18-9.1.el7.x86_64 2/6
インストール中 : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64 3/6
インストール中 : percona-server-client-8.0.18-9.1.el7.x86_64 4/6
インストール中 : percona-server-server-8.0.18-9.1.el7.x86_64 5/6
Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/8.0/management/udf_percona_toolkit.html for more details
削除中 : 1:mariadb-libs-5.5.64-1.amzn2.x86_64 6/6
検証中 : percona-server-client-8.0.18-9.1.el7.x86_64 1/6
検証中 : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64 2/6
検証中 : percona-server-server-8.0.18-9.1.el7.x86_64 3/6
検証中 : percona-server-shared-8.0.18-9.1.el7.x86_64 4/6
検証中 : percona-server-shared-compat-8.0.18-9.1.el7.x86_64 5/6
検証中 : 1:mariadb-libs-5.5.64-1.amzn2.x86_64 6/6
インストール:
percona-server-server.x86_64 0:8.0.18-9.1.el7 percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7
依存性関連をインストールしました:
ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3 percona-server-client.x86_64 0:8.0.18-9.1.el7 percona-server-shared.x86_64 0:8.0.18-9.1.el7
置換:
mariadb-libs.x86_64 1:5.5.64-1.amzn2
完了しました!
Percona Server起動
[ec2-user@ip-xxx ~]$ sudo service mysql start
Redirecting to /bin/systemctl start mysql.service
[ec2-user@ip-xxx ~]$ sudo service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 水 2020-01-15 02:26:06 UTC; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3294 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 3376 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─3376 /usr/sbin/mysqld
1月 15 02:26:00 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Starting MySQL Server...
1月 15 02:26:06 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Started MySQL Server.
Percona Server ログイン
[ec2-user@ip-xxx ~]$ sudo cat /var/log/mysqld.log |grep generated 2020-01-15T02:26:03.644352Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: xxxxxx [ec2-user@ip-xxx ~]$ mysql -uroot -pxxxxxx mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.18-9 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
root ユーザのパスワード変更
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
が出るのでパスワードを変更する
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'passxxxxxx'; Query OK, 0 rows affected (0.00 sec)
マスキング設定
mysql> INSTALL PLUGIN data_masking SONAME 'data_masking.so'; Query OK, 0 rows affected (0.00 sec)
辞書登録
gen_dictionaryで行う。
辞書と呼ばれるものにマスキング用文字列登録します。(再起動すると読み直しが必要)
というわけで、お試しファイルを落としてくる
[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
--2020-01-15 02:50:00-- https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1289773 (1.2M) [text/plain]
`first_names.all.txt' に保存中
100%[==================================================================================================================================================================>] 1,289,773 --.-K/s 時間 0.03s
2020-01-15 02:50:01 (39.1 MB/s) - `first_names.all.txt' へ保存完了 [1289773/1289773]
[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
--2020-01-15 02:50:45-- https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 786535 (768K) [text/plain]
`last_names.all.txt' に保存中
100%[==================================================================================================================================================================>] 786,535 --.-K/s 時間 0.04s
2020-01-15 02:50:46 (18.1 MB/s) - `last_names.all.txt' へ保存完了 [786535/786535]
[ec2-user@ip-xxx ~]$ ll
合計 2032
-rw-rw-r-- 1 ec2-user ec2-user 1289773 1月 15 02:50 first_names.all.txt
-rw-rw-r-- 1 ec2-user ec2-user 786535 1月 15 02:50 last_names.all.txt
[ec2-user@ip-xxx ~]$ mv *.txt /tmp/
[ec2-user@ip-xxx ~]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select gen_dictionary_load('/tmp/first_names.all.txt', 'first_names');
+----------------------------------------------------------------+
| gen_dictionary_load('/tmp/first_names.all.txt', 'first_names') |
+----------------------------------------------------------------+
| Dictionary load success |
+----------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> select gen_dictionary_load('/tmp/last_names.all.txt', 'last_names');
+--------------------------------------------------------------+
| gen_dictionary_load('/tmp/last_names.all.txt', 'last_names') |
+--------------------------------------------------------------+
| Dictionary load success |
+--------------------------------------------------------------+
1 row in set (0.01 sec)
テストテーブルの用意
[ec2-user@ip-xxx ~]$ wget https://github.com/datacharmer/test_db/archive/master.zip
--2020-01-15 03:00:43-- https://github.com/datacharmer/test_db/archive/master.zip
github.com (github.com) をDNSに問いあわせています... 52.69.186.44
github.com (github.com)|52.69.186.44|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: https://codeload.github.com/datacharmer/test_db/zip/master [続く]
--2020-01-15 03:00:44-- https://codeload.github.com/datacharmer/test_db/zip/master
codeload.github.com (codeload.github.com) をDNSに問いあわせています... 52.68.31.213
codeload.github.com (codeload.github.com)|52.68.31.213|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 特定できません [application/zip]
`master.zip' に保存中
[ <=> ] 36,687,757 3.15MB/s 時間 11s
2020-01-15 03:00:55 (3.15 MB/s) - `master.zip' へ保存終了 [36687757]
[ec2-user@ip-xxx ~]$ ll
合計 35828
-rw-rw-r-- 1 ec2-user ec2-user 36687757 1月 15 03:00 master.zip
[ec2-user@ip-xxx ~]$ unzip master.zip
Archive: master.zip
0b66c2338736779e3b150c7d125b1012d95a961f
creating: test_db-master/
inflating: test_db-master/Changelog
inflating: test_db-master/README.md
inflating: test_db-master/employees.sql
inflating: test_db-master/employees_partitioned.sql
inflating: test_db-master/employees_partitioned_5.1.sql
creating: test_db-master/images/
inflating: test_db-master/images/employees.gif
inflating: test_db-master/images/employees.jpg
inflating: test_db-master/images/employees.png
inflating: test_db-master/load_departments.dump
inflating: test_db-master/load_dept_emp.dump
inflating: test_db-master/load_dept_manager.dump
inflating: test_db-master/load_employees.dump
inflating: test_db-master/load_salaries1.dump
inflating: test_db-master/load_salaries2.dump
inflating: test_db-master/load_salaries3.dump
inflating: test_db-master/load_titles.dump
inflating: test_db-master/objects.sql
creating: test_db-master/sakila/
inflating: test_db-master/sakila/README.md
inflating: test_db-master/sakila/sakila-mv-data.sql
inflating: test_db-master/sakila/sakila-mv-schema.sql
inflating: test_db-master/show_elapsed.sql
inflating: test_db-master/sql_test.sh
inflating: test_db-master/test_employees_md5.sql
inflating: test_db-master/test_employees_sha.sql
[ec2-user@ip-xxx ~]$ ll
合計 35832
-rw-rw-r-- 1 ec2-user ec2-user 36687757 1月 15 03:00 master.zip
drwxrwxr-x 4 ec2-user ec2-user 4096 4月 9 2019 test_db-master
[ec2-user@ip-xxx ~]$ cd test_db-master/
[ec2-user@ip-xxx test_db-master]$ ll
合計 168336
-rw-rw-r-- 1 ec2-user ec2-user 964 4月 9 2019 Changelog
-rw-rw-r-- 1 ec2-user ec2-user 4325 4月 9 2019 README.md
-rw-rw-r-- 1 ec2-user ec2-user 4193 4月 9 2019 employees.sql
-rw-rw-r-- 1 ec2-user ec2-user 6276 4月 9 2019 employees_partitioned.sql
-rw-rw-r-- 1 ec2-user ec2-user 7948 4月 9 2019 employees_partitioned_5.1.sql
drwxrwxr-x 2 ec2-user ec2-user 69 4月 9 2019 images
-rw-rw-r-- 1 ec2-user ec2-user 250 4月 9 2019 load_departments.dump
-rw-rw-r-- 1 ec2-user ec2-user 14159880 4月 9 2019 load_dept_emp.dump
-rw-rw-r-- 1 ec2-user ec2-user 1090 4月 9 2019 load_dept_manager.dump
-rw-rw-r-- 1 ec2-user ec2-user 17722832 4月 9 2019 load_employees.dump
-rw-rw-r-- 1 ec2-user ec2-user 39806034 4月 9 2019 load_salaries1.dump
-rw-rw-r-- 1 ec2-user ec2-user 39805981 4月 9 2019 load_salaries2.dump
-rw-rw-r-- 1 ec2-user ec2-user 39080916 4月 9 2019 load_salaries3.dump
-rw-rw-r-- 1 ec2-user ec2-user 21708736 4月 9 2019 load_titles.dump
-rw-rw-r-- 1 ec2-user ec2-user 4568 4月 9 2019 objects.sql
drwxrwxr-x 2 ec2-user ec2-user 77 4月 9 2019 sakila
-rw-rw-r-- 1 ec2-user ec2-user 272 4月 9 2019 show_elapsed.sql
-rwxr-xr-x 1 ec2-user ec2-user 1800 4月 9 2019 sql_test.sh
-rw-rw-r-- 1 ec2-user ec2-user 4878 4月 9 2019 test_employees_md5.sql
-rw-rw-r-- 1 ec2-user ec2-user 4882 4月 9 2019 test_employees_sha.sql
[ec2-user@ip-xxx test_db-master]$ view employees.sql
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:51
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql> show columns from employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
マスキングビュー作成(GEN_RANGE,GEN_DICTIONARY)
mysql> CREATE VIEW deidentified_employees -> AS -> SELECT -> gen_range(900000000, 999999999) as emp_no, -> makedate(year(birth_date), 1) as birth_date, -> gen_dictionary('first_names') as first_name, -> gen_dictionary('last_names') as last_name, -> gender, -> makedate(year(hire_date), 1) as hire_date -> FROM employees; ERROR 1305 (42000): FUNCTION employees. gen_range does not exist
どうやらPerconaの記事のそれをそのまま使うとエラーになるみたい。(どっかに変な文字コードでも入ってるのかな？)
書き換える
mysql> CREATE VIEW deidentified_employees -> AS -> SELECT -> GEN_RANGE(900000000, 999999999) AS emp_no, -> MAKEDATE(YEAR(birth_date), 1) AS birth_date, -> GEN_DICTIONARY('first_names') AS first_name, -> GEN_DICTIONARY('last_names') AS last_name, -> gender, -> MAKEDATE(YEAR(hire_date), 1) AS hire_date -> FROM -> employees; Query OK, 0 rows affected (0.01 sec)
マスキング結果確認
ここでマスキング関数でマスキングされるのはemp_no,first_name,last_name、
birth_dateとhire_dateはMAKEDATE関数で置換表示してます。
元テーブル
mysql> SELECT * FROM employees LIMIT 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
マスキングビュー
mysql> SELECT * FROM deidentified_employees LIMIT 10; +-----------+------------+------------+-------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +-----------+------------+------------+-------------+--------+------------+ | 912474868 | 1953-01-01 | sumio | greff | M | 1986-01-01 | | 998170886 | 1964-01-01 | haralambie | rauhuff | F | 1985-01-01 | | 958103369 | 1959-01-01 | filippina | angilletta | M | 1986-01-01 | | 967871226 | 1954-01-01 | margarine | munoz | M | 1986-01-01 | | 907286604 | 1955-01-01 | ajahnay | agel | M | 1989-01-01 | | 914673574 | 1953-01-01 | kalibo | manganiello | F | 1989-01-01 | | 906652311 | 1957-01-01 | shantai | mitchael | F | 1989-01-01 | | 998921306 | 1958-01-01 | rachela | selbe | M | 1994-01-01 | | 918607185 | 1952-01-01 | bradlay | ehlke | F | 1985-01-01 | | 904967645 | 1963-01-01 | shadiyah | partyka | F | 1989-01-01 | +-----------+------------+------------+-------------+--------+------------+ 10 rows in set (0.00 sec)
マスキングビュー作成(MASK_INNER/MASK_OUTER)
文字列の中もしくは外側をマスキングする関数ですね
mysql> CREATE VIEW deidentified_salaries -> AS -> SELECT -> gen_range(900000000, 999999999) as emp_no, -> gen_range(40000, 80000) as salary, -> mask_inner(date_format(from_date, '%Y-%m-%d'), 4, 0) as from_date, -> mask_outer(date_format(to_date, '%Y-%m-%d'), 4, 2, '0') as to_date -> FROM salaries; ERROR 1123 (HY000): Can't initialize function 'mask_outer'; Wrong argument type: MASK_OUTER(string, int, int, [char])
えー、こっちもそのままだと動かん…。(4つ目の引数を外してみる)
mysql> CREATE VIEW deidentified_salaries AS -> SELECT -> GEN_RANGE(900000000, 999999999) AS emp_no, -> GEN_RANGE(40000, 80000) AS salary, -> MASK_INNER(DATE_FORMAT(from_date,'%Y-%m-%d'),4,0) AS from_date, -> MASK_OUTER(DATE_FORMAT(to_date, '%Y-%m-%d'),4,2) AS to_date -> FROM -> salaries; Query OK, 0 rows affected (0.01 sec)
通った。
マスキング結果確認
from_dateでは前から4文字を除いてマスキング、to_dateでは前から4文字目の次の文字から2文字(-は含まない模様)を除きマスキング
元テーブル
mysql> SELECT * FROM salaries LIMIT 10; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10001 | 60117 | 1986-06-26 | 1987-06-26 | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | | 10001 | 66596 | 1989-06-25 | 1990-06-25 | | 10001 | 66961 | 1990-06-25 | 1991-06-25 | | 10001 | 71046 | 1991-06-25 | 1992-06-24 | | 10001 | 74333 | 1992-06-24 | 1993-06-24 | | 10001 | 75286 | 1993-06-24 | 1994-06-24 | | 10001 | 75994 | 1994-06-24 | 1995-06-24 | | 10001 | 76884 | 1995-06-24 | 1996-06-23 | +--------+--------+------------+------------+ 10 rows in set (0.00 sec)
マスキングビュー
mysql> SELECT * FROM deidentified_salaries LIMIT 10; +-----------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +-----------+--------+------------+------------+ | 950823834 | 70949 | 1986XXXXXX | XXXX-06-XX | | 987110465 | 62877 | 1987XXXXXX | XXXX-06-XX | | 964944005 | 43451 | 1988XXXXXX | XXXX-06-XX | | 990093942 | 65625 | 1989XXXXXX | XXXX-06-XX | | 997508813 | 53442 | 1990XXXXXX | XXXX-06-XX | | 935495362 | 65082 | 1991XXXXXX | XXXX-06-XX | | 991618027 | 65280 | 1992XXXXXX | XXXX-06-XX | | 961487920 | 60056 | 1993XXXXXX | XXXX-06-XX | | 947195247 | 45183 | 1994XXXXXX | XXXX-06-XX | | 976597378 | 62853 | 1995XXXXXX | XXXX-06-XX | +-----------+--------+------------+------------+ 10 rows in set (0.00 sec)
ちなみに
- MySQL 8.0.19の時点で戻り値がlatin1になっている(それ以前はbinary)
- 確認方法は
SELECT CHARSET(gen_rnd_email()); - 文字セットを特定のものにしたい場合はCONVERT関数を使う（
CONVERT(gen_rnd_email() USING utf8mb4);）
- 確認方法は
バグもしくはMySQL EEとは違ってるところ
- mask_outer(a*****,a#####ってなるんじゃないの？)
mysql> SELECT mask_outer('abcdef', 0, 1, '*'), mask_outer('abcdef',0, 1, "#"); +---------------------------------+--------------------------------+ | mask_outer('abcdef', 0, 1, '*') | mask_outer('abcdef',0, 1, "#") | +---------------------------------+--------------------------------+ | abcde* | abcde# | +---------------------------------+--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT mask_outer('abcdef', 0, 2, '*'), mask_outer('abcdef',0, 2, "#"); +---------------------------------+--------------------------------+ | mask_outer('abcdef', 0, 2, '*') | mask_outer('abcdef',0, 2, "#") | +---------------------------------+--------------------------------+ | abcd** | abcd## | +---------------------------------+--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT mask_outer('abcdef', 0, 5, '*'), mask_outer('abcdef',0, 5, "#"); +---------------------------------+--------------------------------+ | mask_outer('abcdef', 0, 5, '*') | mask_outer('abcdef',0, 5, "#") | +---------------------------------+--------------------------------+ | abcdef | abcdef | +---------------------------------+--------------------------------+ 1 row in set (0.00 sec)
- gen_rnd_panに数字が入れられない
mysql> SELECT mask_pan(gen_rnd_pan(19)); ERROR 1123 (HY000): Can't initialize function 'gen_rnd_pan'; Wrong argument list: gen_rnd_pan()
- gen_rangeがマイナス値入れるとバグる
mysql> SELECT gen_range(100, 200), gen_range(-1000, -800); +---------------------+------------------------+ | gen_range(100, 200) | gen_range(-1000, -800) | +---------------------+------------------------+ | 159 | 4294966311 | +---------------------+------------------------+ 1 row in set (0.00 sec)
感想
- マスキング便利(バグっぽいところあるけど、無料でこれらが気軽に使えるのは良い)
- でも、2バイト文字は使えない…。
- 早く2バイト対応版欲しい！
mysql> SELECT mask_inner('41からはじめたんですよね?', 1, 2); +----------------------------------------------------------+ | mask_inner('41からはじめたんですよね?', 1, 2) | +----------------------------------------------------------+ | 4XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX�? | +----------------------------------------------------------+ 1 row in set (0.00 sec)
- ビューで同じデータを加工した形で見せることができるので色々な使い方ができそう
- 一方でPerconaServer AND 8.0なのでそうじゃない環境の場合どうするか？
MySQLも含めてマスキングがまだまだ使い勝手が微妙なところはあるものの、気軽に元データに手を入れずにマスキングできるのは本当に便利。 無いよりはあったほうが絶対に良いので、なんとか使う方法を模索してみるのもありでは。
【番外編】北海道（函館近辺）のおすすめポイントを語ってみる
今回は技術的なことはもちろん、MySQLのMの字も出ない番外編です
北海道（函館近辺）のおすすめポイントを語ってみる
- 嫁の実家があるので年末年始で行ってきた
- 今までにも何度も行ってるんで、函館近辺で良かったとことか雑に紹介してみる
遊
函館の街並み
- 懐かしいレトロな感じが残っている
- 明治～大正くらいの建物が普通に残ってる
- 外人墓地なんかもあって長崎とかと似ている
- いつも「るろうに剣心っぽい」なと感じる街並みである（行ってみたらわかると思う）
夜景
https://www.hakobura.jp/nightview/
雪遊び
- パウダースノーなので転んでも痛くない
- 手につかむとすぐ溶ける
- 雪の結晶が裸眼で見えるくらい細やか
- 自分でウェアとか用意すればかかるのはリフト代くらいなので安くつく
温泉
- 北海道はあちこちに温泉が湧いているが、函館近辺も多い
- 大沼だとスキーしたあとそのままホテルのお風呂が使えるので良い
- 寒い中で温泉はいると幸せな気分になる
- 大衆浴場的なお風呂屋さんも函館市内に多く点在しており、どれも綺麗で大きい
蔦屋書店
- 超巨大なTSUTAYA
- 無い本はほとんどないのでは？と思うような書籍量
- 休むところも多く、地元民は図書館のように使っている
- もちろんレンタルやゲームやカード等の販売もやってる
- 駐車場もバカでかい
食
ラッキーピエロ
- 函館近辺でやってるファーストフード（ご当地なんちゃら）
- 主にハンバーガーを扱っているが、オムライスやカレーライス、カツ丼なんかも食べられる
- 価格はハンバーガーはさほど安くないがオリジナリティはある
- オムライスやカレー、カツ丼あたりは量のわりには安い
- ソフトクリーム、パフェはコスパ高い
- 最近は認知度が上がったため、外国人観光客も多く、激混みで店によっては40分～1h待ちもありうる
- 15歳から75歳までバイトを募集しているらしい
寿司🍣（魚介類）
- いうまでもなく函館に行ったら食べるべきものの一つ
- スーパーの寿司ですら東京のそれと比べ物にならないが、ぜひ回転寿司に行くべき
- 一人3000円出せば「暫く寿司は食べなくてもいいや」という満足した気持ちになれる
- 東京で同じ味を同じ量食べようと思うと諭吉が必要
- 海岸通り沿いにあるところは混んでいるのでお昼過ぎくらいまでに行かないとネタが夜にはかなり無くなっている
大沼だんご🍡
- 保存料とか使ってないので、24h以内に食べないとどんどん固くなるだんご
- 買ってすぐに食べるのが吉。柔らかくてうまい
- だんごといっても串にささっているわけではない
- 結構な量が入っている
- 水曜どうでしょうでも甘いものが苦手なミスターが苦しめられていたｗ
http://www.hakonavi.ne.jp/oonuma/numanoya.html
セイコーマート
- 函館にはセブンイレブンが結構あるが、それ以上にあるのがセイコーマートというコンビニ
- コンビニ業界ではかなりホワイト企業として知られている
- 品物は他のコンビニで見ないようなオリジナル商品が多いが、特にドリンクとHOT CHEFと呼ばれる店内で作られるお弁当やおにぎり、フライドポテトなんかがかなり美味いのでおすすめ
https://www.seicomart.co.jp/instore/lineup.html
交通
飛行機
- 函館空港があり、羽田から約1hで着くんだから驚き
- 羽田まで行くのがそこまで大変じゃなければおすすめ
- 子供が小さければ膝に乗せられる年齢なら子供分が無料
- 本数が少ないJALとANN足して1日8便程度なのは注意（まあ、地方としては多いほうだけど）
新幹線
- 4～5hかかるけどずっと座って行けるのは良いし、何より連休シーズンは飛行機より安い
- 普段できない動画やラジオをまとめて聞ける
- 途中下車も可能だし
- ただし、東北新幹線は基本自由席という概念がない（一部あるけど）ので注意
路面電車
車
- 車の運転に自信があるならレンタカーが吉
- 車があればどこでも行ける（函館はそんなに大きくないが、車がないといけないところは多い）
- 効率よく楽しむのであれば車はサイコー
ロードヒーティング
- 高速道路的なところは大体雪が溶けるように道路が温かい
- 大雪の日でも安心して走れる
- さすがに住宅街とか近辺の道路には無い
その他
- 水曜どうでしょう最新作が最初に見られる（これは北海道だから）
ネガティブな話も少し書いてみる
- 函館市内と新函館北斗駅（新幹線の駅）は結構離れてて、電車で約30分ほどかかる
- 函館市内と函館空港も同様
- 嫁の実家にはインターネット環境がない
- テレビも山に近いので映らなくなること多い
- 嫁の実家には犬と猫がいるのだが、子供がアレルギー持ちで全身湿疹だらけになる（でも動物は好き）
- 病院が遠い（車なしでは行けない）
まとめ
- 函館だけでも十分に北海道を堪能できる
- 夏も冬も北海道は楽しいし、おいしい
- 函館をスタート地点にして北海道旅行始めてみません？
" _ " or " % " - Which is faster? (Like predicate in MySQL's sql)
MySQL Advent Calendar 2019 の22日目です。
MySQLのあいまい検索時のLIKEで使える「_」と「%」について調べてみました。
あいまい検索時に使う「_」と「%」
会社でlike 検索した際に「_」と「%」で速度差ってあるのかな？という話になり、
こういうの調べたことある人って世の中に結構いるんじゃない？と調べてみたんですが、なかなか出てこないので自分で調べてみました
検証について
- カラムはとりあえず文字列としました
- セカンダリインデックスの有無とNULLの有無で比較
- NULLABLEなカラムのほうは全体の1割程度NULLにしてみた(根拠は無い)
- 作業環境はdockerに立てたMySQL8.0.18です(HDD上に結構小さいスペックで立ててます)
テーブル用意
CREATE TABLE `like_test` ( `like_test_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'LIKE文の検証ID', `uuid_key_ari_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_nashi_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_ari_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, `uuid_key_nashi_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, PRIMARY KEY (`like_test_id`), KEY `idx_uuid_key_ari_nn` (`uuid_key_ari_nn`), KEY `idx_uuid_key_ari_dn` (`uuid_key_ari_dn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='LIKE文の検証' ;
| カラム名 | カラムの役割 | インデックス有無 | NOT NULL |
|---|---|---|---|
| like_test_id | PK | 〇 | 〇 |
| uuid_key_ari_nn | UUID値をNOT NULLの形で持つ | 〇 | 〇 |
| uuid_key_nashi_nn | UUID値をNOT NULLの形で持つ | × | 〇 |
| uuid_key_ari_dn | UUID値をNULLABLEの形で持つ | 〇 | × |
| uuid_key_nashi_dn | UUID値をNULLABLEの形で持つ | × | × |
データ用意
INSERT INTO `test`.`like_test` (`like_test_id`, `uuid_key_ari_nn`, `uuid_key_nashi_nn`, `uuid_key_ari_dn`, `uuid_key_nashi_dn`) VALUES (null, uuid(), uuid(), uuid(), uuid());
pythonで書いてuuid v4で入れるか？とも考えたけど、とりあえずいーやってことでMySQLのUUID関数使う。
上のINSERTを適当にぶん回したりアレコレして480000件作った
> select count(1) from like_test; +------------+ | count(1) | |------------| | 480000 | +------------+ 1 row in set Time: 0.059s
ちょっとデータをいじって、４つのカラムがすべて同じ値かつテーブルで一意な値を用意した。
> select * from like_test where like_test_id = 14100; +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ | like_test_id | uuid_key_ari_nn | uuid_key_nashi_nn | uuid_key_ari_dn | uuid_key_nashi_dn | |----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------| | 14100 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ 1 row in set Time: 0.010s
このレコードを使って検証してみる。
検証詳細
- mysqlslapを使う
- 並列数は1、1000回クエリを実行して平均、最小、最大時間を見る
- where句に
uuid_key_ari_nn,uuid_key_nashi_nn,uuid_key_ari_dn,uuid_key_nashi_dnの4カラムでLIKE文を作る - where 句の条件は以下のような感じ
| パターンNo | 条件パターン | WHERE句 |
|---|---|---|
| 1 | _も%も使わないかつlikeじゃなくて= |
= 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
| 2 | _も%も使わない |
like 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
| 3 | _で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-____________' |
| 4 | %で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-%' |
| 5 | _で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-______________-zzzzzzz123456' |
| 6 | %で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-%-zzzzzz123456' |
| 7 | _で後方一致検索 |
like '________-99xx-99yy-99zz-zzzzzz123456' |
| 8 | %で後方一致検索 |
like '%-99xx-99yy-99zz-zzzzzz123456' |
| 9 | _で中間一致検索 |
like '-99xx-99yy-99zz-____' |
| 10 | %で中間一致検索 |
like '%-99xx-99yy-99zz-%' |
ちなみに、後方一致と中間一致(7～10のパターンですね)はインデックス張っててもフルテーブルスキャンになります。
結果
分かりやすい結果が出ました。
- 検索条件値が曖昧じゃない場合は＝のほうがLIKEよりほんのり速い気がする(けど気にしなくてもいいかなってレベル)
- インデックス使えてたら気にしなくていいレベル
- フルテーブルスキャンの場合は前方一致の場合と中間一致・後方一致のばあいだと前方一致検索のほうが速い
- フルテーブルスキャン・前方一致の場合だと「_」と「%」で差が無い
- 一方、フルテーブルスキャン・中間一致/後方一致だと「_」のほうが効率がいいのが明らか
まあ、文字列探索とかしたことある人はイメージしやすいですよね。(インデックスの素晴らしさも改めて実感しましたｗ)
それぞれの速度結果
uuid_key_ari_nn(インデックスあり・NOT NULL)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.005 | 0.003 | 0.012 |
| 2 | 0.005 | 0.003 | 0.017 |
| 3 | 0.005 | 0.003 | 0.011 |
| 4 | 0.005 | 0.003 | 0.013 |
| 5 | 0.005 | 0.003 | 0.011 |
| 6 | 0.005 | 0.003 | 0.013 |
| 7 | 0.160 | 0.154 | 0.176 |
| 8 | 0.248 | 0.240 | 0.263 |
| 9 | 0.160 | 0.154 | 0.182 |
| 10 | 0.248 | 0.241 | 0.269 |
uuid_key_nashi_nn(インデックス無し・NOT NULL)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.143 | 0.137 | 0.156 |
| 2 | 0.147 | 0.141 | 0.162 |
| 3 | 0.147 | 0.140 | 0.175 |
| 4 | 0.147 | 0.141 | 0.160 |
| 5 | 0.147 | 0.140 | 0.159 |
| 6 | 0.147 | 0.141 | 0.162 |
| 7 | 0.160 | 0.153 | 0.177 |
| 8 | 0.248 | 0.241 | 0.286 |
| 9 | 0.160 | 0.154 | 0.182 |
| 10 | 0.248 | 0.240 | 0.269 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.005 | 0.003 | 0.013 |
| 2 | 0.005 | 0.003 | 0.017 |
| 3 | 0.005 | 0.003 | 0.011 |
| 4 | 0.005 | 0.003 | 0.013 |
| 5 | 0.005 | 0.003 | 0.011 |
| 6 | 0.005 | 0.003 | 0.015 |
| 7 | 0.158 | 0.151 | 0.171 |
| 8 | 0.234 | 0.227 | 0.255 |
| 9 | 0.157 | 0.151 | 0.172 |
| 10 | 0.234 | 0.227 | 0.253 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.142 | 0.136 | 0.152 |
| 2 | 0.146 | 0.139 | 0.166 |
| 3 | 0.145 | 0.140 | 0.158 |
| 4 | 0.145 | 0.140 | 0.160 |
| 5 | 0.146 | 0.140 | 0.158 |
| 6 | 0.146 | 0.140 | 0.160 |
| 7 | 0.157 | 0.151 | 0.171 |
| 8 | 0.234 | 0.227 | 0.254 |
| 9 | 0.157 | 0.151 | 0.172 |
| 10 | 0.233 | 0.227 | 0.250 |
結論
- 中間一致検索、後方一致検索で「%」使うとパフォーマンス下がる可能性があるので、そういう場合は「_」に置き換えましょう
- そもそもそんな検索しないのがいいんですが・・・
明日は@asmrt_dsさんの「はじめてMySQLを使ってみた話」です。
COUNT に「OR NULL」入れるの、正しい結果返すのわかったけど、心がザワザワしてたけど、今はスッキリした。(スゲーッ爽やかな気分だぜ)
タイトルにある話はつまりこう
ここに都市名を入れたテーブルがあったとします。
mysql root@127.0.0.1:test> select * from city limit 10; +-----------------+-------------------+-----------+--------------+------------------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | |-----------------+-------------------+-----------+--------------+------------------------| | 1 | 北海道 | 0 | <null> | <null> | | 1 | 北海道 | 100 | 札幌市 | サッポロシ | | 1 | 北海道 | 101 | 札幌市中央区 | サッポロシチュウオウク | | 1 | 北海道 | 102 | 札幌市北区 | サッポロシキタク | | 1 | 北海道 | 103 | 札幌市東区 | サッポロシヒガシク | | 1 | 北海道 | 104 | 札幌市白石区 | サッポロシシロイシク | | 1 | 北海道 | 105 | 札幌市豊平区 | サッポロシトヨヒラク | | 1 | 北海道 | 106 | 札幌市南区 | サッポロシミナミク | | 1 | 北海道 | 107 | 札幌市西区 | サッポロシニシク | | 1 | 北海道 | 108 | 札幌市厚別区 | サッポロシアツベツク | +-----------------+-------------------+-----------+--------------+------------------------+ 10 rows in set
実際には47都道府県分入っています。 city_idが0のものが都道府県を表しているレコードです。 (あ、このテーブル正規化されてなくてイケてないというツッコミはご勘弁をｗ あえてイメージしやすいようにしてるので)
定義はこちらです。
mysql root@127.0.0.1:test> desc city; +-----------------+--------------+--------+-------+-----------+---------+ | Field | Type | Null | Key | Default | Extra | |-----------------+--------------+--------+-------+-----------+---------| | prefecture_id | tinyint(2) | NO | PRI | <null> | | | prefecture_name | varchar(10) | YES | | <null> | | | city_id | smallint(3) | NO | PRI | <null> | | | city_name | varchar(600) | YES | | <null> | | | city_kana | varchar(216) | YES | | <null> | | +-----------------+--------------+--------+-------+-----------+---------+ 5 rows in set
件数は市区町村名まで入ってるレコードと47都道府県の分で1987件あるとします。
mysql root@127.0.0.1:test> select count(1) from city; +------------+ | count(1) | |------------| | 1987 | +------------+ 1 row in set mysql root@127.0.0.1:test> select count(1) from city where city_name is null; +------------+ | count(1) | |------------| | 47 | +------------+ 1 row in set mysql root@127.0.0.1:test> select * from city where city_id = 0; +-----------------+-------------------+-----------+-------------+-------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | |-----------------+-------------------+-----------+-------------+-------------| | 1 | 北海道 | 0 | <null> | <null> | | 2 | 青森県 | 0 | <null> | <null> | | 3 | 岩手県 | 0 | <null> | <null> | | 4 | 宮城県 | 0 | <null> | <null> | | 5 | 秋田県 | 0 | <null> | <null> | | 6 | 山形県 | 0 | <null> | <null> | | 7 | 福島県 | 0 | <null> | <null> | | 8 | 茨城県 | 0 | <null> | <null> | | 9 | 栃木県 | 0 | <null> | <null> | | 10 | 群馬県 | 0 | <null> | <null> | | 11 | 埼玉県 | 0 | <null> | <null> | | 12 | 千葉県 | 0 | <null> | <null> | | 13 | 東京都 | 0 | <null> | <null> | | 14 | 神奈川県 | 0 | <null> | <null> | | 15 | 新潟県 | 0 | <null> | <null> | | 16 | 富山県 | 0 | <null> | <null> | | 17 | 石川県 | 0 | <null> | <null> | | 18 | 福井県 | 0 | <null> | <null> | | 19 | 山梨県 | 0 | <null> | <null> | | 20 | 長野県 | 0 | <null> | <null> | | 21 | 岐阜県 | 0 | <null> | <null> | | 22 | 静岡県 | 0 | <null> | <null> | | 23 | 愛知県 | 0 | <null> | <null> | | 24 | 三重県 | 0 | <null> | <null> | | 25 | 滋賀県 | 0 | <null> | <null> | | 26 | 京都府 | 0 | <null> | <null> | | 27 | 大阪府 | 0 | <null> | <null> | | 28 | 兵庫県 | 0 | <null> | <null> | | 29 | 奈良県 | 0 | <null> | <null> | | 30 | 和歌山県 | 0 | <null> | <null> | | 31 | 鳥取県 | 0 | <null> | <null> | | 32 | 島根県 | 0 | <null> | <null> | | 33 | 岡山県 | 0 | <null> | <null> | | 34 | 広島県 | 0 | <null> | <null> | | 35 | 山口県 | 0 | <null> | <null> | | 36 | 徳島県 | 0 | <null> | <null> | | 37 | 香川県 | 0 | <null> | <null> | | 38 | 愛媛県 | 0 | <null> | <null> | | 39 | 高知県 | 0 | <null> | <null> | | 40 | 福岡県 | 0 | <null> | <null> | | 41 | 佐賀県 | 0 | <null> | <null> | | 42 | 長崎県 | 0 | <null> | <null> | | 43 | 熊本県 | 0 | <null> | <null> | | 44 | 大分県 | 0 | <null> | <null> | | 45 | 宮崎県 | 0 | <null> | <null> | | 46 | 鹿児島県 | 0 | <null> | <null> | | 47 | 沖縄県 | 0 | <null> | <null> | +-----------------+-------------------+-----------+-------------+-------------+ 47 rows in set
さて問題
select count(1) from city where city_name is null;を実行した場合にかえる数値は？
当然、47です。
mysql root@127.0.0.1:test> select count(1) from city where city_name is null; +------------+ | count(1) | |------------| | 47 | +------------+ 1 row in set
では、select count(city_name is null) from city; を実行した場合に返る数値は？
この場合、全件がかえります。
mysql root@127.0.0.1:test> select count(city_name is null) from city; +----------------------------+ | count(city_name is null) | |----------------------------| | 1987 | +----------------------------+ 1 row in set
ここでの city_name is null はtrueの意味の1とfalseの意味の0を返してます。
mysql root@127.0.0.1:test> select *,city_name is null from city limit 10; +-----------------+-------------------+-----------+--------------+------------------------+---------------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | city_name is null | |-----------------+-------------------+-----------+--------------+------------------------+---------------------| | 1 | 北海道 | 0 | <null> | <null> | 1 | | 1 | 北海道 | 100 | 札幌市 | サッポロシ | 0 | | 1 | 北海道 | 101 | 札幌市中央区 | サッポロシチュウオウク | 0 | | 1 | 北海道 | 102 | 札幌市北区 | サッポロシキタク | 0 | | 1 | 北海道 | 103 | 札幌市東区 | サッポロシヒガシク | 0 | | 1 | 北海道 | 104 | 札幌市白石区 | サッポロシシロイシク | 0 | | 1 | 北海道 | 105 | 札幌市豊平区 | サッポロシトヨヒラク | 0 | | 1 | 北海道 | 106 | 札幌市南区 | サッポロシミナミク | 0 | | 1 | 北海道 | 107 | 札幌市西区 | サッポロシニシク | 0 | | 1 | 北海道 | 108 | 札幌市厚別区 | サッポロシアツベツク | 0 | +-----------------+-------------------+-----------+--------------+------------------------+---------------------+ 10 rows in set
where 条件と同じ結果をSELECT 句のCOUNT内で表現するにはどうすれば良いでしょう？
OR NULLを足します
mysql root@127.0.0.1:test> select count(city_name is null or null) from city; +------------------------------------+ | count(city_name is null or null) | |------------------------------------| | 47 | +------------------------------------+ 1 row in set
ちなみに、MySQLでCOUNT内のカラムにNULLが含まれている場合は、NULLのカラムはカウント対象から外れます。 これはマニュアルにも載ってますし、Oracle等でも同じなので結構おなじみな話だと思います。
mysql root@127.0.0.1:test> select count(city_name) from city; +--------------------+ | count(city_name) | |--------------------| | 1940 | +--------------------+ 1 row in set
OR NULLを入れるとなぜ正しい結果がかえるのか？
- count(city_name is null) -> TRUEもFALSEもかえす
- count(city_name is null or null) -> 「OR NULL」を付けることで前の構文に当てはまればTRUE、それ以外はNULL -> NULLはカウント非対象 -> 正しい結果がかえる
何に心がザワつくのか？
- 「OR NULL」付けたら条件として見るというのがイマイチ納得いかない。（じゃあ、IS NULLは何なの？）
- 「OR NULL」付けたら0をNULLに置き換えてるの？
基本的にはWHERE句でやるべきことをCOUNTとかでやろうとするとこのように書くということで、本当は使わないほうがいいんだけど HAVING句で使いたい場合にはこれが出てくるだろうし、なんか納得する説明が欲しいんだけど誰か教えてチョモランマ。
と言ったら、神様仏様、いや、『兄貴ィ』降臨です！
- const OR NULLは
- constが真の時に短絡評価されて真
- constが偽の時に "偽とNULLの和集合" と評価されて不定(= NULL)
真 IS NULLは偽偽 IS NULLは偽NULL IS NULLは真
おふざけが入りましたが、本当にスッキリしました。 ありがとうございました！
インデックスマージ、とても良い
MySQLのインデックス
世の中がMySQL8.0.18のハッシュJOINに盛り上がってる中、オジサンは古いけど自分にとっては新しいマージインデックスの話をします。
複数のインデックスがテーブルに作られていても、使えるのは一つ
というのがMySQLでは原則ですが、これの 例外 があります。
それがタイトルのインデックスマージというものです。
インデックスマージとは？
名前の通りインデックス（の結果）をマージするというものです。
MySQLのドキュメント曰く
複数の range スキャンによって、行を取得しそれらの結果を 1 つにマージするために使用されます。
The Index Merge access method retrieves rows with multiple range scans and merges their results into one.
MySQL5.1では使える機能なんで、それ以降の5.5、5.6、5.7、8.0でも使えます。
1 つのテーブルからのインデックススキャンをマージします。複数のテーブルにわたるスキャンはマージしません。
This access method merges index scans from a single table only, not scans across multiple tables.
というわけで、JOINしたテーブル間からはマージしないとのこと。
単体のテーブルの条件内でのみ発動すると。
詳しくはMySQLのドキュメント読んでもらえばわかりますが、要するに1つのテーブル内で2つのインデックスを使ってORで条件指定している場合にこれが発動する可能性があるということです。
社内のDBのスロークエリを眺めていたところ…
WHERE句にこんな感じの条件が書かれているクエリがありました。
SELECT count(*) FROM A_table WHERE (A_table.last_update_time >= '2019-10-16' OR A_table.saigono_koushin_jikan >= '2019-10-16') AND A_table.last_update_time <= '2019-10-17 17:00:00' AND A_table.saigono_koushin_jikan <= '2019-10-17 17:00:00' AND … ;
（※本当はもうちょっと複雑なんですが、大人の事情でカラム名とか色々変えて、雰囲気だけ伝わるようにしていますｗ）
last_update_time というカラムと saigono_koushin_jikan というカラムでいずれかが前日以降の日付が入っていて、いずれのカラムも今日の取得時点の日付までというレンジスキャンをしています。
いずれのカラムにもインデックスは張られておらず、別のインデックスを使っていました。
（ぶっちゃけほぼインデックスフルスキャンでした）
EXPLAINを見るとこんな感じ
（見せられない色々なものを隠していますが、ご了承ください）
この2つのインデックス、張るとマージインデックス効くんじゃね？と見つけて思った自分はさっそくステージング環境にインデックスを張ってみました。
alter table A_table add key i_saigono_koushin_jikan(saigono_koushin_jikan); alter table A_table add key i_last_update_time(last_update_time);
そして実行計画確認したところ、コスト爆下がりでした！
（今回のクエリでは17万が7になりましたｗ）
本番で4.4秒かかってたクエリなんですが、ステージングではこのくらいでした。
| 追加前（秒） | 追加後（秒） |
|---|---|
| 3.234 | 0.0072 |
これは良い！
まとめ
もちろん、インデックスマージで結果が大きかったらインデックスを張った意味はあまりないかもしれないですし、逆にインデックスを張ったことで更新コストが上がる可能性もあります。
（テーブルにあれこれインデックスが増えないほうが良いと思っている人でもあるので）
（インデックスが増えすぎているということはテーブルが正規化されるべきなのかもしれないというのは別の話）
とはいえ、OR句を使ってて、絞り込まれる条件がそこにあるのなら検討する余地はあります。
インデックスのテクニックとして
- PKやUKで検索するのが最速
- セカンダリインデックスはインデックス内にPK情報を持つ
- カバリングインデックスで複合インデックスは効率よく全て使えるようにしましょう
- 実行計画（EXPLAIN）を見てExtraにUsing whereがあったらインデックスをうまく使えてないかも？
- インデックスは張りすぎると更新処理が遅くなる
といったことに加え、今回のインデックスマージを使えるようになるとまたMySQLが好きになるかもしれません。
原則（使えるインデックスは１つ）は頭に入れつつも、ORを見かけたら「もしかして？」と立ち止まって見てみましょう。
MySQL Technology Cafe #4参加してきた
昨日行ってきたやつのお話共有します。 松信さん（Facebook）の話がすごすぎて聞いてて頭プスプス言ってた。 それを思い返しながらまたプスプス言ってたんですが、なんとかかきおこしました。 正直、まだ理解が追いついてないです…。
MySQL Technology Cafe #4
https://oracle-code-tokyo-dev.connpass.com/event/135081/
タイムスケジュール ＊逐次通訳有
| 時間 | 内容 | 登壇者 |
|---|---|---|
| 18:00-18:25 | 受付 | - |
| 18:25-18:30 | はじめに | MySQL GBU 梶山 隆輔 氏 |
| 18:30-18:55 | State of the Dolphin - What's new in MySQL | Oracle Corporation Philip Antoniades (逐次通訳あり) |
| 18:55-19:55 | MySQL Replication and HA at Facebook | Facebook, Database Engineer, 松信嘉範 氏 |
| 19:55-20:30 | ネットワーキング |
State of the Dolphin - What's new in MySQL
セッション概要 MySQLの最新の開発動向についてご紹介するセッションです。MySQL 8.0のGA以降も機能強化が続けられ、MySQL Cluster 8.0やMySQL Cloud Serviceもリリースに向けて開発が進められています。
登壇者 Philip Antoniades Oracle Corporation, Senior Director MySQL Sales Consulting プロフィール MySQL社の古参メンバーの一人で、現在はMySQLの技術営業部門のを統括。2003年にMySQL ABに入社、現在はオラクルにてワールドワイドのMySQLソリューションエンジニアチームのディレクターを務める。ニューヨーク在住。
Top 8 Best Features in MySQL 8
新機能であるInnoDB ClusterやJSON、GIS関数の強化の話をされていました。
- SQL+NoSQL = MySQL ロゴにもなってるよね！
- MySQL InnoDB Cluster みなさんご存知。
- MySQLShell (SQL, Python, Java（Scriptですね）)
- ROLEとかパスワードとかセキュリティ強化
- GIS関数
- データディクショナリ
- ユニコード改善
- SQLのアナライズ
Oracle Open World 2019でこのあたりの話はもちろん、今回はメルカリ社がMySQL Analytics Serviceの話をするらしい。 MySQL Analytics Serviceって、Nipunさんの話（この動画参照くらいでしか知らないけど、当時聞いたときにまだLaboの研究段階だったと思うし、それをどう実用に活かすかみたいな話が聞けるのかと思うとちょっと今からwkwkする。
MySQL Replication and HA at Facebook
セッション概要 FacebookにおけるMySQLの高可用化技術について紹介します。具体的には、Semi-Synchronous Replicationを始めとするレプリケーション技術の話や、そこに追加した機能の話をします。また、高可用化を実現するための内製ソフトウェアのDBStatus、Binlog Server、Logtailerなどを紹介し、どのような背景で、どのような課題を解決することを目指したのかを説明します。
登壇者 松信嘉範 氏 Facebook, Database Engineer プロフィール Yoshinori is a database engineer at Facebook. Before joining Facebook, Yoshinori was a database and infrastructure architect at DeNA, living in Tokyo. Yoshinori’s primary responsibility at DeNA is to make our database infrastructure more reliable, faster and more scalable. Before joining DeNA, Yoshinori worked at MySQL/Sun/Oracle as a lead consultant in APAC for four years. Yoshinori has written eight MySQL related technical books so far and has published technical articles about MySQL, Linux, and Java for a monthly database magazine since 2004.
MySQL replication and HA @Facebook
以下松信さんの話したことを箇条書きでメモしてる分だけですが書き起こします
今日の話(目次)
レプリケーションの話
- グローバルな構成（複数リージョンでMySQLのマスタースレーブを構成）
- Facebookでは１リージョンに１つのMySQLインスタンスしか持ちません
- マルチマスターはやりません
- ある程度シンプルにシステムを組まないとトラブルの時に大変なので
- チェインもしません
- 孫スレーブはない
- カスケードもしません
- R1＝リージョン１という意味
- ちなみに、普段、マスタースレーブって言っちゃうんですが、プライマリー・レプリカっていうべきじゃないという話ありますよね。だけどslaveってソースに書いてるじゃん！ってことで（松信さんが）スレーブって言ったらレプリカのことだと思ってください
- マスターと同じリージョンにスレーブが必要
- Facebookではレプリケーションの主要な点として、リードをスケールアウトできるようにしたい
- リードをスケールアウトするためには、Writeはクロスリージョンにするのはしょうがないとしている
- 単一（同一）リージョンの中では準同期レプリケーション
- 準同期レプリケーションの仕組みは、ローカルのバイナリログを書いて、
- LBU ロジカルバックアップユニット >binlogを書いてackだけ返す
- Master capable regions マスター候補となるスレーブ
- マスター候補にならないスレーブもいる
- binlogを読んで通知する機能：Wormhole
- バイナリログの更新情報を元にMySQL以外のデータを更新する
- キャッシュとかDWHにBinlogの更新情報を通知する Wormholeの参考
binlog BinlogServerの話
- Live Master Promotion
- マスターを切り替えたい時
- アップグレード(マイナーバージョン)をFacebookでは頻繁に行っている（ので必要）
- 重要なのはダウンタイム(500ms)
- マスターをリードオンリーにした後、同期されたところで切り替え先をRWに書き換え、切り替える
- リージョンが違うのでそのくらいかかる
- Dead Master Promotion
- どっかのRACが常に落ちるんで切り替えるのを自動化してる
- ダウンタイムは30sくらい
- ダウンしてもちゃんと復活するように
- 多少切り替え時間がかかっても
- 一個もセミシンククライアントがいないときは書き込みを止めるということができる＝書き込みをさせない状態にする
- リージョンがまるごと落ちるということもないことはない
- そういう時は人が対応する
- データがロストしたか(自動では)わからないので、人が介入する
Binlog Server Role and Features
- GoogleのRippleのようなもの
ちなみにBinlog Serverの参考資料 もあげときます RippleについてはSmartStyleの説明が分かりやすかった yoku0825さんも検証をやってた
- （マスター→binlog server→スレーブの流れ）
- この機構により別のオートメーションが（スレーブやマスターを）構築する
Dependency replication
- MySQLのレプリケーションを使っているほぼすべての人は遅延を経験したことがあると思うんですけど
- MySQLのレプリケーション自体はすごく進化していて、並列性が上がって早くなっていますが課題はある
- Facebookでは特定のアカウントの特定の投稿、例えばオバマが写真をアップロードした時に、その社員へのアクセスが集中して遅延が発生しました
- こういう場合、特定のDBにデータがあるので、DBの並列性を上げても１つなんで限界がある
- Facebookでは RBR を使ってbinlogから並列に更新可能なトランザクションを判定するグラフを作っている
- トランザクション単位ではなく、レコード単位で並列度を上げていく
RBRって何？ と思ってtom__boさんの記事に行く
- RBR (Row based replication)ではRW/WWコンフリクトを見つけるためのすべてのデータが入っている。
- conflictをDAGに書き換えて、コンフリクトがないものは部分的にであっても(例ではrowレベルで分解して競合しない部分を並列化していた)すぐに適用する
- 最終的には適用でCommit orderingが保持されるようにしている
- この方法でコンフリクトが少ない環境ではsysbenchのoltp_write_onlyのシナリオで最大4~5倍の性能改善
ああ、Row Based Replのことね。 とはいえ、言ってることはわかるけど、3行目の話とかどうやって実現しているのかわけわかめ・・・。
- 非同期なスレーブが準同期なスレーブよりも多くのバイナリログを持っている可能性はある
- Facebookでは、node fenceは準同期レプリケーションを組んでいるスレーブをすべて止める
- スレーブよりもbinlog serverが正としている
- binlog serverが再構築されないようにしている
- 特定のUIDを持ったサーバにしかアクセスできないようにする
- Rows Queryにコメントが書けるので、ほかのシステムから判別できるようにしている
- シャードのIDとかレプリカセットとか
- ネットワークの優先度を調整できるようにしてる
- 準同期しているbinlog serverが最も進んだ状態にすることでフェールオーバーなどをシンプルにできた
RBR COMPLETE image format
- binlog_row_imageについて
- FULLに比べて元の値しか書かない
- MINIMALだと微妙
- FULLだと多いのでその中間のCOMPLETEを追加
Key Learnings/observations
- binlog自体がハックされて壊されると深刻な問題
- checksum機能が役に立ってる
- SBRはやめてる
- RACが部分的に遅延していると、特にリージョン間のネットワークが遅延してると書き込みは完結してても、レプリケーションが遅延する
- SQL Thread をどんなに早くしても遅れてしまう
- できるだけそういう状況を早く検知できるようにしている
今後
あたりが今後のチャレンジ
- あと、パッチの管理、いわゆる技術的負債として残っているものをできるだけなくしていきたい
Q&A
Q: NICの速度の重要性が共感してもらいにくい。松信さんはどうお考えか？
- MySQL だと優先度を変えるというのが大事
- バックアップとかはそうかもしれないけど
- あまりバンドル間での帯域については気にしてない
Q:リストアはどうしている？
- こないだ梶山さんと話したのはバックアップかHAで今回の話はHAにしたんですけど、
- mysqldumpでバックアップとってる
- consistent restore
- 自動的にバックアップをリストアしたものが正しいかを検証する機構がある
Q:変えたくても変えられないものはあるとは思うんですが、もし変えてもいいものがあればどこを変えたいですか？
- MySQLじゃないものを使うこともできますが、レプリも使えてSQLも使えて気軽に使えるMySQLはイイ
- RocksDBエンジンもあるし
- パッチの管理が大変なので、できるだけその改造がしなくていいようにしたいんだけどそうはいかなくて、
- でもMongoDBでどうする？ってのはそれはそれであるし
- KVSでSQLが使えないとめんどい
- だからRDBMSがいいよね、というのでMySQL使ってます
- とにかくパッチを取り入れてほしい
- Group ReplicationとかMySQLClusterの場合、同一リージョンにないといけないのでリージョンをまたがりたいFacebookの方針と合わない
- Group Replicationがリージョンまたがるといいなと思ってる
授賞式
当日出席されていたMySQL ACEのとみたさん、yoku0825さん、三谷さんと、DB Techで登壇された北川さんにイルカの授与式
受賞後のyoku0825さんを撮っちゃいました
最後は残った人で写真撮影
資料について
後日上がるとのことなので、イメージわきづらい人はそれを待ちましょう
最後に
mysql_cafeのスレッドに書かれていることをだいぶ参考に書かせてもらいました。
みなさんありがとうございました（特にyoku0825さん） そして、美味しい食事と飲み物、きれいな会場と、Philipさんと松信さんの貴重なお話を提供してくれたOracle社の関係者の皆様に感謝です。
データを適当に作成するmysql_random_data_loadを試してみた
テストデータ作りたいな
- 自分で手で作るのもめんどいし、定義変わるとアレ（メンテが必要）やしな。
- せや、型から適当にデータ作るツールとかないんかな？
- あったわ→mysql_random_data_load
さすぺる！（さすがPercona！）
試してみよう
use test; -- 適当なテーブル作る CREATE TABLE `person` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `guid` varchar(36) COLLATE utf8mb4_bin NOT NULL COMMENT 'GUID', `name` varchar(150) COLLATE utf8mb4_bin NOT NULL COMMENT '名前', `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '年齢', `money` int(11) DEFAULT NULL COMMENT '手持ちのお金', `marriage_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '結婚フラグ', `prefecture` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住所（都道府県）', `tel_number` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '電話番号', `fax_number` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'FAX番号', `email` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Ｅメールアドレス', `address` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住所', `company` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '会社名', PRIMARY KEY (`id`), UNIQUE KEY `u_1` (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='誰かさんの情報';
すぐ使いたいんで、precompiled binariesから取ってくる。
解凍して、mysql_random_data_loadを$PATHで見えるところに置いて、
$ which mysql_random_data_load /home/xxx/bin/mysql_random_data_load
ってな感じで見えるようになったんでOK。
使い方はhttps://github.com/Percona-Lab/mysql_random_data_loadに書いてある。
デバッグモードで100行くらい入れて試してみるか。
$ mysql_random_data_load test person 100 --debug --user=root --password=xxx --host=127.0.0.1 --port=3307
DEBU[2019-07-06T10:30:48+09:00] &tableparser.Table{
Schema: "test",
Name: "person",
Fields: {
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "id",
OrdinalPosition: 1,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "bigint",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:19, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "bigint(20)",
ColumnKey: "PRI",
Extra: "auto_increment",
Privileges: "select,insert,update,references",
ColumnComment: "ID",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "guid",
OrdinalPosition: 2,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:36, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:144, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(36)",
ColumnKey: "UNI",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "GUID",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "name",
OrdinalPosition: 3,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:150, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:600, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(150)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "名前",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "age",
OrdinalPosition: 4,
ColumnDefault: sql.NullString{String:"0", Valid:true},
IsNullable: false,
DataType: "tinyint",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:3, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "tinyint(4)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "年齢",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "money",
OrdinalPosition: 5,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "int",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:10, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "int(11)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "手持ちのお金",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "marriage_flag",
OrdinalPosition: 6,
ColumnDefault: sql.NullString{String:"0", Valid:true},
IsNullable: false,
DataType: "tinyint",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:3, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "tinyint(1)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "結婚フラグ",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "prefecture",
OrdinalPosition: 7,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:30, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:120, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(30)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "住所（都道府県）",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "tel_number",
OrdinalPosition: 8,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:20, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:80, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(20)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "電話番号",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "fax_number",
OrdinalPosition: 9,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:20, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:80, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(20)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "FAX番号",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "email",
OrdinalPosition: 10,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:255, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:1020, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(255)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "Ｅメールアドレス",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "address",
OrdinalPosition: 11,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:300, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:1200, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(300)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "住所",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "company",
OrdinalPosition: 12,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:300, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:1200, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(300)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "会社名",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
},
Indexes: {
"u_1": {
Name: "u_1",
Fields: {"guid"},
Unique: true,
Visible: true,
},
"PRIMARY": {
Name: "PRIMARY",
Fields: {"id"},
Unique: true,
Visible: true,
},
},
Constraints: {
},
Triggers: {
},
conn: (*sql.DB)(nil),
}
INFO[2019-07-06T10:30:48+09:00] Starting
DEBU[2019-07-06T10:30:48+09:00] Must run 1 bulk inserts having 100 rows each
INFO[2019-07-06T10:30:49+09:00] 100 rows inserted
お、入ってる感じやな。どれどれ？
| id | guid | name | age | money | marriage_flag | prefecture | tel_number | fax_number | address | company | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | recusandae et consequatur eius aut a | consequatur nihil error eius ut. | 5 | 1342458479 | 6 | rerum unde qui perferendis nob | Terry Sanchez | Tammy Torres | eum culpa nam minima maxime. | et non nisi voluptas temporibus aliquam magnam. | eius deleniti nam et sint voluptates id. |
| 2 | aut odio soluta ut dolores. | omnis accusamus est voluptatem consequuntur quisquam quae quia. | 11 | 914091476 | 5 | fugiat magnam aut culpa ut rep | Janet Oliver | Juan King | nemo suscipit doloremque porro error et qui tenetur ut asperiores corporis! | nemo quia magnam hic dolor. | adipisci architecto animi vitae deleniti rerum. |
| 3 | aut dolor consequatur soluta exercit | perspiciatis sit odio fugit quasi non. | 3 | 1255569388 | 11 | ullam facere exercitationem il | Janet Murphy | Fred Grant | ut ea nisi recusandae quo inventore tenetur et. | et blanditiis maxime est quam quidem id. | illo ea voluptas vitae repudiandae architecto saepe. |
| 4 | quidem omnis iste ea a impedit odio | quod velit ut earum dolore molestiae! | 14 | 2003588754 | 12 | et qui et id voluptas voluptat | Tina Garrett I II II | Joe Edwards | quo explicabo eum qui alias sequi. | dolorem quaerat nihil aut perspiciatis qui asperiores nihil. | alias nisi ullam quo debitis iusto animi. |
| 5 | illo earum est pariatur aut in digni | consequatur molestias dicta omnis. | 4 | 1698116023 | 5 | doloribus natus distinctio nos | Brenda Hunt | Sean Carter | assumenda et deserunt quidem. | consequatur occaecati non libero accusantium reprehenderit quam suscipit. | ratione eum beatae laboriosam quo enim. |
| 6 | consequuntur quibusdam nihil soluta | accusamus similique cum eos quidem. | 8 | 1153628287 | 1 | voluptas est et temporibus aut | Paula Parker | Kathryn Watkins | quis voluptatem at perferendis labore. | reiciendis voluptates itaque voluptatem optio ea qui hic. | ab quibusdam illum. |
| 7 | voluptas animi vero tenetur adipisci | et ut et! | 5 | 1640670520 | 8 | quod corporis qui rerum culpa | Joshua Hart | Louise Fernandez | quia et molestiae distinctio quaerat quasi soluta aut voluptatem et recusandae! | doloribus dignissimos ut hic tempora. | temporibus eveniet vel quisquam repellendus aut animi fugit. |
| 8 | quae quod ratione pariatur repellend | suscipit omnis aut consequatur numquam laboriosam non repellendus. | 11 | 1218842385 | 7 | dolorem quia qui accusamus eni | Kenneth Burns | Terry Bryant | consequatur ex aliquam beatae qui et consequatur et. | vitae ratione quaerat rerum facilis. | animi sit ut nobis quae ea. |
| 9 | laudantium odit et facere minima. | ea quis voluptas ipsum dignissimos asperiores sequi aut. | 13 | 601894044 | 12 | id non consequuntur iste disti | Charles Anderson | Raymond Diaz | sequi impedit nihil ea vel et nihil rerum aut. | sit commodi et autem eius. | odit qui vitae facilis modi quia harum amet aspernatur. |
| 10 | magni ullam dignissimos praesentium | dicta qui sunt dolor ut repellat hic. | 10 | 1408984034 | 11 | quia tempora consequatur delec | Stephanie Garcia | Patricia Carroll | repudiandae fuga dignissimos odio voluptate quia. | quia necessitatibus molestiae dolorem natus accusantium. | sunt consectetur est amet doloremque qui autem quae. |
そりゃそうだよね・・・。
だって型で見てるだけなんだもん。
カラム名も見てくれるツール欲しい！
- 日本語で入ってほしいものもあるよね。
- コメントを参考にしたりしてほしいよね。
- MySQL8.0でも動いて欲しい（このツールはMySQL5.7までしか動かない）
自分で作るか、こいつをいじるかな〜
追記
を見るとわかりますが、型のフィールド値に制限が有ります。
decimal(25,0)
とかやると、
panic: invalid argument to Int63n
ってメッセージが出るのでご注意を！
