MySQLとは｜一覧1000件を1件ずつ引くと、返す行が同じでも223.0倍かかった

一覧1000件を1件ずつ引くと、返す行が同じでも223.0倍かかった

索引が効いていても遅くなります。原因は探す時間ではなく、往復の回数です。

MySQLの索引が担うのは、探す部分です。マニュアルは索引は、特定の列の値を持つ行を素早く見つけるために使われると述べています。

探すのが速くても、問い合わせの回数が多ければ遅くなります。その分を数えました。

引き方を2つ比べる

一覧を出したあと、各行の関連を引く場面です。返す行の数はどちらも同じにしてあります。

1往復 0.8ms、1行を返す手間 0.002ms として計算する
一覧の件数を変えて、1件ずつ引く場合とまとめて引く場合を比べる

一覧の件数  1件ずつ（往復）  1件ずつ（時間）  まとめて（往復）  まとめて（時間）  比
10件                     11回            8.8ms                2回             1.6ms     5.4倍
50件                     51回           40.9ms                2回             1.7ms    24.1倍
200件                   201回          161.2ms                2回             2.0ms    80.6倍
1000件                 1001回          802.8ms                2回             3.6ms   223.0倍

1000件では802.8msと3.6msです。223.0倍の差になります。

行を返す手間はどちらも同じ2.0msです。残りの800.8msはすべて往復でした。

件数が増えると急に効く

10件では5.4倍にとどまります。試すときには気づきにくい差です。

比は件数にほぼ比例します。一覧が10件から1000件になれば、比も5.4倍から223.0倍へ広がります。

だから見るべきはその一覧が取りうる最大の件数です。平均ではありません。

索引がなければ、表の全体を最初から読む

費用は行数に比例します。マニュアルも、表が大きいほど高くつくと明記しています。

MySQLで索引がない場合の動きは、マニュアルに明記されています。

索引がなければ、MySQLは最初の行から始めて、該当する行を見つけるために表の全体を読み通さなければならないという記述です。続けて「表が大きいほど、この費用は大きくなる」とも書かれています。

行数に比例する

この比例のしかたは、PostgreSQLの記事で20万行を使って数えました。見に行く行は2万分の1、時間は203分の1でした。

比例するということは、試しに作った小さい表では差が出ないということでもあります。行が1000件なら、全部読んでも一瞬です。

2つの費用は別々にかかる

索引の有無と、往復の回数は別の話です。両方が同時に効きます。

1. 索引があって、まとめて引く。どちらも軽い
2. 索引があって、1件ずつ引く。往復だけが重い
3. 索引がなくて、まとめて引く。探す分だけ重い
4. 索引がなくて、1件ずつ引く。両方が掛かる

2番目が見つけにくいものです。1回ずつの問い合わせは速いので、記録を見ても遅い問い合わせとして出てきません。

見つけるには、1つの画面を出すのに何回問い合わせたかを数える必要があります。1回ごとの時間ではありません。

取り合いは行ではなく、索引の項目に掛かる

掛かる先が索引なので、同じ行を触っていなくても重なることがあります。

MySQLの行ロックは、行そのものに掛かるわけではありません。マニュアルはレコードロックとは、索引のレコードに対するロックであると定めています。

さらにギャップロックとは、索引レコードのあいだの隙間、あるいは最初の索引レコードの前や最後の索引レコードのあとの隙間に対するロックであるという種類もあります。

隙間に掛かるということは、まだ存在しない行の位置も押さえられるということです。重なる範囲が、行の数より広くなります。

重なる割合を変える

重なった分は順番待ちになります。担当を増やしてどこまで縮むかを数えました。

処理 4000件。1件 2ms。同じ行を取り合う分は順番待ちになる
取り合う割合を変えて、担当を増やしたときの短縮を見る

取り合う割合          1人        2人        4人        8人       16人  16人での短縮
0%                 8.00秒     4.00秒     2.00秒     1.00秒     0.50秒         16.0倍
5%                 8.00秒     4.20秒     2.30秒     1.35秒     0.88秒          9.1倍
20%                8.00秒     4.80秒     3.20秒     2.40秒     2.00秒          4.0倍
50%                8.00秒     6.00秒     5.00秒     4.50秒     4.25秒          1.9倍

5%重なるだけで、16人での短縮は9.1倍に落ちます。16.0倍から半分近くです。

20%なら4.0倍です。担当を4人から16人にしても、3.20秒が2.00秒にしかなりません。

重なりを広げるもの

1. 範囲を条件にした更新。隙間まで押さえる
2. 索引がない条件での更新。走査した行に掛かる
3. 同じ値に集中する索引。同じ項目を取り合う
4. 長い取引。押さえている時間が延びる

2番目が要注意です。索引がなければ全部の行を走査するので、その分だけ掛かる範囲が広がります。

つまり索引は、読みを速くするだけではありません。取り合う範囲を狭めるほうでも効きます。