makoto_fujimotoのblog

株式会社進角
代表 藤本信のブログです
どうぞよろしくお願いします

カテゴリ : データベース

Webサイトのアクセスログはインターネットの歴史とともに様々なマーケティングに活用され、マネタイズの情報源にもなって来ました。

おさらいしますと、アクセスログは基本的にIPアドレス、閲覧ページ、ブラウザ種類、リファラー(直前に見たページ)、日時等を記録したものです。このアクセスログを解析することで、人気の高いページや混雑している時間帯が分かりますので、販促活動やWebサイトの改善に利用できます。

標準のアクセスログは、ある程度の匿名性があるので、IPアドレスを元に個人の動向をトレースし続けたり属性の分析を行うには不充分です。一般家庭用プロバイダ契約ではIPアドレスが随時変わる可能性がありますし、企業組織ではゲートウェイとなるIPアドレスが複数のPCで共有されます。

したがって、個人やある種の属性を元にしたアクセス統計を行うには、個人をトレースし続ける仕組みと別のデータベースとのリレーションが必要になります。

10年以上前になりますが、個人的に「将来はログ解析がユーザ別に行われるようになる」と予想していました。当時予想の域を出なかったのは、コンピュータの処理能力とストレージの容量が十分とは言えなかったからです。

例えば100ページ存在するWebサイトの各ページ毎のページビュー統計を行うには、一度の解析による結果のレポートは100項目ですみますが、1000人のユニークユーザ別にレポートを出すには単純に1000倍のコンピュータリソースが必要となり、結果も1000人分出力されます。幸いなことにムーアの法則が期待通りワークし続けたことで、現在この程度のログ解析処理はそれほど難しいものではなくなりました。

しかしながら1000人分のログ解析結果を紙に印刷して評価するのは非現実的ですので、解析結果の保存と二次統計や検索性が新たに必要となります。

アクセスログから個別ユーザをトレースし続けるソリューションについては、Apacheのmod_usertrackを利用したり、各種商用ログ解析ソフトで実現できます。これらは基本的にブラウザのCookieを利用して、ユーザとログを結び付ける仕組みで、アクセスログにユーザのブラウザが持つユニークで永続性※のあるCookie値を記録するような拡張を行います。

このユニークなCookie値が付加されたアクセスログでは、AさんBさんといった匿名の識別は可能ですが、氏名、年齢、性別、職業といった属性を関連づけるにはもう一工夫必要です。そうです、会員系サービスなどで個人情報を記録する際、データベースにもCookie値を記録するようにすればよいのです。これによって会員とアクセスログのリレーションが出来ますので、サイト運営者は個人あるいは特定属性を持つユーザ別の動向を知ることができるようになるのです。

まとめますと、コンピュータの性能向上によって、通常のアクセスログ×会員数という膨大なアクセス解析が可能となり、さらに昨今はAjax技術でページ遷移を伴わないユーザ操作が大量にサーバサイドに記録(しかもユーザの識別が可能な状態で)されるようになっているので、さらに高度な分析も可能になりました。このようなアクセスログ解析こそ、まさにビッグデータ化を先行した技術と言えるのではないでしょうか。

なお今回触れませんでしたが、携帯やスマホを始めとするモバイルデバイスは機器固有のIDを持っていて、そのIDをアクセスログに直接記録する事もできるので、更に個人の動向がトレースしやすくなっています。ある意味怖いですが。。。

※実際のCookieはユーザが任意に削除や編集が可能ですので、あまり永続性が高いとは言えません。


アクセス数やユーザ数の多いサイトを長年運用していると、ある日を境にデータベースのレスポンスが悪くなり、サーバにログインすることも困難になるような障害が発生することがあります。
原因として真っ先に思い浮かぶのは、データ量やアクセス数が多くなってサーバ性能が追いつかなくなったという事です。しかし実際には以前の記事で説明したように、データベースにインデックスが適切に設定されていないことが原因になっていることが多いのです。

1. サーバ状態の確認
まず不具合の検証として、データベースサーバが高負荷になっている時の状況を確認する必要があります。ログインすら出来ない状態であれば、最悪データセンターのオペレータにリセットを依頼する必要があります(立ちあがってくることを祈りましょう)。サーバにログインできたらプロセスやコネクションの状態を確認します。
プロセス数やアクティブなコネクション(ESTABLISHED)がデータベース設定値の上限に達していたら、これ以上リクエストを受け付けることが出来ないパンク状態にあります。

