SQLが重いのを改修したい

Question

現在oracle＋PHPで作られたプログラムの改修を行っています。
その内容が、oracleからMySQLへのDBの変更といった内容なのですが、そこで問題になったのがoracle独自の関数を用いて作られたSQLです。
今、問題なっているのが、

select * from (
  select t1.TENPO_CD as TENPO_CD,
         t1.TODOFUKEN_CD as TODOFUKEN_CD,
         t1.DATE as DATE,
         t1.KBN as KBN,
         t1.TANKA as TANKA,
         trim(format(t1.RESULT,0)) as RESULT, 
         t1.XX_CD as XX_CD,
         t1.XX_NAME as XX_NAME,
         t1.XX_NO as XX_NO,
         (select count(*) +1 from XXtable as t2 where 1 and CAST(CAST(t2.RESULT as UNSIGNED) as SIGNED) > CAST(CAST(t1.RESULT as UNSIGNED) as SIGNED) and concat(t2.KBN,t2.TANKA)= concat(t1.KBN,t1.TANKA)) as rank
    from XXtable as t1
   where TODOFUKEN_CD = '23' 
     and DATE like '2016-03-12%' 
     and DEL_FLG = 0 
  order byKBN asc,TANKA desc) as A1
where rank <= 20;
 

といったSQLです。
もともとはoracleのrow_numberを使って記載されていたものをMySQLに対応させてみました。
ラッピングしている中身だけであれば1秒かからず処理ができるのですが、

where rank <= 20;

の部分を対応させると30秒近くかかってしまいます。
もともとデータをほぼ全読みするものであるため、インデックスなども付けられず、件数はテストデータでは8000件程度です。
本番ではこの7倍が想定されていますので、到底耐えうるものではなくなってしまいました。
何か高速化する手段はないものでしょうか？

MySQLのバージョン（5.1.73）

Comments

[退会済みユーザー]

テーブル定義について、質問です。
RESULTの型が、不明です。
format(t1.RESULT,0)　を使っているので、小数点があると思いましたが、
CAST(CAST(t2.RESULT as UNSIGNED) as SIGNED)　と　CASTしているので
どんな値が、入っているのか気になります。

[T.Yokotani]

カラムの定義はchar型です。
ただし、中身は符号付数値です。

Answer 1

2016/03/15 15:08に退会済みユーザが投稿してる
ユーザ変数を使う方法ですが

OracleACEのAketiJyuuzouさんとyoku0825さんと
日本オラクルの木村明治さんによると
MySQLのユーザ変数は評価順序が未定義です。
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98

なので、結果が保証されませんので、
納品物件などでは、問題外の品質です。

Answer 2

興味深く参照させていただきました。

XXtableの1レコードに対して、
XXtableを全読みした場合のオーダーはn*n

ただ、
XXtableの全読みに対して、インデックスが利けば
実質nで済むので、
インデックスが利くようなSQL文を検討してみてはどうでしょうか？

OracleのRow_Numberだと
クイックソートでも使ってるでしょうから
n*log(n)なんでしょうね。

せめて、MySQLで

select colA
from XXtable a
where (select count(*)
         from (select 1 from XXtable b
                where b.colA=a.colA --順位を決める条件
                Limit 21) as TmpV ) <= 20;

と書ければいいのですが、
サブクエリの2段ネストでは、aはスコープ外になるので不可のようです。

Comments

[T.Yokotani]

回答ありがとうございます。
おっしゃられている通りでした。
プログラムのスコープと扱いが違うので、なかなか難しいですね。。。

Answer 3

MySQLのOffSetを忘れてました。
MySQLでもこれなら通りますね。

select colA
from XXtable a
where not exists(select 1 from XXtable b
                  where b.colA> a.colA --順位を決める条件
                 OffSet 19 Limit 1)

ということで、
1位から20位を取りたいのですから
21位以降は正確な順位は不要ということをふまえて
LimitとOffSetを使ってみました。

サブクエリでCountStopかかるので、
多少は早くなるはずです。

select t1.TENPO_CD as TENPO_CD,
       t1.TODOFUKEN_CD as TODOFUKEN_CD,
       t1.DATE as DATE,
       t1.KBN as KBN,
       t1.TANKA as TANKA,
       trim(format(t1.RESULT,0)) as RESULT, 
       t1.XX_CD as XX_CD,
       t1.XX_NAME as XX_NAME,
       t1.XX_NO as XX_NO
