TiDBでのインデックス設計

Slide 1

Slide 1 text

Slide 2

Slide 2 text

本日の位置づけ  https://pingcap.co.jp/event/  日時  講演内容  2022年7月28日(木) 14:00-15:00  PingCAP Education：TiDBでの表設計  2022年9月9日(金) 14:00-15:00  PingCAP Education：TiKV-トランザクション＆MVCC  2022年10月28日(金) 14:00-15:00  PingCAP Education：TiDB でのロック競合のトラブルシューティング  2022年11月25日(金) 14:00-15:00  PingCAP Education：TiDBでのスロークエリの特定と対応方法  2022年12月09日(金) 14:00-15:00  PingCAP Education：TiDB Data Migrationのご紹介  2022年12月22日(木) 14:00-15:00  PingCAP Education：TiDB でのインデックス設計  2023年01月13日(金) 14:00-15:00  PingCAP Education：インデックスに関するチューニング  2023年01月17日(火) 13:00-14:00  入門編：分散DBはどこで使えるのか？〜DBアーキテクチャの課題解決事例をフィンテック業界を中心に解説〜  2023年01月20日(金) 14:00-15:00  PingCAP Education：TiDBでの索引設計  ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists  

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Copyright ©2022 PingCAP. All Rights Reserved. 表の KV マッピング・ルール(1)~クラスタ表  • クラスタ表の KV マッピングルール   Key: tablePrefix{ TableID }_recordPrefixSep{ Col1 } Value: [col2, col3, col4]   col1 は Cluster Index 列となる、Keyに含まれる  TiDBでの表設計: https://pingcap.co.jp/event-webinar-edu-table-design-thank-you/   SQL例)  mysql> create table t_clustered (col1 int, col2 varchar(10),col3 varchar(10),col4 varchar(10),  primary key(col1) clustered);  mysql> insert into t_clustered   values (1,'aaa','bbb','ccc'),(2,'aaa','bbb','ccc'),(3,'aaa','bbb','ccc'),(4,'aaa','bbb','ccc'),(5,'aaa','bbb','ccc'); 

Slide 7

Slide 7 text

Copyright ©2022 PingCAP. All Rights Reserved. 表の KV マッピング・ルール(1)~クラスタ表  col1  col2  col3  col4  1  aaa  bbb  ccc  2  aaa  bbb  ccc  3  aaa  bbb  ccc  4  aaa  bbb  ccc  5  aaa  bbb  ccc  t91_r1 --------- aaa,bbb,ccc  t91_r2 --------- aaa,bbb,ccc  t91_r3 --------- aaa,bbb,ccc  t91_r4 --------- aaa,bbb,ccc  t91_r5 --------- aaa,bbb,ccc  KEY ------------ VALUE  Key: tablePrefix{ TableID }_recordPrefixSep{ Col1 } … Value: [col2, col3, col4] 

Slide 8

Slide 8 text

Copyright ©2022 PingCAP. All Rights Reserved. 表の KV マッピング・ルール(2)~非クラスタ表  • 非クラスタ表の KV マッピングルール   SQL例)  mysql> create table t_nonclustered (col1 int, col2 varchar(10),col3 varchar(10),col4 varchar(10),  primary key(col1) nonclustered);  mysql> insert into t_nonclustered values(1,'aaa','bbb','ccc'),(2,'aaa','bbb','ccc'),(3,'aaa','bbb','ccc'),(4,'aaa','bbb','ccc'),(5,'aaa','bbb','ccc');  Key: tablePrefix { TableID }_recordPrefixSep{ _Tidb_RowID } Value: [col1, col2, col3, col4] KV ペアの「Value」は実際の行データとなる   _Tidb_RowID はシステムにより自動的に生成された一意の整数となる 

Slide 9

Slide 9 text

