Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the new features of the MySQL8.0 GA version, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Let's talk about the new feature What's New in MySQL 8.0 of MySQL 8.0? (Generally Available)

It is a great pleasure to announce the release of MySQL 8.0 GA, a fully enhanced and attractive new version of MySQL. Not limited to the following points:

SQL: window function, common expression, NOWAIT,SKIPLOCKED, descending index, grouping, regular expression, character set, optimization mode based on performance attrition, histogram

SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.

Support for JSON: extended syntax, new features, enhanced sorting, partial update performance, based on the characteristics of JSON table, you can use SQL processing tools to deal with JSON data.

JSON Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.

Support for Geographic Information system

GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions.

Reliability: DDL statements now implement atomicity and fault recovery (meta-information data is stored in a separate transactional data dictionary based on InnoDB).

Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary. Powered by InnoDB!

Observability: extremely important enhancements to Packers Scripts, configuration parameters, and error logging

Observability Significant enhancements to Performance Schema, Information Schema, Configuration Variables, and Error Logging.

Manageability: remote management, Undo tablespace management, fast DDL

Manageability Remote management, Undo tablespace management, and new instant DDL.

Security: improvements to OpenSSL, new default authentication, SQL role permissions, shredded super permissions, password strength, etc.

Security OpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, and more.

Performance: InnoDB has important advantages in reading and writing, bandwidth limitations, and scenarios of hot business datasets. The new resource group feature gives users an additional tuning option to map user threads to a specified CPU under specific load and hardware conditions.

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.

These are some of the highlights of version 8.0, and I (the original author) recommend that you carefully read the release information of the previous versions of the GA version, and even the project log of these features and implementation methods. Or you can choose to read the source code directly on Github.

Developer-oriented feature Developer features

MySQL 8.0 should be oriented to the needs of MySQL developers, bringing SQL,JSON, common expressions, geographic information system and other features, because many developers have the need to store EmoJi expressions, in the new version UTF8MB4 has become the default character set. In addition, there are bitwise operations for Binary data types, and improvements to IPV6 and UUID functions.

SQL window function Window Functions

MySQL 8.0 brings the window function of standard SQL. Similar to the grouping aggregate function, the window function provides the statistical calculation of a set of row data. However, unlike the grouping aggregate function, which merges multiple rows into one row, the window function shows the aggregation of each row in the result set.

Window functions can be used in two ways, the first is the regular SQL aggregation function and the special window function. General aggregation functions such as COUNT,SUM and other functions. Window functions are proprietary to RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEADand LAG and other functions.

On the support of window function, the voice of users is more frequent. Window functions have long been part of the standard SQL in the SQL2003 specification.

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 Bichot here.

Common expression Common Table Expression

MySQL 8.0 brings the ability to support recursive common expressions. Non-recursive common expressions can be referenced multiple times because they allow temporary tables derived from the form clause, so they are interpreted as improved derived tables (temporary tables in the from clause). The recursive common expression is from a group of original residence, after processing to get a new set of data, and then brought into the processing to get more new data, recurring until no more new data can be generated. Common expressions are also a SQL function that users call frequently.

Immediately report an error or skip the lock-held line NOWAIT and SKIP LOCKED**

MySQL 8.0 brings two additional options, NOWAIT and SKIP LOCKED, to the locking clause of SQL. In the original version, when row data was used by UPDATE or SELECT. After the FOR UPDATE statement is locked, other transactions need to wait for the lock to be released before accessing this row of data. But in some scenarios, there is a need to get feedback immediately (without waiting for a lock). After using the NOWAIT parameter, if the requested data includes locked rows, you will immediately receive an error message that the query failed. After using the SKIP LOCKED parameter, the returned data will skip the locked rows.

Descending index Descending Indexes**

MySQL 8.0 brings support for descending indexes. In the 8.0 descending index, the data is organized in reverse order and looked up in the forward direction. In previous versions, although it supported the creation of descending indexes, it was achieved by creating common positive indexes and then reverse lookups. On the one hand, positive order search is faster than reverse order search, on the other hand, the real descending index in the compound order by statement (that is, asc and desc) can improve index utilization and reduce filesort.

Grouping function GROUPING

MySQL 8.0 introduces the GROUPING () grouping function, which distinguishes the over-aggregated null values generated by the extended functions of the group by clause (such as ROLLUP) by 0 and 1, where 1 is NULL, so that the over-aggregated invalid values can be filtered in the having clause.

