🐈

JSONの要素を抜き出しIndexに格納して検索を行う:Generated Columns と Secondary Index その２

2024/08/15に公開

JSON

TiDB

tidbserverless

tech

前回この記事で、Secondary IndexとGenerated Columnsを活用してSQLの実行を効率化させテーブルフルスキャンを避ける方法などをまとめました。

この記事ではこの２つの機能を組み合わせデータ行にJSONが含まれている場合、JSON内の特定要素を抜き出しインデックス化させ検索対象とさせる手順を纏めます。

Generated Columns と Index の違い

前回の記事でまとめましたがGenerated Columnsには仮想型と保存型の２種類があります。保存型はあらかじめ計算がされているという意味においてIndexとは別の方式でSQLを高速化させることが可能です。
ただしGenerated ColumnsはIndexではないためSHOW INDEXESコマンドでは表示されません。
一方Generated Columnsをテーブル作成時にKEYとして指定するとINDEXとして認識させることが出来ます。この場合仮想型であったとしてもインデックス化はされていますのでSQLの高速化が期待できます。

さっそくやってみる

にサンプルなどがありますのでそれをやっていきます。
まずはテーブルを作成します。

use test;
CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) ,
    address_info JSON ,
    city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city')))
);
desc person;
SHOW INDEXES FROM person;

Field	Type	Null	Key	Extra
id	int(11)	NO	PRI	auto_increment
name	varchar(255)	YES
address_info	json	YES
city	varchar(64)	YES		VIRTUAL GENERATED

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression	Clustered
person	0	PRIMARY	1	id	A	0				BTREE			YES		YES

この通りIndexはデフォルト/PRIMARYの一つのみでGenerated Columsのcity列はIndexではありません。
では再度作り直します。

use test;
drop table person;
CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) ,
    address_info JSON ,
    city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))),
    KEY(city)
);
desc person;
SHOW INDEXES FROM person;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression	Clustered
person	0	PRIMARY	1	id	A	0				BTREE			YES		YES
person	1	city	1	city	A	0			YES	BTREE			YES		NO

Indexがもう一つ作成されていることがわかります。違いはKEY(city)です。

ではここに以下のSQLでテスト用データを挿入します。

use test;
INSERT INTO person (name, address_info) VALUES
('Alice Smith', '{"city": "New York", "street": "5th Avenue", "postal_code": "10001"}'),
('Bob Johnson', '{"city": "Los Angeles", "street": "Sunset Boulevard", "postal_code": "90001"}'),
('Carol White', '{"city": "Chicago", "street": "Michigan Avenue", "postal_code": "60601"}'),
('David Brown', '{"city": "Houston", "street": "Main Street", "postal_code": "77001"}'),
('Eva Green', '{"city": "Phoenix", "street": "Camelback Road", "postal_code": "85001"}');

address_infoには当然SQLの中で指定されている{"city": "Phoenix", "street": "Camelback Road", "postal_code": "85001"}が格納されていますが、自動でcity列が生成されcity要素が抜き出されてインデックス化されています。

id	name	address_info	city
1	Alice Smith	{city": "New York" "postal_code": "10001" "street": "5th Avenue"}"	New York
2	Bob Johnson	{city": "Los Angeles" "postal_code": "90001" "street": "Sunset Boulevard"}"	Los Angeles
3	Carol White	{city": "Chicago" "postal_code": "60601" "street": "Michigan Avenue"}"	Chicago
4	David Brown	{city": "Houston" "postal_code": "77001" "street": "Main Street"}"	Houston
5	Eva Green	{city": "Phoenix" "postal_code": "85001" "street": "Camelback Road"}"	Phoenix

cityを対象にした検索を行う場合以下のSQLで実行計画を見てみるとフルスキャンが行われずインデックスが使われていることがわかります。

EXPLAIN SELECT name, id FROM person WHERE city = 'Chicago';

id	estRows	task	access	object
Projection_4	0.01	root	test.person.name, test.person.id
Projection_12	0.01	root	test.person.id, test.person.name, test.person.city
IndexLookUp_11	0.01	root
IndexRangeScan_9(Build)	0.01	cop[tikv]	table:person, index:city(city)	range:[Chicago",Chicago], keep order:false, stats:pseudo"
TableRowIDScan_10(Probe)	0.01	cop[tikv]	table:person	keep order:false, stats:pseudo

Generated Columns と Index の違い

さっそくやってみる

Discussion