このトピックでは、ポイントクエリを識別する方法と、クエリをポイントクエリに最適化する方法について説明します。
ポイントクエリは、オンライントランザクション処理 (OLTP) データベースのアプリケーションにおける一般的なアクセスパターンです。少量のデータのみをスキャンして結果を返します。たとえば、Taobao での注文情報や製品情報の表示は、データベース内のポイントクエリに相当します。PolarDB-X は、ポイントクエリの応答時間 (RT) とリソース使用量に対して最適化されています。この機能は高スループットをサポートし、高同時実行性の読み取りシナリオに適しています。
ポイントクエリとは
ポイントクエリは、その名の通り、少量のデータのみをスキャンします。重要なのは、これが少量のデータを返すことだけではなく、少量のデータをスキャンすることを指すという点です。たとえば、select * from t1 order by c1 limit 1 という文は 1 行しか返しません。しかし、c1 列にインデックスがない場合、データベースは結果を返すために t1 テーブルのすべての行をスキャンしてソートする必要があります。この操作はポイントクエリには該当しません。
シングルノードデータベースでは、最も一般的なポイントクエリはプライマリキー (PK) によってデータを取得します。プライマリキーインデックスを使用して迅速に結果を取得し、平均して log(n) 行のみをスキャンします。ここで n はテーブルの総行数です。他の条件を使用してクエリを実行する場合は、ローカルセカンダリインデックス (LSI) を追加できます。まず LSI がスキャンされてプライマリキーが取得され、次にルックアップが実行されて完全なレコードが取得されます。特別なケースとして、LSI にクエリが必要とするすべての列が含まれている場合、ルックアップのステップはスキップできます。
PolarDB-X は分散データベースです。複数のデータノード (DN) にデータを分散させるために、パーティションテーブルを使用します。データは複数のパーティションに分割され、各パーティションは 1 つの DN にマッピングされます。データを分割するには、1 つ以上の列をパーティション分割のディメンションとして選択できます。これらの列はパーティションキーと呼ばれます。分散データベースでは、クエリのパフォーマンスはスキャンされるデータ量に線形に依存するだけでなく、スキャンされるシャードの数によっても増加します。したがって、ポイントクエリの定義には、少数のパーティションをスキャンすることも含める必要があります。
PolarDB-X は、透明な分散機能を提供します。デフォルトでは、プライマリキーをパーティションキーとして使用します。プライマリキーでクエリを実行すると、PolarDB-X はまずデータが存在するパーティションを特定し、そのパーティションのプライマリキーインデックスを使用して結果を取得します。このプロセスにより、最適なパフォーマンスが実現されます。他の条件を使用するクエリの場合は、グローバルセカンダリインデックス (GSI) を作成できます。GSI を使用したクエリの最適化は、ローカルセカンダリインデックス (LSI) の使用と似ています。まずプライマリキーが取得され、次にインデックス行 ID に基づいてテーブルにアクセスすることで完全なレコードが取得されます。主な違いは、GSI もパーティションテーブルであり、そのデータがプライマリテーブルとは異なる DN に格納される点です。ほとんどの場合、インデックス行 ID に基づいてテーブルにアクセスするにはネットワーク転送が必要となり、この操作はスタンドアロンデータベースよりもコストが高くなります。そのため、PolarDB-X はクラスター化インデックスの作成をサポートしており、インデックス行 ID に基づくテーブルへのアクセスを不要にし、プライマリキークエリと同等のパフォーマンスを実現します。
重要な注意事項
LSI と GSI はどちらも、追加のストレージ領域と書き込みオーバーヘッドを犠牲にしてクエリのパフォーマンスを向上させます。使用する前に、書き込みパフォーマンスへの影響を評価する必要があります。LSI と GSI のデータ分布はプライマリテーブルとは異なるため、GSI を含むすべての書き込みは、デフォルトで分散トランザクションで実行されます。これにより、GSI とプライマリテーブル間の強力な整合性が保証されます。GSI を使用しないシナリオと比較して、書き込みの RT は 2〜3 倍に増加します。さらに、インデックスとプライマリテーブルが並行して書き込まれるため、高同時実行性の書き込み中に分散デッドロックが発生する可能性が高まります。論理テーブルごとに作成する GSI は 3 つ以下にすることを推奨します。
ポイントクエリの検出方法
前述のように、分散データベースにおけるポイントクエリは、少数のシャードと少量のデータのみをスキャンします。実行計画を確認することで、クエリがスキャンするシャードの数を確認できます。実行計画の詳細については、「最適化の原則と実行計画」をご参照ください。以下にポイントクエリの例を示します。
次の文を実行して実行計画を表示します。
explain select c_custkey, c_name, c_address from customer where c_custkey = 42;出力は次のとおりです。
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LogicalView(tables="TEST1_000002_GROUP.customer_IVgG_10", sql="SELECT `c_custkey`, `c_name`, `c_address` FROM `customer` AS `customer` WHERE (`c_custkey` = ?)") | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+次の文を実行して DN 上の実行計画を表示し、クエリが各 DN で正しいインデックスにヒットするかどうかを確認します。DN は MySQL を使用するため、その実行計画は MySQL のものと一致します。詳細については、MySQL の公式ドキュメントをご参照ください。
explain execute select c_custkey, c_name, c_address from customer where c_custkey = 42;出力は次のとおりです。
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | 1 | SIMPLE | customer | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100 | Using pk access | +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
クエリをポイントクエリとして最適化する方法
すべてのクエリがポイントクエリに最適化できるわけではありません。例としては、select * from t1 のようなフルテーブルスキャン、select * from t1 where c1 = 1 limit 100000, 10 のような非効率なページングクエリ、パラメータ数が増加する IN クエリなどがあります。ポイントクエリに最適化できるクエリは、次の 2 つのカテゴリに分類されます。
固定範囲スキャンを持つクエリ：これには、等価条件 (または等価に簡約できる条件)、小範囲の BETWEEN AND 条件、または固定数のパラメータを持つ IN 条件を含むクエリが含まれます。
結果行数が固定された TopN クエリ：たとえば、
select * from t1 where c1 > 42 limit 10やselect * from t1 order by c1 limit 10などです。
これらのタイプのクエリを最適化するには、適切なインデックスを追加してフルテーブルスキャンをインデックススキャンに変換します。以下に例を示します。
`customer` テーブルを作成します。
CREATE TABLE `customer` ( `c_custkey` int(11) NOT NULL, `c_name` varchar(25) NOT NULL, `c_address` varchar(40) NOT NULL, `c_nationkey` int(11) NOT NULL, `c_phone` varchar(15) NOT NULL, `c_acctbal` decimal(15,2) NOT NULL, `c_mktsegment` varchar(10) NOT NULL, `c_comment` varchar(117) NOT NULL, PRIMARY KEY (`c_custkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`c_custkey`) tbpartition by hash(`c_custkey`) tbpartitions 4;select * from customer where c_phone = "11";の実行計画を確認します。explain select * from customer where c_phone = "11";出力は次のとおりです。
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 論理実行計画 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(concurrent=true) | | LogicalView(tables="[000000-000003].customer_[00-15]", shardCount=16, sql="SELECT `c_custkey`, `c_name`, `c_address`, `c_nationkey`, `c_phone`, `c_acctbal`, `c_mktsegment`, `c_comment` FROM `customer` AS `customer` WHERE (`c_phone` = ?)") | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+`customer` テーブルにはプライマリキーインデックスしかありません。`c_phone` に等価条件が使用されていても、クエリはすべてのシャードをスキャンします。GSI を追加してクエリを最適化できます。
create global index g_i_phone on customer(c_phone) dbpartition by hash(c_phone);GSI を追加した後、実行計画を確認します。
explain select * from customer where c_phone = "11";出力は次のとおりです。
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project(c_custkey="c_custkey", c_name="c_name", c_address="c_address", c_nationkey="c_nationkey", c_phone="c_phone", c_acctbal="c_acctbal", c_mktsegment="c_mktsegment", c_comment="c_comment") | | BKAJoin(condition="c_custkey = c_custkey", type="inner") | | IndexScan(tables="TEST1_000000_GROUP.g_i_phone_2CSp", sql="SELECT `c_custkey`, `c_phone` FROM `g_i_phone` AS `g_i_phone` WHERE (`c_phone` = ?)") | | Gather(concurrent=true) | | LogicalView(tables="[000000-000003].customer_[00-15]", shardCount=16, sql="SELECT `c_custkey`, `c_name`, `c_address`, `c_nationkey`, `c_acctbal`, `c_mktsegment`, `c_comment` FROM `customer` AS `customer` WHERE ((`c_phone` = ?) AND (`c_custkey` IN (...)))") | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+GSI が追加されると、クエリはインデックステーブルに対するポイントクエリとなり、その後にルックアップが続きます。ルックアップは 1 つのシャードにのみアクセスします。(実行計画ではプライマリテーブルのフルテーブルスキャンが表示されますが、これはスキャンする正確なシャードがインデックステーブルの結果に依存するためです。これは EXPLAIN フェーズでは判断できません。)
GSI をクラスター化インデックスに置き換えます。
drop index g_i_phone on customer;create clustered index g_i_phone on customer(c_phone) dbpartition by hash(c_phone);クラスター化インデックスを作成した後、実行計画を確認します。
explain select * from customer where c_phone = "11";出力は次のとおりです。
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexScan(tables="TEST1_000000_GROUP.g_i_phone_fHmZ", sql="SELECT `c_custkey`, `c_name`, `c_address`, `c_nationkey`, `c_phone`, `c_acctbal`, `c_mktsegment`, `c_comment` FROM `g_i_phone` AS `g_i_phone` WHERE (`c_phone` = ?)") | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+GSI がクラスター化インデックスに置き換えられると、インデックステーブルにはプライマリテーブルのすべての列が含まれるようになります。ルックアップは不要になります。実行計画は、インデックステーブルに対するポイントクエリを示します。
上記の例は、インデックスを使用してポイントクエリのパフォーマンスを最適化する一般的なプロセスを説明しています。重要なステップは、クエリパターンに基づいてインデックスに適した列を選択することです。