MySQL 5.7 New features introduction _mysql

1. Introduce
in the MySQL circle, can personally feel everyone to the MySQL 5.7 expectations and enthusiasm, it seems that everyone can not wait to understand, learn and use MySQL 5.7. So, we can not help but ask, MySQL 5.7 in the end what has been done to improve the introduction of what new features, performance and how much improved, so that people look forward to, or even cheer?

Follow me below to learn some of the new features of MySQL 5.7. It is almost impossible to introduce all of the improvements to MySQL 5.7 in an article. Therefore, I will choose some special meaning, particularly useful features to introduce. Hope that through this article, can inspire everyone to the MySQL 5.7 interest in learning, even can attract people to their business to MySQL 5.7.
MySQL 5.7 has been greatly improved in many ways, and this article will be covered in several areas including security (see section 2.1), Flexibility (see section 2.2), ease of use (see section 2.3), Availability (see section 2.4), and performance (see section 2.5). Finally, in the 3rd section, this paper makes a simple summary.

2. New features of MySQL 5.7
in this section, the new features of MySQL 5.7 are described in turn. Since MySQL 5.7 has improved a lot, the new features are simply categorized into security, flexibility, ease of use, usability, and performance. Next, the categories are described in turn.  

2.1 security
  Security is a timeless topic for databases, and there are a number of security-related improvements in MySQL 5.7. Includes:
  MySQL database initialization is completed, a root@localhost user is generated, starting with MySQL 5.7, the root password is no longer empty, but a random password is generated, This also caused the user to install 5.7 when the larger than the 5.6 version of a different point
MySQL has deleted the test database, the default installation is not the test database, even if the user created the test library, you can also control the test library permissions
· The MySQL 5.7 version provides a simpler SSL security access configuration, and the default connection is encrypted with SSL
• You can set the password expiration policy for the user, and after a certain time, force the user to modify the password
    ALTER user /span> ' Jeffrey ' @ ' localhost ' PASSWORD EXPIRE INTERVAL Day; &NBSP
• 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'll introduce the two new features of MySQL 5.7, namely, JSON and generate column. The full use of these two features can greatly improve the flexibility of data storage.
2.2.1 JSON
with the continuous growth of unstructured data storage requirements, databases of unstructured data storage have emerged (e.g. MongoDB). From the latest database use rankings, MongoDB has surpassed the PostgreSQL, its fiery degree can be seen.
Major relational databases are not outdone, providing support for JSON to meet the challenges of unstructured databases. The MySQL database, starting with version 5.7.8, 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 '} ');

MySQL's support for JSON is that it provides a stack of functions to manipulate JSON at the server layer, as with storage, simply encoding json into a blob and handing 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.
When MySQL supports JSON, it always avoids the use of comparisons with MongoDB. However, MySQL supports JSON with at least two points that can be MongoDB:
1. The benefits of mixed storage of structured and unstructured data while owning relational and non-relational databases
2. Ability to provide complete transaction support

2.2.2 Generate column
generated column is a new feature introduced in MySQL 5.7, the so-called generated column, which is computed by other columns in the database.
For example, knowing the two right-angled sides of a right triangle requires the area of a right triangle. Obviously, the area can be calculated by two rectangular edges, then you can just place the right angle in the database, the area using generated column, as follows:

 CREATE TABLE Triangle (Sidea double, sideb double, area double as (Sidea * sideb/2));
Insert into triangle (Sidea, Sideb) VALUES (3, 4);
select * from triangle;
+-------+-------+------+
| sidea | sideb
| +-------+-------+------+
|   3 |   4 |  6 |
+-------+-------+------+

In MySQL 5.7, two generated column supports, virtual generated column and stored generated column, which will only generated column is saved 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 rather than calculated each time it is read. Obviously, the latter holds data that can be computed from existing data and requires more disk space than the virtual column. Therefore, when you do not specify the type of generated column, the default is virtual column, as follows:

 Show CREATE TABLE Triangle\g
*************************** 1 row ***************************
    table:triangle
