What's New in MySQL 8.0

1 .What's New in MySQL 8.0 NoSQL + SQL = MySQL Geir Hoydalsvik, MySQL Engineering Copyright Copyright©©2018, 2018,Oracle Oracleand/or and/oritsitsaffiliates. affiliates.All Allrights rightsreserved. reserved.| 

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

3 . GA MySQL Innovation: 5.7 -> 8.0 MySQL 8.0 - 2x Better Performance - NoSQL Document Store MySQL InnoDB Cluster - JSON - MySQL Group Replication - CTEs - MySQL Router - Window Functions MySQL 5.7 - MySQL Shell - Data Dictionary - 3x Better Performance - InnoDB Redo Log - Replication Enhancements - Instant Add Column - Optimizer Cost Model - Replication - JSON Support - Roles - Improved Security - Unicode - Sys & Performance Schema 2 Years in Development - GIS 400+ Worklogs 5000+ Bugs Fixed 500 New Tests Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 

4 . DMR MySQL NDB Cluster 8.0 NDB 8.0 - Adopting data dictionary - Thousands of nodes NDB 7.6 - 3 - 4 replicas - MySQL Server 5.7 - Larger rows - Redesigned for many Terabyte NDB 7.5 - Redesigned for modern hardware - MySQL Server 5.7 - 50% faster joins - 5x faster restarts - 100% faster scans - JSON Support - Another 10x faster restarts - 50% faster reads - 40% faster read/write Download and test NDB 8.0.13 now https://dev.mysql.com/downloads/cluster/ Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 

5 .Agenda • Open Source & MySQL Community • Product alignment in 8.0 • Continious Delivery Model • NoSQL + SQL = MySQL • Document Store, NoSQL • Developer productivity, SQL, JSON, GIS • Developer experience, MySQL Shell • HA “out of the box”, InnoDB Cluster • DevOps productivity Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 5 

6 .MySQL 8.0: Document Store NoSQL + SQL = MySQL • Document oriented data storage for MySQL – Full JSON document support through SQL and new X DevAPI NoSQL interface • Schema-less and schema based data in the same technology stack – Use COLLECTIONs of documents & relational TABLEs together • Rapid Prototyping & Simple CRUD APIs – Modern APIs using “method chaining” and asynchronous execution (e.g. promises, callbacks, etc.) • Connectors for many different languages and frameworks – Node.JS, Java, NET, C++/C, PHP, Python Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 6 

7 .MySQL 8.0: Document Store Designed for modern Developers • Full Node.js integration – Support for “Promises” • Autocompletion support in IDEs – Due to method chaining support • Intuitive Documentation & Tutorials – Example: COLLECTION.add Function Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 7 

8 .MySQL 8.0: One Giant Leap for SQL “This is a landmark release as MySQL eventually evolved beyond SQL-92 and the purely relational dogma. Among a few other standard SQL features, MySQL now supports window functions (over) and common table expressions (with). Without a doubt, these are the two most important post- SQL-92 features.” https://modern-sql.com/blog/2018-04/mysql-8.0 Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 

9 .MySQL 8.0.13: Functional Indexes • Index over an expression CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); CREATE INDEX idx1 ON t1 ((col1 + col2)); CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1); ALTER TABLE t1 ADD INDEX ((col1 * 40)); • No longer necessary to create an indexed generated column – Behaves like indexed virtual column • Not allowed as PRIMARY KEY Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 9 

10 .MySQL 8.0.13: Expressions as Default Values • No longer limited to literal values CREATE TABLE t1 (uuid BINARY DEFAULT (UUID_TO_BIN(UUID()))); CREATE TABLE t2 (a INT, b INT, c INT DEFAULT (a+b)); CREATE TABLE t3 (a INT, b INT, c POINT DEFAULT (POINT(0,0))); CREATE TABLE t4 (a INT, b INT, c JSON DEFAULT (‘[]’)); • Useful for types without literal values – GEOMETRY, POINT, LINESTRING, POLYGON, ... • Similar to stored generated column, but modifiable Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 10 

11 .MySQL 8.0.11: JSON_TABLE() From JSON Document to SQL Table mysql> SELECT emps.* FROM JSON_TABLE(@jsonempl, "$[*]" COLUMNS (id INT PATH "$.id", name VARCHAR(45) PATH "$.name", age INT PATH "$.age")) emps; +------+------+------+ | id | name | age | +------+------+------+ | 1 | John | 34 | | 2 | Mary | 40 | | 3 | Mike | 44 | +------+------+------+ 3 rows in set (0,00 sec) Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 11 

12 .MySQL 8.0: GIS • Simple – Completely built in and ready to use – No extra configuration or Installation • Powerful – Full Geography Support • Projected – Flat/Across 2 dimensions • Geographic – Spheroid – Details - • 5151 predefined SRSs from the EPSG Dataset 9.3 – 4668 projected – 483 geographic Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 12 

13 .MySQL 8.0: Shell Get started in minutes • Rapid prototyping capabilities – Using JavaScript and Python • Full SQL and X DevAPI support – With built in auto-completion • InnoDB Cluster support – Setup your HA solution within minutes • DevOps Tool – Designed for DevOps operations Copyright © 2018, Oracle and/or its affiliates. All rights reserved. 13 

14 . InnoDB Cluster App Servers with MySQL Router “High Availability becomes a core first class feature of MySQL!” MySQL Shell Setup, Manage, Orchestrate MySQL Group Replication Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 14 

15 .Group Replication • The number of servers has to grow or shrink dynamically • Hide complexity from the application • Reconfigure upon failure • Fast provisioning, clone • Join & Leave • Elect primary • Online A B C D E • Notification Wdnesday, 24th Oct 2018, San Francisco, CA, USA Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Oracle Open World 2018 15 

16 .MySQL 8.0 for DevOps • Online, dynamic config changes, instant add column • Performance, Efficiency, Scale • Reliability, Transactional Data Dictionary • Monitoring, IS & PS • Management, Shell, Set Persist • Security –Users, credentials, privileges, encrypting data over the wire and at rest Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 16 

17 .17 MySQL 8.0: Upgrade Checker • Quick and Easy MySQL Shell Utility – JavaScript – Python • Identifies Issues Based on Severity – No Issues – Potential Errors – Errors that must be fixed before Upgrading • Recommends Fixes – Schema, Configuration – Data on Server, etc. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 

18 .What do YOU want from the Shell? Talk to us.... Thank you for using MySQL ! Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |