はじめに

ZOZOテクノロジーズでSREチームに所属している@hkameです
普段はZOZOTOWNのオンプレ基盤を運用しております

ZOZOTOWNはレガシーなシステムから徐々にパブリッククラウドへのリプレイスを実施していまして
そのプロジェクトに関わりながら、日々クラウドやk8s・CICDのスキルを吸収している人です

マルチクラウドでサービスを構築するための検証として
Aurora->CloudSQLの2サービスのみで、MySQLのレプリケーションができるか
を試したのでそちらを記事にします

レプリ要件

Aurora


https://aws.amazon.com/jp/rds/aurora/
AWSのフルマネージド型RDSで、saasと言われるサービスです
DBエンジンはMySQL, PostgreSQLが選択できます

外部へレプリするためには下記が求められます
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html

• レプリ先のMySQLバージョン5.5以降
• InnoDBストレージエンジンを使用しているテーブルであること

CloudSQL

https://cloud.google.com/sql/?hl=ja
GCP版のフルマネージド型RDS saasです
DBエンジンはMySQL, PostgreSQL、さらにSQLServerが選択できます

外部からレプリを受けるためには、レプリ元データベースサーバに下記が求められます
https://cloud.google.com/sql/docs/mysql/replication/replication-from-external?hl=ja#server-requirements
ポイントは下記になります

• バイナリログが行ベースのロギングであること
• GTID が有効にされていて、GTID 整合性が強制されること
• MySQL ユーザー アカウントに REPLICATION_SLAVE 権限が割り当てられていて、どこからの接続でも受け入れるように構成していること（ホスト = %）
• IPv4 アドレスと TCP ポートに外部からアクセスできること

こちらの要件からAuroraはGTIDを設定する必要があり、IPv4でアクセスできる必要が出てきます

※GTID（グローバルトランザクション識別子）
個々のトランザクションに対してつけられた世界でユニークなIDのこと
それがバイナリログに記載されることで、マスターポジションを指定しないレプリケーションができる
https://dev.mysql.com/doc/refman/5.6/ja/replication-gtids-concepts.html

Aurora->CloudSQLへレプリしてみる

イメージ図

このようなものを作りました
Auroraはマルチリージョンで作成し、書き込みインスタンスからCloudSQLへレプリ
レプリカはマルチゾーンで複数作成

手順

前提

• 検証できるAWS・GCP環境、アカウント、権限
• AuroraのMySQLエンジンクラスタが作成されている
  CloudSQLへインターネット越しにレプリするのでパブリックアクセス許可
• 検証用のデータベース・テーブルが作成されている
• 下記ができる作業用の環境
  • mysqlコマンドで作成したAuroraクラスタの書き込みエンドポイントに接続
  • gcloud、gsutilコマンドでGCPへ接続

Auroraの準備

クラスタのパラメータグループを変更

パラメータグループでクラスタのMySQLパラメータを変更できます
下記を変更します

• binlog_formatをROWに設定
  Aurora内のクラスタはバイナリログでなく、同一ストレージを参照することでレプリが実現されます
  それもありバイナリログはデフォルト無効なので、ROWに設定し行ベースでのbinlogを有効にします
  
• gtid-modeをONに設定
  GTIDはデフォルトOFFなので、ONに設定することで有効になります
• enforce_gtid_consistencyをONに設定
  GTID整合性を強制します

変更後、クラスタの再起動をします

バイナリログの保持期間を設定する

バイナリログの保持期間を1週間に設定します
短いと、レプリ設定が完了する前にバイナリログが削除されてしまう可能性があるためです
Auroraの書き込みエンドポイントに、rootユーザでMySQLログインし下記を実行します

変更
CALL mysql.rds_set_configuration('binlog retention hours', 168);

確認
CALL mysql.rds_show_configuration;

レプリ用ユーザを作成する

作成
CREATE USER '[レプリ用ユーザ名]'@'%' IDENTIFIED BY '[パスワード]';
GRANT REPLICATION SLAVE ON *.* TO  '[レプリ用ユーザ名]'@'%';

確認
SHOW GRANTS FOR '[レプリ用ユーザ名]'@'%';

CloudSQLの準備

CloudStorageでバケット作成

CloudSQLレプリカはAuroraのmysqldumpデータをインポートします
その際、GCPのクラウドストレージであるCloudStorageにデータを設置しておく必要があるため
バケットを作成しておきます

レプリケーション設定

AuroraのMySQLdumpを取得

mysqldump -u admin -p -h [Aurora書き込みエンドポイント] -P3306 \
--databases [データベース] \
--single-transaction \
--order-by-primary | gzip > dump.sql.gz

GTIDを利用した環境ではデータベースを指定した部分dump時に下記の警告がでます
データ自体は取得できていて、今回の検証では無視して進めてOKです

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

