MySQL High Performance Tuning Guide in Udemy Section 3

MySQL High Performance Tuning Guide in Udemy Section 3

15. How does the Optimiser work?

• Explain文はOptimizerが選んだ実行計画を確認できる
• Optimizerはフルスキャン or カバリングインデクス or インデックスで場所を絞り込んでテーブルスキャンするを過去の統計から選ぶ

16. EXPLAIN basics

• 遅いクエリを見つけたらexplainを付けて実行すれば実行計画が見れる
• 実行計画は実際には実行せずOptimizerが推測したもの
• explain analyzeを使うと、実際にクエリを実行し、そのときにOptimizerが実行したものが見れる
• データの状況によって挙動が変わるので、insert,update,deleteはexplainで分析するのは難しい
• explain format=json とやるとjsonで結果が得られる

英語

• along with ...
  • ...に加えて

17.Example 1: Single Table, Table Scan

• no indexなカラムにselectする例
• 使えるindexが無いからクエリを評価するのにフルテーブルスキャンが必要

mysql> explain select * from city where name = 'London'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4046
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

• typeはクエリがアクセスするところを示す
• type ALLは最も基本的なタイプで、全ての行にアクセスしていて、最もコストが高い(フルテーブルスキャン)
• だからALLは全部大文字で書かれている？
• rowsは調査する行の数を示している。この例では4046行を調査することになっている
• filteredは、インデックスの有効性の有無に関わらず、クエリの条件で絞り込まれる行の数の推定値を表しているが、推定値なので真の値ではない
• explain formatは状況によって有効に使える
• format=jsonは詳しい情報が得られるし、format=treeはリレーションシップごとにどのようにクエリが実行されるのか、クエリの順番などを詳しく見ることができる

mysql> explain format=tree select * from city where name = 'London';
+-----------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------+
| -> Filter: (city.`Name` = 'London')  (cost=410.85 rows=405)
    -> Table scan on city  (cost=410.85 rows=4046)
 |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• この例だと、低レベルノードでテーブルスキャンをして、高レベルノードで低レベルノードの結果を使って処理していることがわかる

18. EXPLAIN ANALYZE

• explain analyzeはexplainとは違い、実際にクエリを実行したときの実行計画をtree formatで表示する

mysql> explain analyze select * from city where name = 'London';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (city.`Name` = 'London')  (cost=410.85 rows=405) (actual time=0.643..4.017 rows=2 loops=1)
    -> Table scan on city  (cost=410.85 rows=4046) (actual time=0.074..3.019 rows=4079 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

• 実際に実行するとactualに推定値ではなく実際の値が入っていることがわかる（子ノードでrows=407で、親ノードでrow=2まで絞りこまれている。推定値と違う）
• 推定値のcostは内部的なMySQLの単位で、1回diskから読むと2.0、1回メモリから読むと1.0といった具合

19. Example2: Single Table, Index Access

• indexでアクセスする例

mysql> explain analyze format=tree select * from city where countrycode = 'FRA';
+---------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on city using CountryCode (CountryCode='FRA')  (cost=14.00 rows=40) (actual time=0.197..0.212 rows=40 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

• 一発で取得できていることがわかる
• 推定値と実際の値もどちらもrows=40で一致している。indexは必要な統計値を提供してくれるから。

20. Example 3: Multicolumn Index

• マルチプライマリキーを作成しているテーブルの例

mysql> show columns from countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> explain analyze select * from countrylanguage where CountryCode = 'CHN';
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on countrylanguage using PRIMARY (CountryCode='CHN')  (cost=2.06 rows=12) (actual time=0.035..0.041 rows=12 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• マルチプライマリーキーであってもフィルタリングに使えていることがわかる

21. Example 4: Two Tables With Subquery And Sorting

• 色んな機能を混ぜた例
  • sub query
    • ヨーロッパで面積の小さいTOP10
  • inner join
    • 人口の多い都市TOP5
  • ヨーロッパで面積の小さいTOP10で、人口の多い都市TOP5を作るクエリ

mysql> explain analyze
	select ci.ID, ci.Name, ci.District, co.Name AS Country, ci.Population 
	from world.city ci 
	inner join 
		(select code, name 
		from world.country 
		where Continent = 'Europe'
		order by SurfaceArea
		limit 10) co
	on co.Code= ci.CountryCode
	order by ci.Population DESC
	limit 5\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 5 row(s)  (actual time=0.306..0.307 rows=5 loops=1)
A    -> Sort: ci.Population DESC, limit input to 5 row(s) per chunk  (actual time=0.305..0.306 rows=5 loops=1)
B        -> Stream results  (cost=90.31 rows=174) (actual time=0.199..0.269 rows=15 loops=1)
C            -> Nested loop inner join  (cost=90.31 rows=174) (actual time=0.197..0.264 rows=15 loops=1)
D                -> Table scan on co  (cost=26.91..29.28 rows=10) (actual time=0.169..0.171 rows=10 loops=1)
E                    -> Materialize  (26=cost.65..26.65 rows=10) (actual time=0.169..0.169 rows=10 loops=1)
F                        -> Limit: 10 row(s)  (cost=25.65 rows=10) (actual time=0.156..0.158 rows=10 loops=1)
G                            -> Sort: country.SurfaceArea, limit input to 10 row(s) per chunk  (cost=25.65 rows=239) (actual time=0.156..0.157 rows=10 loops=1)
H                                -> Filter: (country.Continent = 'Europe')  (cost=25.65 rows=239) (actual time=0.047..0.130 rows=46 loops=1)
I                                    -> Table scan on country  (cost=25.65 rows=239) (actual time=0.043..0.106 rows=239 loops=1)
J                -> Index lookup on ci using CountryCode (CountryCode=co.`code`)  (cost=4.53 rows=17) (actual time=0.008..0.009 rows=2 loops=10)

1 row in set (0.00 sec)

• サブクエリから見ると、(I~F)でテーブルをスキャンして行を全て持ってきて、filterしてsortしてlimitしている様子がわかる
• Eでサブクエリの結果を実体化(materialized)している
  • これがテンポラリテーブルに書き込んでいるということ？
• Dがjoinする1つめのテーブルを読み込んでいて、これはEで実体化したテーブルであり、10行をフルスキャンしている
• Jがjoinする2つめのテーブルで、indexを使ったlookupになっている。しかし、CにNested loopとあり、Dで読み込んだ行の数だけJを実行する。Dで読み込んだ行は10行で、たしかにjはloops=10となっており、10回実行されることがわかる。
• Jにrows=2とあるが、一度のJの実行で平均2行取得されるという意味
• 最終的にinner joinの結果15行が取得される(Cのrows=15)
• Cの推定値rows=174が大きすぎるように見えるが、このテーブルのデータが少なすぎるからJの推測値rows=17とDの推測値rows=10をかければ170になるので、悪くはない推論になってる
• BのStream resultsはmaterializedせずにストリームで結果を返すということ
• 最後にSortしてLimitして終わり

22. Spotting jumps in runtime

• explain analyzeをどうやって理解していけばよいか
• 2つを気にすること
• 1. ランタイムは与えられたクエリに対して正当な結果を見せているか
• 2. クエリが遅い場合、どこでランタイムがジャンプしているか
  • jumpとは、クエリの実行時間でいきなり時間がかかっている箇所。explain analyzeの結果を下から読んでいって、3秒台で進んでいるのにいきなり4秒台になっているような箇所のことっぽい
• 推定値と実測値が乖離していないかよく見るべき
• 推定値はしきい値に達したときに更新される

23. Hot and cold cache behaviour

• クエリを2回実行して、2回目が1回目よりもかなり早かったらキャッシュが効いている
• この挙動はhot cacheによるもので、データがすでにDBかOSのキャッシュの乗っている
• クエリを分析するときはhotかcoldに関わらず注意が必要なので、何度か実行して確認すること

24. Conclusions

まとめ