from XXtable as t1
where TODOFUKEN_CD = '23'
  and DATE like '2016-03-12%'
  and DEL_FLG = 0
  and not exists(select 1 from XXtable t2
                  where CAST(CAST(t2.RESULT as UNSIGNED) as SIGNED)
                      > CAST(CAST(t1.RESULT as UNSIGNED) as SIGNED)
                    and concat(t2.KBN,t2.TANKA)= concat(t1.KBN,t1.TANKA)
                 OffSet 19 Limit 1)
order by KBN asc,TANKA desc;

Comments

[T.Yokotani]

ご回答ありがとうございます！
このような考え方もあるのですね。
落ち着いたら試してみます！

Answer 4

テーブルCREATE

CREATE TABLE XXtable (
	id MEDIUMINT NOT NULL AUTO_INCREMENT,
	TENPO_CD      int(10),
	TODOFUKEN_CD  int(10),
	DATE          DATETIME,
	KBN           CHAR(3),
	TANKA         int(10),
	RESULT        CHAR(10),
	XX_CD         int(10),
	XX_NAME       CHAR(12),
	XX_NO         int(10),
	DEL_FLG       tinyint(1),
	PRIMARY KEY (id)
)

テストデータ作成

# coding:utf-8
import random
import time

# 0~100の乱数を生成
def randomAge():
    return random.randint(0, 100)

def randomTempo():
    return random.randint(100, 999)

def randomTodou():
    return random.randint(1, 47)

def randomTanka():
    return random.randint(500, 2000) / 100 * 500

# startからendの間でランダムな日付生成
def randomDate(start, end):
    format = '%Y-%m-%d %H:%M:%S'
    stime = time.mktime(time.strptime(start, format))
    etime = time.mktime(time.strptime(end, format))
    ptime = stime + random.random() * (etime - stime)
    return time.strftime(format, time.localtime(ptime))

# 出力するファイル名
OUTPUT_FILE = "TestData.sql"

# 登録するデータ件数
RECORD_COUNT = 8000

# 実行するSQLコマンド文字列
sqlCommands = ""

# 使用するデータベースを指定(今回はCreateTestData)
sqlCommands += "USE goods;\n"

# 登録するデータの数だけINSERT文を生成
for _ in range(RECORD_COUNT):

    # 登録するランダムなデータの生成
    date = randomDate("2015-12-12 00:00:00", "2015-12-20 00:00:00")
    result = str(randomAge()) + '.' + str(randomAge())

    tenpo = randomTempo() / 100 * 10
    tdou  = randomTodou()
    kbn   = randomTempo() / 100 * 10
    tanka = randomTanka()

    # ランダムなデータからInsert文を生成
    sqlCommands += "INSERT INTO XXtable" \
                   "(TENPO_CD, TODOFUKEN_CD, DATE, KBN, TANKA, RESULT, DEL_FLG) " \
                   "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', 0);\n"\
                   .format(tenpo, tdou, date, kbn, tanka, result)

# 生成したSQLコマンドをファイルに書き出す
f = open(OUTPUT_FILE, 'w')
f.write(sqlCommands)
f.close()

追加のSQL

-- KBN 昇順 TANKA 降順 のインデックス
alter table XXtable ADD KBN_TANKA int;
update XXtable set KBN_TANKA = KBN * 10000000 + ( 10000000 - TANKA);

-- RESULT 降順 のインデックス
alter table XXtable ADD RESULT_SORT int;
update XXtable set RESULT_SORT = 10000000 - RESULT * 1000;

-- RESULT KBN TANKA
 create index index_kbntankaresult_sort on XXtable (KBN_TANKA, RESULT_SORT);

MySQLのインデックスは、降順が出来ず、指定しても無視され昇順で、格納されるので、
降順で並ぶように細工が必要になります。

区分、TANKA 毎のランキング 上位２０位抽出SQL

select * from 
	( select 
		*,
		@kbn_rank := IF(@current_kbn = KBN_TANKA, @kbn_rank + 1, 1) AS kbn_rank, 
		@current_kbn := KBN_TANKA  
	  from (SELECT @kbn_rank := -1) s,
　　　　　　　　(SELECT @current_kbn := -1) c,
　　　　　　　　XXtable 
	  order by KBN_TANKA, RESULT_SORT
	 ) ranked 
where 
	kbn_rank <= 20 
	and TODOFUKEN_CD = '23' 
	and DATE >= '2015-12-17' and DATE < '2015-12-18' 
	and DEL_FLG = 0 ;

Comments

