質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

パフォーマンス

コード効率の向上や計算に関する質問には、このタグを使ってください。

Q&A

解決済

8回答

9225閲覧

MySQL 大容量データ INDEX 前方一致のLIKEが効かない

shin_tera

総合スコア27

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

パフォーマンス

コード効率の向上や計算に関する質問には、このタグを使ってください。

2グッド

5クリップ

投稿2016/09/06 02:52

編集2016/09/15 02:56

###前提・実現したいこと
以下の状況において、LIKE の前方一致によるINDEXが効かず、性能が出なくて困っております。。。
INDEXが効かない原因と対応策を教えて頂けないでしょうか。

<状況>
1: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;

2:上記テーブルは更新型テーブル。更新頻度は毎秒100件くらいのDELETE / INSERTを24時間実施。
(MyISAMのため、参照するとテーブルロックになり、更新はされません)
登録順はcol1をキーにcol5の分だけ削除し、削除したcol1の値でcol5の分だけ登録を行います。

3:上記テーブルは現在、1.5億レコード存在します。AUTO_INCREMENTの値は4.5億です。

4: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

5:実行計画

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だけで1件の表示になる値で実行計画を出しています。そのため、理想の実行計画としては、
refに「IX_url」が表示され、rows項目も1となる事を想定していました。
※仮に「http://abc.jp/xxx/xxxx/xxx%」とした場合、データが存在しないため、
ref項目に「IX_url」が表示され、rows項目も1となります。

6:その他注意事項
・URL項目に対して、Likeではなく、=による絞り込みはNGとなります。
・URLを階層別にハッシュ化して、=による絞り込みもNGとなります。
・URLを1文字ずつずらしてハッシュ化して、=による絞り込みはOKですが、
INDEXが最大数(64個)を超えてしまうため、出来ないと考えてます。
・MATCH AGAINSTはOKですが、この更新型テーブルにFULLTEXT INDEXを貼って効果があるかは、現在調査中です。

###発生している問題・エラーメッセージ
USE INDEX ( IX_url )やUSE KEY( IX_url )を利用しても、実行計画のref項目に「IX_url」は表示されず、
実際に実行してもものすごく時間がかかる(10分経っても終わらない)状況です。
これを数秒以内に結果が返ってくる形にしたいと考えています。

###該当のソースコード
上記SQL参照

###試したこと
上記説明参照

###補足情報(言語/FW/ツール等のバージョンなど)
MySQLのバージョンは5.6.22
phpMyAdminのバージョンは4.5.1(ちょっと古い)

実行計画や速度が遅く感じると判断した際の動作環境は
phpMyAdmin上での実行結果になります。

###追記
check table tb_test_data;
を実行した場合、2時間経っても終わらないため、実施していません。

check table tb_test_data fast quick;
にて、テーブルの状態を確認していますが、
statusは「Table is already up to date」の状態で、問題ない認識です。

show table status like 'tb_test_data%';
にて、断片化は確認をしていて、0.6%程度の断片化率です。
(該当テーブルのURL以外の指定における性能劣化は見られていません。)

###追記2
検索用URL(100文字等)を作ってINDEXを貼って検索するのは、個人的には良いのですが、
仕様決定者的にはNGでした。。

###追記3
index(col7,col1,url(200))の追加案については、
col7での絞込件数≒実際の絞込件数(urlによる絞り込みがほとんどない)
であれば、Limitが効くため、性能は良いですが、
検索結果が「1件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

index(col1,col7,url(200))の追加案については、
全体の件数≒col7での絞込件数≒実際の絞込件数(col7,urlによる絞り込みがほとんどない)
であれば、Limitが効くため、性能は良いですが、
検索結果が「1件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

なお、上記2つのINDEXでは、URLのINDEXには届かないため、1件抽出する場合、即レスポンスにならないです。

INDEXさえ効けば、検索結果が1件の場合、即終わるハズなのですが、
実行計画の時点で効かない原因を知りたいです。。

###追記4
InnoDBについて

InnoDB化した場合、300万の登録に60分経っても終わらなかったため、INDEXが効くかどうかは分かりません。
別の仕様(毎秒100件の削除・登録)が不可であるため、InnoDB化はできません。
(MyISAMであれば、300万の登録に7分で終わります)
InnoDBは統計情報がきれいなはずなので、InnoDBであれば、確かに効く気はしています。

###追記5
INDEXが効いたSQLと効かないSQLがありましたので、情報共有いたします。
”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。
ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。

MySQL5.6.22のバグなんじゃないかと思い始めてます。。

###追記6
AkiraPenguin 様の回答により、解決いたしました!
ありがとうございます!!!!!

なお、追記5の問題も、ちょうど50文字目が「」となっておりました。
」を含むURLは300万件存在するため、「_」の後に存在しないURLを置いていても、
それはINDEXが効きません。。。当然ですね。。。

たくさんの閲覧・回答ありがとうございました!

tanat, ikuwow👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答8

0

ベストアンサー

