MySQL インデックスの設計と最適化の方法

MySQL インデックスの設計と最適化の方法

インデックスとは何ですか?

データベース インデックスは、追加の書き込みとストレージ スペースを犠牲にして、データベース テーブルでのデータ取得操作の速度を向上させるデータ構造です。簡単に言えば、索引は本の目次に似ており、そこに記録されたページ番号に基づいて必要なコンテンツをすばやく見つけることができます。 ——Wikipedia の一般的な索引は何ですか?

  • 通常インデックス: 制限のない最も基本的なインデックス
  • ユニークインデックス:「通常のインデックス」と同様ですが、インデックス列の値は一意である必要がありますが、null値が許可されます
  • 主キー インデックス: null 値を許可しない特別なインデックスです。
  • 全文インデックス: MyISAM テーブルにのみ使用できます。大規模なデータの場合、全文インデックスの生成には時間がかかり、スペースも消費されます。
  • 複合インデックス: 複数条件クエリの効率を向上させるために、「最左接頭辞一致原則」に従って複合インデックスを確立できます。

ここでは比較的複雑な組み合わせを例に挙げて、最適化する方法を紹介します。

左端のプレフィックス一致の原則

まず、左端のプレフィックス一致の原則が何であるかを知る必要があります。

最左プレフィックス一致原則とは、データ取得に B+Tree ジョイント インデックスを使用する場合、MySQL オプティマイザは述語 (フィルタ条件) を読み取り、範囲クエリまたは不等クエリに遭遇するまでジョイント インデックス フィールドが作成された順序で右方向に一致させます。このフィールドの後のインデックス列は使用されません。このとき、 key_lenを計算すると、ジョイント インデックスによって実際に使用されているインデックス列を分析できます。

key_lenの計算方法

key_len計算は、インデックスの左端のプレフィックス一致の原則を理解するのにも役立ちます。

key_lenorder byを除いて、結果セットを取得するために使用される選択インデックスの長さ [バイト数] を示します。つまり、 order byでもインデックスが使用される場合、 key_lenは含まれません。

key_lenを計算する前に、基本的なデータ型を確認しましょう (UTF8 エンコードを例にします)。

タイプ占有スペースNULLの追加使用は許可されません
文字1文字は3バイト1バイト
varchar 1文字は3バイト1バイト
整数4バイト1バイト
ちっちゃい1バイト1バイト

テストデータテーブルは次のとおりです。

