Movatterモバイル変換

We proudly announce General Availability of MySQL 8.0.Download now! MySQL 8.0 is an extremely exciting new version of the world’s most popular open source database with improvements across the board. Some key enhancements include:

1. SQLWindow functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.
   
2. JSONExtended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.
3. GISGeography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes,  spatial indexes,  and spatial functions.
   
4. Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary. Powered by InnoDB!
   
5. Observability Significant enhancements to Performance Schema, Information Schema, Configuration Variables, and Error Logging.
6. Manageability Remote management, Undo tablespace management, and new instant DDL.
7. SecurityOpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, and more.
8. Performance InnoDB is significantly better at Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. Added Resource Group feature to give users an option optimize for specific workloads on specific hardware by mapping user threads to CPUs.

The above represents some of the highlights and I encourage you to further drill into the complete series of Milestone blog posts—8.0.0,8.0.1,8.0.2,8.0.3, and8.0.4 —and even further down in to the individualworklogs with their specifications and implementation details. Or perhaps you prefer to just look at the source code atgithub.com/mysql.

Developer features

MySQL Developers want new features and MySQL 8.0 delivers many new and much requested features in areas such as SQL, JSON, Regular Expressions, and GIS. Developers also want to be able to store Emojis, thus UTF8MB4 is now the default character set in 8.0. Finally there are improvements in Datatypes, with bit-wise operations on BINARY datatypes and improved IPv6 and UUID functions.

SQL

Window Functions

MySQL 8.0 delivers SQL window functions.   Similar to grouped aggregate functions, window functions perform some calculation on a set of rows, e.g.COUNT orSUM. 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. This is the set of aggregate functions in MySQL that support windowing:COUNT,SUM,AVG,MIN,MAX,BIT_OR,BIT_AND,BIT_XOR,STDDEV_POP (and its synonymsSTD,STDDEV),STDDEV_SAMP,VAR_POP (and its synonymVARIANCE) andVAR_SAMP. The set of specialized window functions are:RANK,DENSE_RANK,PERCENT_RANK,CUME_DIST,NTILE,ROW_NUMBER,FIRST_VALUE,LAST_VALUE,NTH_VALUE,LEADandLAG

Support for window functions (a.k.a. analytic functions) is a frequent user request. Window functions have long been part of standard SQL (SQL 2003). See blog post by Dag Wanvik here as well as blog post by Guilhem Bichothere.

Common Table Expression

MySQL 8.0 delivers [Recursive] Common Table Expressions (CTEs).  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. CTE is a commonly requested SQL feature, see for example feature request16244and32174. See blog posts by Guilhem Bichothere,here,here, andhere.

NOWAIT and SKIP LOCKED

MySQL 8.0 deliversNOWAIT andSKIP LOCKED alternatives in the SQL locking clause. Normally, when a row is locked due to anUPDATE or aSELECT ... 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 usingNOWAIT will never wait to acquire a row lock. Instead, the query will fail with an error. A locking clause usingSKIP LOCKED will never wait to acquire a row lock on the listed tables. Instead, the locked rows are skipped and not read at all. NOWAIT and SKIP LOCKED are frequently requested SQL features. See for example feature request49763. We also want to say thank you toKyle Oppenheim for his code contribution! See blog post by Martin Hanssonhere.

Descending Indexes

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. Another benefit of a real descending index is that it enables us to use indexes instead of filesort for anORDER BY clause with mixedASC/DESC sort key parts.Descending Indexes is a frequently requested SQL feature. See for example feature request13375 . See blog post by Chaithra Gopalareddy here.

GROUPING

MySQL 8.0  deliversGROUPING(),SQL_FEATURE T433. TheGROUPING() function distinguishes super-aggregate rows from regular grouped rows.GROUP BY extensions such asROLLUP produce super-aggregate rows where the set of all values is represented by null. Using theGROUPING() function, you can distinguish a null representing the set of all values in a super-aggregate row from aNULL in a regular row. GROUPING is a frequently requested SQL feature. See feature requests3156and46053. Thank you toZoe Dong andShane Adams for code contributions in feature request46053! See blog post by Chaithra Gopalareddy here.

Optimizer Hints

In 5.7 we introduced a new hint syntax foroptimizer hints. With the new syntax, hints can be specified directly after theSELECT | INSERT | REPLACE | UPDATE | DELETE keywords in an SQL statement, enclosed in/*+ */ style comments. (See 5.7 blog post by Sergey Glukhovhere). In MySQL 8.0 we complete the picture by fully utilizing this new style:

• MySQL 8.0 adds hints forINDEX_MERGE andNO_INDEX_MERGE. This allows the user to control index merge behavior for an individual query without changing the optimizer switch.
• MySQL 8.0 adds hints forJOIN_FIXED_ORDER,JOIN_ORDER,JOIN_PREFIX, andJOIN_SUFFIX. This allows the user to control table order for the join execution.
• MySQL 8.0 adds a hint calledSET_VAR.  TheSET_VAR hint will set the value for a given system variable for the next statement only. Thus the value will be reset to the previous value after the statement is over. See blog post by Sergey Glukhovhere.

We prefer the new style of optimizer hints as preferred over the old-style hints and setting ofoptimizer_switch values. By not being inter-mingled with SQL, the new hints can be injected in many places in a query string. They also have clearer semantics in being a hint (vs directive).

JSON

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 exampleSELECT 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. See alsoBug#79052 reported by Roland Bouman.

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 functionsJSON_ARRAYAGG() to generate JSON arrays andJSON_OBJECTAGG() to generate JSON objects . This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object. See blog post by Catalin Besleagahere.

JSON Merge Functions

TheJSON_MERGE_PATCH() function implements the semantics of JavaScript (and other scripting languages) specified byRFC7396, i.e. it removes duplicates by precedence of the second document. For example,JSON_MERGE('{"a":1,"b":2 }','{"a":3,"c":4 }'); #returns {"a":3,"b":2,"c":4}.

TheJSON_MERGE_PRESERVE() function has the semantics of JSON_MERGE() implemented in MySQL 5.7 which preserves all values, for example JSON_MERGE('{"a": 1,"b":2}','{"a":3,"c":4}');# returns {"a":[1,3],"b":2,"c":4}.

The existingJSON_MERGE() function is deprecated in MySQL 8.0 to remove ambiguity for the merge operation. See also proposal inBug#81283 and blog post by Morgan Tockerhere.

JSON Pretty Function

MySQL 8.0 adds aJSON_PRETTY() function in MySQL. The function accepts either a JSON native data-type or string representation of JSON and returns a JSON formatted string in a human-readable way with new lines and indentation.

JSON Size Functions

MySQL 8.0 adds JSON functions related to space usage for a given JSON object. TheJSON_STORAGE_SIZE() returns the actual size in bytes for a JSON datatype.  TheJSON_STORAGE_FREE() returns the free space of a JSON binary type in bytes, including fragmentation and padding saved for inplace update.

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 theJSON_REMOVE(),JSON_SET() andJSON_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. In a replicated environment, it cannot be guaranteed that the layout of a JSON document is exactly the same on the slave and the master, so the physical diffs cannot be used to reduce the network I/O for row-based replication. Thus, MySQL 8.0 provides logical diffs that row-based replication can send over the wire and reapply on the slave. See blog post by Knut Anders Hatlenhere.

GIS

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. In short, MySQL 8.0 understands latitude and longitude coordinates on the earth’s surface and can, for example, correctly calculate the distances between two points on the earths surface in any of the about 5000 supported spatial reference systems.

Spatial Reference System (SRS)

TheST_SPATIAL_REFERENCE_SYSTEMS information schema view provides information about available spatial reference systems for spatial data. This view is based on the SQL/MM (ISO/IEC 13249-3) standard. Each spatial reference system is identified by an SRID number. MySQL 8.0 ships with about 5000 SRIDs from theEPSG Geodetic Parameter Dataset, covering georeferenced ellipsoids and 2d projections (i.e. all 2D spatial reference systems).

SRID aware spatial datatypes

