Movatterモバイル変換

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |State of the DolphinRyusuke Kajiyama / 梶山隆輔MySQL Global Business UnitMySQL Sales Consulting Senior Manager, Asia Pacific & JapanHighlights of MySQL 8.0 technology updates

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Safe Harbor StatementThe following is intended to outline our general product direction. It is intended forinformation purposes only, and may not be incorporated into any contract. It is not acommitment to deliver any material, code, or functionality, and should not be relied uponin making purchasing decisions. The development, release, and timing of any features orfunctionality described for Oracle’s products remains at the sole discretion of Oracle.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |70%By 2018, more than 70% of newin-house applications will bedeveloped on an Open SourceRDBMSSource: Gartner, State of Relational Open Source RDBMSs 2015

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |94.0全文検索/GIS (MyISAM)複数テーブルUPDATE/DELETE組み込みライブラリ型サーバOracleMySQL Sun3.23MyISAMInnoDBレプリケーション5.1プラグガブル・ストレージエンジン・アーキテクチャパーティショニングタスクスケジューラ5.6memcached APIUNDO表領域Global Transaction IDマルチスレッドスレーブオンラインALTER TABLEトランスポータブル表領域5.5InnoDBがデフォルトに準同期型レプリケーションPERFORMANCE_SCHEMA1.0-3.22以前ストレージエンジン (ISAM, HEAP)マルチスレッドWindows対応/64bit対応日本語文字コード (SJIS/UJIS)5.0ストアドプロシージャストアドファンクションカーソル/トリガ/ビューXAトランザクションINFORMATION_SCHEMA4.1Unicode対応サブクエリCSV, ARCHIVEndbcluster1995 2000 2005 2010 20155.7新コストモデルオプティマイザロスレスレプリケーションマルチソースレプリケーショングループレプリケーション全文検索CJK対応GIS Boost.Geometry対応セキュリティ強化JSONサポート透過的データ暗号化SYSスキーマ8.0データディクショナリUNDO表領域ロール共通テーブル式(CTE)Window関数ヒストグラム2020

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Copyright © 2017, Oracle and/or its affiliates. All rights reserved.お客様のイノベーションのために10- 3倍のパフォーマンス向上- レプリケーション改良- JSONサポート- セキュリティ強化MySQL 5.7MySQL InnoDB Cluster- MySQL Group Replication- MySQL Router- MySQL ShellMySQL 8.0 (RC)- データディクショナリ- Roles- ユニコード- 共通テーブル式- Window関数- セキュリティ強化- レプリケーション改良

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle MySQL Cloud Service• ナンバーワンのオープンソースデータベースをOracleCloudにて利用可能• シンプル、自動化、統合 & エンタープライズレベル• MySQL Enterprise Editionをベースとした唯一のデータベースクラウドサービス• 迅速な利用開始、強固なセキュリティ、費用対効果の高いMySQLベースのアプリケーション向け

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL Cloud Service: Only From OracleMySQLエキスパートによるサポートサービスOracle Cloud環境との統合クラウドとオンプレミスのハイブリッド型構成MySQL Enterprise Editionon Oracle Cloudエンタープライズレベルの運用監視ソリューション

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |• サービス概要– https://cloud.oracle.com/ja_JP/mysql• 価格情報– https://cloud.oracle.com/mysql/pricing• マニュアル– 英語：http://docs.oracle.com/cloud/latest/?tab=3– 日本語：http://docs.oracle.com/cd/E60665_01/?tab=3• 30日間無償トライアル– https://cloud.oracle.com/ja_JP/tryitMySQL Cloud Service: 参考情報

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Scalable &Stableアクセス集中時の処理改良、セキュリティと耐障害性強化Data Drivenアプリケーションデータ分析による運用中サービス改良支援Developer Firstハイブリッド型のデータモデルとアクセスAPIによる開発柔軟性Mobile Friendly位置情報ベースのサービス向けの機能強化と絵文字を含めたユニコード対応MySQL 8.0 : Webアプリケーション開発効率向上を実現Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 2524x7at Scale

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL 8.0 : モバイルアプリとの親和性Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 28GIS(空間図形情報)サポートの強化• 位置情報ベースのサービスとの連携の改良• MySQL 5.7 にて Boost.Geometry ライブライリーを統合• MySQL 8.0 にて球面座標と測地座標系(SRS)サポートユニコードをデフォルトキャラクタセットに• 絵文字をサポートする utf8mb4 がデフォルトのキャラクタセットに• ユニコード文字列の処理性能が16倍以上向上するケースも• Unicode 9.0 をサポート• UCA(Unicode照合アルゴリズム)ベースの新しい各言語用の照合� ��

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 29SELECT events.id, events.name, DATE(events.event_date) as date,ROUND(st_distance_sphere(venues.location, POINT(132.7645, 35.368377)), 2)AS distance_in_metersFROM eventsINNER JOIN venues ON events.venue_id=venues.idWHERE event_date > NOW()ORDER BY distance_in_meters;+----+---------------------+------------+---------------------+| id | name | date | distance_in_meters |+----+---------------------+------------+---------------------+| 1 | DB Study Chugoku | 2017-12-02 | 0.00 || 2 | OSC2017 Enterprise | 2017-12-09 | 628852.43 || 3 | OSC2018 Osaka | 2018-01-26 | 261242.60 |+----+---------------------+------------+---------------------+3 rows in set (0.00 sec)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |st_distance()を利用した距離の測定mysql> SELECT ST_Distance_Sphere(ST_GeomFromText('POINT(139.718754 35.671148)'),ST_GeomFromText('POINT(135.492778 34.695758)')) as 'From TOKYO Office To Osaka Office';+-----------------------------------+| From TOKYO Office To Osaka Office |+-----------------------------------+| 399041.1417772843 |+-----------------------------------+mysql> SELECT ST_Distance(ST_GeomFromText('POINT(35.671148 139.718754)', 4326),ST_GeomFromText('POINT(34.695758 135.492778)', 4326)) as 'From TOKYO Office To OsakaOffice';+-----------------------------------+| From TOKYO Office To Osaka Office |+-----------------------------------+| 399801.5254154028 |+-----------------------------------+参考: https://dev.mysql.com/doc/refman/8.0/en/spatial-analysis-functions.html 参照: Google Map

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |utf8mb4 as default character set• 最新のUnicode 9.0をサポート• 国ごとの照合順序を実装(日本語を含む)例) utf8mb4_ja_0900_as_cs, utf8mb4_ja_0900_as_cs_ks– ai as : アクセントセンシティブ(アクセント,濁音,破裂音の区別)– ci cs ：ケースセンシティブ(大文字,小文字の区別)– ks ：カナセンシティブ(’あ’,’ア’,’ァ’を区別)31mysql> select *,@@version from information_schema.COLLATIONS where CHARACTER_SET_NAME = 'utf8mb4' and IS_DEFAULT = 'YES';+--------------------+--------------------+----+------------+-------------+---------+------------------------------+| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | @@version |+--------------------+--------------------+----+------------+-------------+---------+------------------------------+| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | 5.7.18-enterprise-commercial |+--------------------+--------------------+----+------------+-------------+---------+------------------------------+mysql> select *,@@version from information_schema.COLLATIONS where CHARACTER_SET_NAME = 'utf8mb4' and IS_DEFAULT = 'YES';+--------------------+--------------------+-----+------------+-------------+---------+---------------+-----------+| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE | @@version |+--------------------+--------------------+-----+------------+-------------+---------+---------------+-----------+| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | 8.0.2-dmr |+--------------------+--------------------+-----+------------+-------------+---------+---------------+-----------+MySQL 4.1Default: Latin1Option: utf8[mb3]MySQL 5.5Add: utf8mb4MySQL 5.7+ optimizationsMySQL 8.0Default: utf8mb4WL#10818: Add utf8mb4 accent sensitive and case insensitive collationhttps://dev.mysql.com/worklog/task/?id=10818WL#7554: Switch to new default character set and change mtr test caseshttps://dev.mysql.com/worklog/task/?id=7554UNICODE詳細: http://www.unicode.org/

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 32MySQL 8.0 vs MySQL 5.7 utf8mb4045000900001350001800008 16 64 128 512 1024SELECT DISTINCT_RANGES0100002000030000400008 16 64 128 512 1024OLTP RO075001500022500300008 16 64 128 512 1024OLTP RW+300-350% in OLTP RO+176-233% in OLTP RW+1500-1800% in SELECT DISTINCT_RANGES

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL 8.0 : アプリケーション開発者に柔軟性をCopyright © 2017, Oracle and/or its affiliates. All rights reserved. |ハイブリッドAPISQL 関数データ型MySQL X DevAPIJSON 関数JSON データ型33SQL と CRUD な NoSQL のハイブリッドAPIによる開発柔軟性JSON データの参照更新のための各種 SQL 関数を実装。MySQL 8.0 では JSON データを SQL で分析するための変換関数も追加リレーショナルなテーブルと非構造データとシームレスに統合。さらに MySQL 8.0 では更新性能の最適化{ } ();

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |mysql> SELECT id, doc FROM seats WHERE id = 28100G*************************** 1. row ***************************id: 28100doc: {"row": 10, "seat": 13, "section": 215, "properties":{"amenities": [{"type": "washroom", "distance_in_meters":38.564358156700024}, {"type": "bar", "distance_in_meters":152.33173722618423}, {"type": "snacks", "distance_in_meters":35.965617807550004}, {"type": "souvenirs", "distance_in_meters":215.66576701185272}], "accessible": false, "emergency_exits":[{"exit 1": 100.66892563427699}, {"exit 2": 374.19603448751946},{"exit 3": 563.9332987311606}, {"exit 4": 886.7355222969646},{"exit 5": 1900.9778593955355}], "entrance_number": 2}}1 row in set (0.00 sec)36

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |JSON Functions37MySQL 5.7 and 8.0JSON_ARRAY_APPEND()JSON_ARRAY_INSERT()JSON_ARRAY()JSON_CONTAINS_PATH()JSON_CONTAINS()JSON_DEPTH()JSON_EXTRACT()JSON_INSERT()JSON_KEYS()JSON_LENGTH()JSON_MERGE[_PRESERVE]()JSON_OBJECT()JSON_QUOTE()JSON_REMOVE()JSON_REPLACE()JSON_SEARCH()JSON_SET()JSON_TYPE()JSON_UNQUOTE()JSON_VALID()JSON_PRETTY()JSON_STORAGE_SIZE()JSON_STORAGE_FREE()JSON_ARRAYAGG()JSON_OBJECTAGG()JSON_MERGE_PATCH()JSON_TABLE() *labs

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |【例】 JSON_TABLE()SELECT * FROM seats,JSON_TABLE(doc, "$.properties.amenities[*]" COLUMNS (id for ordinality,amenity_type VARCHAR(100) PATH "$.type",distance float PATH '$.distance_in_meters')) AS amenitiesWHERE seats.id = 28100AND amenities.amenity_type IN ('snacks', 'bar')ORDER BY amenities.distance;+------+------------------+------------+| id | amenity_type | distance |+------+------------------+------------+| 2 | bar | 100.538 || 3 | snacks | 136.647 |+------+------------------+------------+2 rows in set (0.00 sec)39Labs

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |コネクター, ドライバー, プロトコル拡張機能MySQLPluginsX Protocol Plugin Memcached PluginCoreMySQL Connectors and DriversX ProtocolStd ProtocolMemcacheddriverX Protocol33060Std Protocol3306SQL API CRUD and SQL APIsMemcacheProtocolX and StdProtocolsMySQLShell40+-------------+----------------+--------------------+| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_DESCRIPTION |+-------------+----------------+--------------------+| mysqlx | 1.0 | X Plugin for MySQL |+-------------+----------------+--------------------+

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |X DevAPI• X Pluginを有効にする事で、X Protocol経由で通信可能• ドキュメントとテーブルのコレクションに対してのCRUD処理• NoSQLライクな構文でドキュメントに対しCRUD処理可能• Fluent APIprod = sess.getSchema("prod")res = prod.users.find("$.name = 'Milk'").fields(["name", "properties"])X Plugin (MySQL) ⇔ X Protocol ⇔ X DevAPI (Driver)41 MySQL Connector/node.js (1.0.x) MySQL Connector/J (6.0.x) MySQL Connector/Net (7.0.x) MySQL Connector/python(2.2.x) MySQL Shell (1.0.x)参照： http://dev.mysql.com/downloads/connector/

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL Connectors include X Dev API• Use SQL, CRUD APIsスキーマレスドキュメントおよびリレーショナルテーブルに対応- Classic APIsに加えて、これらの全てが追加されます42Operation Document RelationalCreate Collection.add() Table.insert()Read Collection.find() Table.select()Update Collection.modify() Table.update()Delete Collection.remove() Table.delete()参照) http://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL 8.0 : データ分析処理の効率向上Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Window 関数共通テーブル式 (CTEs)44• サブクエリの導出表 (derived table) の代替• WITH 句と呼ばれることも• 分析処理 SQL 文の可読性や処理性能の向上、階層構造データ利用にも• ランキング作成などの分析処理用途でユーザーからの追加要望の多かった機能• 検索対象のレコードと周辺データとの関連を集計や分析WITH tickets_filtered AS (SELECT tickets.*, seats.docFROM ticketsINNER JOIN seats ONtickets.seat_id = seats.idWHERE tickets.event_id = 3)SELECT * FROM tickets_filteredWHERE doc->"$.section" = 201GSELECT name, dept_id, salary,RANK() OVER w AS `rank`FROM employeeWINDOW w AS(PARTITION BY dept_idORDER BY salary DESC);

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |WITH tickets_filtered AS (SELECT tickets.*, seats.docFROM ticketsINNER JOIN seats ON tickets.seat_id=seats.idWHERE tickets.event_id = 3)SELECT * FROM tickets_filteredWHERE doc->"$.section" = 201G*************************** 1. row***************************id: 14447event_id: 3seat_id: 16430order_id: NULLdoc: {"row": 2, "seat": 1, "section": 201,"properties": {"amenities": [{"type": "washroom","distance_in_meters": 171.80304788220957}, {"type":"bar", "distance_in_meters": 58.53288591702737},{"type": "snacks", "distance_in_meters":..45

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |DBT-3 Query 15024681012141618View CTEQueryExecutionTime(seconds)46クエリーパフォーマンスビューの場合, 2回マテリアライズされ,CTEは1回のみマテリアライズされるため,クエリの実行時間は半分に短縮されます。ex: revenue0 on P.17サブクエリの場合、同じ処理が複数回発生:SELECT ...FROM (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d1 JOIN (SELECT a, b, SUM(c) sFROM t1 GROUP BY a, b) AS d2 ON d1.b = d2.a;共通テーブル式の場合:WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) SELECT ... FROM d AS d1 JOIN d AS d2ON d1.b = d2.a;

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Window関数とは？• Window関数は、現在の行に関連する行セットについて、集計関数と同様に計算を行える• 集計関数のように行を単一の出力にグループ化するのではなく、複数行を出力する• Window関数は現在の行の近くの行（関連する行）にアクセスできる47集計関数 Window関数

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Window関数: RANK49SELECT name, dept_id AS dept, salary,RANK() OVER w AS `rank`FROM employeeWINDOW w AS (PARTITION BY dept_idORDER BY salary DESC);name dept_id salary rankNewt NULL 75000 1Ed 10 100000 1Newt 10 80000 2Fred 10 70000 3Michael 10 70000 3Jon 10 60000 5Dag 10 NULL 6Pete 20 65000 1Lebedev 20 65000 1Jeff 30 300000 1Will 30 70000 2

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |SELECTseats.doc->"$.row" as row_no,seats.doc->"$.seat" as seat_no,LAG(seats.doc->"$.seat", 1) OVER w AS prev_seat,LEAD(seats.doc->"$.seat", 1) OVER w AS next_seatFROM ticketsJOIN seats ON tickets.seat_id=seats.idWHERE seats.doc->"$.section" = 201WINDOW w AS(PARTITION BY seats.doc->"$.row"ORDER BY seats.doc->"$.seat")ORDER BY row_no, seat_no;+----------+-----------+--------------+-------------+| row_no | seat_no | prev_seat | next_seat |+----------+-----------+--------------+-------------+| 1 | 1 | NULL | 2 || 1 | 2 | 1 | 3 || 1 | 3 | 2 | 4 |+----------+-----------+--------------+-------------+50

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |パフォーマンススキーマデフォルトで取得する性能統計情報の項目を拡張。パフォーマンススキーマへの参照性能向上不可視インデックスオプティマイザーからインデックスを隠蔽。インデックスを残した仮削除や段階的なインデックス追加を実現アクセス集中時の対応改善SELECT FOR UPDATE 文のNOWAIT や SKIP LOCKEDオプションによるロック解放待ち削減MySQL 8.0 : アプリケーションの性能拡張性向上Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 51ヒント句の拡張SQL文本体のテーブル指定順はそのままにJOIN順序やインデックスマージ対象の指定が可能に降順インデックス昇順と降順の複合インデックス利用時にも後方索引スキャンをしないためより高速にヒント句によるセッション変数変更新しいヒント句の SET_VAR により一つのSQL文内でセッション変数を一時的に変更

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 52New! Better Handling of Hot Row ContentionSELECT * FROM ticketsWHERE id IN (1,2,3,4)AND order_id IS NULLFOR UPDATENOWAIT;SELECT * FROM ticketsWHERE id IN (1,2,3,4)AND order_id IS NULLFOR UPDATESKIP LOCKED;Error immediately ifa row is alreadylockedNondeterministicallyskip over lockedrows

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |不可視インデックス (Invisible Indexes)• オプティマイザーから見えない索引– 索引の無効化とは異なる– データ更新時にInvisible Indexesも更新される• 2つのユースケース:– 仮削除(ゴミ箱)– 段階的な展開にてインデックスの有効性の確認53WL#8697: Support for INVISIBLE indexeshttps://dev.mysql.com/worklog/task/?id=8697

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Performance Schema Indexes• パフォーマンススキーマのテーブルへのより効率的なアクセスが可能• 89個のテーブルに対し合計90個のインデックス• オーバーヘッドを削減– 物理インデックスは内部的には維持されません– 索引の実装により、オプティマイザがより良い実行計画を選択540. 10. 20. 30. 40.Query Time MySQL 8.0MySQL 5.7SELECT * FROM sys.session 1000 active sessionsTime in Seconds (Lower is better)Over 30x faster!WL#6616: PERFORMANCE_SCHEMA, INDEXEShttps://dev.mysql.com/worklog/task/?id=6616

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 55【例】 Performance Schema Indexesmysql> SELECT * FROM variables_by_thread IGNORE INDEX (primary)WHERE thread_id = 34 AND variable_name = 'time_zone';+-----------+---------------+----------------+| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |+-----------+---------------+----------------+| 34 | time_zone | SYSTEM |+-----------+---------------+----------------+1 row in set (0.00 sec)mysql> show status like 'Handler_read_%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 0 || Handler_read_last | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 136 |+-----------------------+-------+7 rows in set (0.01 sec)mysql> SELECT * FROM variables_by_threadWHERE thread_id = 34 AND variable_name = 'time_zone';+-----------+---------------+----------------+| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |+-----------+---------------+----------------+| 34 | time_zone | SYSTEM |+-----------+---------------+----------------+1 row in set (0.00 sec)mysql> show status like 'Handler_read_%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+-----------------------+-------+7 rows in set (0.01 sec)Handler_read_rnd_next: The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans.Without Index“Handler_read_rnd_next”が増加しているWith Index“Handler_read_rnd_next”は0のまま増加していない

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Performance Schema Instrumenting SQL Errors56Aggregation Table NameBy Account events_errors_summary_by_account_by_errorBy Host events_errors_summary_by_host_by_errorBy Thread events_errors_summary_by_thread_by_errorBy User events_errors_summary_by_user_by_errorGlobal events_errors_summary_global_by_error

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |mysql> select * from performance_schema.events_errors_summary_by_user_by_error where FIRST_SEEN is not NULL;+------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+| USER | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |+------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+| NULL | 1045 | ER_ACCESS_DENIED_ERROR | 28000 | 1 | 0 | 2016-10-27 15:57:16 | 2016-10-27 15:57:16 || root | 1046 | ER_NO_DB_ERROR | 3D000 | 1 | 0 | 2016-10-27 16:00:37 | 2016-10-27 16:00:37 || root | 1049 | ER_BAD_DB_ERROR | 42000 | 1 | 0 | 2016-10-27 18:21:09 | 2016-10-27 18:21:09 || root | 1064 | ER_PARSE_ERROR | 42000 | 15 | 0 | 2016-10-27 15:58:01 | 2016-10-27 18:24:06 || root | 1146 | ER_NO_SUCH_TABLE | 42S02 | 1 | 0 | 2016-10-27 16:08:03 | 2016-10-27 18:14:41 || root | 1287 | ER_WARN_DEPRECATED_SYNTAX | HY000 | 24 | 0 | 2016-10-27 16:07:10 | 2016-10-27 16:07:10 || root | 3554 | ER_NO_SYSTEM_TABLE_ACCESS | HY000 | 140 | 0 | 2016-10-27 15:57:30 | 2016-10-27 18:38:09 |+------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+mysql> SELECT * FROM performance_schema.events_errors_summary_global_by_error WHERE sum_error_handled > 0 OR SUM_ERROR_RAISED > 0;+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+| ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+| 1049 | ER_BAD_DB_ERROR | 42000 | 3 | 0 | 2017-07-07 14:35:47 | 2017-07-07 14:36:49 || 1054 | ER_BAD_FIELD_ERROR | 42S22 | 1 | 0 | 2017-07-07 08:20:04 | 2017-07-07 08:20:04 || 1062 | ER_DUP_ENTRY | 23000 | 1 | 0 | 2017-07-07 13:30:58 | 2017-07-07 13:30:58 || 1064 | ER_PARSE_ERROR | 42000 | 6 | 0 | 2017-07-07 07:49:59 | 2017-07-07 14:36:08 || 1146 | ER_NO_SUCH_TABLE | 42S02 | 1 | 0 | 2017-07-07 13:30:38 | 2017-07-07 14:37:33 || 1287 | ER_WARN_DEPRECATED_SYNTAX | HY000 | 7 | 0 | 2017-07-07 11:41:03 | 2017-07-07 13:39:42 || 1305 | ER_SP_DOES_NOT_EXIST | 42000 | 4 | 0 | 2017-07-07 12:44:54 | 2017-07-07 13:30:11 || 1411 | ER_WRONG_VALUE_FOR_TYPE | HY000 | 9 | 0 | 2017-07-07 12:45:00 | 2017-07-07 12:45:16 || 3568 | ER_UNRESOLVED_TABLE_LOCK | HY000 | 4 | 0 | 2017-07-07 13:44:45 | 2017-07-07 13:46:28 |+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+【例】パフォーマンススキーマSQLエラーの計測SELECT * FROM test.no_table;ERROR 1146 (42S02): Table 'test.no_table' doesn't exist57存在しないテーブルへの参照エラー発生

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |パフォーマンススキーマヒストグラムmysql> WITH total as-> (SELECT SUM(count_bucket) as t FROM performance_schema.events_statements_histogram_global)-> SELECT sys.decimal_bucket_name(sys.decimal_bucket(MIN(bucket_timer_low))) as bucket,-> sys.visualization(SUM(count_bucket) / (SELECT t FROM total), 50) as visualization,-> SUM(count_bucket) as count-> FROM performance_schema.events_statements_histogram_global-> GROUP BY sys.decimal_bucket(bucket_timer_low);+---------+---------------------------------+--------+| bucket | visualization | count |+---------+---------------------------------+--------+| 0us+ | ############################### | 439148 || 10us+ | ############ | 163434 || 100us+ | | 1517 || 1ms+ | ####### | 99453 || 10ms+ | | 369 || 100ms+ | | 29 || 1s+ | | 2 || 10s+ | | 1 |+---------+---------------------------------+--------+8 rows in set (0.04 sec)58例: mysqlslap実行時のクエリー実行時間の分布WL#5384: PERFORMANCE_SCHEMA Histogramshttps://dev.mysql.com/worklog/task/?id=5384events_statements_histogram_globalとCTEを利用して実行時間の分布をビジュアライズした例

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |パフォーマンススキーマの拡張• どのデータがロックされているか？誰がロックを所有しているか？誰がデータを待っているか？ロック発生状況の確認SELECT thread_id, object_name, index_name, lock_type, lock_mode, lock_dataFROM performance_schema.data_locks WHERE object_name = 'seats';+-----------+-------------+------------+---------------+-----------+-------------+| thread_id | object_name | index_name | lock_type | lock_mode | lock_data |+-----------+-------------+------------+---------------+-----------+-------------+| 33 | seats | NULL | TABLE | IX | NULL || 33 | seats | PRIMARY | RECORD | X | 3, 5 || 33 | seats | PRIMARY | RECORD | X | 3, 6 || 33 | seats | PRIMARY | RECORD | X | 4, 5 || 33 | seats | PRIMARY | RECORD | X | 4, 6 |+-----------+-------------+------------+---------------+-----------+-------------+WL#6657: PERFORMANCE_SCHEMA, DATA LOCKShttps://dev.mysql.com/worklog/task/?id=6657WL#9275: DEPRECATE INFORMATION_SCHEMA.INNODB_LOCKS IN 5.7https://dev.mysql.com/worklog/task/?id=9275ロックされているデータを表示

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |オプティマイザヒントの拡張• SET_VARヒント– SQL単位でシステム変数を変更できるヒント– セッション単位で変更可能なシステム変数をSQL単位で変更可能に（max_allowed_packetなど一部のセッション変数は変更不可）61SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=ON') */ name,region FROMcountry WHERE region='Eastern Asia';使用例

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |降順索引 (Descending Indexes)CREATE TABLE t1 (a INT,b INT,INDEX a_b (a DESC, b ASC));• 5.7: 昇順インデックスが作成され,サーバーがそれを逆方向にスキャンします• 8.0: 降順でインデックスが作成され,サーバはそれをフォワードスキャンします• メリット:– 前方索引スキャンは後方索引スキャンより高速– ASC / DESCソートキーでORDER BYにてfilesortの代わりにインデックスを使用可For B+tree indexes

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |リソースグループスレッドとCPUのマッピングを行うことにより処理効率と性能向上を図るクラウドフレンドリーな設定永続化SET PERSIST コマンドにより、SQLインターフェースからの設定変更を永続化。どこから変更された設定値かを確認するテーブルも追加InnoDB専用サーバー構成仮想マシンやクラウド環境の構成にあわせてInnoDBの最適なパラメタを自動的に設定MySQL 8.0 : MySQLサーバーの性能拡張性向上Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 64トランザクションスケジューリング“Contention-Aware TransactionScheduling”がInnoDBのデフォルトのスケジューリングアルゴリズムとなり性能が劇的に向上コスト見積もりの最適化最新のストレージ技術への対応やデータのキャッシュ状況に応じたオプティマイザーでの実行計画カラムヒストグラムインデックスが設定されていない列の統計情報をオプティマイザーに提供し実行計画をさらに改善

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |InnoDB専用サーバーの自動構成• システムメモリー量を確認し、ログファイルサイズ、バッファプールサイズ、フラッシュメソッドを自動的に調整• 使用方法65SET PERSIST_ONLY innodb_dedicated_server = TRUE参照: http://mysqlserverteam.com/plan-to-improve-the-out-of-the-box-experience-in-mysql-8-0/WL#9193: Autoscale InnoDB resources based on system resources by defaulthttps://dev.mysql.com/worklog/task/?id=9193

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |InnoDB専用サーバーの自動構成• MySQL 8.0.3での設定内容66[innodb_buffer_pool_size]server_memory < 1G ? 128M (現在のデフォルトと同じ)server_memory <= 4G ? server_memory * 0.5server_memory > 4G ? server_memory * 0.75[innodb_log_file_size]server_memory < 1G ? 48M (現在のデフォルトと同じ)server_memory <= 4G ? 128Mserver_memory <= 8G ? 512Mserver_memory <= 16G ? 1024Mserver_memory > 16G ? 2048M[innodb_flush_method]O_DIRECT_NO_FSYNC

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |設定変更の永続化• 以下の構文でシステム変数の変更を永続化可能– SET PERSIST max_connections = 500;– SET PERSIST_ONLY innodb_log_file_size = 128*1024*1024;• システム変数変更のためにファイルシステムへのアクセス不要• いつ、誰に設定されたかを確認出来る情報も追加67mysql> SET PERSIST log_timestamps='SYSTEM';Query OK, 0 rows affected (0.00 sec)mysql> select * from performance_schema.variables_info where variable_source='PERSISTED';+----------------+-----------------+--------------------------------+-----------+-----------+---------------------+----------+-----------+| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |+----------------+-----------------+--------------------------------+-----------+-----------+---------------------+----------+-----------+| log_timestamps | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2017-10-14 14:48:28 | root | localhost |+----------------+-----------------+--------------------------------+-----------+-----------+---------------------+----------+-----------+WL#8688: Support ability to persist SET GLOBAL settingshttps://dev.mysql.com/worklog/task/?id=8688#cat /var/lib/mysql/mysqld-auto.cnf{ "mysql_server": {"log_timestamps":"SYSTEM" } }

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |リソースグループshell> cat /proc/cpuinfo | grep processorprocessor : 0processor : 1mysql> CREATE RESOURCE GROUP CPU1 TYPE=USER VCPU=1;Query OK, 0 rows affected (0.24 sec)mysql> SELECT * from INFORMATION_SCHEMA.RESOURCE_GROUPS;+---------------------+---------------------+------------------------+----------+-----------------+| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |+---------------------+---------------------+------------------------+----------+-----------------+| USR_default | USER | 1 | 0-1 | 0 || SYS_default | SYSTEM | 1 | 0-1 | 0 || CPU0 | USER | 1 | 0 | 0 || CPU1 | USER | 1 | 1 | 0 |+---------------------+---------------------+------------------------+----------+-----------------+4 rows in set (0.00 sec)mysql> SET RESOURCE GROUP CPU0;select "This user connection will use Processor 0 Only";mysql> SELECT /*+ RESOURCE_GROUP(CPU0) */ "This user connection will use Processor 0 Only";68リソースグループを使用すると、そのリソースの制御が可能になり、グループ内のスレッドによるリソース消費を有効または制限できます。 DBAは、さまざまな作業負荷に応じてこれらの属性を変更できます。WL#9467: Resource Groupshttps://dev.mysql.com/worklog/task/?id=9467

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |【例】リソースグループパフォーマンス69040,00080,000120,000160,000No Resource Group With Resource GroupQueriesperSecondSelectUpdateSystem Configuration :Oracle Linux 7,Intel(R) Xeon(R) CPU E7-4860 2.27GHz40 cores-HT (40 Cores Shared) (40 Cores for Select)(10 Cores for Update RG)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |ヒストグラム• データが偏っている場合のクエリーの精度向上• ヒストグラムはインデックスを作成よりコストが低い• ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS;70WL#8706: Persistent storage of Histogram datahttps://dev.mysql.com/worklog/task/?id=8706WL#8707: Classes/structures for Histogramshttps://dev.mysql.com/worklog/task/?id=8707WL#8943: Extend ANALYZE TABLE with histogram supporthttps://dev.mysql.com/worklog/task/?id=8943mysql> SELECT * FROM events_statements_histogram_by_digest WHERE SCHEMA_NAME ='sakila'-> AND DIGEST = 'a5980f0634db05c87a7aeb17e1344f84' AND COUNT_BUCKET > 0 limit 1G*************************** 1. row ***************************SCHEMA_NAME: sakilaDIGEST: a5980f0634db05c87a7aeb17e1344f84BUCKET_NUMBER: 153BUCKET_TIMER_LOW: 10964781961BUCKET_TIMER_HIGH: 11481536214COUNT_BUCKET: 1COUNT_BUCKET_AND_LOWER: 1BUCKET_QUANTILE: 0.500000Statement Histogram Summary Tablesクエリの50％は11.48マイクロ秒未満で実行

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |新しいオプティマイザー・コストモデル•バッファプールのヒット率を意識した改善71Model for a table scan:# pages in table *(IO_BLOCK_READ_COST |MEMORY_BLOCK_READ_COST)# records * ROW_EVALUATE_COST= 25.4 100% in memory= 29.9 100% on diskModel for a range scan:# records_in_range *(IO_BLOCK_READ_COST |MEMORY_BLOCK_READ_COST)# records_in_range *ROW_EVALUATE_COST + #records_in_range *ROW_EVALUATE_COST= 22.5 100% in memory= 60 100% on diskWL#7093: Optimizer provides InnoDB with a bigger bufferhttps://dev.mysql.com/worklog/task/?id=7093この例では、全データがメモリ上にある場合にはレンジスキャンの方がコストが低い※IOブロックリードコストのデフォル値・ディスク上：1・メモリ上：0.25データがメモリ上にある/ないによって、レンジスキャンのパフォーマンスの差が大きい。INNODB_CACHED_INDEXESからヒット率を判断し、適切な実行計画を選択SELECT * FROM Country WHERE population > 20000000;

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |0300,000600,000900,0001,200,0001 2 4 8 16 32 64 128 256 512QueriesperSecondUsersMySQL 8.0 LabsMySQL 5.7MySQL 5.6Intel(R) Xeon(R) Platinum 8168 CPU2CPU-sockets, 48cores-HT2x Intel OptaneOracle Linux 7.31,000,000+ QPSSysbench: IO-bound OLTP RO Point-Selects

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |040,00080,000120,000160,000200,0001 2 4 8 16 32 64 128 256 512QueriesperSecondUsersMySQL 8.0 LabsMySQL 5.7MySQL 5.6Intel(R) Xeon(R) Platinum 8168 CPU2CPU-sockets, 48cores-HT2x Intel OptaneOracle Linux 7.3MySQL8.0では,参照のみで無く更新処理のパフォーマンス改善に注力Sysbench: IO-bound OLTP RW Updates-only

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |セキュリティ強化ロール & Dynamic Privileges機能追加。透過的データ暗号化を拡張。データディクショナリメタデータ管理を InnoDB のテーブルで一元管理。メタデータの一貫性と信頼性向上MySQL InnoDB ClusterMySQL 標準の高可用性パッケージ。自動フェールオーバー & リカバリ、矛盾検知MySQL 8.0 : アプリケーションのセキュリティ&可用性強化Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 74

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL InnoDB Clusterとは？• 以下のコンポーネントの組み合わせから構成されるMySQLの高可用性フレームワーク– MySQL Group Replication ： DBの読み取り拡張性、自動フェイルオーバーを提供– MySQL Router ：アプリ接続先の自動フェイルオーバーを提供– MySQL Shell ：グループ・レプリケーション環境の構築、設定、Routerの設定• 2017年4月12日 GA– 以下の製品を個別にインストールすることで使用可能• MySQL 5.7.19 (2017-07-17)• MySQL Router 2.1.4 (2017-07-24)• MySQL Shell 1.0.10 (2017-07-28)75

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL ConnectorApplicationMySQL RouterMySQL ConnectorApplicationMySQL RouterMySQL ShellMySQL ConnectorApplicationMySQL RouterMySQL ConnectorApplicationMySQL RouterMySQLInnoDBclusterMySQL Enterprise Monitor…mysql-js> cluster.status(){"clusterName": “NewAppCluster","defaultReplicaSet": {"status": "Cluster tolerant to up to ONE failure.","topology": {“hanode1:3306": {"address": “hanode1:3306","status": "ONLINE","role": "HA","mode": "R/W",MySQL InnoDB Cluster: 全体アーキテクチャー77

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL InnoDB Cluster: 内部アーキテクチャー78MM MMySQL ConnectorApplicationMySQL RouterMySQL ConnectorApplicationMySQL RouterMySQL ShellHAGroup ReplicationアプリケーションはMySQL Routerに接続Group Replicationによる高可用性グループとメタデータの管理MySQL Shellで設定管理が可能+-------------------------------+| Database |+-------------------------------+| information_schema || mysql || mysql_innodb_cluster_metadata || performance_schema || sys |+-------------------------------+mysql_innodb_cluster_metadata.*performance_schema.replication_group_membersperformance_schema.replication_group_member_stats

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL Roter 2.1• 透過的にクライアント接続をルーティング– ロードバランシング– アプリケーション接続フェイルオーバー• InnoDB Clusterをネイティブサポート– グループレプリケーションの構成を認識可能– InnoDB Clusterへのルーディングを動的にコントロール可能– マルチマスターモード及びシングルマスターモードをサポート79透過的にアプリケーション接続をルーティングApp Servers withMySQL RouterMySQL Group ReplicationMySQL ShellSetup, Manage,Orchestrate

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL Shell80すべての運用管理タスクのための、統一された単一クライアントApp Servers withMySQL RouterMySQL Group ReplicationMySQL ShellSetup, Manage,Orchestrate• 多言語対応: JavaScript, Python, and SQL• ドキュメントとリレーショナルモデルの両方をサポート• 開発と管理用に完全なAPIを提供”MySQL Shell provides the developer and DBA with a single intuitive,flexible, and powerfull interface for all MySQL related tasks!”[root@misc01 admin]# mysqlsh --help | egrep -i "Start in"--sql Start in SQL mode using a node session.--sqlc Start in SQL mode using a classic session.--js Start in JavaScript mode.--py Start in Python mode.[root@misc01 admin]#

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 81今後の拡張機能MySQL HAOut-Of-Box HARead Scale-OutAsync Replication + Auto FailoverWrite Scale-OutShardingStep1Step2Step3* InnoDB Cluster Now GA!

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |S1 S2 S3 S4 S…MM MMySQL ConnectorApplicationMySQL RouterMySQL ConnectorApplicationMySQL RouterMySQL ShellHAGroup ReplicationRead-Only Slaves82Group Replicationに参照用のスレーブを接続する事が可能。+ 参照先のノードがダウンした場合、自動的に稼働中の他のグループメンバーを参照MySQL InnoDB Cluster: Architecture – Step 2

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |S1 S2 S3 S4 S…MM MMySQL ConnectorApplicationMySQL RouterMySQL ConnectorApplicationMySQL RouterMySQL ShellHAReplicaSet1S1 S2 S3 S4 S…MM MMySQL ConnectorApplicationMySQL RouterHAReplicaSet2ReplicaSet3MySQL ConnectorApplicationMySQL RouterS1 S2 S3 S4MM MHA83Groupを分ける事による、書き込みのシャーディングMySQL InnoDB Cluster: Architecture – Step 3

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Transactional Data Dictionary• 信頼性の向上• InnoDB内部にデータディクショナリーを格納– No FRM files– No DB.OPT files– No TRG files– No TRN files– No PAR files• MyISAMテーブルは含まれなくなりました84WL#6379: Schema definitions for new DDhttps://dev.mysql.com/worklog/task/?id=6379WL#6392: Upgrade to Transactional Data Dictionaryhttps://dev.mysql.com/worklog/task/?id=6392WL#6394: Bootstrap code for new DDhttps://dev.mysql.com/worklog/task/?id=6394 and more[root@DockerHost oracle]# ls -l /docker/docker802/worldtotal 1084-rw-r----- 1 27 27 638976 Jul 18 01:25 city.ibd-rw-r----- 1 27 27 196608 Jul 18 01:25 country.ibd-rw-r----- 1 27 27 262144 Jul 18 01:25 countrylanguage.ibd8.0SQLSQL8.05.7

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Transactional Data Dictionary• 柔軟な異種OSとの互換性の向上– ファイルシステムのセマンティクスに依存しない• アトミックDDL– レプリケーションの改善、サーバーのエッジケースの簡素化• 外部キーのMDL（メタデータロック）• 柔軟なメタデータAPI– 新しい機能の容易な追加85Ver Delete Tables Delete Stored Programs Delete Schema ATOMICITY5.7 - Metadata, TRN/TRG/FRMfiles- Data, InnoDB tables- Metadata, rows in MyISAM(non-transactional)- Metadata, DB.OPT file Mix of filesystem,non-transactional/transactionalstorage and multiple commits8.0 - Metadata, rows in InnoDB- Data, InnoDB tables- Metadata, rows in InnoDB - Metadata, rows inInnoDBUpdates to transactionalstorage, one commit

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Information Schema Performance•100 schemas times 50 tables (5000 tables)860. 1. 2. 3. 4.Count All SchemasSchema aggregate size statsAll Dynamic Table InfoAll Static Table InfoAuto Increments Near LimitCount All ColumnsCount All IndexesMySQL 8.0MySQL 5.7Time in Seconds (Lower is better)独自のテストで7/10のクエリがより高速化！X30 FAST: SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,ROW_FORMAT FROM information_schema.tables WHERE TABLE_SCHEMA LIKE ‘db%';

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |MySQL 8.0 : セキュリティの強化Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 87SQLロールの実装Easier to manage user and applicationsrights and SQL standard compliantメタデータ変更がアトミックにNew InnoDB based data dictionary enablesACL statements atomic and reliableDynamic PrivilegesProvides finer grained administrative levelaccess controls for less use of root userログファイルの透過的暗号化AES 256 encryption of REDO, UNDO and BinaryLog in addition to tablespace filesパスワード管理強化Establish password-reuse policy with PasswordHistory, and faster with cachingOpenSSL IntegrationMySQL Community Edition to use OpenSSL as itsstandard cryptography library

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |ユーザーロールMySQLアクセスコントロールの改善• 8.0.0 DMRで導入されました (5.7: PROXY USER)• ユーザーとアプリケーションの権限管理を容易に• ユーザー/ロールに対してロールの付与• デフォルトロールを定義• ROLES_GRAPHML()関数でロール確認88DirectlyIn directlySet Role(s)Default Role(s)Set ofACLSSet ofACLSmysql> select user(),current_role();+------------------+----------------+| user() | current_role() |+------------------+----------------+| user01@localhost | `role80`@`%` |+------------------+----------------+WL#988: Roleshttps://dev.mysql.com/worklog/task/?id=988

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |アトミック ACLステートメント• 長年にわたるMySQLの課題！– レプリケーション、HA、バックアップ等• ACLテーブルをInnoDB Data Dictionaryに格納• テーブル操作だけでなく、メモリキャッシュも更新が必要• 複数の論理演算を実行するステートメントに適用されます例:– CREATE USER u1, u2– GRANT SELECT ON *.* TO u1, u2• ACLのキャッシュとテーブルを変更時– カスタムMDLロックを使用してACL関連のアクティビティをブロック90WL#9045: Make user management DDLs atomichttps://dev.mysql.com/worklog/task/?id=9045+--------------+------------+--------+| TABLE_SCHEMA | TABLE_NAME | ENGINE |+--------------+------------+--------+| mysql | user | InnoDB |+--------------+------------+--------+

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |InnoDB Redo/Undo 暗号化• AES 256 暗号化• Redo/Undoログがディスクに書き出し時に暗号化される• Redo/Undoログがディスクから読み出し時に複合される• メモリ上ではRedo/Undoログデータは暗号化されていない• InnoDB表領域暗号化と同様の2層暗号化鍵管理– 鍵のローテーションが高速、高パフォーマンス• 容易に使用可能– システム変数 innodb_redo_log_encrypt, innodb_undo_log_encrypt で制御91WL#9289: InnoDB: Support Transparent Data Encryption for Undo Tablespaceshttps://dev.mysql.com/worklog/task/?id=9289WL#9290: InnoDB: Support Transparent Data Encryption for Redo Loghttps://dev.mysql.com/worklog/task/?id=9290

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |パスワード強化• New! パスワード履歴 - より幅広いセキュリティポリシーに対応– Require new passwords not reuse old ones - By number of changes and/or time.– Establish password-reuse policy globally as well as on a per-account basis.• New! キャッシュ付きHA2– Strong and Fast– Strong - SHA-256 password hashing (many rounds, seeds, …)– Fast - Caching• Greatly reduces latency• New! より多くのプロトコルのサポート• New! シームレスなRSAパスワード交換 (OpenSSLのリンク不要)92

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |New! Dynamic PrivilegesProvides finer grained administrative level access controls• Too often super is required for tasks when less privilege is really needed– Support concept of “least privilege”• Needed to allow adding administrative access controls– Now can come with new components– Examples• Replication• HA• Backup• Give us your ideas93

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |Why Dynamic Global Privileges?• How to add a new global privilege (the 5.7 version)– Add a column in mysql.user– Extend the parser– Amend ACL cache code: reading, caching, writing, upgrade, …– Add checks for the new privilege• Not possible from a plugin !• Abuse of existing privileges (SUPER) !• The SUPER-potent SUPER !94Feature Requestfrom DBAs

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |How Do Dynamic Privileges Work ?• Provides new component service– Can add, remove and check global privileges• Only GRANTs are persisted– Stored in mysql.global_grants• Uses the familiar– GRANT <dynamic_acl> ON *.* TO … syntax95

[中国地方DB勉強会] 第22回 Webアプリ開発をデータベース側から変革していく - MySQL 8.0新機能

Movatterモバイル変換

Change Language

[中国地方DB勉強会] 第22回 Webアプリ開発をデータベース側から変革していく - MySQL 8.0新機能

Embed presentation

Recommended

More Related Content

What's hot

Similar to [中国地方DB勉強会] 第22回 Webアプリ開発をデータベース側から変革していく - MySQL 8.0新機能

More from Ryusuke Kajiyama

[中国地方DB勉強会] 第22回 Webアプリ開発をデータベース側から変革していく - MySQL 8.0新機能