[退会済みユーザー]

マニュアルに
https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html
>ただし、ユーザー変数を含む式の評価の順序は、定義されていません。
とあるので

区分、TANKA 毎のランキング 上位２０位を抽出できることは、
保証されないのではないでしょうか？

[退会済みユーザー]

そうですね。
(SELECT @kbn_rank := -1) s,
(SELECT @current_kbn := -1) c,
XXtable
変数の初期化を忘れていました。
http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql

[退会済みユーザー]

https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html
マニュアルに
>1つのステートメントで複数回のユーザ変数を使った場合の
>評価順序が保証されない。
とあります。

いいかえれば
OrderBy句のソート順かつ、Select句の左から右に
ユーザ変数が評価されることが保証されてませんので、

インラインビューを増やして、-1で初期化しても
結果は保証されないと思います。
(たいていは、期待した結果になるようですが)

[退会済みユーザー]

まっやって見て　できてれば　OK.　
何を　心配しているのか、　まったく　私には、大学も出ていない、高校も出ていないので
わかりません。ただし、プログラムに関しては、マニュアルを読まずにソースを見て
動いているものを見て覚えています。
マニュアルを見て、理解して　プログラム出来る人は、天才だと思います。
IBMのスパーコンピュータのドキュメントすべて記憶している人の月収は、３００万とか聞きました。脱線しましたが、指摘の内容がわかりません。

[T.Yokotani]

terion様の例題を参考に高速化することができました。
ただ、グループ化したランキングごとに20位までという点についてはどうしてもうまくいかないようでしたので、その部分はプログラムにて対応することにいたしました。
長時間お力添えいただきましてありがとうございました。

[退会済みユーザー]

(退会済みユーザその1)さんが御指摘されてる
MySQLユーザ定義変数の評価順序については、
OracleACEのAketiJyuuzouさんとyoku0825さん
が分かりやすくかかれてますね。
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98

(退会済みユーザその2)さんは、御一読されてみてはどうでしょう？

Answer 5

terionさんの指摘も組み込んで、まとめてみました。
下記いかがでしょうか？

 select t1.TENPO_CD as TENPO_CD,
         t1.TODOFUKEN_CD as TODOFUKEN_CD,
         t1.DATE as DATE,
         t1.KBN as KBN,
         t1.TANKA as TANKA,
         trim(format(t1.RESULT,0)) as RESULT, 
         t1.XX_CD as XX_CD,
         t1.XX_NAME as XX_NAME,
         t1.XX_NO as XX_NO,
         (select count(t2.TEMPO_CD) +1  --count(*)より、主キーやNOT NULLのインデックスのはられた列をcountした方が高速です。
           from
             XXtable as t2
           where
             1 
             and CAST(CAST(t2.RESULT as UNSIGNED) as SIGNED) > CAST(CAST(t1.RESULT as UNSIGNED) as SIGNED) --ここでやりたいことってどういうことなんでしょうか？冗長な匂いがします。
             and concat(t2.KBN,t2.TANKA)= concat(t1.KBN,t1.TANKA)) as rank
    from XXtable as t1
   where TODOFUKEN_CD = '23' 
     and DATE >= '2016-03-12' and DATE < '2016-03-13'
     and rank <= 20 --rankを内側に入れ、ネストを１つ減らしました。
     and DEL_FLG = 0 --絞り込みに使いにくい条件はWhereの外側に、絞り込める条件を内側に書いた方が早くなる場合あります。
  order byKBN asc,TANKA desc) as A1

Comments

[T.Yokotani]

非常に丁寧な回答ありがとうございます。
rankに関しては私も内部でやってみたのですが、Unknown columnになってしまいます。
どうも条件には使えないようです。
また、countをキー項目で行うことも試してみたのですが、微々たる変化しかありませんでした。
分割してプログラムに一部機能を移植しようかと思いだしています。

[Odacchi]

実際に動作させることができないこともあり、動かない解答を送ってしまいすみません。

問題の本質は、8000件のcountを8000件のデータに対して毎回行っていることによって、
8000*8000回（O(n^2)）、つまりレコード件数の２乗に比例する時間がかかっていることだと思います。

ですので、下記の１番ネストが深い部分が何をしたいのかが分かれば、要件を満たす別のSQLを考えるなどもできるかもしれません。
ヒントになれば幸いです。

