https://blog.utgw.net/entry/2024/12/17/214954

はじめに

これは Go - Qiita Advent Calendar 2024 - Qiita の記事です。

tl;dr

goquでMySQLのクエリを組み立てるとき、BOOLEAN型のカラムの値を比較するとき
- Eq メソッドなどや goqu.Ex 型にGoのbool型の値を渡さない
- IsTrue IsFalse メソッドを使わない
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の回答が詳しいです。

stackoverflow.com

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を書きました。

github.com

このlinterは単一のlinter・go vet -vettool 経由で・golangci-lintのプラグインなどさまざまな方法で使えます。

$ go vet -vettool=`which goqumysqllint` ./...

今のところテストコードにあるようなコードをlinterで検出できるようになっています。

github.com

おわりに

IS 演算子でBOOLEAN型のカラムを比較するとインデックスが効かない問題は id:SlashNephy に教えてもらいました。みなさまも気をつけましょう。クエリビルダやO/Rマッパーが発行するSQLを気にしてみる・EXPLAINの結果を注意深く見てみるといいかもしれません。

PostgreSQLやSQLiteだと問題にならないのでしょうか? 今後の課題とします。あるいは知ってる方がいれば教えてください。

*1:MySQL :: MySQL 8.4 Reference Manual :: 13.1.1 Numeric Data Type Syntax

*2:MySQL :: MySQL 8.4 Reference Manual :: 11.1.6 Boolean Literals

*3:https://dev.mysql.com/doc/refman/8.4/en/comparison-operators.html#operator_is