New Features of MySQL 5.7 and new features of MySQL

New Features of MySQL 5.7 and new features of MySQL

1. Introduction
In the MySQL circle, we can feel everyone's expectations and enthusiasm for MySQL 5.7. It seems that everyone can't wait to understand, learn, and use MySQL 5.7. So, we can't help wondering what improvements have been made in MySQL 5.7, what new features have been introduced, and how much performance has been improved, so that everyone can look forward to and even be excited?

Next, let's take a look at some of the new functions of MySQL 5.7. It is almost impossible to introduce all the improvements to MySQL 5.7 in an article. Therefore, I will introduce some particularly interesting and useful functions. I hope that this article will inspire everyone's interest in learning MySQL 5.7 and even attract everyone to migrate their businesses to MySQL 5.7.
MySQL 5.7 has been significantly improved in many aspects. This article will focus on security (see section 2.1), flexibility (see section 2.2), and ease of use (see Section 2.3) availability (see section 2.4) and performance (see section 2.5. Finally, the article is summarized in section 3rd.

2. New Features of MySQL 5.7
This section describes various new features of MySQL 5.7 in sequence. As MySQL 5.7 has improved a lot, this article classifies these new features into security, flexibility, ease of use, availability, and performance. Next, we will introduce each type in sequence. 

2.1 Security
Security is an eternal topic of databases. in MySQL 5.7, there are many security-related improvements. Including:
• After the MySQL database Initialization is complete, a root @ localhost user will be generated. Starting from MySQL 5.7, the root user's password is no longer blank, but a random password will be generated, this also leads to a big difference with version 5.7 when users install version 5.6.
• MySQL has deleted the test database. By default, the test database does not exist after installation. Even if you have created the test database, you can control the permissions of the test database.
• MySQL 5.7 provides simpler SSL Secure Access configurations and uses SSL encryption for default connections.
• You can set a password expiration Policy for users to force users to change their passwords after a certain period of time
Alter user 'jeffre' @ 'localhost' password expire interval 90 DAY;
• Users can be locked to temporarily disable a user.
Alter user 'jeffre' @ 'localhost' account lock;
Alter user l 'Jeffrey '@ 'localhost' account unlock;

2.2 flexibility
In this section, I will introduce two new functions 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
As the demand for unstructured data storage continues to grow, databases that store various types of unstructured data (such as MongoDB) have emerged ). From the latest database usage rankings, MongoDB has exceeded PostgreSQL, and its popularity is evident.
All major relational databases are also not weak. They have provided support for JSON to cope with the challenges of unstructured databases. MySQL database 5.7.8 and later versions also support JSON. The usage is as follows:

 CREATE TABLE t1 (jdoc JSON);INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

MySQL provides a bunch of JSON functions at the server layer to facilitate JSON operations. For storage, JSON is simply encoded into BLOB, then it is processed by the storage engine layer. That is to say, the JSON support of MySQL 5.7 has nothing to do with the storage engine, and the MyISAM storage engine also supports the JSON format.
After MySQL supports JSON, it cannot always be compared with MongoDB. However, MySQL's support for JSON can win MongoDB at least two points:
1. Hybrid storage of structured and unstructured data, with the advantages of relational and non-relational databases
2. Provide complete transaction support

2.2.2 generate column
Generated column is a new feature introduced by MySQL 5.7. The so-called generated column is calculated by other columns in the database.
For example, knowing the two sides of a right triangle requires the area of the right triangle. Obviously, the area can be calculated using two straight corner edges. At this time, only the straight corner edges can be stored in the database, and the area uses generated column, as shown below:

 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 | area |+-------+-------+------+|   3 |   4 |  6 |+-------+-------+------+

MySQL 5.7 supports two types of generated columns: virtual generated column and stored generated column. The former only saves the generated column in the data dictionary (Table metadata ), this column of data will not be persisted to the disk; the latter will persist the generated column to the disk, instead of calculating the result each time the column is read. Obviously, the latter stores data that can be computed using existing data and requires more disk space, which has no advantage over virtual column. Therefore, when the generated column type is not specified, the default value is virtual column, as shown below:

 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=InnoDB DEFAULT CHARSET=latin1

If you think that the functions provided by generate column can also be implemented in user code, there is nothing remarkable about it, then there may be a feature that will attract you, create an index for the 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 the user code. Using generate column becomes very simple:
Alter table triangle add index ix_area (area );

2.3 ease of use 
Ease of use is an eternal topic of the database. MySQL is continuously improving the ease of use of the database. In MySQL 5.7, there are many Usability Improvements, from the use of a client shortcut ctrl + c to the dedicated system library (sys) to help DBAs and developers use databases. This section focuses on the sys library introduced by MySQL 5.7.
• In linux, we often use ctrl + c to terminate the running of a command. Before MySQL 5.7, if you enter an incorrect SQL statement, press ctrl + c, although the SQL statement can be "ended", it will also exit the current Session. MySQL 5.7 has improved this intuition and will no longer quit the session.
• MySQL 5.7 can explain a running SQL statement, which is useful for DBA to analyze statements that have been running for a long time.
• In MySQL 5.7, performance_schema provides more monitoring information, including memory usage, MDL locks, and stored procedures.
2.3.1 sys schema
Sys schema is a system library introduced in MySQL 5.7.7. It contains a series of views, functions, and stored procedures. This project focuses on the ease of use of MySQL. For example, we can quickly know through sys schema which statements use temporary tables, which user requests the most io, which thread occupies the most memory, and which indexes are useless indexes.
Sys schema contains a large number of views. Where are the information of these views from? The view information comes from the performance schema statistics. Here is a good analogy:
For Linux users I like to compare performance_schema to/proc, and SYS to vmstat.
That is to say, performance schema provides information sources, but it does not make good use of these information groups. Sys schema uses performance schema information to provide answers to actual problems through views.
For example, before MySQL 5.7, you must use an external tool to know the following questions. In MySQL 5.7, you can directly query the corresponding tables in the sys database to obtain the answer:
• How to view the redundant index select * from sys. schema_redundant_indexes in the database;
• How to obtain unused indexes select * from schema_unused_indexes;
• How to view the full table scan SQL statement select * from statements_with_full_table_scans

