Secondary Index と Generated Columns
今日はSQLの実行を高速化させる２つのオプション、Secondary IndexとGenerated Columnsについてみていきたいと思います。
Secondary Index
Secondaryというからには勿論Primaryもあります。Primaryはテーブル作成時に自動で生成されクエリオプティマイザなどが使用します。
この辺りにその使用がまとまっています。これとは別に特定SQLの実行を高速化させるために明示的に作成されるのがSecondary Indexです。Secondary Indexはテーブル作成時に明示的に指定することや、後から追加することが可能です。
Secondary Indexはグローバルなインデックスとして作成され独立したストレージを持ちます。このためあまり不必要なSecondary Indexを作成しすぎるとストレージ容量が急激に増えていきます。またストレージが複数存在するということは、書き込み遅延も懸念されますので、Secondary Index作成時は、１個ずつ作成しながら期待されたパフォーマンス改善がみられているかと判断していくことが重要です。
そして以下も記事でも触れたクエリオプティマイザが判断に用いる情報が増えていくため、不必要なSecondary Indexの作成はSQL実行開始までの遅延も生じさせ得る、ということを考慮しておく必要があります。
とはいえSecondary IndexはSQLの実行を高速化させる可能性がありますので、そこまで気にする必要はないかもしれませんが、作ったら作った分だけはやくなるというものではない、ということは留意しておく必要があります。
また単一リージョンで起動されるTiDB Serverlessではそこまで気にする必要はないかもしれませんが、複数リージョンに分散してストレージを構築可能なTiDB Clusterでは、Secondary Indexはグローバルオブジェクトとなるため、テーブルのインデックスとデータは必ずしも同じデータシャーディング上に存在しているわけではない、という点は重要です。https://docs.pingcap.com/ja/tidb/stable/tidb-best-practices#secondary-index
このような記載があります。
インデックスを介してクエリを実行する場合、最初にインデックスをスキャンして対応する行 ID を取得し、次に行 ID を使用してデータを取得する必要があります。したがって、この方法では 2 つのネットワーク要求が伴い、一定のパフォーマンス オーバーヘッドが発生します。
クエリに多数の行が含まれる場合、インデックスのスキャンは同時に進行します。最初のバッチの結果が返されると、テーブルのデータの取得を続行できます。したがって、これは並列 + パイプライン モデルです。2 つのアクセスによってオーバーヘッドが発生しますが、レイテンシーは高くありません。
つまりSQLを発行対象のデータが複雑になればなるほどSecondary Indexを用いるオーバーヘッドよりも並列性によるメリットが上回ることとなり、複雑なデータにはSecondary Indexは有益と言えます。その他Secondary Index利用時の考慮点は上記ドキュメントに纏まっています。
さっそくやってみる
ではテスト用Serverless Clusterを新規に作成し試してみます。クラスターを起動したらテスト用テーブルを以下に作成します。
USE test;
CREATE TABLE indextest (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
age INT,
PRIMARY KEY (id)
);
その後以下のSQLを実行しダミーデータを書き込みます。
-- 50件のダミーデータを挿入するSQLスクリプト
INSERT INTO indextest (name, age) VALUES
('John Smith', 25),
('Emily Johnson', 32),
('Michael Williams', 28),
('Sarah Brown', 45),
('David Jones', 38),
('Emma Davis', 27),
('James Miller', 30),
('Olivia Wilson', 29),
('Daniel Moore', 33),
('Sophia Taylor', 31),
('Matthew Anderson', 26),
('Isabella Thomas', 24),
('Joshua Martinez', 34),
('Mia Garcia', 22),
('Andrew Martinez', 37),
('Ava Rodriguez', 26),
('Ethan Lee', 29),
('Charlotte Walker', 25),
('Benjamin Hall', 40),
('Amelia Young', 31),
('Jacob Allen', 27),
('Harper Scott', 28),
('William King', 35),
('Ella Green', 23),
('Alexander Adams', 30),
('Grace Baker', 26),
('Liam Nelson', 32),
('Zoe Carter', 29),
('Ryan Hill', 24),
('Lily Evans', 33),
('Noah Walker', 25),
('Avery Mitchell', 38),
('Elijah Perez', 28),
('Chloe Roberts', 22),
('Lucas Turner', 27),
('Sofia Phillips', 30),
('Oliver Campbell', 26),
('Mia Robinson', 34),
('Henry Cooper', 29),
('Luna Bailey', 31),
('Jack Murphy', 24),
('Eleanor Rogers', 37),
('Sebastian Reed', 25),
('Aria Bell', 32),
('Aiden Jenkins', 28),
('Natalie Wood', 30),
('Grayson Bennett', 27),
('Hannah Gray', 29),
('Jack Green', 33);
データ書き込みが完了したら以下のSQLを実行してみます。
SELECT name
FROM indextest
WHERE name LIKE '%John%';
以下のように２件データが出力されていれば準備は完了です。
次に実行計画を見てみます。
USE test;
desc SELECT name
FROM indextest
WHERE name LIKE '%John%';
| id | estRows task | access object | operator info |
|---|---|---|---|
| TableReader_7 | 39.2 | root | data:Selection_6 |
| Selection_6 | 39.2 | cop[tikv] | like(test.indextest.name, %John%",92)" |
| TableFullScan_5 | 49 | cop[tikv] | table:indextest keep order:false, stats:pseudo |
テーブルのフルスキャンが走っていることがわかります。これはデータが増えれば増えるほど処理遅延を招きますし、このSQLを実行中に他の同時並行を行いたいSQLにも影響を与えます。
ではSecondary Indexを作成します。構文は以下の通りです。
CREATE INDEX {index_name} ON {table_name} ({column_names});
今回の例でいうと以下になります。
CREATE INDEX testindex ON indextest (name);
尚テーブル作成時に同時にSecondary Indexを作成する場合はこちらの手順を参考にしてください。
もう一度実行計画を見てみます。
USE test;
desc SELECT name
FROM indextest
WHERE name LIKE '%John%';
| id | estRows | task | access object | operator info |
|---|---|---|---|---|
| IndexReader_10 | 39.2 | root | index:Selection_9 | |
| Selection_9 | 39.2 | cop[tikv] | like(test.indextest.name, %John%", 92)" | |
| IndexFullScan_8 | 49 | cop[tikv] | table:indextest, index:testindex(name) keep order:false, stats:pseudo |
先ほどと異なりデフォルトでデータのフェッチをストレージから行うTableReaderからIndexReaderへと変更となり、フルスキャンも別ストレージに対して実行されています。こうすることによって他のSQLに影響を与えないようクラスター全体の並列性を高めています。このサンプルでは純粋な文字列に対するLIKE検索ですので、フルスキャンであることは変りませんが以下の例では、フルスキャンがレンジスキャンに代わる例が記載されています。
SHOW INDEXES FROM indextest;
こちらを実行することで存在しているインデックスの一覧が確認できます。
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| indextest | 0 | PRIMARY | 1 | id | A | 0 | BTREE | YES | YES | ||||||
| indextest | 1 | testindex | 1 | name | A | 0 | YES | BTREE | YES | NO |
Primaryとは別にもう一つIndexが存在していることが確認できます。
Generated Columns
続いてGenerated Columnsを試していきます。多くのデータベースエンジンがサポートしている機能であり生成列や計算列とも言われ、SQLの実行を高速化させる機能です。一般的な列とは異なり、生成された列の値は列定義内の式によって計算されます。
TiDBではストレージを占有せず、読み取られるときに計算される仮想列と書き込まれる (挿入または更新される) ときに計算され、ストレージを占有する保存列の２種類をサポートしています。前者はコストパフォーマンスが良いですが、必要な計算はSQL発行時に行われます。また後者はSecondary Indexと同様にストレージは占有しますが、必要な計算が前もって行われているためSQLの高速化に期待できます。
に使用などがまとまっています。この中に以下の記載があります。
生成された列の主な用途の 1 つは、JSON データ型からデータを抽出し、そのデータにインデックスを付けることです。
JSONを格納しているカラムからJSONを取り出しGenerated Columnsに格納してSQLからJSONではなく、JSONから取り出した要素を操作できるようにします。この辺りの使い方は次回の記事に回してまずはシンプルなGenerated Columnsの使い方を見ていきたいと思います。
さっそくやってみる
先ほどのテーブルを利用します。
USE test;
alter table indextest add column birth bigint as (2024-age) virtual;
(年齢から生まれた年を計算するのはそんなに簡単ではないですが、テスト用なのでそこは良しとします。）
vritualは都度計算を行う仮想列という意味です。あらかじめ計算をしておく保存列の場合storedを指定しますが、alter tableでは指定が行えないようでCreate tableの際に指示すべき様です。
drop table t;
create table t(a int,b bigint AS (a+1) stored);
で生成されました。
Secondary Index と Generated Columns の使い分け
クエリ内の式がインデックスを持つ生成カラムと厳密に等価な場合、TiDBは式を対応する生成カラムに置き換えます。
という記載がありました。つまり対象データ範囲及び型が同じものがSecondary IndexとGenerated Columns双方で存在している場合、クエリオプティマイザはGenerated Columnsを優先させるようです。この場合Generated Columnsが仮想型であった場合、リアルタイム計算を行う際にフルスキャンが行われますので期待されたパフォーマンスが出ない可能性がありますので注意が必要そうです。上記では対象データ範囲は同じだが型が異なるSecondary IndexとGenerated Columnsを作成した際に、Secondary Indexが優先的に用いられる例が記載されています。
また同時に以下のルールも知っておく必要があります。
現在、生成列インデックスの置換ルールは、生成列が仮想生成列である場合にのみ有効です。保存された生成列では有効ではありませんが、生成列自体を直接使用することでインデックスを使用することができます。
仮想列の確認方法
生成された仮想列は以下のコマンドで確認が可能です。
desc <tablename>
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| a | int(11) | YES | |||
| b | bigint(20) | YES | STORED GENERATED | ||
| c | varchar(255) | YES | VIRTUAL GENERATED |
次回はこの機能を使いながらJSONを扱うサンプルを作成していきたいと思います。
Discussion