MySQLの検索の高速化方法について

Question

お世話になります。
MySQLで２つのテーブルを結合し検索を行ったところ、私のPCで検索結果が得られるまで136秒もかかってしまい、非常に困っています。
これを1～2秒程度に納めたいのですが、どこを改善していいのかご教示いただけないでしょうか。

検索でやりたいことは、
検索キーと一致するNAMEを検索し、これに対応するFILE_IDに対応づけられているすべてのCLASSとNAMEの組み合わせを検索し、
得られたCLASSとNAMEの組み合わせの検索数をその組み合わせごとに集計し、
検索数の多い順に、CLASSとNAMEの組み合わせを出力する、
というものです。

例えば、結合テーブルが次のような場合に検索キーを与えたときは、次のような結果を得るものです。

FILE_ID  GOODS_ID  CLASS  NAME
1        1         1      商品A
1        2         1      商品B
2        1         1      商品A
2        2         1      商品B
2        3         2      商品C
3        1         1      商品A
3        3         2      商品C

検索キー「商品A」の場合の検索結果：
CLASS NAME  NUMBER
1     商品A 3
1     商品B 2
2     商品C 2

検索キー「商品B」の場合の検索結果：
CLASS NAME  NUMBER
1     商品A 2
1     商品B 2
1     商品C 1

検索キー「商品C」の場合の検索結果：
CLASS NAME  NUMBER
1     商品A 2
1     商品C 2
1     商品B 1


（１）RELATION_GOODSテーブルの構造
CREATE TABLE IF NOT EXISTS RELATION_GOODS(
  FILE_ID INT NOT NULL,
  GOODS_ID INT NOT NULL,
  PRIMARY KEY(FILE_ID, GOODS_ID),
  INDEX FILE_ID(FILE_ID) USING BTREE,
  INDEX GOODS_ID(GOODS_ID) USING BTREE)

（２）GOODSのテーブルの構造
CREATE TABLE IF NOT EXISTS GOODS(
  GOODS_ID INT NOT NULL,
  CLASS INT NOT NULL,
  NAME VARCHAR(1000) NOT NULL,
  PRIMARY KEY(GOODS_ID),
  INDEX CLASS_GOODS(CLASS, NAME(255)) USING BTREE,
  INDEX GOODS(NAME(255)) USING BTREE,
  INDEX CLASS(CLASS) USING BTREE)

（３）検索式
SELECT B.CLASS, B.NAME, COUNT(*) AS NUMBER
FROM RELATION_GOODS AS A
  INNER JOIN GOODS AS B ON(A.GOODS_ID = B.GOODS_ID)
WHERE A.FILE_ID IN(
  SELECT A.FILE_ID
  FROM RELATION_GOODS AS A
    INNER JOIN GOODS AS B ON(A.GOODS_ID = B.GOODS_ID)
  WHERE B.NAME LIKE 'DVD'
)
GROUP BY B.CLASS, B.NAME
ORDER BY NUMBER DESC

（４）諸情報
テーブル名      レコード数  データ型 照合順序         サイズ    オーバーヘッド
GOODS              612,750  InnoDB   utf8_general_ci  69.6 MiB  --
RELATION_GOODS  12,352,490  InnoDB   utf8_general_ci   485 MiB  --

バージョン： MySQL5.6.24
（３）の実行時間：Showing rows 0 - 24 (30181 total, Query took 136.2325 seconds.)

yambejp · Accepted Answer

あ、ごめんなさい１個処理がぬけてた

SELECT D.CLASS,D.NAME,COUNT(*) AS NUMBER
FROM RELATION_GOODS AS A
INNER JOIN GOODS AS B ON A.GOODS_ID = B.GOODS_ID
AND B.NAME ='商品Ｂ'
INNER JOIN RELATION_GOODS AS C ON A.FILE_ID = C.FILE_ID
INNER JOIN GOODS AS D ON C.GOODS_ID = D.GOODS_ID
GROUP BY CLASS,NAME
ORDER BY NUMBER DESC

yambejp · Answer

explainで検証してみてください
一部インデックスが利いていないですね
GOODSテーブルのCLASS_GOODSを捨てて、3カラムでインデックスを貼ってみてはどうでしょう？

ALTER TABLE GOODS DROP INDEX CLASS_GOODS;
ALTER TABLE GOODS ADD INDEX ID_CLASS_GOODS(GOODS_ID,CLASS,NAME);