CloudStorageにdumpを設置

gsutil cp dump.sql.gz gs://[バケット名]/.

CloudSQLのレプリカを作成

GCPのコンソール画面のMIGRATE DATA 機能で
Auroraの書き込みエンドポイントをソースにレプリカを作成します

ここでひと工夫必要になりました
CloudSQLの外部からレプリケーションを受けるための、レプリ元サーバの要件

• IPv4 アドレスと TCP ポートに外部からアクセスできること

レプリ元をIPv4でしか指定できなかったのです！

AuroraはIPv4のエンドポイントを提供していません
仕方ないので、暫定的に書き込みエンドポイントをAWS外部から名前解決した際に確認できる
Auroraインスタンスの実態であるEC2インスタンスのIPv4アドレスを使用します
確認方法は下記になります

dig [書き込みエンドポイント] +noall +answer

結果例
[書き込みエンドポイント] 5   IN CNAME [書き込みインスタンスのエンドポイント]
[書き込みインスタンスのエンドポイント] 5 IN CNAME [EC2インスタンスのエンドポイント]
[EC2インスタンスのエンドポイント] 86400   IN A xx.xx.xx.xx

AuroraでCloudSQLレプリカからの通信許可

レプリカを作成すると、送信IPアドレスが払い出されます
Auroraのセキュリティグループのインバウンドルールで
送信IPアドレスからの3306通信を許可します

レプリ完了をまつ

レプリカのステータスアイコンがロード中から
緑のチェックに変わるとレプリ完了です

SHOW SLAVE STATUS¥Gで確認すると
このような状態でレプリケーションがされていました

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

slaveIO、SQLどちらも正常

Seconds_Behind_Master: 0

スナップショットから最新バイナリログまで追いついている

Replicate_Wild_Ignore_Table: mysql.%

mysqlデータベース以外がレプリケーション対象

また、レプリカインスタンスはread_onlyがONでした

複数のレプリカを作成する

1つ目はコンソール画面から作成する必要があるのですが
2つ目以降はgcloudコマンドでのみ作成ができます

gcloud beta sql instances create [レプリカインスタンス名] \
--master-instance-name=[外部プライマリインスタンス名] \
--master-username=[レプリユーザ名] --prompt-for-master-password \
--master-dump-file-path=gs://[バケット名]/[dumpファイル名] \
--tier=[インスタンススペック/例:db-n1-highmem-2] --storage-size=[DISKサイズ/例:10G]

同様に、AuroraでCloudSQLレプリカからの通信許可をしレプリ完了を待ちます

完了

以上でAurora->CloudSQLへレプリケーションができました

検証は成功なの？

レプリケーションをすることはできましたが
今回の構成ではAuroraの書き込みインスタンスがフェイルオーバーなどで変更した場合
レプリケーションが止まってしまうため、失敗です

レプリ元をIPv4で指定せざるを得ないため
フェイルオーバーでレプリ元が降格したレプリカを指定する状態になってしまい

Slave_IO_Running: No
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'

とレプリが止まってしまいました

各クラウドのエンジニアにも確認しましたが

• AuroraがIPv4のエンドポイントを提供
• CloudSQLがレプリ元をFQDNで指定可能

現在はどちらもサービス提供はしていないとのことで
どちらかが可能になれば2サービスのみで
Aurora->CloudSQLができるのではないかと思っております！

最後に

まだまだ知識不足で、文言など間違いあったらすいません
マルチクラウドでシステム構築したい！
という方がどのくらいいるのか疑問ではありますが、少しでも興味を持って頂けたら幸いです

※ MySQLの全文検索に真正面から取り組んだ話です。ケーススタディとして、ご参考になれば幸いです。

依頼を受けた

突然依頼を受けました。主な依頼内容はこちらです。

- テキストのデータが大量にあるので、これを素早く検索できるようにしてほしい。
テキストは、スペース区切りになっているものがある。日本語英語は混じっている。
　　数十文字の文章もあれば、
　　「沖縄 ホテル」のような短いテキストもある。
単語一致、部分一致で検索したい。
AND/OR条件、除外条件にも対応してほしい。
MySQLを使ってリアルタイムに（数秒で）レスが返ってきてほしい。
「B'z」を検索してもちゃんと返ってくる。

突然の依頼ではありましたが、依頼主とは常日頃BAD COMMUNICTION、、あ、いやいや、大変Goodで円滑なコミュニケーションを取れていたので、要望はきっちり聞き出せました。

