SQLのパフォーマンスを分析する方法

#この記事では、Explain を使用して SQL を分析する方法を紹介します。

実際、インターネット上には Explain の使用法を詳細に紹介する記事がたくさんあります。この記事では、例と原則を組み合わせて、理解を深めていただけるように努めています。信じてください。真剣に考えてください。読んだ後に特別な利益があります。

explain は「説明する」と訳されます。これは mysql では実行プランと呼ばれます。つまり、このコマンドを使用すると、オプティマイザによる分析後に mysql が SQL の実行をどのように決定するかを確認できます。

オプティマイザーについて言えば、もう 1 つ言わせてください。MySQL には強力なオプティマイザーが組み込まれています。オプティマイザーの主なタスクは、作成した SQL を最適化し、可能な限り低コストで実行することです。たとえば、スキャンする行を減らし、並べ替えを避けるなどです。 SQL ステートメントを実行するときに何を経験しましたか?前回の記事でオプティマイザについて紹介しました。

あなたは、通常どのようなときに Explain を使用するのかと疑問に思われるかもしれませんが、ほとんどの場合、クエリ効率が比較的遅い SQL は、Explain 分析を使用するために mysql のスロー クエリ ログから抽出されます。 , インデックスを追加し、追加したインデックスがヒットするかどうかを Explain を使用して分析するなど、ビジネス開発中にニーズが満たされる場合は、Explain を使用していずれかを選択する必要がある場合があります。

では、Explain を使用するにはどうすればよいでしょうか? それは非常に簡単で、以下に示すように SQL の前に Explain を追加するだけです。

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

ご覧のとおり、explain は約 10 個のフィールドを返します。バージョンが異なると返されるフィールドは若干異なります。各フィールドは特定の意味を表します。この記事では各フィールドの詳細については説明しません。導入には多くの内容が含まれており、覚えるのは簡単ではないと思います。最初にいくつかの重要なフィールドを理解することをお勧めします。

フィールドの種類、キー、行、および追加の方が重要だと思います。これらのフィールドの意味をより深く理解できるように、具体的な例を使用します。

まず第一に、これらのフィールドの文字通りの意味を簡単に紹介する必要があります。

type は、MySQL がデータにアクセスする方法を表します。一般的なものには、フル テーブル スキャン (all)、インデックス トラバーサル (index)、間隔クエリ (範囲)、定数または同等のクエリ (ref、eq_ref)、主キーなどが含まれます。テーブル (システム) にレコードが 1 つだけある場合のクエリ (const)。以下は、最高から最低までの効率のランキングです。

system > const > eq_ref > ref > range > index > all

key は、クエリ プロセスで実際に使用されるインデックス名を表します。

rows は、クエリ プロセス中にスキャンする必要がある行数を表します。このデータは必ずしも正確ではなく、MySQL のサンプリング統計のデータです。

Extra は追加情報を表し、通常、インデックスが使用されているかどうか、並べ替えが必要かどうか、一時テーブルが使用されるかどうかなどを示します。

さて、サンプル分析を正式に始めましょう。

前回の記事で作成したストレージ エンジンを引き続き使用して、テスト テーブルを作成します。ここに 10 w 個のテスト データを挿入します。テーブルの構造は次のとおりです:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

次に、次のクエリ ステートメントでは、これに注意してください。テーブルには現在主キー インデックスが 1 つだけあり、通常のインデックスはまだ作成されていません。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

タイプの値は ALL で、テーブル全体がスキャンされたことを示します。行フィールドには 100,332 のエントリが表示されることに注意してください。実際、データは合計 100,000 個しかないため、このフィールドはmysql の単なる推定値であり、必ずしも正確であるとは限りません。このフルテーブルスキャンの効率は非常に低いため、最適化する必要があります。

次に、フィールド a とフィールド b にそれぞれ通常のインデックスを追加し、インデックスを追加した後のいくつかの SQL ステートメントを確認します。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

上記の SQL は少しわかりにくいと思いますか? type は実際にフィールド a にインデックスが追加されたことを示しており、 possible_keys も a_index が利用可能であることを示していますが、キーは null を示しており、mysql が実際にインデックスを使用していないのですが、なぜですか?

これは、* を選択した場合、b フィールドを見つけるために主キー インデックスに戻る必要があるためです。このプロセスはテーブル リターンと呼ばれます。このステートメントは、条件を満たす 90,000 個のデータをフィルターで除外します。つまり、これらの 9,000 個のデータはすべてテーブルを返す操作が必要であり、フル テーブル スキャンには 100,000 個のデータしか含まれていないため、MySQL オプティマイザの観点からは、直接のフル テーブルほど優れたものではありません。スキャンを実行すると、少なくともテーブルを返すプロセスが不要になります。

もちろん、これは、テーブルを返す操作がある限りインデックスにヒットしないという意味ではありません。インデックスを使用するかどうかの鍵は、mysql がどのクエリの方がコストが低いと考えるかによって決まります。上記の SQL の where 条件を少し変更してみましょう。

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

今回のタイプ値は range で、キーは a_index です。これは、インデックスがヒットすることを意味します。この SQL 条件を満たすデータは 1000 個しかないため、これは適切な選択です。MySQLテーブルリターンはテーブル全体のスキャンよりもコストが低いので、mysql は実際には非常に賢い人です。

また、[Extra] フィールドの値が [インデックス条件を使用] であることもわかります。これは、インデックスは使用されていますが、テーブルを返す必要があることを意味します。次のステートメントを見てください。

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

这个 Extra 中的值为 Using where; Using index ，表示查询用到了索引，且要查询的字段在索引中就能拿到，不需要回表，显然这种效率比上面的要高，所以不要轻易写 select * ，只查询业务需要的字段即可，这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 Extra 中返回了一个 Using filesort，意味着需要排序，这种是需要重点优化的的，也就是说查到数据后，还需要 mysql 在内存中对其进行排序，你要知道索引本身就是有序的，所以一般来讲要尽量利用索引的有序性，比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | a_index | 5       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

我们再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | ab_index | 5       | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

这条 sql 刚刚在上面也有讲到过，在没有创建复合索引的时候，是走的全表扫描，现在其实是利用了覆盖索引，同样是免去了回表过程，即在 （ab_index） 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划，也提到了一些常见的索引优化，事实上还有更多的可能性，你也可以自己去写一个 sql ，然后使用 explain 分析，看看有哪些是可以被优化的。

这篇文章我断断续续写了有三四天了，本来准备了更多的例子，但每次都是写了一部分，思路也打乱了，好了，有问题欢迎在下面留言交流，文章对你有帮助，点个赞表示鼓励支持。

更多MySQL相关技术文章，请访问MySQL教程栏目进行学习！

以上がSQLのパフォーマンスを分析する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。