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