Name: MySQL EXPLAINを使用してクエリを最適化する (Use MySQL EXPLAIN for Query Optimization)
Uploaded: 2025-03-22T13:32:37.000Z
Duration: 17 min 19 s
Description: VoiceTubeの動画で発音を聞きながら英語表現を覚えよう！学べる英語：

In this tutorial, we're going to see why MySQL creates a query plan before each query request.

このチュートリアルでは、MySQLが各クエリ要求の前にクエリプランを作成する理由を説明します。

Then, we're going to use a series of examples to show how the EXPLAIN output can be used to identify where MySQL spends time on your query and why, and we'll learn which are the important pieces of information to look for in order to spot performance issues.

次に、EXPLAIN 出力を使用して、MySQL がクエリのどこで時間を費やしているのか、またその理由を特定する方法を、一連の例を使用して説明します。

Once we have a candidate query for optimization, we need to analyze why is it slow or why it impacts the system so much.

最適化の候補となるクエリが決まったら、そのクエリがなぜ遅いのか、なぜシステムに大きな影響を与えているのかを分析する必要がある。

The main tool to do that is the EXPLAIN statement, which provides information about the query plan chosen by the optimizer.

そのための主なツールがEXPLAIN文であり、オプティマイザが選択したクエリプランに関する情報を提供する。

The optimizer has to make a few decisions before executing each query.

オプティマイザは、各クエリを実行する前にいくつかの決定を下さなければならない。

For example, what is cheaper from a response time perspective?

例えば、レスポンスタイムの観点からは何が安いのか？

Fetch the data directly from a table, or go to an index and stop here because all the columns required by the client are in the index, or get the location of the records from the index and go to the table to get the actual data.

テーブルから直接データを取得するか、インデックスに移動し、クライアントが必要とするカラムはすべてインデックスにあるのでここで止めるか、インデックスからレコードの場所を取得し、実際のデータを取得するためにテーブルに移動する。

The first method, fetching data directly from the table, is called fullscan.

最初の方法は、テーブルから直接データを取得するもので、フルスキャンと呼ばれる。

This is normally the most expensive because all rows must be fetched from the table and checked against a condition, yet this method works best for small tables.

これは、すべての行をテーブルから取得し、条件と照合しなければならないため、通常は最もコストがかかる。

In the second set of options, we access an index.

2つ目のオプションでは、インデックスにアクセスする。

If the index has all the necessary data and there is no need to access the table, we have what is called a covering index.

インデックスが必要なデータをすべて持っていて、テーブルにアクセスする必要がない場合は、いわゆるカバーリングインデックスとなる。

However, this is less often the case, so the index is used to filter out rows and then access those rows from the table.

しかし、このようなケースはあまりないため、インデックスを使用して行をフィルタリングし、テーブルからその行にアクセスする。

Usually, this is the cheapest way to access a table.

通常、これがテーブルにアクセスする最も安い方法である。

Still, if the client selects a large number of rows, this may not be valid anymore.

それでも、クライアントが大量の行を選択した場合、これはもはや有効ではないかもしれない。

Therefore, the optimizer has to make a lot of decisions based on particular database statistics before the query is executed.

そのため、オプティマイザはクエリが実行される前に、特定のデータベース統計に基づいて多くの決定を下さなければならない。

As a result, our goal will be to observe what the optimizer thinks is the most expensive subtask so that we could eliminate or enhance that part.

その結果、私たちの目標は、オプティマイザが最もコストがかかると考えるサブタスクを観察し、その部分を削除または強化できるようにすることだ。

If you have a slow query, the first thing to try is running it with EXPLAIN.

遅いクエリがある場合、まずEXPLAINで実行してみることです。

This will show the query plan, in other words, the list of things expected to happen when the query is executed.

これは、クエリプラン、言い換えれば、クエリが実行されたときに起こると予想されることのリストを表示します。

If you instead use EXPLAIN ANALYZE before the statement, you'll get both the estimation of what the planner expected, along with what actually happened when the query ran.

代わりに文の前にEXPLAIN ANALYZEを使用すると、プランナが予想したものと、実際に問い合わせが実行された時に起こったことの両方を得ることができます。

This query is not executed, so it won't delete all the rows.

このクエリは実行されないので、すべての行は削除されない。

Therefore, it's safe to obtain the query plan.

したがって、クエリ・プランを取得することは安全である。

To actually execute the query, we can use EXPLAIN ANALYZE, as we'll see later in more detail.

実際にクエリを実行するには、後で詳しく説明しますが、EXPLAIN ANALYZEを使用します。