Copyright ©2022 PingCAP. All Rights Reserved. 表の KV マッピング・ルール(2)~非クラスタ表  col1  col2  col3  col4  1  aaa  bbb  ccc  2  aaa  bbb  ccc  3  aaa  bbb  ccc  4  aaa  bbb  ccc  5  aaa  bbb  ccc  t93_r1 --------- 1,aaa,bbb,ccc  t93_r2 --------- 2,aaa,bbb,ccc  t93_r3 --------- 3,aaa,bbb,ccc  t93_r4 --------- 4,aaa,bbb,ccc  t93_r5 --------- 5,aaa,bbb,ccc  KEY ------------ VALUE  Key: tablePrefix { TableID }_recordPrefixSep{ _Tidb_RowID } … Value: [col1, col2, col3, col4] 

Slide 10

Slide 10 text

Copyright ©2022 PingCAP. All Rights Reserved. 表の KV マッピング・ルール(3)~まとめ  • クラスタ表のKVマッピングルール   Key: tablePrefix { TableID }_recordPrefixSep{ _Tidb_RowID } Value: [col1, col2, col3, col4] Key: tablePrefix{ TableID }_recordPrefixSep{ Col1 } Value: [col2, col3, col4] KV ペアの「Value」は実際の行データとなる    col1 は Cluster Index 列となる、Keyに含まれる  • 非クラスタ表のKVマッピングルール  

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Copyright ©2022 PingCAP. All Rights Reserved. 非一意インデックス  Key: tablePrefix{TableID}_indexPrefixSep{IndexID}_indexedColumnsValue_{RowID}  Value: null  • インデックスの KV マッピングルール   SQL例)  mysql> create table employee (id int, name varchar(10), age int, role varchar(25),  key idx_name (name)); 

Slide 13

Slide 13 text

Copyright ©2022 PingCAP. All Rights Reserved. id  name  age  role  100  Jack  50  Manager  101  Jack  23  Engineer  102  Jack  30  Sales  103  Tom  25  Engineer  Key: tablePrefix{TableID}_indexPrefixSep{IndexID}_indexedColumnsValue_{RowID} … Value: null  非一意インデックス(例)  idx_name  KEY ---> VALUE  t95_r1 ---> [100,”Jack”,50,”Manager”]   t95_r2 ---> [101,”Jack”,23,”Engineer”]   t95_r3 ---> [102,”Jack”,30,”Sales”]   t95_r4 ---> [103,”Tom”,25,”Engineer”]   KEY ---> VALUE  t95_i2_Jack_1 ---> null  t95_i2_Jack_2 ---> null  t95_i2_Jack_3 ---> null  t95_i2_Tom_4 ---> null 

Slide 14

Slide 14 text

Copyright ©2022 PingCAP. All Rights Reserved. 一意インデックス  Key: tablePrefix{tableID}_indexPrefixSep{indexID}_indexedColumnsValue  Value: RowID  • UK & 非クラスタ表の主キーの KV マッピングルール   SQL例)  mysql> create table employee (id int, name varchar(10), age int, salary int,  unique idx_id(id));  mysql> create table employee (id int, name varchar(10), age int, salary int,  primary key idx_id(id) nonclustered); 

Slide 15

Slide 15 text

Copyright ©2022 PingCAP. All Rights Reserved. Key: tablePrefix{tableID}_indexPrefixSep{indexID}_indexedColumnsValue … Value: RowID  一意インデックス(例)  idx_id  KEY ---> VALUE  t95_r1 ---> [100,”Jack”,50,”Manager”]   t95_r2 ---> [101,”Jack”,23,”Engineer”]   t95_r3 ---> [102,”Jack”,30,”Sales”]   t95_r4 ---> [103,”Tom”,25,”Engineer”]   id  name  age  role  100  Jack  50  Manager  101  Jack  23  Engineer  102  Jack  30  Sales  103  Tom  25  Engineer  KEY ---> VALUE  t95_i3_100 ---> 1  t95_i3_101 ---> 2  t95_i3_102 ---> 3  t95_i3_103 ---> 4 

Slide 16

Slide 16 text

