技術めも技術めもIT系のめもを蓄積していこうかとSequenceのdefault設定MariaDB10.3系で導入されたsequence(nextvalの方)において、ほかテーブルのdefault値として設定できるようです。確認したバージョンは、10.3.8です。最初にsequenceを作ります。0」をつけておきます。0」をつけても、1ずつインクリメントされます。|+--------------------------+-------次に以下のようなテーブルを作成します。(user_id));上記テーブルにおいて、seq_noカラムのdefault値にsequenceの結果を設定したので、以下のようなinsertにて、nextvalの結果が格納されます。|+---------+--------+--------+sequenceが導入されてから、見落としていたのですが、なにげにこれは嬉しい。公式にもこっそり書いてありました。https://mariadb.com/kb/en/library/sequence-overview/PERSISTENTタイプの生成カラムで設定できたら、変更不能な連番を作り出せたので、それがあっても良かったのかなと少し思います。(とはいえ変更不能も困るか。)なお、sequenceの上限は、9223372036854775806で、javaなどのlong型の限界値9223372036854775807より-1したものとなっています。CONSTRAINTについて挙動を確認してみました。使用したバージョンは、10.3.8です。table時に一緒に制約を入れてみる。10))これで、statusカラムは1以上10以下しか入らないはずで、以下のSQLを流してみる。`test`.`users`見事に、0と11が弾かれている。0と11を登録可能なようにするためには、一度制約を解除する必要がある。次に、ALTERのときの動きを見てみる。前回作って800万件入っている以下のテーブルを利用する。0やはり、大量のレコードがある状態では、制約を掛けるのも時間がかかることがわかる。最後にCHECK制約の仕様をまとめておきます。keyに対しても制約は掛けられるauto_incrementに対しては制約は掛けることができない合わせて公式のマニュアルも参照ください。カテゴリー:mariadbMariaDBの生成カラムのALTER時の速度確認MariaDBの生成カラムをALTERするときの速度検証してみました。確認バージョンは、10.3.8です。準備として、以下のようなテーブルを作成し、データを投入しておきます。sec)下準備として、約800万件のデータを投入。この状態で、通常・PERSITENT・VIRTUALのカラムをALTERで追加してみます。通常カラムは、最後尾に追加すると、INSTANTが発動してしまうので、すべてAFTERで途中に差し込んでみます。0カラム追加:17秒インデックス追加:20秒カラム削除:16秒という結果でした。0カラム追加:36秒インデックス追加:19秒カラム削除:16秒という結果でした。0カラム追加:0.01秒インデックス追加:20秒カラム削除:0.01秒という結果でした。通常に比べて、ADDも遅くなるという結果でした。通常に比べて、ADD/DROPも圧倒的に高速ですね。インデックスの作成速度は変わらないですね。基本的には、VIRTUALを使っていく方針でよいのかな?という考察です。以下2点勘違いをしていた。COLUMNにインデックスを張ることができる(すいません、できないと思ってました。)MariaDBでもCHECK制約を使うことで、「JSONの正しさ」を保証できる(すいません、できないと思ってました。)以前書いた記事にも訂正を追記しておきました。いうわけで、MariaDB10.2.17、10.3.10のバージョンで生成カラムについて見ていきます。以下のようなテーブルを作ってみます。CHARSET=utf8mb4;ちょっと長いのですが、ポイントは以下の通り。addrsカラムに対して、PERSISTENTタイプの生成カラムを利用して、全文検索INDEXを張るbodyカラムに対して、JSONの型チェックを行うsignカラムに対して、VIRTUALタイプの生成カラムを利用して、JSONの部分抽出を行い、セカンダリーINDEXを張る1はカラムを結合した状態を全文検索にしたい場合、2はJSONの正しさを保証したい場合、3はJSONの中の特定のキーを条件に高速にデータアクセスしたい場合、に用いられるような想定です。この状態のテーブルを見てみます。sec)生成カラムの情報がdescのExtraにも表現されていますね。・・・」は付けられないようで、生成カラムの評価ができない(失敗?)の場合は、NULLが入る仕様のようです。次に以下のようなデータを流し込んで、結果を見てみます。sec)ちゃんとデータが入っていますので、PERSISTENTタイプのaddrsへの全文検索と、VIRTUALタイプのsignへの検索を行ってみます。sec)ちょっと件数少ないですが、両方共INDEXが効いていることがわかります。生成カラムって実際どのくらい使われているのかわかりませんが、うまく使うことで、機能追加にも柔軟な対応ができそうな予感です。生成カラムの進化はこれからも期待しています。ADDは、生成カラムには効かないので、ご注意ください。INSTANT対応も今後はあるのでは、と期待してます。カテゴリー:mariadbpt-online-schema-changeをslaveで実行したときpt-online-schema-changeをslaveに対して実行した場合に痛い目見たので、その備忘録。そもそもの話、master側で不要なINDEXをslaveのみ張ってました。OLTP側ではいらないINDEXだけど、集計とかであると嬉しいINDEXってやつですね。slaveに張っているINDEXをpt-oscでオンラインで張替えをしようとしたら、リレーログからの取り込みが、旧テーブルに行ってしまい、pt-oscで作成される新テーブルに入らなかったいうオチ。その結果、レプリケーションの不整合が起きて、最初から構築するという目にあった。slaveを掛けてからDDLを実行すれば良かったです。いえ、リレーログからのデータ取り込みの際、pt-oscで実行しているとダメなことがわかったので、もうちょっとこのあたりについて調べてみようかと思います。mysqlMariaDB10.2のderivedテーブルに対するCP前回の記事で、unionの条件判定が10.2から変更になっていると記載しましたが、どうやら、10.2から追加されたoptimizer_switchの動作によるものらしい。具体的には、condition_pushdown_for_derived=onというパラメータに依存するようです。実際に、上記パラメータを切り替えてテストしてみた。sec)というように、condition_pushdown_for_derivedのパラメータによって上記の差異がでる。気になったのが、10.2から、mrr=offmrr_cost_based=offに変更されている。mrrはセカンダリーインデックスのIO負荷軽減のものであったかと思うが、このあたりの絡みでパフォーマンスにどう影響があるのか、再度調べてみようと思う。MySQL8ではonのままであった。カテゴリー:mariadbunionする前と後のwhereについてunionする前にwhereをするのと、unionした後にwhereをする場合、mysqlにおいて、差異はあるのかを確認してみた。「unionする前にwhereをする」のと「unionした後にwhereをする」が同パフォーマンスであれば、multisourceでviewを作っておいても十分高速に引ける可能性がある、と思ったところです。いろいろなバージョンで試してみました。以下のようなテーブルを作成しておき、100万件登録しておきます。sec)これ、正直言って、動機を満たす結果を得るのは無理だと思ってたが、、、MariaDB10.2と10.3は嬉しい結果を出してくれました。tableのinstantについてMariaDB10.3、MySQL8から、ALTER構文にinstantの機能が導入されました。利用できる範囲は限られていますが、その中であれば超高速でALTERが完了します。メタデータだけ書き換えて、実際のデータは初回アクセス時に補完する形をとっているような感じです。詳細は、公式マニュアルを参考指定だければと思います。https://mariadb.com/kb/en/library/instant-add-column-for-innodb/https://dev.mysql.com/doc/refman/8.0/en/alter-table.html実際にデータを突っ込んでみて、どのくらいALTERが高速になるか試してみました。以下のようなテーブルを用意して、100万件のデータを入れておきます。