Spatial datatypes can be attributed with the spatial reference system definition, for example with SRID 4326 like this:CREATE TABLE t1 (g GEOMETRY SRID 4326); The SRID is here a SQL type modifier for the GEOMETRY datatype. Values inserted into a column with an SRID property must be in that SRID. Attempts to insert values with other SRIDs results in an exception condition being raised. Unmodified types, i.e., types with no SRID specification, will continue to accept all SRIDs, as before.

MySQL 8.0 adds theINFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS view as specified in SQL/MM Part 3, Sect. 19.2. This view will list all GEOMETRY columns in the MySQL instance and for each column it will list the standardSRS_NAME ,SRS_ID , andGEOMETRY_TYPE_NAME.

SRID aware spatial indexes

Spatial indexes can be created on spatial datatypes. Columns in spatial indexes must be declared NOT NULL. For example like this:CREATE TABLE t1 (g GEOMETRY SRID 4326 NOT NULL, SPATIAL INDEX(g));

Columns with a spatial index should have an SRID type modifier to allow the optimizer to use the index. If a spatial index is created on a column that doesn’t have an SRID type modifier, a warning is issued.

SRID aware spatial functions

MySQL 8.0 extends spatial functions such as ST_Distance() andST_Length() to detect that its parameters are in a geographic (ellipsoidal) SRS and to compute the distance on the ellipsoid. So far,ST_Distance and spatial relations such asST_Within,ST_Intersects,ST_Contains,ST_Crosses, etc.  support geographic computations. The behavior of each ST function is as defined in SQL/MM Part 3 Spatial.

Character Sets

MySQL 8.0 makesUTF8MB4 the default character set. SQL performance – such as sorting UTF8MB4 strings  – has been improved by a factor of 20 in 8.0 as compared to  5.7. UTF8MB4 is the dominating character encoding for the web, and this move will make life easier for the vast majority of MySQL users.

• The default character set has changed fromlatin1 toutf8mb4 and the default collation has changed fromlatin1_swedish_ci toutf8mb4_800_ci_ai.
• The changes in defaults applies to libmysql and server command tools as well as the server itself.
• The changes are also reflected in MTR tests, running with new default charset.
• The collation weight and case mapping are based onUnicode 9.0.0 , announced by the Unicode committee on Jun 21, 2016.
• The 21 language specific case insensitive collations available for latin1 (MySQL legacy) have been implemented for utf8mb4 collations, for example the Czech collation becomes utf8mb4_cs_800_ai_ci. See complete list inWL#9108 . See blog post by Xing Zhanghere .
• Added support for case and accent sensitive collations. MySQL 8.0 supports all 3 levels of collation weight defined by DUCET (Default Unicode Collation Entry Table). See blog post by Xing Zhanghere.
• Japaneseutf8mb4_ja_0900_as_cs collation forutf8mb4 which sorts characters by using three levels’ weight. This gives the correct sorting order for Japanese. See blog post by Xing Zhanghere.
• Japanese with additional kana sensitive feature,utf8mb4_ja_0900_as_cs_ks,  where ‘ks’ stands for ‘kana sensitive’. See blog post by Xing Zhanghere.
• Changed all new collations, from Unicode 9.0.0 forward, to beNO PAD instead ofPAD STRING, ie., treat spaces at the end of a string like any other character. This is done to improve consistency and performance. Older collations are left in place.

See also blog posts by Bernt Marius Johnsenhere,here andhere.

Datatypes

Bit-wise operations on binary data types

MySQL 8.0 extends the bit-wise operations (‘bit-wise AND’, etc) to also work with[VAR]BINARY/[TINY|MEDIUM|LONG]BLOB. Prior to 8.0 bit-wise operations were only supported for integers. If you used bit-wise operations on binaries the arguments were implicitly cast toBIGINT (64 bit) before the operation, thus possibly losing bits. From 8.0 and onward bit-wise operations work for allBINARY andBLOB data types, casting arguments such that bits are not lost.

IPV6 manipulation

MySQL 8.0 improves the usability of IPv6 manipulation by supporting bit-wise operations on BINARY data types. In MySQL 5.6 we introduced  theINET6_ATON() andINET6_NTOA() functions which convert IPv6 addresses between text form like'fe80::226:b9ff:fe77:eb17' andVARBINARY(16). However, until now we could not combine these IPv6 functions with  bit-wise operations since such operations would – wrongly – convert output toBIGINT. For example, if we have an IPv6 address and want to test it against a network mask, we can now use INET6_ATON(address)
& INET6_ATON(network) becauseINET6_ATON() correctly returns theVARBINARY(16) datatype (128 bits). See blog post by Catalin Besleagahere.

UUID manipulations

MySQL 8.0 improves the usability of UUID manipulations by implementing three new SQL functions:UUID_TO_BIN(),BIN_TO_UUID(), andIS_UUID(). The first one converts from UUID formatted text toVARBINARY(16), the second one fromVARBINARY(16) to UUID formatted text, and the last one checks the validity of an UUID formatted text. The UUID stored as aVARBINARY(16) can be indexed using functional indexes. The functionsUUID_TO_BIN() andUUID_TO_BIN() can also shuffle the time-related bits and move them at the beginning making it index friendly and avoiding the random inserts in the B-tree, this way reducing the insert time. The lack of such functionality has been mentioned as one of thedrawbacks of using UUID’s. See blog post by Catalin Besleagahere.

Cost Model

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. See blog post by Øystein Grøvlenhere.

Optimizer Histograms

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”.

The user creates a histogram by means of theANALYZE TABLE syntax which has been extended to accept two new clauses:UPDATE HISTOGRAM ON column [, column] [WITH n BUCKETS] andDROP HISTOGRAM ON column [, column]. The number of buckets is optional, the default is 100. The histogram statistics are stored in the dictionary table “column_statistics” and accessible through the viewinformation_schema.COLUMN_STATISTICS. The histogram is  stored as a JSON object due to the flexibility of the JSON datatype.ANALYZE TABLE  will automatically decide whether to sample the base table or not, based on table size. It will also decide whether to build asingleton or aequi-height histogram based on the data distribution and the number of buckets specified. See blog post by Erik Frøsethhere.

Regular Expressions

MySQL 8.0 supports regular expressions for UTF8MB4 as well as new functions likeREGEXP_INSTR(),REGEXP_LIKE(),REGEXP_REPLACE(), andREGEXP_SUBSTR().  The system variablesregexp_stack_limit (default8000000 bytes) andregexp_time_limit (default 32 steps) have been added to control the execution. TheREGEXP_REPLACE()  function is one of the most requested features by the MySQL community, for example see feature request reported asBUG #27389 by Hans Ginzel. See also blog posts by Martin Hanssonhere and Bernt Marius Johnsenhere.

Dev Ops features

Dev Ops care about operational aspects of the database, typically about reliability, availability, performance, security, observability, and manageability. High Availability comes with MySQL InnoDB Cluster and MySQL Group Replication which will be covered by a separate blog post. Here follows what 8.0 brings to the table in the other categories.

Reliability

MySQL 8.0 increases the overall reliability of MySQL because :

1. MySQL 8.0 stores its meta-data into InnoDB, a proven transactional storage engine.  System tables such as Users and Privileges  as well as Data Dictionary tables now reside in InnoDB.
2. MySQL 8.0 eliminates one source of potential inconsistency.  In 5.7 and earlier versions there are essentially two data dictionaries, one for the Server layer and one for the InnoDB layer, and these can get out of sync in some crashing scenarios.   In 8.0 there is only one data dictionary.
3. MySQL 8.0 ensures atomic, crash safe DDL. 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.

This work is done in the context of the new, transactional data dictionary. See blog posts by Staale Deraashere andhere.

Observability

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. This makes Information Schema practically usable by external tooling. See blog posts by  Gopal Shankarhere andhere , and the  blog post by Ståle Deraashere.

Performance Schema (speed up)

MySQL 8.0 speeds up performance schema queries by adding more than 100 indexes on performance schema tables.  The indexes on performance schema tables are predefined. They cannot be deleted,added or altered. A performance schema index is implemented as a filtered scan across the existing table data, rather than a traversal through a separate data structure. There are no B-trees or hash tables to be constructed, updated or otherwise managed. Performance Schema tables indexes behave like hash indexes in that a) they quickly retrieve the desired rows, and b) do not provide row ordering, leaving the server to sort the result set if necessary. However, depending on the query, indexes obviate the need for a full table scan and will return a considerably smaller result set. Performance schema indexes are visible withSHOW INDEXES and are represented in theEXPLAIN output for queries that reference indexed columns. Seecomment from Simon Mudd. See blog post by Marc Alffhere.

Configuration Variables

MySQL 8.0 adds useful information about configuration variables, such as the variable name,min/max values,where the current value came from, who made the change and when it was made. This information is found in a new performance schema table called variables_info. See blog post by  Satish Bharathyhere.

Client Error Reporting – Message Counts

MySQL 8.0 makes it possible to look at aggregated counts of client error messages reported by the server. The user can look at statistics from 5 different tables: Global count, summary per thread, summary per user, summary per host, or summary per account. For each error message the user can see the number of errors raised, the number of errors handled by the SQL exception handler, “first seen” timestamp, and “last seen” timestamp. Given the right privileges the user can eitherSELECT from these tables orTRUNCATE to reset statistics. See blog post by Mayank Prasadhere.

Statement Latency  Histograms

MySQL 8.0 provides performance schema histograms of statements latency, for the purpose of better visibility of query response times. This work also computes “P95”, “P99” and “P999” percentiles from collected histograms. These percentiles can be used as indicators of quality of service. See blog post by Frédéric Descampshere.

Data Locking Dependencies Graph

MySQL 8.0 instruments data locks in the performance schema. When transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. The added instrumentation exposes which data is locked (R), who owns the lock (A), and who is waiting for the data (B). See blog post by Frédéric Descampshere.

Digest Query Sample

MySQL 8.0 makes some changes to theevents_statements_summary_by_digest performance schema table to capture a full example query and some key information about this query example. The columnQUERY_SAMPLE_TEXT is added to capture a query sample so that users can run EXPLAIN on a real query and to get a query plan. The columnQUERY_SAMPLE_SEEN is added  to capture the query sample timestamp. The columnQUERY_SAMPLE_TIMER_WAIT is added to capture the query sample execution time. The columnsFIRST_SEEN andLAST_SEEN  have been modified to use fractional seconds. See blog post by Frédéric Descamps here.

Meta-data about Instruments

MySQL 8.0  adds meta-data such asproperties,volatility, anddocumentation to the performance schema table setup_instruments. This read only meta-data act as online documentation for instruments, to be looked at by users or tools. See blog post by Frédéric Descampshere.

Error Logging

MySQL 8.0 delivers a major overhaul of the MySQLerror log. From a software architecture perspective the error log is made a component in the new  service infrastructure. This means that advanced users can write their own error log implementation if desired. Most users will not want to write their own error log implementation but still want some flexibility in what to write and where to write it.  Hence, 8.0 offers users facilities to addsinks (where) andfilters (what).  MySQL 8.0 implements a filtering service (API) and a default filtering service implementation (component). Filtering here means to suppress certain log messages (selection) and/or fields within a given log message (projection). MySQL 8.0 implements a log writer service (API) and a default log writer service implementation (component).  Log writers accept a log event and write it to a log. This log can be a classic file, syslog, EventLog and a new JSON log writer.

By default, without any configuration, MySQL 8.0 delivers many out-of-the-box error log improvements such as:

• Error numbering: The format is a number in the 10000 series preceded by “MY-“, for example “MY-10001”. Error numbers will be stable in a GA release, but the corresponding error texts are allowed to change (i.e. improve) in maintenance releases.
• System messages:System messages are written to the error log as [System] instead of [Error], [Warning], [Note]. [System] and [Error] messages are printed regardless of verbosity and cannot be suppressed.  [System] messages are only used in a few places, mainly associated with major state transitions such as starting or stopping the server.
• Reduced verbosity:The default oflog_error_verbosity changes from 3 (Notes) to 2 (Warning). This makes MySQL 8.0 error log less verbose by default.
• Source Component: Each message is annotated with one of three values [Server], [InnoDB], [Replic] showing which sub-system the message is coming from.

This is what is written to the error log  in 8.0 GA after startup :

The introduction of error numbering in the error log allows MySQL to improve an error text in upcoming maintenance releases (if needed) while keeping the error number (ID) unchanged.   Error numbers also act as the basis for filtering/suppression  and internationalization/localization.

