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は思ったよりもずっとデリケートなのかもしれない。

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