Create table:create Table ' triangle ' (
 ' Sidea ' double default null,
 ' sideb ' double default null,
 ' area ' Double generated ALWAYS as ((' Sidea ' * ' Sideb ') () VIRTUAL
) Engine=innodb DEFAULT charset=latin1

If the reader feels that the functionality provided by Generate column can also be implemented in user code, and there is nothing great about it, then perhaps there is another feature that will attract the picky one, that is, to create an index for generate column. In this example, if we need to create an index based on the area to speed up the query, we cannot implement it in user code, and using generate column becomes very simple:
ALTER TABLE triangle Add index Ix_area (area);

2.3 Ease of use
Ease of use is the eternal topic of the database, and MySQL is continually improving the usability of the database. In MySQL 5.7, there are many ease-of-use improvements, small enough to be used by a client shortcut key, CTRL + C, to provide a system library (SYS) specifically to help DBAs and developers use the database. This section will focus on the SYS library introduced by MySQL 5.7.
• In 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 able to "end" The SQL statement run, but, will also exit the current session, MySQL 5.7 Improved on this counterintuitive point and no longer exits the session.
MySQL 5.7 can explain a running SQL, which is useful for DBA analysis of long-running statements
• 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 system library introduced in the MySQL 5.7.7 that includes a series of views, functions, and stored procedures that focus on MySQL's ease of use. For example, we can quickly know through the SYS schema which statements use a temporary table, 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 from these views come from? The information in the view comes from performance schema statistics. Here's a good analogy:
For Linux users I like to compare Performance_schema To/proc, and SYS to Vmstat.
That is, the performance schema provides the source of information, but it does not work well by organizing the information into useful information. The SYS schema uses the performance schema information to provide an answer to the actual problem through the view.
For example, the following issues, before MySQL 5.7, need to use external tools to know that in MySQL 5.7, directly query the SYS library under the corresponding table can get the answer:
• How to view redundant indexes in a databaseselect * from Sys.schema_redundant_indexes;
• How to obtain an unused indexselect * from Schema_unused_indexes;
• How to view SQL statements that use full table scansSELECT * from Statements_with_full_table_scans

2.4 Availability
MySQL 5.7 's usability improvements have also been a lot of surprises. Here are some of the most useful improvements, including:
• Online Settings replication filter rules no longer need to restart MySQL, just stop SQL thread, after the modification is complete, start SQL thread
• Online modification of buffer pool size
MySQL 5.7 In order to support online buffer pool resize, the introduction of the concept of chunk, each chunk default is 128M, when we modify the buffer pool online, to chunk as the unit for growth or contraction. The introduction of this parameter has a certain effect on the configuration of Innodb_buffer_pool_size. InnoDB requires that the buffer pool size be a multiple of the innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances, and if not, it will be appropriately tuned innodb_buffer_ Pool_size to meet the requirements, the actual allocation of the buffer pool may appear to be larger than the size specified in the configuration file
online DDL MySQL 5.7 supports renaming indexes and modifying the size of varchar, both of which require rebuilding indexes or tables in previous versions
ALTER TABLE T1 algorithm=inplace, change COLUMN C1 C1 VARCHAR (255);
• Open Gtid online , in the previous version, because it does not support online open Gtid, users want to upgrade the lower version of the database to support the Gtid version of the database, you need to close the database, and then start in Gtid mode, so the upgrade is particularly troublesome. After MySQL 5.7, this problem no longer exists

2.5 Performance
Performance has always been the user's most concern, in MySQL each new version, there will be a lot of performance improvement. In MySQL 5.7, there is a lot of performance-related improvements, which only cover some of the improvements, including the performance improvements associated with temporary tables, performance optimization for read-only transactions, optimization of connection establishment speeds, and replication performance improvements.
2 . 5.1 Performance improvements for temporary tables
MySQL 5.7 In order to improve the performance of temporary tables, the sections related to temporary tables have been drastically modified, including the introduction of new temporary tablespaces, the definition of the table for temporary tables, and the definitions of tables that are not persisted, the DML of temporary tables, the redo, and the change buffer. All temporary table changes are based on the following two facts:
1. Temporary tables are visible only in the current session
2. The life cycle of the temporary table is the current connection (MySQL downtime or reboot, then the current connection ends)
That is, the operation of a temporary table does not require the same strict consistency guarantee as other data. Reduce the IO of temporary table operations to improve the performance of temporary table operations by not persisting meta information, avoiding writing redo, and so on.
2.5.2 read-only transactional improvements
As we all know, in traditional OLTP applications, read operations are far more than write operations, and the read operation does not modify the database, if it is locked read, the read operation does not need to lock. Therefore, it is a good choice to optimize the read-only transaction.
In MySQL 5.6, a number of optimizations have been made on read-only transactions. For example, the transaction list in the MySQL internal implementation is divided into read-only transaction list and common transaction list, so it is much smaller to traverse the transaction list length when creating Readview.
In MySQL 5.7, you first assume that a transaction is a read-only transaction and that it is converted to a normal transaction only if the transaction initiates a modification operation. MySQL 5.7 Optimizes the cost of read-only transactions and improves the overall performance of the database by avoiding the allocation of transaction IDs for read-only transactions, by not allocating rollback segments for read-only transactions, and by reducing lock competition.
2.5.3 Accelerated Connection processing
Prior to MySQL 5.7, the initialization of the variable (THD, VIO) was done in the connection to the receiving thread, which is now distributed to the worker thread to reduce the workload of the connection receiving thread and improve the processing speed of the connection. This optimization will be useful for applications that frequently build short connections.
Improvement of 2.5.4 replication performance
MySQL replication latency is one of the issues that has been criticized, and it is gratifying that the MySQL 5.7 version already supports "real" concurrent replication capabilities. MySQL 5.7 The idea of parallel replication is straightforward, in short, "a group of committed transactions can be replayed in parallel," Since these transactions have entered the prepare phase of the transaction, there is no conflict between the transactions (otherwise it is impossible to commit). After MySQL 5.7, replication latency issues never exist.
It should be noted here 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, MySQL's default configuration is the library level of parallel replication, in order to give full play to the MySQL 5.7 parallel replication function, we need to configure Slave-parallel-type into Logical_clock.
3. Summary
1. As you can see from this article, MySQL 5.7 does bring a lot of exciting features, we do not even need to make any changes, just want to move the business to MySQL 5.7, can bring a lot of performance improvements.
2. As you can see from this article, although the MySQL 5.7 has a lot of improvements in ease of use, but there are a lot to pay attention to, for example: 1 in the set InnoDB buffer pool, you need to pay attention to the existence of chunk, reasonable set buffer pool instance otherwise, the actual allocated buffer pool size may be much larger than expected; 2 multithreading replication requires attention to set the Slave_parallel_type to Logical_clock, otherwise, MySQL uses a library-level parallel replication, which has little effect on most applications. So, what is the correct posture to use MySQL 5.7? NetEase Honeycomb is a good choice, NetEase Honeycomb's RDS (relational database service, referred to as RDS) project is an open, stable and reliable, flexible and scalable online database services. The service provided by RDS is the use of a database that has been tuned so that users do not need to make any changes to the database parameters to obtain an excellent performance database service.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.