2010年6月28日

[mysql 15349] 検索条件カラムを縦持ちにした場合の検索の性能について。

松永です。

あるテーブルに紐付く検索条件項目が非常に多くある場合のテーブル設計/検索の仕方で悩んでいます。

普通に横持ちの場合、
create table a (
a_id int(10) not null,
fk_id int(10) not null,

name varchar(255) not null,
cond1 int(2),
cond2 int(2),
cond3 int(2),
....
primary key (a_id),
index (cond1, cond2, cond3,...)
)
のような感じで、条件が60以上並び、また条件が追加などされる可能性があるテーブルがあります。このテーブルは他のテーブルをjoinして使います。
検索軸としては、cond1 > 10 and cond3 = 20のように、必ず全ての条件カラムを使うとは限りません。

そこで、条件を下記のように縦持ちにしてみました。
また、条件はコードのようになっているので、cond1自体を001、cond2自体を002等とし、そのcond自体が持つコードを後ろに連結し、cond1が10の場合を00110(正しくは110)というintで表わすようにしました。

create table a (
a_id int(10) not null,
o_id int(10) not null,
name varchar(255) not null,
primary key (a_id)
)

create table cond (
a_id int(10) not null,
cond int(5) not null,
primary key (a_id, cond)
)

この状態で、他のtable(other)をjoinしつつ、cond1(001)が10あるいは20、かつcond2(002)が70あるいは80あるいは90の条件を見たすrecordが欲しい場合、
select a_id, o.val
from
a
inner join cond c1 using(a_id)
inner join cond c2 using(a_id)
inner join other o using(o_id)
where
c1.cond between 00110 and 00120
and c2.cond between 00270 and 00290;
というsqlを実行してみたのですが、結構重い状態です。
condにはテストデータが135万件程度入ってる状態で、 初回実行(キャッシュに乗っていないと思われる状態)で、8秒近く掛る場合もある為、ちょっと性能的に問題外な状態になってしまってます。

本番の場合はcondは1000万件になる予定です。また、このqueryは発行される数は、5q/s程度考えられます。

これ、なにか考え方が間違っているのでしょうか。
なにか指摘頂けるとありがたいです。


下記に上記内容を纏めたsqlを添付します。
---------------------------------------------
create table other (
o_id int(10) not null,
val varchar(255) not null,
primary key(o_id)
);
create table a (
a_id int(10) not null,
o_id int(10) not null,
name varchar(255) not null,
primary key (a_id),
index (a_id, o_id)
);
-- 条件テーブル
create table cond (
a_id int(10) not null,
cond int(5) not null,
primary key (a_id, cond)
);
insert into other (o_id, val) values(1,'1'), (2,'2'), (3,'3');
insert into a (a_id, o_id, name) values (1, 1, '1'), (2, 1, '2'), (3,2,'3'),(4,2,'4');
insert into cond (a_id, cond) values(1, 00110), (2, 00120), (1, 00201), (2, 00270), (2,00230), (3,00290);

-- explain
select a_id, o.val
from
a
inner join cond c1 using(a_id)
inner join cond c2 using(a_id)
inner join other o using(o_id)
where
c1.cond between 00110 and 00120
and c2.cond between 00270 and 00290;


--
MATSUNAGA Ichiro
e-mail: vikke.bsd@xxxxx
/.j: http://slashdot.jp/~vikke/
last.fm: http://www.lastfm.jp/user/vikke_bsd/
twitter: http://twitter.com/vikke

Just remember - when you think all is lost, the future remains.
-- Robert H. Goddard

GPG fingerprint = DCEF C86E 2930 45D0 0941 E977 4DCE A95F 3914 4BED

投稿者 xml-rpc : 2010年6月28日 21:38
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/97763
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。