MySQLとPostgreSQLにおけるステートメントの確認

by

MySQLとPostgreSQLにおける重い処理をシステムテーブル（view)から確認する方法

MySQL, PostgreSQLそれぞれ運用するなかで、遅い処理を確認する為にlog_min_duration_statementやslow_query_logを利用して該当のSQLをログに出力して適宜SQLをチューニングする等の対応されていると思います。ここでは、その他にMySQLとPostgreSQLでそれぞれ提供されている、管理用のVIEWを利用して実行されたSQLとその処理の詳細を確認しています。

log_min_duration_statement@PostgreSQL

このパラメータに設定した時間（ミリ秒）以上の処理時間を要したSQLは、そのSQL文とその所要時間をログに出力します。
例えば 10秒以上かかるクエリを特定するには、設定ファイルpostgresql.conf にて以下のように設定を行います。

log_min_duration_statement=10000

long_query_time@MySQL

slow_query_logとslow_query_log_fileシステム変数を使用します。slow_query_logログを無効にするには0に設定し有効にするには1に設定します。設定ファイル my.cnf にて以下のようにlong_query_timeにて所要時間(秒）の設定を行います。また、slow_query_log_fileログファイルの名前を指定するように設定します。これらは、動的パラメータなので稼働中もSETコマンドで設定する事が可能です。

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/host-ip-slow.log
long_query_time = 10

sys.statement_analysis@MySQL

これらのビューは統計を集計し正規化したステートメントをリストします。デフォルトで行は合計レイテンシを降順にソートされています。

27.4.3.35 The statement_analysis and x$statement_analysis Views


[sys]> select db,query,full_scan,exec_count,err_count,warn_count,total_latency,max_latency,avg_latency,lock_latency,rows_sent,tmp_disk_tables,sort_merge_passes from statement_analysis limit 10;
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
| db    | query                                                             | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | tmp_disk_tabl
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
| APP   | SELECT `WORD` FROM `INFORMATIO ... INNODB_FT_INDEX_TABLE` LIMIT ? | *         |          1 |         0 |          0 | 11.84 min     | 11.84 min   | 11.84 min   | 7.95 ms      |        10 |              
| APP   | SELECT `WORD` , COUNT ( `WORD` ...  ORDER BY `total` DESC LIMIT ? | *         |          1 |         1 |          0 | 9.24 min      | 9.24 min    | 9.24 min    | 153.00 us    |         0 |              
| APP   | COMMIT                                                            |           |      14300 |         0 |          0 | 3.02 min      | 850.07 ms   | 12.66 ms    |   0 ps       |         0 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbdata`                           | *         |          3 |         0 |          3 | 29.54 s       | 11.67 s     | 9.85 s      | 6.14 ms      |    833154 |              
| myosm | SELECT SQL_NO_CACHE * FROM `nodes`                                | *         |          3 |         0 |          3 | 23.93 s       | 15.15 s     | 7.98 s      | 137.00 us    |   1486554 |              
| APP   | INSERT IGNORE INTO `FTS_dbdata ... TEM_USER , TEXT ) VALUES (...) |           |      14300 |         0 |          0 | 20.06 s       | 895.94 ms   | 1.40 ms     | 5.07 s       |         0 |              
| myosm | SELECT SQL_NO_CACHE * FROM `nodetags`                             | *         |          3 |         0 |          3 | 19.41 s       | 6.57 s      | 6.47 s      | 127.00 us    |   8332701 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbda`                             | *         |          3 |         0 |          3 | 17.62 s       | 6.21 s      | 5.87 s      | 130.00 us    |    852759 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbdaa`                            | *         |          3 |         0 |          3 | 9.96 s        | 4.23 s      | 3.32 s      | 131.00 us    |    455574 |              
| APP   | SET NAMES ? COLLATE ?                                             |           |      14300 |         0 |          0 | 7.77 s        | 198.99 ms   | 543.57 us   |   0 ps       |         0 |              
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
10 rows in set (0.00 sec)

statement_analysisはevents_statements_summary_by_digestを参照しているので、テーブルに格納されるステートメントは以下のサイズに依存しています。


[sys]> show global variables like 'performance_schema_digests_size';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 10000 |
+---------------------------------+-------+

補足： MySQL SYS Schema


pg_stat_statements@PostgreSQL

pg_stat_statementsモジュールはサーバで実行されたすべてのSQL文の実行時の統計情報を記録する手段を提供します。このモジュールは追加の共有メモリを必要とするため、postgresql.confのshared_preload_librariesにpg_stat_statementsを追加してモジュールをロードしなければなりません。 このことは、このモジュールを追加もしくは削除するにはサーバを再起動する必要があるということを意味しています。

pg_stat_statementsはロードされると、サーバのデータベース全体に渡って統計情報を記録します。 この統計情報にアクセスしたり操作したりするために、このモジュールはビューpg_stat_statementsとユーティリティ関数pg_stat_statements_reset、pg_stat_statementsを提供します。 これらはサーバー全体で利用可能ではなく、CREATE EXTENSION pg_stat_statementsで特定のデータベースで利用可能になります。

参照： F.29. pg_stat_statements

postgresql.confの設定変更(サービスの再起動が必要)


shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.save = on

設定と確認

app=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

app=# \dx
                                        インストール済みの拡張一覧
        名前        | バージョン |  スキーマ  |                           説明                            
--------------------+------------+------------+-----------------------------------------------------------
 pg_freespacemap    | 1.2        | public     | examine the free space map (FSM)
 pg_stat_statements | 1.7        | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0        | pg_catalog | PL/pgSQL procedural language
(3 行)

app=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.7             | 1.7               | track execution statistics of all SQL statements executed
(1 行)


app=# select name, setting, unit from pg_settings where name like 'pg_stat_statements%';
               name               | setting | unit 
----------------------------------+---------+------
 pg_stat_statements.max           | 5000    | 
 pg_stat_statements.save          | on      | 
 pg_stat_statements.track         | top     | 
 pg_stat_statements.track_utility | on      | 
(4 行)

実際に記録されたステートメントの確認


app=# \pset pager
「ページャーを使う」は off です。
app=# select userid,dbid,queryid,left(query,30),calls,total_time,min_time,max_time,mean_time,stddev_time,shared_blks_hit,shared_blks_read from pg_stat_statements order by total_time desc;
 userid | dbid  |       queryid        |              left              | calls |      total_time      |       min_time        |       max_time       |      mean_time       |      stddev_time       | shared_blks_hit | shared_blks_read 
--------+-------+----------------------+--------------------------------+-------+----------------------+-----------------------+----------------------+----------------------+------------------------+-----------------+------------------
     10 | 49596 |  5910544648824246769 | select * from memo where id =  |    16 |   352.84530299999994 |              0.016142 |   351.54439399999995 |   22.052831437500004 |      85.07473611928185 |              44 |                4
     10 | 49596 |  6711476591887504002 | explain select * from memo     |     2 |           105.592792 |              0.074625 |           105.518167 |            52.796396 |     52.721771000000004 |              40 |               12
     10 | 49596 |  1258245615633632122 | explain select * from memo whe |     6 |    61.98016300000001 |              0.084408 |            61.497362 |   10.330027166666667 |      22.88272889325042 |               4 |                9
     10 | 49596 |  5761671877250351757 | select name,setting,category f |     3 |   5.1763959999999996 |              1.102193 |             2.606261 |   1.7254653333333332 |     0.6404653712024995 |               0 |                0
     10 | 49596 |    -3158048787087772 | select userid,dbid,queryid,lef |     3 |              0.41186 |   0.13373200000000002 |  0.14129799999999998 |  0.13728666666666667 |  0.0031056397444362608 |               0 |                0
     10 | 49596 |   956172393795458702 | select * from pg_stat_statemen |     3 |  0.26237699999999997 |  0.009907000000000001 |             0.129361 |             0.087459 |   0.054896911898575866 |               0 |                0
     10 | 49596 | -9208585654704159892 | explain select * from memo ord |     3 |             0.250689 |              0.073775 |             0.097503 |             0.083563 |   0.010122049331368954 |               4 |                0
     10 | 49596 |  3231151743520748149 | select userid,dbid,queryid,que |     2 |  0.21030100000000002 |              0.102633 |             0.107668 |  0.10515050000000001 |  0.0025174999999999954 |               0 |                0
     10 | 49596 |  8685157009571086729 | select userid,dbid,queryid,lef |     1 |  0.15841100000000002 |   0.15841100000000002 |  0.15841100000000002 |  0.15841100000000002 |                      0 |               3 |                0
     10 | 49596 |  4048041203833672862 | LOAD 'auto_explain'            |     1 |             0.125106 |              0.125106 |             0.125106 |             0.125106 |                      0 |               0 |                0
     10 | 49596 | -7547750407391136812 | explain select * from memo ord |     1 |             0.098115 |              0.098115 |             0.098115 |             0.098115 |                      0 |               0 |                0
     10 | 49596 |  6755220269130009187 | explain select * from memo ord |     1 |             0.087065 |              0.087065 |             0.087065 |             0.087065 |                      0 |               0 |                0
     10 | 49596 | -1920304080562092233 | SET auto_explain.log_min_durat |     3 |             0.064131 |              0.006951 |             0.049859 |             0.021377 |   0.020140381790489142 |               0 |                0
     10 | 49596 |  9022527725580902277 | select relname,relpages,reltup |     3 |             0.062013 |  0.017379000000000002 |             0.026977 | 0.020671000000000002 |   0.004460459468111628 |               9 |                0
     10 | 49596 | -7365002070969254732 | SELECT tablename,attname,corre |     1 | 0.051623999999999996 |  0.051623999999999996 | 0.051623999999999996 | 0.051623999999999996 |                      0 |              15 |                0
     10 | 49596 | -2189995260333419544 | select ($1 * $2) + ($3 * $4) " |     3 |             0.028727 | 0.0046240000000000005 |             0.019399 | 0.009575666666666666 |    0.00694622239462253 |               0 |                0
     10 | 49596 | -6203021216765832473 | SET auto_explain.log_min_durat |     3 | 0.022913999999999997 |              0.007163 |             0.007885 |             0.007638 | 0.00033596527598349574 |               0 |                0
     10 | 49596 |  2065125607359684573 | SET auto_explain.log_min_durat |     2 |             0.015098 |              0.007356 |             0.007742 |             0.007549 |  0.0001929999999999998 |               0 |                0
     10 | 49596 |  7413539732739831561 | SET auto_explain.log_min_durat |     1 |             0.007563 |              0.007563 |             0.007563 |             0.007563 |                      0 |               0 |                0
     10 | 49596 |  6161394367587035080 | SET auto_explain.log_analyze = |     1 |             0.007052 |              0.007052 |             0.007052 |             0.007052 |                      0 |               0 |                0
     10 | 49596 |  8733460176246631674 | select ($1 * $2) + ($3 * $4) + |     1 |             0.005196 |              0.005196 |             0.005196 |             0.005196 |                      0 |               0 |                0
(21 行)

app=#

pg_stat_statements.maxは、このモジュールによって記録されるSQL文の最大数(すなわち、pg_stat_statementsビューにおける行の最大数)です。
これを超えて異なるSQL文を検出した場合は、最も実行回数の低いSQL文の情報が捨てられます。 デフォルトは5000です。 このパラメータはサーバの起動時にのみ指定できます。

app=# select name,setting,unit,context,category,short_desc from pg_settings where name like 'pg_stat_statements%';
               name               | setting | unit |  context   |    category    |                              short_desc                              
----------------------------------+---------+------+------------+----------------+----------------------------------------------------------------------
 pg_stat_statements.max           | 5000    |      | postmaster | 独自オプション | Sets the maximum number of statements tracked by pg_stat_statements.
 pg_stat_statements.save          | on      |      | sighup     | 独自オプション | Save pg_stat_statements statistics across server shutdowns.
 pg_stat_statements.track         | top     |      | superuser  | 独自オプション | Selects which statements are tracked by pg_stat_statements.
 pg_stat_statements.track_utility | on      |      | superuser  | 独自オプション | Selects whether utility commands are tracked by pg_stat_statements.
(4 行)

statement_analysisとevents_statements_historyによるSQLのパフォーマンス確認

by

SYSスキーマのstatement_analysisとperformance_schemaのevents_statements_historyを利用して、MySQLで簡単にSQLのパフォーマンス確認。

statement_analysis / x$statement_analysis
Description:
Lists a normalized statement view with aggregated statistics,
mimics the MySQL Enterprise Monitor Query Analysis view, ordered by the total execution time per normalized statement
https://github.com/mysql/mysql-sys

events_statements_history
events_statements_history テーブルには、スレッドごとの最新の N ステートメントイベントが格納されます。
N の値はサーバー起動時に自動サイズ設定されます。テーブルサイズを明示的に設定するには、サーバー起動時にperformance_schema_events_statements_history_sizeシステム変数を設定します。
ステートメントイベントは終了するまでテーブルに追加されません。新しいイベントが追加されたときに、テーブルがいっぱいである場合、古いイベントが破棄されます。
https://dev.mysql.com/doc/refman/5.6/ja/events-statements-history-table.html

以下、幾つかテストしてみました。データも十分に入っていないので、十分なテストでは無いですが、
挙動は把握出来るかと思いますので、実際の検証環境で確認して見てください。

【1】 JSONデータのFULLスキャンの状況確認
(1) 先ずは、既存のsys.statement_analysisの状況を確認してみます。

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis;
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db                 | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:16:47 | SHOW TABLES                                                       | sys                | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | sys                | *         |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SHOW SCHEMAS                                                      | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SHOW TABLES                                                       | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SYSTEM_USER , HOST , `d ... != ? AND `state` NOT IN (...)  | performance_schema | *         |          2 |               2 |                 0 |
| 2016-06-03 22:16:47 | SHOW SCHEMAS                                                      | sys                | *         |          1 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SCHEMA ( )                                                 | performance_schema |           |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SCHEMA ( )                                                 | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT SYSTEM_USER ( )                                            | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:16:47 | SELECT @@`version_comment` LIMIT ?                                | NULL               |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

(2) こちらのJSONデータを確認するQueryを実行してみます。

echo "INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。"
echo "SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル"
echo ""

echo "【TEXT型】"
time /usr/local/mysql/bin/mysql -u demo_user -ppassword -e "SELECT distinct json_extract(feature,'$.type') as feature FROM NEW57.features_txt;"

echo ""

echo "【JSON型】"
time /usr/local/mysql/bin/mysql -u demo_user -ppassword -e "SELECT distinct json_extract(feature,'$.type') as feature FROM NEW57.features_json;"

(3) 実行 (やっぱりJSONデータ型は、TEXT型と比較するとバイナリーで早いですね：206,000件のデータ参照）

[admin@misc01 SOD2015]$ ./json_and_text_without_index.sh 
INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。
SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

【TEXT型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m8.147s
user    0m0.007s
sys     0m0.004s

【JSON型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m1.379s
user    0m0.009s
sys     0m0.005s
[admin@misc01 SOD2015]$

(4) 実行結果
INDEXも無い、206,000件のデータなのでどちらも、FULLスキャン,temp table,そしてディスクのtemp tableへ変換されてますね。

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis;
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db                 | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:19:02 | SELECT DISTINCTROW `json_extra ... FROM `NEW57` . `features_txt`  | NULL               | *         |          1 |               1 |                 0 | ※
| 2016-06-03 22:19:02 | SELECT DISTINCTROW `json_extra ... ROM `NEW57` . `features_json`  | NULL               | *         |          1 |               1 |                 0 | ※
| 2016-06-03 22:19:02 | SHOW TABLES                                                       | sys                | *         |          2 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | sys                | *         |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SHOW SCHEMAS                                                      | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SYSTEM_USER ( )                                            | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SHOW SCHEMAS                                                      | sys                | *         |          2 |               0 |                 0 |
| 2016-06-03 22:19:02 | SHOW TABLES                                                       | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SYSTEM_USER , HOST , `d ... != ? AND `state` NOT IN (...)  | performance_schema | *         |          2 |               2 |                 0 |
| 2016-06-03 22:19:02 | SELECT @@`version_comment` LIMIT ?                                | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SCHEMA ( )                                                 | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:19:02 | SELECT SCHEMA ( )                                                 | performance_schema |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
12 rows in set (0.01 sec)

root@localhost [sys]>

【2】 JSONデータにGENERATE COLUMN(生成列)でINDEXを付与した場合の状況確認

(1) 実行スクリプト

echo "JSONデータ型のサンプルを入れたテーブル"
/usr/local/mysql/bin/mysql -u demo_user -ppassword -e "select * from NEW57.features where feature_street = '\"MARKET\"' limit 1\G"

read -p "Press [Enter] key to resume."
echo "JSONデータ型とGenerated Columnを利用したテーブル"
/usr/local/mysql/bin/mysql -u demo_user -ppassword -e "show create table NEW57.features\G"

read -p "Press [Enter] key to resume."
echo "JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where feature_street = '\"MARKET\"'"
/usr/local/mysql/bin/mysql -u demo_user -ppassword -e "explain select feature from NEW57.features where feature_street = '\"MARKET\"'\G"

(2) 実行と実行結果

[admin@misc01 SOD2015]$ ./json_and_generated_column_index.sh 
JSONデータ型のサンプルを入れたテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
feature_street: "MARKET"
Press [Enter] key to resume.

JSONデータ型とGenerated Columnを利用したテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_type` (`feature_type`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
Press [Enter] key to resume.

JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where feature_street = '"MARKET"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: idx_feature_street
          key: idx_feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
[admin@misc01 SOD2015]$

(3) 実行後のsys.statement_analysisを確認すると、full_scanでも無く,temp tableでの処理も無いことが確認出来る。

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis;
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db                 | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:22:03 | SELECT DISTINCTROW `json_extra ... FROM `NEW57` . `features_txt`  | NULL               | *         |          1 |               1 |                 0 |
| 2016-06-03 22:22:03 | SELECT DISTINCTROW `json_extra ... ROM `NEW57` . `features_json`  | NULL               | *         |          1 |               1 |                 0 |
| 2016-06-03 22:22:03 | SELECT * FROM `NEW57` . `featu ... _extract` ( `feature` , ? ) )  | NULL               |           |          0 |               0 |                 0 | ※
| 2016-06-03 22:22:03 | SHOW TABLES                                                       | sys                | *         |          3 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  | sys                | *         |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW SCHEMAS                                                      | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SYSTEM_USER ( )                                            | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW SCHEMAS                                                      | sys                | *         |          3 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT @@`version_comment` LIMIT ?                                | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW TABLES                                                       | performance_schema | *         |          1 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SYSTEM_USER , HOST , `d ... != ? AND `state` NOT IN (...)  | performance_schema | *         |          2 |               2 |                 0 |
| 2016-06-03 22:22:03 | EXPLAIN SELECT `feature` FROM  ... _extract` ( `feature` , ? ) )  | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SHOW CREATE TABLE `NEW57` . `f ... _extract` ( `feature` , ? ) )  | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SCHEMA ( )                                                 | NULL               |           |          0 |               0 |                 0 |
| 2016-06-03 22:22:03 | SELECT SCHEMA ( )                                                 | performance_schema |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+--------------------+-----------+------------+-----------------+-------------------+
15 rows in set (0.00 sec)

root@localhost [sys]>

(4) 強制的にデータのソート処理を発生させて、sys.statement_analysisにてsort_merge_passesが確認出来るか念の為確認。
group byの処理により、tmp_disk_tables、sort_merge_passes共に発生しています。


root@localhost [NEW57]> SELECT json_extract(feature,'$.properties.STREET'),count(json_extract(feature,'$.properties.STREET')) as feature FROM NEW57.features group by json_extract(feature,'$.properties.STREET');
<SNIP>
1717 rows in set (4.61 sec)

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'NEW57';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-04 08:40:56 | SELECT `json_extract` ( `featu ... on_extract` ( `feature` , ? )  | NEW57 | *         |          1 |               1 |                 1 | ※DISK処理発生
| 2016-06-04 08:40:56 | SELECT `json_extract` ( `featu ... re` FROM `NEW57` . `features`  | NEW57 | *         |          0 |               0 |                 0 |
| 2016-06-04 08:40:56 | SHOW SCHEMAS                                                      | NEW57 | *         |          1 |               0 |                 0 |
| 2016-06-04 08:40:56 | SHOW TABLES                                                       | NEW57 | *         |          1 |               0 |                 0 |
| 2016-06-04 08:40:56 | SELECT `json_extract` ( `featu ... re` FROM `NEW57` . `features`  | NEW57 |           |          0 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
5 rows in set (0.01 sec)

root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| SQL_TEXT                                                                                                                                                                                  | t (ms)    | ROWS_EXAMINED |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| NULL                                                                                                                                                                                      |    0.0197 |             0 |
| NULL                                                                                                                                                                                      |    0.0080 |             0 |
| NULL                                                                                                                                                                                      |    0.0048 |             0 |
| NULL                                                                                                                                                                                      |    0.0039 |             0 |
| select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'NEW57'                                                               |    3.2530 |            25 |
| SELECT json_extract(feature,'$.properties.STREET'),count(json_extract(feature,'$.properties.STREET')) as feature FROM NEW57.features group by json_extract(feature,'$.properties.STREET') | 4606.0133 |        209994 |
| SELECT json_extract(feature,'$.properties.STREET') as feature FROM NEW57.features                                                                                                         |  559.5791 |        206560 |
| SELECT json_extract(feature,'$.properties.STREET'),count(json_extract(feature,'$.properties.STREET')) as feature FROM NEW57.features                                                      |    0.2148 |             0 |
| NULL                                                                                                                                                                                      |    0.0981 |             0 |
| SET @slave_uuid= 'b1f2d384-009f-11e6-9aa6-0800275fa837'                                                                                                                                   |    0.0996 |             0 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

【3】 ここからは、MySQLのオフィシャルサンプルDB(World)を利用してMySQLの挙動とパフォーマンスを確認してみます。

パターン1) Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+---------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query         | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+---------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:25:41 | SHOW TABLES   | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:25:41 | SHOW SCHEMAS  | world | *         |          1 |               0 |                 0 |
+---------------------+---------------+-------+-----------+------------+-----------------+-------------------+
2 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [world]> SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+---------------------------+----------+
| Language                  | COUNT(1) |
+---------------------------+----------+
| Abhyasi                   |        1 |
| Acholi                    |        1 |
| Adja                      |        1 |
<SNIP>
| Zhuang                    |        1 |
| Zulu                      |        3 |
| [South]Mande              |        1 |
+---------------------------+----------+
457 rows in set (0.04 sec)

root@localhost [world]> 


root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:25:52 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(1回目)
| 2016-06-03 22:25:52 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:25:52 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
3 rows in set (0.00 sec)

root@localhost [sys]>

上記、Queryの実行プランと実行時間を確認してみます。

root@localhost [world]> explain SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL    | PRIMARY             | NULL        | NULL    | NULL                  |  239 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | City            | NULL       | eq_ref | PRIMARY             | PRIMARY     | 4       | world.Country.Capital |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref    | PRIMARY,CountryCode | CountryCode | 3       | world.Country.Code    |    4 |   100.00 | Using index                                  |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> 



root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SQL_TEXT                                                                                                                                                                    | t (ms) | ROWS_EXAMINED |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.3615 |          2368 |(1回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.5577 |             2 |
| truncate table performance_schema.events_statements_history                                                                                                                 | 0.9153 |             0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

パターン2) CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:28:15 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:28:15 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:28:15 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:28:15 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
4 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [world]> SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+---------------------------+----------+
| Language                  | COUNT(1) |
+---------------------------+----------+
| Abhyasi                   |        1 |
| Acholi                    |        1 |
| Adja                      |        1 |
<SNIP>
| Zhuang                    |        1 |
| Zulu                      |        3 |
| [South]Mande              |        1 |
+---------------------------+----------+
457 rows in set (0.01 sec)

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:29:13 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(1回目)
| 2016-06-03 22:29:13 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |(2回目)
| 2016-06-03 22:29:13 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:29:13 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:29:13 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
5 rows in set (0.01 sec)

root@localhost [sys]>

上記、Queryの実行プランと実行時間を確認してみます。
行数は、多いですがFULL SCANは発生していません、また時間も若干早いです。（但し、何回も実施していないので確かではありません、但しこのサイズではそれ程大きく変わらない事は確かです。）

root@localhost [world]> explain SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                               | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | CountryLanguage | NULL       | index  | PRIMARY,CountryCode | CountryCode | 3       | NULL                              |  984 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | Country         | NULL       | eq_ref | PRIMARY             | PRIMARY     | 3       | world.CountryLanguage.CountryCode |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | City            | NULL       | eq_ref | PRIMARY             | PRIMARY     | 4       | world.Country.Capital             |    1 |   100.00 | Using index                                  |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> 

root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SQL_TEXT                                                                                                                                                                    | t (ms) | ROWS_EXAMINED |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.0868 |          3865 |(2回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 1.5986 |             6 |
| SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.3615 |          2368 |(1回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.5577 |             2 |
| truncate table performance_schema.events_statements_history                                                                                                                 | 0.9153 |             0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
5 rows in set (0.00 sec)

root@localhost [sys]>

パターン3) Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code
ヒント無しなので、オプティマイザー次第

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:33:16 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:33:16 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |
| 2016-06-03 22:33:16 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
6 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [world]> SELECT Language, COUNT(1) FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+---------------------------+----------+
| Language                  | COUNT(1) |
+---------------------------+----------+
| Abhyasi                   |        1 |
| Acholi                    |        1 |
| Adja                      |        1 |
<SNIP>
| Zhuang                    |        1 |
| Zulu                      |        3 |
| [South]Mande              |        1 |
+---------------------------+----------+
457 rows in set (0.01 sec)

root@localhost [world]> 

root@localhost [sys]> select now(),query,db,full_scan,tmp_tables,tmp_disk_tables,sort_merge_passes from sys.statement_analysis where db = 'world';
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| now()               | query                                                             | db    | full_scan | tmp_tables | tmp_disk_tables | sort_merge_passes |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
| 2016-06-03 22:33:29 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(1回目)
| 2016-06-03 22:33:29 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |(2回目)
| 2016-06-03 22:33:29 | SELECT LANGUAGE , COUNT (?) FR ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |(3回目)
| 2016-06-03 22:33:29 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world | *         |          1 |               0 |                 0 |
| 2016-06-03 22:33:29 | SHOW TABLES                                                       | world | *         |          2 |               0 |                 0 |
| 2016-06-03 22:33:29 | EXPLAIN SELECT LANGUAGE , COUN ... OM `City` ) GROUP BY LANGUAGE  | world |           |          1 |               0 |                 0 |
| 2016-06-03 22:33:29 | SHOW SCHEMAS                                                      | world | *         |          1 |               0 |                 0 |
+---------------------+-------------------------------------------------------------------+-------+-----------+------------+-----------------+-------------------+
7 rows in set (0.01 sec)

root@localhost [sys]>

上記、Queryの実行プランと実行時間を確認してみます。(今回はヒントを付けてませんが、1回目と同じ実行プランになりました）
よって、EXPLAINも時間も殆ど同じです。

root@localhost [world]> explain SELECT Language, COUNT(1) FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL    | PRIMARY             | NULL        | NULL    | NULL                  |  239 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | City            | NULL       | eq_ref | PRIMARY             | PRIMARY     | 4       | world.Country.Capital |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref    | PRIMARY,CountryCode | CountryCode | 3       | world.Country.Code    |    4 |   100.00 | Using index                                  |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

root@localhost [world]> 


root@localhost [sys]> select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SQL_TEXT                                                                                                                                                                    | t (ms) | ROWS_EXAMINED |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
| SELECT Language, COUNT(1) FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE          | 7.3393 |          2368 |(3回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.8451 |            10 |
| SELECT Language, COUNT(1) FROM CountryLanguage STRAIGHT_JOIN Country ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.0868 |          3865 |(2回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 1.5986 |             6 |
| SELECT Language, COUNT(1) FROM Country STRAIGHT_JOIN CountryLanguage ON CountryLanguage.CountryCode = Country.Code WHERE Capital IN (SELECT Id FROM City) GROUP BY LANGUAGE | 7.3615 |          2368 |(1回目)
| select SQL_TEXT,(timer_wait)/1000000000.0 "t (ms)",ROWS_EXAMINED from performance_schema.events_statements_history order by TIMER_START desc limit 10                       | 0.5577 |             2 |
| truncate table performance_schema.events_statements_history                                                                                                                 | 0.9153 |             0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------+
7 rows in set (0.00 sec)

root@localhost [sys]>

【参考】
■ この動画とPDFは参考になる良い資料です。
https://community.oracle.com/docs/DOC-997564

■ こちらのツールを利用すると複数MySQLのシステムとSQLを監視する事が出来ます。
簡単にGUIベースで確認して管理コスト削減したいたい場合はお勧めします。
https://www-jp.mysql.com/products/enterprise/monitor.html