MySQLでwhere句に変数を使うとindexが効かない

困っていること

MySQL5.7環境で、where句にユーザ定義変数を使うと、インデックスが効きません。
具体的には以下のような実装となります。

set @code = 'XXX'
select * from test where code = @code

code列は文字列型で、プライマリーキーです。レコード件数は１００万件を超えています。
体感で3～5秒程度のレスポンスとなっています。

@codeの位置に直接文字リテラル'XXX'を記載すると、一瞬で処理が終わります。
explainでも変数の場合は全件走査、文字リテラル直接指定だとインデックスが有効と
なる結果が返ってきます。

** explain 変数利用時 **
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:--|:--|:--|
|1|SIMPLE|test||ALL|||||1756058|100|Using where|

** explain 文字リテラル直接指定時 **
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:--|:--|:--|
|1|SIMPLE|test||const|PRIMARY|PRIMARY|152|const|1|100||

原因として考えられることがあれば、どなたか助言のほどよろしくお願いします。

そもそもの経緯

上記は要約した形で記載しておりますが、元々はストアドプロシージャ内で発生した事象になります。

cursorを定義(testテーブル)
select結果を入れる変数を定義
cursorをオープンしてフェッチ（2の変数に代入しながらループ）
任意の処理（データ加工など）
testテーブルをcodeを指定してupdate ※ここでSQLが遅延

別のセッションからshow processlistで見ると「5」がプライマリーキー指定にも
かかわらず５秒程度時間がかかっていることが発覚。
固定文字に置き換えると再現しなくなったので、変数を条件にしていることが
原因と予想し、最初に記載した内容で検証したところ同様の事象が発生。

補足情報

MySQLのバージョンは5.7.31です。
元々はインデックスが有効となっていたはずなのですが、別件でDBが破損したことにより
再構築をしてから、当該事象が発生するようになりました。。
環境周りの設定を疑い、以前の状態と比較したのですが、それらしい設定での相違は
見当たりませんでした...。打つ手がない状況です。

2020.10.21追記
検証中に、利用しているDBツール(HeidiSQL)で以下の警告が表示されました。（昨日までは出ていなかったんですけど...）

Warnings from last query:
Warning: Cannot use ref access on index 'code' due to type or collation conversion on field 'code'

対象フィールドの型と、変数の方が不一致なので暗黙的な型変換がされてるよ。っていう感じのこと…？
確かに変数には型指定していない（できない）ので、納得できるような気もしますが…。

maisumakun

2020/10/20 06:17

変数を入れた状態のクエリでexplainは行ってみましたか？

m.ts10806

2020/10/20 06:20

テーブル定義と実行計画の結果をご提示ください

nakatomodesu

2020/10/20 06:50

maisumakun様反応していただきありがとうございますm(__)m 変数を入れた状態のexplainで type=ALL となりました。詳しい実行結果はm.ts10806様への返信にて記載させていただきます。

nakatomodesu

2020/10/20 07:10

m.ts10806様反応していただき感謝いたしますm(__)m explainの結果を追記しました。テーブル定義につきましては、ユーザ様の案件となっているためそのまま提示は出来ないのですが、主に設定したのは以下の内容となります。　行数：約180万行(2.2GiB) 　データ型：InnoDB 　照合順序：utf8_generarl_ci 　インデックス：20程ありますが、PrimaryKeyは最初の投稿のcodeとなっています。その他必要な情報があれば提示させていただきます。何卒ご助言のほどよろしくお願いいたしますm(__)m

hihijiji

2020/10/20 08:28

修復の時にインデックスの再構築と統計情報の更新は行いましたか？

nakatomodesu

2020/10/20 08:56

hihijiji様コメントありがとうございます。 analyze tableは実施済でした。再構築は試していないので一度削除してから作り直してみましたが、結果は変わらなかったです(T_T)

hihijiji

2020/10/20 10:14

順番としてはインデックスの再構築 → 統計情報の更新ですね。普段は必要ないのですが、物理的な不都合(インデックスの断片化やストレージの代替処理) が疑われる時はやってみる価値があります。

nakatomodesu

2020/10/21 00:57

hihijiji様ありがとうございます。インデックス再構築後にもanalyzeしたのですが、状況変わらずです(>_<) 他のテーブルでも試したのですが、やはり変数を使うと全件走査になっているようでした。 @hoge とプロシージャ内での変数はまた扱いが違うのかもしれませんね…。本日引き続き検証していたところ、なぜか昨日までは出ていなかった警告が出ましたので、補足として元投稿に追記したいと思います。

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

回答2件

これでいけませんか？

SQL
1set @code = 'XXX';
2set @sql ='select * from test where code = ?';
3prepare stmt from @sql;
4execute stmt using @code;

投稿2020/10/20 11:14

yambejp

総合スコア114747

yambejp

2020/10/20 11:57

なぜ変数で指定しなくてはいけないかがわかりませんが・・・

nakatomodesu

2020/10/21 00:55

yambejp様回答ありがとうございます！その方法もWebで見つけて試してたのですが、バインド変数を使う方法は知りませんでした。。この方法ですと、インデックスがしっかり効いてそうです。変数を使わないといけない理由ですが、「そもそもの経緯」で記載した通り、プロシージャ内で複数のレコードをフェッチしながら処理をしておりまして、最後にカレントレコードのキーを条件にアップデートを実行しております。ここで変数に入れておいたキーが必要となっています。対象件数が多くて10万件を超える想定なのですが、この方法で対応する場合、prepareまでをループ前に実装し、ループ内ではバインド変数だけを変更しながら実行ですよね。件数が結構あるので、オーバヘッド等問題が無いかは確認したいと思います。有益な情報をご提供いただき、ありがとうございますm(__)m

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

自己解決

解決？

プロシージャを貼り直すことで遅延が無くなりました…。

ストアドプロシージャのコンパイルの仕組みが良く分かっていませんが、この結果から推測すると
「通常のSQLは実行時に解析等の処理が実施されるが、ストアドプロシージャはコンパイル時に
使われているSQL全てが解析される。」と思われます。

通常は発生しない事象ですが、今回は対象のテーブルに対してインデックスの定義変更
（key→primarykey）を実施したために、コンパイル時の解析情報が古くなり、
実施時に正しいインデックス参照が出来なくなった。そして、プロシージャを貼り直す
（再コンパイルする）ことで、最新のテーブル定義で解析が行われ、改善した。というのが
自分の考えです。

間違っていたらごめんなさい…。

いろいろ助言等くださった皆様、ありがとうございましたm(__)m

投稿2020/10/21 07:16

nakatomodesu

総合スコア12