コマンド例
$ ps auxw
$ netstat -na |grep ESTABLISHED

次に原因となっているSQLを調査します。

2. 原因となるSQLの特定
psコマンドの結果で、ある程度負荷の原因となっているSQLが判明する場合もありますが、PostgreSQLやMySQLではスロークエリ、つまりレスポンスに時間がかかったSQL文をログに記録する機能があるので、それをあらかじめ設定して確認する方法もあります。
スロークエリのログを記録していなかったり、閾値が適切でなかった場合はデータベースサーバを確認しても原因となるSQL文が分からない場合があります。その場合は負荷が高かった時間帯のApacheログを確認し、データベースアクセスを伴うスクリプトに当たりをつけます。そしてそのプログラム内で発行されているSQL文を探しあてます。

3. EXPLAINによるSQLの検証
原因と思われるSQL文が確認できたら、実際にデータベースにコマンドモードでログインし、インデックスが有効に効いているか確認してみましょう。SQLのコマンドモードでEXPLAINという命令のあとに続けて、原因と思われるSQL文を入力して実行すると、オプティマイザがどの様なインデックスを使用したか、あるいは使用しなかったか、そしてスキャンされたレコード数やレスポンスタイムなどが確認できます。インデックスが効いていない検索の場合はシーケンシャルスキャンといって、対象となるテーブルを全件頭から最後まで照合しているので大変効率が悪く時間がかかります。

4. インデックスの作成
シーケンシャルスキャンとなってしまっているSQL文が見つかったら性能改善の余地があります。大抵の場合、条件に指定されているカラムにインデックスが設定されていなかったり、マッチング方法があいまいでインデックスが有効に機能していない状態になっています。そして最も簡単で安全な性能改善方法は、検索条件に指定されているカラムに対してCREATE INDEXを行うだけです。この操作はサイトの運用を続けたまま実行できます。
インデックスを作成したら再びEXPLAINでSQLを確認してみましょう。この結果、作成したインデックスが使用されるようになっていれば、レスポンスタイムとも劇的に性能が改善しているはずです。

インデックス作成例
mysql> CREATE INDEX インデックス名 ON テーブル名 (カラム名);

5. 全文検索用インデックスの利用
また良くあるケースでは、全ての投稿記事をキーワード検索(あいまい検索)するような機能を実装している場合にインデックスが効いていない事があります。記事の中から特定のキーワードだけ見つけるような場合に、通常のLike句での検索ではテーブル内のレコードがすべて対象となってしまい、検索効率が非常に悪くなります。このようなケースでは全文検索用のインデックスを使用する必要があります。
以前のフリーSQLサーバなどは、全文検索用のインデックスが標準装備されていないことが多かったので追加モジュールの組込などが必要でしたが、最近のバージョンではおおむね標準実装されている様です。全文検索用インデックスを利用するにはテーブルの属性変更やプログラムの改修、そしてバッチによるインデックス生成などの対応が必要ですが、高い効果が期待できます。

6. まとめ
このように、データベースサーバの性能が悪化してもインデックスの改善でレスポンスが劇的に改善することが非常に多くありますので、ハードウェア増強やクラウド移管を考える前に、まずはインデックスが適切に用いられているか確認することをお勧めします。

データベース(テーブル)を設計する際、レコードの識別を行うカラムにオートインクリメント型を使用することがあります。この属性を使用するとレコードを追加するだけで自動的に一意な値をセットしてくれるので便利です。

しかし、この属性を安易に使用した場合、基幹系システムの構築や運用時にビジネス要件を満たせなかったり、データベース構造が破たんする可能性があります。

オートインクリメント型は自動で値がセットされる代わり、"基本的に"任意の値をセットできない※というデメリットがあります。任意の値をセットできないということは、例えば「社員番号を割り振る際、非正規社員のコードは9で始まるIDにしよう」とか「商品コードの頭二桁は分類が一目でわかる文字にしよう」とか「取引先IDは旧システムのIDを再利用しよう」などといった既存のビジネス要件を満たせないことになります。

もう一つのデメリットとして、このオートインクリメント型で採番された値を他のテーブルとのリレーションに使用すると、オートインクリメントによって"安易に振られたID"が参照側に残ることになるので、万が一障害が発生して被参照側データを削除したり追加した場合、新たなIDが振られてリンクが切れてしまうという事態になります。

