MySQL インデックスクエリ最適化スキルを習得するための記事

序文

この記事では、DBA がいないチームが参考にできるように、MySQL の一般的な使用に関するヒントをいくつかまとめています。特に指定がない限り、ストレージエンジンは InnoDB です。

MySQLの機能

MySQL の特性を理解することで、MySQL をより効果的に使用できるようになります。MySQL と他の一般的なデータベースとの最大の違いは、データの保存と読み取りを担当するストレージエンジンの概念です。ストレージエンジンにはそれぞれ異なる特性があります。ユーザーは、ビジネスの特性に応じて適切なストレージエンジンを選択したり、新しいエンジンを開発したりすることもできます。 MySQL の論理アーキテクチャは次のとおりです。

MySQL のデフォルトのストレージエンジンは InnoDB です。このストレージエンジンの主な機能は次のとおりです。

トランザクション処理をサポート
行レベルのロックをサポート
データは、一連のデータファイルで構成される表領域に保存されます。
MVVC（マルチバージョン同時実行制御）メカニズムを使用して高い同時実行性を実現します。
テーブルは主キーのクラスター化インデックスに基づいて作成される
ホットバックアップをサポート

その他の一般的なストレージエンジン機能の概要:

MyISAM: MySQL の旧バージョンのデフォルトエンジン。トランザクションと行レベルのロックはサポートされていません。開発者はテーブルロックを手動で制御できます。フルテキストインデックスをサポートしています。クラッシュ後に安全に復元することはできません。圧縮テーブルをサポートしています。圧縮テーブルのデータは変更できませんが、占有スペースが少なくなり、クエリのパフォーマンスが向上します。
アーカイブ: 挿入と選択のみをサポートします。バッチ挿入は非常に高速です。データはテーブル全体をスキャンして照会されます。
SCV: SCV ファイルをテーブルとして扱う
メモリ: データはメモリに保存されます

他にもたくさんありますが、全てを列挙することはできません。

データ型の最適化

データ型を選択するための原則:

より少ないスペースを占めるデータタイプを選択する
シンプルなタイプを選ぶ
不要なNULL列を避ける

フットプリントが小さいタイプでは、ディスク、メモリ、CPU などのハードウェアリソースをより多く節約できます。単純な型を使用するようにしてください。たとえば、可能であれば char ではなく int を使用します。char のソートには文字セットの選択が含まれ、int を使用するよりも複雑になるからです。 NULL 可能列はより多くのストレージスペースを使用するため、NULL 可能列にインデックスを作成すると、MySQL ではレコードを保持するために追加のバイトが必要になります。テーブルを作成する場合、デフォルトでは null 値が許可されますが、開発者はこれを見落としがちです。保存するデータに null 値が絶対に含まれない場合は、手動で null 値を許可しないように変更するのが最適です。

整数型

整数型には次のものがあります:

ちっちゃい
小さい整数
中程度
整数
ビッグイント

それぞれ8、16、24、32、64ビットを使用して数値を保存し、

範囲内の数値の前に unsigned 修飾子を付けると、正の数値の表現可能な範囲を 2 倍にすることができますが、負の数値は表現できません。また、整数の場合は長さを指定しても意味がありません。データ型が決まると、それに応じて長さも決まります。

小数点型

フロート
ダブル
小数点

float と double は、通常の float と double です。前者は 32 ビットを使用してデータを格納し、後者は 64 ビットを使用してデータを格納します。整数と同様に、長さを指定しても意味がありません。

10 進数型はより複雑で、正確な計算をサポートし、大量のスペースを占有します。10 進数は 4 バイトを使用して 9 桁を表します。たとえば、10 進数 (18,9) は、数値の長さが 18 で、9 桁の 10 進数、9 桁の整数、および小数点自体 (合計 9 バイト) が含まれることを意味します。小数点型はより多くのスペースを占め、精度の計算が非常に複雑であることを考慮すると、データ量が大きい場合は、代わりに bigint を使用することを検討できます。永続化と読み取りの前に、実際のデータに対していくつかのスケーリング操作を実行できます。

文字列型

varchar
文字
varbinary
バイナリ
ブロブ
文章
列挙する

varchar 型データが実際に占有するスペースは、文字列の長さに、文字列の長さを記録するために使用される 1 バイトまたは 2 バイトを加えた値になります (行形式が固定に設定されていない場合)。varchar は多くのスペースを節約します。テーブルの列内の文字列データの長さが大きく変化する場合は、varchar を使用するのが適切です。

char が実際に占めるスペースは固定です。表内の文字列データの長さがほぼ同じか非常に短い場合は、チャートタイプを使用するのが適切です。

varchar と char に対応するのは、varbinary と binary です。後者はバイナリ文字列を格納します。前者と比較すると、後者は大文字と小文字を区別し、エンコード方法を考慮する必要がなく、比較演算を実行するときに高速です。