テーブル `test_table` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) デフォルトはNULLではない、
 `b` int(11) デフォルトはNULLではない、
 `c` int(11) デフォルトはNULLではない、
 主キー (`id`)、
 キー `test_table_a_b_c_index` (`a`,`b`,`c`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

ヒットインデックス:

mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12 | const、const、const | 1 | 100.00 | インデックスを使用 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+

key_len = 12がわかります。これはどのように計算されるのでしょうか?
文字セットは UTF8 なので、1 つのフィールドは 4 バイトを占め、3 つのフィールドは 4 * 3 = 12 バイトになります。

NULL が許可されるかどうか。NULL が許可される場合は、フィールドをマークするために追加のバイトが必要です。異なるデータ型には異なるバイト サイズが必要です。

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15 | const、const、const | 1 | 100.00 | インデックスを使用 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+

フィールドが空になることが許可されている場合、 key_len 15 = 4 3 + 1 3 になることがわかります (INT 型が空の場合、余分なバイトを占有します)。

インデックスの最適化

これらの基礎知識があれば、実際の SQL に基づいてパフォーマンスを判断できます。

上記のデータ テーブルを例に、3 つのフィールド a、b、c の結合インデックスを作成します。

SQL ステートメントインデックスするかどうか
select * from test_table where a = 1 and b = 2 and c = 3; を説明します。追加: インデックス key_len: 15 を使用
select * from test_table where a = 1 and b = 2 and c = 3 order by c; を説明します。追加: インデックス key_len: 15 を使用
select * from test_table where b = 2 and c = 3; を説明します。追加: where の使用; インデックス key_len の使用: 15
select * from test_table where a = 1 order by c; を説明します。追加: where の使用; index の使用; filesort の使用 key_len: 5
select * from test_table order by a, b, c; を説明します。追加: インデックス key_len: 15 を使用
select * from test_table order by a, b, c desc; を説明します。追加:インデックスの使用; ファイルソートの使用 key_len:15
select * from test_table where a in (1,2) and b in (1,2,3) and c = 1; を説明します。追加: where の使用; インデックス key_len の使用: 15

通常、実行プランを表示するときに、Extra 列が Using index になっている場合は、オプティマイザーがカバーリング インデックスを使用していることを意味します。

  • SQL1はカバーインデックスを使用でき、パフォーマンスが良好です。
  • SQL2はカバーインデックスを使用してソートを回避できるため、パフォーマンスが向上します。
  • SQL3ではカバーインデックスを使用できますが、where句に基づいてフィルタリングする必要があります。
  • SQL4では部分インデックスaを使用できますが、ソートを避けることができず、パフォーマンスが低下します。
  • SQL5 では、カバーリング インデックスをフルに活用してソートを回避できるため、パフォーマンスが向上します。
  • SQL6 ではカバーリング インデックスを使用できますが、ソートは避けられません (これは、MySQL InnoDB がインデックスを作成するときに昇順をデフォルトとし、インデックスを自動的に降順でソートできないためです)。
  • SQL7 ではカバーリングインデックスを使用できますが、where 句 (非固定値クエリ) に従ってフィルタリングする必要があります。

インデックス仕様を作成する

  • インデックスのメンテナンスのコストを考慮すると、1 つのテーブル内のインデックスの数は 5 を超えてはならず、1 つのインデックス内のフィールドの数も 5 を超えてはなりません。
  • 性別などのカーディナリティの低い列にはインデックスを作成しないでください。 低カーディナリティ列で作成されたインデックス クエリは、特にテーブルを返すコストがある場合、必ずしも完全なテーブル スキャンよりもパフォーマンス上の利点があるわけではありません。
  • 適切に結合インデックスを作成します。(a,b,c) は (a)、(a,b)、(a,b,c) と同等です。
  • カバーリング インデックスを適切に使用すると、IO が削減され、ソートが回避されます。

上記は、MySQL インデックスの設計と最適化の方法の詳細です。MySQL インデックスの設計と最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQLスケーラブル設計の基本原則
  • プロフェッショナルなMySQL開発設計仕様とSQL記述仕様
  • MySQL のバックアップとリカバリの設計アイデア
  • MySQL 20 の高性能アーキテクチャ設計原則 (収集する価値あり)
  • MySQL データベース設計 3 つのパラダイム例分析
  • MySQL テーブルとデータベース シャーディングのアプリケーション シナリオと設計方法
  • MySQLデータベース設計:Pythonを使ったスキーマ操作方法の詳しい解説
  • MySQLのインデックス設計の原則と一般的なインデックスの違いについて簡単に説明します。
  • 効率的で合理的なMySQLクエリステートメントを設計する方法
  • PHP+MySQL ツリー構造(無制限分類)データベース設計 2 つの例
  • 数百万件のレコードの分散ストレージを実現するための MySQL シャーディングのバッチ クエリ設計パターンの詳細な説明
  • PHP+MySQL投票システムの設計と実装
  • よくある MySQL テーブル設計エラーの概要

<<:  img usemap 属性 中国地図リンク

>>:  フレックスレイアウトは左のテキストオーバーフローを実現し、右のテキストの適応を省略します

推薦する

Nginx での Frp による https への強制リダイレクト設定の詳細な説明

自宅のルーターが300Mの帯域幅を80Mに強制的に減らしたため、3205Uソフトルーターを購入しまし...

require loaderの実装原理の深い理解

序文Node は新しいプログラミング言語ではなく、JavaScript のランタイムに過ぎないとよく...

MySQL で誕生日から年齢を計算する複数の方法

以前はMySQLをあまり使用していなかったため、MySQLの機能にあまり詳しくありませんでした。この...

Vueのライブ放送機能の詳しい説明

最近、会社でたまたま生放送をしていたのですが、今日は私が遭遇した落とし穴を記録します。会社のサーバー...

ネイティブ JS オブジェクト指向タイピング ゲーム

この記事では、JSオブジェクト指向タイピングゲームの具体的なコードを参考までに紹介します。具体的な内...

Centos8 システムの VMware インストール チュートリアル図 (中国語グラフィカル モード)

目次1. ソフトウェアとシステムイメージ2. 仮想マシンを作成する3. CentOS8をインストール...

Vueはキー表示のショートカットキー効果を取得する入力コンポーネントを実装します

ページ上でショートカットキーをカスタマイズする要件に遭遇し、ショートカットキーを設定して表示する場所...

空のパスがページのパフォーマンスに与える影響に対する解決策

数日前、Google Reader で Yu Bo さんが共有した投稿「空のパスがページのパフォーマ...

VueはWebSocketを使用してチャット機能をシミュレートします

この効果は、2つのブラウザが互いにシミュレートしていることを示しています 1. シミュレートされたノ...

Mysqlトランザクション処理の詳細な説明

1. MySQLのトランザクションの概念MySQL トランザクションは主に、操作量が多く複雑度の高い...

スライダーを作成するためのネイティブ js ドラッグ アンド ドロップ機能のサンプル コード

ドラッグ アンド ドロップはフロントエンドでよく使われる機能であり、多くのエフェクトで js のドラ...

count(1)、count(*)、count(列名)の実行の違いの詳細な説明

実施効果: 1. count(1) と count(*)テーブル内のデータ量が多い場合、テーブルを分...

Linux で実行可能ファイルを実行するときに「そのようなファイルまたはディレクトリはありません」というプロンプトが表示される場合の解決策

最近、Linux オペレーティング システムを使用して実行可能ファイルを実行していたところ、「そのよ...

Sublime TextがUbuntuで中国語を入力できない問題の最も簡単な解決策

崇高なSublime Text はコード エディター (Sublime Text2 は有料ソフトウェ...

HTMLタグの説明

HTMLタグの説明1. HTMLタグタグ: !DOCTYPE説明: HTML ドキュメントが準拠する...