MySQLでIN検索するとインデックスが効かない場合の対処

前提・実現したいこと

MySQLで以下のタグテーブルwp_tagsがあるとします。
PHPでタグ名の配列$tags = ['a','b'];を受けて、
・なければ保存しそのIDを取得
・あればそのIDを取得
したいです。

###発生している問題
そのために複数のタグをSELECTで存在確認し、なければINSERTという流れを組んでみました。
しかし存在確認でINを使うと、インデックスが効きませんでした。

該当のソースコード

以下のタグテーブルとします。tag_nameにユニークがかかっている状態で、これを複数検索したいということです。
show crate table wp_tagsの結果↓

SQL
1CREATE TABLE `wp_tags` (
2 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
3 `tag_name` varchar(50) NOT NULL,
4 `lang_id` tinyint(2) unsigned NOT NULL DEFAULT 1,
5 `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
6 `is_official` tinyint(1) unsigned NOT NULL DEFAULT '0',
7 PRIMARY KEY (`ID`),
8 UNIQUE KEY `u_tag_name` (`tag_name`),
9 KEY `i_is_official` (`is_official`),
10) ENGINE=InnoDB AUTO_INCREMENT=1334 DEFAULT CHARSET=utf8

上のテーブルはユニークなので次のクエリでtype=constとなると思ったのですが、EXPLAINをつけてみたら画像のようにtype=rangeでして、ユニークが活躍できていません

EXPLAIN
SELECT * FROM wp_tags WHERE tag_name IN ( 'a','b' );

↓結果

###試したこと
まずINを一つにしたらtype=constとなりまして、複数ある場合の問題だと思います。

EXPLAIN
SELECT * FROM wp_tags WHERE tag_name IN ( 'a' );

###考えた解決策
上記試したことから、要はINで複数検索することは非効率なのだと思いました。
そこで考えた解決策ですが、次の【A】【B】はいずれがよろしいでしょうか？

【A】
PHP側のforeachを使い、一件ずつ存在確認をする

php
1$IDs = []; // $queryによって存在を確認し、存在するタグ名のIDを取得して入れる
2$tag_names = ['a','b']; // POSTされたタグ名で、存在すれば$IDsにいれ、存在しなければINSERTする
3$not_exist_tag_names = []; // 存在しないタグ名で、INSERTのときに使う
4
5// 存在を確認
6foreach($tag_names as $tag_name){
7  $query = "SELECT * FROM wp_tags WHERE tag_name = $tag_name;";
8}
9
10// bがなかったのでINSERTする
11$query = "INSERT INTO wp_tags (`tag_name`) VALUES ('b');";
12
13// こんな感じの戻り値を予定（bだけ存在しなかったのでINSERTされ、aは存在したのでIDが取得された）
14return [
15["status"=>"exist",  "ID"=>1, "tag_name"=>"a"]
16["status"=>"insert", "ID"=>2, "tag_name"=>"b"]
17]
18];

【B】MySQL側のUNIONを使う

php
1$query = "
2EXPLAIN
3(SELECT * FROM wp_tags WHERE tag_name = 'a')
4UNION
5(SELECT * FROM wp_tags WHERE tag_name = 'b')
6";

または他に良い方法ございましたらご教示願えませんでしょうか。
どうぞ宜しくお願い致します。

###バージョン
MySQL 5.7.31

AbeTakashi

2021/01/22 08:06

登録されているデータ件数はどうなってるでしょうか。もし件数が極端に少ない場合はオプティマイザがあえてレンジスキャンにしている可能性があります。負荷を気にして検証されていると思いますが、まずは想定する件数のデータを用意して検証した方が良いと思います。最低でも数万件データがないと正しい検証はできないのではないでしょうか。逆に将来的にでも数百件しかデータが入らないと言うことであれば、そこまでシビアにインデックスの効きを気にする必要はないと思います。一般的にInを使うのが非効率ってのはあると思いますが、Union検索に優位性あるかどうかはケースバイケースな気がします。データをいっぱい入れて実際に検証するのが手っ取り早いかと。

行動規範の内容に同意します

回答2件

ベストアンサー

range でユニークインデックスを活用できているんだから問題ないんじゃないですかね。
実測した結果、速度差異が大きかったですか?

投稿2021/01/22 08:23

68user

総合スコア2022

68user

2021/01/22 08:47

今一度質問を読みましたが、未登録のタグを追加するのなら下記でいいんじゃないでしょうか。・UNIQUE KEY 張ってあるんだから、とりあえず INSERT すればよい。重複エラーが発生しても問題ない。・INSERT IGNORE で複数タグ分一気に登録するのもアリ。また、タグなので IN ('a', 'b') となることはまれであって、IN ('Linux', 'PHP') というような使い方がメインということですね。であれば測定しなおすべきじゃないでしょうか。

hana_hana

2021/01/22 09:52

ご回答ありがとうございます。rangeで良かったでしょうか。一つならconstなのにrangeかぁ…とモヤモヤしていたので。

hana_hana

2021/01/22 09:54

なるほど。とりあえずINSERTですね。いいと思ったのですが、存在する場合にはそのIDも取得したい、という点もございますので、やはりSELECTでないと、と思います。（INSERTでユニークが重複したときにそのIDを取得できるようなコードなど…ないですよね？）測定しなおすべきとのことですが、IN ('a', 'b') と IN ('Linux', 'PHP') とで何が異なるのでしょうか？ VARCHAR(1) と VARCHAR(5) ということでしょうか？

68user

2021/01/22 10:05

一括 INSERT した後に SELECT で狙いのタグを一括で id 取得はどうでしょうか。どちらが速いかは測ってみないと感はありますけれども。 a は 0x61, b は 0x62 と連続値なので、動作イメージとしては where substring(tag_name,0,1) between 0x61 and 0x62 と range 的な検索ができるけれども、Linux と PHP は連続値ではないので range にはならないのではないか、ということです。なお、本件、現時点でよほど遅いならともかくそうでないなら後回しでもよいのでは、と思いました。タグの数によっては全レコードバッファキャッシュに乗っかるので別に遅くないという結論かもしれませんし、最終的には Redis 等の KVS でキャッシュするかもしれません (技術的探求をしたいということなら深堀りするのは問題ないと思います)。

hana_hana

2021/01/22 10:13

色々な方法を知りたいので大変助かります。一括 INSERT した後の、「狙いのタグ」とは何を意味していらっしゃいますでしょうか？「ユニーク重複によって INSERT に失敗したタグ」は取得できないでしょうし… 後回しは本当におっしゃる通りですね。優先順位が低いのに気になったら考えてしまうのですｗお気遣い感謝致します。

68user

2021/01/22 10:16

"a","b"というタグが渡されて、"a" のみ新タグで INSERT した場合でも "a","b" 両方の id を知りたいのかなと思って、後から SELECT してみてはと書きました。INSERT した id のみ (この場合は "a") 知りたい場合はわたしが書いた方法は使えませんね。

hana_hana

2021/01/22 10:18

仰る通り両方の id を知りたいです。あ、やっとわかりました。なるほど。そちらを試してみます。度々ありがとうございます。

hana_hana

2021/01/22 10:22

最後に一点だけ失礼致します。普通に INSERT だけでも良いように思うのですが、INSERT IGNORE をご推奨されたのはどういった意味でしょうか？INSERT IGNORE は「あればスキップ」と理解しています。というこは、INSERTだけだと重複エラーが出るけど、INSERT IGNORE ならスキップされるのでエラーが出ないのでコードがキレイ。といった意味でしょうか？

68user

2021/01/22 10:42

INSERT INTO wp_tags (tag_name) values ('a'), ('b') というふうに複数レコードを一括で INSERT するイメージで考えていました。その場合、もし片方が登録済みだとエラーとなりもう片方の登録に失敗します。 INSERT IGNORE INTO wp_tags (tag_name) values ('a'), ('b') であれば、片方が登録済みでもエラーは無視され、もう片方の登録は成功します (試したわけではないのでそうならなかったら申し訳ございません)。あと、いま思い出しましたが、INSERT IGNORE は一意制約以外のエラーも無視するのでダメ、という話がありましたね。 https://qiita.com/arutemyan/items/1afebd383a1181f84a90 いまどきの MySQL は InnoDB で strict mode でもこういうよくない挙動なんでしたっけというのは調べていないのですが、もしそうなら INSERT IGNORE はやめて複数回 INSERT の方がよいかもしれません。さらについでにいうと、数個じゃなくて大量データなら、一時テーブルに全件 INSERT して、INSERT INTO SELECT ... が定石かと思います。参考: https://stackoverflow.com/questions/12948554/mysql-direct-insert-into-with-where-clause SQL 文の実行はクライアント->サーバ->クライアントとターンアラウンドの時間が長いので、SQL の回数をむやみに増やすのは避けたほうがよいと思います。とはいえ最終的には計測してみるしかないですね。

hana_hana

2021/01/22 10:56

2つの違いよくわかりました。なんとご丁寧に… 成功するかどうかの件と、一意制約以外のエラーの件、試してみます。事前にこれを知ることができて本当によかったです。大量のデータではなく一回につき２０個程度ですが、いつかのために一時テーブルの件覚えておきたいと思います。ありがとうございました！

行動規範の内容に同意します