Copyright ©2022 PingCAP. All Rights Reserved. v6.0 以降~  v6.0 からより多い collation をサポートするために new collation (new_collations_enabled_on_first_bootstrap)   がデフォルトに有効になっております。その場合、インデックスにおけるマッピング・ルールは以下に変更されました。  Key: tablePrefix{tableID}_indexPrefixSep{indexID}_sortKeys_{RowID}  Value: indexedColumnsValue  ● 非一意インデックス  Key: tablePrefix{tableID}_indexPrefixSep{indexID}_sortKeys  Value: RowID_indexedColumnsValue  ● 一意インデックス  sortKeys : システムにより自動的に計算された値となります。 

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Copyright ©2022 PingCAP. All Rights Reserved. TiDB ならではのインデックスの更新  ・Google Percolate モデルによるトランザクションを実現しております。   ➔ 2PCやプライマリロックといったところにより原子性を維持する仕組みとなります。  ➔ TiKV-トランザクション＆MVCC: https://pingcap.co.jp/event-webinar-edu-tikv-mvcc-thank-you/      ・インデックスはトランザクションでの更新と伴って更新されます。インデックスの更新も   　プライマリ・ロックに依存していることにより表データとの整合性を維持しております。  

Slide 19

Slide 19 text

Copyright ©2022 PingCAP. All Rights Reserved. インデックスの作成(1)  • TiDB ではインデックスを作成する構文が MySQL と互換しています。   • インデックスはテーブルを作成する際に、一緒に作られます。   CREATE TABLE t1 (  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  c1 INT NOT NULL,  c2 INT NOT NULL,  key idx_t1_c1 (c1)  ); 

Slide 20

Slide 20 text

Copyright ©2022 PingCAP. All Rights Reserved. インデックスの作成(2)  • 既存のテーブルに ALTER TABLE.. ADD INDEX により新規インデックスを追加可能となります。   -- c2 列にインデックス idx_t1_c2 を追加する  ALTER TABLE t1 ADD INDEX idx_t1_c2(c2);  -- id 列に一意インデックス uidx_t1_id を追加する  ALTER TABLE t1 ADD UNIQUE INDEX uidx_t1_id(`id`);  • v6.2 以降には1つの ALTER TABLE … ADD INDEX 句で同時的に複数のインデックスの作成も　サポートされるようになりました。   • TiDB では、インデックスの作成(Online DDL)によりテーブルへの Write/Read をブロックすることはありません。  ＊トランザクションの中で Online DDL が実行されたら、表構造が変更されたことにより、”Information schema is changed” が発生し、トランザクションが失敗することがあります。v6.3 から Metadata lock との機能を提供し、Write/Read をブロックすることも可能となります。  

Slide 21

Slide 21 text

Copyright ©2022 PingCAP. All Rights Reserved. 複合インデックス(1)  CREATE TABLE t2 (  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  c1 INT NOT NULL,  c2 INT NOT NULL,  c3 INT NOT NULL,  key idx_t2 (c1, c2, c3)  );  • 複合インデックスを使用する際には、leftmost prefix matching ルールに基づきます。   Key: tablePrefix{TableID}_indexPrefixSep{IndexID}_indexedColumnsValue_{RowID}  Value: null  • KV マッピングルール(非一意の場合)     　(c1,c2,c3の列値) 

Slide 22

Slide 22 text

Copyright ©2022 PingCAP. All Rights Reserved. • コスト削減: 1つの複合インデックス idx_t2(c1,c2,c3) を作成すると、実際には３つの (c1), (c1,c2), 　 (c1,c2,c3) 索引として使えます。   -- (c1,c2,c3) の複合インデックスが有効に使えるケース   select * from t2 where c1 between 1 and 20;  select * from t2 where c1 = 3 and c2 > 5 and c2 < 10;  select * from t2 where c1 = 1 and c2 = 10 and c3 between 1 and 200;   select * from t2 where c3 between 1 and 200 and c2 = 10 and c1 = 1; --自動的に順序を調整する     --部分的に使えるケース   select * from t2 where c1 between 1 and 20 and c2 = 100; -- (c1)  select * from t2 where c1 = 3 and c2 > 5 and c2 < 10 and c3 = 100; -- (c1, c2)    -- 使えないケース  select * from t2 where c2 = 20 and c3 = 100;  select * from t2 where c3 = 100;  複合インデックス(2) 