This is not only going to show the query plan for deleting those rows, it is actually going to delete them.

これは、行を削除するクエリプランを表示するだけでなく、実際に行を削除します。

Usually, it's more difficult to compare the timings of operations when doing inserts, updates, or deletes using EXPLAIN ANALYZE.

通常、EXPLAIN ANALYZEを使用して挿入、更新、または削除を行う場合、操作のタイミングを比較することはより困難です。

This is because the underlying data will change while executing the same queries.

同じクエリーを実行しても、基礎となるデータは変化するからだ。

Optionally, we can add the FORMAT option to specify whether we want the results returned in traditional table format, JSON, or tree-style format.

オプションで、FORMATオプションを追加して、結果を従来のテーブル形式、JSON形式、ツリー形式のどれで返したいかを指定できる。

Keep in mind that each format will show more or less information about the query plan.

各フォーマットは、クエリプランに関する多かれ少なかれ情報を表示することに留意してください。

For instance, the JSON format is the most verbose of all.

例えば、JSONフォーマットは最も冗長だ。

Let's see some examples to dig deeper into the query plan.

クエリプランをより深く掘り下げるために、いくつかの例を見てみましょう。

We will use the sample database world link in the description, and for the first example we will execute a simple SELECT on the city table with a condition on a non-indexed column called name.

最初の例では、nameというインデックスを持たないカラムを条件として、cityテーブルに対して単純なSELECTを実行します。

Since there is no index that can be used, it will require a full table scan to evaluate the query.

使用できるインデックスがないため、クエリを評価するためにはテーブルのフルスキャンが必要になります。

The table access types show whether a query accesses the table using an index, scan, and the similar.

テーブル・アクセス・タイプは、クエリがインデックス、スキャン、および同様のものを使用してテーブルにアクセスするかどうかを示します。

Since the costs associated with each type fluctuate greatly, it is also one of the most important values to look for in the EXPLAIN output to determine which part of the query to work on to improve the performance.

各タイプに関連するコストは大きく変動するため、パフォーマンスを向上させるためにクエリのどの部分に取り組むべきかを決定するために、EXPLAIN出力で探すべき最も重要な値の1つでもあります。

The output has the access type set to ALL, which is the most basic access type, because it scans all rows for the table.

これは最も基本的なアクセスタイプで、テーブルのすべての行をスキャンするからである。

It is also the most expensive one, and for this reason, this type is written in ALL uppercase.

このため、このタイプはすべて大文字で表記される。

It's estimated that 4046 rows will be examined, and for each row a WHERE clause will be applied.

4046行が検査されると推定され、各行に対してWHERE句が適用される。

It's expected that 10% of the rows examined will match the WHERE clause.

検査される行の10%がWHERE句にマッチすると予想される。

Here, the optimizer uses default values to estimate the filtering effect of various conditions, so we can't really use this filtering value to estimate whether an index is useful or not.

ここで、オプティマイザは様々な条件のフィルタリング効果を推定するためにデフォルト値を使用するため、インデックスが有用かどうかを推定するためにこのフィルタリング値を実際に使用することはできない。

This is the traditional format, however, it doesn't show the relationship between the executed subtasks, so it's more difficult to have an overview of the query plan.

これは伝統的な形式ですが、実行されたサブタスク間の関係が表示されないため、クエリプランの概要を把握するのが難しくなります。

Which format is preferred depends on your needs.

どの形式がいいかは、あなたのニーズ次第だ。

For example, the traditional format it's easier to use to see the indexes used and other basic information about the query plan, while the JSON format provides much more details.

例えば、従来のフォーマットでは、使用されているインデックスやクエリプランに関する他の基本的な情報を見るのに使いやすいが、JSONフォーマットでは、より詳細な情報を得ることができる。

The TREE style is the newest format, and is the format we're going to use for the next examples.

TREEスタイルは最新のフォーマットで、次の例で使用するフォーマットだ。

The TREE format focuses on describing how the query is executed, in terms of the relationship between the parts of the query, and also the order in which these parts are executed.

TREEフォーマットは、クエリの実行方法を記述することに重点を置き、クエリのパート間の関係や、これらのパートが実行される順序を記述します。

In this case, EXPLAIN OUTPUT is organized into a series of nodes.

この場合、EXPLAIN OUTPUTは一連のノードに編成される。

At the lowest level, there are nodes that scan tables or search indexes.

最も低いレベルでは、テーブルをスキャンしたりインデックスを検索するノードがある。

Higher level nodes take the results from the lower level nodes and operate on them.

上位のノードは下位のノードから結果を受け取り、それを操作する。

