yousefmeska

yousefmeska

ホームアーカイブ

クエリ最適化 - 高パフォーマンスのためのインデックス調整

#performance#db#mysql#indexes51
AI 翻訳
この記事はAIを通じて英語から日本語に翻訳されました。原文を表示
AI が生成した要約
Indexes, also known as keys in MySQL, are used by storage engines to quickly find rows. They are not standardized and work differently in each engine. In MySQL, indexes are used to search for specific values in a table. There are different types of indexes, such as B-Tree, Hash, and Bitmap. B-Tree indexes are commonly used in MySQL and store values in order. They are useful for searching for ranges of data. B-Tree indexes can be used for lookups by full key value, key range, and key prefix. They can also support index-only queries.

はじめに#

インデックス（MySQL では「キー」とも呼ばれます）は、ストレージエンジンが行を迅速に見つけるために使用するデータ構造です。また、この章では探求するいくつかの他の有益な特性も持っています。

したがって、インデックスは標準化されていません：インデックス作成は各エンジンでわずかに異なり、すべてのエンジンがすべてのタイプのインデックスをサポートしているわけではありません。複数のエンジンが同じインデックスタイプをサポートしている場合でも、内部での実装が異なる場合があります。

MySQL では、ストレージエンジンはインデックスを、特定の用語を含むページを検索するために使用した本の索引のように使用します。インデックスのデータ構造内で値を検索します。一致するものが見つかると、それを含む行を見つけることができます。次のクエリを実行したとしましょう。

SELECT first_name FROM sakila.actor WHERE actor_id = 5;

[!info] インデックスは、テーブル内の 1 つ以上の列からの値を含みます。複数の列をインデックスする場合、列の順序は非常に重要です。なぜなら、MySQL はインデックスの最も左側のプレフィックスでのみ効率的に検索できるからです。

2 つの列にインデックスを作成することは、2 つの別々の単一列インデックスを作成することとは異なります。

インデックスの種類#

インデックスには多くの種類があり、それぞれ異なる目的に対して良好に機能するように設計されています。以下の内容をカバーします。

  • B-Tree インデックス

  • ハッシュインデックス

  • ビットマップインデックス

[!info] インデックスは、ストレージエンジン層で実装されており、サーバー層ではありません。

MySQL は、CREATE TABLEや他のステートメントでデフォルトで B-Tree インデックスを使用します。ただし、ストレージエンジンは内部で異なるストレージ構造を使用する場合があります。

ストレージエンジンのパフォーマンスは、B-Tree インデックスの利用方法によって影響を受けることがあります。たとえば、MyISAM はインデックスサイズを削減するプレフィックス圧縮方法を採用していますが、InnoDB はインデックス値を非圧縮のまま保持します。さらに、MyISAM インデックスは、物理ストレージ位置に基づいてインデックスされた行を特定しますが、InnoDB は主キー値（クラスタキー）を使用してそれらを参照します。各アプローチには独自の利点と欠点があります。

B-Tree の一般的なアイデアは、すべての値が順序で保存され、各リーフページがルートから同じ距離にあることです。

この図は、InnoDB ストレージエンジンによって使用される B-Tree インデックスの抽象を示しています。

