MySQLインデックスの失敗の典型的なケース

MySQLインデックスの失敗の典型的なケース

典型的なケース

次の構造を持つ 2 つのテーブルがあります。

テーブル「student_info」を作成します(
  `id` int(11) NULLではない、
  `name` varchar(10) デフォルト NULL,
  主キー (`id`)、
  キー `idx_name` (`name`)
) エンジン=InnoDB デフォルト文字セット=utf8mb4

テーブル「student_score」を作成します(
  `id` int(11) NULLではない、
  `name` varchar(10) デフォルト NULL,
  `score` int(11) デフォルト NULL,
  主キー (`id`)、
  キー `idx_name` (`name`)
) エンジン=InnoDB デフォルト文字セット=utf8

1 つは情報テーブルで、もう 1 つはスコア テーブルです。スコア テーブルには、情報テーブルよりも 1 つ多くのスコア フィールドがあります。

データを挿入:

mysql> student_info に値 (1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu') を挿入します。
クエリは正常、4 行が影響を受けました (0.01 秒)
記録: 4 重複: 0 警告: 0

mysql> student_score に値 (1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90) を挿入します。
クエリは正常、4 行が影響を受けました (0.01 秒)
記録: 4 重複: 0 警告: 0

mysql> student_info から * を選択します。
+----+----------+
| ID | 名前 |
+----+----------+
| 2 | リシ |
| 3 | 王武 |
| 1 | 張さん |
| 4 | 昭六 |
+----+----------+
セット内の 4 行 (0.00 秒)

mysql> student_score から * を選択します。
+----+----------+-------+
| ID | 名前 | スコア |
+----+----------+-------+
| 1 | 張さん | 60 |
| 2 | リシ | 70 |
| 3 | 王武 | 80 |
| 4 | 昭六 | 90 |
+----+----------+-------+
セット内の 4 行 (0.00 秒)

次のステートメントを実行すると:

mysql> B.* を選択して説明してください
        から
        学生情報 A、学生スコア B
        ここで、A.name=B.name、A.id=1 です。
+----+-------------+----------+-----------+---------+------------------+----------+---------+-----------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+------------------+----------+---------+-----------+------------+-------------+
| 1 | SIMPLE | A | NULL | const | PRIMARY、idx_name | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | B | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | where の使用 |
+----+-------------+----------+-----------+---------+------------------+----------+---------+-----------+------------+-------------+
セットに 2 行、警告 1 件 (0.00 秒)

B.name にインデックスがあるのに、実行プランでテーブル B を 2 回目に選択するときに、インデックスが使用されず、代わりに完全なテーブル スキャンが使用されるのはなぜですか? ? ?

分析:

この SQL は次の 3 つのステップを実行します。

1. まずA.id=1のレコードをフィルタリングし、主キーインデックスを使用して、LAの1行のみをスキャンします。

2. LA 行から「zhangsan」という名前の値を見つけます。

3. LA.name の値に従ってテーブル B を検索し、同じ値 zhangsan を見つけて返します。

このうち、3 番目のステップは次のように簡略化できます。

name=$LA.name の student_score から * を選択

ここで、LA はテーブル A 情報の内容であり、テーブル情報の文字セットは utf8mb4 であり、テーブル B スコアの文字セットは utf8 です。

それで

実行すると、utf8 型の左側の値と utf8mb4 型の右側の値を比較するのと同じです。utf8mb4 には utf8 型が完全に含まれており (長いバイトに短いバイトが含まれている)、MySQL は utf8 を utf8mb4 に変換します (主にデータの切り捨てを防ぐため、逆変換ではありません)。

したがって、以下を実行するのと同等です。

選択 * 学生スコアから CONVERT(name USING utf8mb4)=$LA.name

ご存知のように、インデックス フィールドで暗黙的な型変換が使用されると、インデックスは無効になり、MySQL オプティマイザは完全なテーブル スキャンを使用して SQL を実行します。

この問題を解決するには、2 つの方法があります。

a. 文字セットを変更します。

b. SQL ステートメントを変更します。

文字セットを変更する方法は次のとおりです。

mysql> alter table student_score 名前をvarchar(10)文字セットutf8mb4に変更します。
クエリは正常、4 行が影響を受けました (0.03 秒)
記録: 4 重複: 0 警告: 0

mysql> explain select B.* from student_info A,student_score B where A.name=B.name and A.id=1;
+----+-------------+-----------+----------+--------+------------------+---------+-------+-------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+-----------+----------+--------+------------------+---------+-------+-------+------+------+
| 1 | SIMPLE | A | NULL | const | PRIMARY、idx_name | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | B | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+----------+--------+------------------+---------+-------+-------+------+------+
セットに 2 行、警告 1 件 (0.01 秒)

SQL メソッドを自分で変更してみることもできます。

付録: 一般的なインデックス障害の状況

1. 列に対して関数を使用する場合、列のインデックスは有効になりません。

2. 列に対して操作 (+、-、​​、/、! など) を実行する場合、列のインデックスは有効になりません。

3. 場合によっては、列インデックスに対して LIKE 操作が機能しないことがあります。

4. 逆操作を使用すると、列のインデックスが機能しない場合があります。

5. WHERE で OR を使用する場合、1 つの列にインデックスがないと、他の列のインデックスは機能しません。

6. 暗黙的な変換によりインデックスが無効化されます。これは真剣に受け止める必要があります。開発でよくある間違いでもあります。

7. not in や not existing などの文を使用する場合。

8. 変数が時間変数であり、テーブルのフィールドが日付変数である場合、またはその逆の場合。

9. B ツリー インデックスが null の場合、失敗しません。is not null を使用すると、失敗します。ビットマップ インデックスが null の場合と、is not null の場合は、どちらも失敗します。

10. インデックス列が作成されている限り、結合されたインデックスは null ではなくなり、(順序は特に決まっていません) 無効になります。

上記は、MySQL インデックス障害の典型的なケースの詳細です。MySQL インデックス障害の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL テーブルを返すとインデックスが無効になるケースの説明
  • MySQLのあいまいクエリインデックスの失敗の問題を解決するいくつかの方法
  • MySQLインデックスが失敗するいくつかの状況の分析
  • MySQLインデックスが失敗するいくつかの状況の詳細な分析
  • MySQL インデックスが失敗するいくつかの状況の概要
  • MySQL インデックス障害の 5 つの状況の分析
  • Mysql インデックスが失敗するいくつかの状況の分析
  • MySQL インデックス障害の上位 10 の問題の概要

<<:  親ページの更新を制御するために HTML で iframe を実装するためのアイデアとコード

>>:  Linuxカーネルとデバイスツリーのコンパイルと書き込みを分析する

推薦する

Linux で fdisk を使用してディスクをパーティション分割する方法

Linux パーティションでよく使用されるコマンド: fdisk、MBR パーティション テーブルの...

ウェブデザインのためのロイヤルブルーのカラーマッチング入門

古典的な色の組み合わせは力と権威を伝え、強いロイヤルブルーはあらゆる古典的な色の組み合わせの中心的な...

JavaScript キャンバスで動的な点と線の効果を実現

この記事では、動的な点と線の効果を実現するためのJavaScriptキャンバスの具体的なコードを参考...

MySQLデータの重複チェックと重複排除の実装ステートメント

テーブル user があり、フィールドは id、nick_name、password、email、p...

MySQL をインストールするときに初期パスワードを忘れた場合のシンプルで効果的な解決策

MySQL をインストールすると初期パスワードが与えられますが、この初期パスワードは大文字と小文字の...

WeChatアプレットリクエストの前処理方法の詳細な説明

質問一部のページでは、onload でデータを要求してからビューをレンダリングするため、ミニプログラ...

Linux IO 多重化 epoll ネットワーク プログラミング

序文この章では、基本的な Linux 関数と epoll 呼び出しを使用して、Linux 上で実行で...

CocosCreator でカメラトラッキングに cc.follow を使用する方法

Cocos Creator バージョン: 2.3.4デモのダウンロード: https://files...

MacOS に MySQL 8.0 をインストールして MySQL にログインする方法

公式チュートリアルに従って、インストール パッケージをダウンロードし、[インストール] をクリックし...

Firefox で Flash を再生するためのオブジェクトとパラメータの書き方

コードをコピーコードは次のとおりです。 <object classid="clsid...

VMware での Ubuntu 16.04 イメージの完全インストール チュートリアル

この記事では、VMware 12でのUbuntu 16.04イメージのインストールチュートリアルを参...

VMware pro15 インストール macOS10.13 詳細インストール図(画像とテキスト)

編集者は最近、macOS システムを使い始めたかったので、VMware に macOS イメージ シ...

MySQLは既存のコンテンツを保持し、後でコンテンツを追加します

このコマンドは、データ テーブル ff_vod を変更し、vod_url フィールドの内容の後に 9...

dockerコンテナにviコマンドをインストールする簡単な操作

docker コンテナを使用する場合、vim がインストールされていないことがあり、vim コマンド...

MySQLはカスタム関数を使用して親IDまたは子IDを再帰的に照会します

背景: MySQL では、レベルに制限がある場合、たとえば、ツリーの最大深度を事前に決定できる場合、...