goroumaru41gou

遊んでいる中でインプットした内容をアウトプットする場

トップ > database > DBにおけるインデックス付与について(検証編)

DBにおけるインデックス付与について(検証編)

database database-MySql Go-GORM

別記事「DBにおけるインデックス付与について」において、インデックスの付け方を記載したので、その効果を検証してみる。

取り扱うもの

  • DBインデックス付与による効果検証
  • GORMのインデックス
  • MySql

結論

長いので、結論から・・・

  1. Where句で使うカラムは、インデックスをつける（もちろん、カーディナリの高いカラム）

  2. 複合インデックスに含めたカラムは、すべてWhere句で使う。一部だけ使うと、インデックスが適用されない。 つまり、where句で組み合わせて使わないカラムは、複合インデックスとしない。

  3. 複合インデックスに含めるカラムのうちカーディナリの高いカラムは、先頭に近いカラムへ配置する。 これにより、2.で述べたwhere句へ一部のカラムを使っても、インデックスが適用される。

検証

MySQL Workbenchを使って、各ケースにおいてインデックス有無のDuration/Fetch変化を計測する。

検証対象のテーブル

Rowが約1700万ある。

f:id:goroumaru41gou:20200727210234p:plain

Columnは６つ。 f:id:goroumaru41gou:20200727210311p:plain

カーディナリは、ざっくり以下の通り。 今回の目的はインデックス効果検証なので、カーディナリは「検証する対象データ」を考える。

  • 検証する対象データ

    pair type price amount timestamp sequence_id
    1 2 10万 1万 4万 timestampと等しい

  • 運用を踏まえたとき

    pair type price amount timestamp sequence_id
    10 2 100万 10万 多い timestampと等しい

検証ケース一覧

検証ケース番号を最左欄へ「CaseNo.」とする。 インデックス付与するカラムには「Index」とし、インデックス名が同じ場合は、複合インデックスを表す。

CaseNo. pair type price amount timestamp sequence_id
1 - - - - - -
2 - - - - index_1 -
3 - index_2 - - index_2 -

追加：複合インデックスの優先順位をつけたケース（カーディナリの高いカラムを左欄へ）

CaseNo. timestamp sequence_id pair type price amount
4 index_2 - index_2 - - -

各CaseNo.において、３種類のクエリを実行する。

  • Q1. timestampで検索
    SELECT * FROM local.depths where timestamp = 1588660177238 LIMIT 0, 1000
  • Q2. timestampとtypeで検索
    SELECT * FROM local.depths where timestamp = 1588660177238 and type = "ask" LIMIT 0, 1000
  • Q3. timestampとtypeで検索、priceでソート
    SELECT * FROM local.depths where timestamp = 1588660177238 and type = "ask" order by price desc LIMIT 0, 1000

CaseNo.1　 indexを付与しない

f:id:goroumaru41gou:20200727210311p:plain

explainによる分析

  • 分析結果 type = ALLなのでテーブルを全検索しており、遅い。
 QueryNo.,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
    Q1,'SIMPLE','depths',NULL,'ALL',NULL,NULL,NULL,NULL,'17086351','10.00','Using where'
    Q2,'SIMPLE','depths',NULL,'ALL',NULL,NULL,NULL,NULL,'17086351','5.00','Using where'
    Q3,'SIMPLE','depths',NULL,'ALL',NULL,NULL,NULL,NULL,'17086351','5.00','Using where; Using filesort'

Dulation / Fetchによる分析

  • 分析結果

    QueryNo. Duration Fetch returned Row num
    Q1 0.0012 sec 5.932 sec 400 row(s)
    Q2 6.455 sec 0.000072 sec 202 row(s)
    Q3 6.612 sec 0.000034 sec 202 row(s)