検索用の URL には '%', '_' などは含んでいませんか?
上記を含んでいる場合、適切にエスケープしないと文字列の途中にワイルドカードがある状態となり、極端にパフォーマンスが低下すると思われます。

また、'like' を 'like binary' としてはどうでしょうか?
多少レスポンスが良くなるのではと思います。

メモリ量が原因かどうか、同一データを仮想マシン上でメモリを増減させて検索時間を確認すると良いかもしれません。
結局、メモリの補強、DB用ストレージをSSDにするなどの対策が必要かもしれません。

投稿2016/09/13 09:39

AkiraPenguin

総合スコア51

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/14 03:55

おおおおおおおおおおおおおおおおおおおおおおおおおおおお!!!!! ありがとうございます!!! > 検索用の URL には '%', '_' などは含んでいませんか? ⇒%は含めていませんが、「_」が含まれておりました!! 急いで確認します。ありがとうございます!!
shin_tera

2016/09/15 02:56

おかげさまで、解決いたしました!! 本当にありがとうございます!!
AkiraPenguin

2016/09/15 10:57

いえいえ。お互い様です。 同じ問題で困っている方を見かけたら助けてあげて下さい。
guest

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は1億レコードの場合、処理が終わらないため、

実行しておりません。
その代り、定期的(2月に1回程度)に、別テーブルへのデータ移行&プログラム修正(参照先修正)を2日かけて行っています。

となると、相当な程度、統計情報が狂っている可能性があります。

更新頻度は毎秒100件くらいのDELETE / INSERTを24時間実施。

ということは 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
KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/06 04:18 編集

ANALYZE TABLEやOPTIMIZE TABLEは1億レコードの場合、処理が終わらないため、 実行しておりません。 その代り、定期的(2月に1回程度)に、別テーブルへのデータ移行&プログラム修正(参照先修正)を2日かけて行っています。 FORCE INDEXについて、ありがとうございます。 FORCE INDEXでも同じように、実行計画ではref項目はNULLとなってしまい、 「IX_url」は現れませんでした。実際に実行しても処理が終わらない状況でした。。。
KiyoshiMotoki

2016/09/06 06:35

返信ありがとうございます。 回答欄に追記させていただきました。 確認願います。
shin_tera

2016/09/06 08:44

ご確認・ご回答ありがとうございます。 > 相当な程度、統計情報が狂っている可能性があります。 ⇒ご指摘の通り、狂っている可能性が高いと思いますが、  現時点で狂ってる確証を得るための調べる術がなく。。。  なお、本現象(URLの前方一致のINDEXが効かない)については、  (データ移行前はもちろんですが)データ移行直後も発生しております。  そのことから、統計情報の狂いではないと推測(調べられない)してます。 >「(統計情報が)問題ない認識」 ⇒すみません。「fast quickの結果としては問題ない認識」となります。  そのため、「正しく閉じられているテーブル」である認識です。 >ストレージエンジンを InnoDB に変更することも検討してみてください。 ⇒ありがとうございます。  他のテーブルも全てMyISAMであり、テーブルロックの方が(別の要件で)都合が良く、影響調査が大変であるため、  InnoDBへの切替は躊躇していましたが、  本問題の解決策として、ちょっとやってみます。  分かり次第、質問に追記いたします。
退会済みユーザー

退会済みユーザー

2016/09/12 22:58

実行計画を見る限り複数のindex張りすぎで起きている気がする。
Panzer_vor

2016/09/14 10:53

超絶な横槍ですが、 ・DBエンジンはMyISAM ・更新系テーブル ・貼られたインデックス数が多い ・データ量も多い 遅かれ早かれ抜本的な対策を取らないといけないでしょうね・・・ ぎりぎりになって慌てるより、 可能であれば少しずつでも舵切りを進めた方が良さそう^^;
guest

0

まぁ第一に1.5億データとなるとパフォーマンス的にかなり厳しいでしょう
nosql系の処理に切り替えた方がよいかもしれません。

たしかtextにはインデックスって貼れないですよね?
別途textから50~100文字切り出した検索用varcharカラムを用意するなど
根本的な対策が必要じゃないでしょうか?

データ容量も相当でしょうから、何らかの切り分けやグルーピングができるなら
パーティショニングなどで分散管理したほうがよいかもしれません
MySQL 5.6.6 以降はSELECT時のロックが該当するパーティションのみになるよう
効率化されたようです

投稿2016/09/06 03:43

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/06 04:08

回答ありがとうございます。 >たしかtextにはインデックスって貼れないですよね? ⇒INDEX作成時にカラム名に文字数を設定する事で、  貼る事は可能(上記のCREATE TABLE文)ですが、  なぜか効かないんですよね。。。 そう考えると、確かに、varchar(200)等で切り出して、INDEXを貼るのは、効果的な気がします。ありがとうございます!
shin_tera

2016/09/06 05:38

質問に追記させて頂きましたが、検索用varcharカラムでは要件を満たせませんでした。。 > 別途textから50~100文字切り出した検索用varcharカラムを用意する「など」 ⇒こちら、他の根本的な対策には、何が考えられますでしょうか。
yambejp

2016/09/06 05:46