select count(t2.TEMPO_CD) +1
from
XXtable as t2
where
1
and CAST(CAST(t2.RESULT as UNSIGNED) as SIGNED) > CAST(CAST(t1.RESULT as UNSIGNED) as SIGNED) --ここでやりたいことってどういうことなんでしょうか？冗長な匂いがします。
and concat(t2.KBN,t2.TANKA)= concat(t1.KBN,t1.TANKA)) as rank

[T.Yokotani]

冗長ではないかという部分の意味ですが、そちらの項目は符号付数値が文字列として入っております。
そのため、unsignののち、signで定義しないと数値型として変換できなかったという経緯があります。
数値にしてから比較をしないとランキングがうまく出せなかったためです。

区分と単価毎にグループ化して、結果でソートしてグループ内のランキングを出すというものです。
この事故結合のところがやはり問題ですよね。。。。
結果1行に対して必ず1回SQLで全読みしてしまうというものなので、どうしても時間がかかりますよね。。。。
データベースの変更がこんなに大変だとは思っていませんでした。
SQLを少し変えればいいんだと思っていましたので、反省しております＾＾；

[Odacchi]

なるほどです。
このSQLは画面表示時などに毎回流すものでしょうか？

そうであれば、リアルタイム性は損なわれますが、
[XXtable]に[RANK]というカラムを追加し、
定期的にバッチでUPDATEかけるようにしてみてはいかがでしょうか？

[T.Yokotani]

こちら画面表示プログラムになります。
1画面で複数のランキングを一度に表示する機能であるため、グループごとのランキングが必要になっております。
ランクカラムについてなのですが、色々な条件で抽出をかけるため、抽出条件ごとに順位が変わるため、それもできなかったのです。。。
色々とご指導いただき、本当にありがとうございます。

Answer 6

グループ毎の上位20件を抽出するってことですよね？
難しいですね。
所で、サブクエリだけなら1秒で終わるんですか？count(*)の要素まで含めても？
それならソートをサブクエリの外に移したらどうでしょう？
サブクエリ内でソートしたものを、外側でrankで絞り込むためにまた、rankでソートしたりしてるんじゃないかなと思ったり。
※後、外にwhereを置いたSQLだとサブクエリ内のソートは無意味だったような気がします。(うろ覚え)

Comments

[T.Yokotani]

ご回答ありがとうございます。
サブクエリだけですと、count(*)を含めても1秒以内でした。（8000件でですが）
ソートを外で行ってみましたが、変化はありませんでした。

[hirohiro]

そうですか、お役に立てず申し訳ない。
後、これも有効かどうか不明ですが、countではなくexistsではどうでしょう？

and EXISTS (
SELECT *
FROM (
SELECT RESULT
FROM XXtable
WHERE KBN = t1.KBN
AND TANKA = t1.TANAKA
LIMIT 20
) t2
WHERE trim(format(t1.RESULT,0)) >= trim(format(t2.RESULT,0))
)
サブクエリ部分だけにして、countの部分を消し、絞込み条件に追加のつもりです。
自分のRESULTより大きな値がグループの上位20件に存在すれば..的な条件です。
※コード検証していません。サブクエリのLIMITはmysqlのバージョンで縛りがあったかも

mysqlのオプティマイザに過大な期待かもしれませんが、
サブサブクエリがグループ数分だけですむことに気づいてくれればひょっとして...

[T.Yokotani]

何度もありがとうございます。
上記試してみましたが、t1のスコープが届きませんでした。。。。

[hirohiro]

そうか、なるほど、そうですね。
ちょっとこの方面で進めていっても私に動作確認環境がないのと、遅い根本原因とずれている感じがしてきましたのでやめておこうと思います。すみません。

私もlilithchanさんが書かれているように8000件分の相関サブクエリでのcount集計が遅いのだと思っていましたが、(一秒が十分な速度かという問題はありますが)その点に問題がなかったのなら、Odacchiさんの方法でうまくいきそうな気がします。

[T.Yokotani]

色々教えていただきましてありがとうございました。
プログラム側に一部移植しつつ対応する方向にしていこうかと考え出しています。

Answer 7

MySQL でrankを出すとき
変数を使用すると、早くなります。
http://stackoverflow.com/questions/11084668/finding-rank-of-student-in-table

追記１
日付はLIKE検索してはいけません。
LIKE検索の場合はインデックスが使用されずフルテーブルスキャンが走ってしまいます。

 SELECT * FROM test WHERE c2 >= '2014-01-02' AND c2 < '2014-01-03';

Comments

[T.Yokotani]

回答ありがとうざいます。
変数については不勉強で、まだ使ったことがありませんでした。
少し調べてみます。