Profilingによる分析

  • profilingクエリ
    set profiling = 1;
    select "ここへクエリ番号の内容をいれる"
    show profile;
  • 分析結果 Status欄Sending dataがボトムネックとなっているのがわかる。Sending dataは、検索の絞り込みと読み込みのこと。
    Status,Duration Q1,Duration Q2,Duration Q3
    starting,'0.000037','0.000046','0.000056'
    checking permissions,'0.000006','0.000005','0.000006'
    Opening tables,'0.000012','0.000011','0.000014'
    init,'0.000020','0.000028','0.000028'
    System lock,'0.000006','0.000007','0.000006'
    optimizing,'0.000008','0.000009','0.000011'
    statistics,'0.000013','0.000016','0.000019'
    preparing,'0.000010','0.000012','0.000013'
    'Sorting result',-,-,'0.000003'
    executing,'0.000002','0.000002','0.000002'
    Sending data,'5.820025','6.370191','0.000006'
    'Creating sort index',-,-,'6.443935'
    end,'0.000011','0.000012','0.000011'
    query end,'0.000006','0.000006','0.000006'
    closing tables,'0.000006','0.000006','0.000005'
    freeing items,'0.000105','0.000144','0.000145'
    cleaning up,'0.000012','0.000015','0.000012'

CaseNo.2　 timestampへindexを付与する

f:id:goroumaru41gou:20200727212216p:plain

分析結果の考察

  • カーディナリの高いカラムへインデックスを追加すると、驚くほど高速になる。

explainによる分析

  • 分析結果 type = refとなっているので、インデックスを利用してクエリを実行している。
    id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
    Q1,'SIMPLE','depths',NULL,'ref','index_1','index_1','9','const','400','100.00',NULL
    Q2,'SIMPLE','depths',NULL,'ref','index_1','index_1','9','const','400','50.00','Using where'
    Q3,'SIMPLE','depths',NULL,'ref','index_1','index_1','9','const','400','50.00','Using index condition; Using where; Using filesort'

Dulation / Fetchによる分析

  • 分析結果

    QueryNo. Duration Fetch returned Row num
    Q1 0.0013 sec 0.00038 sec 400 row(s)
    Q2 0.0013 sec 0.000062 sec 202 row(s)
    Q3 0.0014 sec 0.000063 sec 202 row(s)

Profilingによる分析

  • profilingクエリ
    set profiling = 1;
    select "ここへクエリ番号の内容をいれる"
    show profile;
  • 分析結果
    Status,Duration Q1,Duration Q2,Duration Q3
    starting,'0.000041','0.000040','0.000052'
    checking permissions,'0.000005','0.000004','0.000005'
    Opening tables,'0.000012','0.000011','0.000012'
    init,'0.000021','0.000026','0.000028'
    System lock,'0.000006','0.000006','0.000006'
    optimizing,'0.000007','0.000010','0.000009'
    statistics,'0.000057','0.000062','0.000061'
    preparing,'0.000009','0.000011','0.000015'
    'Sorting result',-,-,'0.000005'
    executing,'0.000002','0.000002','0.000002'
    Sending data,'0.001247','0.000785','0.000006'
    'Creating sort index',-,-,'0.000867'
    end,'0.000004','0.000004','0.000004'
    query end,'0.000005','0.000004','0.000005'
    closing tables,'0.000005','0.000004','0.000004'
    freeing items,'0.000087','0.000183','0.000171'
    cleaning up,'0.000011','0.000018','0.000010'

CaseNo.3　 typeとtimestampへindexを付与する

f:id:goroumaru41gou:20200727212253p:plain

分析結果の考察

  • カーディナリの低いカラムを複合インデックスへ追加しても、速度は大きく変わらない
  • 複合インデックスに含まれるカラムのうち、ひとつだけWhere句で使うと、インデックスが利用されない つまり、複合インデックスをすべてWhere句で使うと、インデックスが利用される。

explainによる分析

  • 分析結果
    id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
    Q1,'SIMPLE','depths',NULL,'ALL',NULL,NULL,NULL,NULL,'17086351','10.00','Using where'
    Q2,'SIMPLE','depths',NULL,'ref','index_2','index_2','11',"'const,const'",'202','100.00',NULL
    Q3,'SIMPLE','depths',NULL,'ref','index_2','index_2','11',"'const,const'",'202','100.00','Using index condition; Using filesort'