つまり、オートインクリメント型を採用しても問題ないケースは、"使い捨て"のユニークIDをレコードに振りたい場合に限ると言ってもいいと思います。

いま流行のフレームワークを使用すると、テーブル作成時にデフォルトでオートインクリメント型のカラムが作成されることがあるそうですが、本来基幹系システムの開発では「ID(コード)設計」というのが一つのタスクになるくらい重要なプロセスになりますので、ビジネス要件と照らし合わせてリレーションに使用できる"キー"となりうるかを十分評価してから採用する必要があります。

※不可能ではありませんが、危うい設計になるので通常ケースでは設計に織り込みません。

データベースを勉強する機会があると、必ずデータの正規化について学びます。 正規化とはデータの冗長性を排除したりデータのメンテナンス性を高める設計手法で、かつては高価だった記憶装置の使用コストを削減する目的もありました。またインデックスの効果が得られやすいので、統計やリレーションに使用する項目を正規化(コード化)しておくことでアプリケーションのパフォーマンスを上げることもできます。

 しかしこれを"教科書通り"に行うと開発の生産性を低下させたり、まったく費用対効果が得られないケースが多くあります。

例えばアンケート(性別、都道府県、職業、氏名、媒体、好きなペット、個人情報承諾)を収集するようなデータベースを正規化手法を用いて構築した場合、実データは以下のようになります。
--------------------------------------------------
0,12,3,"フジモト マコト","2|3|7",2,0
1,33,6,"スズキ ユカリ","5|7",1,1
0,35,8,"アマカス ジロウ","1|2|3|5",4,0
--------------------------------------------------
このデータを見ただけではそれぞれの数値の意味が分からないので"人間にとっては"可読性がよくありません。

この例を正規化しない状態(つまり非正規化)にすると以下のようになります。
------------------------------------------------------------------------------
男性,千葉県,自営業,"フジモト マコト","テレビ|ラジオ|インターネット",ネコ,承諾する
女性,岡山県,公務員,"スズキ ユカリ","中刷り広告|インターネット",犬,承諾しない
男性,山口県,パート・アルバイト,"アマカス ジロウ","雑誌|テレビ|ラジオ|中刷り広告",鳥,承諾する
------------------------------------------------------------------------------
と、このようにひと目でそれぞれの項目の意味が分かるので、一次データとしてはこのまま使用できそうです。ちなみに最近のコンピュータは性能が極めて高いので、統計などで数十万件程度のデータから文字列検索を行う場合でも数秒程度しか時間はかかりません。

Webキャンペーン応募などは期間中にアンケートデータを収集するだけの処理(INSERT)が大半なので、検索や更新は運営側の処理が多く、相対的な正規化メリットはとても低くなります。統計やレポートは通常非同期に行われる作業ですから、なんでしたらお手元の素晴らしいバックオフィスソフトで十分処理が可能です。

その昔、途中から関わることになった案件で、性別や都道府県までもがマスタテーブル化、つまり正規化されているシステムを見て驚いたことがありました(当然管理画面もあります)。設計者は何故か性別や都道府県に新たな区分が加わったり変更がありうることを想定していたようです。世の経験値が浅いかコスト意識の低いエンジニアの中には、"正規化すべし"ということを教科書通り真に受けて、費用対効果の低い正規化とテーブル増加を安易に行ってしまう方がいるようです。

以前述べたとおり、データベース(テーブル)数とコスト・工数には強い相関性がありますから、「そんなに細分化したテーブル設計にお金を支払ってくれるなんてずいぶん太っ腹なお客様がいたもんだなぁ」と関心すらしてしまいます。逆にお金と時間を余分に頂かずにこのような設計を行った場合こそプロジェクトが"デスマーチ"となる大きな要因になるでしょう。

正規化とはコストと付加価値を十分見極めて設計する必要があり、つまり"ビジネス要件"でもあるのです。