![](https://i.imgur.com/4ULPTTm.png align="left")

リーフページは、他のページへのポインタではなく、インデックスデータへのポインタを含む点で異なります。データへの「ポインタ」の種類は、異なるストレージエンジン間で異なります。

B-Tree はインデックス列を順序で保存するため、データの範囲を検索するのに役立ちます。

例を見てみましょう。

CREATE TABLE People ( 
	last_name varchar(50) not null,
	first_name varchar(50) not null, 
	dob date not null, 
	gender enum('m', 'f') not null, 
	key(last_name, first_name, dob) 
);

ここでのインデックスは、テーブル内の各行の last_name、first_name、および dob 列からの値を含みます。インデックスが保存するデータの配置方法は次のとおりです。

![](https://i.imgur.com/JblrQr0.png align="left")

インデックスは、CREATE TABLE ステートメントで指定されたインデックスの列の順序に従って値をソートします。

B-Tree を使用するタイミングと使用しないタイミングを知ることがはるかに重要です！

B-Tree インデックスを使用できるクエリのいくつかのタイプがあります。これらは、ルックアップに使用できるものです。

  • 完全キー値

  • キー範囲

  • キープレフィックス

[!danger] ルックアップがインデックスの最も左側のプレフィックスを使用する場合にのみ役立ちます。

前の例を考慮して、インデックスが使用される場合と使用されない場合を見てみましょう！

完全値キーの一致 インデックス内のすべての列に対して値を指定します。

SELECT first_name FROM People WHERE last_name = 'Allen' AND first_name = 'Cuba' AND dob = '1960-01-01'

最も左側のプレフィックスの一致 last_name が Allen であるすべての人を見つけます。これはインデックスの最初の列のみを使用します。

SELECT first_name FROM People WHERE last_name = 'Allen'

列プレフィックスの一致 列の値の最初の部分で一致します。たとえば、姓が J で始まるすべての人を見つけます。

SELECT first_name FROM People WHERE last_name LIKE 'J%'

値の範囲の一致 last_name が Allen と Meska の間にある人を見つけることができます。

SELECT first_name FROM People WHERE last_name BETWEEN 'Allen' AND 'Meska';

一部を正確に一致させ、他の部分で範囲を一致させる このインデックスは、姓が Allen で、名が K（Kim、Karl など）で始まるすべての人を見つけるのに役立ちます。これは last_name の正確な一致と first_name の範囲クエリです。

SELECT first_name FROM People WHERE last_name = 'Allen' AND first_name LIKE 'K%'

インデックスのみのクエリの一致 B-Tree インデックスは通常、インデックスのみのクエリをサポートできます。これは、インデックスのみをアクセスし、行ストレージをアクセスしないクエリです。この最適化については、「カバリングインデックス」で後で説明します。

[!info] B-Tree を使用するもう 1 つの重要な利点は、ルックアップと同様に、ソートされた順序で値を見つけることです。B-Tree のノードはソートされているためです。

したがって、B-Tree インデックスはORDER BYおよびGROUP BY句に役立ちます。

B-Tree インデックスの欠点と制限#

インデックスされた列の最も左側から始まらないルックアップには役立ちません。

SELECT first_name FROM People WHERE first_name = 'Yousef';

上記のクエリはインデックスを使用しません。また、特定の文字で終了する姓を持つ人を見つけることもできません。たとえば、以下のクエリはインデックスを使用しません。

SELECT first_name FROM People WHERE last_name LIKE '%J';

ストレージエンジンは、最初の範囲条件の右側にある列でのアクセスを最適化できません。たとえば、クエリが次のような場合：

WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'

インデックスアクセスは、インデックス内の最初の 2 列のみを使用します。なぜなら、LIKE は範囲条件だからです（サーバーは他の目的のために残りの列を使用できます）。限られた数の値を持つ列の場合、範囲条件の代わりに等価条件を指定することで、これを回避できることがよくあります。この章の後半で、インデックスのケーススタディで詳細な例を示します。

これらの制限のいくつかは B-Tree インデックスに固有のものではなく、MySQL のクエリオプティマイザとストレージエンジンがインデックスを使用する方法の結果です。将来的にいくつかは解消されるかもしれません。

今理解したように、列の順序の重要性は過小評価できません。これらの制限はすべてそれに絡んでいます。最適なパフォーマンスを達成するには、クエリを満たすために異なる順序で同じ列を持つインデックスを生成する必要があるかもしれません。

「インラインクエリ」とは、必要な情報がインデックス自体に存在するシナリオを指します。たとえば、クエリ「EXPLAIN ANALYZE SELECT id from employees where id = 1;」では、「id」が主キー（btree）であり、「Heap Fetches」値は 0 になります。対照的に、「SELECT name from employees where id = 2」というクエリ（「name」にインデックスがない場合）では、エンジンは最初に「id」（インデックス上）を使用してページにアクセスし、「name」に必要な情報を含むページ（ディスクに保存されており、別の読み取りが必要）を取得します。上記のクエリが繰り返されると、キャッシュによりはるかに短い時間で済みます。別のクエリを見てみましょう。

SELECT id from employee where name = 'Zsh';

これは、非常に多くの時間がかかるシーケンシャルスキャンを引き起こします。[フルテーブルスキャン]。ただし、MySQL は作業スレッドを適用することで、並行してシーケンシャルスキャンを行うことができます。次に、名前にインデックスを作成します。

CREATE INDEX employee_idx on employees(name)

$ ビットマップインデックスが作成されました。さて、検索してみましょう。

SELECT id,name FROM employees Where name = 'Yousef'; (インデックスが使用されます)
SELECT id,name FROM employees WHERE name like '%You%' (インデックスは使用されません) #

実際、この式は単一の値ではなく、多くの可能性があるためです。

インデックスの利点#

高パフォーマンスのためのインデックス戦略#

効率的にインデックスを選択し実装する方法は多数あります。これは、さまざまな最適化技術と専門的な動作が利用可能であるためです。どのアプローチを使用するか、いつ使用するかを判断し、決定のパフォーマンスへの影響を評価する能力は、習得に時間がかかるスキルです。

このセクションでは、以下の戦略をカバーします。

  • 列の分離

  • プレフィックスインデックスとインデックス選択性

  • マルチカラムインデックス

  • 良い列の順序の選択

  • クラスタインデックスと非クラスタインデックス

  • カバリングインデックス

列の分離#

インデックスを無効にするか、MySQL が利用可能なインデックスを使用できなくするクエリをよく見かけます。MySQL は、列がクエリ内で分離されていない限り、通常、列にインデックスを使用できません。「列を分離する」とは、列が式の一部であったり、クエリ内の関数の中にあったりしないことを意味します。たとえば、次のクエリは actor_id のインデックスを使用できません。

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5

人間は WHERE 句が actor_id = 4 と同等であることを簡単に見抜けますが、MySQL は actor_id の方程式を解くことができません。これを行うのはあなたの役目です。WHERE 条件を簡素化する習慣を身につけ、インデックス列が比較演算子の一方の側に単独で存在するようにするべきです。

プレフィックスインデックスとインデックス選択性#

時折、長い文字列列にインデックスを付ける必要があり、これが大きくて遅いインデックスを引き起こすことがあります。1 つのアプローチは、前の章で示したように、シミュレートされたハッシュインデックスを作成することですが、これが常に十分であるとは限りません。

そのような場合に何ができるでしょうか？1 つの解決策は、列全体の値ではなく、最初の数文字のみをインデックスすることです。これによりインデックスサイズが減少しますが、選択性も減少します。インデックス選択性は、異なるインデックス値の数（カーディナリティ）とテーブル内の行の総数（#T）の比率であり、1/#T から 1 の範囲です。非常に選択的なインデックスは望ましいです。なぜなら、MySQL が一致を検索する際により多くの行をフィルタリングできるからです。ユニークインデックスは選択性が 1 であり、これは可能な限り最良の値です。

列のプレフィックスは、良好なパフォーマンスを達成するのに十分に選択的であることがよくあります。BLOB や TEXT 列、または非常に長い VARCHAR 列にインデックスを付ける場合、MySQL はその全長をインデックスすることを禁止しているため、プレフィックスインデックスを定義する必要があります。プレフィックスは、良好な選択性を提供するのに十分な長さでありながら、スペースを節約するのに十分短い必要があります。

プレフィックスは、インデックス全体がインデックスされているかのように、インデックスをほぼ同じように有用にするのに十分な長さであるべきです。言い換えれば、プレフィックスのカーディナリティは、完全な列のそれに近い必要があります。

良いプレフィックスの長さを決定するには、最も頻繁に出現する値を見つけ、そのリストを最も頻繁に出現するプレフィックスのリストと比較します。

都市の例データセットがあると仮定して、最も頻繁に出現する都市を見つけてみましょう。

SELECT COUNT(*) AS cnt, city
FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10

各値の出現回数は約 45 から 65 です。次に、3 文字のプレフィックスから始まる最も頻繁に出現する都市名のプレフィックスを見つけます。

cntcity
65ロンドン
49広島
48テボクサリ
48パク・クレット
48ヤウンド
47テルアビブ - ヤッファ
47シモガ
45カブヤオ
45カジャオ
45ビスリ
SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
cntpref
483サン
195チャ
177タン
167ソウ
163アル -
163サル
146シ
136ハル
130バル
129バット
各プレフィックスの出現回数が多く、ユニークなプレフィックスの数が少ないことがわかります。プレフィックスの長さを増やして、プレフィックスが列の全長に近い選択性を持つようにします。少しの実験で、7 が良い値であることがわかります。
cntpref
----------
70サンティアゴ
68サン・フェル
65ロンドン
61バレー・d
49広島
48テボクサ
48パク・クレ
48ヤウンド
47テル・アビ
47シモガ

良いプレフィックスの長さを計算する別の方法は、完全な列の選択性を計算し、プレフィックスの選択性をその値に近づけることです。完全な列の選択性を見つける方法は次のとおりです。

テーブル内の値の総数に対する異なる値の総数を計算します。

SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;

プレフィックスは、平均してほぼ同じくらい良いでしょう（ただし、ここには注意点があります）。選択性を.031 近くにターゲットにすることができます。非常に大きなテーブルで多くの異なる長さを 1 つのクエリで評価することが可能です。以下のように、1 つのクエリでいくつかのプレフィックス長の選択性を見つけることができます。

SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3, 
COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4, 
COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,  
COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
FROM sakila.city_demo;

このクエリは、プレフィックスの長さを増やすと、7 文字に近づくにつれて成功率が次第に小さくなることを示しています。平均選択性だけを見ておくのは良くありません。注意点は、最悪のケースの選択性も重要であるということです。平均選択性が 4 文字または 5 文字のプレフィックスが十分であると思わせるかもしれませんが、データが非常に不均一な場合、それは罠になる可能性があります。最も一般的な都市名プレフィックスの出現回数を 4 文字の値を使用して見ると、不均一性が明らかになります。

SELECT COUNT(*) AS cnt, LEFT(city, 4) AS pref 
FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;
cntpref
205サン
200サン
135ソウト
104チャン
91トゥール

4 文字のプレフィックスでは、最も頻繁に出現するプレフィックスが最も頻繁に出現する完全な長さの値よりもかなり多く発生します。つまり、これらの値の選択性は平均選択性よりも低くなります。このランダムに生成されたサンプルよりも現実的なデータセットを持っている場合、この効果をさらに見ることができるでしょう。たとえば、実際の都市名に 4 文字のプレフィックスインデックスを構築すると、「サン」や「ニュー」で始まる都市に対して非常に悪い選択性が得られます。

サンプルデータに対して良い値を見つけたので、列にプレフィックスインデックスを作成する方法は次のとおりです。

ALTER TABLE sakila.city_demo ADD KEY (city(7))

プレフィックスインデックスは、インデックスサイズを削減し、パフォーマンスを向上させるための便利なアプローチを提供します。ただし、いくつかの欠点もあります。MySQL は ORDER BY や GROUP BY クエリでプレフィックスインデックスを使用できず、カバリングインデックスとしても使用できません。プレフィックスインデックスが有利になる一般的な状況は、長い 16 進数識別子が使用される場合です。前の章では、そのような識別子を保存するより効率的な方法について説明しましたが、変更できないパッケージ化されたソリューションを使用している場合はどうでしょうか？これは、長い 16 進数文字列に基づいてウェブサイトセッションを保存するために MySQL を使用する vBulletin や他のアプリケーションでよく見られるケースです。最初の 8 文字（またはそれに近い）にインデックスを追加すると、アプリケーションに対して完全に透明な方法で、パフォーマンスが大幅に向上することがよくあります。

マルチカラムインデックス#

マルチカラムインデックスは、非常に理解されていないことがよくあります。一般的な間違いは、多くの列を個別にインデックスすることや、間違った順序で列をインデックスすることです。次のセクションで列の順序について説明します。最初の間違い、つまり多くの列を個別にインデックスすることは、SHOW CREATE TABLEで特異な署名を持っています。

CREATE TABLE t ( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3) )

[!info] このインデックス戦略は、一般的に「WHERE 句に現れる列にインデックスを作成する」といったあいまいだが権威的に聞こえるアドバイスを受けたときに発生します。このアドバイスは非常に間違っています。これにより、せいぜい 1 つ星のインデックスが生成されます。これらのインデックスは、真に最適なインデックスよりも多くのオーダーで遅くなる可能性があります。時には、3 つ星のインデックスを設計できない場合、WHERE 句を無視して最適な行の順序に注意を払うか、カバリングインデックスを作成する方がはるかに良いです。

多くの列に対する個別のインデックスは、ほとんどのクエリのパフォーマンスを向上させるのに役立ちません。MySQL 5.0 以降は、インデックスマージと呼ばれる戦略を使用して、こうした不適切にインデックスされたテーブルに少し対処できます。これにより、クエリは単一のテーブルから複数のインデックスを制限的に使用して、目的の行を見つけることができます。MySQL の古いバージョンでは、単一のインデックスしか使用できなかったため、十分な単一インデックスがない場合、MySQL はしばしばテーブルスキャンを選択しました。

たとえば、film_actor テーブルには film_id に対するインデックスと actor_id に対するインデックスがありますが、どちらもこのクエリの WHERE 条件の両方に対して良い選択肢ではありません。

SELECT film_id, actor_id FROM sakila.film_actor
WHERE actor_id = 1 OR film_id = 1;

古い MySQL バージョンでは、そのクエリは、2 つのクエリの UNION として書かない限り、テーブルスキャンを生成します。

SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 UNION ALL 
SELECT film_id, actor_id FROM sakila.film_actor WHERE film_id = 1 AND actor_id <> 1;

しかし、MySQL 5.0 以降では、クエリは両方のインデックスを使用し、同時にスキャンして結果をマージできます。

アルゴリズムには 3 つのバリエーションがあります：OR 条件のためのユニオン、AND 条件のための交差、そして 2 つの組み合わせのユニオンです。次のクエリは、2 つのインデックススキャンのユニオンを使用しています。Extra 列を調べるとわかります。

EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 OR film_id = 1

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type: index_merge possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY,idx_fk_film_id key_len: 2,2 ref: NULL rows: 29 Extra: **Using union(PRIMARY,idx_fk_film_id); Using where**

インデックスマージは、結局のところコストのかかる操作であることを覚えておくべきです。したがって、「インデックスマージ」がEXPLAINに表示される場合は、テーブルが不適切にインデックスされている可能性があります。

  • サーバーがインデックスを交差させる場合（通常は AND 条件の場合）、通常、関連するすべての列を含む単一のインデックスが必要であり、複数のインデックスを組み合わせる必要はありません。

  • サーバーがインデックスをユニオンする場合（通常は OR 条件の場合）、アルゴリズムのバッファリング、ソート、およびマージ操作が多くの CPU およびメモリリソースを使用することがあります。特に、すべてのインデックスが非常に選択的でない場合、スキャンが多くの行をマージ操作に返すためです。EXPLAIN でインデックスマージが表示された場合は、クエリとテーブル構造を調べて、これが本当に最良の結果であるかどうかを確認する必要があります。最適化スイッチオプションまたは変数を使用してインデックスマージを無効にすることができます。また、IGNORE INDEX を使用することもできます。

良い列の順序の選択#

列の順序を選択するための古い経験則があります：最も選択的な列をインデックスの最初に配置します。この提案はどれほど有用でしょうか？いくつかのケースでは役立ちますが、すべてのことを考慮すると、ランダム I/O やソートを避けることよりも重要ではないことが通常です（特定のケースは異なるため、すべてに当てはまるルールはありません。このルールはおそらく、あなたが考えるよりも重要ではないことを示しています）。

最も選択的な列を最初に配置することは、ソートやグループ化を考慮しない場合には良いアイデアです。したがって、インデックスの目的は WHERE ルックアップを最適化するだけです。そのような場合、インデックスを設計して、WHERE 句でインデックスのプレフィックスのみを指定するクエリに対してできるだけ早く行をフィルタリングするのが効果的です。ただし、これは列の選択性（全体のカーディナリティ）だけでなく、行を検索するために使用する実際の値、つまり値の分布にも依存します。これは、良いプレフィックスの長さを選択するために探求したのと同じタイプの考慮事項です。実際には、実行するクエリに対してできるだけ選択的になるように列の順序を選択する必要があるかもしれません。

例のクエリを取り上げて、より良く診断してみましょう。

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

(staff_id, customer_id) にインデックスを作成すべきか、それとも列の順序を逆にすべきか？

値の分布を調べるために、テーブル内の各述語のカーディナリティをカウントするためのクイッククエリを実行できます。

SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;

*************************** 1. row *************************** SUM(staff_id = 2): 7992 SUM(customer_id = 584): 30

経験則によれば、customer_id がテーブル内の行の数が少ないため、インデックスの最初に配置すべきです。次に、この特定の customer ID で選択された行の範囲内で staff_id がどれほど選択的であるかを確認するために、クエリを再実行できます。

SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584;

SUM(staff_id = 2): 17

ただし、特定のサンプルを実行できない場合は、全体的なカーディナリティを見て、古い経験則を使用する方が良いかもしれません。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, 
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*) 
FROM payment\G