The optimizer recommends that Optimizer Hints

With the introduction of the new optimizer recommended syntax in version 5.7, the optimizer suggests that it can be wrapped with / * + * / and placed directly after the SELECT | INSERT | REPLACE | UPDATE | DELETE keyword. In the 8.0 version, we added a new pose.

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

Version 8.0 adds INDEX_MERGE and NO_INDEX_MERGE to allow users to control whether the index merge feature is used in a single query.

MySQL 8.0 adds hints for INDEX_MERGE and NO_INDEX_MERGE. This allows the user to control index merge behavior for an individual query without changing the optimizer switch.

Version 8.0 adds JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, and JOIN_SUFFIX to allow users to control the order in which join tables are associated.

MySQL 8.0 adds hints for JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, and JOIN_SUFFIX. This allows the user to control table order for the join execution.

Version 8.0 adds SET_VAR, and the optimizer suggests that you can set a system parameter that takes effect only in the next statement.

MySQL 8.0 adds a hint called SET_VAR. The SET_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 Glukhov here.

Compared to previous optimizer recommendations and optimizer feature switch parameters, we prefer to recommend a new form of optimizer recommendation, which suggests that many locations of the query statement can be injected without invading the SQL statement (which refers to the unannotated business part of the modification statement). Compared with the optimizer suggestion that modifies the statement directly, the optimizer suggestion of the new situation is clearer in SQL semantics.

JSON

Version 8.0 adds a new JSON function and can improve performance when sorting and grouping JSON data.

MySQL 8.0 adds new JSON functions and improves performance for sorting and grouping JSON values.

Extended Paradigm Syntax Extended Syntax for Ranges in JSON path expressions** in JSON path expressions

MySQL 8.0 extends the surrounding syntax in JSON path expressions, such as SELECT JSON_EXTRACT ('[1, 2, 3, 4, 5]','$[1 to 3]'); you can get the results of [2, 3, 4]

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

JSON table function JSON Table Functions

MySQL 8.0 adds JSON table functions that can be used with SQL processing tools on JSON data. The JSON_TABLE () function creates a relational view of JSON data. JSON data can be estimated into relational rows, and users can perform SQL operations on the data returned by this function in the same way as regular relational data tables.

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 aggregate function JSON Aggregation Functions

MySQL 8.0 adds the aggregate function JSON_ARRAYAGG () for generating JSON arrays and the JSON_OBJECTAGG () function for generating JSON objects, making it possible to combine multi-line JSON documents into JSON arrays or JSON objects.

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. See blog post by Catalin Besleaga here.

JSON merge function JSON Merge Functions

The JSON_MERGE_PATCH () function executes the syntax of JavaScript, and when duplicate key-value pairs occur during a merge, the second document's key-value pair is preferred and the duplicate key value corresponding to the first document is deleted.

The JSON_MERGE_PATCH () function implements the semantics of JavaScript (and other scripting languages) specified by RFC7396, 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}.

The JSON_MERGE_PRESERVE () function has the same meaning as JSON_MERGE () in version 5. 7, which retains all values when merging.

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

The original JSON_MERGE () function was deprecated in version 8.0 to reduce ambiguity in merge operations.

JSON beautification function JSON Pretty Function

Version 8.0 adds JSON that can receive JSON native data types and string representations, and returns an indented, easy-to-read JSON formatted string.

JSON file size function JSON Size Functions

Version 8.0 adds functions related to the space occupation of specified JSON objects. JSON_STORAGE_SIZE () can return the actual size of a JSON data type in bytes, and JSON_STORAGE_FREE () can return the remaining space of that JSON data type (including fragments and preparatory space to accommodate length changes after the change).

Sorted JSON Improved Sorting of improved JSON

Version 8.0 improves the performance of JSON sorting groups by using variable-length sort keys. In some scenarios, the stress test results of Preliminary have been improved by a factor of 1.2 to 18.

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.

Partial update JSON Partial Update of JSON

Version 8.0 adds support for partial updates to the JSON_REMOVE (), JSON_SET () and JSON_REPLACE () function. If a part of the JSON document is updated, we will give the details of the change to the handle. This eliminates the need for the storage engine and replication relationships to write to the entire JSON document. In the previous replication environment, it was impossible to ensure that the arrangement (layout) of JSON documents was completely consistent on the master and slave, so the differences in physical files in the case of line-based replication could not be used to reduce the network IO consumption caused by the transmission of replication information. Therefore, version 8.0 provides a logical way to distinguish differences that can be transferred and applied to the slave library in the case of row replication.