alter table tb_test_data add index(col1,col7,url(100)) のようなインデックスは貼ってあるという認識でよろしいですか?
shin_tera

2016/09/06 08:37

説明不足ですみません。 col7,col1,urlもcol1,col7,urlのINDEXも貼っていませんが、 検索結果が col7での絞込件数≒実際の絞込件数であったり、 全体の件数≒col7での絞込件数≒実際の絞込件数 であれば、(indexにurlを含めなくても) Limitが効くため、上記INDEXで性能が出ることは確認していますが、 検索結果が「1件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。 そのため、URLが1件、最初にINDEXを効かせたいのは、 urlもしくは、col7,urlになります。 また、説明を省いてしまいましたが、col7には、urlの一部が格納されているため、 urlのみのINDEXで大丈夫という認識でした。 ⇒結果、INDEXが効かないという事態に陥っています。。
yambejp

2016/09/06 09:42

col1は除外するのは了解しました 場合によってはFORCE INDEXないしUSE INDEXで任意のインデックスを 指定して検索してみるとか INDEX指示のある無しで効率が違うかEXPLAINで検証してみるとよいでしょう
guest

0

  • USE INDEXFORCE INDEX を付加したときと、しなかったときの、実行計画の違い
  • SHOW PROFILE により、どこに時間がかかっているかの分析

があれば助けになるような気がします。

”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。

ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。

ということですので、INDEX が効かない場合に、何に時間がとられているか…でしょう。

特定の場合にだけ劣化する場合

  • 一度、「col7 等は無視して、URL のみで該当する記事数を count で取るクエリ」若しくは、LIMIT 付きのクエリで URL に該当があるか、ないかだけを確認

し、その後発行するクエリを変えてみる手はあるかもしれません。ざっくりした言い方ですみません。

投稿2016/09/14 02:19

takotakot

総合スコア1111

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/14 03:55

回答ありがとうございます。 SHOW PROFILEは MySQL 5.6.7以降、非推奨なので、あまり信用できず。。 色々なSQLを実行していますが、 遅延原因はURLのINDEXが効いていないために、 別のINDEXによる検索が走っているのが原因になります。 もうちょっと調べてみます。
takotakot

2016/09/14 04:27

そうですね、特定の状況で、パフォーマンスが大幅に異なるという点で、私が以前行った質問 https://teratail.com/questions/43797 と似ているかなとは思ったのですが。INDEX が効かないのであれば、URL だけのクエリにしても効かないのかどうか、その際に USE INDEX をつけたら速度が変わってくるか…というのが気になるところでした。 unknown00012 様の言うように、そのための USE INDEX なので、どこにネックがあるのか…というところですね。
guest

0

私もTEXT型の列に128byteなどの指定をしてINDEXを作り運用しています。
直接的な解答にはならないかと思いますが、1億という数が影響している可能性があるのであれば確認のため5,000万、1,000万など数を落としてINDEXが働くか見てみるのは如何でしょうか。
INDEXのために必要なメモリや物理的な書き込みなどがボトルネックになっている可能性がないか切り分けが進められるかも知れません。

投稿2016/09/13 06:48

cnx

総合スコア19

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/14 03:52

回答ありがとうございます。 ためしに、col7で絞り込める件数(300万件)のみのテーブルを用意しましたが、 性能劣化が発生してしまっている状況です。
guest

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

km668

総合スコア65

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/12 17:28

回答ありがとうございます。 試しましたが、やはりURLのINDEXは実行計画・実測共に効きませんでした。 なお、col7はNOT NULL項目なので、NULLになる事はありませんので、 ご指摘の注意事項には該当しないと思われます。
guest

0

他のかたもコメントされてますが、3項目のインデックスをいくつか試したほうがいいんじゃないでしょうか?

INDEX(url(100), col1, col7) INDEX(col1, url, col7) INDEX(col7, col1, url)

とか。

投稿2016/09/06 09:01

kodai

総合スコア759

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/06 09:31

説明不足ですみません。 1億レコードあるうち、ヒットするURLが1件のみの場合、 INDEX(url(255))の効果がなく、性能劣化が発生します。 他のINDEXについては、試しており、質問に追記しました。
guest

0

col7,col1,url のインデックスを作成してみると良いかもしれません。
インデックスを使えないと、100万件で45秒ほどかかる(大型コンピュータ)処理です。
ただ、すでにインデックスはたくさんあり、テーブル更新から見ると時間がかかってしまいます。
既存のインデックスはすべて必要なんでしょうか?

投稿2016/09/06 03:23

maiko0318

総合スコア876

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shin_tera

2016/09/06 04:14

ありがとうございます。 INDEXが効けば、たとえ1億レコードあっても、1件のみの抽出に1秒かからないハズなんです。。 そのため、col7,col1,urlのインデックスですと、 col7で絞り込まれた100万件のスキャンがかかり、 45秒(こちらの環境で300秒)かかってしまう状況です。 なお、既存のINDEXは全て必要になります。 (URLのINDEXはこのためだけなので、他に方法があれば、URLのINDEXは違う形でも構いません)
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問