Availability 2.4
The availability improvement of MySQL 5.7 brings many surprises. Here we will introduce several particularly useful improvements, including:
• You do not need to restart MySQL to set replication filtering rules online. You only need to stop the SQL thread. After modification, start the SQL thread
• Modify the buffer pool size online
MySQL 5.7 introduces the concept of chunk to support online buffer pool resize. Each chunk is 128 MB by default. When we modify the buffer pool online, the chunk is used as the Unit to increase or contract. The introduction of this parameter affects the configuration of innodb_buffer_pool_size. Innodb requires the buffer pool size to be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If not, innodb_buffer_pool_size will be adjusted to meet the requirements. Therefore, the size specified in the actual shard ratio configuration file of the buffer pool may be large.
• Online DDL MySQL 5.7 supports renaming indexes and modifying the size of varchar. in earlier versions, indexes or tables must be rebuilt.
Alter table t1 ALGORITHM = INPLACE, change column c1 c1 VARCHAR (255 );
• Enable GTID online. In earlier versions, since GTID cannot be enabled online, if you want to upgrade a database of earlier versions to a database version that supports GTID, you must first disable the database, it is started in GTID mode, which makes the upgrade very troublesome. This problem no longer exists after MySQL 5.7

2.5 Performance
Performance has always been a concern of users. In each new version of MySQL, there will be many performance improvements. In MySQL 5.7, there are many performance-related improvements. Here we only introduce some improvements, including performance improvement related to temporary tables, performance optimization of read-only transactions, optimization of connection establishment speed, and improvement of replication performance.
2. 5.1 Performance Improvement of temporary tables
MySQL 5.7 has made significant changes to the temporary table-related parts to Improve the Performance of temporary tables, including introducing new temporary table spaces. DDL statements for temporary tables are not persistent; for DML of temporary tables, do not write redo, and disable change buffer. Changes to all temporary tables are based on the following two facts:
1. The temporary table is only visible in the current session.
2. the life cycle of the temporary table is the current connection (MySQL is down or restarted, and the current connection ends)
That is to say, for temporary table operations, consistency is not strictly guaranteed as other data. The metadata is not persisted, avoiding writing redo and other methods, reducing the I/O of temporary table operations to improve the performance of temporary table operations.
2.5.2 read-only transaction Performance Improvement
As we all know, in traditional OLTP applications, there are far more read operations than write operations, and read operations do not modify the database. If read operations are not locked, no locks are required for read operations. Therefore, optimizing read-only transactions is a good choice.
In MySQL 5.6, read-only transactions have been optimized a lot. For example, the transaction linked list in the internal implementation of MySQL is divided into the read-only transaction linked list and the normal transaction linked list. In this way, the length of the transaction linked list to be traversed is much smaller when the ReadView is created.
In MySQL 5.7, assume that a transaction is a read-only transaction. It is converted to a normal transaction only when the transaction initiates a modification operation. MySQL 5.7 optimizes the overhead of read-only transactions by avoiding allocating transaction IDs for read-only transactions, not allocating rollback segments for read-only transactions, and reducing lock contention, improves the overall performance of the database.
 2.5.3 accelerated connection Processing
Before MySQL 5.7, variable initialization operations (THD and VIO) were completed in the connection receiving thread. Now, these jobs are delivered to the working thread to reduce the workload of connecting the receiving thread, increase the connection processing speed. This optimization will be very useful for applications that frequently establish short connections.
 2.5.4 improvement of replication Performance
 The replication delay of MySQL has been a problem that has been criticized for a long time. We are delighted that MySQL 5.7 already supports the "real" parallel replication function. The idea of MySQL 5.7 parallel replication is simple and easy to understand. In short, it is "a group of committed transactions can be played back in parallel", because these transactions have entered the prepare stage of the transaction, it indicates that there is no conflict between transactions (otherwise it is impossible to commit ). After MySQL 5.7, the replication delay problem never exists.
Note that, to be compatible with MySQL 5.6 DATABASE-based parallel replication, 5.7 introduces the new variable slave-parallel-type, which can be configured as DATABASE (default) or LOGICAL_CLOCK. We can see that the default configuration of MySQL is database-level parallel replication. To fully utilize the parallel replication function of MySQL 5.7, We need to configure slave-parallel-type to LOGICAL_CLOCK.
 3. Summary
1. from this article, we can see that MySQL 5.7 has indeed brought a lot of exciting features. We don't even need to make any changes, just need to migrate the business to MySQL 5.7, this will bring about a lot of performance improvements.
2. as you can see in this article, although MySQL 5.7 has made many improvements in ease of use, there are also a lot of things to note, such as: 1) when setting the buffer pool of innodb, pay attention to the existence of chunk. Set the buffer pool instance reasonably. Otherwise, the actual allocated buffer pool size may be much larger than expected. 2) for multi-threaded replication, you must set slave_parallel_type to LOGICAL_CLOCK. Otherwise, MySQL uses database-level parallel replication, which has no effect on most applications. So how is the correct posture for using MySQL 5.7? Netease honeycomb is a good choice. NetEase Honeycomb's RDS (Relational Database Service) project is an out-of-the-box, stable, reliable, and elastically scalable online Database Service. By using the services provided by RDS, you can use a database that has been optimized. You do not need to modify the database parameters to obtain an excellent database service.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.