Geographic information system GIS

Version 8.0 provides support for terrain, including support for data source information of spatial frames of reference, SRS aware spatial data types, spatial indexes, and spatial functions. All in all, version 8.0 can understand the longitude and latitude information of the earth's surface, and can calculate the distance between any two points on the earth in any supported 5000 spatial reference frames.

Spatial frame of reference Spatial Reference System (SRS)

ST_SPATIAL_REFERENCE_SYSTEMS exists in the information schema view library and provides the name of the SRS coordinate system available for use. Each SRS coordinate system has an SRID number. Version 8.0 supports more than 5, 000 coordinate systems in EPSG Geodetic Parameter Dataseset (including stereo and 2D plane earth models)

SRID Geographic data Type SRID aware spatial datatypes

The data type of the spatial class can be obtained directly from the definition of the SRS coordinate system, for example: using the SRID 4326 definition to build the table: CREATE TABLE T1 (g GEOMETRY SRID 4326);. SRID is a data type suitable for geographic types. Only data from the same SRID will be inserted into the row. An error is reported when you try to insert data with the current SRID data type. Tables that do not define SRID numbers will accept all SRID numbered data.

Version 8.0 adds the INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS view, which can display the data rows of all geographic information in the current instance and their corresponding SRS names, numbers, and geographic type names.

MySQL 8.0 adds the INFORMATION_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 standard SRS_NAME, SRS_ID, and GEOMETRY_TYPE_NAME.

SRID spatial index SRID aware spatial indexes

Spatial indexes can be created on spatial data types, and the columns that create spatial indexes must be non-empty, for example: CREATE TABLE T1 (g GEOMETRY SRID 4326 NOT NULL, SPATIAL INDEX (g))

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))

The column that creates the spatial index must have a SRID data identity for use by the optimizer, and if the spatial index is built on a column that does not have a SRID data identity, the waring information will be output.

SRID space function SRID aware spatial functions

8.0 adds parameters such as ST_Distance () and ST_Length () to determine whether the data is in SRS and calculates its spatial distance. So far, ST_Distance and other spatial relational functions such as ST_Within,ST_Intersects,ST_Contains,ST_Crosses support geographic computing. See SQL/MM Part 3 Spatial for its operational logic and behavior.

Character set Character Sets

Version 8.0 uses UTF8MB4 as the default character set by default. Compared to version 5.7, SQL performance (such as sorting UTF8MB4 strings) has been greatly improved. UTF8MB4 type is playing an important role in web coding. Setting it as the default data type will bring traversal to the vast majority of MySQL users.

The default character set changes from latin1 to utf8mb4, and the default collation rule changes from latin1_swedish_ci to utf8mb4_800_ci_ai.

Utf8mb4 has also become the default coding of libmysql, server command line tool, and server layer.

Utf8mb4 has also become the default coding of the MySQL testing framework.

The weight and case of the sorting and proofreading rules are based on version 9.0.0 of Unicode published by the Unicode Committee in 16 years.

In previous versions of MySQL, the special case and collation rules of 21 languages in latin1 coding were introduced into utf8mb4 collation rules. For example, the Czech sorting and proofreading rule has become utf8mb4_cs_800_ai_ci.

Added support for special context and stress-sensitive sorting and proofreading rules. Version 8.0 supports all three levels of DUCET (Default Unicode Collation Entry Table) sorting and proofreading rules.

Utf8mb4's utf8mb4_ja_0900_as_cs sort check rule supports three-level weight sorting for Japanese characters.

There is additional Kana support for Japanese, and ks in utf8mb4_ja_0900_as_cs_ks indicates katakana distinction.

Change unpadding in all sort checking rules prior to Unicode 9.0.0 into padding characters, which helps to improve string consistency and performance. For example, treat the space at the end of a string as other characters. The previous sort check rule left the string as it was when dealing with this situation.

Data type Datatypes Bit-wise operation Bit-wise operations on binary data types for binary data types

Version 8.0 extends the use of bit-wise operations (such as bit-wise AND, etc.) to all BINARY data types. Previously, only integer data was supported. If you force a Bit-wise operation on a binary data type, it will be implicitly converted to a 64-bit BITINT type, and several bits of data may be lost. Since version 8.0, bit-wise operations can be used on BINARY and BLOB types without worrying about reduced accuracy.

