All You Need to Know about Isolation Levels and Read Inconsistencies
Just like real life, the world of computer science is replete with trade-offs. Relational databases are no exception. When interacting with relational databases, we face the dilemma between data consistency and transaction concurrency. The former guarantees the data is trustworthy, while the latter ensures relational databases can conduct transactions swiftly. Both are desirable qualities of relational databases, but we cannot simultaneously achieve them to the fullest extent....DBのプライマリキーは複合主キーではなく、サロゲートキーを使うべし
別件でUnicodeの「サロゲートペア」について調べていたところ、データベースの「サロゲートキー」と「複合主キー」についての記事が サロゲート つながりで引っかかって来て思いっきり脱線しました（笑） そんなこんなで、あまりデータベースに慣れ親しんでいないと、「サロゲートキー」「複合主キー」「ナチュラルキー」って何？──となる人は多いはずなので、自分自身のおさらいも兼ねて一度まとめておこうかと思った次第。 プライマリキー（主キー） データベースのたいていのテーブルに存在する、データを一意に管理するためのキー制約。このキーが指定された列中には重複する値を入れることができず（ユニークキー制約）、空白（NULL）にすることもできない。 ユニークキー（一意キー、ユニークインデックス） このキーが指定されている列中には重複する値を格納できず、一意にならなければならないというキー制約。ただし、プライマリキーと異なり空白（NULL）は格納可能で、空白（NULL）のみは重複ができる。このキーが指定されると同時にインデックスも貼られるため、ユニークインデックスと同義である。 サロゲートキー（代理キー） オートインクリメント属性等により、連番の数値が格納されているユニークな値を持つカラムに対してプライマリキー制約を付与した場合のプライマリキーのこと。テーブル内で一意となる唯一の行レコードを特定するための列の集合（ナチュラルキー）を代替するため代理キーとも言われる。単一の列で全行レコードの一意性を識別できるのでシステム的に有益なのだが、実サービス等に利用するデータとしては意味をなさず、データ利用者側の観点からだとデータベースの物理ストレージ領域を無駄に圧迫する列でしかない。 ナチュラルキー（自然キー） テーブル内の行レコードを特定するためのユニークキー制約のついた列の集合のこと。行レコードの一意性が識別できれば単一の列でもかまわないが、例えば、WordPressだと「投稿ID」「タグID」の組み合わせで、とあるタグに属する投稿データを一意に識別できるような場合、それら2列の集合体がナチュラルキーと呼ばれ、そのナチュラルキーを持つテーブルがwp_term_relationshipsだ。原則的にナチュラルキーに含まれる列はユニークキー制約をもっていなければならないため、プライマリキーに設定されていることが多い。データ利用者側の視点では、意味あるデータのみによって一意性が識別されるナチュラルキー型テーブルは用途が明確で無駄がない（と見えるようだ）。 複合主キー 複数プライマリキー、サロゲートキー＋ナチュラルキー、プライマリキーなしの複数列集合のナチュラルキーのみといったテーブルが複合主キー型のテーブルといえる。要は単一列のみで行レコードの一意性が識別できないタイプのテーブルである。他のテーブル同士をリレーションさせるために外部キーの結合のみを管理するようなリレーションテーブルによく見られるタイプ（ナチュラルキーの項で紹介した、WordPressのwp_term_relationshipsテーブルなど）。リレーションテーブルを覗いたことがある人やDB設計したことがある人には良く理解できるはずだが、このような複合主キー型テーブルはそれぞれの外部テーブルのサロゲートキーを集中管理するような構造になっていて、実データのリレーション関係が一目ではわからないのが常だ。かといって、ナチュラルキーをプライマリキーとしたテーブルのプライマリキーを外部キーとしてリレーションさせるとそのリレーションテーブルを見るだけでデータの結合性が一目でわかるものの、参照コストの高い文字列データ等がデータベース内に重複して存在することになって、データベース全体としてパフォーマンス低下を招く上、物理ストレージ領域の圧迫もサロゲートキー以上に進んでしまう。 また、特にナチュラルキーが一意性識別に関与するようなテーブルだと、ユニークなデータを取り扱う際のシステムコストが高くなる。 言葉の整理をしてて改めて感じたのだが、複合主キーのテーブルを操作するのは結構面倒そうだということ。まぁ、実際に面倒なんですがね。例えば、複合主キーのナチュラルキーのうち一つの列の値を変更したい場合、その行レコードを特定するために同じナチュラルキーを検索条件に入れなければならなかったりして、ちょっと処理を考えるのがイヤになる。 プライマリキーが二つ以上あるテーブルをUPDATEしようとするとMySQLに怒られたりするしね…。 私個人の結論としては、データベースは人ではなく、システムが応答し易いように作った方が良く、パフォーマンスを犠牲にしてまで人間側のデータ視認性にこだわるのは本末転倒だと思いますね。人間側に認識し易いデータは別途中継処理等を作って整形してあげるのがデータベースを利用するシステムの正しいカタチではないかと。なぜなら、人間側が要求するデータは、時と場合と人によってフィルタリングさせないと有益なデータにならないケースがほとんどだから…。つまり、プライマリキーは、複合主キーとかは使わず、サロゲートキーを使うのがベストプラクティスだと思うわけです。...MHA(MasterHighAvailabilityManager)の導入設定
MHA(MasterHighAvailabilityManager)の導入設定 使用バージョン：mha4mysql-manager-0.53-0、MySQL-5.5 ①簡単な利点などの解説 MHAとはmysqlのマスタ障害時に最新のスレーブをマスタとして他のスレーブの差分を補完しマスタの向き先を変えてくれるプロダクト。 Heartbeat+mon+mysqlに比べるとreplicationの再構成も行ってくれるので切り替わってもDBがシングルにならないのが利点。(3台以上の構成の場合) 作者のスライド 公式サイト MHAの制約：mysql5.0以上、SBR(ステートメントベースレプリケーション)の場合LOAD DATA INFILEを使えない ※マネージャはadminサーバ、ノードはDBサーバ(マスタ・スレーブ共通) ② マネージャにてインストール ※以下、admサーバから実施 ・インストール [php] wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.53-0.noarch.rpm yum –enablerepo=rpmforge install \ perl-Config-Tiny \ perl-Time-HiRes \ perl-Log-Dispatch \ perl-Parallel-ForkManager \ perl-Params-Validate yum install perl-DBD-MySQL rpm -ivh mha4mysql* [/php] 入ってなかったら [shell]yum install –enablerepo=remi mysql mysql-server perl-DBD-MySQL[/shell] ※マネージャはmysql-serverいらないかも ③ ノードにてインストール ※以下、dbサーバから実施...スクリプトで WordPress の DB にアクセスしたい
WordPress の DB にスクリプトからアクセスしたい場合、単純に MySQL にアクセスして処理すればいいのですが、何らかの理由で WordPress の関数を使いたい場合（画像の投稿をスクリプトで行う等）は、 require_once('wp-blog-header.php'); でOKなのですが、一部のメソッドは直接そのメソッドが記述されたソースをインクルードする必要があります。 例えば、カテゴリ登録するための wp_insert_category() を使いたい場合、 require_once('/wp-admin/includes/taxonomy.php'); とする必要があります。/wp-admin/includes/にクラスがあるので必要なものを探しだして、インクルードします。 また、wp-blog-header.phpをインクルードするということは、例えば、HTTPSで必ずアクセスさせたり、ログインユーザのみに見せるといったようなWebの制限をfunction.phpなどに入れて、サイト全体に制限かけた場合、影響を受けます。 そこで、そういった制限を入れた場合は、その制限処理に、 if ( ! preg_match( '/^(script.php|hogehoge.*.php)/', basename( $_SERVER['SCRIPT_NAME'] ) ) ){ // 制限処理 } のように除外したいスクリプト名（上記の場合、「script.php」か「hogehoge.*.php」にマッチするスクリプト）を指定して、処理から除外する必要があります。 これをしない場合、スクリプトを実行すると、制限に引っかかり、処理が止まります。...WordPressのデータベース仕様書を書いてみた
以前から、 WordPressCodexのデータベース構造のページが見づらくて、仕様も古くなっていたのが、ちょっと気になっていたので、自分でデータベース仕様書を書き直してみようかと思っていたところ、WordPressでシステム開発する業務が急激に増えて来て、特にマルチサイト系の仕組みを把握する必要もあって、WordPressのデータベース仕様書をチートシート的に書き起こしておかないとシステム設計がしづらいという状況になってきた。そこで、自分なりのデータベース仕様書（テーブル定義書）を書いてみた次第。 基本的には日本語版Codexと本家WordPress.orgのサイトを見ながら、実際のデータベース構造と見比べて書いてみたのだが、マルチサイト系のテーブル部分はまだちょっと不明なところが多いんだなこれが（特にwp_sitecategoriesテーブル…このテーブルってwp3.0以降ではドロップされてしまったのか？…みたいな）。 まぁ、一通り書き切ったので、ここに共有化しておこうかと。 PDF形式：WordPress3.4.xDataBase (655KB) バージョン：0.1 さて話は変わる（…ようでそうでもないのだ）が、今月12/11に新安定バージョンのWordPress3.5がリリースされたので、さっそく本デベラボも3.5へアップグレードしてみた。 管理パネルからの自動アップデートが上手く動作しなかった（パッケージデータを取得して展開している途中でタイムアウトしてしまう）ので、一旦アップグレードするのを躊躇したのだけど、PHP側の設定を見直すのは面倒だったので、最終的に手動でアップデートしてしまいました。アップデート後、今のところテーマやプラグインに不具合もなく動いているようだけど、管理パネルに常にWordPressの自動更新失敗のノーティフィケーションが出るようになってしまった…（大した影響はなさそうなので、そのうち直そうかと）。 そんなわけで、WPも3.5になったので、書き上げたデータベース仕様書と構成の突合せをしてみようと、バージョン3.5のデータベースを覗いてみた。 ざっと構造を見てみましたが、3.4.x → 3.5 でデータベースへの変更はなかった（マルチサイト側は突合せてませんが…）。 本記事が今年最後のデベラボの投稿になりそうだ。 それではみなさん、良いお年を。...WordPressでHyperDBによるデータベース二重化を行う時の注意点
商用サイトなどをWordPressで構築する場合、WEBアプリケーションサーバとデータベースサーバを切り離して、データベース側を二重化することが良くあります。単にデータベースへのアクセス負荷を分散させてサービスのパフォーマンスを向上させるためでもあり、もし障害が発生した場合にミラーリングしている副系ノードのデータベースから正系データベースを復旧させることができる意味でも、サービスとして最悪のケースである「データ消失」のリスクを軽減できるからです。 WordPressにはそういう正副ノードのミラーリングデータベース構成にも対応できる「HyperDB」というドロップインがあります。これを使うことで、データベースの更新はマスターデータベースのみに集約し、データベース参照はマスターとセカンダリに分散させるということができます。 （※ 「HyperDB」ドロップインによるデータベース二重化の方法は、QuickKnowLedge : MySQL HyperDB + Keepalivedを利用した、wordpress の負荷分散 などで詳しく説明されています。） さて、この「HyperDB」を利用しているWordPressサイトで、WordPressのコア処理に依存しない形で、データベースアクセスの処理を独自拡張している場合に、問題が発生することがあります。 一体どういうケースかと言うと、WordPressのデータベース制御系のグローバル変数「$wpdb」をWordPressページ外で使う場合です。 例えば、WordPressの投稿からAjaxなどで直接独自に作成したPHPをコールし、そのPHP内でWordPressのデータベースへのアクセスを行う処理があるような場合です。このようなWordPressのプログラム群から独立した外部プログラムでデータベースアクセス用のグローバル変数「$wpdb」を利用する場合に、 [code lang=“php” light=“true”]require_once(’./wp-config.php’);[/code] …と、上記のように設定ファイルをインクルードしているケースだと「HyperDB」を有効化した途端に動かなくなります。 この場合、インクルードするファイルを設定ファイルからブログヘッダーに変更することで、正常化します。 [code lang=“php” light=“true”]require_once(’./wp-blog-header.php’);[/code] 特にインクルードファイルを変更してもパフォーマンスに変化が見られなかったため、結論的には、外部プログラムで「$wpdb」を利用する場合は「wp-blog-header.php」をインクルードしておくのが無難なようです。...