rdbmsTweetMySQLでGeoHashを試してみた。APIを利用して、住所から緯度経度を逆引きしてvarcharで作った列に情報を格納していましたが、あの時にgeohashを知っていたら色々とサービスで使えたかもしれない。思い、少し確認してみた。01111左から数え始め、経度は偶数ビット(1110001101011)、緯度は奇数ビット(101100101011)として表現される。二進数は1ビットずつ、左のビットから順に評価されるて、緯度については、-90から+90が2つの区間に分割され、経度は-180から+180の区間に分割される。sec)MySQL8.0のgeohash関連関数ST_から始まるものが、最新のGEO関数です。ST_GeoHash(経度,緯度,最大長)、ST_GeoHash(point,max_length)接続文字セットと照合順序でgeohash文字列を返します。ST_LatFromGeoHash(geohash_str)geohash文字列値から[-90、90]の範囲の倍精度数として緯度を返します。ST_LongFromGeoHash(geohash_str)geohash文字列値から[-180,180]の範囲の倍精度数値として経度を返します。ST_PointFromGeoHash(geohash_str、srid)geohash文字列値を指定して、解読されたgeohash値を含むPOINT値を返します。Mapを利用して確認オラクル青山オフィスのgeohash(5桁:xn76g)を利用してレストランを検索。データをImport後に、緯度-経度を読み込んで確認してみる。検証用のデータはこちらからダウンロード可能です。region:Asiahttp://download.geofabrik.de/asia.htmlインポート方法はこちらが参考になります。APIに渡して、地図を確認してみると、問題なく、同じGeohashにあるレストランが表示されているようです。インデックスを併用すれば、結構レスポンスの良い空間データベースになりそうです。上記処理を実行した時の、MySQLで実行プランは以下のような感じです以上で確認は終了ですが、ついでにSRIDを指定してテーブルを作成する事が出来るようになっていたので確認してみた。緯度-経度の順番でINSERTする場合は、列にSRIDを指定して作成した方が良いようです。反対に経度-緯度の場合はDefaultの0のままで良い。データINSERT時に緯度と経度の順番のバリデーションが無いのであった方がよさそうです。rdbmsTweetデモの準備をしていて、MySQL5.7で動いていたページが以下のようにエラーになったので、今後の為にメモ。マニュアルやブログで確認してはいて、情報としては認識していたのですがMySQL8.0.4以降の変更点なので忘れてました。8.0では、mysql_native_passwordではなくcaching_sha2_passwordがデフォルトの認証プラグインです。変更理由はセキュリティ強化とパフォーマンスcaching_sha2_passwordおよびsha256_password認証プラグインは、mysql_native_passwordプラグインよりも安全なパスワード暗号化を提供し、caching_sha2_passwordはsha256_passwordよりも優れたパフォーマンスを提供します。8.0.4以降の認証プラグインであり、mysql_native_passwordではなくデフォルトの認証プラグインでもあります。https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password現状では、コネクターが対応していない為、アカウント認証をMySQL8.0.4以前の認証方法に戻します。8.0サーバに接続する際に互換性を保証する最良の方法です、それらはデフォルトの認証プラグインとしてcaching_sha2_passwordを使用して構成されています。8.0.4以降のlibmysqlclientクライアントライブラリ。mysqlやmysqladminなどの標準的なMySQLクライアントはlibmysqlclientベースである為、互換性があります。higher.注意:2018年2月現在、caching_sha2_password互換クライアントはGAになってない為、MySQL8.0がGAになるまでしばらく進捗を確認する必要があります。USERステートメントを使用して変更できます。8.0.4以降にアップグレードした後で互換性の問題が発生した場合、これらの問題に対処して8.0以前の互換性を復元する最も簡単な方法は、サーバを再設定しプラグイン(mysql_native_password)を利用します。必要に応じて、オプションファイルに次の行を設定してください。[mysqld]default_authentication_plugin=mysql_native_passwordMySQL8.0をアプリケーションで利用される場合はこちらを確認ください。Firewallも再度機能確認してみました。Firewallの組み合わせで、重要な情報を扱うデータベースに追加する事で、セキュリティを更に強固にすることが可能です。Webで公開しているアカウントにFirewallを設定して,外部からの入力フィルターをアプリケーションのフレームワークのEscape処理のみに依存しない。社内で利用しているアカウントにWHERE句を利用しない参照処理をさせないように制限する。Monitorと連携してセキュリティ担当者にメールやSNMPで知らせる。“サイバーセキュリティ経営ガイドライン@経済産業省“に「指示5サイバーセキュリティリスクに対応するための仕組みの構築」に、新たに「攻撃の検知」を含めたリスク対応体制についての記載”が改定されていました。Auditの機能が拡張されています。主な変更点は、以下の4つになります。関わるデータに監査が必用な場合は、MySQLのオプション機能として利用するを検討しても良いかもしれません。MySQL5.7.21から監査設定用のインストールスクリプトもMyISAMからInnoDBに変更されています。FirewallのスクリプトもEngineがInnoDBに変更されていました。Auditを利用されている方は、監査定義で利用するテーブルを以下のようにマニュアルでInnoDBに変更する事を推奨となっています。Auditのファンクションを利用してJSONのログを読み取る。audit_log_read_bookmark()最後に書き込まれた監査ログイベントのブックマークを表すJSON文字列を返します。このブックマークはaudit_log_read()に渡して、読み込みを開始する場所を指定するのに適しています。audit_log_read()監査ログからイベントを読み取り、監査イベントの配列を含むJSON文字列を返します。GZIPされていても、audit_log_readでログファイルを確認する事が可能なようだ。Auditログの暗号化AESでAuditログを暗号化する機能が追加された。Auditログは暗号化されているため、先に複合化しないと読み込めない。RCがリリースされたので、その中でJSON関連の関数JSON_TABLEがリリースされていたので、合わせて挙動を確認してみました。MySQLにはJSON関数が30弱ありますが、便利なJSON関数が増えてきているので、これまで以上にNOSQL関連データを扱う処理の選択肢として活用出来そうです。参照:https://mysqlserverteam.com/the-mysql-8-0-4-release-candidate-is-available/多くの変更があるので、上記ブログを確認頂いた方が良さそうです。JSONデータを作成して、JSON_TABLE関数を利用してデータを抽出。SONデータをそのまま、通常のリレーショナルなテーブルから列を抽出するように扱う事が出来る。JSONデータにWHERE句を利用した抽出も可能。JSONデータと生成列を活用してINDEXを利用して参照した方が早いとは思いますが、使いどころは色々とあるかと思います。rdbmsTweet5.6.1で既に実装されていてDefaultでONになっているので,5.6や5.7では普段殆ど気にしてませんでしたが、質問頂いたのでindex_condition_pushdownの条件を再確認。DefaultはONになっています。OFFにするメリットはあまり無いかと思います。ストレージエンジンからフェッチしたレコードをMySQLが評価してWHERE区の条件による絞り込みを行っていたが、インデックスが貼られたカラムを用いた評価については、ストレージエンジンへ条件式を渡し(プッシュダウン)、ストレージエンジン側で評価を行わせることによってオーバーヘッドの低減させる。操作を減らすことです。バッファーに読み込まれています。セカンダリインデックスにのみ使用されます。テーブルには選択条件がある。ストレージエンジンがICPをサポートする。index_condition_pushdownスイッチがオンで、ICPの使用がNO_ICPヒントによって無効にされていない。クエリーは、複数表の更新または削除ステートメントでは無い。この要件の理由は、選択/結合と更新の両方に同じハンドラが使用されるためです。プッシュされたインデックス条件は、更新部分を実行するときにストレージエンジンによって適用され、誤ったレコードを更新または更新するためのレコードを見つけられない結果になることがあります。JOIN_TABは、”NULLキーをフル・スキャン”実行中にオンまたはオフにできる条件を保護しているサブクエリの一部ではありません。setup_join_buffering5.結合タイプはCONSTまたはSYSTEMではありません。これらの結合タイプを除外する理由は、これらの結合タイプが、ストレージエンジンから一度だけレコードを読み取り、後でそれを再利用するように最適化されているためです。プッシュされたインデックス条件が結合シーケンスの前のテーブルからのフィールドを評価する結合では、プッシュされた条件は、レコード値が最初に必要とされたときにのみ評価されます。6.インデックスはクラスタ化インデックスではありません。クラスタ化されたキーでインデックス条件をプッシュすると、クラスタ化されていないキーよりも大幅に低くなります。WL#6061が実装されている場合、この制限は再評価する必要があります。7.仮想生成列の索引は、ICPではサポートされていません。License)商用版のMySQLをDockerで利用する事が可能です。https://blogs.oracle.com/mysql/mysql-enterprise-edition-now-in-docker-storeMySQLライセンスについては此方を参照下さい。https://www.mysql.com/jp/products/物理サーバーライセンスなので、Dockerを複数立ち上げるような開発環境ではコストメリットが大きいかと思います。STEP2:Checkoutに進むと以下のように入力が画面が出てくるので、必要な入力項目を入れて下さい。Checkoutするとインストール方法とイメージのPULL用URLが表示されます。自分のアカウントで利用可能な、パッケージが以下のURLにアクセスする事が出来ます。ここからは実際にイメージをダウンロードして初期設定後に環境の確認をしてみます。あとで、設定を適宜変更したいのでデータやオプションファイルのパスを指定してインスタンスの初期化を行っています。ログインして、初期パスワードを変更後にバージョンとプラグインを確認。現状ではプラグインを設定してないのでロードはされてませんが、モジュールが存在するところまで確認してあります。本日の説明はここまで。Editionの機能を利用して開発や検証したい方にはお勧め。rdbmsTweetMySQL8.0のヒント句が便利になっていたので基本動作確認。バッチ処理前にSETコマンドで動的に設定していたセッション変数を実行するクエリーにヒントとして追加する事が出来るようです。by等でソート処理していて、Sort_merge_passesが多発している場合にソートバッファーの不足をヒント句で回避してディスクI/Oの発生を抑える事が出来る。大量データのソートが多いバッチ処理などに組み込んでおくと、処理が早く終わらせる事が出来る。sec)使いどころが難しいが、以下のようにauto_increment_increment等の値もヒント句で変更出来るようです。sec)その他、Worklogによると以下のように、色々なセッション変数をSETコマンドを利用しないでヒント句で指定出来るようです。RCのDockerイメージがリリースされていたので、今後の検証やデモ用に設定しました。手軽に検証出来るので、軽く検証するにはお勧めです。色々な場面で活用する事が出来るかと思います。rdbmsTweetMySQL8.0がリリース候補版になりました。Defaults詳細:http://mysqlserverteam.com/the-mysql-8-0-3-release-candidate-is-available/全てをカバーしてませんが、先日のセミナー資料で概要を紹介しています。