TiDBでのインデックス設計

このスライドでは、分散型NewSQLデータベース「TiDB」ならではの索引設計について解説します。TiDBクラスタで索引はどのようなKVペアに変換して、ストレージに格納しているか、いくつかの索引の使用方法、索引の使用に当たる制限事項やメンテナンス・テクニックを紹介します。TiDB独自の索引実現方法や、TiDBをより生かせる索引の使用方法にご興味がある方は是非ご覧ください。
トピック
・索引のKVマッピング・ルール
・TiDBでサポートしている索引の使用方法
・索引に関する制限事項
・メンテナンス・テクニック

アーカイブ動画
https://youtu.be/dZxvg1pV0xw

QUIZ(資料P34)の解答：　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は正しいです。

Transcript

1. Copyright ©2022 PingCAP. All Rights Reserved. TiDB でのインデックス設計
 Xinke Qiao / 喬 心軻(キョウ　シンカ) 
 Technical support engineer 
 PingCAP Japan

2. 本日の位置づけ
 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 

3. Copyright ©2022 PingCAP. All Rights Reserved. TiDB でのインデックス設計

4. Copyright ©2022 PingCAP. All Rights Reserved. アジェンダ
 • インデックスの KV マッピング・ルール
 
 • TiDB でサポートしているインデックス
 
 • インデックスに関する注意事項
 
 • Maintenance Tips
 
 • まとめ&QA

5. Copyright ©2022 PingCAP. All Rights Reserved. おさらい
 表の KV マッピング・ルール
 00

6. 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');

7. 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]

8. 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 はシステムにより自動的に生成された一意 の整数となる

9. 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]

10. 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マッピングルール 

11. Copyright ©2022 PingCAP. All Rights Reserved. インデックスの KV マッピング・ルール
 01

12. 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));

13. 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

14. 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);

15. 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

16. 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 : システムにより自動的に計算された値となります。

17. Copyright ©2022 PingCAP. All Rights Reserved. TiDB でサポートしているインデックス
 02

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

19. 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)
 );

20. 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 をブロックすることも可能となります。 

21. 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の列値)

22. 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)

23. 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)

24. 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 |
 +--------------------------------------------------------------+

25. 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)

26. 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;

27. Copyright ©2022 PingCAP. All Rights Reserved. TiDB でのインデックスに関する注意事項
 03
28. 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 ではサポートしていない 

29. Copyright ©2022 PingCAP. All Rights Reserved. Maintenance Tips
 04
30. 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 

31. Copyright ©2022 PingCAP. All Rights Reserved. サマリー
 1. インデックスの KV マッピング・ルール 
 
 1. TiDB でサポートしているインデックス 
 
 1. インデックスに関する注意事項
 
 1. Maintenance Tips

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

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

34. 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つを選ぶ)

35. 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は正しいです。 
 

36. Q&A

37. Thank You!
 https://www.pingcap.com/
 pingcapjp@pingcap.com