Here, it can be easier to understand the execution by reading the output from the INSIDE OUT.

ここで、INSIDE OUTからの出力を読むことで、実行を理解しやすくなる。

The TREE format is also the default format for EXPLAIN ANALYZE statement, which is as new as MySQL 8.0.18.

TREE フォーマットは、EXPLAIN ANALYZE ステートメントのデフォルト フォーマットでもあります。

This TREE format output gives us a good overview of how the query is executed.

このTREE形式の出力は、クエリがどのように実行されるかの概要を教えてくれる。

First, there is a table scan on the city table, and then a filter is applied for the name column.

まず、市区町村テーブルのテーブルスキャンが行われ、次に名前カラムにフィルターが適用される。

Here, we also have an estimation cost for an expected 4000 rows.

ここでは、4000行を想定した場合の推定コストも示している。

Please note that this estimation cost is represented in some internal MySQL units.

この見積もりコストは、MySQL の内部単位で表されていることに注意してください。

For example, reading a row from the disk can have an associated cost of 2, while reading the same row from the memory will have a cost of 1.

例えば、ディスクから行を読み出す場合、それに関連するコストは2であるが、メモリーから同じ行を読み出す場合のコストは1である。

Moving to the actual statistics, we see that the first row was read in about 4 milliseconds, and all other rows were read in roughly 5 milliseconds.

実際の統計に目を移すと、最初の行は約4ミリ秒で読み込まれ、他の行はすべて約5ミリ秒で読み込まれている。

There was a single loop for this query, because there was no join-close involved.

このクエリーのループは1回だけである。

In this case, the estimate was pretty accurate regarding the numbers of returned rows.

この場合、返された行の数に関して、見積もりはかなり正確だった。

Then, these rows are passed to the second phase for filtering, where we see a slight increase in the actual time of execution.

その後、これらの行はフィルタリングのために第2フェーズに渡されるが、そこでは実際の実行時間がわずかに増加しているのがわかる。

The second example is similar to the first, except that the filter condition is changed to use the country code column, which has a secondary, non-unique index.

つ目の例は、フィルター条件が、セカンダリで一意でないインデックスを持つ国コード・カラムを使用するように変更されていることを除けば、最初の例と似ている。

This should make it cheaper to access the matching rows.

これにより、一致する行へのアクセスがより安くなるはずである。

For example, let's retrieve all French cities.

例えば、フランスの全都市を検索してみよう。

Select ALL from city, where country code is equal to FRA.

国番号がFRAに等しい都市からALLを選択する。

This time, only a lookup on the country code index can be used for the query.

今回は、国コード・インデックスのルックアップのみがクエリーに使用できる。

It's estimated that 40 rows will be accessed, which is exactly as InnoDB responds when asked how many rows will match.

アクセスされる行数は40行と見積もられており、InnoDBがマッチする行数を聞かれた時の答えと同じである。

This is because an index will also bring some statistics with it.

というのも、インデックスには統計も含まれているからだ。

For the next example, we'll use the country language table, which has a primary key with two columns, country code and language.

次の例では、国コードと言語の2つのカラムを持つ主キーを持つ国別言語テーブルを使用します。

Imagine that we want to find all languages spoken in a single country.

ある国で話されているすべての言語を見つけたいとする。

In that case, we'll need to filter on the country code, but not on language.

その場合、言語ではなく国コードでフィルターをかける必要がある。

A query that can be used to find all languages spoken in China is Select ALL from country language, where country code is CHN.

中国で話されているすべての言語を見つけるのに使えるクエリは、Select ALL from country languageで、国コードはCHNである。

The index on the primary key can still be used to perform the filtering.

主キーのインデックスを使用してフィルタリングを行うこともできる。

The EXPLAIN output shows that with a primary tag and also the column of the index that was used.

EXPLAIN出力は、プライマリタグと使用されたインデックスのカラムを示します。

As always, only the left part of the index can be used for filtering.

いつものように、フィルタリングに使えるのはインデックスの左側部分だけである。

For the last example, we'll use a mix of various features and with multiple query blocks.

最後の例では、さまざまな機能をミックスし、複数のクエリー・ブロックを使用する。

To save time, I'll just copy-paste the query, but you can find it in the description.

時間を節約するため、クエリをコピーペーストするだけだが、説明文の中にある。

This query will select the top 10 smallest countries by area, and then for these countries, find the largest cities by population.

このクエリーは、面積の小さい国トップ10を選び、その国について人口最大の都市を見つける。

The query plan starts out with a subquery that uses the country table to find the 10 smallest countries by area.

