Original SOURCE: Oracle Blog
April 19, 2018: By: Mike Frank Product Management Director
MySQL adds NoSQL and many new enhancements to the world’s most popular open source database:
- NoSQL Document Store gives developers the flexibility of developing traditional SQL relational applications and NoSQL, schema-free document database applications. This eliminates the need for a separate NoSQL document database.
- SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.
- JSON Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.
- GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions.
- Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary
- Observability Performance Schema, Information Schema, Invisible Indexes, Error Logging.
- Manageability Persistent Configuration Variables, Undo tablespace management, Restart command, and New DDL.
- High Availability InnoDB Cluster delivers an integrated, native, HA solution for your databases.
- Security OpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, authorization.
- Performance Up to 2x faster than MySQL 5.7.
MySQL 8.0 delivers many new features requested by developers in areas such as SQL, JSON and GIS. Developers also want to be able to store Emojis, thus UTF8MB4 is now the default character set in 8.0.
NoSQL Document Store
MySQL Document Store gives developers maximum flexibility developing traditional SQL relational applications and NoSQL, schema-free document database applications. This eliminates the need for a separate NoSQL document database. The MySQL Document Store provides multi-document transaction support and full ACID compliance for schema-less JSON documents.
MySQL 8.0 delivers SQL window functions in MySQL. Similar to grouped aggregate functions, window functions perform some calculation on a set of rows, e.g. COUNT or SUM. But where a grouped aggregate collapses this set of rows into a single row, a window function will perform the aggregation for each row in the result set.
Window functions come in two flavors: SQL aggregate functions used as window functions and specialized window functions.
Common Table Expression
MySQL 8.0 delivers [Recursive] Common Table Expressions (CTEs) in MySQL. Non-recursive CTEs can be explained as “improved derived tables” as it allow the derived table to be referenced more than once. A recursive CTE is a set of rows which is built iteratively: from an initial set of rows, a process derives new rows, which grow the set, and those new rows are fed into the process again, producing more rows, and so on, until the process produces no more rows.
MySQL CTE and Window Functions in MySQL Workbench 8.0
NOWAIT and SKIP LOCKED
MySQL 8.0 delivers NOWAIT and SKIP LOCKED alternatives in the SQL locking clause. Normally, when a row is locked due to an UPDATE or a SELECT … FOR UPDATE, any other transaction will have to wait to access that locked row. In some use cases there is a need to either return immediately if a row is locked or ignore locked rows. A locking clause using NOWAIT will never wait to acquire a row lock. Instead, the query will fail with an error. A locking clause using SKIP LOCKED will never wait to acquire a row lock on the listed tables. Instead, the locked rows are skipped and not read at all.
MySQL 8.0 delivers support for indexes in descending order. Values in such an index are arranged in descending order, and we scan it forward. Before 8.0, when a user create a descending index, we created an ascending index and scanned it backwards. One benefit is that forward index scans are faster than backward index scans.
MySQL 8.0 delivers GROUPING(), SQL_FEATURE T433. The GROUPING() function distinguishes super-aggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP produce super-aggregate rows where the set of all values is represented by null. Using the GROUPING()function, you can distinguish a null representing the set of all values in a super-aggregate row from a NULL in a regular row.
MySQL 8.0 adds new JSON functions and improves performance for sorting and grouping JSON values.
Extended Syntax for Ranges in JSON path expressions
MySQL 8.0 extends the syntax for ranges in JSON path expressions. For example SELECT JSON_EXTRACT(‘[1, 2, 3, 4, 5]’, ‘$[1 to 3]’);results in [2, 3, 4]. The new syntax introduced is a subset of the SQL standard syntax, described in SQL:2016, 9.39 SQL/JSON path language: syntax and semantics.
JSON Table Functions
MySQL 8.0 adds JSON table functions which enables the use of the SQL machinery for JSON data. JSON_TABLE() creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns. The user can query the result returned by the function as a regular relational table using SQL, e.g. join, project, and aggregate.
JSON Aggregation Functions
MySQL 8.0 adds the aggregation functions JSON_ARRAYAGG() to generate JSON arrays and JSON_OBJECTAGG() to generate JSON objects . This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.
JSON Merge Functions
JSON Improved Sorting
MySQL 8.0 gives better performance for sorting/grouping JSON values by using variable length sort keys. Preliminary benchmarks shows from 1.2 to 18 times improvement in sorting, depending on use case.
JSON Partial Update
MySQL 8.0 adds support for partial update for the JSON_REMOVE(), JSON_SET() and JSON_REPLACE() functions. If only some parts of a JSON document are updated, we want to give information to the handler about what was changed, so that the storage engine and replication don’t need to write the full document.
MySQL 8.0 delivers geography support. This includes meta-data support for Spatial Reference System (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions.
MySQL 8.0 makes UTF8MB4 the default character set. UTF8MB4 is the dominating character encoding for the web, and this move will make life easier for the vast majority of MySQL users.
Query Optimizer Takes Data Buffering into Account
MySQL 8.0 chooses query plans based on knowledge about whether data resides in-memory or on-disk. This happens automatically, as seen from the end user there is no configuration involved. Historically, the MySQL cost model has assumed data to reside on spinning disks. The cost constants associated with looking up data in-memory and on-disk are now different, thus, the optimizer will choose more optimal access methods for the two cases, based on knowledge of the location of data.
MySQL 8.0 implements histogram statistics. With Histograms, the user can create statistics on the data distribution for a column in a table, typically done for non-indexed columns, which then will be used by the query optimizer in finding the optimal query plan. The primary use case for histogram statistics is for calculating the selectivity (filter effect) of predicates of the form “COLUMN operator CONSTANT”.
Transactional Data Dictionary
MySQL 8.0 increases reliability by ensuring atomic, crash safe DDL, with the transactional data dictionary. With this the user is guaranteed that any DDL statement will either be executed fully or not at all. This is particularly important in a replicated environment, otherwise there can be scenarios where masters and slaves (nodes) get out of sync, causing data-drift.
Information Schema (speed up)
MySQL 8.0 reimplements Information Schema. In the new implementation the Information Schema tables are simple views on data dictionary tables stored in InnoDB. This is by far more efficient than the old implementation with up to 100 times speedup.
Performance Schema (speed up)
MySQL 8.0 speeds up performance schema queries by adding more than 100 indexes on performance schema tables.
MySQL 8.0 adds the capability of toggling the visibility of an index (visible/invisible). An invisible index is not considered by the optimizer when it makes the query execution plan. However, the index is still maintained in the background so it is cheap to make it visible again. The purpose of this is for a DBA / DevOp to determine whether an index can be dropped or not. If you suspect an index of not being used you first make it invisible, then monitor query performance, and finally remove the index if no query slow down is experienced.
MySQL InnoDB Cluster delivers an integrated, native, HA solution for your databases. It tightly integrates MySQL Server with Group Replication, MySQL Router, and MySQL Shell, so you don’t have to rely on external tools, scripts or other components.
OpenSSL by Default in Community Edition
MySQL 8.0 is unifying on OpenSSL as the default TLS/SSL library for both MySQL Enterprise Edition and MySQL Community Edition.
MySQL 8.0 implements SQL Roles. A role is a named collection of privileges. The purpose is to simplify the user access right management. One can grant roles to users, grant privileges to roles, create roles, drop roles, and decide what roles are applicable during a session.
MySQL 8.0 is up to 2x faster than MySQL 5.7. MySQL 8.0 comes with better performance for Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads.
Scaling Read/Write Workloads
MySQL 8.0 scales well on RW and heavy write workloads. On intensive RW workloads we observe better performance already from 4 concurrent users and more than 2 times better performance on high loads comparing to MySQL 5.7. We can say that while 5.7 significantly improved scalability for Read Only workloads, 8.0 significantly improves scalability for Read/Write workloads. The effect is that MySQL improves hardware utilization (efficiency) for standard server side hardware (like systems with 2 CPU sockets). This improvement is due to re-designing how InnoDB writes to the REDO log. In contrast to the historical implementation where user threads were constantly fighting to log their data changes, in the new REDO log solution user threads are now lock-free, REDO writing and flushing is managed by dedicated background threads, and the whole REDO processing becomes event-driven.
Utilizing IO Capacity (Fast Storage)
MySQL 8.0 allows users to use every storage device to its full power. For example, testing with Intel Optane flash devices we were able to deliver 1M Point-Select QPS in a fully IO-bound workload.
Better Performance upon High Contention Loads (“hot rows”)
MySQL 8.0 significantly improves the performance for high contention workloads. A high contention workload occurs when multiple transactions are waiting for a lock on the same row in a table, causing queues of waiting transactions. Many real world workloads are not smooth over for example a day but might have bursts at certain hours. MySQL 8.0 deals much better with such bursts both in terms of transactions per second, mean latency, and 95th percentile latency. The benefit to the end user is better hardware utilization (efficiency) because the system needs less spare capacity and can thus run with a higher average load.
MySQL 8.0 Enterprise Edition
For mission critical applications, MySQL Enterprise Edition provides the following additional capabilities:
- MySQL Enterprise Backup for full, incremental and partial backups, Point-in-Time Recovery and backup compression.
- MySQL Enterprise High Availability for integrated, native, HA with InnoDB Cluster.
- MySQL Enterprise Transparent Data Encryption (TDE) for data-at-rest encryption.
- MySQL Enterprise Encryption for encryption, key generation, digital signatures and other cryptographic features.
- MySQL Enterprise Authentication for integration with existing security infrastructures including PAM and Windows Active Directory.
- MySQL Enterprise Firewall for real-time protection against database specific attacks, such as an SQL Injection.
- MySQL Enterprise Audit for adding policy-based auditing compliance to new and existing applications.
- MySQL Enterprise Monitor for managing your database infrastructure.
- Oracle Enterprise Manager for monitoring MySQL databases from existing OEM implementations.
MySQL Cloud Service
Oracle MySQL Cloud Service is built on MySQL Enterprise Edition and powered by Oracle Cloud, providing an enterprise-grade MySQL database service. It delivers the best in class management tools, self service provisioning, elastic scalability and multi-layer security.