複数キーのインデックスのパフォーマンス

複数キーでユニークになるテーブルにインデックスを作成するときに一番多いユースケースはそのうちの一部のキーである場合に、その一番多いユースケースのキーだけのインデックスを作るべきか迷ったので簡単なテーブルで実験してみました。

DDL

CREATE TABLE test_tbl(
  col1 BIGINT,
  col2 BIGINT,
  col3 BIGINT,
  col4 BIGINT,
  col5 VARCHAR(255)
);

CREATE INDEX ui ON test_tbl(col1,col2,col3,col4);

データ

800万件のデータで実験してみました。

キーの組み	カーディナリティ
col1	25%
col1, col2	1.25%
col1, col2, col3	0.00625%
col1, c0l2, col3, col4	一意（0.00125%）

（最初は100万件のデータでやるつもりでしたが間違えて800万件作ってしまいました）

結果

結果としては一部キーしか使わなくても私のケースでは十分に検索のパフォーマンスは出る、というものでした。（一応全部の行に載せていますが、インデックスがない時のパフォーマンスは検索対象のカラムによる影響はほぼなかったです）

where 条件	index	no index
col1	1 m 22 s	21 s
col1, col2	1 m 20 s	0.01 s

なんでこうなるのか？というところは相変わらず曖昧なままだったのでもう少し別の角度から調査をしたいと思います。具体的にはBTREEのアルゴリズムをちゃんと理解するとこの辺の予測もしやすくなりそうなのでBTREEを深掘りしたいと思います。

ちなみに色々な組み合わせの結果

col1, col3 のように間を飛ばしたりするともうちょっとパフォーマンス落ちるのかなと思いましたが組み合わせによってはそうでもない感じでした。この辺はデータの入り方にもよると思うのですがBTREEアルゴリズムを理解しているともう少し想像と実際の乖離が少なくなるのかもしれないと期待しています。

where	index	no index
col1, col3	1 m 20 s	0.32 s
col1, col4	1 m 20 s	15.25 s
col2, col3	1 m 20 s	0.05 s
col2, col4	1 m 20 s	0.00 s
col3, col4	1 m 20 s	0.88 s
col2	1 m 20 s	0.04 s
col3	1 m 20 s	0.85 s
col4	1 m 20 s	1 m 9 s

データの作成

Haskell で Bulk Insert の SQL ファイルを生成する簡単なスクリプトを作りました。

sql_generator.hs

import Data.List (intercalate)
import System.Directory.Internal.Prelude (getArgs)

col2 :: Int -> [Integer]
col2 n
  | n `mod` 4 == 1 = [1..500]
  | n `mod` 4 == 2 = [501..1000]
  | n `mod` 4 == 3 = [1001..1500]
  | otherwise = [1501..2000]

col234Values :: [Integer] -> [Char]
col234Values a = intercalate "," (map show a) ++ ", '" ++ ['A'..'z'] ++ "'"

toValues :: String -> [String] -> String
toValues col1  s = intercalate ",\n" (map (\s -> "    (" ++ col1 ++ "," ++ s ++ ")") s)

main = do
  (inputStr:_) <- getArgs
  let input = read inputStr
  let col1 = ((input-1) `div` 4) + 1

  let d = [[a,b,c] | a <- col2 input, b <- [1..200], c <- [1..5]]
  let t = map col234Values d
  let v = toValues (show col1) t
  putStrLn "INSERT INTO test_tbl(col1, col2, col3, col4, col5) VALUES"
  putStrLn v
  putStrLn ";"

seq 16 | xargs -I@ -P4 bash -c "runghc sql_generator.hs @ > data@.sql"

seq 16 | xargs -I@ echo "mysql -u root -ppassword test < data@.sql" | bash