IPV6 operation IPV6 manipulation

Version 8.0 improves the operability of IPv6 data by supporting Bit-wise operations on BINARY. Version 5.6 introduces the INET6_ATON () and INET6_NTOA () functions that support the translation of IPv6 addresses and 16-bit binary data to and from each other. But until 8.0, we couldn't combine IPv6 conversion functions with bit-wise operations because of the problems in the previous paragraph. Because INET6_ATON () correctly returns 128bit's VARBINARY (16), if we want to compare an IPv6 address to a gateway address, we can now use the INET6_ATON (address) & INET6_ATON (network) operation.

UUID operation UUID manipulations

Version 8.0 improves the availability of UUID by adding three new functions (UUID_TO_BIN (), BIN_TO_UUID (), and IS_UUID ()). UUID_TO_BIN () can convert text in UUID format to VARBINARY (16), while BIN_TO_UUID (), in contrast, IS_UUID () is used to verify the validity of UUID. Once the UUID is stored as VARBINARY (16), you can use a practical index. The UUID_TO_BIN () function can move the time-dependent bits in the original converted binary value (time-related when UUID is generated) to the beginning of the data, which is more friendly to the index and reduces random insertion in the B-tree, thus reducing insertion time.

Consumption-sensitive model Cost Model query optimizer will take care of data buffering Query Optimizer Takes Data Buffering into Account

Version 8.0 automatically selects a query plan based on whether the data exists in memory. In previous versions, consumption-sensitive models always assumed that the data was on disk. Because the consumption constant of querying memory data is different from that of querying hard disk data, the optimizer will choose a more optimized way to read data according to the location of the data.

Histogram Optimizer Histograms of query optimizer

Histogram statistics are added in version 8.0. Users can generate data distribution statistics for a column in the table (usually non-indexed columns) based on the histogram, so that the optimizer can use this information to find a more optimized query plan. The most common use scenario for histograms is to calculate the selectivity of fields.

The ANALYZE TABLE syntax used to create histograms has been extended with two new clauses: UPDATE HISTOGRAM ON column [, column] [WITH n BUCKETS] and DROP HISTOGRAM ON column [, column]. The total number of histograms (buckets) is optional, with a default of 100. The statistics of the histogram are stored in the dictionary table column_statistics and can be viewed using information_schema.COLUMN_STATISTICS. Due to the flexibility of the JSON data format, histograms are now stored as JSON objects. According to the size of the table, the ANALYZE TABLE command will automatically determine whether the table should be sampled or not, and even create equal-frequency or equal-height histograms according to the distribution and total amount of data in the table.

Regular expression Regular Expressions

Along with UTF8MB4's regular support, version 8.0 also adds new functions such as REGEXP_INSTR (), REGEXP_LIKE (), REGEXP_REPLACE (), and REGEXP_SUBSTR (). In addition, regexp_stack_limit (default 8000000 bits) and regexp_time_limit (default 32 steps) parameters are added to the system to control the causality of regular expressions. REGEXP_REPLACE () is also a popular feature in the community.

Operation and maintenance automation feature Dev Ops features

Developers and operators are concerned about the operational nature of database instances, that is, reliability, availability, performance, security, observability, and manageability. We will introduce the reliability of InnoDB Cluster and MGR separately in a new article, and the following paragraphs will introduce other operational changes to the table in version 8.0.

Reliability Reliability

Version 8.0 increases reliability overall for the following reasons:

MySQL 8.0 increases the overall reliability of MySQL because:

Version 8.0 stores meta-information in InnoDB, a proven transactional storage engine. Tables such as user permissions and data dictionaries are now stored in InnoDB.

Version 8.0 removes a hidden danger that can lead to inconsistencies. In versions 5.7 and earlier, there were two data dictionaries at the service layer and engine layer, which could lead to failure of synchronization between data dictionaries in case of failure. In version 8.0, there is only one data dictionary.

Version 8.0 implements DDL with atomization and no fear of downtime. Depending on this feature, DDL statements are either fully executed or not executed at all. This is critical for the replication environment, otherwise it will lead to data drift between the master and slave because of the inconsistent table structure.

Based on the new transactional data dictionary, the reliability has been improved.

Observability Observability

Improve the performance of the information view library Information Schema (speed up)

Version 8.0 reimplements the information view library. In the new implementation, the tables of the information view library are simple views based on the data dictionary tables stored in InnoDB. This is a hundred times better than before. Make the information view library more practical to be referenced by external tools.