Dulation / Fetchによる分析

  • 分析結果

    QueryNo. Duration Fetch returned Row num
    Q1 0.0094 sec 5.929 sec 400 row(s)
    Q2 0.0011 sec 0.000061 sec 202 row(s)
    Q3 0.0012 sec 0.000062 sec 202 row(s)

Profilingによる分析

  • profilingクエリ
    set profiling = 1;
    select "ここへクエリ番号の内容をいれる"
    show profile;
  • 分析結果
    Status,Duration id1,Duration id2,Duration id3
    starting,'0.000039','0.000051','0.000057'
    checking permissions,'0.000005','0.000006','0.000005'
    Opening tables,'0.000011','0.000012','0.000013'
    init,'0.000022','0.000027','0.000026'
    System lock,'0.000006','0.000006','0.000006'
    optimizing,'0.000008','0.000009','0.000009'
    statistics,'0.000014','0.000069','0.000065'
    preparing,'0.000011','0.000011','0.000015'
    'Sorting result',-,-,'0.000005'
    executing,'0.000002','0.000002','0.000002'
    Sending data,'5.937721','0.000545','0.000006'
    'Creating sort index',-,-,'0.000627'
    end,'0.000013','0.000004','0.000004'
    query end,'0.000006','0.000005','0.000005'
    closing tables,'0.000006','0.000004','0.000004'
    freeing items,'0.000100','0.000186','0.000203'
    cleaning up,'0.000011','0.000017','0.000011'

CaseNo.4　 timestampをtypeより左カラムとし、CaseNo.3実行

左カラムへ移動することで、優先度を上げる。

f:id:goroumaru41gou:20200727212346p:plain

分析結果の考察

  • カラムの優先度を上げることで、複合インデックスに含まれるカラムの一部だけwhere句として使っても、インデックスが適用される

explainによる分析

  • 分析結果

    Q1についてもtype = refとなり、インデックスが使用できている。 これは、where句で使っているtimestampカラムの優先順位を上げたためであり、複合インデックスに含まれるカラムをすべてwhere句に使う必要がなくなる。

 id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
    Q1,'SIMPLE','depths',NULL,'ref','index_2','index_2','9','const','400','100.00',NULL
    Q2,'SIMPLE','depths',NULL,'ref','index_2','index_2','11',"'const,const'",'202','100.00',NULL
    Q3,'SIMPLE','depths',NULL,'ref','index_2','index_2','11',"'const,const'",'202','100.00','Using index condition; Using filesort'

Dulation / Fetchによる分析

  • 分析結果

    Q1に対しても、インデックスが利用されているので、高速になっている。

    QueryNo. Duration Fetch returned Row num
    Q1 0.0013 sec 0.00040 sec 400 row(s)
    Q2 0.0011 sec 0.000062 sec 202 row(s)
    Q3 0.0012 sec 0.000062 sec 202 row(s)

Profilingによる分析

  • profilingクエリ
    set profiling = 1;
    select "ここへクエリ番号の内容をいれる"
    show profile;
  • 分析結果
    Status,Duration id1,Duration id2,Duration id3
    starting,'0.000036','0.000046','0.000045'
    checking permissions,'0.000005','0.000005','0.000005'
    Opening tables,'0.000012','0.000013','0.000013'
    init,'0.000023','0.000028','0.000028'
    System lock,'0.000006','0.000006','0.000007'
    optimizing,'0.000007','0.000009','0.000009'
    statistics,'0.000060','0.000067','0.000065'
    preparing,'0.000010','0.000011','0.000015'
    'Sorting result',-,-,'0.000005'
    executing,'0.000002','0.000002','0.000002'
    Sending data,'0.001325','0.000524','0.000006'
    'Creating sort index',-,-,'0.000637'
    end,'0.000006','0.000004','0.000004'
    query end,'0.000005','0.000005','0.000005'
    closing tables,'0.000005','0.000004','0.000004'
    freeing items,'0.000099','0.000185','0.000184'
    cleaning up,'0.000012','0.000016','0.000011'

参考