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を使用すると、プランナが予想したものと、実際に問い合わせが実行された時に起こったことの両方を得ることができます。

Consider the following statement.

次の文を考えてみよう。

EXPLAIN DELETE FROM city.

EXPLAIN DELETE FROM city.

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を実行することがある。

So, it's definitely worth considering other options that are causing the bad estimates.

だから、悪い見積もりの原因となっている他の選択肢を検討する価値は間違いなくある。

Let's see such an example.

その例を見てみよう。

We'll switch to a clean database, and we'll create a simple table.

クリーンなデータベースに切り替え、シンプルなテーブルを作成します。

This table has an autoincrement primary key and two integer values.

このテーブルはオートインクリメントの主キーと2つの整数値を持つ。

Then, we add an index on the first integer value column, alter table, test estimates, and add an index.

次に、最初の整数値カラムにインデックスを追加し、テーブルを変更し、見積りをテストし、インデックスを追加する。

Then, we'll load some data using a simple Python script in the MySQL CH tool.

次に、MySQL CHツールで簡単なPythonスクリプトを使ってデータをロードする。

This script will execute the insert statement 100,000 times.

このスクリプトはinsert文を100,000回実行する。

The integer value is also an autoincremented value from 1 to 100,000.

整数値も1～100,000のオートインクリメント値である。

After loading the data, we make sure that optimizer statistics are created.

データをロードした後、オプティマイザーの統計が作成されていることを確認する。

And now, let's execute a query and check the estimates.

では、クエリーを実行し、見積もりをチェックしてみよう。

Explain analyze, select all from test estimates, where 2 multiplied with value is less than 3.

分析について説明し、テスト見積もりから、2の乗算値が3より小さいものをすべて選択する。

In many cases, MySQL might not be able to estimate the where clause properly, even though it has an index.

多くの場合、MySQLはインデックスがあるにもかかわらず、where句を正しく推定できないことがある。

This is because it only has statistics on columns and not on expression.

これはカラムの統計のみで、式の統計は持っていないからである。

What we see here is a major overestimation of the data returned from the where clause.

ここで見られるのは、where句から返されるデータの大幅な過大評価である。

Because MySQL thinks that it will need to read the whole table, it considers that the index is overhead, so it goes ahead and scans the whole table, ignoring the index.

MySQLはテーブル全体を読み込む必要があると考えているため、インデックスはオーバーヘッドであると考え、インデックスを無視してテーブル全体をスキャンします。

In this case, trying to avoid the expression on database level is the best choice.

この場合、データベース・レベルでの表現を避けることが最良の選択である。

However, if that is not possible, adding an index will fix statistics and will also ensure significantly better performance.

しかし、それが不可能な場合は、インデックスを追加することで統計が修正され、パフォーマンスも大幅に向上する。

So, let's add an index on the expression.

では、式にインデックスを追加してみよう。

So, let's add an index on the expression.

では、式にインデックスを追加してみよう。

The key takeaway from this example is to take a look at the row counters for estimates versus actual execution.

この例から得られる重要なことは、推定と実際の実行の行カウンターを見ることである。

A large difference, that is, a couple of orders of magnitude or more between the estimated number of rows and the actual numbers of rows is a sign that you need to look closer at it.

推定行数と実際の行数に大きな差、つまり数桁以上の差がある場合は、よく調べる必要があるというサインである。

The optimizer chooses its plan based on the estimates, but looking at the actual execution may tell you that another plan would have been better.

オプティマイザは推定値に基づいてプランを選択するが、実際の実行を見れば、別のプランの方が良かったかもしれないとわかるかもしれない。

If you run a query twice, the second will likely be much faster simply because of caching, regardless of whether the plan was better or worse.

クエリを2回実行した場合、プランの良し悪しにかかわらず、キャッシュのおかげで2回目の方がはるかに速くなる可能性が高い。

This represents hot cache behavior, meaning that the data needed for the query was already in the database or the operating system caches.

これは、クエリに必要なデータがすでにデータベースまたはオペレーティング・システムのキャッシュにあったことを意味する、ホット・キャッシュの動作を表している。

Basically, it was left in the cache from where the data was loaded in the first place.

基本的には、最初にデータがロードされた場所からキャッシュに残された。

Whether your cache is hot or cold is a thing to be very careful of when analyzing queries.

キャッシュがホットかコールドかは、クエリを分析する際に非常に注意すべき点である。

One way to solve this problem is to repeatedly run the query and check if it takes the same amount of time each run.

この問題を解決する一つの方法は、クエリーを繰り返し実行し、実行するたびに同じ時間がかかるかどうかをチェックすることである。

This means that the amount of cached data is staying constant and not impacting the results.

これは、キャッシュされたデータ量が一定で、結果に影響を与えないことを意味する。

In this case, it's 100% cached.

この場合、100％キャッシュされる。

Explain Analyze is a profiling tool for your queries that will show you where MySQL spends time on your query and why.

Explain Analyze はクエリのプロファイリング・ツールで、MySQL がクエリのどこに時間を費やしているのか、またその理由を表示します。

It will plan the query, instrument it, and execute it while counting rows and measuring time spent at various points in the execution plan.

クエリを計画し、インストルメントを作成し、行数をカウントしながら実行し、実行プランの様々なポイントで費やされた時間を計測する。

It is important to understand how the queries are actually executed based on the statistics available.

利用可能な統計に基づいて、クエリが実際にどのように実行されるかを理解することが重要である。

Once you see how to read query plans and understand how each of the underlying nodes type work on MySQL, then you should be confident to manage the queries on a production database.

クエリプランの読み方を理解し、MySQL の各基盤ノードタイプがどのように動作するかを理解すれば、本番データベースでクエリを管理する自信がつくはずです。