Slide 23

Slide 23 text

Copyright ©2022 PingCAP. All Rights Reserved. • Covering Index : インデックスだけで参照データを取得できるなら、インデックスを直接スキャンすることで、参照データ全て取得できます。表への参照処理は必要がなくなったため、 I/O 操作が節約できます。そのため、実際の運用時、Covering Index はパフォーマンス・チューニングの手段の１つとして挙げられます。     　Covering Index としての使用方法例)   select c1 from t2 where c1 = 1 and c2 = 10 and c3 between 1 and 200;   select c1, c2, c3 from t2 where c1 = 1 and c2 between 1 and 200;   複合インデックス(3) 

Slide 24

Slide 24 text

Copyright ©2022 PingCAP. All Rights Reserved. 関数インデックス(1)  • 関数インデックスは１つのテーブルに1つ以上の列に適用された関数の結果に定義されるインデックスとなります。  　例）  CREATE INDEX idx1 ON t1 ((lower(col1)));     　関数インデックスに関数の部分は ( ) で囲む必要がある  • tidb_allow_function_for_expression_index から関数インデックスがサポートされている関数   mysql> select @@tidb_allow_function_for_expression_index;  +--------------------------------------------------------------+  | @@tidb_allow_function_for_expression_index |  +--------------------------------------------------------------+  | lower, md5, reverse, upper, vitess_hash |  +--------------------------------------------------------------+ 

Slide 25

Slide 25 text

Copyright ©2022 PingCAP. All Rights Reserved. • 関数インデックスに定義された関数は select 句に含まれる場合、オプティマイザは当該関数インデックスを使用します。　例） SELECT lower(col1) FROM t;  SELECT * FROM t WHERE lower(col1) = "a";  SELECT * FROM t WHERE lower(col1) BETWEEN "a" AND "b";   SELECT * FROM t ORDER BY lower(col1);  SELECT min(col1) FROM t GROUP BY lower(col1);  関数インデックス(2) 

Slide 26

Slide 26 text

Copyright ©2022 PingCAP. All Rights Reserved. 不可視のインデックス  • 不可視のインデックス(Invisible Indexes) はオプティマイザにより使用することはありません。   • 不可視というのはオプティマイザに限られていて、ユーザによる削除や変更はできます。   • ALTER INDEX によりインデックスの VISIBLE 属性を(VISIBLE または INVISIBLE に)修正できます。   • Primary Key を不可視( INVISIBLE)にすることができません。   • USE INDEX のヒントを使っていても、オプティマイザが不可視インデックスを使用することはありません。     　SQL 例)  CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE(c2));  CREATE UNIQUE INDEX idx_c1 ON t1 (c1) VISIBLE;  ALTER TABLE t1 ALTER INDEX idx_c1 INVISIBLE; 

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Copyright ©2022 PingCAP. All Rights Reserved. 注意事項  • FULLTEXT、HASH および SPATIAL インデックスをサポートしていない   • MySQL 5.7 の動作と同じ、降順インデックスをサポートしていない   • CLUSTERED の PRIMARY KEY を後から追加することはサポートしていない   • CLUSTERED の PRIMARY KEY の削除はサポートしていない   • MySQL で提供している use_invisible_indexes=on といった optimizer_switch は TiDB ではサポートしていない  

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Copyright ©2022 PingCAP. All Rights Reserved. インデックスの Region 配分情報 SHOW TABLE [table_name] INDEX [index_name] REGIONS [WhereClauseOptional];   SHOW TABLE t1 index idx_t1_b REGIONS;   +----------------+----------------------------------------------------------------+---------------------------------------------------------------+------------------+-----------------------------+-------- +-----------+   | REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |   +-------------+-----------------------------------------------------+------------------------------------------------------+-------+----------+----------+----------+----------------+-----------+--------+- ---------+  | 59 | t_53_ | t_53_i_1_0380000000000000fa030000000000 | 60 | 1 | 60 | 0 | 1323 | 0 | 4 | 29032 |   | 61 | t_53_i_1_0380000000000000fa030000000000 | t_53_i_1_0380000000000001f4030000000000 | 62 | 1 | 62 | 0 | 1323 | 0 | 2 | 0 |   | 63 | t_53_i_1_0380000000000001f4030000000000 | t_53_i_1_0380000000000002ee030000000000| 64 | 1 | 64 | 0 | 1323 | 0 | 8 | 116401 |   | 65 | t_53_i_1_0380000000000002ee030000000000 | t_53_i_2_ | 66 | 1 | 66 | 0 | 27 | 0 | 12 | 146948 |   +-------------+-----------------------------------------------------+------------------------------------------------------+-------+----------+----------+----------+----------------+-----------+--------+- ---------+  ご参考) SHOW TABLE REGIONS  

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Copyright ©2022 PingCAP. All Rights Reserved. Quiz (1) 以下の TiDB データベースのインデックスに関する説明の中で、誤っているのは？    A. インデックスの作成により関連表への読み込む/書き込む処理をブロックすることがない    A. 一般的には、非一意インデックスの KV ペアの Value 値が NULL となる    A. クラスタ表の PRIMARY KEY は削除できない     A. TiDB で、全ての関数が関数インデックスに使える    