staff_id_selectivity: 0.0001 customer_id_selectivity: 0.0373 COUNT(*): 16049

customer_id はより高い選択性を持っているため、再びその列をインデックスの最初に配置することになります。

ALTER TABLE payment ADD KEY(customer_id, staff_id);

最終的に、選択性とカーディナリティに関する経験則は探求するのが興味深いですが、ソート、グループ化、クエリの WHERE 句に範囲条件が存在することなど、他の要因がクエリパフォーマンスに大きな違いをもたらすことがあります。

クラスタインデックスと非クラスタインデックス#

クラスタインデックス#

クラスタインデックスは、別のタイプのインデックスではありません。むしろ、データストレージへのアプローチです。正確な詳細は実装によって異なりますが、InnoDB のクラスタインデックスは、実際に B-Tree インデックスと行を同じ構造内に保存します。

テーブルにクラスタインデックスがある場合、その行は実際にインデックスのリーフページに保存されます。

テーブルごとに 1 つのクラスタインデックスしか持てません。なぜなら、行を同時に 2 つの場所に保存することはできないからです。

ストレージエンジンはインデックスを実装する責任があるため、すべてのストレージエンジンがクラスタインデックスをサポートしているわけではありませんが、ここではInnoDBに焦点を当てます。

![](https://i.imgur.com/8X2TrZD.png align="left")

クラスタインデックスでのレコードの配置方法に注意してください。リーフページには完全な行が含まれていますが、ノードページには上記の図に示すようにインデックスされた列のみが含まれています。クラスタインデックス（テーブルに 1 つのみ、主に主キー）

一部のデータベースサーバーでは、どのインデックスをクラスタするかを選択できますが、執筆時点では MySQL の組み込みストレージエンジンはどれもそうではありません。InnoDB は主キーによってデータをクラスタリングします。

主キーを定義しない場合、InnoDB は代わりにユニークな非 NULL インデックスを使用しようとします。そのようなインデックスがない場合、InnoDB は隠れた主キーを定義し、それに基づいてクラスタリングします。

クラスタリングはパフォーマンスを助けることができますが、否定的で深刻な問題を引き起こすこともあります。

クラスタリングデータの利点は何ですか？

  • 関連データを近くに保つことができます。たとえば、メールボックスを実装する場合、user_id でクラスタリングすることで、ディスクからわずか数ページを取得することで、単一のユーザーのすべてのメッセージを取得できます。クラスタリングを使用しない場合、各メッセージは独自のディスク I/O を必要とするかもしれません。

  • データアクセスが速いです。クラスタインデックスは、インデックスとデータを 1 つの B-Tree に保持するため、クラスタインデックスから行を取得するのは通常、非クラスタインデックスでの比較検索よりも速いです。

  • カバリングインデックスを使用するクエリは、リーフノードに含まれる主キー値を使用できます。

欠点は何ですか？

  • クラスタリングは I/O バウンドのワークロードに対して最大の改善を提供します。データがメモリに収まる場合、アクセスの順序はそれほど重要ではないため、クラスタリングはあまり利益をもたらしません。

  • 挿入速度は挿入順序に大きく依存します。主キー順に行を挿入することが、InnoDB テーブルにデータをロードする最も速い方法です。主キー順に行をロードしなかった場合、大量のデータをロードした後に OPTIMIZE TABLE でテーブルを再編成することをお勧めします。

  • クラスタインデックス列の更新は高コストです。なぜなら、InnoDB は各更新された行を新しい位置に移動させる必要があるからです。

  • クラスタインデックスに基づくテーブルは、新しい行が挿入されたときや、行の主キーが更新されて行を移動する必要がある場合にページスプリットの影響を受けます。ページスプリットは、行のキー値がその行をデータで満たされたページに配置する必要があることを示すときに発生します。ストレージエンジンは、その行を収容するためにページを 2 つに分割する必要があります。ページスプリットは、テーブルがディスク上でより多くのスペースを使用する原因となることがあります。

  • クラスターテーブルは、特に行が密に詰まっていない場合やページスプリットのために非連続的に保存されている場合、フルテーブルスキャンが遅くなる可能性があります。

  • 非クラスタインデックスへのアクセスは、1 つのインデックスルックアップではなく、2 つのインデックスルックアップを必要とします。最後のポイントは少し混乱を招くかもしれません。なぜ非クラスタインデックスが 2 つのインデックスルックアップを必要とするのでしょうか？その答えは、非クラスタインデックスが保存する「行ポインタ」の性質にあります。リーフノードは、参照された行の物理的な位置へのポインタを保存するのではなく、行の主キー値を保存します。

つまり、非クラスタインデックスから行を見つけるために、ストレージエンジンは最初に非クラスタインデックス内のリーフノードを見つけ、そこに保存されている主キー値を使用して主キーをナビゲートし、行を見つけます。これは二重作業です：1 つの B-Tree ナビゲーションではなく、2 つの B-Tree ナビゲーションです。

InnoDB と MyISAM のデータレイアウトの比較#

クラスターデータレイアウトと非クラスターデータレイアウトの違い、そして主インデックスと副インデックスの対応する違いは、混乱を招き驚くべきものです。以下のテーブルを InnoDB と MyISAM がどのようにレイアウトするかを見てみましょう。

CREATE TABLE layout_test ( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2));