Improve the speed of the performance information base Performance Schema (speed up)

Version 8.0 improves its query performance by adding more than 100 indexes to the performance database. These indexes are preset and cannot be deleted, modified, or added. Instead of being convenient on a separate data structure, the index is achieved by filtering scans on existing data tables. There is no need to manage B-trees, or rebuild, update, and other operations of hash tables. The index of the performance repository behaves more like a hash index: 1, can quickly return data, 2, does not support sorting operations (pushed to the service layer for processing). According to the query, the index avoids the need for a full table scan and returns a fairly elaborate dataset. To show indexes, the index of the performance repository is not visible, but it will appear in the relevant parts of the results of the explain.

Parameter configuration Configuration Variables

Version 8.0 adds useful information about configuration parameters, such as variable name, maximum and minimum value, source of current value, change user, change time, and so on. You can query in a new performance information table, the variables_ information table.

Client error report information statistics Client Error Reporting-Message Counts

Version 8.0 makes it possible to view the summary statistics of client error messages exposed by the server. Users can view statistics in five different tables: Global count, summary per thread, summary per user, summary per host, and summary per account.

Users can view the number of times a single error message, the number of times it has been processed by the SQL exception handle, the timestamp of the first occurrence, and the last timestamp. After the user is given the appropriate permissions, the user can either query it with select or reset the statistics using truncate.

Statement delay histogram Statement Latency Histograms

In order to observe the corresponding query time, version 8.0 provides a histogram of statement delay in the performance information base, and the information of 95%, 99%, 9999% is calculated from the histogram. These percentages are used as indicators of quality of service.

Graphical data dependency lock Data Locking Dependencies Graph

Version 8.0 adds the data lock producer identity to the performance repository. When transaction A locks row R, transaction B is waiting for a row that is locked by A. The new producer identity will see which data is locked (row R in this case), who owns the lock (transaction An in this case), and who is waiting for the locked transaction (transaction B in this case)

Query sample summary Digest Query Sample

Version 8.0 made some changes to the events_statements_summary_by_digest table in the performance repository in order to capture the completed query sample and some key information about this query case. In order to capture a real query and let the user explain and get the query plan, a column of QUERY_SAMPLE_TEXT has been added. To capture the sample timestamp of the query, a column of QUERY_SAMPLE_SEEN has been added. To capture the query execution time, a column of QUERY_SAMPLE_TIMER_WAIT has been added. At the same time, the FIRST_SEEN and LAST_SEEN columns are modified to allow seconds with decimals.

Producer's meta-information Meta-data about Instruments

Version 8.0 adds meta-information such as attributes, mutable, documents, etc., to the setup_ accounts table of the performance information base. These read-only information are consulted by users or tools as producers' online documents.

Error record Error Logging**

Version 8.0 brings important changes to the error log. From the perspective of software architecture, error logging has become part of the new service architecture. This means that advanced users can write their own error log implementations as needed. Although most users don't want to do this, they may require some flexibility in how to write and where to write. Therefore, version 8.0 provides users with sinks and filters to implement. Version 8.0 implements a filtering service (API) and a default filtering service implementation (component). The filter here refers to suppressing the data of some specific error message and the output of a part of a given error message. Version 8.0 implements a log writing (API) and a default log writing service implementation (component). The log writer can receive log information and write it to the log, which can refer to a typical file log, syslog log, or JSON log.

Without making any settings by default, version 8.0 brings out-of-the-box error logging improvements. For example:

Error coding: the encoding format is now 10000 series data starting with MY. Such as "MY-10001". The error number will not change in the GA version, but the meaning it represents may change in a later maintenance release.

System information: the systematic information [System] replaces the previous [Error] (refers to the previous error log that is systemically related but prefixed with [Error]) and is added to the error log.

Error log detail reduced: the default error log detail level log_error_verbosity has been changed from 3 (output) to 2 (output warning level and above)

Information source section: each message is preceded by a comment of [Server], [InnoDB], [Replic] to show which subsystem the information is output from.

Startup information in the 8.0GA version error log:

