Azure Database for MySQL ‐ フレキシブル サーバーのトラブルシューティングのベスト プラクティス
以下のセクションのようにすると、Azure Database for MySQL フレキシブル サーバー データベースのスムーズな実行を維持できます。この情報は、スキーマを最適に設計し、アプリケーションに最適なパフォーマンスを提供するための指針として使用できます。
インデックスの数をチェックする
ビジー状態のデータベース環境では、I/O 使用率が高くなる場合があり、これはデータ アクセス パターンが不適切であることを示している可能性があります。 使われないインデックスは、ディスク領域とキャッシュを消費し、書き込み操作 (INSERT、DELETE、UPDATE) を遅くするため、パフォーマンスに悪影響を及ぼす可能性があります。 使われないインデックスは、不要なストレージ領域を余分に消費し、バックアップ サイズを大きくします。
インデックスを削除する前に、十分な情報を収集して、それが使われなくなっていることを確認してください。 この検証により、四半期または年に 1 回だけ実行されるクエリにとって重要なインデックスを誤って削除するのを回避できます。 また、インデックスが一意性または順序付けの適用に使われているかどうかを検討してください。
注意
忘れずにインデックスを定期的に確認し、テーブルのデータに対する変更に基づいて必要な更新を行ってください。
SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;
(または)
use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));
サーバー上で最もよく使われているインデックスの一覧を表示する
次のクエリからの出力では、データベース サーバー上のすべてのテーブルとスキーマで最もよく使われているインデックスに関する情報が提供されます。 この情報は、インデックスごとの読み取りに対する書き込みの比率と、読み取りおよび個々の書き込み操作の待ち時間を識別するのに役立ち、これは基になるテーブルと依存クエリに対してさらにチューニングが必要であることを示す可能性があります。
SELECT
object_schema AS table_schema,
object_name AS table_name,
index_name, count_star AS all_accesses,
count_read,
count_write,
Concat(Truncate(count_read / count_star * 100, 0), ':',
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio,
count_fetch AS rows_selected ,
count_insert AS rows_inserted,
count_update AS rows_updated,
count_delete AS rows_deleted,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency ,
Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency,
Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency,
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency,
Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star > 0
ORDER BY sum_timer_wait DESC
主キーの設計を確認する
Azure Database for MySQL フレキシブル サーバーでは、すべての非一時的テーブルに InnoDB ストレージ エンジンが使われます。 InnoDB では、データは B ツリー構造を使ってクラスター化インデックス内に格納されます。 テーブルは主キーの値に基づいて物理的に編成されます。つまり、行は主キーの順序で格納されます。
InnoDB テーブルの各セカンダリ キー エントリには、データが格納されている主キー値へのポインターが含まれています。 言い換えると、セカンダリ インデックスのエントリには、エントリが指している主キーの値のコピーが含まれます。 したがって、主キーの選択は、テーブルでのストレージ オーバーヘッドの量に直接影響します。
キーが実際のデータ (ユーザー名、メール アドレス、SSN など) から派生する場合は、"ナチュラル キー" と呼ばれます。 キーが人工的であり、データから派生したものではない場合は (自動インクリメントされた整数など)、"合成キー" または "代理キー" と呼ばれます。
通常は、ナチュラルな主キーを使わないことをお勧めします。 多くの場合、このようなキーは非常に幅が広く、1 つまたは複数の列の長い値を含みます。 これにより、主キーの値が各セカンダリ キー エントリにコピーされるときに、大きなストレージ オーバーヘッドが発生する可能性があります。 さらに、通常、ナチュラル キーは事前に決定された順序に従っていないため、パフォーマンスが大幅に低下し、行が挿入または更新されたときにページの断片化を引き起こします。 これらの問題を回避するには、ナチュラル キーの代わりに単調に増加する代理キーを使います。 自動インクリメントの (big)integer 列は、単調に増加する代理キーの良い例です。 列の特定の組み合わせが必要な場合は、一意にして、それらの列を一意のセカンダリ キーとして宣言します。
アプリケーションの構築の初期段階では、テーブルが 20 億行に近づき始めるときのことを前もって想像できないかもしれません。 その結果、ID (主キー) 列のデータ型に符号付き 4 バイト整数を使うことを選ぶかもしれません。 すべてのテーブルの主キーを調べて、大量になる、または増加する可能性に対応するには、8 バイト整数 (BIGINT) の列を使うように切り替えてください。
Note
データ型とその最大値について詳しくは、MySQL リファレンス マニュアルの「データ型」をご覧ください。
カバリング インデックスを使用する
前のセクションでは、MySQL のインデックスが B ツリーとして編成されることを説明しました。クラスター化インデックスでは、リーフ ノードには基になるテーブルのデータ ページを含まれます。 セカンダリ インデックスは、クラスター化インデックスと同じ B ツリー構造を持ち、クラスター化インデックスまたはヒープを使用するテーブルまたはビューでそれらを定義できます。 セカンダリ インデックスの各インデックス行には、非クラスター化キー値と行ロケーターが含まれています。 このロケーターは、キー値があるクラスター化インデックスまたはヒープのデータ行を指します。 その結果、セカンダリ インデックスを含む検索で主キー値を取得するには、ルート ノードからブランチ ノードを経由して正しいリーフ ノードに移動する必要があります。 その後、システムは主キー インデックスでランダムな IO 読み取りを実行して (もう一度ルート ノードからブランチ ノードを通して正しいリーフ ノードに移動します)、データ行を取得します。
データ行を取得するための主キー インデックスでのこのような余分なランダム IO 読み取りを避けるには、クエリで必要なすべてのフィールドを含むカバリング インデックスを使います。 一般に、この方法を使うと、I/O にバインドされるワークロードやキャッシュされるワークロードにメリットがあります。 そのため、ベスト プラクティスとして、カバリング インデックスはメモリに収まり、すべての行をスキャンするより小さく、読み取り効率が高いため、カバー インデックスを使用します。
たとえば、2000 年 1 月 1 日以降に入社したすべての従業員を検索するために使用するテーブルについて考えます。
mysql> show create table employee\G
****************** 1. row ******************
Table: employee
Create Table: CREATE TABLE `employee` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(10) DEFAULT NULL,
`lname` varchar(10) DEFAULT NULL,
`joindate` datetime DEFAULT NULL,
`department` varchar(10) DEFAULT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`
`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';
このクエリで EXPLAIN プランを実行すると、現在はインデックスが使われておらず、従業員レコードのフィルター処理に where 句のみが使われていることがわかります。
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)
一方、WHERE 句の列をカバーするインデックスと射影された列を追加した場合は、インデックスが使われて、列をより迅速かつ効率的に見つけることができます。
mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);
ここで、同じクエリで EXPLAIN プランを実行すると、"Extra" フィールドに "Using Index" という値が表示されます。これは、InnoDB が前に作成したインデックスを使ってクエリを実行することを意味し、これがカバリング インデックスであることを確認できます。
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: range
possible_keys: cvg_idx_ex
key: cvg_idx_ex
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
Note
クエリを正しく処理するには、カバリング インデックスの列を正しい順序で選ぶことが重要です。 一般的なルールは、最初にフィルター処理 (WHERE 句)、次に並べ替えとグループ化 (ORDER BY と GROUP BY)、最後にデータ プロジェクション (SELECT) を行うように列を選びます。
前の例から、クエリにカバリング インデックスを使うと、レコード取得パスがいっそう効率的になり、同時実行が非常に多いデータベース環境でのパフォーマンスが最適化されます。