pk が 1 から 10,000 までランダムに挿入され、OPTIMIZE TABLEで最適化されたテーブルを想定します。言い換えれば、データはディスク上で最適に配置されていますが、行はランダムな順序である可能性があります。col2 の値は 1 から 100 の間でランダムに割り当てられているため、重複がたくさんあります。

非クラスタインデックス設計は、単一操作の行ルックアップを提供できないことがよくあります。行が変更されると、元の位置に収まらない可能性があるため、断片化された行やテーブル内の「転送アドレス」が発生する可能性があり、どちらも行を見つけるためにより多くの作業を必要とします。

![](https://i.imgur.com/hsFXyrH.png align="left")

![](https://i.imgur.com/NjLLYou.png align="left")

MyISAM のデータレイアウト#

MyISAM は、挿入された順序でディスク上に行を保存します。

![](https://i.imgur.com/tyOooS9.png align="left")

行は固定サイズであるため、MyISAM はテーブルの先頭から必要なバイト数をシークすることで任意の行を見つけることができます（MyISAM は、行が固定サイズか可変サイズかに応じて異なる戦略を使用します）。

インデックス内の各リーフノードは、単に行番号を含むことができます。

InnoDB のデータレイアウト#

主キー順に行を挿入する InnoDB#

![](https://i.imgur.com/69yQyJ1.png align="left")

/** クラスタインデックス **/
ALTER TABLE dbo.MyTable
	ADD CONSTRAINT PK_MyTable PRIMARY KEY(Id);

/** 非クラスタインデックス **/
ALTER TABLE dbo.MyTable
ADD CONSTRAINT UC_MyTable_Id UNIQUE(Id);

![](https://i.imgur.com/fmPsCIk.png align="left")

![](https://i.imgur.com/bP6t5C5.png align="left")

非クラスタインデックスを作成したと仮定します。

![](https://i.imgur.com/JpDfCFc.png align="left")

![](https://i.imgur.com/jRIGhug.png align="left")

行ロケータには以下が含まれます：

  • クラスタキー（employee_id）

  • 実際の行 [name]

クラスタインデックスと非クラスタインデックスの両方が協力してデータを見つけるために機能しています！

実際の行を見つけるために使用されるステップは何ですか？

![](https://i.imgur.com/ZacibzY.png align="left")

  1. SQL サーバーは、インデックス上の名前列に対して非クラスタインデックスを使用して、インデックス上の従業員エントリを迅速に見つけます。

  2. クラスタインデックス（employee_id）が実際の行を見つけるために使用されます！

SQL Server では、実行計画を上から下、右から左に読みます！

![](https://i.imgur.com/gnjyi2X.png align="left")

クエリがクラスタインデックスツリーをナビゲートして基礎となるテーブルデータにアクセスすることをクラスタインデックスシークと呼びます。クラスタインデックスには基礎となるテーブルデータ自体が含まれているため、1 つのクラスタインデックスを作成できます。

![](https://i.imgur.com/mYNFF3C.png align="left")

非クラスタインデックスは基礎データから分離されており、基礎データはクラスタインデックスとして存在する可能性があります。直接利用可能なデータは制限される可能性があります。なぜなら、通常、非クラスタインデックスにはテーブルの列のサブセットのみが含まれるからです。

インデックスに含まれていない列からの値が要求される場合、クエリは前述の参照を使用して基礎データに戻る可能性があります。クエリオプティマイザがそれを行うのが高コストすぎると判断した場合、インデックスを使用する代わりに基礎データをスキャンすることに戻るかもしれません。

非クラスタインデックスが基礎データから分離されていることは、いくつかの重要な機能をもたらします。

フィルタリングインデックスは、ユーザー定義の述語を満たす行のみを含みます。これを作成するには、インデックス定義にWHERE句を使用します。したがって、クラスタインデックスは、テーブル上のすべてのデータを含む必要があるため、使用できません。

CREATE NONCLUSTERED INDEX IX_PhoneBook_NCI 
ON dbo.PhoneBook(LastName, FirstName)
WHERE (LastName >= 'Yousef');

要約：クラスタインデックスはデータ全体を表現する方法です。非クラスタインデックスは、基礎データを参照する物理的に分離された構造であり、異なるソート順序を持つことができます。

読み込み中...
文章は、創作者によって署名され、ブロックチェーンに安全に保存されています。