クエリプランは、国テーブルを使用して、面積で最小の10カ国を見つけるサブクエリから始まります。

Here we can see how the CO table is a materialized subquery created by first doing a table scan on the country table, then applying a filter for the continent, then sorting based on the surface area, and then limiting the result to 10 rows.

ここでは、COテーブルがマテリアライズされたサブクエリであり、最初に国テーブルに対してテーブルスキャンを行い、次に大陸のフィルタを適用し、表面積に基づいてソートし、結果を10行に制限していることがわかります。

Once the derived table has been constructed, it can be used as the first table for the join with the CI table.

派生テーブルが構築されると、CIテーブルとの結合の最初のテーブルとして使用することができます。

The second part of the nested loop is simpler, as it just consists of an index lookup on the CI table using the country code index.

ネストされたループの2番目の部分は、国コード・インデックスを使ってCIテーブルのインデックス・ルックアップを行うだけなので、よりシンプルである。

Here, the estimated cost was about 4 for an expected 17 rows.

ここでは、想定される17列に対して推定コストは約4。

There were 10 loops, one for each of the 10 countries, and each fetching an average of 2 rows for a total of 20 rows.

ループは10回あり、10カ国それぞれに1回ずつ、平均2行ずつ、合計20行をフェッチした。

So, in this case, the estimate was not very accurate, because the query exclusively picks small countries.

というのも、このクエリーは小国だけを選んでいるからだ。

It is estimated that the join will return 174 rows, which comes from the 10th rows in the derived table, multiplied with the estimated 70 rows per index lookup in the CI table.

この結合は174行を返すと推定されます。これは、派生テーブルの10行目とCIテーブルのインデックス検索あたりの推定70行を掛け合わせたものです。

When the nested loop has been resolved using the inner join, the result is streamed, that is, not materialized, for the sorting, and the first 5 rows are returned.

入れ子ループが内側joinを使用して解決されると、結果はソートのためにマテリアライズされずにストリームされ、最初の5行が返されます。

The total cost of the query is estimated to be around 4 milliseconds.

クエリーの総コストは約4ミリ秒と推定される。

So, what can we do with this information?

では、この情報を使って何ができるのか？

When looking at a plan, there are two questions that you might want to ask yourself.

プランを見るとき、自問したいことが2つある。

Is the runtime shown by the EXPLAIN ANALYZE clause justified for the given query?

EXPLAIN ANALYZE句が示す実行時間は、指定されたクエリに対して正当化されますか？

If the query is slow, where does the runtime jump?

クエリが遅い場合、ランタイムはどこでジャンプするのか？

Looking for jumps in the execution time of the query will reveal what is really going on.

クエリの実行時間のジャンプを探すことで、実際に何が起こっているかが明らかになる。

Some general advice, it's not possible here, because there are too many things that can cause issues, but here are some tips.

一般的なアドバイスは、問題を引き起こす可能性のあるものが多すぎるため、ここでは不可能だが、いくつかのヒントを紹介しよう。

What we should pay attention to is whether the estimates and the actual costs differ from each other.

注目すべきは、見積もりと実際のコストが異なるかどうかである。

If there is a big difference, the optimizer will make poor decisions.

この差が大きいと、オプティマイザーは誤った判断を下してしまう。

Possible causes for this difference could be that either the optimizer doesn't have up-to-date statistics, or the optimizer estimates are off for some reason.

この違いの原因として考えられるのは、オプティマイザーが最新の統計情報を持っていないか、オプティマイザーの推定値が何らかの理由でずれていることだ。

Running an ANALYZE TABLE clause is definitely a good thing to start with.

ANALYZE TABLE句を実行することは、まず間違いなく良いことだ。

This will collect statistics for the table.

これはテーブルの統計情報を収集する。

However, under normal circumstances, you don't need to analyze the table, as this happens automatically when certain thresholds are reached.

しかし、通常の状況では、テーブルを分析する必要はない。ある閾値に達すると、自動的に分析が行われるからだ。

For example, the index cares for himself in everyday situation, but on high-traffic tables, for example, with lots of deletes, the index might get confused and needs more time to select the right rows.

例えば、日常的な状況ではインデックスが気にかけてくれるが、削除が多いなどトラフィックの多いテーブルでは、インデックスが混乱して正しい行を選択するのに時間がかかるかもしれない。

In this case, we might run ANALYZE TABLE when no other traffic is made.

この場合、他のトラフィックがないときにANALYZE TABLEを実行することがある。

字幕リスト動画再生

MySQL EXPLAINを使用してクエリを最適化する (Use MySQL EXPLAIN for Query Optimization)

sort

access

expression

description