varchar(5) と varchar(200) は文字列 "hello" を格納するときに同じストレージスペースを使用しますが、varchar の長さを大きく設定してもパフォーマンスに影響しないというわけではありません。実際、メモリ内に一時テーブルを作成する (一部のクエリでは MySQL が自動的に一時テーブルを作成します) などの MySQL の内部計算によって、データを格納するために固定サイズのスペースが割り当てられます。

Blob はバイナリ文字列を使用して大きなテキストを保存し、text は文字を使用して大きなテキストを保存します。InnoDB は、このようなデータを保存するのに専用の外部ストレージ領域を使用し、データ行にはそれらのポインタのみを保存します。このようなデータにインデックスを作成することは適切ではありません (作成する必要がある場合は、文字列プレフィックスに対してのみ作成できます) が、誰もそうしません。

文字列の列が大量に繰り返され、コンテンツが限られている場合は、代わりに列挙を使用できます。MySQL は列挙を処理するときに「数値文字列」テーブルを維持します。列挙を使用すると、多くのストレージスペースを節約できます。

時間タイプ

年
日付
時間
日時
タイムスタンプ

日時ストレージ範囲は 1001 ～ 9999 で、秒単位の精度です。タイムスタンプは、1970 年 1 月 1 日の午前 0 時からの秒数を格納し、2038 年まで表現できます。これは 4 バイトを占有し、これは datetime が占有するスペースの半分です。 timestamp で表される時間は、タイムゾーンに関連しています。また、timestamp 列には別の機能があります。挿入または更新ステートメントを実行すると、MySQL は timestamp 型の最初の列のデータを現在の時間に自動的に更新します。多くのテーブルは、UpdateTime という列を使用して設計されています。システムが 2038 年まで使用されない場合は、この列にタイムスタンプを使用するのが適切です。この列は自動的に更新されます。

主キータイプの選択

可能な限り整数を使用してください。整数は占有するスペースが少なく、自動的に拡張するように設定できます。特に、GUID、MD5 などのハッシュ値文字列を主キーとして使用しないでください。これらの文字列は非常にランダムです。InnoDB の主キーはデフォルトでクラスター化インデックス列であるため、データストレージが分散しすぎます。また、InnoDB のセカンダリインデックス列には、デフォルトでプライマリキー列が含まれます。プライマリキーが長すぎると、セカンダリインデックスも多くのスペースを占有することになります。

特殊なタイプのデータ

IP アドレスを保存するには、32 ビットの符号なし整数を使用するのが最適です。MySQL には、IP アドレスのデジタル表現と文字列表現を変換する関数 inet_aton() と inet_ntoa() が用意されています。

インデックスの最適化

InnoDBはインデックスを実装するためにB+ツリーを使用します。例えば、Peopleテーブルがあるとします。テーブル作成ステートメントは次のようになります。

テーブル「people」を作成します（
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `名前` varchar(5) NOT NULL,
 `年齢` tinyint(4) NOT NULL,
 `Number` char(5) NOT NULL COMMENT 'Number',
 主キー (`Id`)、
 キー `i_name_age_number` (`名前`,`年齢`,`番号`)
) ENGINE=InnoDB AUTO_INCREMENT=14 デフォルト CHARSET=utf8;

データを挿入:

インデックス構造はおおよそ次のようになります。

つまり、インデックス列の順序は非常に重要です。2 つのデータ行の Name 列が同じ場合は、Age 列を使用してサイズを比較します。Age 列が同じ場合は、Number 列を使用してサイズを比較します。まず最初の列で並べ替え、次に 2 番目の列で並べ替え、最後に 3 番目の列で並べ替えます。

クエリを使用する場合は、左から右への一致を試みる必要があります。また、左の列の範囲を検索すると、右の列はインデックスを使用できません。また、代替の列を検索することはできません。そうしないと、後続のインデックスを使用できなくなります。次の SQL ステートメントは肯定的な例です。

名前 = 'Abel'、年齢 = 2、番号 = 12312 の人から * を選択
名前が 'Abel' である人から * を選択
名前が 'Abel%' のような人から * を選択
名前が「Andy」で、年齢が11～20歳の人から*を選択
人から*を選択名前で並べ替え
人から*を選択名前、年齢で並べ替え
人から*を選択 GROUP BY 名前

次の SQL ステートメントは否定的な例です。

年齢 = 2 の人から * を選択
NAME が '%B' のような人から * を選択
年齢が 2 である人から * を選択
NAME = 'ABC' AND number = 3 の人から * を選択
NAME が 'B%' で、年齢が 22 である人から * を選択

ハッシュ値を使用してインデックスを作成する手法