なんで急にB'z？って話ですが、私は根っからのB'zファンです。なのでよく「B'z」でググったりするわけですが、「B'z」って、よくよく考えると特徴的なワードですよね。英字・記号・英字の3文字。ちゃんと「'」をエスケープしてないと大体の場面でエラーになっちゃうので、テストにも最適なワードです。是非とも「B'z」を素早く検索したい！ 最先端から加速したい・・・！！！

要件の整理

部分一致はいいとして、ここでの単語一致とは、スペース区切りの単語で見たら完全一致になるような条件、ということです。「'沖縄'の単語一致」→「沖縄 ホテル」は引っかかるが「沖縄県」は引っかからない。部分一致は、この場合どちらも引っかかります。

「テキスト」「MySQL」「速く」、、的なワードでググってみると、どうも「全文検索」というのが良いらしいです。
全文検索とは、テキストの検索に適した特殊なインデックス（FULLTEXTインデックス）を張ったカラムに対してかける検索手法のこと。もともと英文への適用が前提のため、スペース区切りの単語ごとにインデックスを張る、みたいなやり方ができ、日本語に対しても「n-gramパーサー」を使い、n文字区切りでインデックスを張ることができるため、日本語の高速なテキスト探索が可能になります。
しかも今回の場合、テキストはスペース区切りになっているものが多いので、通常のFULLTEXTインデックスを張れば「単語一致検索」が可能になりそうです。すばらしい！

というわけで、以下の方針で実装を進めることにしました。これでB'zを検索できる・・・

実装案

• InnoDBのFULLTEXTインデックスを用いる
• textとtext_ngramという2種類のカラムを作り、それぞれ内容は同じだが、インデックスの張り方だけ変える
• 通常のFULLTEXTインデックスを張ったカラムに対する全文検索により単語一致を実現する。
• ngramパーサーのFULLTEXTインデックスを張ったカラムに対する全文検索により部分一致を実現する。
• 全文検索のboolean modeを用い、AND・OR・除外を表現する。

日本語において通常のFULLTEXTインデックスが有効という、レアなケースかもしれません。
通常のFULLTEXTインデックスとngramの方を別カラムにしたのは、
　「A」の単語一致 かつ 「B」の部分一致
みたいな特殊な検索にも対応できるようにするためです。

natural language mode と boolean mode

詳しくは公式リファレンスをご参照いただければと思いますが、こちらでもまとめてみます。
(MySQL5.6のリファレンスをリンクさせていますが、8.0のリファレンスもほぼ同内容だったため、日本語のページをリンクさせています)

natural language mode (自然言語検索)

リファレンスによると、

デフォルトの場合や IN NATURAL LANGUAGE MODE 修飾子が指定された場合は、MATCH() 関数は、テキストコレクションに対して文字列の自然言語検索を実行します。コレクションは、FULLTEXT インデックスに含まれる 1 つ以上のカラムのセットです。検索文字列は、AGAINST() への引数として指定されます。MATCH() は、テーブルの行ごとに関連性の値を返します。つまり、検索文字列と、MATCH() リストで名前が指定されたカラムの該当行のテキスト間で類似性が評価されます。

つまり、検索文字列を多く含むテキストが類似度上位として検索されます。
　match(text) against ('沖縄' in natural language mode)
とすると、単語「沖縄」をたくさん含むテキストが優先的に選択されます。
ngramパーサーのFULLTEXTインデックスの場合、gram数で区切った文字列が検索対象となります。
　match(text_ngram) against ('ダイエット' in natural language mode)
とすると、gram数=2のとき、「ダイ」「イエ」「エッ」「ット」をたくさん含むテキストが優先的に選択されます。
テキストに「ダイエット」が入っていると、上記2文字のフレーズがたくさん含まれますので、必然的にそのテキストが上位に来ます。
ただ、仮に「ット」をすご～くたくさん含むテキストがあったとしたら、それも上位に現れます。"あいまい検索"と言えるかもしれません。

boolean mode (ブール検索)

こちらもリファレンスを参照すると、

IN BOOLEAN MODE 修飾子を使用することでブール全文検索を実行できます。この修飾子を使用すると、検索文字列の先頭または末尾にある特定の文字が特別な意味を持ちます。

こちらは、検索文字列を含むか含まないかの0/1で判断します。また、修飾子を用いてより細かく条件を指定できるのも特長です。例えば
　match(text) against ('A' in boolean mode) → 「A」を含むテキスト
　match(text) against ('A B' in boolean mode) → 「A」「B」どちらかを含むテキスト
　match(text) against ('+A +B' in boolean mode) → 「A」「B」両方含むテキスト
　match(text) against ('A -B' in boolean mode) → 「A」を含み、「B」を含まないテキスト
　match(text) against ('"A B"' in boolean mode) → 「A」「B」がその順に並んだフレーズを含むテキスト

今回は、厳密に単語一致・部分一致を実現したいため、boolean modeを使うこととします。