それでもインデックスが微妙なので、いくつかパターンを試してみるとよいかもしれません。

試しに集計テーブルをつくってためしてみてください

CREATE TABLE SHUKEI (CLASS INT NOT NULL,NAME VARCHAR(1000) NOT NULL,B_NAME VARCHAR(1000) NOT NULL,INDEX BNAME(B_NAME));

2回目以降は
TRUNCATE SHUKEI;

//データ投入
INSERT INTO SHUKEI
SELECT D.CLASS,D.NAME,B.NAME AS B_NAME
FROM RELATION_GOODS AS A
INNER JOIN GOODS AS B ON A.GOODS_ID = B.GOODS_ID
INNER JOIN RELATION_GOODS AS C ON A.FILE_ID = C.FILE_ID
INNER JOIN GOODS AS D ON C.GOODS_ID = D.GOODS_ID;

//結果表示
SELECT CLASS,NAME,COUNT(*) AS NUMBER
FROM SHUKEI
WHERE B_NAME ='商品Ｂ'
GROUP BY CLASS,NAME
ORDER BY NUMBER DESC

yambejp · Answer

よくよく考えたら・・・こういうことじゃない？

SELECT C.GOODS_ID,B.NAME,COUNT(*) AS NUMBER
FROM RELATION_GOODS AS A
INNER JOIN GOODS AS B ON A.GOODS_ID = B.GOODS_ID
AND B.NAME ='商品Ｃ'
INNER JOIN RELATION_GOODS AS C ON A.FILE_ID = C.FILE_ID
GROUP BY GOODS_ID,NAME
ORDER BY NUMBER DESC

yambejp · Answer

ちなみに・・・
60万件と1200万件のリレーションだとさすがに重いかもしれませんね
特定の期間やIDの範囲で集計用テーブルをつくっておくようなテクニックが必要かもしれません。

検索条件をうまく使えばパーティショニングなども効果があるかもしれません。

yambejp · Answer

とりあえずWHERE IN をはじいてみました
サンプルは同じ結果を返すと思うのですがちょっと自信なし
高速化しているかどうかは大規模なデータでテストしてみないとわかりません。

SELECT B.CLASS,B.NAME,COUNT(*) AS NUMBER
FROM RELATION_GOODS AS A
INNER JOIN 
(SELECT FILE_ID
FROM RELATION_GOODS AS A2
INNER JOIN GOODS AS B ON A2.GOODS_ID = B.GOODS_ID
WHERE B.NAME ='商品Ｃ'
) AS A2 USING(FILE_ID)
INNER JOIN GOODS AS B ON A.GOODS_ID = B.GOODS_ID
GROUP BY B.CLASS, B.NAME
ORDER BY NUMBER DESC

yambejp · Answer

例示したデータと、テーブルの構造があっていないので
いまいち何がしたいのかわかりません。

ちなみにFILE_IDとCLASSの用途はなんなのでしょう？

検索式についてはWHERE句でINを利用しているところが
ちょっと気になるかな・・・
またB.NAMEにワイルドカードなしでLIKEしていますが
完全一致なら「=」の方が効率がよいかも
またVARCHAR(1000)とかちょっと検索性を確保するにはどうかなぁ・・・

いずれにしろテストできていないので、
RELATION_GOODSテーブルとGOODSテーブルに
INSERT INTOで仮データを投入する例も書いてもらった方がよいかと

lowrider_2005 · Answer

まずは遅い原因がCPUなのかストレージなのかなど原因を突き止めるのが先決でしょう。
その上でCPUが遅ければ速いマシンを使うとか、ストレージをSSDにするとかオンメモリでDBを使うなどの対応をする。

うちは原則として必要以上に人手（プログラムやクエリの改修、チューニング）をかけません。
金で解決できること（CPU交換やSSD搭載）は金で解決するほうが結果的に安上がりなことがほとんどなので。

MySQLの検索の高速化方法について

あ、ごめんなさい１個処理がぬけてた

explainで検証してみてください

よくよく考えたら・・・こういうことじゃない？

ちなみに・・・

とりあえずWHERE IN をはじいてみました

例示したデータと、テーブルの構造があっていないので

まずは遅い原因がCPUなのかストレージなのかなど原因を突き止めるのが先決でしょう。

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング