MySQL 5.7 GA new features

Reprinted from:

Http://www.chinaxing.org/articles/Database/2015/10/23/2015-10-22-mysql-5.7.html

Percona a test, in the case of MySQL5.7 innodb_page_size adjustment to 4k, can be improved than 16k performance of more than 70%; https://www.percona.com/blog/2016/08/10/ small-innodb_page_size-performance-boost-ssd/

MySQL 5.7 GA new features table of Content

• Performance
• Ease of operation
• SQL Optimizer elevation
• InnoDB Enhancement
• Performace Schema
• Buffer Pool Dump/load
• Replication
• Reference

MySQL 5.7 is an enlarged recruit, ease of use, performance, usability, security, etc. have a great improvement.

Performance

More than 3 times times performance improvement. Including:

• Temporary table performance
• Index builds faster when bulk data is imported
• Improved read and write performance of InnoDB
• InnoDB's Parallel Brush disk O (n^2), O (n), formerly single-threaded, allows the use of multicore
• Connection handling capability improving Connect/disconnect performance

Ease of operation

• GTID Online Setup enabling Global Transaction Identifiers without downtime in MySQL 5.7.6
• Online modification innodb_buffer_pool_size Resizing the InnoDB Buffer Pool online
• Automatically truncate the undo log to prevent the undo log from being too large
• Rename an index online
• Expand VARCHAR the length of the type column online
• Modify copy Filter online CHANGE REPLICATION FILTER Making MySQL Slave Replication filter Dynamic
• Change the master of slave online without having to stop copying changes CHANGE MASTER master without stopping slave altogether

SQL Optimizer elevation

• Parser overriding SQL parser refactoring
• Hierarchical SQL Optimizer
• Add GIS implementations
• Query Rewrite plugin framework, users can write their own query Rewrite plug-in Write yourself a query Rewrite Plugin; https://yq.aliyun.com/articles/53686
• Subquery processing normalization: View and from statements 2 ways to get subquery to use a unified approach
• A method of performing a row expression by using interval scanning improves theperformance of "in Queries" Range Access:now in an inpredicate
• "UNION All" no longer creates temporary tables, reducing disk space and disk IO operations. State of the UNION
• Compresses unsorted fields in the sort cache, using sort caches more efficiently, reducing disk IO
• Can be executed EXPLAIN FOR CONNECTION to view the execution of the query in the case, the role of the run-time profile.
• The JSON EXPLAIN increases the output of query overhead, the number of data sets processed, and other information that makes it easier for users to make decisions about execution plans. MySQL EXPLAIN explained
• The optimizer's hint syntax is increased, and can be directly followed by a key word like SELECT,/*+ */In the form of comments
• Added an index-level hint
• Add a hint to control the execution policy of a subquery
• Optimizer's new cost evaluation model MySQL Optimizer costs model Project

InnoDB Enhancement

• Page Compression increase transparent page compression, for the support of sparse files on the system, compressed page content, and the use of sparse file system features only save compressed content, save disk space usage.

Performace Schema

The schema for monitoring system performance can be redesigned, including metadata Locking, transactions, Memory Usage, Stored Programs, Prepared statements,mysql Performace Schema

Buffer Pool Dump/load

Boost Bufferpool dump and load, support only dump each Bufferpool the hottest per n (default 25%) page, load runs in the background, does not affect user query execution MySQL dumping and reloading the InnoDB Buffer Pool

Replication

• Mutli-source Replication MySQL multi-source Replication allows multiple master configurations in the same slave
• Multi-threaded slaves configuring slave for intra-database parallelization support slave The SQL execution thread concurrently applies the Binlog event to the same database and schema (as long as there is no intersection), and the previous version is a concurrent application that supports only multiple libraries.
• Semi-sync Master can wait for n slave to confirm Binlog Log received, before it is only waiting for 1, this benefit is increased the reliability of the persistence Lossless semi-synchronous Replication on MySQL 5.7.2
• GTID mode of replication Client Auto-sensing GTID, ensuring gtid_executed and gtid_purged are not lost enabling Global Transaction Identifiers without downtime in MySQL 5.7.6
• No-blockingSHOW SLAVE STATUS
• Mysqlbinlog support rewrite-DB can overwrite db in rowevent according to the instructions
• Add more replication-related state variables to the performance Schema Table
• Binlogging xa-prepared transactions full support for XA transactions in replication

Reference

What ' s New in MySQL 5.7 GA

MySQL 5.7 GA new features