Manageability

INVISIBLE Indexes

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. This feature has been asked for by many users, for example throughBug#70299. See blog post by Martin Hanssonhere.

Flexible Undo Tablespace Management

MySQL 8.0 gives the user full control over Undo tablespaces, i.e.how many tablespaces,where are they placed, andhow many rollback segments in each.

1. No more Undo log in the System tablespace. Undo log is migrated out of the System tablespace and into Undo tablespaces during upgrade. This gives an upgrade path for existing 5.7 installation using the system tablespace for undo logs.
2. Undo tablespaces can be managed separately from the System tablespace. For example, Undo tablespaces can be put on fast storage.
3. Reclaim space taken by unusually large transactions (online). A minimum of two Undo tablespaces are created to allow for tablespace truncation. This allows InnoDB to shrink the undo tablespace because one Undo tablespace can be active while the other is truncated.
4. More rollback segments results in less contention.The  user might choose to have up to 127 Undo tablespaces, each one having up to 128 rollback segments. More rollback segments mean that concurrent transactions are more likely to use separate rollback segments for their undo logs which results in less contention for the same resources.

See blog post by Kevin Lewishere.

SET PERSIST for global variables

MySQL 8.0 makes it possible to persist global, dynamic server variables. Many server variables are both GLOBAL and DYNAMIC and can be reconfigured while the server is running. For example:SET GLOBAL sql_mode='STRICT_TRANS_TABLES';However, such settings are lost upon a server restart.

This work makes it possible to writeSET PERSIST sql_mode='STRICT_TRANS_TABLES';The effect is that the setting will survive a server restart. There are many usage scenarios for this functionality but most importantly it gives a way to manage server settings when editing the configuration files is inconvenient or not an option. For example in some hosted environments you don’t have file system access, all that you have is the ability to connect to one or more servers. As forSET GLOBAL you need the super privilege forSET PERSIST.

There is also theRESET PERSIST command. TheRESET PERSIST command has the semantic of removing the configuration variable from the persist configuration, thus converting it to have similar behavior asSET GLOBAL.

MySQL 8.0 allowsSET PERSIST to set most read-only variables as well, the new values will here take effect at the next server restart.  Note that a small subset of read-only variables are left intentionally not settable. See blog post by  Satish Bharathyhere.

Remote Management

MySQL 8.0 implements an SQL RESTART command. The purpose is to enable remote management of a MySQL server over an SQL connection, for example to set a non-dynamic configuration variable bySET PERSIST followed by aRESTART.  See blog post MySQL 8.0: changing configuration easily and cloud friendly !  by Frédéric Descamps.

Rename Tablespace (SQL DDL)

MySQL 8.0 implementsALTER TABLESPACE s1 RENAME TO s2; A shared/general tablespace is a user-visible entity which users can CREATE, ALTER, and DROP. See alsoBug#26949,Bug#32497, andBug#58006.

Rename Column (SQL DDL)

MySQL 8.0 implementsALTER TABLE ... RENAME COLUMN old_name TO new_name;This is an improvement over existing syntax ALTER TABLE <table_name> CHANGE … which requires re-specification of all the attributes of the column. The old/existing syntax has the disadvantage that all the column information might not be available to the application trying to do the rename. There is also a risk of accidental data type change in the old/existing syntax which might result in data loss.

Security features

New Default Authentication Plugin

MySQL 8.0 changes the default authentication plugin frommysql_native_password tocaching_sha2_password. Correspondingly, libmysqlclient will  use caching_sha2_password as the default authentication mechanism, too. The new  caching_sha2_password  combines better security (SHA2 algorithm) with high performance (caching). The general direction is that we recommend all users to use TLS/SSL for all  their network communication. See blog post by Harin Vadodariahere.

OpenSSL by Default in Community Edition

MySQL 8.0 is unifying onOpenSSL as the default TLS/SSL library for both MySQL Enterprise Edition and MySQL Community Edition.  Previously, MySQL Community Edition usedYaSSL. Supporting OpenSSL in the MySQL Community Edition has been one of the most frequently requested features. See blog post by Frédéric Descampshere.

