mysqlでのSQL速度の改善について

Question

mysqlでのSQLについてアドバイスをいただきたく、投稿しました。

以下のようなDBレコードが、現在、120,000件程存在します。

id	this_key	ref_key	created_at
1	AAA	2017-07-21
2	BBB	ZZZ,AAA	2017-07-22
3	CCC	AAA	2017-07-23
4	DDD	BBB	2017-07-24

以下、クリエイト文です。

create table (
	id			BIGINT		AUTO_INCREMENT,
	this_key	TEXT,
	ref_key		TEXT,
	created_at	datetime,
	INDEX( id, this_key(150),  ref_key(150), created_at )
)

ref_key に含まれる文字列を this_key で参照し、レコードの相関表を出力する次のようなSQLを流しました。

select A.id, min(B.id) 
from hoge as A left join hoge as B on ( B.ref_key like concat( '%', A.this_key , '%' ) and B.created_at >= A.created_at )
group by A.id
order by A.id;

想定される出力結果は次のとおりです。

A.id	B.id
1	2
2	4
3	NULL
4	NULL

が、レコード件数増加にともない、このSQL文が処理が終わらず、戻ってこなくなりました。
（数時間たっても反応が無い状態です）

レコード数が1000件前後だったころは、問題もなく処理できていたのですが、
増加に伴いレスポンスが著しく低下してきている現状です。

ref_key に対する like演算子の影響で indexは機能していない状態なのですが、
速度の改善がみられるようなSQL文の記述方法、別のアプローチなどあれば、アドバイスいただけないでしょうか？

どうぞ、よろしくお願い申し上げます。

Comments

[退会済みユーザー]

インデックス情報がわかるように、CREATE TABLE で提示してください。

Answer 1

１つのカラムに複数の情報を入れていますね。これはリレーショナルモデルデータベースで最もやってはいけない手法のひとつです。DBの操作が非常に煩雑になる上にパフォーマンスは出ないし、データの整合が容易に起きてしてしまいます。

これを解決するには、this_key と ref_key の関係を表した 交差テーブル を作ることです。内容は下記のような感じです：

this_key	ref_key
BBB	ZZZ
BBB	AAA
CCC	AAA
DDD	BBB

---

こうしたデータベース設計のやっていけない事とその解決法をあつめた「SQLアンチパターン」という鉄板本があります。同著では今あなたが陥っている問題を Jaywalking(信号無視) というタイトルで紹介しています。すばらしい本ですので、ぜひ買って読んでみてください。

Comments

[dsk777]

なるほどです。勉強になりました。確かにこのアプローチで実現できそうです。書籍も買わせていただきました。

[dsk777]

交差テーブルを作成することで５分ほど処理が完了するようになりました。
ありがとうございました。

Answer 2

this_keyとref_keyがどうリンクしているのかよくわかりません。
普通に正規化してはいけないのでしょうか？
ちなみに前方後方一致「%文字%」はインデックスが効かないので遅いはずです

問題点

もともとテーブルの定義もおかしいです。

(1)ref_keyの重複にたいしてcreate_atが同じ日が存在した場合
どちらのidを取っていいかわかりません

id	this_key	ref_key	created_at
1	AAA	2017-07-21
2	BBB	ZZZ,AAA	2017-07-22
3	CCC	AAA	2017-07-23
4	DDD	BBB	2017-07-24
5	EEE	AAA	2017-07-22

---

(2)this_keyに重複がないという保証がありません
idはauto_incrementとのことなのできっとユニークなのでしょう

id	this_key	ref_key	created_at
1	AAA	2017-07-21
2	BBB	ZZZ,AAA	2017-07-22
3	CCC	AAA	2017-07-23
4	DDD	BBB	2017-07-24
2	AAA	2017-07-25

Comments

[dsk777]

ありがとうございます。
正規化というのは、this_keyとref_keyが完全に一致するように加工する？というイメージでしょうか？

このテーブル、実際のデータは、メールヘッダーのIDとReferencesがそれぞれthis_keyとref_keyに格納されています。
その為、Referencesの性質上、ref_keyの内容は性質上、これまでのメールの履歴（IDが羅列されたもの）となっており、
部分一致を使っての検索をおこなっている次第です。

おっしゃるとおり、完全一致ですとINDEXも効き、速度も大幅に改善するのが確認は取れましたが、
データとしての整合性が取れていませんでした。

Answer 3

like検索してる時点で手筋が限られててしまうのですが...。
方針としてはおそらくご理解していると思いますが、
0.hogeを正規化してもつ
のがベストです。これが無理な場合、私が思いつくところでは
1.hogeテーブルの更新に追随して変更されるようなindex付テーブルをつくる(Oracleのマテリアライズドビュー)
2.全文検索+FullTextつかう。
とかでしょうか(hogeテーブルの更新頻度とリアルタイム性要求で最適解は変わってくると思いますが)?
1はよくトリガーつかった方法が紹介されていますが、多分若干のラグがあるんじゃないかと思います。
(トランザクションはれば確実にatomicにできますが)
2は割とクセの有る動きをするので使いにくい代物ですが、質問の内容では問題ない可能性もあります。

Comments

[dsk777]

ありがとうございます。
無事、別表を一旦作成し、indexを貼ることで速度改善を実現することができました。