12342018-03-08T10:14:29.289863Z 0 [System] [MY-010116] [Server] / usr/sbin/mysqld (mysqld 8.0.5) starting as process 8063 2018-03-08T10:14:29.745356Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2018-03-08T10:14:29.765159Z 0 [System] [MY-010931] [Server] / usr/sbin/mysqld: ready for connections. Version: '8.0.5' socket:'/ tmp/mysql.sock' port: 3306 Source distribution. 2018-03-08T10:16:51.343979Z 0 [System] [MY-010910] [Server] / usr/sbin/mysqld: Shutdown complete (mysqld 8.0.5) Source distribution.

The newly introduced error coding method allows MySQL to improve the error details in future maintainability releases without changing the error number. At the same time, the error number can also be used as a basis for filtering / masking error messages in customization.

Manageability Manageability invisible index INVISIBLE Indexes

Version 8.0 makes it possible to manage the visibility of indexes. An invisible index is not taken into account when the optimizer specifies a query execution plan. But the index is still maintained in the background, so it is less expensive to make it visible than to delete and add the index. Invisible indexes are designed to help DBA determine whether the index can be deleted. If you assume that the index will no longer be used, you can first make it invisible, then observe the query performance, and finally delete it if the related query performance is not degraded. This function is also widely expected.

Flexible Undo tablespace management Flexible Undo Tablespace Management

Version 8.0 gives users full control over Undo tablespaces, such as the number of tablespaces, the location of storage, and the number of rollback segments per undo tablespace.

The Undo logs are no longer stored in the system tablespace: during the version upgrade, the Undo logs are separated from the system tablespace and placed in the Undo tablespace. This leaves a way for the upgrade of existing non-independent Undo tablespaces.

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.

Undo tablespaces can be managed separately: for example, on faster disk storage.

Undo tablespaces can be managed separately from the System tablespace.For example, Undo tablespaces can be put on fast storage.

Online Undo tablespace recycling: for the need of Undo tablespace cleanup, two small Undo tablespaces are generated, which allows InnoDB to shrink Undo tablespaces online while one cleanup is active.

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.

Increase rollback segments and reduce contention: users can now choose to use up to 127Undo tablespaces, each with up to 128rollback segments. More rollback segments can make parallel transactions more likely to use separate rollback segments for their undo logs, thus reducing contention for the same resource.

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 Lewis here.

Persist SET PERSIST for global variables by setting global parameters

Under normal circumstances, global dynamic parameters can be changed online, but these parameter settings that are not written to or conflict with the configuration file may be lost after the instance is restarted. Changes to global dynamic parameters can be persisted in version 8.0.

This makes it possible to write SET PERSIST sql_mode='STRICT_TRANS_TABLES';. In this way, the parameter settings will remain after the restart. There are many scenarios in which this feature is used, but the most important thing is to give an option if it is inconvenient or impossible to change the configuration file. For example, in some hosting scenarios, you do not have access to the file system at all, only to connect to the database service. Like SET GLOBAL, SET PERSIST requires super permissions.

In addition, there is the RESET PERSIST command, which can cancel the persistence of the parameter value after the previous change using the SET PERSIST command, so that the parameter value is the same as that set by SET GLOBAL, which may be lost the next time you start.

Version 8.0 also allows SET PERSIST changes to read-only parameters in most of the current startup environment, which will take effect the next time you start. Of course, some of the read-only parameters cannot be changed by setting.

Remote management Remote Management

The RESTART command has been added in version 8.0. Its purpose is to allow remote management of MySQL instances over SQL connections. For example, after you change the read-only parameter through the SET PERSIST command, you can use this command to restart the MySQL instance. Of course, shutdown permissions are required. At the same time, mysqld processes need to be managed by supervisor processes. ).

Rename tablespace Rename Tablespace (SQL DDL)

Version 8.0 adds ALTER TABLESPACE S1 RENAME TO S2; functions, common tablespaces or shared tablespaces can be created, changed, and deleted by users.

Column rename Rename Column (SQL DDL)

Version 8.0 supports ALTER TABLE... RENAME COLUMN old_name TO new_name;, this is relative to the existing ALTER TABLE CHANGE... Syntax (which needs to be noted as all the attributes listed above) is an improvement. For some reason, the application may not be able to get all the information about the column, so this is a drawback of the previous syntax. At the same time, the previous syntax may also occasionally lead to data loss (rename should be online DDL, no need to rebuild the table)

MySQL 8.0 implements ALTER TABLE... RENAME COLUMN old_name TO new_name;This is an improvement over existing syntax

Security features Security features new default authentication plug-in New Default Authentication Plugin

