MySQLのSELECT ステートメントでのLIMIT 節は
便利なので良く使っているが、
これは以外に性能を落とす原因となりうる。
ここでは、試験の得点成績の例の中で
同点順位の求め方を含めて
説明していく。
MySQLより引用
<<<<<
引数が 1 つの場合、その値は、戻り値として返す、
結果セットの冒頭からのレコード数を表す。
引数が 2 つの場合、最初の引数は戻り値として返す
最初のレコードまでのオフセットを表し、
2 つ目の引数は戻り値として返す最大レコード数を表す。
最初のレコードのオフセット値は 0（1 ではない）。
>>>>>
例えば、学校の生徒名簿があるとして
一年三組の成績10位-19位を求めるなら
SQL文は、
SELECT * FROM 生徒名簿 WHERE 学年 = 1 AND クラス = 3 ORDER BY 成績 LIMIT 9,10
である。
性能を落とす原因は、
- 最終結果セットを作る前の中間セットのレコードが大量である
(クラスの人数が100万人とか、、、)
- 中間セットをORDER BYで並べ替えしている
(成績順とか)
などである。
------------------------------------------------------
性能確保のためには、
中間セットを早い段階で
WHERE 節で絞り込むことが大切である
------------------------------------------------------
アイデア1 - 学年とクラス番号を組にした、インデックスを作る。
これは、簡単で極めて効果的である。
------------------------------------------------------
アイデア2 - 成績の順位をレコードに記す。
もし頻繁に成績を参照し(つまりLIMIT 節のSQLを使う頻度が高い)、
成績の更新頻度が稀ならば
成績の順位をレコードに記しておくことがよい。
レコードソート対象数(今回の例ならクラスの人数)が
100を超えたときに検討の価値有りと推測する。
そうすると、ソート対象レコードが極小になり時間節約、つまり、性能確保となる。
つまりこんなSQLになる
SELECT * FROM 生徒名簿 WHERE 学年 = 1 AND クラス = 3
WHERE 10 <= クラス順位 AND クラス順位 <= 19 ORDER BY クラス順位
で、クラス順位をどのように成績でつけるかが問題である。
クラス順位をレコードに記す方法はいろいろあるだろうが、
ユーザ変数を使う方法が便利である。
SET @srank = 0 ; UPDATE 生徒名簿 SET クラス順位 = @srank := @srank + 1 WHERE 学年 = 1 AND クラス = 3 ORDER BY 成績 DESC
成績は同点の場合がありうるので、クラス順位にはクラス順位と同点順位がありうる。
同点順位をきちんとつけるには、頭をつかう必要がある。
次の例を参考にして欲しい。
SET @pre_point = 100, @rank = 1, @srank = 0;
UPDATE 生徒名簿
SET
同点順位 =
if(@pre_point = 成績,
@rank,
@rank := @srank + 1 + (@pre_point := 成績) * 0) ,
クラス順位 = @srank := @srank + 1
ORDER BY 成績 DESC, 生徒番号
--------------------------------------
頭をつかうところは、
+ (@pre_point := 成績) * 0)
の部分である
--------------------------------------
No comments:
Post a Comment