Q&A
ANALYZE TABLEやOPTIMIZE TABLEは１億レコードの場合、処理が終わらないため、
実行しておりません。
その代り、定期的（２月に１回程度）に、別テーブルへのデータ移行＆プログラム修正（参照先修正）を２日かけて行っています。
FORCE INDEXについて、ありがとうございます。
FORCE INDEXでも同じように、実行計画ではref項目はNULLとなってしまい、
「IX_url」は現れませんでした。実際に実行しても処理が終わらない状況でした。。。
###前提・実現したいこと
以下の状況において、LIKE の前方一致によるINDEXが効かず、性能が出なくて困っております。。。
INDEXが効かない原因と対応策を教えて頂けないでしょうか。
＜状況＞
１：CREATE TABLEテーブルは以下です。
SQL
1CREATE TABLE `tb_test_data` ( 2 `id` bigint(20) NOT NULL AUTO_INCREMENT, 3 `col1` bigint(20) NOT NULL DEFAULT '0', 4 `col2` int(11) NOT NULL DEFAULT '0', 5 `col3` float NOT NULL DEFAULT '0', 6 `col4` float NOT NULL DEFAULT '0', 7 `col5` int(3) NOT NULL DEFAULT '0', 8 `col6` int(11) NOT NULL DEFAULT '0', 9 `col7` bigint(20) NOT NULL, 10 `url` text, 11 `date` date NOT NULL, 12 PRIMARY KEY (`id`), 13 UNIQUE KEY UNI_col1_col5( col1, col5 ), 14 KEY `IX_col2` (`col7`, col2), 15 KEY `IX_col3` (`col7`, col3), 16 KEY `IX_col4` (`col7`, col4), 17 KEY `IX_col5_col1` (`col7`, col5, col1), 18 KEY `IX_col6` (`col7`, col6), 19 KEY `IX_col1_col5` (`col7`, col1, col5), 20 KEY `IX_date` (`col7`, date), 21 KEY `IX_url` ( url(255) ) 22) ENGINE=MyISAM DEFAULT CHARSET=utf8;
２：上記テーブルは更新型テーブル。更新頻度は毎秒１００件くらいのDELETE / INSERTを２４時間実施。
（MyISAMのため、参照するとテーブルロックになり、更新はされません）
登録順はcol1をキーにcol5の分だけ削除し、削除したcol1の値でcol5の分だけ登録を行います。
３：上記テーブルは現在、１．５億レコード存在します。AUTO_INCREMENTの値は４．５億です。
４：URL に INDEXを効かせたいSQLは以下のSQLです。
SQL
1SELECT T1.* 2FROM tb_test_data T1 3WHERE T1.col7 = 123456 4 AND T1.url like 'http://abc.jp/xxx/xxxx/xxx%' 5ORDER BY col1 6Limit 0, 100
５：実行計画
SQL
1id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 21 | SIMPLE | T1 | range | IX_col1,IX_col2,IX_col3,IX_col5_col1,IX_col6,IX_col1_col5,IX_date,IX_url | IX_col1_col5 | 8 | NULL | 1157468 | Using index condition; Using where
※上記の実行計画は「http://abc.jp/xxx/xxxx/xxx%」の部分を68文字のURLを指定し、このURLだけで１件の表示になる値で実行計画を出しています。そのため、理想の実行計画としては、
refに「IX_url」が表示され、rows項目も1となる事を想定していました。
※仮に「http://abc.jp/xxx/xxxx/xxx%」とした場合、データが存在しないため、
ref項目に「IX_url」が表示され、rows項目も1となります。
６：その他注意事項
・URL項目に対して、Likeではなく、=による絞り込みはNGとなります。
・URLを階層別にハッシュ化して、=による絞り込みもNGとなります。
・URLを１文字ずつずらしてハッシュ化して、=による絞り込みはOKですが、
INDEXが最大数（64個）を超えてしまうため、出来ないと考えてます。
・MATCH AGAINSTはOKですが、この更新型テーブルにFULLTEXT INDEXを貼って効果があるかは、現在調査中です。
###発生している問題・エラーメッセージ
USE INDEX ( IX_url )やUSE KEY( IX_url )を利用しても、実行計画のref項目に「IX_url」は表示されず、
実際に実行してもものすごく時間がかかる（１０分経っても終わらない）状況です。
これを数秒以内に結果が返ってくる形にしたいと考えています。
###該当のソースコード
上記SQL参照
###試したこと
上記説明参照
###補足情報(言語/FW/ツール等のバージョンなど)
MySQLのバージョンは5.6.22
phpMyAdminのバージョンは4.5.1（ちょっと古い）
実行計画や速度が遅く感じると判断した際の動作環境は
phpMyAdmin上での実行結果になります。
###追記
check table tb_test_data;
を実行した場合、２時間経っても終わらないため、実施していません。
check table tb_test_data fast quick;
にて、テーブルの状態を確認していますが、
statusは「Table is already up to date」の状態で、問題ない認識です。
show table status like 'tb_test_data%';
にて、断片化は確認をしていて、0.6%程度の断片化率です。
（該当テーブルのURL以外の指定における性能劣化は見られていません。）
###追記２
検索用URL（100文字等）を作ってINDEXを貼って検索するのは、個人的には良いのですが、
仕様決定者的にはNGでした。。
###追記３
index(col7,col1,url(200))の追加案については、
col7での絞込件数≒実際の絞込件数（urlによる絞り込みがほとんどない）
であれば、Limitが効くため、性能は良いですが、
検索結果が「１件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。
index(col1,col7,url(200))の追加案については、
全体の件数≒col7での絞込件数≒実際の絞込件数（col7,urlによる絞り込みがほとんどない）
であれば、Limitが効くため、性能は良いですが、
検索結果が「１件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。
なお、上記２つのINDEXでは、URLのINDEXには届かないため、１件抽出する場合、即レスポンスにならないです。
INDEXさえ効けば、検索結果が１件の場合、即終わるハズなのですが、
実行計画の時点で効かない原因を知りたいです。。
###追記４
InnoDBについて
InnoDB化した場合、３００万の登録に６０分経っても終わらなかったため、INDEXが効くかどうかは分かりません。
別の仕様（毎秒１００件の削除・登録）が不可であるため、InnoDB化はできません。
（MyISAMであれば、３００万の登録に７分で終わります）
InnoDBは統計情報がきれいなはずなので、InnoDBであれば、確かに効く気はしています。
###追記５
INDEXが効いたSQLと効かないSQLがありましたので、情報共有いたします。
”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。
ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。
MySQL5.6.22のバグなんじゃないかと思い始めてます。。
###追記６
AkiraPenguin 様の回答により、解決いたしました！
ありがとうございます！！！！！
なお、追記５の問題も、ちょうど50文字目が「」となっておりました。
「」を含むURLは300万件存在するため、「_」の後に存在しないURLを置いていても、
それはINDEXが効きません。。。当然ですね。。。
たくさんの閲覧・回答ありがとうございました！
回答8件
0
ベストアンサー
検索用の URL には '%', '_' などは含んでいませんか？
上記を含んでいる場合、適切にエスケープしないと文字列の途中にワイルドカードがある状態となり、極端にパフォーマンスが低下すると思われます。
また、'like' を 'like binary' としてはどうでしょうか？
多少レスポンスが良くなるのではと思います。
メモリ量が原因かどうか、同一データを仮想マシン上でメモリを増減させて検索時間を確認すると良いかもしれません。
結局、メモリの補強、DB用ストレージをSSDにするなどの対策が必要かもしれません。
投稿2016/09/13 09:39
総合スコア51
0
ANALYZE TABLE および OPTIMIZE TABLE は、定期的に実行していますか？
https://dev.mysql.com/doc/refman/5.6/ja/optimizing-queries-myisam.html
MySQL がクエリーをより適切に最適化できるようにするには、テーブルにデータがロードされたあとに、それに対して ANALYZE TABLE を使用するか、または myisamchk --analyze を実行します。
定期的に OPTIMIZE TABLE を使用して、動的フォーマット MyISAM テーブルの断片化を防ぎます。
また、
USE INDEX ( IX_url )やUSE KEY( IX_url )を利用しても、実行計画のref項目に「IX_url」は表示されず
とのことですが、FORCE INDEX 構文なら効くかもしれません（これも確実ではありませんが）。
https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html
USE INDEX (index_list) と同様の機能を持つが、テーブルスキャンが非常に負荷が大きいと見なされる点が追加された FORCE INDEX を使用することもできます。つまり、テーブルスキャンは、指定されたインデックスのいずれかを使用してテーブル内の行を検索する方法がない場合にのみ使用されます。
追記
ANALYZE TABLEやOPTIMIZE TABLEは１億レコードの場合、処理が終わらないため、
実行しておりません。
その代り、定期的（２月に１回程度）に、別テーブルへのデータ移行＆プログラム修正（参照先修正）を２日かけて行っています。
となると、相当な程度、統計情報が狂っている可能性があります。
更新頻度は毎秒１００件くらいのDELETE / INSERTを２４時間実施。
ということは 2ヶ月で約5億レコードの DELETE / INSERT が発生するため、
データ移行をする頃には、前回のデータ移行時からレコードが丸ごと入れ替わっていてもおかしくないからです。
check table tb_test_data fast quick;
にて、テーブルの状態を確認していますが、
statusは「Table is already up to date」の状態で、問題ない認識です。
とのことですが、
「（統計情報が）問題ない認識」
という意味なら、これは誤りです。
FAST オプションを指定すると、統計情報の更新は行なわないためです。
https://dev.mysql.com/doc/refman/5.6/ja/check-table.html
FAST 正しく閉じられていないテーブルのみを検査します。
可能であれば、ストレージエンジンを InnoDB に変更することも検討してみてください。
InnoDB であれば統計情報は自動的に更新されます（※1）し、
sql
1ALTER TABLE tb_test_data ENGINE InnoDB;
という SQL で OPTIMIZE TABLE と同じことを、テーブルロック無しに実現できる（※2）からです。
※1 http://nippondanji.blogspot.jp/2010/09/innodb.html
InnoDBの場合、ANALYZE TABLEは不要である。なぜなら、InnoDBが自発的に統計情報を更新するからだ。
※2 同上
InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。
※2 https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html
並列 DML ではあるが、テーブルコピーが引き続き必要
（中略）
「null」 ALTER TABLE ... ENGINE=INNODB ステートメントを使用したテーブルの再構築
投稿2016/09/06 04:05
編集2016/09/06 06:35
総合スコア4791
返信ありがとうございます。
回答欄に追記させていただきました。
確認願います。
ご確認・ご回答ありがとうございます。
> 相当な程度、統計情報が狂っている可能性があります。
⇒ご指摘の通り、狂っている可能性が高いと思いますが、
現時点で狂ってる確証を得るための調べる術がなく。。。
なお、本現象（URLの前方一致のINDEXが効かない）については、
（データ移行前はもちろんですが）データ移行直後も発生しております。
そのことから、統計情報の狂いではないと推測（調べられない）してます。
>「（統計情報が）問題ない認識」
⇒すみません。「fast quickの結果としては問題ない認識」となります。
そのため、「正しく閉じられているテーブル」である認識です。
>ストレージエンジンを InnoDB に変更することも検討してみてください。
⇒ありがとうございます。
他のテーブルも全てMyISAMであり、テーブルロックの方が（別の要件で）都合が良く、影響調査が大変であるため、
InnoDBへの切替は躊躇していましたが、
本問題の解決策として、ちょっとやってみます。
分かり次第、質問に追記いたします。
実行計画を見る限り複数のindex張りすぎで起きている気がする。
超絶な横槍ですが、
・DBエンジンはMyISAM
・更新系テーブル
・貼られたインデックス数が多い
・データ量も多い
遅かれ早かれ抜本的な対策を取らないといけないでしょうね・・・
ぎりぎりになって慌てるより、
可能であれば少しずつでも舵切りを進めた方が良さそう^^;
0
まぁ第一に1.5億データとなるとパフォーマンス的にかなり厳しいでしょう
nosql系の処理に切り替えた方がよいかもしれません。
たしかtextにはインデックスって貼れないですよね？
別途textから50~100文字切り出した検索用varcharカラムを用意するなど
根本的な対策が必要じゃないでしょうか？
データ容量も相当でしょうから、何らかの切り分けやグルーピングができるなら
パーティショニングなどで分散管理したほうがよいかもしれません
MySQL 5.6.6 以降はSELECT時のロックが該当するパーティションのみになるよう
効率化されたようです
投稿2016/09/06 03:43
総合スコア116443
回答ありがとうございます。
>たしかtextにはインデックスって貼れないですよね？
⇒INDEX作成時にカラム名に文字数を設定する事で、
貼る事は可能（上記のCREATE TABLE文）ですが、
なぜか効かないんですよね。。。
そう考えると、確かに、varchar(200)等で切り出して、INDEXを貼るのは、効果的な気がします。ありがとうございます！
質問に追記させて頂きましたが、検索用varcharカラムでは要件を満たせませんでした。。
> 別途textから50~100文字切り出した検索用varcharカラムを用意する「など」
⇒こちら、他の根本的な対策には、何が考えられますでしょうか。
alter table tb_test_data add index(col1,col7,url(100))
のようなインデックスは貼ってあるという認識でよろしいですか？
説明不足ですみません。
col7,col1,urlもcol1,col7,urlのINDEXも貼っていませんが、
検索結果が
col7での絞込件数≒実際の絞込件数であったり、
全体の件数≒col7での絞込件数≒実際の絞込件数
であれば、（indexにurlを含めなくても）
Limitが効くため、上記INDEXで性能が出ることは確認していますが、
検索結果が「１件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。
そのため、URLが１件、最初にINDEXを効かせたいのは、
urlもしくは、col7,urlになります。
また、説明を省いてしまいましたが、col7には、urlの一部が格納されているため、
urlのみのINDEXで大丈夫という認識でした。
⇒結果、INDEXが効かないという事態に陥っています。。
col1は除外するのは了解しました
場合によってはFORCE INDEXないしUSE INDEXで任意のインデックスを
指定して検索してみるとか
INDEX指示のある無しで効率が違うかEXPLAINで検証してみるとよいでしょう
0
USE INDEXやFORCE INDEXを付加したときと、しなかったときの、実行計画の違いSHOW PROFILEにより、どこに時間がかかっているかの分析
があれば助けになるような気がします。
”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。
ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。
ということですので、INDEX が効かない場合に、何に時間がとられているか…でしょう。
特定の場合にだけ劣化する場合
- 一度、「col7 等は無視して、URL のみで該当する記事数を count で取るクエリ」若しくは、LIMIT 付きのクエリで URL に該当があるか、ないかだけを確認
し、その後発行するクエリを変えてみる手はあるかもしれません。ざっくりした言い方ですみません。
投稿2016/09/14 02:19
総合スコア1111
回答ありがとうございます。
SHOW PROFILEは
MySQL 5.6.7以降、非推奨なので、あまり信用できず。。
色々なSQLを実行していますが、
遅延原因はURLのINDEXが効いていないために、
別のINDEXによる検索が走っているのが原因になります。
もうちょっと調べてみます。
そのための USE INDEX なんですが
/* https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html */
そうですね、特定の状況で、パフォーマンスが大幅に異なるという点で、私が以前行った質問
https://teratail.com/questions/43797
と似ているかなとは思ったのですが。INDEX が効かないのであれば、URL だけのクエリにしても効かないのかどうか、その際に USE INDEX をつけたら速度が変わってくるか…というのが気になるところでした。
unknown00012 様の言うように、そのための USE INDEX なので、どこにネックがあるのか…というところですね。
0
私もTEXT型の列に128byteなどの指定をしてINDEXを作り運用しています。
直接的な解答にはならないかと思いますが、1億という数が影響している可能性があるのであれば確認のため5,000万、1,000万など数を落としてINDEXが働くか見てみるのは如何でしょうか。
INDEXのために必要なメモリや物理的な書き込みなどがボトルネックになっている可能性がないか切り分けが進められるかも知れません。
投稿2016/09/13 06:48
総合スコア19
回答ありがとうございます。
ためしに、col7で絞り込める件数（300万件）のみのテーブルを用意しましたが、
性能劣化が発生してしまっている状況です。
0
カラムを等価な式に変えるとDBエンジンは等価と判断できないことを利用して
（DBエンジンをあえてあざむいて）、
SELECT T1.col7+0 = 123456 AND T1.url LIKE ....
SELECT CONCAT(T1.cok7,’’) = 123456 AND T1.url LIKE ....
のように col7と直に比較評価しない条件指定をすると、インデックスのあるurlの方を
優先的に比較評価するかもしれません。
注）col7がNULLであると言う条件を指定できない欠陥があります。
投稿2016/09/09 20:56
総合スコア65
回答ありがとうございます。
試しましたが、やはりURLのINDEXは実行計画・実測共に効きませんでした。
なお、col7はNOT NULL項目なので、NULLになる事はありませんので、
ご指摘の注意事項には該当しないと思われます。
0
col7,col1,url のインデックスを作成してみると良いかもしれません。
インデックスを使えないと、１００万件で４５秒ほどかかる（大型コンピュータ）処理です。
ただ、すでにインデックスはたくさんあり、テーブル更新から見ると時間がかかってしまいます。
既存のインデックスはすべて必要なんでしょうか？
投稿2016/09/06 03:23
総合スコア876
ありがとうございます。
INDEXが効けば、たとえ１億レコードあっても、１件のみの抽出に１秒かからないハズなんです。。
そのため、col7,col1,urlのインデックスですと、
col7で絞り込まれた１００万件のスキャンがかかり、
４５秒（こちらの環境で３００秒）かかってしまう状況です。
なお、既存のINDEXは全て必要になります。
（URLのINDEXはこのためだけなので、他に方法があれば、URLのINDEXは違う形でも構いません）
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/09/14 03:55
2016/09/15 02:56
2016/09/15 10:57