テーブル内に長い文字列を格納する列がある場合、たとえば名前が URL である場合、この列に作成されるインデックスは大きくなります。これを軽減する方法があります。URL 文字列の数値ハッシュ値にインデックスを作成します。 URL のハッシュ値を格納するための新しいフィールド (たとえば、URL_CRC) を作成し、このフィールドのインデックスを作成して、次のクエリを記述します。

URL_CRC = 387695885、URL = 'www.baidu.com' の場合、t から * を選択します。

データ量が多い場合は、ハッシュの競合を防ぐために、ハッシュ関数をカスタマイズするか、MD5 関数の戻り値の一部をハッシュ値として使用することができます。

SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)

プレフィックスインデックス

文字列列に格納されているデータが長く、作成されたインデックスも大きい場合は、プレフィックスインデックスを使用できます。つまり、文字列の最初の数文字のみをインデックス化します。これにより、インデックスサイズを短縮できます。ただし、当然ながら、このタイプのインデックスは order by および group by を実行するときには機能しません。

プレフィックスインデックスを作成するときは、プレフィックスの長さを選択することが重要です。元のデータ分布を破壊せずに、より短いプレフィックスを選択するようにしてください。たとえば、ほとんどの文字列が「abc」で始まる場合、プレフィックスインデックスの長さが 4 に制限されていると、インデックス値に「abcX」の繰り返しが多すぎます。

複数列インデックス

上記の「People」に作成されたインデックスは複数列のインデックスであり、多くの場合、複数の単一列のインデックスよりも優れています。

複数のインデックスに対して AND クエリを実行する場合は、複数の単一列インデックスではなく、複数列インデックスを作成する必要があります。
次のように書いてみてください:

select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'

複数列インデックスの順序は非常に重要です。通常、並べ替えやグループ化のクエリを考慮しない場合は、選択性が高い列 (選択性とは、テーブルインデックス列内の異なるデータの数/行の合計数を指します。選択性が高いということは、重複データが少ないことを意味します) を先頭に配置する必要があります。ただし、例外もあります。一部のクエリが頻繁に実行されることが確認できる場合は、これらのクエリの選択性を優先する必要があります。たとえば、上記の People テーブルで Name の選択性が Age よりも高い場合、クエリステートメントは次のように記述する必要があります。

名前 = 'xxx'、年齢 = xx の人から * を選択します

Name列をインデックスの左側に配置する方が適切ですが、特定のSQL実行率が最も高い場合、例えば

名前 = 'xxx'、年齢 = 20 の人から * を選択します

データベース内に age=20 のレコードが非常に少ない場合、インデックス列の左端に age を配置する方が効率的です。年齢をインデックスの左側に配置すると、年齢が 20 でない他のクエリに対して不公平になる可能性があります。年齢 = 20 が最も頻繁に発生するクエリ条件であるかどうかわからない場合は、すべての要素を考慮する必要があり、名前を左側に配置する方が適切です。

クラスター化インデックス

クラスター化インデックスは、データストレージ構造です。InnoDB は、セカンダリインデックスの場合のように、インデックス列の値と、ポイントされた行のプライマリキー値のみを格納するのではなく、プライマリキーインデックスのリーフノードにデータ行を直接格納します。この機能により、テーブルにはクラスター化インデックスを 1 つだけ持つことができます。テーブルに主キーまたは一意のインデックスを持つ列が定義されていない場合、InnoDB は非表示の列を生成し、それをクラスター化インデックス列として設定します。

カバーインデックス

簡単に言えば、一部のクエリではインデックス列のみをクエリする必要があるため、インデックス B ツリーノードに記録された主キー ID に基づいてセカンダリクエリを実行する必要はありません。

重複した冗長なインデックス

列にインデックスを繰り返し作成しても、メリットはなくデメリットしか生じないため、避けるようにしてください。たとえば、InnoDB の主キーはデフォルトでクラスター化インデックスであるため、主キーに対して一意のインデックスと通常のインデックスを作成するのは冗長です。

冗長インデックスは重複インデックスとは異なります。たとえば、あるインデックスが (A,B) で、別のインデックスが (A) の場合、これは冗長インデックスと呼ばれます。前者は後者を置き換えることができますが、後者は前者を置き換えることはできません。ただし、(A,B) と (B)、および (A,B) と (B,A) は冗長なインデックスではなく、どちらも他方を置き換えることはできません。

テーブル内に既にインデックス（A）が存在し、インデックス（A、B）を作成する場合は、既存のインデックスを拡張するだけで済みます。新しいインデックスを作成する必要はありません。インデックス (A) がすでに存在する場合は、インデックス (A, ID) を作成する必要はありません。ID は主キーを指します。これは、インデックス A にはすでにデフォルトで主キーが含まれており、冗長な主キーと見なされるためです。

ただし、冗長なインデックスが望ましい場合もあります。たとえば、既存のインデックス (A) があり、それを (A, B) に拡張すると、列 B が非常に長い型であるため、A のみをクエリしても以前ほど高速ではありません。この場合は、新しいインデックス (A, B) を作成することを検討できます。