[退会済みユーザー]

LIKE問題がクリアになったので、インデックス出来るんじゃないかな？

[T.Yokotani]

上記ありがとうございます。
LIKEは確かにおかしいですね。。。以前のSQLのゴミでした。。。
こちらのインデックスに関しても対応してみましたが、あまり変わらずでした。
どうしても事故結合している部分をwhere句に入れると遅くなってしまいます。

変数に関してもcount(*)の部分に適用してみたのですが、グループが変わったときに初期化されず、データとしてNGになってしまいました。

[退会済みユーザー]

concat(t2.KBN,t2.TANKA)= concat(t1.KBN,t1.TANKA))　のぶぶん、
ちょっと、文字数減らすために、やっているのが　逆に遅くなっていますね。
これもインデックスを使えなくしている要素です。
(( t2.KBN = t1.KBN ) AND ( t2.TANKA= t1.TANKA)) と書けば　インデックスが効くと思います。
この部分で、結合しているので、結合しないように出来きる方法があるか、検討が必要かも、

[退会済みユーザー]

わかった。
参考サイト
http://qiita.com/hmuronaka/items/1afc132ddf400363efc

count でランキングを自分もやってたけど、300秒かかってしまい。
＠変数のranking にしたら、0.025 秒になったことがあります。
まさに、上記のサイトの書かれいることです。

[退会済みユーザー]

もうひとつ、グルーピングのランキングのやり方、自己結合なし変数版
http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql

[退会済みユーザー]

ユーザ変数で、row_numberなどの代用はよく見かけますが

マニュアルに
https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html
>ただし、ユーザー変数を含む式の評価の順序は、定義されていません。

とあり、MySQLのユーザ変数は、
OrderBy句のソート順で、なおかつ、Select句の左から右に評価することを
保証していないため。

MySQLのユーザ変数でのRankの代用は、結果も保証されないと思います。
(たいていは、期待した結果になるようですが)

[T.Yokotani]

terion様
実例ありがとうございます。qiitaの方はnot foundになってしまい拝見できませんでしたが、stackoverflowの方は確認いたしました。まさにやりたいのはこれなのですが、変数の使い方が想像以上に難しく。。。。勉強いたします！

HibinoMeguru様
回答ありがとうございます。
そちらも読ませていただきました。MySQLとOracleでこんな違いがあるとは思いませんでした。。。

[退会済みユーザー]

期待通りの結果を得られるかもしれませんが、それが確約されないSQLで、
仕事では使いものになりませんので、マイナス投票します。

http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html

>基本的なルールは、ステートメントの一部でユーザ変数値を割り当てないこと
>および同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、これは確約されていません。

Answer 8

深く読み込んだわけではないですが、下記のように外側のSelect句はいらないように思いますが、いかがでしょうか？

select t1.TENPO_CD as TENPO_CD,
         t1.TODOFUKEN_CD as TODOFUKEN_CD,
         t1.DATE as DATE,
         t1.KBN as KBN,
         t1.TANKA as TANKA,
         trim(format(t1.RESULT,0)) as RESULT, 
         t1.XX_CD as XX_CD,
         t1.XX_NAME as XX_NAME,
         t1.XX_NO as XX_NO,
         (select count(*) +1 from XXtable as t2 where 1 and CAST(CAST(t2.RESULT as UNSIGNED) as SIGNED) > CAST(CAST(t1.RESULT as UNSIGNED) as SIGNED) and concat(t2.KBN,t2.TANKA)= concat(t1.KBN,t1.TANKA) ) as rank
    from XXtable as t1
   where TODOFUKEN_CD = '23' 
     and DATE like '2016-03-12%' 
     and DEL_FLG = 0
     and rank <= 20
  order by KBN asc,TANKA desc

Comments

[T.Yokotani]

返答ありがとうございました。
試してみたのですが、なぜか１件になってしまいました。。。

[Odacchi]

ソースコードを修正しました。
rank <= 20の箇所を変えてます。
これで単純にスピードアップしませんか？

Answer 9

ORDER BYした結果を副問い合わせしてLIMITで件数を絞ればいいのではないでしょうか。

http://www.dbonline.jp/mysql/select/index12.html

たぶん、row_numberを改変した質問文のやり方ですと、8000件分のSELECT COUNT(*)が走ると思うので激重だと思います。

Comments

[T.Yokotani]

回答ありがとうございます。
グループごとにランキングを付けているので、LIMITが使えないのです。