Slide 33

Slide 33 text

Copyright ©2022 PingCAP. All Rights Reserved. Quiz (1) 以下の TiDB データベースのインデックスに関する説明の中で、誤っているのは？    A. インデックスの作成により関連表への読み込む/書き込む処理をブロックすることはない    A. 一般的には、非一意インデックスの KV ペアの Value 値が NULL となる    A. クラスタ表の PRIMARY KEY は削除できない     A. TiDB で、全ての関数が関数インデックスに使える    

Slide 34

Slide 34 text

Copyright ©2022 PingCAP. All Rights Reserved. Quiz (2)  CREATE TABLE t (  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  a INT NOT NULL,  b INT NOT NULL,  c INT NOT NULL,  d INT NOT NULL,  key idx_abc (a, b, c)  );  A. select * from t where c = 4 and a = 100 and b < 99;   B. select * from t where c = 100 and b = 99;   C. select a, b, c, d from t;   D. select a, b from t;  以下のDDL にてテーブルt およびインデックス idx_abc を作成した場合、選択肢の中でどれが索引idx_abc を使える？(2つを選ぶ) 

Slide 35

Slide 35 text

Copyright ©2022 PingCAP. All Rights Reserved. Quiz (2) 解説 CREATE TABLE t (  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  a INT NOT NULL,  b INT NOT NULL,  c INT NOT NULL,  d INT NOT NULL,  key idx_abc (a, b, c)  );  A. select * from t where c = 4 and a = 100 and b < 99;   B. select * from t where c = 100 and b = 99;   C. select a, b, c, d from t;   D. select a, b from t;   以下のDDL にてテーブルt およびインデックス idx_abc を作成した場合、選択肢の中でどれが索引idx_abc を使える？(2つを選ぶ)   正解は AとDとなります。 A についてはオプティマイザが Where 条件を並び替えして、 where a = 100 and b < 99 and c = 4とします。そこで、aがマッピングできて、bの条件もマッピングできます。ただ、b に関する検索条件は=ではないので、次のcに関する検索条件は索引使えないですが、aとbの検索条件に使えますので、Aは正解となります。   Bについてはオプティマイザが Where 条件を並び替えしても、 where 条件にaに関する条件がないため、leftmost prefix matching できません。したがって、Bは誤りです。   Cについては、Covering Indexの使い方に関する選択肢となりますが、idx_abc に入っていない列dがselect の対象列になっているため、表へ参照する必要がありますので、Covering Index としては使えません。なので、Cは誤りです。   Dについては、select の対象列 a,b が全て複合インデックス idx_abc に入っておりますので、Covering Index としては使えます。なので、Dは正しいです。    