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 メソッドを自分で変更してみることもできます。