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

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

ただいまの
回答率

88.62%

SQLが重いのを改修したい

解決済

回答 9

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 3,834

T.Yokotani

score 141

現在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)

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • 退会済みユーザー

    退会済みユーザー

    2016/03/15 09:01

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

    キャンセル

  • T.Yokotani

    2016/03/15 10:30

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

    キャンセル

回答 9

+6

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+5

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

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はスコープ外になるので不可のようです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/15 17:33

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

    キャンセル

+2

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;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/16 10:50

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

    キャンセル

+1

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/14 20:08

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

    キャンセル

+1

深く読み込んだわけではないですが、下記のように外側の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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/14 20:07

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

    キャンセル

  • 2016/03/14 21:35 編集

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

    キャンセル

+1

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/14 21:52

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

    キャンセル

  • 2016/03/14 23:12 編集

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

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

    キャンセル

  • 2016/03/15 10:39

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

    キャンセル

+1

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を内側に入れ、ネストを1つ減らしました。
     and DEL_FLG = 0 --絞り込みに使いにくい条件はWhereの外側に、絞り込める条件を内側に書いた方が早くなる場合あります。
  order byKBN asc,TANKA desc) as A1

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/15 14:42

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

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

    キャンセル

  • 2016/03/15 15:03

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

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

    キャンセル

  • 2016/03/15 19:26

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

    キャンセル

-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 毎のランキング 上位20位抽出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 ;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/15 16:41

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

    キャンセル

  • 2016/03/15 17:37

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

    キャンセル

  • 2016/04/04 17:22

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

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

    キャンセル

checkベストアンサー

-5

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/15 11:28

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

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

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

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

    キャンセル

  • 2016/03/15 14:45

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

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

    キャンセル

  • 2018/09/16 15:46

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

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

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

    キャンセル

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

  • ただいまの回答率 88.62%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る