mysqlにおけるvarcharのindex使用時の注意

概要

みなさんmysqlを使用していてvarcharのカラムにindexをつけることってないだろうか?
selectする際にindexが付いているカラムを対象にwhereをつけているのにindexが有効になっていない機会があり得る。
今回はそんな時に認識しておかないと、はまるかもしれない挙動について取り上げる。
(そもそもvarcharにindexつけることについてはまた別の機会にでも)

準備

  • テーブル
  • varcharのカラムにindex付いているテーブルを用意

  • 確認方法
  • 確認方法としてはmysqldの設定としてINDEXの効いていないクエリがslowlogに出力されるように設定し、slowlogの出力を確認しながら進める。

    事前確認

    まずは普通にqueryを発行してみる

  • 数値として検索した場合
  • mysql> select count(*) from t1 where column1 = 7395584;
    +----------+
    | count(*) |
    +----------+
    |       37 |
    +----------+
    1 row in set (0.63 sec)
    
    # Time: 150129  5:47:34
    # User@Host: mysql[mysql] @ localhost []
    # Query_time: 0.633334  Lock_time: 0.000088 Rows_sent: 1  Rows_examined: 1264725
    SET timestamp=1422510454;
    select count(*) from t1 where column1 = 7395584;
    
  • 文字列として検索した場合
  • mysql> select count(*) from t1 where column1 = '7395584';
    +----------+
    | count(*) |
    +----------+
    |       37 |
    +----------+
    1 row in set (0.01 sec)
    

    早い。slowlogの出力はなし。

  • explain
  • explainすると数値として検索した場合はいわゆるフルスキャンとなっていることがわかる

    mysql> explain select count(*) from t1 where column1 = 7395584;
    +----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
    | id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
    +----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
    |  1 | SIMPLE      | t1     | index | idx1          | idx1 | 396     | NULL | 1265625 | Using where; Using index |
    +----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
    1 row in set (0.00 sec)
    
    mysql> explain select count(*) from t1 where column1 = '7395584';
    +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
    | id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
    +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
    |  1 | SIMPLE      | t1     | ref  | idx1          | idx1 | 386     | const |   37 | Using where; Using index |
    +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
    1 row in set (0.00 sec)
    

    と、生のmysqlクライアントからの検索は分かった。
    アプリケーションからの実行はどうなるんだろうか。
    使用する機会の多いpdoについて検証してみる。

    アプリケーションレイヤからの検証

  • 生のquery
  • 簡単なphpアプリケーションを作成して、slowlogの出力を見る。

    <?php
    $dns = "mysql:host=localhost;dbname=testdb;charset=utf8";
    $user = "mysql";
    $pass = "pass";
    $pdo = new PDO($dns, $user, $pass);
    
    $stmt = $pdo->query("SELECT count(*) AS cnt FROM t1 WHERE column1 = 7395584");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    var_dump($row);
    

    出力は下記のようになった。きちんと動作はしている。

    [vagrant@local tmp]$ php select.php 
    array(1) {
      ["cnt"]=>
      string(2) "37"
    }
    

    さてslowlog出力は?

    # Time: 150129  5:55:12
    # User@Host: mysql[mysql] @ localhost []
    # Query_time: 0.242804  Lock_time: 0.000055 Rows_sent: 1  Rows_examined: 1264725
    SET timestamp=1422510912;
    SELECT count(*) AS cnt FROM t1 WHERE column1 = 7395584;
    

    でた!
    これに関してはpdoは特に何もしない。mysqlクライアントから直接クエリを入力した時と同じような結果となった。
    結果は割愛するが、シングルクォートで文字列を囲った時にはslowlogの出力はされなくなった。したがってindexによる検索が有効になっているようである。

    ここで勘の良い人は気になっているかもしれないが、では逆に数値のカラムにINDEXがはられている場合、where句に記述したデータをシングルクォートでくくり文字列として検索した場合どうなるのか。
    ということで気になっているであろうが、これに関して検証した結果どちらでも適切にindexが効くことが確認できた。

  • prepared statement
  • さてprepared statementを利用した場合はどのようになるのか、検証したいと思う。
    pdoにはbindを行うために二種類のインタフェースが存在するが、今回はbindValueを取り上げる。
    マニュアルを確認すると第三引数にバインドするパラメータの方が指定できる。なんとなく今検証していることをの結果を匂わせるようなインタフェースをしている。

    まずは第三引数のことはおいておき、純粋にPHPの型推論による挙動の変化を観察しようではないか。

    初めに数値としてbindValueを実行してアプリケーションを叩いてみる。
    アプリケーションは下記のようになる。

    <?php
    $dns = "mysql:host=localhost;dbname=testdb;charset=utf8";
    $user = "mysql";
    $pass = "pass";
    $pdo = new PDO($dns, $user, $pass);
    
    $sql = "SELECT count(*) AS cnt FROM t1 WHERE column1 = :column1";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array('column1' => 7395584));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    var_dump($row);
    

    動作を確認し、結果も正常に取得できている。

    [vagrant@local tmp]$ php select2.php 
    array(1) {
      ["cnt"]=>
      string(2) "37"
    }
    

    さて、肝心なslowlogはどうなっているかというと。
    出ていない!
    prepared statementとbindを利用すると、数値文字列などのギャップはpdo側で吸収してくれるようだ。

    また当然bindの引数の型を文字列に加工したとしてもslowlogは出力されずに、indexが有効になっていることが確認できた。

    これについて思うことは、phpの場合に限って言うとなるべくprepared statementを使用したほうがお馬鹿なミスが減るだろうということだ。
    生のsqlは思ったよりもずっとデリケートなのかもしれない。

    他のアプリケーションに関してもアプリケーションが提供してくれる機能を用いるほうが良いだろう。


    macのtopコマンドを追う

    家に帰ると充電器に指しておいた愛器のMACがファンを最高スピードまでクロックアップさせながら唸りをあげていた。

    なぜだ。蓋閉じてるのに。

    今回はこういった漠然とした状態からコンピュータ内部でざっくり何が起こっているか判別するときに使用できるtopコマンドを掘り下げて解説しよう。

    topコマンドはosx(mac)だけでなくunix実装のほとんどのディストリビューションで提供されているツールであろう。
    システム全体をざっくり見るときによく用いられる。
    ただこれディストリビューションによって確認できる情報や、オプションとか結構変わってくるので注意。

    さてmacでterminalを立ち上げtopコマンドを打鍵すると下記のような表示が見て取れると思う。

    Processes: 226 total, 4 running, 9 stuck, 213 sleeping, 1096 threads
    Load Avg: 3.12, 3.16, 2.93  CPU usage: 16.81% user, 23.22% sys, 59.95% idle  SharedLibs: 9200K resident, 14M data, 0B linkedit.
    MemRegions: 45374 total, 1842M resident, 59M private, 1496M shared. PhysMem: 5103M used (1685M wired), 2594M unused.
    VM: 538G vsize, 1066M framework vsize, 3072256(54) swapins, 3399825(0) swapouts.  Networks: packets: 6042458/6896M in, 3543389/504M out.
    Disks: 1397033/93G read, 1239425/75G written.
    

    Load Avg: 3?何も起動してないのに。。。

    表示される項目について解説する。

    Processes – total

    マシン上で動作しているプロセスの数

    Processes – running

    実行中プロセスの数
    実行中となりうるプロセス数は1CPUにつき1プロセスだけである。
    動作しているマシンのCPUがクアッドコアなため最大で同時に4つのプロセスが動作可能

    Processes – stuck

    そもそもstuckとは?
    osxのtopコマンドにおけるstuckとはプロセスの状態がLIBTOP_STATE_STUCKとなっている状態のプロセス数である。
    またカーネルの状態としてはTH_STATE_UNINTERRUPTIBLEであることを指す。
    これはプロセスが割り込み不可能なwait状態であることを指す。
    通常はディスクやネットワークに対するI/O待ちのような状態が該当する。

    Processes – sleeping

    wait状態のプロセスの数を表す。

    Processes – threads

    スレッドの数を表す。

    Load Avg

    ロードアベレージとは実行キューの中に入っている平均ジョブ数を表す。
    (これはosxの定義であってディストリビューションによって算出方法は多少異なることもある)
    3つの数値が並んでいるがこれは左から、1分平均、5分平均、15分平均を表す。

    CPU usage – user

    ユーザ実行時間を表す。ユーザ時間とはアプリケーションレイヤでカーネル処理(システムコール)に費やされている時間以外の時間のことを指す。
    例えばアプリケーション中でシステムコールを利用している場合、その間はシステム時間として認識される。

    CPU usage – system

    システム実行時間を表す。アプリケーションやOSによりシステムコールに費やされている時間を表す。

    CPU usage – idle

    アイドル時間を表す。

    SharedLibs – resident

    メモリに常駐している共有メモリを表す。

    SharedLibs – data

    データ領域を表す。

    SharedLibs – linkedit

    MemRegions – total

    使用メモリサイズ。単位はmach virtual memory。

    MemRegions – resident

    常駐メモリのサイズ

    MemRegions – private

    非共有メモリのサイズ

    MemRegions – shared

    共有メモリのサイズ

    PhysMem – used

    使用中の物理メモリサイズ
    wiredという表記があるが、これはos kernelによって使用されていることを意味する。

    PhysMem – unused

    未使用な物理メモリサイズ

    VM – vsize

    仮想メモリの総サイズ。
    仮想メモリとは実際には存在しないが、実メモリにマップ領域を用意し、実際のメモリ上にページを配置したり(スワップイン)
    抱えきれなくなったページをディスク上に吐き出したり(スワップアウト)することでプロセスからは膨大なメモリが使用可能なように見えるようにする仕組み。
    当然実メモリよりも大幅に大きなサイズとなる。

    VM – framework vsize

    共有メモリにより諸費される仮想メモリサイズ。

    VM – swapins

    スワップインを起こしたページ数

    VM – swapouts

    スワップアウトを起こしたページ数

    Network packets

    ネットワークに対するin/outのパケットサイズを表す。

    Disks

    ディスク装置に対するread/writeのデータサイズを表す。

    本日はこんなところで。にしてもosxはドキュメントが少ないですね。
    https://apple.stackexchange.com/
    上記はstackoverflowのapple版みたいなものなんですが、そちらが一番情報量が豊富なように思います。