カテゴリー: SQL

  • 【MySQL】カラムの全部取得と一部取得の速さ

    MySQLを扱っていて、SELECT文でSQLを発行する場合、カラム(フィールド)を全部取得した方が速いのか、一部取得の方が速いのかベンチマークを取ってみました。

    結論から言うと、一部取得の方が速いです。

    発行するSQL

    一部取得 : ”SELECT state FROM table1″

    全部取得 : ”SELECT * FROM table1″

    一部のカラムを抽出(state)
    平均値: 0.023152 [sec]
    全測定結果:

    Array
    (
        [1] => 0.006020
        [2] => 0.005948
        [3] => 0.006169
        [4] => 0.029970
        [5] => 0.025944
        [6] => 0.006965
        [7] => 0.006757
        [8] => 0.005894
        [9] => 0.005891
        [10] => 0.131971
        [mean] => 0.023152
        [iterations] => 10
    )

    全部のカラムを抽出 (*)
    平均値: 0.128534 [sec]
    全測定結果:

    Array
    (
        [1] => 0.025478
        [2] => 0.161143
        [3] => 0.135521
        [4] => 0.175835
        [5] => 0.104871
        [6] => 0.125868
        [7] => 0.138533
        [8] => 0.125199
        [9] => 0.141315
        [10] => 0.151584
        [mean] => 0.128534
        [iterations] => 10
    )

    ちなみに使用したベンチマーカーは、PEAR::Benchmark_Iterateです。 

     更に、もう一つ結果を。下で、使用したベンチマーカーは、PEAR::Benchmark_Timerです。 

      ex time %
    Start – 0.00%
    全部のカラム抽出(*) 0.035709 81.70%
    一部のカラム抽出(state) 0.007989 18.28%
    Stop 0.000009 0.02%
    total 0.043707 100.00%

    どれくらい速度に差が出るかは、データベースのカラムの構成などにもよりますが、一つのカラムをたくさんのカラムから抽出する場合には、特定のカラムを指定してSELECTした方が速いようです。

  • 【MySQL】SQL発行時に日時計算(加算・減算)する方法

    PHP+MySQLを利用していて、DATETIME型のカラムに対して、直接、加算・減算をしたSQLの条件を指定したい場合の方法について覚書しておきます。

    もちろん、PHPで日時計算をして、変数に代入してからSQLに組み込むなどしても日時の加算・減算の条件付けはできるのですが、SQLの発行時点で加算・減算した方が楽に操作できます。

    MySQLの公式マニュアルにも記述があるのですが、MySQLのマニュアルは読みづらいと思うので要点をまとめてみました。

    (参考)公式マニュアル
    http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-functions.html

    該当するのはDATE_ADD DATE_SUBの箇所です。

    分かりづらいので、具体的なSQLを組んでみます。

    "SELECT count(id) as cnt FROM table  
    WHERE (date > (NOW() - INTERVAL 60 SECOND)",

    上記SQLは、dateカラムにある値が、現在時刻から60秒前以降の値のものを抽出して、そのidの数を数えるというものです。

    ポイントは、INTERVAL です。

    INTERVALの前にマイナス-・プラス+記号をつけることで、減算・加算ができます。

    +(-) INTERVAL 数値 単位 という構文になります。

    単位は、

    MICROSECOND
    SECOND
    MINUTE
    HOUR
    DAY
    WEEK
    MONTH

    など様々なものが使えます。公式マニュアルに詳しく載っているので参考にしてください。

    加算・減算の対象となるのは、NOW()関数だけではなく、DATETIME型の具体的な数値でもOKなようです。

    もう一つの方法は、DATE_ADD()、DATE_SUB() 関数を使用する方法です。

    (date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY))

    CURDATE()関数は今日の年月日を返します。10 DAY の部分に日時に対応するものを置くと、日時を加算・減算することができます。

    上記例では、今日の日付から 10日前の年月日を返すことになります。

    ちなみに、INTERVALを利用せず、

    NOW()-60

    などとしてもエラーにならず値を取得することができます。

    ただし、-60ならば、60秒前となりますが、-100とすると100秒前ではなく、1分前となります。DATETIMEを数値の羅列にしたものから100を引く計算になるので1分前となるわけです。

    混乱を避けるためにも、INTERVALを利用した方がよさそうです。

  • 【MySQL】予約語に注意!

    PHPとMySQLを連携させてWEBアプリを開発している時、SQLクエリが通らずエラーを返してくることがあります。

    たいていは、SQLの構文を間違えていたりするのですが、正しい構文でもエラーを返す場合があります。

    その一つが、MySQLの予約語を気づかずに使っているケースです。

    たとえば、テーブルのカラム(フィールド)に、key やfromやtoなどを使用するとSQLクエリはエラーを返します。

    理由は、それらの語句がMySQLの予約語だからです。構文が正しいはずなのにSQLクエリがエラーになる場合には当該フレーズが予約語でないか確認してみると問題の解決につながります。

    MySQLには、驚くほどたくさんの予約語があります。

    下のリンクはMySQL5の予約語(公式マニュアル)です。

    http://dev.mysql.com/doc/refman/5.1/ja/reserved-words.html

    ざっと見た感じ、200語句くらいありそうです。

    もっとも、公式マニュアルを読むと

    予約語は引用符で囲まれた場合、識別子として使用できます。

    と説明があり、予約語を使うテクニックもあるようです。

    ただ、トラブル回避のため、はじめから予約語を使うのは避けたほうが無難だと思います。

  • 【PHP+MySQL】トランザクション構文の基礎

    PHPとMySQLを連携させてデータベースを扱っていると、トランザクションの処理が必要になることがあります。

    そこで、ここでは、トランザクション構文の基礎を紹介します。

    <?php
    
    (接続部分は省略)
    
    //オートコミットをオフに設定
    $sql = "SET AUTOCOMMIT=0" ;
    mysql_query($sql) ;
    
    //トランザクション開始
    $sql = "BEGIN" ;
    mysql_query($sql) ;
    
    //テーブルに挿入
    $sql = sprintf('INSERT INTO table SET data="%s"' , $data) ;
    $res = mysql_query($sql) or die("insert error") ;
    if ($res) {
    //コミット
    $sql = "COMMIT" ;
    mysql_query($sql) ;
    echo "成功" ;
    } else {
    //ロールバック
    $sql = "ROLLBACK" ;
    mysql_query($sql) ;
    echo "失敗" ;
    }
    ?>

    基本的に、これでトランザクションが機能します。

    注意点としては、データベースの種別に InnoDB などを選択しておくことです。

    デフォルトの MyISAM ではトランザクションを利用することはできません。

    autocommit というのがよくわからなかったので調べてみました。

    InnoDBのデフォルトだとautocommit機能がオンで、commitする前でも、クエリ成功時点で自動的に確定してしまうらしい。

    これだとrollbackできないケースが出てくるので、autocommit機能はオフにした方が良いとのこと。

    ちなみに、CORESERVERのInnoDBを調べたら、オンになっていました。

    (参考)autocommitについて役立つページ

    http://redwarcueid.seesaa.net/article/140085587.html

    http://open-groove.net/mysql/autocommit/

    http://dbinfo.sakura.ne.jp/?contents_id=132

  • 【書評】図解SQLこれからはじめる基本の知識

    図解 SQL これからはじめる基本の知識 島田裕二(著)の書評です。

    良い点

    • SQLの基礎を一通りカバー
    • 複数のデータベースについて言及
    • 読みやすく、短時間で読み終えられる
    • 青と黒の二色刷りで見やすい

    悪い点

    • 正規化の解説は無い

    総評

    表紙の記述に、『基礎』、『1冊目』とあるように、入門者向けのSQL解説本です。

    特徴は、『図解』されていること。見開き2ページで項目が完結していて、左に解説文、右に図表という構成になっています。

    この構成が最後まで続くので読みやすく、『リファレンス』としても使えます。

    ただ、初心者向けで易しいのは良いのですが、ある程度、SQLを勉強した人には、少し物足りないかもしれません。ここら辺は、基礎のSQLとのバランスで難しいところだとは思いますが。

    正規化を具体例を出して解説したり、PHPなどと絡めて実践的なサンプルを出してくれると自分的にはポイントが高いのですが、基礎のSQLを学ぶという目的の1冊目としては良書だと思います。

    図解 SQL これからはじめる基本の知識