What's New in MySQL 5.7 InnoDB
23 likes14,118 views
db tech showcase 2016 Tokyo で発表した資料です。MySQL 5.7の新機能のうち、InnoDBについてまとめてあります。
![空間データの検索
● MBRContains() 、 MBRWithin() 、 ST_Contains() 、 ST_Within() といった
関数に対して使用可能。
mysql> SELECT id,ST_AsText(g) FROM geomtest
-> WHERE ST_Contains(ST_GeomFromText(
-> 'POLYGON((0 0,150 0,150 150,0 150,0 0))'), g);
mysql> SELECT id,ST_AsGeoJSON(g) FROM geomtest
-> WHERE ST_Contains(ST_GeomFromGeoJson('{ "type":
"Polygon", "coordinates": [ [ [0.0, 0.0], [150.0,
0.0], [150.0, 150.0], [0.0, 150.0], [0.0,
0.0] ] ] }'), g);
MySQL 5.7 では GeoJSON も追加された。
空间インデックスとは関係ないけど
GeoHash というものも追加された。](https://image.slidesharecdn.com/whats-new-57-160714020632/85/What-s-New-in-MySQL-5-7-InnoDB-52-320.jpg)
More Related Content
What's hot (20)
Similar to What's New in MySQL 5.7 InnoDB (20)
![[OSC 2017 Tokyo/Fall] OSSコンソーシアム DB部会 MySQL 8.0](https://cdn.slidesharecdn.com/ss_thumbnails/20170909osc-osscons-mysql8-170918232718-thumbnail.jpg?width=560&fit=bounds)
More from Mikiya Okuno (20)
Recently uploaded (6)
What's New in MySQL 5.7 InnoDB
- 1. What's New inWhat's New in MySQL 5.7 InnoDBMySQL 5.7 InnoDB 奥野 幹也 Twitter: @nippondanji mikiya (dot) okuno (at) gmail (dot) com @DB Tech Showcase Tokyo 2016
- 3. 自己紹介 ● MySQL サポートエンジニア – 日々のしごと ● トラブルシューティング全般 ● Q&A 回答 ● パフォーマンスチューニング など ● ライフワーク – 自由なソフトウェアの普及 ● オープンソースではない ● GPL 万歳！！ – 最近はまってる趣味はリカンベント（自転車） ● ブログ – 漢のコンピュータ道 – http://nippondanji.blogspot.com/
- 5. MySQL 5.7 の数多くの新機能 ● 実に 150 以上もの新機能が追加された！！ – MySQL 5.7 の新機能完全リスト https://yakst.com/ja/posts/3037 – yoku0825++ ● レプリケーション関連 ● InnoDB 関連 ● オプティマイザー関連 ● セキュリティ関連 ● パフォーマンススキーマ関連 ● GIS 関連 ● JSON 関連 etc etc... 本日のお題
- 6. オプティマイザの新機能 ● EXPLAIN for CONNECTION ● JSON EXPLAIN ● コストモデル – JOIN の順序選択 – 統計情報の正確性 – コストの係数のユーザーによる設定 ● GROUP BY ● FROM 句のサブクエリ ● IN サブクエリ ● UNION ALL ● ソート ● テンポラリテーブル
- 7. レプリケーションの新機能 ● マルチスレッドスレーブの改良 ● マルチソースレプリケーション ● 準同期レプリケーションの改良 – ロスレスレプリケーション – パフォーマンスの改善 – ACK を返すスレーブ数の指定 ● GTID の改良 – スレーブでの管理が効率化 – 再起動時の GTID 計算方法が効率化 ● オンライン操作の拡充 – GTID 有効化 – CHAMGE MASTER の一部 – レプリケーションフィルター
- 8. セキュリティ関係の新機能 ● パスワード期限の設定 ● ユーザーのロック、アンロック ● SET PASSWORD コマンドの仕様変更 ● CREATE USER … IF NOT EXISTS ● ログイン不可能なユーザーアカウント ● SSL のセットアップが容易に – キーファイルの自動生成 – mysql_ssl_rsa_setup ● 透過的テーブルスペース暗号化 ● デフォルトの状態がセキュアに – test データベースの廃止 – 匿名ユーザーの廃止 – localhost 以外の root の廃止
- 9. InnoDB の新機能 ● 一般テーブルスペース ● テンポラリテーブルの InnoDB 化 ● バッファプールのオンラインリサイズ ● UNDO ログの自動トランケート ● 32/64KB ページのサポート ● REDO ログフォーマットの改善 ● バッファプールをダンプする割合の指定 ● ページ統合におけるページ充填率の指定 ● コピーしない ALTER TABLE 操作の増加
- 10. InnoDB の新機能 つづき ● 全文検索用プラガブルパーサーのサポート – ngram あるいは MeCab による日本語の全文検索 ● 全文検索の最適化 ● 空间インデックスのサポート ● 透过的テーブル圧缩 ● 透過的テーブルスペース暗号化 ● ダブルライトが不要なとき自動的に無効化 ● NUMA サポートの追加 ● InnoDB モニターの有効化方法変更 ● 情報スキーマの改良 ● デフォルト行フォーマットの指定
- 11. InnoDB の新機能 性能改善系 ● テンポラリテーブルのための最適化 ● RO トランザクションの性能改善 ● RW トランザクションの性能改善 – index->lock の競合改善（ SX ロック） ● リードビュー作成の性能改善 ● trx_t のキャッシュ効率改善 ● AHI のミューテックス競合改善 ● ページクリーナーのマルチスレッド化 ● フラッシュアルゴリズムの改善 ● クラッシュリカバリの性能改善 ● ログファイル書き込みの性能改善 ● インデックス作成の高速化 etc
- 12. 一般 テーブルスペース
- 13. 一般テーブルスペースとは ● 従来からある InnoDB のテーブルスペース – ibdata1 – .ibd ファイル ● innodb_file_per_table=1 – 個別の UNDO ログ ● 一般テーブルスペースとは第三のテーブルスペース – 明示的に作成する – 複数のテーブルをまとめて格納する – 任意のディレクトリ上にデータファイルを作成することが可 能
- 14. 作成例 mysql> CREATE TABLESPACE ts1 ADD DATAFILE '/path/to/ssdvol/tablespace.ibd' ENGINE INNODB; mysql> CREATE TABLE tbl_name1 (... 中略 ...) TABLESPACE ts1; mysql> CREATE TABLE tbl_name2 (... 中略 ...) TABLESPACE ts1;
- 15. 一般テーブルスペースの主な用途 ● 特定のテーブルだけ別のストレージを割り当てたい – 頻繁にアクセスされるテーブルに高速なストレージ – 殆どアクセスされないテーブルに低速で大容量のストレー ジ ● 複数のテーブルで、同じ圧縮設定を使いたい ● 多数のパーティションをまとめて格納したい ● 大きなバッファプール使用時の効率化 – DROP TABLE によるバッファプールのスキャンを避けたい
- 16. 圧縮設定 FILE_BLOCK_SIZE innodb_page_size FILE_BLOCK_SIZE KEY_BLOCK_SIZE 圧縮 / 非圧縮 64K 65536 N/A 非圧縮 32K 32768 N/A 非圧縮 16K 16384 N/A 非圧縮 8192 8 圧縮 4096 4 圧縮 2048 2 圧縮 1024 1 圧縮 8K 8192 N/A 非圧縮 4096 4 圧縮 2048 2 圧縮 1024 1 圧縮 4K 4096 N/A 非圧縮 2048 2 圧縮 1024 1 圧縮
- 18. 明示的なテンポラリテーブル ● CREATE TEMPORARY TABLE で作成 ● ストレージエンジンの指定が可能 ● 作成したセッションだけからアクセス可能 ● アプリケーションが中間結果を格納し、再利用したい場合に 便利
- 19. 暗黙的なテンポラリテーブル ● クエリの実行途中で自動的に作成される – 実行過程の中間結果を格納 – UNION, GROUP BY, JOIN + ORDER BY, DISTINCT + ORDER BY, FROM 句のサブクエリ , 一部のビュー ● 作成当初はメモリベース – MEMORY ストレージエンジン ● 大きくなるとディスクベースへ自動的に変換 – MySQL 5.6 までは MyISAM – MySQL 5.7 では InnoDB または MyISAM
- 20. MySQL 5.7 ではテンポラリテーブルの オーバーヘッドを徹底的に解消 ● CREATE/DROP のオーバーヘッドが激減した ● REDO ログを生成しない ● 行データにロールバックポインターを持たない ● 更新は DELETE と INSERT によって代替されている ● ダブルライトをしない ● 変更バッファを使用しない ● 専用のテーブルスペース（ ibtmp1 ）内に格納され、再起動時に再 作成される ● ページへのアクセスにおいてラッチをしない ● ページチェックサムをしない ● テーブルのメタデータがディスク上に格納されない ● アダプティブハッシュインデックスを使わない ● ROWID はテンポラリテーブルごとに固有のものが使われる
- 22. innodb_file_per_table なのに ibdata1 が肥大化 ● それは、 UNDO ログのせいかもしれません??? – = ロールバックセグメント ● 更新前のデータをコピーしておく場所 – ロールバックするときに用いられる – MVCC によって参照される ● パージで開放される
- 23. UNDO ログの格納場所 ● MySQL 5.5 までの格納場所は ibdata1 で変更不可 ● MySQL 5.6 – デフォルトでは ibdata1 – オプションで分離可能（ファイルは複数） – ただしサイズ縮小はできない ● MySQL 5.7 – デフォルトでは ibdata1 – オプションで分離可能（ファイルは複数） – UNDO ログファイルをひとつずつトランケート可能
- 24. UNDO ログトランケートの様子 UNDO ログ空き領域 UNDO ログ使用中の領域 innodb_max_ undo_log_size INACTIVE 初期サイズ = 10MB INACTIVE にして 空になるまで待つ。 その間は他の UNDO ログファイルで凌ぐ。 UNDO ログ ファイルを初期化 UNDO ログファイルの サイズがしきい値を超えた
- 25. バッファプールまわりの 改善
- 26. バッファプールの オンラインリサイズ ● バッファプールサイズの増減を、無停止で実行 – innodb_buffer_pool_chunk_size: デフォルト 128MB – innodb_buffer_pool_instances: デフォルト 1 or 8 ● バッファプールサイズが 1GB 以上の時 8 ● チャンクサイズとインスタンス数の積の倍数でサイズを指定 mysql> SET GLOBAL innodb_buffer_pool_size = 100 * 1024 * 1024 * 1024; mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_resize_status';
- 27. バッファプールをダンプする 割合の指定 ● MySQL 5.6 において、バッファプール上のページの番号をダ ンプしたり、該当のページをロードする機能が追加された ● バッファプール上のキャッシュは LRU で管理 – 古いキャッシュデータは再び参照されない可能性が高い – ダンプしても無駄になるかも – 比較的最近アクセスされたページだけで OK ● 起動時間短縮 ● innodb_buffer_pool_dump_pct: デフォルト 25
- 28. 透过的テーブル圧缩
- 29. 圧縮フォーマットって 前からあったよね？ ● innodb_file_format = Barracuda ● CREATE TABLE tblname (…) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE=4; バッファプールが 二重に消費される。 オーバーヘッド大。
- 30. 透過的って何が？ ● ファイルシステムの SPARSE ファイルを利用 ● データを圧縮して未使用領域を SPARSE 化 ● CREATE TABLE tblname (…) ENGINE = InnoDB COMPRESSION = lz4;
- 31. 透过的テーブル圧缩の模式図
- 32. 注意点 ● 使えるかどうかはファイルシステム次第 – Windows: NTFS で利用可能。ただしフォーマット時にクラス ターサイズをデフォルトの 4KB から 512B に変更する必要 がある。 ● Compression Unit: 64KB → 8KB – Linux: NVMFS 、 XFS （カーネル 2.6.38 以降）、 ext4 （カーネ ル 3.0 以降）、 btrfs （カーネル 3.7 以降）などで利用可能 ● SPARSE ファイルはランダムアクセスになりがち – HDD で使うとえらい目に??? – 高速なストレージデバイスを使おう
- 33. 透過的テーブルスペース 暗号化
- 34. 透過的テーブルスペース 暗号化とは ● コミュニティ版、商用版の両方で利用可能 ● InnoDB の .ibd ファイルを暗号化 – 書き込み時に暗号化 – 読み込み時に復号化 ● 暗号化されるのはファイル上のデータのみ – メモリ上（バッファプール）のデータは暗号化されない – 第三者からのファイルアクセスからデータを守る ● キーリングプラグインと連携 – 鍵管理のためのフレームワーク – コミュニティ版にはファイルベースの keyring_file プラグイン が付属 – 商用版には Oracle Key Vault と連携する keyring_okv プラグ インが付属
- 36. 使い方 ● キーリングの設定 – INSTALL PLUGIN keyring_file SONAME 'keyring_file.so'; ● テーブルの作成 – CREATE TABLE table_name (... 略 ...) ENCRYPTION='Y'; ● マスターキーのローテーション – ALTER INSTANCE ROTATE INNODB MASTER KEY;
- 37. SX ロック
- 38. index->lock の競合 ● ひとつのテーブルあるいはインデックスにアクセスが集中す ると、 index->lock の競合が多発し、ボトルネックになってし まっていた。 – 排他処理はどうしても必要。 – 同時に実行ができる処理を増やせば良い。 ● SX ロックの導入 – 既存の rwlock を拡張。 – S ロック、 X ロックの他に、新たに SX ロックが加えられた。 – SX ロックは S ロックと互換性あり、 SX ロックと X ロックとは 互換性なし。 – X ロックが必要だった処理を見直し、 SX ロックへ変更する ことで、同時実行可能な S ロックの処理が増えた。
- 39. SX ロックの互換性 S ロック SX ロック X ロック S ロック ○ ○ × SX ロック ○ × × X ロック × × ×
- 40. インデックス作成の 高速化
- 41. Fast Index Creation (MySQL 5.5) ● 従来のインデックス作成は、新しい定義の（インデックス追 加後の定義を持つ）テーブルを作成し、すべてのデータを 再コピーするというものだった。 – インデックス構築中は参照だけが可能であり、更新はブ ロックされるようになっていた。 ● MySQL 5.5 で追加された Fast Index Creation では、セカンダリ インデックスだけを構築するようになった。 – ただし、参照だけが可能という点は変わらない。
- 42. オンライン DDL (MySQL 5.6) ● DDL 実行中にも更新ができるようになった。 ● これにより、長時間かかるであろう ALTER TABLE も、比較的 気軽に出来るようになった。 ● ただし、 ALTER TABLE 開始時と終了時のみ、アクセスが排 他的になる。 – 長時間実行中の SELECT があると、 ALTER TABLE は待たさ れてしまう。
- 43. Sorted Index Build (New in MySQL 5.7) ● MySQL 5.6 までは、セカンダリインデックスへの挿入は、クラ スタインデックスから順に 1 行ずつ読み取り、セカンダリイン デックスへ挿入するというアルゴリズムだった。 – セカンダリインデックスへのアクセスがランダムアクセスに なるので遅い上にフラグメンテーションが発生する ● MySQL 5.7 ではセカンダリインデックス構築のアルゴリズムが 改善された。 – クラスタインデックスからデータを読み取り、テンポラリファ イルに格納 – セカンダリインデックスのキーの順にソート – セカンダリインデックスへ、ソートされた状態で格納
- 44. 全文検索
- 45. プラガブルパーサーのサポート ● MySQL 5.6 – InnoDB がフルテキストインデックスをサポート – MySQL そのものにはプラガブルパーサーが存在 – ただし Inno DB は独自のフルテキストパーサーを搭載 ● stopword を用いた実装＝日本語は扱えない ● MySQL 5.7 – 標準のプラガブルパーサーをサポート – stopword を用いないパーサーを選択可能 ● 日本語の全文検索が可能！！
- 46. パーサーの追加 ● MeCab – 言わずと知れたオープンソースの形態素解析エンジン – 辞書を用いて日本語を単語に分解 – インデックスのサイズがコンパクト ● NGRAM – 文字列を N 文字ごとに区切ってインデックスを構成 – 日本語の意味を考慮しないので、意図しない検索結果 （ノイズ）が多い – インデックスのサイズは大きくなりがち
- 47. 使い方 ● パーサープラグインのインストール mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so'; ● テーブルの作成 mysql> CREATE TABLE fttest ( -> a SERIAL , -> b VARCHAR(100), -> FULLTEXT(b) WITH PARSER mecab -> ) CHARACTER SET utf8; ● 検索例 mysql> insert into fttest (b) values(' この先生 きのこるには '),(' ここではきものをぬげ '); mysql> select * from fttest where match (b) against (' きもの ');
- 48. 性能について ● 正直なところ、検索性能はそんなに速くない ● InnoDB はトランザクショナルなので堅牢性は高い ● 絶対的な検索性能が必要なら mroonga を使うべき ● カジュアルにちょっと全文検索が欲しいというときに便利
- 49. 空间インデックス
- 50. 最小外接矩形 (Minimum Bounding Rectangle) ● 図形に隣接する矩形の中で最小のもの
- 52. 空間データの検索 ● MBRContains() 、 MBRWithin() 、 ST_Contains() 、 ST_Within() といった 関数に対して使用可能。 mysql> SELECT id,ST_AsText(g) FROM geomtest -> WHERE ST_Contains(ST_GeomFromText( -> 'POLYGON((0 0,150 0,150 150,0 150,0 0))'), g); mysql> SELECT id,ST_AsGeoJSON(g) FROM geomtest -> WHERE ST_Contains(ST_GeomFromGeoJson('{ "type": "Polygon", "coordinates": [ [ [0.0, 0.0], [150.0, 0.0], [150.0, 150.0], [0.0, 150.0], [0.0, 0.0] ] ] }'), g); MySQL 5.7 では GeoJSON も追加された。 空间インデックスとは関係ないけど GeoHash というものも追加された。
- 53. まとめ
- 54. MySQL 5.7 の InnoDB は 超絶進化！！ ● 性能向上 – 並列実行性能の向上 – 処理の効率化およびチューニング – 限界更新性能の向上 ● 管理性向上 – 一般テーブルスペースによるテーブルの一括管理 – UNDO ログトランケートによるディスクスペースの節約 – ALTER TABLE の高速化 ● 利便性向上 – 透過的データ圧縮 – 透過的暗号化 – 全文検索 – GIS 検索 – MyISAM よ、さらば！ もうこれは 使うしかない！！
- 55. 宣伝： 新書籍「詳解 MySQL 5.7 」 ● MySQL 5.7 の新機能を網羅 – 175 もの新機能を解説 – 新機能の理解に欠かせ ないアーキテクチャの話 も盛りだくさん – 本日解説しなかった新 機能も詳しく解説！！ ● 8 月 23 日発売予定