FULLTEXTインデックスを張る前に

早速データに適用したいところですが、いろいろ事前に設定しておきます。

パラメータの調整

collation_connection=utf8mb4_general_ci
default_collation_for_utf8mb4=utf8mb4_general_ci

今回のデータは日本語を含みます。MySQL8.0を使うのですが、文字コードでutf8mb4を使うとき、MySQL8.0ではデフォルトのcollationがutf8mb4_0900_ai_ciです。これだと「ハハパパ問題」が発生してしまいますので、変更します。collationについては こちらも大変参考になります。

ft_min_word_len=1
innodb_ft_min_token_size=1

これらパラメータで、検索できる最低文字数を設定します。春とか扉とか、日本語には1文字で重要な意味を持つ単語がたくさんありますので、1文字の検索を許容します。

innodb_ft_enable_stopword=0

AとかTHEとか、検索で引っかかっても分析にあまり寄与しない単語がストップワードとして事前に登録されています。デフォルト設定が英文に最適化したものなのでそうなっているのですね。日本語がメインのデータベースで、これらを除外したいという強い要件も無いので、今回はストップワードも検索対象とします。「IN THE LIFE」とかが検索できなくなりますし。。。

ngram_token_size=2

ngramは、日本語の全文検索では一般的なn=2で対応します。これは、1文字での部分一致検索ができなくなることを意味しますが、パフォーマンスの観点から致し方ありません（ngram=1も試したが、かなり重くなってしまった）。

データ投入

データの投入の際もひと工夫があるとよいです。
　インデックスを張る → データをインポート
より、
　データをインポート → インデックスを張る
の方が速いケースが多いです。
先にインデックスを張ってしまうと、レコードが増えるたびにインデックスのデータの更新が入るような挙動が見られ、レコードの増加と共に、それにかかる時間がどんどん増えてしまいます。
データを入れ替える際も、
　truncate
　 → drop index
　 → insertなりload dataなり
　 → add fulltext
とすると幾分か速いです。

テーブルはこんな構成です：