Version 8.0 changed the default validation plug-in from mysql_native_password to caching_sha2_password. Accordingly, libmysqlclient also uses the caching_sha2_password authentication mechanism. The new caching_sha2_password combines higher security features (SHA2 algorithm) and high performance (caching). We currently recommend that all users use TLS/SSL for network communications.

The community version uses OpenSSL OpenSSL by Default in Community Edition by default

Version 8.0 is using OpenSSL as the unified default TLS/SSL library for enterprise and community versions. The previous community version used YaSSL. Support for OpenSSL is also a popular request from the community.

Dynamically linked OpenSSL OpenSSL is Dynamically Linked

Version 8.0 is dynamically linked with OpenSSL. From the point of view of the consumer of the MySQL software source, the MySQL package now depends on the OpenSSL file provided by the system. After dynamic linking, updates to OpenSSL do not require MySQL to be updated or patched as well.

Encrypted Encryption of Undo and Redo log for Undo and Redo logs

Version 8.0 adds static encryption of Undo and Redo logs. In version 5.7, we introduced encryption of InnoDB tables that use separate tablespaces. It provides static encryption for physical tablespace data files. In version 8.0, we extended this sticker to Undo and Redo logs.

SQL role SQL roles

The SQL role was introduced in version 8.0. A role is a collection of permissions. Its purpose is to simplify the user rights management system. You can assign roles to users, authorize roles, create roles, delete roles, and define which roles are appropriate for the current session.

Allow authorization or revocation of permissions for public roles Allow grants and revokes for PUBLIC

Version 8.0 introduces a configurable parameter, mandatory-roles, to automatically add roles to newly created users. Permissions granted to all user-specified roles cannot be redistributed. However, these roles still need to be activated unless they are set as the default role. Of course, you can also set the newly introduced activate-all-roles-on-login parameter to ON, so that all authorized roles are automatically activated after user authentication is connected.

Decompose super permission Breaking up the super privileges

Version 8.0 defines a new set of granular permissions in many ways to replace the SUPER permissions used in previous versions. It is intended to restrict users from getting only permissions related to their work. Such as BINLOG_ADMIN, CONNECTION_ADMIN, and ROLE_ADMIN.

Authorization model Authorization model to manage XA-transactions for managing XA transactions

Version 8.0 introduces a new system permission XA_RECOVER_ADMIN to control the permissions to execute XA RECOVER statements. All unauthorized users who try to execute XA RECOVER statements will cause an error.

Password rotation Policy Password rotation policy

Version 8.0 introduces restrictions on password reuse, which can be configured either at the global level or at a separate user level. Historical passwords in the past are encrypted for security reasons (disclosure of secret habits, or phrases). The password rotation policy is superimposed on other policies. Can coexist with existing mechanisms such as password expiration and password security policies.

Slow down the brute force against users' passwords to crack Slow down brute force attacks on user passwords

Version 8.0 introduces the process of waiting for verification after successive erroneous login attempts. It is designed to slow down the violent cracking of users' passwords. The number of consecutive password errors and the wait time after consecutive errors can be configured.

Undo skip-grant-tables (in the case of a remote connection) Retire skip-grant-tables

Version 8.0 disables remote user connections when the instance is started with the-skip-grant-tables parameter

Add some functions of mysqld_safe, Add mysqld_safe-functionality to server, to the instance

Version 8.0 introduces some of the logic from the previous mysqld_safe in the instance. You can improve availability in some cases when using the-- daemonize startup parameter. This also reduces the user's dependence on the mysqld-safe script that we are about to remove.

Performance Performance

Version 8.0 brings better read and write loads, IO dependent workloads, and loads in business hot data sets. In addition, the new resource group feature gives users the option to assign user threads to a specified CPU under a specific hardware load.

Scalable read and write load Scaling Read/Write Workloads

Version 8.0 is just right for both reading and writing and high-write loads. In the case of centralized load of both reading and writing, we observe that the performance of high load is twice as high as that of version 5.7 when 4 users are concurrent. On 5.7, we significantly improved the performance in the case of read-only, and 8.0 significantly improved the scalability of read-write load. The utilization of hardware performance is improved for MySQL, which is based on the redesign of the method that InnoDB writes to Redo logs. In contrast to the previous user threads scrambling to write their data changes, in the new Redo logging solution, Re'do logs are now handled by dedicated threads because of their write and cache operations. User threads no longer hold locks related to Redo writes, and the entire Redo process is time-driven.

