MySQLでインデックスエラーが発生する状況について簡単に説明します

MySQLでインデックスエラーが発生する状況について簡単に説明します

以下に、トレーニング機関からのヒントと私自身の要約をいくつか示します。
以下のインデックスの内容を説明するために、まず一時テーブルtest02を作成します。

テーブル `sys_user` を作成します (
  `id` varchar(64) NOT NULL COMMENT '主キー',
  `name` varchar(64) デフォルト NULL コメント 'name',
  `age` int(64) デフォルト NULL コメント '年齢',
  `pos` varchar(64) デフォルト NULL コメント '位置',
  主キー (`id`)、
  キー `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザーテーブル';

このテーブルには、主キー、名前、年齢、役職の4つのフィールドがあります。

最初の式を説明しましょう:
1. フルバリューマッチ私のお気に入り
2. 最良左接頭辞ルール(重要)

完全な値の一致とは、結合された複合インデックスの順序と数が、検索条件の順序と数と同じである必要があることを意味します。
最良左プレフィックス ルールとは、複数の列にインデックスが付けられる場合、最も左のプレフィックス ルールに従う必要があることを意味します。これは、クエリがインデックスの左端の列から開始され、インデックス内の列をスキップしないことを意味します。<br /> 次に、このテーブルに複合インデックスを作成します。

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

以下は検索ステートメントです:

SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

ここに画像の説明を挿入

検索ステートメントの前にキーワード EXLAIN を追加すると、インデックスが使用されているかどうかがわかります。

(1) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(2)EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22;
(3)EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND pos = 'java';

ここに画像の説明を挿入

ここに画像の説明を挿入

ここに画像の説明を挿入

表示された結果から、最初の複合インデックスの 3 つのフィールドすべて、2 番目の複合インデックスの 2 つのフィールドのみ、3 番目の複合インデックスの 1 つのフィールドのみを使用していることがわかります。 3 つのステートメントすべてでインデックスを使用しますが、最初のステートメントが最も優れていることは明らかです。

何が失敗するか見てみましょう:

(4)EXPLAIN SELECT * FROM sys_user WHERE age = 22;
(5) EXPLAIN SELECT * FROM sys_user WHERE pos = 'java';
(6) EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos = 'java';

ここに画像の説明を挿入

ここに画像の説明を挿入

ここに画像の説明を挿入

上記の 3 つの状況はすべて、最左プレフィックスの原則に違反しているため、フル テーブル スキャンになります。複合インデックスの左端の列は name であるため、検索条件 name が先頭にない場合、インデックスは無効になります。最初の状況は完全な値の一致を満たし、2 番目の状況は name と age の 2 つのフィールドを満たし、3 番目の状況は name のみを満たすため、インデックスは name のみを使用します。

3. インデックス列に対して操作 (計算、関数 (自動または手動) の型変換) を実行しないでください。実行すると、インデックスが無効になり、完全なテーブル スキャンに変換されます。

(7) EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='Xiaoming';

ここに画像の説明を挿入

7 番目のケースは、インデックス列が計算または関数操作を実行し、テーブル全体のスキャンが発生するため失敗します。

4. ストレージ エンジンは、インデックス内の範囲条件の右側にある列を使用できません<br /> 上記のテキストの意味がわからないかもしれません。理解するために、以下のクエリ ステートメントを実行してみましょう。

(8) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age < 22 AND pos = 'java';

ここに画像の説明を挿入

上の図から、type が範囲レベルになっていることがわかります。つまり、age<22 以降の pos フィールドのインデックスは無効です。

5. カバーリングインデックス(インデックスのみにアクセスするクエリ(インデックス列とクエリ列は同じ))の使用を試み、select *の使用を減らします。<br /> これは文字通りの意味です。特定のフィールドをクエリする方が、*をクエリするよりも効率的です。比較してみましょう。

(9) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(10) EXPLAIN SELECT name, age, pos FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

ここに画像の説明を挿入

ここに画像の説明を挿入

6. MySQLは不等号(!=または<>)を使用する場合はインデックスを使用できないため、テーブル全体のスキャンが発生します。
(!= または <>) は通常、大量のデータと一致します。インデックスの使用コストがテーブル全体のスキャンよりも大きい場合、MySQL はインデックスの使用を断念し、代わりにテーブル全体のスキャンを選択します。

(11) EXPLAIN SELECT * FROM sys_user WHERE name != 'Xiaoming'

ここに画像の説明を挿入

結果は、インデックスの失敗によりテーブル全体のスキャンが発生したことを示しています。

7. null、nullではない、インデックスは使用できません
is null、is not null は通常、大量のデータと一致します。インデックスの使用コストがフル テーブル スキャンよりも大きい場合、MySQL はインデックスの使用を断念し、代わりにフル テーブル スキャンを選択します。

(12) EXPLAIN SELECT * FROM sys_user WHERE nameがnullでない

ここに画像の説明を挿入

8. がワイルドカード ('%abc...') で始まる場合、MySQL インデックスは無効になり、操作は完全なテーブル スキャンになります。(右側に % を記述すると、インデックスの無効化を回避できます。ビジネスで '%abc...%' が本当に必要な場合は、カバー インデックスを使用してインデックスの無効化を回避できます。)

(13) EXPLAIN SELECT * FROM sys_user WHERE name like '%明%'
(14) EXPLAIN SELECT * FROM sys_user WHERE name like '明%'
(15) EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

ここに画像の説明を挿入

ここに画像の説明を挿入

ここに画像の説明を挿入

上記の結果から、最初のインデックスは失敗します。2 番目は、右側に % を記述するだけでインデックスの失敗を回避できます。3 番目は、ビジネスで '%abc...%' のような SQL が本当に必要な場合に、カバー インデックスを使用してインデックスの失敗の問題を解決できます。

9. 文字列を一重引用符で囲まないとインデックス作成が失敗する

(16) EXPLAIN SELECT * FROM sys_user WHERE name=222;

ここに画像の説明を挿入

検索文字列は一重引用符で囲む必要があるため、上記で使用されている 222 は int 型です。検索時に、MySQL は名前が varchar 型であると判断し、取得のために 222 を '222' に変換します。インデックス列は型変換されるため、インデックスは無効になります。

10. 接続に使用するとインデックスが無効になるため、以下を使用してください。

(16) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' または age = 22; 

ここに画像の説明を挿入

MySQL インデックスが無効になる状況に関するこの記事はこれで終わりです。MySQL インデックスの無効化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の複合インデックスはどのように機能しますか?
  • MySQLデータベースインデックスの詳細な紹介
  • MySQLインデックスの基礎となるデータ構造の詳細
  • MySQL データベースのインデックスとトランザクション
  • MySQL データベースのインデックス順序の詳細な説明
  • MySQL Innodb インデックス メカニズムの詳細な紹介
  • MySQLデータベースインデックスの左端一致原則
  • MySQLデータベースインデックスの詳細な説明
  • MySQLインデックストランザクションの詳細な分析

<<:  CSS3で実装された読み込みアニメーション

>>:  nginx.pid を開く際の失敗と無効の解決策

推薦する

MySQL トランザクションの詳細

目次導入取引の4つの特徴トランザクション分離レベル確認するMVCC現在の読書スナップショット読み取り...

正の整数かどうかを判断するMYSQLカスタム関数の例コード

関数を記述できます。主に正規表現を使用して判断を行います。入力文字が空の場合は、「-」を使用して置き...

Alibaba Cloud OSS アクセス権設定(RAM 権限制御)実装

シナリオmyBuket の static/material/ ディレクトリなど、Alibaba Cl...

JS オブジェクトのコピー (ディープ コピーとシャロー コピー)

目次1. 浅いコピー1. Object.assign(ターゲット、ソース、ソース...) 2. スプ...

CSS3 は 3D キューブの読み込み効果を作成します

簡単な説明これは CSS3 のクールな 3D キューブのプリロード効果です。この特殊効果は、シンプル...

MySQL Installer 8.0.21 インストール チュートリアル (画像とテキスト付き)

1. 理由新しいシステムに MySQL を再インストールする必要があったので、将来詳細を忘れた場合...

Vue はボタンをクリックしてファイルをダウンロードする操作コードを実装します (バックエンド Java)

前回の記事では、ボタンをクリックしてファイルをダウンロードするVueの機能を紹介しました。今日は、ボ...

CSS における要素の表示モード

CSS では、要素タグは、要素の表示モードの違いに応じて、インラインレベル要素とブロックレベル要素の...

Linux サーバー上で複数の SVN リポジトリを構成するプロセスの詳細な説明

1. 指定されたディレクトリにリポジトリを作成し、ディレクトリ全体を保存してください。この記事のサン...

Nginx を使用して https ルートドメイン名への 301 リダイレクトを実装するためのサンプル コード

SEO とセキュリティを考慮して、301 リダイレクトが必要です。以下の一般的な処理には Nginx...

Tudou.com フロントエンドの概要

1. 分業とプロセス<br />Tudou.comでは、プロジェクト開発が中核であり、誰...

MacBook 向け Python 3.7 インストール チュートリアル

MacBookにpython3.7.0をインストールする詳細な手順は、参考までに記録されています。具...

Docker で MySQL 接続と設定ファイルの最大数を変更する

1. MySQLイメージを見つける ドッカーps 2. ミラーmysqlイメージを入力する dock...

JavaScript で Baidu Maps API にアクセスする方法と手順

目次1. Baidu Map API アクセス2. HTML で Baidu Map API を使用...