はじめに
これは Go - Qiita Advent Calendar 2024 - Qiita の記事です。
tl;dr
- goquでMySQLのクエリを組み立てるとき、BOOLEAN型のカラムの値を比較するとき
Eqメソッドなどやgoqu.Ex型にGoのbool型の値を渡さないIsTrueIsFalseメソッドを使わない
- MySQLでは、BOOLEAN型のカラムを
IS演算子で比較するとインデックスが効かなくなる - したがって、以下のいずれかの方法を取るべき
1(真) もしくは0(偽) と比較する (整数値を使う場合)goqu.L("TRUE")もしくはgoqu.L("FALSE")と比較する (SQLのリテラルを使う場合)
- これらのコードを検出するlinterを書いた
goquについて
goquはGoのクエリビルダです。SQLを文字列ではなくGoの式として組み立てることで、よくあるミスや文法エラーを回避したり、クエリの再利用性を高めたりすることができます。
goquでBOOLEAN型のカラムを比較するときの注意点
さて、goquでBOOLEAN型のカラムの値を比較して絞り込むようなクエリを記述することを考えましょう。
よくない例
「カラム col の値が TRUE である」という条件で絞り込むとき、普通に考えると以下のいずれかの書き方を試すと思います。
goqu.C("col").Eq(true) // Eqメソッドを使う場合 goqu.C("col").IsTrue() // IsTrueメソッドを使う場合 _ = goqu.Ex{"col": true} // goqu.Exを使う場合
先述した書き方はいずれも同じ条件式に変換され、実際にクエリを生成すると以下のようになります。
SELECT * FROM `tbl` WHERE `col` IS TRUE;
このクエリは一見正しそうだし、実行結果も意図したものになる (col の値が TRUE である行だけが取得できる) と思います。
が、実はパフォーマンスがよくありません。col カラムに対してインデックスが貼ってあっても、それがうまく使われないクエリが発行される可能性があります。
よい例
じゃあどうしたらいいかというと、以下の2つのいずれかの方法を使いましょう。
1(真) もしくは0(偽) と比較する (整数値を使う場合)goqu.L("TRUE")もしくはgoqu.L("FALSE")と比較する (SQLのリテラルを使う場合)
goqu.C("col").Eq(1) _ = goqu.Ex{"col": 1} goqu.C("col").Eq(goqu.L("TRUE")) _ = goqu.Ex{"col": goqu.L("TRUE")}
MySQLにおける BOOLEAN 型は TINYINT(1) 型と同一であり*1、 TRUE FALSE はそれぞれ 1 0 のエイリアスです*2。整数値を使うことで、Eq メソッドや goqu.Ex 型を使って比較したときに = 演算子で比較されるようになります。
または、goqu.L 関数を使うことでSQLのリテラルとして比較する値を渡すことでも = 演算子を使うことを強制できます。
実験
以下のようなテーブルを用意して実験してみましょう (MySQL 8.4.3で実験しました)。
CREATE TABLE `tbl` ( `id` BIGINT UNSIGNED NOT NULL, `is_active` BOOLEAN NOT NULL, PRIMARY KEY (`id`), KEY `is_active` (`is_active`) ) ENGINE=InnoDB;
このテーブルに1000000行のデータをINSERTします。INSERTするときに、is_active カラムの値がおよそ0.5%の確率で TRUE になるようにします。
今回は以下のような行数になりました。is_active = TRUE の行が5054行、is_active = FALSE の行が994946行あります。
mysql> SELECT is_active, COUNT(*) FROM tbl GROUP BY is_active; +-----------+----------+ | is_active | COUNT(*) | +-----------+----------+ | 0 | 994946 | | 1 | 5054 | +-----------+----------+ 2 rows in set (0.12 sec)
このようなテーブルに対して、先ほどの例で取り上げたようなgoquが生成するSQLを模したクエリを流してみましょう。
mysql> SELECT COUNT(*) FROM `tbl` WHERE `is_active` IS TRUE; +----------+ | COUNT(*) | +----------+ | 5054 | +----------+ 1 row in set (0.12 sec)
is_active カラムの値が TRUE である行数を数えるのに0.12秒かかっています。EXPLAINしてみるとどうでしょうか。
mysql> EXPLAIN SELECT COUNT(*) FROM `tbl` WHERE `is_active` IS TRUE; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | tbl | NULL | index | NULL | is_active | 1 | NULL | 998568 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
インデックスは使っていそうだけど、rows の値を見るとテーブルのほぼ全行をなめてしまっていることが分かります。おやおや……。
今度は IS 演算子ではなく = 演算子で比較するようにしてみましょう。EXPLAINも一気に見ます。
mysql> SELECT COUNT(*) FROM `tbl` WHERE `is_active` = TRUE; +----------+ | COUNT(*) | +----------+ | 5054 | +----------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT COUNT(*) FROM `tbl` WHERE `is_active` = TRUE; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | tbl | NULL | ref | is_active | is_active | 1 | const | 5054 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
今度は行数を数えるのに0.01秒しかかからなかったし、EXPLAIN結果のrows の値を見ると、なめる行数が5054行で済んでいます。こっちはインデックスがしっかり効いていると言えるでしょうね。
どうしてこうなった
以下のStackoverflowの回答が詳しいです。
IS TRUE という式でカラムを絞り込むと、MySQLはカラムがtruthyな値であるかどうかをキャストして確かめます*3。なのでインデックスが効かないわけですね。
= TRUE と書くことで、定数との等号比較クエリになってインデックスを効かせられるようになります。
先ほどの実験で発行したクエリに対してEXPLAIN ANALYZE句で解析するとより分かりやすいですね。
mysql> EXPLAIN ANALYZE SELECT COUNT(*) FROM `tbl` WHERE `is_active` = TRUE\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=1012 rows=1) (actual time=1.8..1.8 rows=1 loops=1)
-> Covering index lookup on tbl using is_active (is_active=true) (cost=507 rows=5054) (actual time=0.15..1.45 rows=5054 loops=1)
1 row in set (0.01 sec)
mysql> EXPLAIN ANALYZE SELECT COUNT(*) FROM `tbl` WHERE `is_active` IS TRUE\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=200171 rows=1) (actual time=152..152 rows=1 loops=1)
-> Filter: ((0 <> tbl.is_active) is true) (cost=100314 rows=998568) (actual time=151..152 rows=5054 loops=1)
-> Covering index scan on tbl using is_active (cost=100314 rows=998568) (actual time=2.16..109 rows=1e+6 loops=1)
1 row in set (0.15 sec)
goqumysqllintを書いた
この問題を検出するためのlinterを書きました。
このlinterは単一のlinter・go vet -vettool 経由で・golangci-lintのプラグイン などさまざまな方法で使えます。
$ go vet -vettool=`which goqumysqllint` ./...
今のところテストコードにあるようなコードをlinterで検出できるようになっています。
おわりに
IS 演算子でBOOLEAN型のカラムを比較するとインデックスが効かない問題は 
id:SlashNephy に教えてもらいました。みなさまも気をつけましょう。クエリビルダやO/Rマッパーが発行するSQLを気にしてみる・EXPLAINの結果を注意深く見てみる といいかもしれません。
PostgreSQLやSQLiteだと問題にならないのでしょうか? 今後の課題とします。あるいは知ってる方がいれば教えてください。
