- 投稿日:2019-07-16T23:49:33+09:00
MySQL8.0の間検索が遅い?の続き1
はじめに
昨日「MySQL8.0の間検索が遅い?」という記事(以降「前記事」とする)を書いたのですが、それに対するバグ報告を見つけたので検証してみました。
残念ながら本記事でも原因は特定できておらず解決には至っていませんが、本不具合をMySQL開発チームが確認済みであることは確認できました。(ステータスがVerifiedとなっているため)https://qiita.com/miyauchi/items/f6621cc79aff98dd46e0
対象となるバグ報告
私が確認した事象に関すると思われる不具合報告は下記です。
https://bugs.mysql.com/bug.php?id=94655&thanks=2¬ify=67
同不具合報告では、MBRWITHIN関数は速いのに、MBRINTERSECTS関数やMBROVERLAPS関数は遅い、という結果になったことが報告されています。
報告者であるCedric Tabin氏が使用しているのはMBRINTERSECTSやMBRWITHINなどの関数ですが、私が検証した限りではST_IntersectsやST_WithIn関数でも同様の結果となるようです。
検証結果
前記事により、ST_Intersects関数を使った実行結果の件数は391件で、実行時間は1〜2分であることは確認できています。
よって今回はST_WithIn関数とST_Overlaps関数で検証してみました。
結果としてはCedric Tabin氏の検証結果同様に、ST_WithIn関数では実行速度は速い(1秒かからない)にも関わらず、ST_Overlapsは遅い(1〜2分)となることがわかりました。
ST_WithIn関数を使った検証SQLは下記です。
SELECT ST_AsGeoJson((SHAPE)) FROM `e_stat.utf8` WHERE ST_WithIn( SHAPE, ST_GeomFromText( 'POLYGON((43.08256990265745 141.3162472988521, 43.056831004971265 141.3162472988521, 43.056831004971265 141.35259659542191, 43.08256990265745 141.35259659542191, 43.08256990265745 141.3162472988521))', 4326 ) );実行結果は下記でした。
297 rows in set (0.13 sec)また、ST_Opverlaps関数を使った検証用のSQLは下記です。
SELECT ST_AsGeoJson((SHAPE)) FROM `e_stat.utf8` WHERE ST_Overlaps( SHAPE, ST_GeomFromText( 'POLYGON((43.08256990265745 141.3162472988521, 43.056831004971265 141.3162472988521, 43.056831004971265 141.35259659542191, 43.08256990265745 141.35259659542191, 43.08256990265745 141.3162472988521))', 4326 ) );実行結果は下記でした。
94 rows in set (1 min 47.24 sec)ちなみに、ST_Intersects、ST_WithIn、ST_Overlaps関数のそれぞれの意味は下記の記事がわかりやすかったです。
https://qiita.com/boiledorange73/items/dc7c5e1ebe95625aee60
ST_Intersectsは、ST_WithInとST_OverlapsのORである気がするので(実際両関数の件数を足しあげるとST_Intersectsの件数と一致する)、せめてST_Overlapsが速ければ良かったのですが。
最後に
本不具合は2019年3月に登録され、最終更新が5月と比較的最近登録された不具合です。
Cedric Tabin氏の度重なる報告により、すでにMySQL開発チームの内部データベースに登録されているようですが、重要度はFeature Requestとあまり高くないように見えます。ので、「Affects me」ボタンを押しておきました。
引き続き調査を続けたいと思います。
- 投稿日:2019-07-16T23:49:33+09:00
MySQL8.0の空間検索が遅い?の続き1
はじめに
昨日「MySQL8.0の空間検索が遅い?」という記事(以降「前記事」とする)を書いたのですが、それに対するバグ報告を見つけたので検証してみました。
残念ながら本記事でも原因は特定できておらず解決には至っていませんが、本不具合をMySQL開発チームが確認済みであることは確認できました。(ステータスがVerifiedとなっているため)https://qiita.com/miyauchi/items/f6621cc79aff98dd46e0
対象となるバグ報告
私が確認した事象に関すると思われる不具合報告は下記です。
https://bugs.mysql.com/bug.php?id=94655&thanks=2¬ify=67
同不具合報告では、MBRWITHIN関数は速いのに、MBRINTERSECTS関数やMBROVERLAPS関数は遅い、という結果になったことが報告されています。
報告者であるCedric Tabin氏が使用しているのはMBRINTERSECTSやMBRWITHINなどの関数ですが、私が検証した限りではST_IntersectsやST_WithIn関数でも同様の結果となるようです。
検証結果
前記事により、ST_Intersects関数を使った実行結果の件数は391件で、実行時間は1〜2分であることは確認できています。
よって今回はST_WithIn関数とST_Overlaps関数で検証してみました。
結果としてはCedric Tabin氏の検証結果同様に、ST_WithIn関数では実行速度は速い(1秒かからない)にも関わらず、ST_Overlapsは遅い(1〜2分)となることがわかりました。
ST_WithIn関数を使った検証SQLは下記です。
SELECT ST_AsGeoJson((SHAPE)) FROM `e_stat.utf8` WHERE ST_WithIn( SHAPE, ST_GeomFromText( 'POLYGON((43.08256990265745 141.3162472988521, 43.056831004971265 141.3162472988521, 43.056831004971265 141.35259659542191, 43.08256990265745 141.35259659542191, 43.08256990265745 141.3162472988521))', 4326 ) );実行結果は下記でした。
297 rows in set (0.13 sec)また、ST_Opverlaps関数を使った検証用のSQLは下記です。
SELECT ST_AsGeoJson((SHAPE)) FROM `e_stat.utf8` WHERE ST_Overlaps( SHAPE, ST_GeomFromText( 'POLYGON((43.08256990265745 141.3162472988521, 43.056831004971265 141.3162472988521, 43.056831004971265 141.35259659542191, 43.08256990265745 141.35259659542191, 43.08256990265745 141.3162472988521))', 4326 ) );実行結果は下記でした。
94 rows in set (1 min 47.24 sec)ちなみに、ST_Intersects、ST_WithIn、ST_Overlaps関数のそれぞれの意味は下記の記事がわかりやすかったです。
https://qiita.com/boiledorange73/items/dc7c5e1ebe95625aee60
ST_Intersectsは、ST_WithInとST_OverlapsのORである気がするので(実際両関数の件数を足しあげるとST_Intersectsの件数と一致する)、せめてST_Overlapsが速ければ良かったのですが。
最後に
本不具合は2019年3月に登録され、最終更新が5月と比較的最近登録された不具合です。
Cedric Tabin氏の度重なる報告により、すでにMySQL開発チームの内部データベースに登録されているようですが、重要度はFeature Requestとあまり高くないように見えます。ので、「Affects me」ボタンを押しておきました。
引き続き調査を続けたいと思います。
- 投稿日:2019-07-16T21:38:14+09:00
家計簿管理システム midas の DB 権限の設定
はじめに
以前、家計簿管理システムの話をしたが、今回はそのシステムの DB 周りの権限を適切に設定した話である。
現在の設定
現在 API から DB に接続しているユーザーは midas であり、そのユーザーはデータベース midas の全権限を持っている。でも実際に必要としているのは、SELECT と INSERT のみである(あくまでも現時点では)。
また、DB から NAS にバックアップしているユーザーは root である。おかしい話だ。しかもバックアップをとるシェル内にパスワードを生で書いている。もうひどい。どっかの (3+4)pay のことが笑えない。
ちゃんと設定してあげる
今回準備するユーザーは下記の通り
- midas
- データベース midas 配下の全権限と権限付与ができるユーザー
- いわば データベース midas の root ユーザー
- midas_api
- API から DB に接続するユーザー
- データベース midas 配下の全てのテーブルに対しての SELECT と INSERT ができる
- midas_backup
- バックアップ取得用のユーザー
- データベース midas 配下の全てのテーブルに対して SELECT のみできる
設定したときのコマンド
- midas ユーザーにデータベース midas に
GRANT OPTION
権限を付与GRANT GRANT OPTION ON midas.* TO midas@localhost;
- midas_api ユーザーと midas_backup ユーザーの作成
CREATE USER midas_api@localhost IDENTIFIED BY 'hitonookanede';CREATE USER midas_backup@localhost IDENTIFIED BY 'yakinikutabetai';
- midas_api ユーザーに SELECT と INSERT 権限を付与
GRANT SELECT, INSERT ON midas.* TO midas_api@localhost;
- midas_backup ユーザーに SELECT 権限を付与
GRANT SELECT ON midas.* TO midas_backup@localhost;最後に
権限周りはちゃんとしておくことで事故が防げると思っているので、今回対応できてよかった。
- 投稿日:2019-07-16T20:42:58+09:00
MySQL で DB ユーザー周りのコマンド
DB ユーザー周りのコマンドをまとめてみた
DB のユーザーの作成、確認、権限の付与などいつも使うときに忘れてしまうのでまとめてみる。
※ 下記コマンドは root ユーザーで実行する必要があるものがある(てかほとんどがそうだと思う)
※ MySQL5.5 で実行確認しているユーザーに関するもの
- 登録されているユーザーの確認
SELECT Host, User FROM mysql.user;
- ユーザーの作成
CREATE USER user@host IDENTIFIED BY 'password';
- ユーザーのパスワードの変更
SET PASSWORD FOR user@host=password('password');※ password() 関数で暗号化している
- ユーザーの削除
DELETE FROM mysql.user WHERE user='user';権限に関するもの
- ユーザーの権限の確認
SHOW GRANTS FOR user@host;
- ユーザーに全権限を付与
GRANT ALL PRIVILEGES ON *.* TO user@host;
- ユーザーから全権限を剥奪
REVOKE ALL, GRANT OPTION FROM user@host;
- ユーザーに特定の権限を付与
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO user@host;※ 付与した権限は、mysqldump するのに最低必要な権限
- ユーザーから一部の権限を剥奪
REVOKE SHOW VIEW, TRIGGER, LOCK TABLES ON *.* FROM user@host;※ 特定の条件下だと mysqldump するには SELECT 権限だけよかったので他は剥奪というケースを想定
- 投稿日:2019-07-16T18:28:51+09:00
MySQLにコマンドラインからパスワードを入力せず接続
はじめに
この記事は初心者インフラエンジニアが備忘録的に書いたものです。
やりたいこと
1.MySQLに接続し、バックアップの取得
2.cronを使ってバックアップの自動化動作環境
$ mysql -V mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapperバックアップ時の問題点
$ mysqldump -u USER_NAME -pPASSWORD -x -A > FILE_NAME上記のコマンドだとパスワードが丸見えなのでセキュリティ上大問題です。
なので、$ mysqldump -u USER_NAME -p $ Enter password:PASSWORDとするのが普通です。
しかし、自動バックアップとなると後者のコマンドは使用できないため必然的に前者のコマンドになってしまいます。解決案
1.スクリプトを作成し、変数を使う
結果から言うと意味ないです。
backup.sh#!/bin/bash USER_NAME="root" PASSWORD="hogehoge" mysqldump -u $USER_NAME -p$PASSWORD -x -A > FILE_NAME上記のようにスクリプト内に記述してもcronのログに
[Warning] Using a password on the command line interface can be insecure.という警告文を吐かれます。
コマンドラインにパスワードを書くなと怒られます。2.設定ファイルを読み込ませる
今回はこの方法で解決しました。
適当な場所に設定ファイルを作成します。$ vi /etc/sql.cnf隠しファイルにしたい場合はファイル名の先頭に「.(ドット)」をつけます。(てか、隠しファイルにしたほうが安全です。)
設定ファイルの中身は下記のようにします。sql.cnf[client] user = root password = hogehoge host = localhostパーミッションを最低限に設定します。
$ chmod 600 /etc/sql.cnf以下のコマンドで設定ファイルを読み込むことができます。
$ mysqldump --defaults-extra-file=/etc/sql.cnf -x -A > FILE_NAMEこれであとはcronに設定するだけです。
まとめ
・バックアップを自動化する際はパスワードをコマンドラインに入力しない
・設定ファイルを用いたやり方が分かりやすい
・他にいい方法があったら教えてください
- 投稿日:2019-07-16T11:35:57+09:00
PHP の mysqli, mysqlnd, pdo_mysql, PDO, libmysqlclient まとめ
PHP で MySQL を利用する時、通常は PHP 拡張モジュール(extension)を用いて接続します。
その時、
mysqli
,mysql
,mysqlnd
,pdo_mysql
,PDO
,libmysqlclient
といったわかりづらい名前の諸々があるので、ここでまとめておきます。拡張モジュール
php.iniextension=mbstring.soこんな感じで設定を書くことで、ネイティブ C で記述された機能を PHP 上で利用出来るようになります。
PHP の実装よりも高速に動作するため、 MySQL との通信処理など処理速度がボトルネックになるようなものは拡張モジュールとして実装することで、処理速度の向上をしています。
拡張モジュール用ライブラリ
まず、 MySQL 拡張モジュールを実装する際にベースとされるライブラリがあります。
現状は
mysqlnd
とlibmysqlclient
のどちらかです。これらは、 PHP のコンパイル時に
--with-pdo-mysql=mysqlnd
などと指定して選ぶことが可能です。mysqlnd
MySQL Native Driver が現在推奨されているネイティブライブラリで、デフォルトでこれが使われます。
libmysqlclient
よりも様々な点で優れているため、こちらを選ぶことがほとんどのケースで最適です。
mysqlnd
それ自体も PHP 拡張モジュールとして実装されているため、パフォーマンスも高いです。libmysqlclient
mysqlnd
の前に MySQL チーム(現 Oracle)が作成した、 PHP 依存のない純粋な C 言語のライブラリです。利用時に MySQL をインストールする必要があったり、 MySQL ライセンスのため PHP に同梱出来なかったりと欠点がいくつかあります。
サポート自体は継続されていますが、一般に利用することはほぼないでしょう。
まとめ1
mysqlnd
とlibmysqlclient
は MySQL に接続するためのベースライブラリで選択可能- 基本は
mysqlnd
を選択するmysql
単純に
mysql
と言った時は、大体 PHP 2.0 からある古い拡張モジュールのことを指します。mysql_connect 関数などを提供している extension ですが、 PHP 7 以降は利用出来ません。
なので、この拡張モジュールは新規開発では利用されないでしょう。そもそもトランザクションすらないので、実用性もありません。
超絶古い PHP 3 や 4 で開発されたプロダクトがまだ生きていて、保守しなければならない時に見かけることがあるかもしれない、くらいです。mysqli
MySQL Improved
拡張モジュールです。公式ではこの拡張モジュールが推奨されています。$mysqli = new mysqli("example.com", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: " . $mysqli->connect_error; } $res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL"); $row = $res->fetch_assoc(); echo $row['_msg'];こんな感じで手軽にクエリを投げることが出来る API を提供しています。
mysql
と似た API も提供しているので、移行も比較的楽に可能です。PDO
PHP の基本機能として、複数のデータベースアプリケーション(MySQL, sqlite, Oracle など...)間で出来るだけインターフェースを統一し、同じ PHP コードで別 DB にも投げれるようにした API 実装が PDO です。
これを使うことで、接続先のDB用のドライバを切り替えるだけで手軽に別の DB アプリケーションへ移行することが出来ます。
(実際に別の DB アプリケーションに移行することがあるのかはおいといて)Laravel や CakePHP は PDO をラップして実装されているので、
mysqli
拡張モジュールを利用することは出来ません。
逆に、 Doctrine や CodeIgniter はmysqli
を使うことも可能です。PDO を利用するのであれば、
extension=mysqli
としてmysqli
拡張モジュールを有効にする必要はありません。pdo_mysql
pdo_mysql
拡張モジュールは、 PDO API で MySQL に接続するためのドライバです。PDO を使って MySQL に接続したい場合はこの拡張モジュールを有効にする必要があります。
他にも、
pdo_sqlite
pdo_pgsql
などのドライバ拡張モジュールがあり、それらを有効にすることで PDO を通して接続可能になります。まとめ2
mysql
はもう使われない古い拡張モジュールmysqli
は推奨されている拡張モジュールpdo_mysql
は他の DB に繋ぐことも考えた抽象化層であるPDO
を一個挟んだ拡張モジュールフレームワークや OR マッパーによってこの辺りは隠蔽されることがほとんどですが、実際に使われている拡張モジュールが何かを知ることは
php.ini
の設定や Dockerfile を書く時などで必要になってきます。これでもう間違わないで拡張モジュールを選べるようになったのではないでしょうか。参考資料
- 投稿日:2019-07-16T05:41:08+09:00
シェルスクリプトでMySQLからデータ取得
シェルスクリプトからMySQLの実行結果を、Slackへの通知やPrometheusのexporter用データの生成などに利用しています。
mysqlSelect.sh#!/bin/bash # シェルスクリプトからMySQL実行 # コマンド例)$ sh mysqlSelect.sh 127.0.0.1 3306 mysql_user mysql_password # 第1引数:MySQLホスト # 第2引数:MySQLポート # 第3引数:MySQLユーザ # 第4引数:MySQLパスワード # SQL sql="select id, name from db_name.table_name;" echo "sql=$sql" # SQL結果出力先 nowDateTime=(`date '+%Y%m%d%H%M%S'`) sqlResult="./logs/mysqlSelect_${nowDateTime}.log" # SQL実行 $(mysql -h $1 -P $2 -u $3 -p"$4" -N -e "${sql}" > $sqlResult) # SQL実行結果 cat $sqlResult sqlCount=`cat $sqlResult | wc -l` echo "sqlCount=$sqlCount"
- 投稿日:2019-07-16T02:53:30+09:00
SQLアンチパターン-13章 恐怖のunknown-まとめ
NULL値にもっと注意深くなろう
今回は値がないということに対する正しい挙動を学ぶことがゴールです。
プログラミング言語によって「値がない」ということをどう扱うかは様々です。SQLではどのように扱われるでしょうか。
それはNULL
です。
- SQLは
NULL
と(false
、0
、空の文字列
)を区別します。NULL
との四則演算の結果は必ずNULL
になります。決して0
の扱いではありません。
coalesce
関数で0
に変換はできるNULL
との文字結合もまたNULL
です。(DBの種類によっては違うそうです)NULLを含む検索
SELECT * FROM Books WHERE category = 'comedy';この検索を行ったとき、
category
がNULL
の行は当然取得されません。SELECT * FROM Books WHERE NOT (category = 'comedy');この検索を行ったときもまた、
category
がNULL
の行は取得されません。
ある値ではない
ということは等価演算になりますが
つまり、NULL != 'comedy'
がTRUE
、もしくは
NULL == 'comedy'
がFALSE
を返せば良いのですが
NULL
との等価演算は決してTRUE
にはなりません。
どこまでいってもNULL
です。MySQLには、
NULL
に対応した演算子があります。
<=>
です。SELECT * FROM Books WHERE category <=> NULL;プリペアドステートメントにNULLを渡せるか
プリペアドステートメントで予約語である
NULL
を値として渡すには
bindValue()
を使うしかありません。第3引数に型を指定できるので
そこでPDO::PARAM_NULL'を指定してあげます。
excute`でそのまま渡すのでは対応ができないということです。
NULLから逃げるのも違う
そんな
NULL
はTRUE
orFALSE
で扱えるわけでもないので
また別の値を使った独自のルールを使うことで対応しようとすることもあるでしょう。
例えば数字でステータスを表す時に
1以上は有効
、-1は存在しない
というルールを決めたとします。
得点計算をするカラムがそのように扱われていたとしたらどうなるでしょう。
もし仮に、平均や偏差値などを算出する際に
単純に-1
を計算に含めてしまうと、求めていない計算結果が出力されてしまいます。ここでは、本来得点を意味するデータが入るところに
別論理の存在しているかどうか
を定義する-1
が混ざってしまっています。
これではまともなテーブル設計とは言えません。
アプリケーションレベルでも注意する必要のある厄介な仕様となってしまいます。NULLを理解しよう
NULL
は真偽のロジックである2値論理
とは別の、3値論理
として理解する必要があります。
4値論理
とかもあるそうで。
参考NULLと演算子
NULL
を使った計算がどうなるか、まとめてみましょう。下記の計算結果は全て
NULL
です。NULL = 0 NULL = 123 NULL <> 123 NULL + 123 (四則演算全て) NULL = NULL NULL <> NULL特に
NULL = NULL
はこういう論理です。
不明な値と不明な値を比較しても、等しいかどうかはわからない
NULL <> NULL
は
不明な値と不明な値を比較しても、等しくないかどうかはわからない
ということになります。NULLと論理式
NULL AND TRUE
は、NULL
となります。
NULL AND FALSE
では、AND FALSE
でFALSE
が確定しているのでFALSE
になります。
NULL OR FALSE
はNULL
となります。
NULL OR TRUE
はOR TRUE
でTRUE
が確定しているのでTRUE
になります。
NOT (NULL)
は、NULL
になります。
AND FLASE
とOR TRUE
がキーですね。
これはそのまま覚えるのが良さそう。NULLで検索したい時
MySQLには
NULL
で検索するための構文が用意されています。
それがIS NULL
です。SELECT * FROM Books WHERE category IS NULL;こう使います。
IS NOT NULL
も存在します。呟き
思ったけど、
NULL
って虚数
みたいな感覚で扱うとわかりやすいんじゃ??メモ
a<=>b
はaとbは等しい(NULLにも対応)
=>spaceship
というらしい
a<>b
はaとbは等しくない
知らなかった。