未使用のインデックス

未使用のインデックスは、挿入、更新、削除操作の効率を高めるだけなので、適時に削除する必要があります。

インデックスの使用状況の概要

インデックスの 3 つ星原則:

クエリ関連のレコードを順番にまとめるインデックスには 1 つの星が付きます。
インデックス内のデータの順序がクエリ結果の順序と一致する場合は星1つ
インデックスにクエリに必要なすべての列が含まれている場合は星 1 つ

最初の原則は、where 条件内のクエリの順序がインデックスと一致していることを意味し、これは前述のように左から右にインデックスを使用することです。

インデックスは万能薬ではありません。データの量が大きい場合、インデックス自体の維持にパフォーマンスが消費されるため、パーティション分割とテーブルストレージを検討する必要があります。

クエリの最適化

クエリが遅い理由

データベースから追加の行が要求されたかどうか

たとえば、アプリケーションに必要なデータは 10 個だけですが、データベースからすべてのデータを要求し、UI に表示する前にほとんどのデータを破棄します。

データベースから追加の列が要求されるかどうか

たとえば、アプリケーションでは5列のみを表示する必要がありますが、すべての列はselect * fromによってチェックされます。

同じクエリが複数回実行されるかどうか

アプリケーションは、一度クエリを実行してキャッシュし、後で必要になったときに最初のクエリからのレコードを使用することを検討できますか?

MySQL は追加のレコードをスキャンしていますか?

実行プランを表示することで、スキャンする必要があるレコードの数を大まかに把握できます。この数が予想を超える場合は、インデックスの追加、SQL の最適化 (このセクションの焦点)、またはテーブル構造の変更 (特定のステートメントクエリ専用の別のサマリーテーブルの追加など) によって解決を試みてください。

クエリを再構築する方法

複雑なクエリを複数の単純なクエリに分割する
大きなクエリを、それぞれ同じクエリ機能を持つ小さなクエリに分割し、小さな部分のみを完了します。
関連するクエリを分解します。大規模な関連クエリを複数のテーブルを個別にクエリするように変更し、アプリケーションコードで処理することができます。

その他

count() の最適化

Count には 2 つの機能があります。1 つは指定された列または式をカウントすること、もう 1 つは行の数をカウントすることです。パラメータが列名または式を渡す場合、count は結果が NULL でないすべての行をカウントします。パラメータが * の場合、count はすべての行をカウントします。式を渡す例を次に示します。

人からcount('B%'のような名前)を選択

count() の代わりに、実行プラン内の行数などの近似最適化を使用できます。
インデックスカバースキャン
要約表を追加
メモリキャッシュシステム内のデータレコード数を増やす

関連クエリの最適化

MySQL オプティマイザは、関連テーブルクエリを次のように実行します。たとえば、列 c によって関連した 2 つのテーブル A と B がある場合、MySQL はテーブル A を走査し、列 c の値に基づいてテーブル B のデータを検索します。まとめると、通常、インデックスがない場合は、テーブル B の列 c にインデックスを追加するだけで済みます。
インデックスが機能するように、order by および group by に関係する列が 1 つのテーブルにのみ属していることを確認します。

サブクエリの最適化

MySQL 5.5 以下の場合は、サブクエリの代わりに結合を使用するようにしてください。

グループ化と個別化を最適化する

可能であれば、両方の操作を主キーに適用してみてください。

制限を最適化する（例：SQL）

sa_stockinfo から * を選択 ORDER BY StockAcc LIMIT 400, 5

MySQL オプティマイザーは 405 行の列データをすべて検索し、そのうち 400 行を破棄します。カバーリングインデックスクエリを使用できる場合は、それほど多くの列をクエリする必要はありません。まず、次のように変更します。

SELECT * FROM sa_stockinfo i JOIN (SELECT StockInfoID FROM sa_stockinfo ORDER BY StockAcc LIMIT 400,5)t ON i.StockInfoID = t.StockInfoID

StockAcc にインデックスが構築されています。このクエリは、インデックスカバレッジを使用して条件を満たす主キーをすばやく検索し、結合クエリを実行します。データ量が多い場合、その効果は明らかです。

最適化された結合

必要ない場合は、必ずキーワード union all を使用して、MySQL がデータを一時テーブルに格納するときに一意性の検証を実行しないようにしてください。

レコードが存在するかどうかを判断するための通常の方法は、

t の where 条件から count(*) を選択

次のように書く方が良いでしょう。

SELECT IFNULL((条件 LIMIT 1 の tableName から 1 を選択),0)

要約する

これで、MySQL インデックスクエリの最適化手法に関するこの記事は終了です。より関連性の高い MySQL インデックスクエリの最適化手法については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: