What's New inWhat's New inMySQL 5.7 OptimizerMySQL 5.7 Optimizer

奥野 幹也Twitter: @nippondanjimikiya (dot) okuno (at) gmail (dot) com

@MySQL User Conference Tokyo 2015

免責事項本プレゼンテーションにおいて示されている見解は、私自身の見解であって、オラクル・コーポレーションの見解を必ずしも反映したものではありません。ご了承ください。

自己紹介

● MySQL サポートエンジニア– 日々のしごと

● トラブルシューティング全般● Q&A 回答● パフォーマンスチューニング

など● ライフワーク

– 自由なソフトウェアの普及● オープンソースではない● GPL 万歳！！

– 最近はまってる趣味はリカンベントに乗ること● ブログ

– 漢のコンピュータ道– http://nippondanji.blogspot.com/

MySQL 5.7登場しましたね！！

ブログを書いてなくてすみません・・・

MySQL 5.7 の数多くの新機能

● 実に 150 以上もの新機能が追加された！！– https://yakst.com/ja/posts/3037– yoku0825++

● レプリケーション関連● InnoDB 関連● オプティマイザー関連● セキュリティ関連● パフォーマンススキーマ関連● GIS 関連● JSON 関連

etc etc...

オプティマイザの新機能！！

● EXPLAIN for CONNECTION● JSON EXPLAIN● コストモデル

– JOIN の順序選択– 統計情報の正確性– コストの係数のユーザーによる設定

● GROUP BY● FROM 句のサブクエリ● IN サブクエリ● UNION ALL● ソート● テンポラリテーブル

EXPLAIN forCONNECTION

現在実行中のクエリの実行計画を見る機能

● 長時間実行中のクエリを発見したときに便利● 実行例

– EXPLAIN FOR CONNECTION 123;● コネクション ID は SHOW PROCESSLIST 等で取得

JSON EXPLAINの改善

JSON EXPLAIN とは

● EXPLAIN の情報を JSON フォーマットで出力する機能● MySQL 5.6 から使用可能● MySQL Workbench と組み合わせて使用すると、ビジュアル

EXPLAIN ができる● MySQL 5.7 ではオプティマイザのコストを出力するように

実行例

mysql> explain format=json select * from City, Country where City.countrycode = Country.code and City.name like 'a%'\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1420.94" }, "nested_loop": [ { "table": { "table_name": "City", "access_type": "ALL", "possible_keys": [ "CountryCode" ...以下略

ビジュアル EXPLAIN 実行例

select * from City, Country where City.countrycode = Country.code and City.name like 'a%'\G

オプティマイザトレースもよろしく！！

● 選択された実行計画だけでなく、検討した実行計画についての情報を表示する機能

● MySQL 5.6 から追加● 表示は JSON 形式● RTFM （マニュアルヨメ）

コストモデルの改善

MySQL のオプティマイザはコストベース

● 各種操作のコストを見積もり、最適な実行計画を探す● 最適なインデックスはどれか● 最適な結合（ JOIN ）の順序はどれか

etc● ルールベースオプティマイザは持っていない。残念！！

● ヒントを使って実行計画をコントロールすることは可能

MySQL 5.7 におけるコストモデルの改良点

● かなり大きなリファクタリングが行われた● ユーザー側から見える改良点は次の 3 つ

– JOIN の順序選択の改善– テーブルおよびインデックス統計情報の改良– 各種操作のコストが設定可能に

JOIN の順序選択の改善

WHERE 句による絞り込み

● MySQL 5.6以前のオプティマイザの問題点– 駆動表（ JOIN で先に読まれるテーブル）の行数の見積もり

に不備があった● カウントされるのはアクセスメソッドによるもののみ● その後 WHERE 句で絞りこまれて行数が減ることは考慮さ

れていなかった● その結果、望ましくない JOIN の順序になってしまうこと

が・・・● MySQL 5.7 では

– WHERE 句による絞り込みについても考慮に入れる– JOIN の順序がより最適なものに！！

サンプル

Select * from City inner join Country on Country.Code = City.CountryCode where City.name like 'a%'

MySQL 5.6 MySQL 5.7

サンプルmysql> explain select * from City inner join Country on Country.Code = City.CountryCode where City.name like 'a%'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City partitions: NULL type: ALLpossible_keys: CountryCode key: NULL key_len: NULL ref: NULL rows: 4188 filtered: 11.11 Extra: Using where*************************** 2. row *************************** id: 1 select_type: SIMPLE table: Country partitions: NULL type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 3 ref: world.City.CountryCode rows: 1 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)

ここに注目！！

City から読まれる行数は4188 x 0.1111 = 465.2868

という見積もりになる

PARTITION 、 EXTENDEDby default

● EXPLAIN PARTITIONS と EXTENDED がデフォルトで有効化– さっきのサンプルで filtered が表示されていましたね？

● パーティション情報はパーティショニングされたテーブルでは常に重要

統計情報の改良

統計情報がより正確に

● ストレージエンジンからオプティマイザに渡されるひとつのキーの値あたりの行数が整数から浮動小数点に

● コストの見積もりがより正確に

サンプル

mysql> alter table City add index (district);Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table City add index (name);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0

mysql> select index_name, cardinality from information_schema.statistics where table_name = 'City' and index_name in ('District', 'Name')\G*************************** 1. row *************************** index_name: Districtcardinality: 4188*************************** 2. row *************************** index_name: Namecardinality: 41882 rows in set (0.00 sec)

サンプル : MySQL 5.6mysql> explain extended select * from City inner join Country on Country.name = City.name and Country.name = City.district\G*************************** 1. row ***************************...略 ...*************************** 2. row *************************** id: 1 select_type: SIMPLE table: City type: refpossible_keys: District,Name key: District key_len: 20 ref: world.Country.Name rows: 1 filtered: 100.00 Extra: Using index condition; Using where2 rows in set, 1 warning (0.00 sec)

サンプル : MySQL 5.7mysql> explain extended select * from City inner join Country on Country.name = City.name and Country.name = City.district\G*************************** 1. row ***************************...略 ...*************************** 2. row *************************** id: 1 select_type: SIMPLE table: City partitions: NULL type: refpossible_keys: Name,District key: Name key_len: 35 ref: world.Country.Name rows: 1 filtered: 10.00 Extra: Using index condition; Using where2 rows in set, 2 warnings (0.00 sec)

サンプル

mysql> set optimizer_trace='enabled=on';Query OK, 0 rows affected (0.00 sec)

mysql> select * from City inner join Country on Country.name = City.name and Country.name = City.district;

mysql> select * from information_schema.optimizer_trace\G

サンプル

MySQL 5.6"best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "District", "usable": false, "chosen": false }, { "access_type": "ref", "index": "Name", "usable": false, "chosen": false }, { "access_type": "scan", "rows": 4188, "cost": 862.6, "chosen": true } ]},

MySQL 5.7"best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "Name", "rows": 1.0475, "cost": 300.43, "chosen": true }, { "access_type": "ref", "index": "District", "rows": 3.0636, "cost": 878.65, "chosen": false }, { "rows_to_scan": 4188, ...略 ... } ]},

各種操作のコストが設定可能に

ハードコード → 設定可能

● MySQL 5.6 では、概算コストを算出するときに使用する係数が固定（ハードコード）だった

● MySQL 5.7 ではシステムテーブル上で設定可能に– mysql.server_cost … オプティマイザが使用する、スト

レージエンジンによらないコスト– mysql.engine_cost … ストレージエンジンに依存したコ

スト– FLUSH OPTIMIZER_COSTS;

server_cost● key_compare_cost

– ひとつのキーの値を比較するのにかかるコスト– デフォルト 0.1

● row_evaluate_cost– 行を読み込んでから評価するのにかかるコスト– デフォルト 0.2

● {disk|memory}_tmptable_{create|row}_cost– ディスクあるいはメモリ上のテーブルを作成または読み書

きするのにかかるコスト– ディスクのデフォルトは作成 40.0 、 rw 1.0– メモリのデフォルトは作成 2.0 、 rw 0.2

engnie_cost● io_block_read_cost

– データファイルへアクセスするときのコスト– デフォルト 1.0

● memory_block_read_cost– バッファプールへアクセスするときのコスト– デフォルト 1.0

サンプル

mysql> update server_cost set cost_value = 0.5 where cost_name = 'row_evaluate_cost';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> update engine_cost set cost_value = 0.1 where cost_name = 'memory_block_read_cost';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush optimizer_costs;Query OK, 0 rows affected (0.00 sec)

サンプル

BEFORE"cost_info": { "read_cost": "239.00", "eval_cost": "5.31", "prefix_cost": "340.60", "data_read_per_join": "1K"},

AFTER"cost_info": { "read_cost": "23.90", "eval_cost": "5.31", "prefix_cost": "120.10", "data_read_per_join": "1K"},

explain format=json select * from City join Country on City.id = Country.capital where City.name like 'ab%'\G

オプティマイザヒント

オプティマイザの実行計画を細かく制御

● クエリ単位でアルゴリズムの ON/OFF が指定できる● /*+ ... */ で囲った中にヒントを記述する● テーブルごと、クエリブロックごとにヒントを指定可能

– optimizer_switch は常にクエリ全体● 例

– select /*+ BKA(City) */ * from City join Country on Country.Code = City.CountryCode where City.name like 'a%'

– select /*+ NO_RANGE_OPTIMIZATION(Country) */ Name from Country where Code like 'J%' union all select Name from City where CountryCode like 'I%'

– select Name from Country where Code in (select /*+ SUBQUERY(MATERIALIZATION) */ CountryCode from City where name like 'ab%')

GROUP BY の改善

ONLY_FULL_GROUP_BY● MySQL 5.7 で

– ONLY_FULL_GROUP_BY という sql_mode がデフォルトになった

– ONLY_FULL_GROUP_BY の挙動が変わった● 問題となるクエリの例

– SELECT Code, Name, AVG(GNP) FROM Country GROUP BY Code;

● GROUP BY 句に含まれていないカラムが出現している！！

バージョンによる挙動の違い

● ONLY_FULL_GROUP_BY が有効でない場合は、 GROUP BY を実行するときに読んだ行のどれかの値が返される

– 結果は不定！！ Non-deterministic ！！● MySQL 5.6 では、 ONLY_FULL_GROUP_BY が有効な場合、 GROUP BY

句に含まれない、かつ集約関数でないカラムがある場合、エラーになる

● MySQL 5.7 では、 ONLY_FULL_GROUP_BY が有効な場合（デフォルト）、 GROUP BY 句に含まれるカラムに関数従属しているカラムの出現は許可され、それ以外はエラーになる

– ただし主キーやユニークキーによって FD が保証されている場合のみ

FROM 句のサブクエリの改善

不要なテンポラリテーブルよ、さらば！！

● 不要な場合にはテンポラリテーブルを作成しない● 外部クエリに FROM 句のサブクエリ内部の条件がマージされ

る● FROM 句のサブクエリの実行効率超アップ！！

サンプル： MySQL 5.6> EXPLAIN SELECT Name FROM (SELECT Name FROM City WHERE Name LIKE 'a%') t\G*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 258 Extra: NULL*************************** 2. row *************************** id: 2 select_type: DERIVED table: City type: rangepossible_keys: Name key: Name key_len: 35 ref: NULL rows: 258 Extra: Using where; Using index2 rows in set (0.01 sec)

サンプル： MySQL 5.7mysql> EXPLAIN SELECT Name FROM (SELECT Name FROM City WHERE Name LIKE 'a%') t\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4188 filtered: 11.11 Extra: Using where1 row in set, 1 warning (0.00 sec)

IN サブクエリの改善

行コンストラクタでインデックスが使用可能に

select * from CountryLanguage where (countrycode, language) in (('jpn', 'Japanese'), ('USA','English'));

UNION ALL の改善

テンポラリテーブルが不要に！！

● MySQL 5.6 では UNION ALL はすべてテポラリテーブルを作っていた

– 不要！！！● MySQL 5.7 では必要がない限りテンポラリテーブルは作らな

い。– 効率アップ！！– ただしソートする場合にはテンポラリテーブルが必要

ソートバッファの利用効率改善

可変長のカラムが省スペース化

● MySQL 5.6 では、たとえ可変長のカラムをソートする場合でも、カラムの最大サイズ分だけソートバッファが消費された

● MySQL 5.7 では、下記のデータはパックされた状態でソートバッファに格納される

– CHAR型の値– VARCHAR型の値– NULL

● つまりより多くの行がソートバッファに入る！！

テンポラリテーブルがInnoDB に

Using temporary...● ディスクベースのテンポラリテーブルが必要な場合に

は、 MyISAM ではなく InnoDB が使われるようになった– MyISAMへ変更可– InnoDB のテンポラリテーブル作成 /削除が高速化したこと

による

まとめ

まとめ

● MySQL 5.7 のオプティマイザは激しく進化！！– EXPLAIN for CONNECTION– JSON EXPLAIN– コストモデル

● JOIN の順序選択● 統計情報の正確性● コストの係数のユーザーによる設定

– GROUP BY– FROM 句のサブクエリ– IN サブクエリ– UNION ALL– ソート– テンポラリテーブル

● MySQL 5.7 はオプティマイザ以外にも数多くの新機能！！– 合計 150以上

Q&Aご静聴ありがとうございました。