Q&A
変数を入れた状態のクエリでexplainは行ってみましたか？
困っていること
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様
反応していただきありがとうございますm(__)m
変数を入れた状態のexplainで type=ALL となりました。
詳しい実行結果はm.ts10806様への返信にて記載させていただきます。
m.ts10806様
反応していただき感謝いたしますm(__)m
explainの結果を追記しました。
テーブル定義につきましては、ユーザ様の案件となっているためそのまま提示は
出来ないのですが、主に設定したのは以下の内容となります。
行数：約180万行(2.2GiB)
データ型：InnoDB
照合順序：utf8_generarl_ci
インデックス：20程ありますが、PrimaryKeyは最初の投稿のcodeとなっています。
その他必要な情報があれば提示させていただきます。
何卒ご助言のほどよろしくお願いいたしますm(__)m
修復の時にインデックスの再構築と統計情報の更新は行いましたか？
hihijiji様
コメントありがとうございます。
analyze tableは実施済でした。再構築は試していないので一度削除してから
作り直してみましたが、結果は変わらなかったです(T_T)
順番としては インデックスの再構築 → 統計情報の更新 ですね。
普段は必要ないのですが、物理的な不都合(インデックスの断片化やストレージの代替処理)
が疑われる時はやってみる価値があります。
hihijiji様
ありがとうございます。インデックス再構築後にもanalyzeしたのですが、状況変わらずです(>_<)
他のテーブルでも試したのですが、やはり変数を使うと全件走査になっているようでした。
@hoge とプロシージャ内での変数はまた扱いが違うのかもしれませんね…。
本日引き続き検証していたところ、なぜか昨日までは出ていなかった警告が出ましたので、
補足として元投稿に追記したいと思います。
回答2件
0
これでいけませんか？
SQL
1set @code = 'XXX'; 2set @sql ='select * from test where code = ?'; 3prepare stmt from @sql; 4execute stmt using @code;
投稿2020/10/20 11:14
総合スコア116726
なぜ変数で指定しなくてはいけないかがわかりませんが・・・
yambejp様
回答ありがとうございます！
その方法もWebで見つけて試してたのですが、バインド変数を使う方法は知りませんでした。。
この方法ですと、インデックスがしっかり効いてそうです。
変数を使わないといけない理由ですが、「そもそもの経緯」で記載した通り、プロシージャ内で
複数のレコードをフェッチしながら処理をしておりまして、最後にカレントレコードのキーを
条件にアップデートを実行しております。ここで変数に入れておいたキーが必要となっています。
対象件数が多くて10万件を超える想定なのですが、この方法で対応する場合、prepareまでを
ループ前に実装し、ループ内ではバインド変数だけを変更しながら実行ですよね。
件数が結構あるので、オーバヘッド等問題が無いかは確認したいと思います。
有益な情報をご提供いただき、ありがとうございますm(__)m
0
自己解決
解決？
プロシージャを貼り直すことで遅延が無くなりました…。
ストアドプロシージャのコンパイルの仕組みが良く分かっていませんが、この結果から推測すると
「通常のSQLは実行時に解析等の処理が実施されるが、ストアドプロシージャはコンパイル時に
使われているSQL全てが解析される。」と思われます。
通常は発生しない事象ですが、今回は対象のテーブルに対してインデックスの定義変更
（key→primarykey）を実施したために、コンパイル時の解析情報が古くなり、
実施時に正しいインデックス参照が出来なくなった。そして、プロシージャを貼り直す
（再コンパイルする）ことで、最新のテーブル定義で解析が行われ、改善した。というのが
自分の考えです。
間違っていたらごめんなさい…。
いろいろ助言等くださった皆様、ありがとうございましたm(__)m
投稿2020/10/21 07:16
総合スコア12
なんかよくわかりませんが、それで解決したなら問題ありません。
私もテストで変数を条件に検索したらインデックスの効き方が悪化するのを確認したので
プレースホルダーをおすすめしたまでです
yambejp様
ありがとうございました。プレースホルダーはいろいろと使えそうなので、技術の引き出しに
させていただきます！また何かあればよろしくお願いいたしますm(__)m
あなたの回答
tips
プレビュー
