MySQL 5.7 New features Introduction

1. Introduction

Being in the MySQL community and being able to feel everyone's expectations and enthusiasm for MySQL 5.7, it seems that everyone is eager to learn, learn and use MySQL 5.7. So, we can not help but ask, what has MySQL 5.7 done, what new features are introduced, how much performance has been improved, so that everyone is looking forward to, or even cheering?

Follow me here to learn some of the new features of MySQL 5.7. It is almost impossible to introduce all the improvements in MySQL 5.7 in an article. Therefore, I will choose some special meaning, particularly useful features to introduce. Hopefully, this article will inspire you to learn about MySQL 5.7 and even get you to move your business to MySQL 5.7.

MySQL 5.7 has been vastly improved in a number of ways, from security (see section 2.1), Flexibility (see section 2.2), ease of use (see section 2.3), Usability (see section 2.4), and performance (see section 2.5). Finally, in the 3rd section, this paper makes a brief summary.

2. New features of MySQL 5.7

In this section, the new features of MySQL 5.7 are described in turn. Because MySQL 5.7 has improved a lot, the new features are simply categorized into security, flexibility, ease of use, usability, and performance. Next, it will be introduced in turn from each category.

2.1 Security

Security is the eternal topic of the database, and there are many security-related improvements in MySQL 5.7. Including:

• After the MySQL database initialization is completed, will produce an [email protected] user, starting from MySQL 5.7, the root user's password is no longer empty, but a random generation of a password, which also caused the user to install 5.7 found with the 5.6 version of a different point of the larger
• MySQL official has deleted the test database, the default after installation is not test database, even if the user created the test library, you can also control the test library permissions
• MySQL version 5.7 provides a more simple SSL secure access configuration, and the default connection is encrypted with SSL

• You can set a password expiration policy for the user, forcing the user to change the password after a certain amount of time

    ALTER USER ' Jeffrey ' @ ' localhost ' PASSWORD EXPIRE INTERVAL;

• You can "lock" the user to temporarily disable a user

    ALTER USER  ' jeffrey ' @ ' localhost ' account LOCK;  ALTER USER l ' Jeffrey ' @ ' localhost ' account  UNLOCK;

2.2 Flexibility

In this section, I will introduce the two new features of MySQL 5.7, JSON and generate column. The full use of these two features can greatly improve the flexibility of data storage.

2.2.1 JSON

As the demand for unstructured data storage continues to grow, databases of various unstructured data stores emerge (such as MongoDB). From the latest database usage leaderboard, MongoDB has surpassed PostgreSQL, and its fiery level is evident.

Major relational databases are also being outdone, providing support for JSON to address the challenges of unstructured databases. The MySQL database starts with version 5.7.8 and also provides support for JSON. It is used in the following ways:

CREATE TABLE T1 (Jdoc JSON); INSERT into T1 VALUES (' {' Key1 ': ' value1 ', ' key2 ': ' value2 '} ');

The way MySQL supports JSON is to provide a bunch of functions at the server level that are easy to manipulate JSON, and as for storage, simply encode the JSON into blobs and then hand it over to the storage engine layer, which means that the JSON support for MySQL 5.7 is not related to the storage engine. The MyISAM storage engine also supports JSON format.

After MySQL supports JSON, it's always easy to avoid some comparisons with MongoDB. However, MySQL support for JSON, at least two points can be a victory over MongoDB:

1. Hybrid storage of structured and unstructured data with the benefits of relational and non-relational databases
2. Ability to provide full transactional support

2.2.2 Generate column

Generated column is a new feature introduced by MySQL 5.7, the so-called generated column, which is calculated from the other columns in the database.

For example, knowing the two right-angled edges of a right triangle requires an area of right triangle. Obviously, the area can be calculated by two right-angled edges, so this time it is possible to store only right-angled edges in the database, with an area of generated column, as shown below:

create TABLE triangle (Sidea double, sideb double, area DOUBLE as (Sidea * sideb/2)); insert into triangle (s Idea, Sideb) VALUES (3, 4), select * from triangle;+-------+-------+------+| Sidea | Sideb |     Area |+-------+-------+------+|     3 |    4 | 6 |+-------+-------+------+ 