データベースすなわちテーブルを設計する際、要求仕様にかかわらず、いかなる用途であっても必ず記録した方がよいメタ情報があります。これらの情報記録はWeb系システムの開発を行う組織であれば"データベース設計指針"として社内標準の一つとしてもよいのではないでしょうか。 

  1. 登録日時
    「日時が無い情報は情報ですらない」と言ってもいいくらい必須の情報です。
  2. 更新日時
    データに変化のあった日時を記録することは不具合の検証にも役立ちます。
  3. リモートIPアドレス
    アクセス元を記録することで万一不正アクセスがあった場合の調査やセキュリティ対策に用いることができます。またプロバイダや地域、企業、組織を調べてマーケティングに利用することもできます。
  4. ユーザエージェント
    ユーザが使用しているブラウザ(バージョン)、OS、モバイル・スマホ機器などを分析することで、サイトの品質向上やマーケティングに利用することもできます。

その他に、サーバが複数台となるシステム構成では"サーバ自体のIPアドレス"も記録することをおススメします。どのサーバを経路したかがわかるので、不具合調査(どこのログを見たら良いか)や負荷分散の確認に役立てることができます。またレコードの論理削除を行ったことがわかるように、"削除日時"を設けるケースもあります。

「○○を見ている人は○○も見ています」のようなレコメンドシステムの要件があった場合のデータ処理プロセスを考えてみました。

  1. ロギング
    商品の閲覧ログを記録するテーブルを作成しユニークユーザを識別するcookie値を記録する。

    商品ID   | 日時                           | Cookie
    --------+-----------------------+--------------
    000001 | 2012-12-15 12:00:05 | AA0000012326
    000002 | 2012-12-15 12:10:05 | AB7890987001
    000001 | 2012-12-15 12:11:05 | AB7890987001
    000003 | 2012-12-15 12:12:05 | AA0000012326
    000001 | 2012-12-15 14:12:05 | AA0000012326
    000004 | 2012-12-15 15:45:09 | AB7890987001

  2. バッチ処理1
    閲覧ログから商品ID毎にユーザ(Cookie)リストを抽出し更にそのユーザが見た他の全商品IDを抽出して別テーブルに書き出し。

    商品ID1 | Cookie               |商品ID2| 日時
    --------+-----------------+--------+---------------------
    000001 | AA0000012326 | 000003 | 2012-12-15 12:12:05
    000001 | AB7890987001 | 000002 | 2012-12-15 12:10:05
    000001 | AB7890987001 | 000004 | 2012-12-15 15:45:09
    000002 | AB7890987001 | 000001 | 2012-12-15 12:11:05
    000002 | AB7890987001 | 000004 | 2012-12-15 12:45:09
    000003 | AA0000012326 | 000001 | 2012-12-15 12:00:05
    000003 | AA0000012326 | 000001 | 2012-12-15 14:12:05
    000004 | AB7890987001 | 000002 | 2012-12-15 12:10:05
    000004 | AB7890987001 | 000001 | 2012-12-15 12:10:05

  3. バッチ処理2
    2.で生成したテーブルから同一ユーザが同一商品を見たレコードの重複を排除し閲覧数統計を行って別テーブルに書き出す(最大レコード数は"総商品数の二乗-総商品数"になる)。

    商品ID1 |商品ID2| 閲覧数
    --------+-------+--------
    000001 | 000002 |   999
    000001 | 000003 |   999
    000001 | 000004 |   999
     ・
     ・
     ・
    000002 | 000001 |   999
    000002 | 000003 |   999
    000002 | 000004 |   999
     ・
     ・
     ・

  4. レコメンド情報の表示
    3.で生成したテーブルから現在見ている商品に関連する閲覧数の多い商品を抽出してページに出力する。

わりと大変な処理の様です。。。
あとこの設計だとリアルタイムには分析できないという欠点があります


営業担当者にとって、システム開発の見積もりはけっこう頭を悩ませるんじゃないでしょうか。要件や仕様書が専門的だったり不十分だったり、引き合い段階では未確定な要素が多かったりします。しかも、顧客想定から大きく外れると受注できなかったり赤字になる可能性も高く、概算とは言えいい加減な数字は出せません。

ちなみに見積り依頼があった際、プログラマーに「工数計算しといて」などと丸投げし、やっと上がってきた工数に対して鉛筆をなめて出したりしていませんでしょうか?これだと顧客が「今すぐ概算をくれ」と言っている状況では時間がかかって心象がよくありません。

そんなことをせずに、開発に直接タッチしない営業担当でも比較的精度の高い概算を算出できる方法があります。

それはデータベース(テーブル)の数だけを推測する方法です。