CREATE TABLE data_fulltext (
  text varchar(384) COLLATE utf8mb4_general_ci NOT NULL,
  text_ngram varchar(384) COLLATE utf8mb4_general_ci NOT NULL,
  FULLTEXT KEY ft (text),
  FULLTEXT KEY ftn (text_ngram) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

全文検索の性能検証

速さ

データをインポートできたら、早速検索してみます。全文検索は match(カラム) against ('ワード') という、変わった構文で行います。
まずは単語一致から。

select count(1) from data_fulltext 
  where match(text) against ('沖縄' in boolean mode);

+----------+
| count(1) |
+----------+
|    12442 |
+----------+
1 row in set (0.15 sec)

速い！ 続いて部分一致。

select count(1) from data_fulltext 
  where match(text_ngram) against ('沖縄' in boolean mode);

+----------+
| count(1) |
+----------+
|    18558 |
+----------+
1 row in set (0.03 sec)

速いです。
これがlikeだとだいぶかかります。

select count(1) from data_fulltext 
  where text like '%沖縄%';

+----------+
| count(1) |
+----------+
|    18558 |
+----------+
1 row in set (1 min 33.25 sec)

90秒以上かかってしまいました。なお、通常のインデックスを張っていませんが、likeで部分一致する際インデックスは利かないので、FULLTEXTインデックスを使わないと最速でこれだけ、ということになります。全文検索すごいです。
90秒かかるところ1秒未満になるのだから、性能としては十分でしょう。もちろん、レコード数が多ければその分時間はかかりますし、count(1)じゃなくselect * from ~~~と全件出力する場合ももう少しかかります。が、全文検索の方がダントツで速いです。

B'z

さて、あとは「B'z」が素早く検索できさえすればもうこちらの勝利です。

select count(1) from data_fulltext 
  where match(text) against ('B\'z' in boolean mode);

+----------+
| count(1) |
+----------+
|    12790 |
+----------+
1 row in set (0.03 sec)

相変わらずの速さ。一応中身も確認しておきましょう。

select * from data_fulltext 
  where match(text) against ('B\'z' in boolean mode) limit 3;

+---------------------+---------------------+
| text                | text_ngram          |
+---------------------+---------------------+
| #B'Z                | #B'Z                |
| &Z                  | &Z                  |
| / B'Z               | / B'Z               |
+---------------------+---------------------+
3 rows in set (0.01 sec)

（データはもちろんダミーです）
お～B'zが拾えてきてます！ ・・・が、何かおかしい。B'zじゃないものもありますね。
まず「#B'Z」は単語一致ではないし、「&Z」は、一体何なんでしょう。
何かミエナイチカラがはたらいているようです・・・

全文検索において記号は無視される

公式のリファレンスによると、

MySQL FULLTEXT の実装では、トゥルーワード文字 (文字、数字、およびアンダースコア) のシーケンスが単語とみなされます。そのシーケンスには、アポストロフィー (「'」) も含めることはできますが、1 行に 1 つまでです。

とあります。
「B'z」の「'」はアポストロフィーなわけですが、どうやら「'」も無視されてしまっているようです。
「#B'Z」が拾われているのも、「#」はトゥルーワード文字ではないので無視された、ということになります。
では「&Z」は？？？ これは、検索した文字列「B'z」の「'」が無視され、where match(text) against ('B z' in boolean mode)と同義になり、
　「B」と「z」のいずれかを含む
という条件で拾われてしまったようです（かつ、「&Z」の「&」も無視）。

「B'z」で検索して、「#B'Z」は許せるにしても「&Z」は許せない。。。そこで、検索時、「B'z」を「"」で囲ってみます。
「"」で囲うと、囲った単語がその順に並んだテキストに合致します。

select * from data_fulltext 
  where match(text) against ('"B\'z"' in boolean mode) limit 3;

+---------------------+---------------------+
| text                | text_ngram          |
+---------------------+---------------------+
| #B'Z                | #B'Z                |
| / B'Z               | / B'Z               |
| B Z                 | B Z                 |
+---------------------+---------------------+
3 rows in set (0.01 sec)

狙い通り、「B'z」がたくさん出るようになりました。
しかし、これでもまだ「記号が無視される」仕様により「B Z」というテキストが拾われてしまいます。
（そもそも「'」は単語の一部として認められるはずなんですが・・・謎です）
これはもうどうしようもないのかなあ、、、と諦めかけましたが、ちょっと考え方を変えてみることにしました。

データを見直す

お気づきの方もいらっしゃるかもしれませんが、selectされたテキスト、「B'z」じゃなくて「B'Z」なんですよね。likeで当ててみても、「B'z」という文字が出てこない。で、依頼者に問い合わせたところ、
　「あ～アルファベットは全て大文字に正規化してるんですよ」
なるほど、表記ゆれを丸めたかったのか、そういう前処理を施していたようです。つまり、このデータにアルファベット小文字は存在しない。。。

若干狂気じみたことを思いつきまして。記号を全てアルファベット小文字に置換してしまえばいいじゃないか！ 「'」は「sq」（single quote）、「"」は「dq」（double quote）のように、アルファベット小文字2文字に置換してやります。ただこれだと、記号が複数文字、例えば2文字並んだとき、アルファベット小文字が4文字並ぶわけですが、2,3文字目のアルファベット小文字が別の記号を意味するようなことがあってはいけません。そこで、記号を表すアルファベットの先頭は必ずyとし、続いて、記号に対してユニークなアルファベット小文字2文字をつなげる(ただしその2文字にyは含めない)とすることにしました。これなら、記号が複数文字並んだとしても、記号を表すのは「y」から始まるアルファベット小文字3文字になるので、ダブることもありません。

記号を英小文字に置換し、全文検索で引っかかるようにする

というわけで、データを一から作り直します。
以下のような置換処理を行いました：

記号	置換後	由来
・	ybp	bulltet point
!	yex	exclamation mark
/	ysl	slash
(	ylr	left round bracket
'	ysq	single quotation
・・・	・・・	・・・

（ちなみに「y」は、記号の英訳「s y mbol」からとってます・・・「s」は結構頭文字として頻繁に出てくるので。。。）

さらに、Collationがutf8mb4_general_ciのままだとアルファベット大文字と小文字が区別されませんので、utf8mb4_binに変更します。
さて、これで「B'z」を検索してみます。「'」は・・・「ysq」ですね。

select * from data_fulltext 
  where match(text) against ('"BysqZ"' in boolean mode) limit 3;

+--------------+--------------+
| text         | text_ngram   |
+--------------+--------------+
| ysl BysqZ    | ysl BysqZ    |
| BysqZ        | BysqZ        |
| 2018 BysqZ   | 2018 BysqZ   |
+--------------+--------------+
3 rows in set (0.01 sec)

遂に「BysqZ」を検索することができました・・・！
BysqZファンとしては感無量です・・・。ライブ行きたくなってきました。

おっと忘れてました。今のは単語一致。部分一致はいかがでしょう。

select * from data_fulltext 
  where match(text_ngram) against ('"BysqZ"' in boolean mode);

Empty set (13.01 sec)

あれ？ 割と時間がかかった挙句、0件・・・

どれだけ頑張りゃいい・・・

FULLTEXTインデックス × utf8mb4_bin にバグがある

いろいろ調べてみたんですが、どうも下記の条件が揃うと結果が0件になってしまうという、MySQLのバグのようです。

• collationがutf8mb4_bin
• ngramのfulltext index
• 英字を含むワードの検索
• boolean mode

　関連記事：https://bugs.launchpad.net/percona-server/+bug/1689268

まさにB'zの部分一致検索の条件そのままです。しかもそのバグ、まだfixされていないようです・・・（このあたり、なにか知見がありましたらコメントいただけると嬉しいです！）

natural language modeなら大丈夫

boolean modeでなく、natural language modeだとEmpty setを回避できました。ただしこのままだと「あいまい検索」になるため、部分一致検索を実現するためには、全文検索かつlikeでもあてる、というトリッキーなクエリにします。

select * from data_fulltext 
  where match(text_ngram) against ('"BysqZ"') and text_ngram like '%BysqZ%' limit 3;

+--------------+--------------+
| text         | text_ngram   |
+--------------+--------------+
| ynmBysqZ     | ynmBysqZ     |
| ysl BysqZ    | ysl BysqZ    |
| BysqZ        | BysqZ        |
+--------------+--------------+
3 rows in set (0.01 sec)

※ ynm = 「#」
BysqZファンの私でもちょっとこれはさすがに、と思ってしまうクエリになってしまいましたが、FULLTEXTインデックスのメリットも享受しつつ、B'zの部分一致が実現できました。

B'z恐るべし

これでなんとかB'zの単語一致・部分一致ともに実現できました。上記の通り変わったクエリになってしまいましたので、気になる場合は、natural language modeで十分なケースもあるかと思いますので、そこは要件次第かなと思います。
さらに、Collation=utf8mb4_binはバグも残っており、その点注意を要します（まだ見ぬバグを踏むかもしれない）。utf8mb4_general_ciに戻し、「B'z」等の記号を含むワードの検索時はlikeであてる、というルールを課す、でもよいかもしれません。消去法でイケることもあるらしいですから、先方の要望とも天秤を掛けて決めたいところですね。

おわりに

というわけで、真面目にMySQLの全文検索に取り組んでみて、非常に有用であることがわかりました。まだバグがちらほら残っていたりして発展途上な面はありますが、活用できる場面は多いのではと思います。制約も少なくないので、B'zの検索等をご検討の方はご注意ください。
なお、記号を含むワードの検索について、今回はデータの特性を生かした解決策を導きましたが(それでもだいぶパワープレイ)、他にやりようがありそうでしたら是非コメントいただきたいです。

i-plug Advent Calendar 2019 の【12日目】の記事です

4日目にスクラム開発の話をしました。

今日は趣を変えて、SQLの話です。

基礎的なことのようで、意外と知らない人も多いっぽいので、啓蒙していきます。
（想定はMySQL、バージョンは5.7です）

なにがゆるふわなのかは知りません。

前提：テーブル

-- 商品マスタ
create table m_goods (
  ID int(11) auto_increment
  , goods_name varchar(255) not null
  , goods_category varchar(32) not null
  , price int(11) not null
  , primary key(ID)
  , index(goods_category)
)
;

-- 会員マスタ
create table m_member (
  ID int(11) auto_increment
  , member_name varchar(32) not null
  , prefecture varchar(32) not null
)
;

-- 受注トラン
create table `t_order` (
  ID int(11) auto_increment
  , goods_id int(11) not null  -- m_goodsテーブルのID
  , member_id int(11) not null -- m_memberテーブルのID
  , order_date datetime not null
  , primary key(ID)
)
;

※あくまで、説明用

Lesson１：とりあえず条件付けずに検索してみる

mysql> select * from m_goods;

53957 rows in set (4.13 sec)

mysql> select * from m_member;

6274 rows in set (0.28 sec)

結構、実行時間に差がありますね。
この差は、テーブルに入っているレコード数によるもの。

「そんなの、実行する前にはわからないよ！」というあなた。
実行するSQLの前にexplainを付けてみてください。

mysql> explain select * from m_goods;

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | m_goods | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 63369 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+

mysql> explain select * from m_member;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | m_member | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5911 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

わちゃわちゃ出てきたー！

これは【実行計画】と言って、SQLを実行する際に、DBがどうやってデータを検索するかの手順のようなものになります。
なので、これを見ると、どの辺にボトルネックが出てきそうかが、見えてくる代物！

色々書いてますが、一旦、typeとrowsに注目。
typeはテーブルの検索の仕方で、ALLは「テーブルの全レコードにアクセスしている」＝一番遅くて負荷が高いことを、rowsは検索対象になるレコード数を示しています。
これを見ると、どっちの方がレコードが多いか一目瞭然！

selectの結果と差があるよ

するどい！
実は、実行計画は、リアルタイムの状況ではなく、定期的にDBが取得している、統計情報というものに従っています。
そのために、差が出てくるんですよねー。
（詳しいことは他の方の解説に任せます）

まとめ

• チューニングのためには、explainで、SQLがどう実行されるか確認！
• 対象のレコード数で、実行時間が大きく変わる！

Lesson2：INDEXを使ってみよう

次は、カラムを指定した検索をしてみます。

mysql> select * from m_goods where goods_category = 1;

254 rows in set (0.05 sec)

は、はやい...！！
何が起こっているのか、実行計画を見てみましょうー

> explain select * from m_goods where goods_category = 1;

+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | m_goods | NULL       | ref  | goods_category | goods_category | 5       | const |  254 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

さっきと比べ、

• typeがrefになっている
• だいぶrowsが少ないですね！

ということが見えるかと思います。
WHERE句で指定したgoods_categoryカラムにINDEXが付与されていて、検索対象のレコードを限定出来ていることを示しています。

次は、指定する値を、変えてみましょう。

mysql> select * from m_goods where goods_category = 999;

35522 rows in set (1.96 sec)

あれ？実行時間が増えた...
こんなときは実行計画！

mysql> explain select * from m_goods where goods_category = 999;

+----+-------------+---------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows  | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | m_goods | NULL       | ref  | goods_category | goods_category | 5       | const | 31684 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+

rowsが増えてます。
INDEXには、goods_category = 1と、goods_category = 999のレコードがそれぞれ記録されていて、指定されたSQLによって、それぞれ該当のレコードを１行ずつ精査しているんですね。
だから、INDEXが効いているレコードを使っても、条件により実行時間に差が出ると。

ちなみに、カラムが取りうる値の種類数をカーディナリティと言います。
都道府県だと４７、血液型だと4、〜フラグだと２になります。
一般的に、カーディナリティが大きいほどINDEXの効果が高いと言われています。
１つの値に該当するレコード件数が少なくなるからですね。

でもあくまで、「１つの値に該当するレコード件数が少なくなるから」なので、カーディナリティが２でも、極端に片方に値が寄っているような状態で、レコード件数が少ない方を検索する場合は有効ですよ！

最後に、goods_categoryが999以外のものを取得するSQLを見てみましょうー

mysql> explain select * from m_goods where goods_category != 999;

+----+-------------+---------+------------+------+----------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | m_goods | NULL       | ALL  | goods_category | NULL | NULL    | NULL | 63369 |    50.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+-------+----------+-------------+

おっと、typeがALLになってる！？
否定形だと、INDEXは有効でないんですねー

まとめ

• INDEXが付与されたカラムを指定すると、その条件に該当するレコードだけが対象になる
• カーディナリティが高いものを指定して上げると効果が高くなりやすい
• 否定形だと、INDEXは使われない

Lesson3：テーブルを結合してみる

mysql> select
    ->   *
    -> from t_order o
    ->   inner join m_member m
    ->     on m.id = o.member_id
    -> ;

何分待っても、返ってこない・・・

実行計画チェック！！

mysql> explain
    -> select
    ->   *
    -> from t_order o
    ->   inner join m_member m
    ->     on m.id = o.member_id
    -> ;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | m     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  5770 |   100.00 | NULL                                               |
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 83981 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+

２行出てきましたね。
これは２つのテーブルを利用していることを示しています。

着目するのは、rowsとExtra。
ExtraでBlock Nested Loopと言ってますね。
ふむふむ。つまり、m_memberの5770行の１行毎に、t_orderの83981行と結合している、ということになります。
つまり、対象は5770*83981=484570370行。。。
終わるわけないね！（こんなの本番環境で実行したら、トラブルの元です。気をつけましょうｗ）

ちゃんとINDEXが付与されているカラムを結合条件に指定しましょうね。ということなのですが、m_memberには、まともにINDEXが指定されていないので、今回は、Primary Keyを指定しちゃいます。
（実行時、テーブルロックがかかるので、本番環境へ実行するときは気をつけましょう）

mysql> alter table m_member add primary key(ID);

Query OK, 0 rows affected (1.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

はい、INDEX付与できた。
もう１回確認。

mysql> explain
    -> select
    ->   *
    -> from t_order o
    ->   inner join m_member m
    ->     on m.id = o.member_id
    -> ;

+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+-------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                        | rows  | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+-------+----------+-------+
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                       | 83981 |   100.00 | NULL  |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | adventcalendar.o.member_id |     1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+-------+----------+-------+

INDEX参照するようになって、rowsも減りましたね。
実行してみましょう。

mysql> select
    ->   *
    -> from t_order o
    ->   inner join m_member m
    ->     on m.id = o.member_id
    -> ;

100834 rows in set (11.57 sec)

んー。まだ時間かかりますが、一旦現実的な時間で結果を取得できました。

まとめ

• １つのSQLで複数テーブルを使うと、実行計画は複数行になる
• そのときの対象レコードは、rowsの掛け算
• 適切な結合条件を指定しないと、パフォーマンス劣化しやすいので、注意しましょう

Lesson4：内部結合とサブクエリ

そういえば、よくMySQLは、サブクエリが重いから結合使おうね、という話を聞きます。
本当？
確認してみましょう。

mysql> explain
    -> select
    ->   *
    -> from t_order o
    ->   inner join m_member m
    ->     on m.id = o.member_id
    ->     and m.prefecture = '大阪府'
    -> ;

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        | 83981 |   100.00 | NULL        |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | adventcalendar.o.member_id  |     1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+

mysql> explain
    -> select
    ->   *
    -> from t_order o
    -> where o.member_id in (
    ->   select c.id
    ->   from m_member m
    ->   where m.prefecture = '大阪府'
    -> )
    -> ;

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        | 83981 |   100.00 | NULL        |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | adventcalendar.o.member_id  |     1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+

実行計画は変わらないですね。
実際に実行してみましょう。

mysql> select
    ->   *
    -> from t_order o
    ->   inner join m_member m
    ->     on m.id = o.member_id
    ->     and m.prefecture = '大阪府'
    -> ;

24294 rows in set (1.57 sec)

mysql> select
    ->   *
    -> from t_order o
    -> where o.member_id in (
    ->   select c.id
    ->   from m_member m
    ->   where m.prefecture = '大阪府'
    -> )
    -> ;

24294 rows in set (0.42 sec)

おぉ。サブクエリの方が早い！
まぁ、m_memberの件数が少ないから、結合のコストよりin句に含めるコストの方が少なかっただけ、という話かな。
でも、少なくとも、MySQLにおいて、結合>サブクエリという定説は覆されたのです！！
（5.1とか、古いバージョンではわかりません）

まとめ

• 一概に、結合がサブクエリより優れている、ということはない
• 利用するテーブル次第でどっちが早いかは変わる
• ２つ目のテーブルのカラムを結果に含めたい→結合、条件だけに使いたい→サブクエリ、みたいに使い分けた方が、可読性は高いかも

Lesson５：内部結合と外部結合

そういや、基本left joinにして、inner joinしない人、結構いますよね。
その辺も明らかにしましょうか。

mysql> explain
    -> select
    ->   *
    -> from t_order o
    ->   left join m_member m
    ->     on m.id = o.member_id
    -> where m.prefecture = '大阪府'
    -> ;

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        | 83981 |   100.00 | NULL        |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | adventcalendar.o.member_id  |     1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+

実行計画はかわらず。
実際に実行してみましょう。

mysql> select
    ->   *
    -> from t_order o
    ->   left join m_member m
    ->     on m.id = o.member_id
    -> where m.prefecture = '大阪府'
    -> ;

24294 rows in set (2.24 sec)

結構、時間変わった！

まとめ

• 同じ結果を求めるなら、外部結合と内部結合使い分けた方が、パフォーマンスも可読性もいい感じ

最後に

ぶっちゃけ、ここで説明したことはデータ量や、他のINDEXなどにより変わってきます。
（バージョン辺りでも違いでるかも）

なので、大筋は参考にしてもらいつつ、実際には、実行計画と実行結果を見て、修正していく必要があると思います。

あと、細かい理屈、もっと詳細なパフォーマンス調査は省いています。この辺がゆるふわですね。
興味のある人は、RDBMSのアーキテクチャやSHOW PROFILEとか調べてみてください。

ちなみに

この記事はi-plug Advent Calendar 2019 の【12日目】の記事です。
今は、13日です。

AdventCalendarもスクラムもSQLも、計画と、実践結果を受けての適応が大事ですね。

背景・動機

業務でどうしてもSQLでの抽出結果をExcelに持っていく必要があり、CSVファイルで結果を出力できたらと思い調べたら、結構簡単にできたので備忘録をかねて投稿します。

概要

• MySQLの実行結果をファイルに保存するには INTO OUTFILE ~ を使う。
• ファイルの形式をCSVにするには FIELDS TERMINATED BY ~ を使う。

例

SELECT id, name FROM products INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

おまけ

• CSVファイルをインポートするには LOAD DATA INFILE を使う。
• その場合も同じく FIELDS TERMINATED BY ~ を使う。

例

LOAD DATA INFILE '/path/to/file.csv' INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES; -- ヘッダ行がある場合に指定

最後に

• Excelや他のツールとデータをやり取りする場合にCSVフォーマットはよく使うと思います。
• コマンドラインで簡単にCSVファイルの入出力ができるので、是非やってみてください。