Version 8.0 allows the use of storage devices with full horsepower. For example, when using Intel Auten flash drives, we can get 1 million sampled QPS under IO-sensitive load (IO sensitivity here means that it is not in IBP and must be obtained from secondary storage devices). This change is due to the fact that we got rid of the contention for file_system_mutex global locks.

Better performance Better Performance upon High Contention Loads ("hot rows") under high contention (hot spot data) load

Version 8.0 significantly improves performance under high contention loads. High contention load usually occurs when many transactions contend for locks on the same row of data, resulting in the generation of transaction waiting queues. In the real world, the load is not steady, and the load may break out in a specific period of time (80 over 20 rule). Version 8.0 is better for short burst loads, whether in terms of transactions per second (in other words, delays) or 95% latency. For end users, it is reflected in better hardware resource utilization (efficiency). Because the system needs to squeeze the hardware performance as much as possible in order to provide a higher average load.

Resource group Resource Groups

Version 8.0 introduces global resource groups for MySQL. With the concept of resource groups, managers can manage the allocation of CPU by user threads and system threads. This feature can be used to split the load by CPU to achieve higher efficiency and performance in certain usage scenarios. So DBA has another tool in its toolbox that can help you improve hardware utilization or query performance. For example, when running Sysbench read and write loads on Intel Zhiqiang E7-4860 2.27 GHz 40-core hyper-threaded processors, we can double the overall request input by limiting the write load to 10 cores. Resource groups are quite advanced tools, but because the effects vary depending on the type of load and the hardware on hand, experienced managers are required to use them according to local conditions.

Other features Other Features better default value Better Defaults

In the MySQL team, we keep a close eye on the default values of MySQL in order to make the user experience work right out of the box. We have changed more than 30 parameters in version 8.0 to the default values that we think are more appropriate.

Protocol Protocol

Version 8.0 adds the option to turn off metadata generation and convert it to a result set. Building, parsing, sending, and receiving metadata result sets all consume instance, client, and network resources. In some scenarios, the metadata size may be larger than the actual result set and unnecessary. Therefore, after we turn off the generation and storage of metadata, the transformation of query result sets is significantly improved. If the client does not want to receive the metadata information returned with the data, it can set the CLIENT_OPTIONAL_RESULTSET_METADATA

C language client API C Client API

Version 8.0 extends libmysql's C language API to make it more stable to stream replication transactions from the server. It is designed to implement binlog-based programs (similar to Hadoop tools for receiving MySQL data) and then need to avoid calls to informal API and packaging of internal header files

Memory cache Memcached

Version 8.0 improves InnoDB's in-memory caching skills by making use of a variety of get operations and support for range queries. We also improve read performance by supporting a variety of get operations, for example, users can get multiple key-value pairs in a single in-memory cache query. Support for scope queries is at the request of Facebook's Yoshinori. Using the range query, the user can specify a specific range and get all the desired key values for that range. Both of these features are critical to reducing back-and-forth interaction between the client and the server.

Persistent self-increasing counter Persistent Autoinc Counters

Version 8.0 persists the self-increment counter by writing to the redo log. The persistent self-increment counter solves a long-standing BUG. The MySQL recovery process replays the redo log to ensure the exact value of the self-incrementing counter. There is no longer a rollback from the value of the increment counter. This means that when the database instance recovers, the last self-increment issued in the redo log is used as the starting point for rebuilding the counter. It ensures that the value of the self-increasing counter will not be issued repeatedly, and the value of the counter is monotonously increasing. Be aware, however, that there may be holes (that is, self-appreciation that is distributed but not used). Unpersisted self-increment has always been considered a troublesome point of failure in the past.

Summary

To sum up, version 8.0 brings a lot of new features and performance improvements, so download it from dev.mysql.com now and give it a try ("▽").

Of course, you can also upgrade from the existing 5.7 instances to version 8.0. In the process, you may want to try the new upgrade checking tool that we distributed with the shell toolkit. This tool can help you check the compatibility of existing version 5.7 version 8.0 upgrades. You can try the t Migrating to MySQL 8.0 without breaking old application blog post written by Fr é d é ric Descamps.

In this article, we mainly cover the new features of the server. Not only that, we have also written a lot of articles about other aspects, such as replication, group replication, InnoDB clustering, MySQL Shell, developing API and its related connection components ((Connector/Node.js, Connector/Python, PHP, Connector/NET)

This is the end of the new features of the MySQL8.0 GA version. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.