なぜかというと、データベース(テーブル)数というのは入出力(画面等)の数と概ね相関しており、当然工数も相関しているからです。部屋の数と出入口、窓の数、照明数などが相関しているようなものです。この際「いやいや、面倒な仕様だったら想定以上にかかるぞ」という制作現場のごもっともな意見はとりあえず無視します。

データベース数を推測するのはある程度のセンスを必要としますが、たとえばECサイトであれば最低限、商品マスタ、売上情報、顧客情報の3点が推測できます。少し複雑になると商品マスタ、在庫情報、売上情報、仕入先マスタ、リコメンド情報、ポイント情報、顧客情報など7点以上になったりしますが、切り口さえわかればエンジニアじゃなくてもそれほど難しい行為ではありません。

データベースの数が推測できたら、あとは基準となる単価を掛けるだけで概算見積りは完了です。

これだったら電話口で即答できる可能性もあります。

仮に1データベースあたりの単価を30万円とした場合、テーブルを3つ使用したシステムは概算で90万円という単純計算になります。この事前の単価設定プロセスにおいては"諸般の事情"や"比重"を加味してあげれば制作現場から怒られることも避けられます。ただし市場価格については堂々と営業・経営マターで設定すればよいのではないでしょうか。

もう少し具体的な係数を使って算出してみましょう。
①データベース数:5
②単価:30万円
③設計費係数:0.1
④間接費係数:0.2 (制作進行管理、デバッグ、現調、経費、営業管理費など)

①×②+(①×②×③)+(①×②×④)=195万円

この算出方法はデータベースを使用するシステム案件に限ったものですが、概算を即答するコツは制作者や外注に積算させなくても、単純な数を推測するだけで算出可能な根拠をあらかじめ作っておくことだと思います。

余談ですが、建築業界は概ね床面積とグレードで施工価格が決まるそうです。

情報システムやWebサイトにてデータベース(ここではDBMS)を使用する意義は様々ありますが、主な役割は"必要な情報を整理された形で高速に取り出す機能"になります。

ひとえにデータベースが単なるファイルと異なる点は"インデックス"が使用できるところにあります。

インデックスはその名の通り、ドキュメントの見出しや目次のことですが情報量が増えれば増えるほどその効果が高まります。

例えば以下のような不規則なデータの集合があったとします。
poiuytrgewqlkjhgfdsamnbvcxzaqwertyuioplkjhgfdsmnbvcxzasdfghjklqwertyuiop
この中から"g"という文字の出現回数を数えるには先頭から一文字一文字注意深く観察する必要があります。

この情報が以下のようにアルファベット順に整列していた場合はどうでしょうか、aaabbccdddeeefffgggghhhiiijjjkkklllmmnnooopppqqqrrrssstttuuuvvwwwxxyyyzz
あっと言う間にgが4個あることがわかりますよね。

このようにあらかじめデータの整列を(実際には論理的に)行い、検索時にデータを瞬時に取り出せるような仕組みがインデックスの主な役割です。なお、利用者が数百万人、数億人いるようなSNSサイトであっても瞬時に自分のプロフィールを呼び出して認証を行ったり、変更を行えるのもインデックスが有効に機能しているからです。

しかし、これだけデータベース活用に重要であるにも関わらず、システム開発において中心となるプログラマの中にはデータベースのインデックスについて関心(あるいは興味)やスキルが不足している人が比較的多いという残念な現状があります。開発時や運用初期段階ではデータ件数も少なく、また無理な工期も少なくなくデータベース性能にまで気が回りにくい状況もあるのでしょう。

データベースを運用して数年も経ってから、「最近どうもデータベースのレスポンスが遅い」という現象が発生し、詳細に調査を行ってみたら必要なインデックスが設定されていなかった、なんてことにもたびたび遭遇します。この時きちんとした技術者であれば原因の一つとしてインデックスの不備が思い浮かびますが、安易に「サーバ性能が足りないから」と判断してメモリやCPUの増強を提案される場合も多いです。

私の経験的にはデータベーストラブルの7割以上がインデックスの不備によるものです。

私はインデックスに不備がある状態をお客様に説明する際には「でたらめに本が並べられた本屋で必要な本を探し求めることを想像してみてください」と伝えます。欲しい本が比較的簡単に見つかったり"無いこと"がわかるのは書店の店員がキチンと分類を行い、部分的にあいうえお順に並べてくれるからです。たまに、特に気にしていないと思われる本屋があったりしますが、我慢できるのは商店街にあるような小さな商店までですよね。

このページのトップヘ