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カーネルとデバイスツリーのコンパイルと書き込みを分析する

推薦する

Vue で変数式セレクターを実装する方法

目次HTML構造の定義入力タグのバインディング属性入力タグはキーダウンイベントをリッスンしますli ...

VUE+CanvasはシンプルなGobangゲームの全プロセスを実現します

序文レイアウトの点では、Gobang はランダムな動きを目的とするゲームよりも実装がはるかに簡単で、...

MySQL でタイムスタンプを日付に変換する例

序文職場で次のような状況に遭遇しました。ログ システムのテーブルでは、時間フィールドには日付データで...

WeChatアプレットは日付と時刻に基づいた並べ替え機能を実装

最近、小さなプログラム プロジェクトを引き継いだのですが、リストを日付と時刻で並べ替えるという要件が...

JavaScript の toLocaleString() での時間フォーマットに関する新しいアイデア

目次1. 時刻表示に関する従来の考え方2. 時刻の書式設定 toLocaleString() Obj...

HTML 中国語文字エンコード標準の概要

HTML では、Web ページで使用されるエンコーディングを指定する必要があります。一般的な指定方法...

任意の長さの配列を作成または埋めるための JS のヒントの要約

目次序文直接充填方式for ループの push() メソッド配列コンストラクタメソッド配列コンストラ...

Linux bash: ./xxx: バイナリ ファイルを実行できません エラー

今日、Ubuntu 用の小さなツールを顧客に送りましたが、ユーザーはそれを受け取った後、実行できませ...

なぜ IE6 が最も多くの人に使用されているのでしょうか?

まず第一に、私はウェブデザイナーです。具体的には、私は XHTML フロントエンド デザイナーです。...

Nginx 構成 PC サイトとモバイル サイトの分離によるリダイレクトの実現

PCサイトとモバイルサイトの分離設定にはnginxを使います。私のPCサイトとモバイルサイトは、SE...

MySQL ピボットテーブルについての簡単な説明

次のような製品部品表があります。一部 部品ID 部品タイプ 製品ID ---------------...

面接の質問: 3 行 3 列のレイアウト、表は結合され、ネストされた表は許可されません

面接の質問で、3 行 3 列のレイアウトが求められます。1 行目の 2 番目の列と 2 行目の 2 ...

CentOS 8.1 で LEMP (Linux+Nginx+MySQL+PHP) 環境を構築する (チュートリアルの詳細)

目次ステップ1: CentOS 8でパッケージを更新するステップ2: CentOS 8にNginx ...

WeChatアプレットタブの左右スライドスイッチ機能実装コード

効果画像: 1. はじめに独自のアプレットでこのような機能を実装する必要がある1. 核となる考え方ス...

CentOS7環境にMySQL5.5データベースをインストールする

目次1. 現在のシステムにMySQLがインストールされているかどうかを確認する2. インストールされ...