in MySQL 5.7, two generated column, virtual generated column and stored generated column, are supported, the former only generated column is stored in the data dictionary (the table's metadata) and does not persist this column of data to disk, which persists the generated column to disk instead of being computed each time it is read. Obviously, the latter stores data that can be computed from existing data, requires more disk space, and has no advantage over virtual column. Therefore, when you do not specify a type of generated column, the default is virtual column, as follows:

Show CREATE TABLE triangle\g*************************** 1. Row ***************************       table:trianglecreate table:create Table ' triangle ' (  ' Sidea ' double DEFAULT NULL,  ' Sideb ' double DEFAULT NULL,  ' area ' double GENERATED always as ((' Sidea ' * ' sideb ')/2) "VIRTUAL) engine=i Nnodb DEFAULT charset=latin1

If the reader feels that generate column provides functionality that can be implemented in user code, and there is nothing remarkable about it, then perhaps there is a feature that will appeal to you, which is to create an index for the Generate column. In this example, if we need to create an index from an area to speed up the query, it cannot be implemented in the user code, and using the Generate column becomes very simple:

ALTER TABLE triangle Add index Ix_area (area);

2.3 Ease of Use

Usability is the eternal topic of the database, and MySQL continues to improve the usability of the database. In MySQL 5.7, there are many usability improvements, as small as a client shortcut to CTRL + C, and large enough to specialize in providing a system library (SYS) to help DBAs and developers use the database. This section focuses on the SYS library that MySQL 5.7 introduces.

• Under Linux, we often use CTRL + C to terminate the operation of a command, before MySQL 5.7, if the user entered the wrong SQL statement, press CTRL + C, although the ability to "end" The SQL statement run, but will also exit the current session, MySQL 5.7 This is a violation of the intuition of the place has been improved, no longer quit the session.
• MySQL 5.7 can explain a running SQL, which is useful for a long-running statement of DBA analysis
• In MySQL 5.7, Performance_schema provides more monitoring information, including memory usage, MDL locks, stored procedures, etc.

2.3.1 SYS Schema

The SYS schema is a library of systems introduced in MySQL 5.7.7 that contains a series of views, functions, and stored procedures that focus on the ease of use of MySQL. For example, we can quickly know through the SYS schema which statements use temporal tables, which user requests the most IO, which thread consumes the most memory, which indexes are useless indexes, etc.

The SYS schema contains a large number of views, so where does the information for these views come from? The information in the view is derived from the performance schema statistics. Here's a good metaphor:

For Linux users I like to compare Performance_schema To/proc, and SYS to Vmstat.

In other words, the performance schema provides a source of information, but it is not well organized into useful information, which does not work well. The SYS schema uses the performance schema information to give answers to practical questions in the form of views.

For example, the following questions, before MySQL 5.7, require external tools to know that in MySQL 5.7, directly query the corresponding table under the SYS library to get the answer:

• How to view redundant indexes in a database

    SELECT * from Sys.schema_redundant_indexes;

• How to get unused indexes

    SELECT * from Schema_unused_indexes;

• How to view SQL statements that use full table scans

    SELECT * FROM Statements_with_full_table_scans

2.4 Availability

MySQL 5.7 Improvements in usability also bring a lot of surprises. Here are some particularly useful improvements, including:

• Online Settings replication filter rules no longer need to restart MySQL, only need to stop SQL thread, after the modification is complete, start SQL thread

• Modify the size of the buffer pool online

  MySQL 5.7 In order to support the online buffer pool resize, the introduction of the concept of chunk, each chunk default is 128M, when we modify the buffer pool online, in the chunk as the unit to grow or shrink. The introduction of this parameter has a certain effect on the configuration of Innodb_buffer_pool_size. InnoDB requires buffer pool size to be a multiple of innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances, and if not, it will be adjusted appropriately innodb_buffer_ Pool_size to meet the requirements, therefore, the actual allocation of buffer pool may occur larger than the size specified in the configuration file

• The Online DDL MySQL 5.7 supports renaming indexes and modifying the size of varchar, both of which need to be rebuilt in previous versions of the index or table

    ALTER TABLE T1 algorithm=inplace, change COLUMN C1 C1 VARCHAR (255);

• Open Gtid Online, in the previous version, because the online gtid is not supported, if users want to upgrade the lower version of the database to support Gtid version of the database, you need to shut down the database and then start in Gtid mode, which makes the upgrade particularly troublesome. After MySQL 5.7, this problem no longer exists.

2.5 Performance

Performance has always been the user's greatest concern, and in every new version of MySQL, there will be a lot of performance improvements. There are a lot of performance-related improvements in MySQL 5.7, and here are just a few improvements, including temporary table-related performance improvements, performance optimizations for read-only transactions, optimization of connection establishment speed, and improvements in replication performance.

Performance improvements for 2.5.1 temporary tables

MySQL 5.7 To improve temporal table-related performance, the parts related to the temporal table have been significantly modified, including the introduction of new temporary table space, the DDL for temporary tables, the related table definition is not persisted, DML for temporary tables, no redo, turn off change buffer, and so on. All temporary table changes are based on the following two facts:

1. Temporary tables are only visible in the current session
2. The life cycle of the staging table is the current connection (MySQL down or restart, the current connection ends)

That is, for temporary table operations, there is no need for consistency guarantees as strictly as other data. Reduce the IO of temporary table operations by not persisting meta information, avoiding write redo, etc., to improve the performance of temporary table operations.

2.5.2 read-only transactional performance improvements

As is known to all, in traditional OLTP applications, read operations are much more than write operations, and read operations do not modify the database, and if read is not locked, the read operation does not need to be locked. Therefore, it is a good choice to optimize the read-only transaction.

In MySQL 5.6, there has been a lot of optimizations for read-only transactions. For example, the transaction list in the MySQL internal implementation is divided into a read-only transaction list and a normal transaction list, so that when creating readview, it is much smaller to traverse the transaction list length.

In MySQL 5.7, the first assumption is that a transaction is a read-only transaction that is converted to a normal transaction only if the transaction initiates a modification operation. MySQL 5.7 Optimizes the overhead of read-only transactions and improves the overall performance of the database by avoiding the allocation of transaction IDs for read-only transactions, not allocating rollback segments for read-only transactions, reducing lock contention, and more.

2.5.3 Accelerated Connection Processing

Prior to MySQL 5.7, the initialization of variables (THD, VIO) was done in the connection receiving thread, which is now distributed to the worker thread to reduce the amount of connection to the receiving thread and increase the processing speed of the connection. This optimization will be very useful for applications that frequently build short connections.

Improvements in 2.5.4 replication performance

MySQL replication latency is one of the problems that has been criticized, and it is gratifying that the MySQL 5.7 version already supports the "real" parallel replication feature. The idea of MySQL 5.7 parallel replication is straightforward, in short, that "a group commits a transaction that can be played back in parallel" because these transactions have entered the prepare phase of the transaction, which means there is no conflict between the transactions (otherwise it is impossible to commit). After MySQL 5.7, replication latency issues never exist.

It is important to note that in order to be compatible with MySQL 5.6 Library-based parallel replication, 5.7 introduces a new variable slave-parallel-type, which can be configured as database (default) or Logical_clock. As you can see, the default configuration for MySQL is library-level parallel replication, in order to fully exploit the functionality of MySQL 5.7 parallel replication, we need to configure Slave-parallel-type as Logical_clock.

3. Summary

1. As you can see from this article, MySQL 5.7 does bring a lot of exciting features, and we don't even need to make any changes, just to move the business to MySQL 5.7, which can lead to a lot of performance improvements.

2. As you can see from this article, although MySQL 5.7 has a lot of usability improvements, there are a number of things to be aware of, such as: 1. When setting the buffer pool for InnoDB, you need to be aware of the presence of chunk and set the buffer pool reasonably instance otherwise the actual allocation of buffer pool size may appear much larger than expected; 2) multithreaded replication needs to be aware that Slave_parallel_type is set to Logical_clock, otherwise, MySQL uses parallel replication at the library level and has little effect for most applications. So, what is the correct posture to use MySQL 5.7? NetEase Hive is a good choice, NetEase hive RDS (relational database service, referred to as RDS) project is a ready-to-use, stable and reliable, elastic scalable online database services. The service provided by RDS is to use a database that has already been tuned, and the user does not need to make any modifications to the database parameters to get a good performance database service.

MySQL 5.7 New features Introduction