SQL文の記述と最適化 - AnalyticDB for MySQL - Alibaba Cloud ドキュメントセンター

AnalyticDB for MySQLでSQL文を記述して最適化するときは、データの分散特性を考慮する必要があります。このトピックでは、SQL文を記述して最適化する方法について説明します。

SQL開発ルール

AnalyticDB for MySQLでSQL文を記述する場合、次のルールが適用されます。

単純なSQL文の記述
ほとんどの場合、SQLクエリの複雑さが増すと、データベースのパフォーマンスが低下します。たとえば、単一テーブルクエリ (冗長データ) を使用するデータベースは、テーブル結合クエリを使用するデータベースよりもパフォーマンスが優れています。
I/O操作の数を減らす
列スキャンの数を減らすことで、より少ないデータを返すことができ、I/O操作とメモリオーバーヘッドの数を減らすことができます。
分散コンピューティングの使用Use distributed computing
ビッグデータコンピューティングシナリオでは、ローカルコンピューティングは分散コンピューティングリソースを完全に使用して、データがノード間で送信されるのを防ぎます。
パーティションプルーニングの使用
高いクエリ /秒 (QPS) パフォーマンスとミリ秒の応答時間が必要なビジネスシステムでは、テーブルとSQLステートメントにパーティションプルーニングを使用する必要があります。

SQL最適化ルール

冗長列の削除
AnalyticDB for MySQLは、ハイブリッド行と列ストレージを提供します。返される列の数は、SQLのパフォーマンスに影響します。 SQL文を記述するときは、ビジネスに必要な列のみを指定することをお勧めします。すべての列でステートメントを実行するためにアスタリスク (*) を使用しないことをお勧めします。
例
- 構文は推奨されません
```
select * from tab1 where c1>100およびc1<1000;
```
- 推奨される構文
```
選択col1、col2 from table_nameここで、c1>100およびc1<1000;
```
インデックスとスキャンの使用
SQL文に複数のクエリ条件が含まれている場合、高度なフィルター条件にインデックスを使用し、残りの条件にはスキャンを使用できます。
AnalyticDB for MySQLは、ハイブリッド行と列ストレージを提供します。単一の列を使用して、データを効率的にフィルタリングできます。次に、内部レコードポインターを使用して他の列値をスキャンできます。これにより、他の列に対するインデックスベースのクエリのオーバーヘッドを減らすことができます。
AnalyticDB for MySQLを使用すると、クラスターでクエリを実行するときに、特定の列のインデックスの使用を無効にできます。詳細については、「プッシュダウンなしの条件のフィルタリング」をご参照ください。
例
説明
次の例は、3.14より前のエンジンバージョンに適用できます。エンジンバージョン3.14以降の場合は、filter_not_pushdown_columnsヒントを使用します。
- 時間条件に内部スキャンを使用
  次のSQL文では、c1フィールドとtimeフィールドを使用してデータをフィルタリングします。 c1=3が使用される場合、10,000エントリなどの少数のエントリが返される。ただし、time>'2010-01-01 00:00:00 'を使用すると、大量のエントリが返されます。
```
選択c1、c2 from tab1 (c1=3およびtime >='2010-01-01 00:00:00 ');
```
  クエリの効率を向上させ、より有効なエントリを返すために、c1のみを使用してデータをインデックスし、時間条件の内部スキャン方法を使用できます。例：
```
/* + no_index_columns=[tab1.time] * /
tab1からc1、c2を選択
ここで、c1=3およびtime>='2010-01-01 00:00:00 '; 
```
  ヒントは、time>='2010-01-01 00:00:00 '条件のスキャンを強制します。
  コンピューティングエンジンは、最初にc1列のインデックスを取得して、c1=3の条件を満たす行セットを取得します。次に、エンジンは各行の時間列からデータを読み取ります。行の時間列の時間がtime >='2010-01-01 00:00:00 'の条件を満たす場合、エンジンはこの行を結果に追加します。
- <> 条件に内部スキャンを使用
  c2<>100などの <> 条件を含むクエリは、インデックススキャンを使用する場合、無効なエントリを除外できません。例：
```
選択c1,c2 from tab1ここでc1=3そしてc2<>100;
```
  no_index_columnsヒントを追加して、<> 条件の内部スキャンを実行します。例：
```
/* + no_index_columns=[tab1.c2] * /
c1、c2をtab1から選択します。c1=3、c2<>100; 
```
- <> 条件に内部スキャンを使用
  like条件は、like '% abc' やlike' % abc % 'など、接尾辞またはインフィックスを持つクエリで使用されます。
  no_index_columnsヒントを追加して、同様の条件の内部スキャンを実行し、有効なエントリの取得を高速化します。例：
```
/* + no_index_columns=[tab1.c3] * /
c1、c2をtab1から選択します。c1=3、c3は '% abc %'; 
```
インデックスの失敗
クエリに使用されるインデックスが失敗した場合、SQL文はテーブル内のすべてのエントリをスキャンします。テーブルに多数のエントリが含まれている場合、クエリのパフォーマンスが低下します。
たとえば、次のシナリオでインデックスの失敗が発生する可能性があります。
- 関数変換 (列)
- 型変換
- のような条件のような '% abc %'
例
- 次のSQL文では、関数の変換によってインデックスが失敗します。時間列はTIMESTAMP型であり、2017-12-10 10:00:23の値を含む。
```
select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='2017-12-10';
```
- 推奨される構文
```
select c1,c2 from tab1 where time>='2017-12-10 00:00:00:00 'and time<='2017-12-10 23:59:59';
```
不要なフィルタ条件を削除するis not null
例
- 構文は推奨されません
```
選択c1、c2 from tab1、c1>100、c1<1000、c1はnullではありません。
```
- 上記のSQL文では、c1 is not nullは冗長フィルター条件です。次のSQL文は最適化されたものです。
```
選択c1、c2 from tab1ここでc1>100およびc1<1000;
```
マルチテーブル結合
異なるマルチテーブル結合シナリオでは、異なるSQL最適化ルールが使用されます。
- 別のファクトテーブルと結合されているファクトテーブルに、パーティション列のJOIN条件を含めます。それ以外の場合は、WHERE条件を使用して不要なデータを除外します。
- レプリケートされたテーブルがファクトテーブルと結合されるとき、制限は課されない。
マルチテーブル結合クエリでは、WHERE条件の各テーブルのフィルター条件を指定する必要があります。従来のデータベースでは、インデックスフィールドを使用してテーブルを結合し、データを検索します。例：
```
カウントを選択 (*)
t1 C joinから
t2 O on C.t1_id= O.t1_id
ここでO.t2_time between'2018-07-20 10:00:11'
および '2018-09-30 10:00:11'
およびO.t2_amount=100; 
```
t2テーブルとt1テーブルに同じ時間と型のフィルターがある場合は、SQL文を次のように変更することを推奨します。
```
カウントを選択 (*)
t1.id=t2.idのt1 join t2から
「2017-12-10 00:00:00」と「2017-12-10 23:59:59」の間のt1.time
とt1.type= 100
「2017-12-10 00:00:00」と「2017-12-10 23:59:59」の間のt2.time
とt2.type=100 
```