OpenSSL is Dynamically Linked

MySQL 8.0 is linked dynamically with OpenSSL. Seen from theMySQL Repository users perspective , the MySQL packages depends on the OpenSSL files provided by the Linux system at hand. By dynamically linking, OpenSSL updates can be applied upon availability without requiring a MySQL upgrade or patch. See blog post by Frédéric Descampshere.

Encryption of Undo and Redo log

MySQL 8.0 implements data-at-rest encryption of UNDO and REDO logs. In 5.7 we introducedTablespace Encryption for InnoDB tables stored in file-per-table tablespaces. This feature provides at-rest encryption for physical tablespace data files. In 8.0 we extend this to include UNDO and REDO logs.  See documentationhere.

SQL roles

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. See blog post by Frédéric Descampshere.

Allow grants and revokes for PUBLIC

MySQL 8.0 introduces the configuration variablemandatory-roles which can be used for automatic assignment and granting ofdefault roles when new users are created. Example:role1@%,role2,role3,role4@localhost.  All the specified roles are always considered granted to every user and they can’t be revoked. These roles still require activation unless they are made into default roles. When the new server configuration variableactivate-all-roles-on-login is set to “ON”, all granted roles are always activated after the user has authenticated.

Breaking up the super privileges

MySQL 8.0  defines a set of new granular privileges for various aspects of what SUPER is used for in previous releases. The purpose is to limit user access rights to what is needed for the job at hand and nothing more. For exampleBINLOG_ADMIN,CONNECTION_ADMIN, andROLE_ADMIN.

Authorization model to manage XA-transactions

MySQL 8.0 introduces a new system privilegeXA_RECOVER_ADMIN which controls the capability to execute the statementXA RECOVER. An attempt to doXA RECOVER by a user who wasn’t granted the new system privilegeXA_RECOVER_ADMIN will cause an error.

Password rotation policy

MySQL 8.0 introduces restrictions on password reuse. Restrictions can be configured at global level as well as individual user level. Password history is kept secure because it may give clues about habits or patterns used by individual users when they change their password. Thepassword rotation policy comes in addition to other, existing mechanisms such as thepassword expiration policy andallowed password policy. SeePassword Management.

Slow down brute force attacks on user passwords

MySQL 8.0 introduces a delay in the authentication process based on consecutive unsuccessful login attempts. The purpose is to slow down brute force attacks on user passwords. It is possible to configure the number of consecutive unsuccessful attempts before the delay is introduced and the maximum amount of delay introduced.

Retire skip-grant-tables

MySQL 8.0  disallows remote connections when the server is started with–skip-grant-tables.  See alsoBug#79027 reported by Omar Bourja.

Add mysqld_safe-functionality to server

MySQL 8.0 implement parts of the logic currently found in themysqld_safe script inside the server. The work improves server usability in some scenarios for example when using the--daemonize startup option. The work also make users less dependent upon themysqld_safe script, which we hope to remove in the future. It also fixesBug#75343 reported by Peter Laursen.

Performance

MySQL 8.0 comes with better performance for Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. In addition, the new Resource Group feature gives users an option to optimize for specific workloads on specific hardware by mapping user threads to CPUs.

Scaling Read/Write Workloads

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 (Pareto distributed). 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. The original patch was contributed by Jiamin Huang (Bug#84266). Please study theContention-Aware Transaction Scheduling (CATS) algorithm and read the MySQL blog post by Jiamin Huang and Sunny Bainshere.

MySQL 8.0 introduces global Resource Groups to MySQL. With Resource Groups, DevOps/DBAs can manage the mapping between user/system threads and CPUs. This can be used to split workloads across CPUs to obtain better efficiency and/or performance in some use cases. Thus, Resource Groups adds a tool to the DBA toolbox,  a tool which can help the DBA to increase hardware utilization or to increase query stability. As an example, with a Sysbench RW workload running on a Intel(R) Xeon (R) CPU E7-4860 2.27 GHz 40 cores-HT box we doubled the overall throughput  by limiting the Write load to 10 cores. Resource Groups is a fairly advanced tool which requires skilled DevOps/DBA to be used effectively as effects will vary with type of load and with the hardware at hand.

Other Features